MySQL执行计划分析

大家好，我是anyux。本文介绍MySQL执行计划分析。

作用

通过explain或desc命令将优化器选择后的执行计划截取出来，便于管理和判断语句的执行效率

获取执行计划

desc SQL语句

explain SQL语句

获取执行计划后选择的方法

desc select * from `test`.`t100w` where k2='rsEF';

使用explain获取执行计划

explain select * from `test`.`t100w` where k2='rsEF';

获取执行计划时，SQL语句并没有真正运行，对性能没有影响。desc 和 explain对于获取执行计划的结果相同的

获取内容如下所示：

分析执行计划

首先要查看table对应的表，在真实环境中，可能存在多个联合查询，分析时需要明确是哪张表查询出现性能瓶颈

然后是查看type对应的值，第三个是possible-keys,第四个key,第五个key_len,第六个Extra

type指的是查询的类型，分为全表扫描和索引扫描。全表扫描是低效的。索引扫描又分为几个级别，包含辅助索引扫描和聚集索引扫描，各个级别不一样，性能也不一样

全表扫描对应的执行计划是：ALL。全表扫描只有一种

索引扫描对应的执行计划分别为：index,range,ref,eq_ref,const(system),NULL

索引扫描按上面的排序，从左到右性能依次变好

index：全索引扫描

演示index索引扫描

use world;

desc city;

desc select id from city;

下面图中type值为index，意味着select id from city; 这条语句执行计划是index索引扫描

range:索引范围扫描

range索引范围扫描包含的符号有：>(大于)，=(大于等于),

只要在SQL语句中出现以上关键字或符号的，就代表着会使用range索引范围扫描

演示1:range索引扫描

range表示范围扫描

desc select * from city where id>2000;

desc select * from city where countrycode like 'CH%';

下面图中type值为range，意味着select * from city where id>2000; 这条语句执行计划是range:索引范围扫描

演示2:range索引扫描

range表示范围扫描

desc select * from city where countrycode='CHN' or countrycode='USA'\G

desc select * from city where countrycode in ('CHN','USA')\G

下面图中type值为range，意味着select * from city where countrycode='CHN' or countrycode='USA'; 这条语句执行计划是range:索引范围扫描

注意：在同一数量级下，例如在千万条记录中获取10条数据，演示1的SQL语句性能优于演示2的SQL语句，原因是MySQL5.7默认使用B*Tree，在枝结点上存在双向指针，不需要再向下一结点查询，可以做到很快的响应处理，对于演示1中大于2000，能够快速响应，而像like 'CH%',字符存储也是连续的，也能够快速响应。而对于演示2的SQL语句只能使用普通BTree的查找算法，对于每个值都需要重新遍历叶子结点，所以性能不是特别好。

像演示2这种情况一般需要改写，改写为 union all语句

desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

改写后，type值改为了ref,明显ref比较range范围索引性能更高

ref：辅助索引等值查询

ref代表着索引变化为等值的，相比range而言，范围缩小了，查询效率也更高了

演示ref索引扫描

desc select * from city where countrycode='CHN';

下面图中type值为ref，意味着select * from city where countrycode='CHN';这条语句执行计划是ref索引扫描

eq_ref：子表使用主键列或唯一列作为连接条件

在多表连接时，子表使用主键列或唯一列作为连接条件。在使用join连接多表时，说过驱动表和子表。驱动表就是from后面紧跟着的表，一般使用记录行少的表作为驱动表。右边的表都属于子表。

A join B on A.id=B.aid

A是驱动表，B是子表

当B.aid是主键或唯一列的时候，使用的是eq_ref方式查询。原因是驱动表是不使用索引的，而是使用全表扫描的方式，从第二张表是可以使用索引的。一般地开发人员会在设计数据库时，会有预想到数据量增加及多表联查的情况

演示:eq_ref索引扫描

desc select CITY.name ,COU.name,CITY.population from city as CITY join country as COU on CITY.countrycode=COU.code where CITY.population

下面图中type值为eq_ref，意味着select CITY.name ,COU.name,CITY.population from city as CITY join country as COU on CITY.countrycode=COU.code where CITY.population

const(system):主键或唯一键的等值查询

使用此种索引返回的记录均为1，查询效果相同。但是性能存在一定差距，使用主键等值查询会好一些。唯一索引是辅助索引，还是要回到原表查询id,效果依然比辅助索引查询来得要好

演示1:const(system)索引扫描

desc select * from city where id=100;

下面图中type值为const，意味着select * from city where id=100;这条语句执行计划是const索引扫描

演示2:const(system)索引扫描

为构造tmp_db，需要修改tmp_student表结构

create database tmp_db charset utf8mb4;

use tmp_db;

create table tmp_student(id int not null primary key auto_increment,name varchar(20) not null,intime datetime not null,tel varchar(20) not null unique);

insert into tmp_student(name,intime,tel) values('zs',now(),'110'),('ls',now(),'120'),('ww',now(),'130');

desc select * from tmp_student where tel='120';

下面图中type值为const，意味着select * from tmp_student where tel='120';这条语句执行计划是const索引扫描

NULL:不工作时，时间最短

desc select * from tmp_student where tel='1';

下面图中type值为NULL，意味着select * from tmp_student where tel='1';这条语句执行计划是NULL,即不工作

type值为NULL代表的是不需要回数据行查询记录，原因是表中不存在要查询的记录

提示：

对于辅助索引来说!=(不等于),(不等于)，not in(),使用的是全表扫描,不走索引

对于主键索引来说!=(不等于),(不等于)，not in(),使用的是range索引范围扫描

对于like ，如果%(百分号)在前面，使用的是全表扫描，不走索引

如果like给定的条件太少导致可选范围过大，使用全表扫描，不走索引