Explain详细介绍,可前往MySQL Explain执行计划详解查看
现有如下表:
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时 间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
)
实际案例
案例一
select * from employees where name = 'LiLei' and position='dev' order by age;
可以先看下这条SQL语句,是否会走索引?如果走索引,会走几个字段?
首先,肯定是会走索引的,至少name字段是会走索引的,但是position不会走,因为中间缺少了age字段。
接下来使用explain来看下执行结果:
key_len长度为74:在MySQL Explain执行计划详解中介绍了varchar的计算方式为:varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
所以,这条SQL语句的ken_len为:3*24+2=74
在这里可以思考下,age有没有走索引呢?
答:age其实是走索引的,只是age不是等值查询,而是走的排序。在上面explain的结果中可以看到Extra中的内容是Using index condition,那么证明走了索引的排序。(因为name字段已经确定了,在name字段一定的情况下,age字段在索引树中是有序的,所以走了索引)
总的分析如下:
利用最左前缀法则:中间字段不能断,因此查询用到了name索引 ,从key_len=74也能看出,age索引列用在排序过程中,因为Extra字段里没有using filesort
案例二
select * from employees where name = 'LiLei' order by position;
分析如下:
从explain的执行结果来看:key_len=74,查询使用了name索引,由于用了position进行排序,跳过了age,出现了Using filesort。(跳过了age字段,position字段不一定有序了,所以没有用到索引)
案例三
select * from employees where name = 'LiLei' order by age,position;
结合上述描述,思考下这条SQL是否会走排序的索引???
使用Explain查看:
分析如下:
查找只用到索引name,age和position用于排序,无Using filesort。(因为索引的创建顺序是name,age,position,在name相同的情况下,先根据age排序,再根据是position排序符合索引树的顺序,所以走了索引)
案例四
select * from employees where name = 'LiLei' order by position,age;
那么如果把排序字段的顺序换一下,还会不会走索引呢???
可以使用Explain来看下:
分析如下:
和案例三中explain的执行结果一样,但是出现了Using filesort,因为索引的创建顺序为 name,age,position,但是排序的时候age和position颠倒位置了。
那么有小伙伴可能会有疑问:为什么where后面的条件不按照索引创建的顺序出现,SQL优化的时候,会优化顺序,但是order by确不能优化成索引的顺序呢???
这个问题其实很简单,大家可以想一下:where后面的条件,不管怎么交换位置,不会影响到查询的结果,但是order by交换位置的话就是影响到最终的排序结果,有多个字段排序,会先按照第一个字段排,第一个字段相同排不了序的会按照第二个字段排。。。以此类推,如果SQL帮我们优化了order by的顺序,结果就不是我们想要的排列顺序了,所以MySQL底层就没有优化。
因此需要注意:在使用order by的时候,必须按照索引创建的顺序出现。
案例五
select * from employees where name = 'LiLei' and age = 18 order by position,age;
如果,where后面增加一个age条件,会走索引吗???
可以使用Explain来看下:
分析如下:
在Extra中并未出现Using filesort,因为age为常量,在排序中被优化(相当于age排序并没有用,因为所有的数据age都为18),所以索引未颠倒, 不会出现Using filesort。
案例六
select * from employees where name = 'LiLei' order by age asc ,position desc;
先思考下一个字段升序,一个字段降序,会不会走索引??
可以使用Explain来看下:
分析如下:
虽然排序的字段列与索引顺序一样,且order by默认升序,这里position desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
案例七
select * from employees where name in('LiLei','Hanmeimei') order by age,position;
如果把等值找换成范围查找,还会不会走索引呢??
使用Explain来看下结果:
in很有可能不走索引,name列虽然有索引但是不一定走,有可能走,也有可能不走,具体为什么走或者为什么不走可以使用trace工具来查一下,看查询成本cost是多少,具体可前往MySQL是如何选择索引的?查看
in类似于范围查找,而第一个字段不能确定,是按照范围查找,查出的数据不一定是按照索引树的顺序,再按照剩余字段的去排序,根据索引是排不了的。
对于in,一般来说主键而且是整型大多数情况下是会走索引的,但是如果in里面的元素太多,也不会走索引
如果是非主键索引,一般都不会走。具体走不走索引是有很多因素的,结果会有差别。
案例八
select * from employees where name > 'a' order by name;
先思考下像这种情况会走索引吗???
使用explain来查看下结果
这种情况不一定走索引,按照我们常规的理解是会走索引的,但是最终MySQL优化器决定是否走索引是有各种因素影响的。
可以使用覆盖索引优化:
优化总结
1、MySQL支持两种方式的排序filesort和index。
Using index是指MySQL扫描索引本身完成排序,index 效率高,filesort效率低。
2、order by满足两种情况会使用Using index。
1) order by语句使用索引最左前列。
2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则。
4、如果order by的条件不在索引列上,就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最左前缀法则。
对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意,where高于having,能写在where中 的限定条件就不要去having限定了。
ps:备注
在上面的案例中,我们看到了有index排序和filesort排序两种
index排序是很快的,大家都知道index排序实际上就是在索引树上去排,有B+ Tree的算法,查找是非常快的。
filesort实际上是文件排序,相当于在磁盘上进行排序,逐行去扫描,然后比较大小排序,性能是非常低的
如果到这里小伙伴们对filesort感兴趣的话,可前往Using filesort文件排序原理详解查看