前言:
我们常说的SQL优化,简单来说就是索引优化,通过合理创建索引,调整SQL语法等,来提升查询效率,想要进行SQL优化,就必须知道索引的原理,而且能够看懂SQL的执行计划。
MySQL–索引底层数据结构详解
MySQL–索引类型详解
MySQL–explain执行计划详解
准备数据:
本篇数据都基于上一篇,传送门如下:
MySQL–索引优化实战篇(1)
MySQL–索引优化实战篇(2)
MySQL–索引优化实战篇(3)
案例一 or 查询:
explain select * from user where user_name='张三' or age=28 and address='广东';
执行计划:
分析执行计划:
- key为空,很明显,没有使用到联合索引 index_name_age_address。
原因:SQL语法使用了 or ,导致无法使用索引了。
那怎么才能命中索引呢?同样是使用覆盖索引,如下:
explain select user_name,age,address from user where user_name='张三' or age=28 and address='广东';
执行计划:
分析执行计划:
- 同样的查询条件,只是这次指定返回联合索引中的字段,这次就走了索引。
结论:跟猜想一致,or 查询使用覆盖索引后可以命中索引,返回字段不是很多的时候,我们可以尽量去设计走覆盖索引。
案例二 in 和 not in 查询:
explain select * from user where user_name='张三' and age in (23,25,28) and address='广东';
执行计划:
分析执行计划:
- 使用了 index_name_age_address 索引,没有问题。
- key_len 1006,根据前面几篇分析可知联合索引 index_name_age_address 的三个字段都命中了索引,复合最左前缀法则,没有问题。
再看 not in:
explain select * from user where user_name='张三' and age not in (23,25,28) and address='广东';
执行计划:
分析执行计划:
- 使用了 index_name_age_address 索引,没有问题。
- key_len 204,根据前面几篇分析可知只命中了联合索引 index_name_age_address 的user_name 和 age 字段,address 字段并没有命中索引。
结论:in 和 not in 都可以走索引,并非很多资料所说 in查询走索引,not in 查询不走索引,但是还是可以看出来,in 查询可以命中跟多的索引,对应的效率也会高一些,具体是 in 查询还是用 not in 查询要根据实际情况而定,总之就是需要合理设计索引,多看SQL执行计划。
索引优化小技巧:
- 多看执行计划,只有看了执行计划且看懂了,才能进行SQL优化。
- 多使用联合索引,少使用单列索引。
- 合理使用覆盖索引,可以解决很多不能命中索引的情况。
- 按需返回结果,尽量不要使用 select * from table。
- 谨慎使用 or、!=、not in、is nul、is not null 等。
- 多尝试不同的SQL写法,比如有时候使用 exists 代替 in 查询。
- 尽量使用后缀模糊查询,谨慎使用前缀模糊查询。
- 对于group by 引起的filesort,可以尝试使用order by null来禁止分组后排序。
- 实践实践验真理的唯一标准。
如有不正确的地方请各位指出纠正。