关于explain的简介,请看前一篇博客
优化常用方法
- 全值匹配最好
- 最佳左前缀原则
- 查询无法使用索引范围条件右边的列
- 尽量使用覆盖索引,也就是只查询创建索引的字段,减少*的使用
- mysql中,在使用!=(<>)这两个符号的时候,索引会失效
- is null is not null 也会索引失效
- 用like模糊查询,如果以“%xx”开头,也会索引失效,使用“xx%”索引不失效,同时针对覆盖索引“%xx%”也不失效
- 使用or也会索引失效
全值匹配
也就是我们怎么创建的索引,我们就怎么使用税索引,顺序也不改变,通过下面的例子
这里有2个索引,一个是主键索引一个是自定义索引,那么我们使用就按如下使用
EXPLAIN select * from demo1 where c1='a1'
EXPLAIN select * from demo1 where c1='a1' and c2='a2'
EXPLAIN select * from demo1 where c1='a1' and c2='a2' and c3='a3'
以上的三个例子,就是完全按照我们的索引建立顺序使用的,没有一点不一样,那么接下来,继续看一个例子
EXPLAIN select * from demo1 where c1='a1' and c3='a3' and c2='a2'
这个例子可以很明显的看出我们索引的顺序是不一样,然而还是使用了三个索引,这个又是什么原因呢?这个就是因为我们程序执行的顺序,和我们写的顺序不一定是一样的,mysql会自动帮我们优化
最佳左匹配原则
其实也就是索引的使用,会匹配我们创建的索引字段,而且是从头开始匹配,如果中间有缺失,那后续的索引也将失效
EXPLAIN select * from demo1 where c1='a1' and c3='a3'
GROUP BY/ORDER BY
我们都知道这两个关键字,一个是分组,一个是排序,那么这两种又是否可以使用到我们的索引呢?又或者说着两种会不会存在索引失效
EXPLAIN select * from demo1 where c1='a1' and c3='a3' GROUP BY c2
EXPLAIN select * from demo1 where c1='a1' and c2='a2' GROUP BY c3
- 如果在where中缺失,一样的会导致后面的索引失效
EXPLAIN select * from demo1 where c1='a1' and c3='a3' ORDER BY c2
EXPLAIN select * from demo1 where c1='a1' and c2='a2' ORDER BY c3
EXPLAIN select * from demo1 where c1='a1' ORDER BY c3,c2
EXPLAIN select * from demo1 where c1='a1' ORDER BY c2,c3
- 这个给出了4句关于排序的sql语句,进行对比分析,每一句都相差不大
- 其实我们可以看出,只要c1,c2,c3的顺序能连起来就都能使用到所有的索引,无论是查询还是排序,虽然查询会因为最左法则,而缺失
- 然后,如果我们的排序是基于多个数据一起排序的,因为这个排序,会首先按照第一字段排序,然后看第二字段,所以是无法改变顺序的,所以如果顺序是c1,c3,c2就会导致排序无法使用索引,我们只需要改变一下顺序就可以了
不等号以及is null失效
EXPLAIN select * from demo1 where c1!='a2' and c3>'a3' and c2='a2'
EXPLAIN select * from demo1 where c1 is not null and c3>'a3' and c2='a2'
关于like的模糊查询
EXPLAIN select * from demo1 where c1 like '%a' and c3>'a3' and c2='a2'
EXPLAIN select * from demo1 where c1 like '1%' and c3>'a3' and c2='a2'
EXPLAIN select * from demo1 where c1 like '%a1%' and c3>'a3' and c2='a2'
EXPLAIN select id,c1,c2,c3 from demo1 where c1 like '%a1%' and c3>'a3' and c2='a2'
- 这里给出4组模糊查询的对比分析
- 明显看出%在前是索引失效,%在后不影响索引,然后两侧都有%的是看是否是全值匹配
总结
- 以上就是针对explain的全部讲解以及针对索引优化的分析了
- 希望这些查询例子可以帮助到你,
- 如果大家有补充欢迎评论区留言交流