SQL优化策略

文章转自https://developer.aliyun.com/article/779151

声明：以下SQL优化策略适用于数据量较大的场景下，如果数据量较小，没必要以此为准，以免画蛇添足。

一、避免不走索引，而导致数据库引擎放弃索引而进行全表扫描

序号	问题排查	需要优化的例子	优化方式	补充说明
1	避免在字段开头模糊查询	SELECT * FROM t WHERE username LIKE ‘%陈%’	SELECT * FROM t WHERE username LIKE ‘陈%’	如果需求是要在前面使用模糊查询，那么1) 使用MySQL内置函数INSTR(str,substr) 来匹配,作用类似于java中的indexOf()，查询字符串出现的角标位置 2)使用FullText全文索引，用match against 检索数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级.3)当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。
2	尽量避免使用in 和not in	SELECT * FROM t WHERE id IN (2,3)`	如果是连续数值，可以用between代替。如果是子查询，可以用exists代替	SELECT * FROM t WHERE id BETWEEN 2 AND 3 或者 select * from A where exists (select * from B where B.id = A.id);
3	尽量避免使用 or	SELECT * FROM t WHERE id = 1 OR id = 3	可以用union代替or	SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3
4	尽量避免进行null值的判断	SELECT * FROM t WHERE score IS NULL	可以给字段添加默认值0，对0值进行判断	SELECT * FROM t WHERE score = 0`
5	尽量避免在where条件中等号的左侧进行表达式、函数操作	SELECT * FROM T WHERE score/10 = 9	可以将表达式、函数操作移动到等号右侧	SELECT * FROM T WHERE score = 10*9`
6	当数据量大时，避免使用where 1=1的条件	SELECT username, age, sex FROM T WHERE 1=1	用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and	别懒得写代码就行
7	查询条件不能用 <> 或者 !=	使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。	如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。
8	where条件仅包含复合索引非前置列	select col1 from table where key_part2=1 and key_part3=2	复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。	如果要用上联合索引，则第一个索引不能漏
9	隐式类型转换造成不使用索引	elect col1 from table where col_varchar=123;	SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引	要么在数据库表结构修改属性类型，要么修改where条件的属性类型
10	order by 条件要与where中条件一致	SELECT * FROM t order by age;	SELECT * FROM t where age > 0 order by age;
11	正确使用hint优化	SELECT * FROM students FORCE WHERE class_id = 1 ORDER BY id DESC;	SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;	待补充专项说明

二、SELECT语句其他优化

序号	问题	原因	优化方式
1	避免出现 select *	select * 在任何类型的数据库中都是不好的sql编写习惯。select * 取出全部列，会让优化器无法完成索引覆盖这类优化，会影响优化器对执行计划的选择，也会增加网络带宽的消耗，更会带来额外的I/O，内存，CPU的消耗。	需要什么字段，直接select + 字段名称
2	避免出现不确定结果的函数	特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。
3	多表关联查询时，小表在前，大表在后	在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
4	使用表别名	当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误
5	用where字句替换HAVING字句	避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中
6	调整Where字句中的连接顺序	MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

三、查询条件优化

序号	优化方式	解释	样例
1	对于复杂查询，可以使用中间临时表暂存数据
2	优化group by 语句	默认情况下，MySQL 会对GROUP BY分组的所有值进行排序，因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL禁止排序	SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;
3	优化join语句	子查询可以用join来替代
4	优化union查询	宁可使用 union all ，而不只是 union
5	拆分复杂SQL为多个小SQL避免大事务	1)简单的SQL容易使用到MySQL的QUERY CACHE；2)减少锁表时间特别是使用MyISAM存储引擎的表；3)可以使用多核CPU
6	使用truncate代替delete	当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位，使自增字段值归零
7	使用合理的分页方式以提高分页效率