2020-09-21

SQL性能优化总结

1、优先考虑使用等号(=)操作。其次考虑使用大于等于(>=)，小于等于(<=)操作。然后再考虑大于(>)，小于(<)操作
2、谨慎使用排序(ORDER BY)和分组(GROUP BY)
3、减少使用LIKE操作，并避免使用不等于(<>)操作。
4、如果有order by的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现file_sort的情况，影响查询性能。
5、使用LIKE的时候，应该使用右匹配(A%)，而避免使用左右匹配(%A%)和左匹配(%A)（如果需要请走搜索引擎来解决）。使用右匹配尽可能提供较多的数据降低查询开销。
6、使用And时，筛选结果集小的表达式应该置于And左侧，使用Or语句时，筛选结果集大的表达式应该列于Or左边。
7、避免不必要的排序。使用UNION ALL替代UNION，添加恰当的索引，或在DISTINCT，GROUP BY，ORDER BY子句涉及的列上创建索引。
8、避免对3个以上的字段排序。
9、避免对长度超过30的字符字段进行排序。
10、避免在排序字段上添加表达式
11、不使用任何聚合函数情况下，使用DISTINCT替代GROUP BY。
12、根据业务逻辑选择最低粒度的隔离级别。在允许脏读的前提下，在查询语句中应该使用WITH UR，避免不必要的锁表。
13、不应该在代码使用DDL相关操作。
14、查询语句应该设置有效的限定条件，返回预期的结果集，避免返回过大的结果集。
15、使用 SELECT…FETCH FIRST n ROWS ONLY OPTIMIZE FOR n ROWS限制结果集大小,建议100以内.
16、用于比较的数据应该使用相同的数据类型，避免发生数据转换。
17、SQL语句变量应该使用参数化，尤其是id类型，时间类型字段等。
18、避免在WHERE条件中对索引字段进行函数操作。
19、SQL中的子查询嵌套层次避免超过4层。
20、查询表达式左边避免出现函数以及其他运算表达式，必须的表达式应该用替代的方法在右边实现。
21、所有的sql应该尽可能提前在测试环境上进行优化和调试。获取必要的执行计划和运行时间信息。
22、SQL语句尽可能简单 大SQL语句尽可能拆成小SQL语句
23、事务要简单，整个事务的时间长度不要太长，SQL结束后及时提交。限制单个事务所操作的数据集大小，不能超过10000条记录
24、禁止使用触发器、函数、存储过程。
说明：存储过程难以调试和扩展，更没有移植性。
25、查询语句的结果字段应该书写明确的列名，避免使用通配符（select ），需要查询哪几个字段就select哪几个字段，避免buffer pool被无用数据填充。
说明：1）增加查询分析器解析成本。2）增减字段容易与resultMap配置不一致。
26、避免在数据库中进行数学运算（数据库不擅长数学运算和逻辑判断），不在索引列进行数学运算和函数运算
27、条件中使用到OR的SQL语句必须改写成用IN()（OR的效率比IN低很多）
28、in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，IN里面的数据个数尽量控制在500个以内
29、可以用exist代替in，exist在某些场景比in效率高，尽量不使用not in
30、limit分页注意效率，limit越大，效率越低，当只要一行数据时使用LIMIT 1
说明：可以改写limit，例如：select id from test limit 10000,10 可以改写为 select id from test where id > 10000 limit 10
31、获取大量数据时，建议分批次获取数据，每次获取数据少于 10000 条，结果集应小于 1M
32、避免使用大表做 JOIN，使用group by分组、自动排序
33、超过三个表禁止JOIN；需要join的字段，数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。
34、SQL语句禁止出现隐式转换，例如：select id from test where id=’1’，其中 id 列为 int 等数字类型
35、禁止在OLTP类型系统中使用没有where条件的查询
36、使用 prepared statement 语句，只传参数，比传递 SQL 语句更高效；一次解析，多次使用；降低SQL注入概率
37、禁止单条 SQL 语句同时更新多个表
38、不在业务高峰期批量更新或查询数据库，避免在业务高峰期alter表
39、禁止在主库上执行 sum,count 等复杂的统计分析语句，可以使用从库来执行（MySQL）
40、查询列和排序列与索引列次序保持一致，确保使用到索引。
41、使用组合索引，必须确保组合索引的第一个字段出现在查询条件中。
42、不要使用count(列名)或count(常量)来替代count()，count()就是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关。
说明：count()会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行。
43、count(distinct col) 计算该列除NULL之外的不重复数量。注意 count(distinct col1, col2) 如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0。
44、当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL，因此使用sum()时需注意NPE问题。
45、在代码中写分页查询逻辑时，count为0应直接返回，避免执行后面的分页语句。
46、不得使用外键与级联，一切外键概念必须在应用层解决。
说明：外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。