怎样提高sql效率？

来源：互联网发布：matlab与java混合编程编辑：程序博客网时间：2024/04/28 20:14

索引相关

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

2. 应尽量避免在 where 子句中对字段进行 null 值判断，会导致引擎放弃使用索引

select id from t where num is null;

可以在 num 上设置默认值 0,确保表中 num 列没有 null 值，然后这样查询：

select id from t where num=0;

3. 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引。

4. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

5. 应尽量避免在 where 子句中使用 or 来连接条件，在某些版本中将导致引擎放弃使用索引

select id from t where num=10 or num=20;

可以这样查询：

select id from t where num=10 union allselect id from t where num=20;

6. 如果在 where 子句中使用参数，也会导致全表扫描。

因为 SQL 只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

Sql 代码 : select idfrom t where num=@num ;

7. 应尽量避免在 where 子句中对字段进行表达式、模糊查询，这将导致引擎放弃使用索引而进行全表扫描。

select id from t where num/2=100;

可以这样查询：

select id from t where num=100*2;

select id from t wheresubstring(name,1,3)='abc'; --name 以 abc 开头的 id

应改为：

select id from t where name like 'abc%';

8. not in 也要慎用，否则会导致全表扫描：

9. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

需要联合索引(或联合主键)的数据库要注意索引的顺序。SQL语句中的匹配条件也要跟索引的顺序保持一致。

10. 并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引列有大量数据重复时， SQL 查询可能不会去利用索引，如一表中有字段 , male、female 几乎各一半，那么即使在上建了索引也对查询效率起不了作用。

11. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过5 个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

表设计相关：

12. 除非必要，表必须有主键

13. 库名、表名、列名，使用小写字母、下划线，不超过32字符，禁止拼音英文混合

1. 数据库和表名应尽可能和所服务的业务模块名一致

2. 服务与同一个子模块的一类表应尽量以子模块名(或部分单词)为前缀或后缀

3. 表名应尽量包含与所存放数据对应的单词

4. 字段名称也应尽量保持和实际数据相对应

5. 联合索引名称应尽量包含所有索引键字段名或缩写，且各字段名在索引名中的顺序应与索引键在索引中的索引顺序一致，并尽量包含一个类似idx的前缀或后缀，以表明期对象类型是索引。

6. 约束等其他对象也应该尽可能包含所属表或其他对象的名称，以表明各自的关系

14. 对于中日韩文环境，尽量使用UTF-8编码

15. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型只要比较一次。

16. 尽可能的使用 varchar/nvarchar 代替 char/nchar , 因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索（条件判断）效率显然要高些。

17. 非特别需求不使用text、blob这样的大容量类型

18. 如非必要，禁止在数据库中存放文件、图片等大容量数据，以及流水队列数据(如日志)，应将这些存为文件，在数据库中存储URL地址

19. 建议：钱数使用整数，避免浮点，增加精确度

这个问题产生于浮点数的精度问题，而整数没有此类问题

20. 手机号码使用varchar，节省资源

varchar可以更好的使用模糊查询

21. 对于大数据环境，禁止使用存储过程、视图、触发器、Event

高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”。

22. 如需要删除表，先 truncate table ,然后 drop table ,这样可以减少事务的使用，减少资源消耗。

23. 减少外键使用，从而减少数据库复杂度，为高并发提供更高的性能

24. 注意备份

查询相关：

25. 任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

减少CPU io net浪费

26. 尽量避免大事务操作，提高系统并发能力。

每个事务操作都会锁定行或表，若执行了一个很大规模的事务操作则会长期处在锁定状态。

27. select count(*) from table；这样不带任何条件的count效率低下，尽量改为count(1)提高效率。

28. 尽量杜绝对大数据量的表执行JOIN操作，会导致读消耗极大

29. 不使用INSERT INTO t_xxxVALUES(xxx) 这种不指定列的添加

30. 后端使用时，除非对数据来源保证安全，否则一律使用绑定变量（参数化）

Select * from employee where id=?

Preparestatement.setInt(1,1234567)

Java中Preparestatement就是为处理绑定变量提供的对像，绑定变量有以下优点：