MySQL执行计划

时间:2022-05-28 ClassicalRain 人气:0

前言

项目开发中，性能是我们比较关注的问题，特别是数据库的性能；作为一个开发，经常和SQL语句打交道，想要写出合格的SQL语句，我们需要了解SQL语句在数据库中是如何扫描表、如何使用索引的；

MySQL提供explain/desc命令输出执行计划，我们通过执行计划优化SQL语句。

下面我们以MySQL5.7为例了解一下执行计划：

注：文中涉及到的表结构、sql语句只是为了理解explain/desc执行计划，有不合理之处勿喷

explain/desc 用法

只需要在我们的查询语句前加explain/desc即可

准备数据表

-- 创建user表
create table user(
    id int,
    name varchar(20),
    role_id int,
    primary key(id)
)engine=innodb default charset=utf8;
-- 创建role表
create table role(
    id int,
    name varchar(20),
    primary key(id)
)engine=innodb default charset=utf8;

查询，执行计划

 explain select * from user;

执行计划输出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra，这些内容有什么意义，下面简单介绍一下

explain/desc 输出详解

一、id ，select 查询序列号

1 id相同，从上往下一次执行；

-- 左关联
explain select * from user a left join user b on a.id=b.id;
-- 右关联
explain select * from user a right join user b on a.id=b.id;

通过left join 和 right join 验证；id一样（注意执行计划的table列），left join 先扫描a表，再扫描b表；rightjoin 先扫描b表，再扫描a表

2 id不同，id越大优先级越高，越先被执行

desc select * from user where role_id=(select id from role where name='开发');

我们编写查询角色为开发的用户；可以知道先查询角色name为开发角色id，查询序列号为2；再根据角色id查询用户，查询序列号为1；

二、select_type，查询语句类型

(1)SIMPLE(简单SELECT,不使用UNION或子查询等)

explain select * from user;

(2)PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

 desc select * from user where role_id=(select id from role where name='开发');

(3)UNION(UNION中的第二个或后面的SELECT语句)

desc select * from user where name='Java' union select * from user where role_id=1;

(4)DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)

desc select * from user a 
     where id in ( 
         select b.id from user b where b.id=a.id union 
         select c.id from role c where c.id=a.role_id 
     );

(5)UNION RESULT(UNION的结果)

desc select * from user where name='Java' union select * from user where role_id=1;

(6)SUBQUERY(子查询中的第一个SELECT)

 desc select * from user where role_id=(select id from role where name='开发');

(7)DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)

desc select * from user where role_id = ( select id from role where id=user.id );

(8)DERIVED(派生/衍生表的SELECT, FROM子句的子查询)

desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;

(9) MATERIALIZED(物化子查询) 在SQL执行过程中，第一次需要子查询结果时执行子查询并将子查询的结果保存为临时表，后续对子查询结果集的访问将直接通过临时表获得。

(10)UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

(11)UNCACHEABLE UNION(UNION查询的结果不能被缓存)

三、table，查询涉及的表或衍生表

table分别user、role表

四、partitions查询涉及到的分区

创建分区表，

-- 创建分区表，
-- 按照id分区，id<100 p0分区，其他p1分区
create table user_partitions (id int auto_increment,
    name varchar(12),primary key(id))
    partition by range(id)(
        partition p0 values less than(100),
        partition p1 values less than maxvalue
    );

desc select * from user_partitions where id>200;

查询id大于200（200>100，p1分区）的记录，查看执行计划，partitions是p1，符合我们的分区规则

五、type提供了判断查询是否高效的重要依据依据

通过type字段, 我们判断此次查询是全表扫描还是索引扫描等，下面简单介绍一下常用的type；

（1）system: 表中只有一条数据，相当于系统表；这个类型是特殊的const类型；

（2）const:主键或者唯一索引的常量查询，表格最多只有1行记录符合查询，通常const使用到主键或者唯一索引进行定值查询。

主键

-- 创建user表
create table user(id int primary key, name varchar(20), role_id int );
-- 插入一条记录
insert into user values (1, 'a', 1 );
-- 按id查询
desc select * from user where id=1;
-- 按role_id查询
desc select * from user where role_id=1;

分别查看按id和按role_id查询的执行计划；发现按主键id查询，执行计划type为const

将主键设置为id和role_id

-- 删除主键
alter table user drop primary key;
-- 设置主键id,role_id
alter table user add primary key(id,role_id);
-- 按照部分主键查询
desc select * from user where id=1;
-- 按照部分主键查询
desc select * from user where role_id=1;
-- 按照全部主键查询
desc select * from user where id=1 and role_id=1;

发现只有按照全部主键查询，执行计划type为const

唯一索引

-- 删除主键
alter table user drop primary key;
-- 设置主键
alter table user add primary key(id);
-- 设置role_id为唯一索引
alter table user add unique key uk_role(role_id);
-- 按照唯一索引查询
desc select * from user where role_id=1;

发现按role_id唯一索引查询；执行计划type为const

普通索引

-- 将role_id设置成普通索引
-- 删除唯一索引
alter table user drop index uk_role;
-- 设置普通索引
alter table user add index index_role(role_id);
-- 按照普通索引查询
desc select * from user where role_id=1;

发现按role_id普通索引查询；执行计划type为ref

const用于主键或唯一索引查询；将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时使用；与索引类型有关。

（3）eq_ref: 除了system和const类型之外,效率最高的连接类型；唯一索引扫描，对于每个索引键，表中只有一条记录与之对应；常用于主键或唯一索引扫描

准备数据

-- 创建teacher表
create table teacher( id int primary key, name varchar(20), tc_id int );
-- 插入3条数据
insert into teacher values (1,'a',1),(2,'b',2),(3,'c',3);
-- 创建teacher_card表
create table teacher_card( id int primary key, remark varchar(20) );
-- 插入2条数据
insert into teacher_card values (1,'aa'),(2,'bb');
-- 关联查询，执行计划
desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

执行计划

根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：

先查询t表就是teacher表中name字段为a的记录，由于name字段没有索引，所以全表扫描（type:ALL），一共有3条记录，扫描了3行（rows:3）,1条符合条件（filtered:33.33 1/3）；

再查询tc即teacher_card表使用主键和之前的t.tc_id关联；由于是关联查询，并且是通过唯一索引（主键）进行查询，仅能返回1或0条记录，所以type为eq_ref。

 -- 删除teacher_card主键
 alter table teacher_card drop primary key;
 -- 这是teacher_card.id为唯一索引
 alter table teacher_card add unique key ui_id(id);
 -- 关联查询，执行计划
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

分析结果，将teacher_card的id设置为唯一索引，type为eq_ref；满足仅能返回1或0条记录。

 -- 删除teacher_card唯一索引
 alter table teacher_card drop index ui_id;
 -- 设置teacher_card.id为普通索引
 alter table teacher_card add index index_id(id);
 -- 关联查询，执行计划
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

分析结果，将teacher_card的id设置为普通索引，type为ref；不满足仅能返回1或0条记录。

equ_ref用于唯一索引查询，对每个索引键，表中只有一条或零条记录与之匹配。

（4）ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询（换句话说，连接不能基于键值选择单行,可能是多行）。

 -- teacher.tc_id无索引，执行计划
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where tc.remark='aa';
 -- 设置teacher.tc_id为普通索引
 alter table teacher add index index_tcid(tc_id);
 -- teacher.tc_id有索引，执行计划
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where tc.remark='aa';

先查询tc表就是teacher_card表中remark字段为aa的记录，由于remark字段没有索引，所以全表扫描（type:ALL），一共有2条记录，扫描了2行（rows:2）,1条符合条件（filtered:50,1/2）；

tc_id无索引再查询t即teacher表使用tc_id和之前的tc.id关联；由于是关联查询，不是索引,全表扫描，所以type为ALL。

tc_id有索引再查询t即teacher表使用tc_id和之前的tc.id关联；由于是关联查询，索引扫描，能返回0或1或多条记录，所以type为ref。

（5）range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

 desc select * from teacher where id>2;
 desc select * from teacher where id in (1,2,3);

（6）index: 扫描索引树

如果索引是复合索引，并且复合索引列满足select所需的所有数据，则仅扫描索引树。在这种情况下，Extra为Using index。仅索引扫描通常比ALL索引的大小通常小于表数据更快。

索引列不满足select所需的所有数据，此时需要回表扫描；按索引顺序查找数据行。Uses index没有出现在Extra列中。

 -- 查看teacher表索引
 show index from teacher;
 -- 查询tc_id，执行计划
 desc select tc_id from teacher;
 -- 按tc_id索引分组，执行计划
 desc select name from teacher group by tc_id;

查询tc_id，扫描索引树，type为index，Extra为Using index；

按tc_id分组，全表扫描，以按索引顺序查找数据行。

（7）ALL: 全表扫描,没有任何索引可以使用时。这是最差的情况，应该避免。

 -- 查看teacher表索引
 show index from teacher;
 desc select * from teacher where name='a';

由于name字段不存在索引，type:ALL全表扫描；可通过对name字段设置索引进行优化。

六、possible_keys：指示MySQL可以从中选择查找此表中的行的索引。

七、key：MySQL查询实际使用到的索引。

-- 创建course表
create table course(id int primary key,name varchar(20),t_id int,key index_name(name),key index_tid(t_id));
-- 插入数据
insert into course values (1,'Java',1), (2,'Python',2);
-- 查询1
desc select * from course where name='Java' or t_id=1;
-- 查询2
desc select * from course where name='Java';

查看执行计划

查询1，查询name为Java或t_id为1的记录；可能用到的索引possible_keys为index_name，index_tid；实际用到的索引key为NULL

查询2，查询name为Java；可能用到的索引possible_keys为index_name；实际用到的索引key为index_name

八、key_len：表示索引中使用的字节数（只计算利用索引作为index key的索引长度，不包括用于group by/order by的索引长度）

一般地，key_len 等于索引列类型字节长度，例如int类型为4 bytes，bigint为8 bytes；
如果是字符串类型，还需要同时考虑字符集因素，例如utf8字符集1个字符占3个字节，gbk字符集1个字符占2个字节
若该列类型定义时允许NULL，其key_len还需要再加 1 bytes
若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引也被视为动态列类型），其key_len还需要再加 2 bytes

字符集会影响索引长度、数据的存储空间，为列选择合适的字符集；变长字段需要额外的2个字节，固定长度字段不需要额外的字节。而null都需要1个字节的额外空间，所以以前有个说法：索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外一个字节的存储空间。

-- key_len的长度计算公式：

-- varchar(len)变长字段且允许NULL      : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)
-- varchar(len)变长字段且不允许NULL    : len*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

-- char(len)固定字段且允许NULL         : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
-- char(len)固定字段且不允许NULL       : len*(Character Set：utf8=3,gbk=2,latin1=1)

下面验证一下结论：

-- 创建user_info表
create table user_info( id int comment '主键',
    name varchar(10) character set utf8 not null comment '姓名',
    alias_name varchar(10) character set utf8 comment '姓名',
    role_id int comment '角色id',
    remark varchar(10) character set gbk not null comment '备注',
    primary key(id),
    key index_name(name),
    key index_alias(alias_name),
    key index_role(role_id),
    key index_remark(remark)
)engine=innodb;
-- 插入数据
insert into user_info values (1,'a','aa',1,'aaa');
-- 按主键查询
desc select * from user_info where id=1;
-- 按索引role_id查询
desc select * from user_info where role_id=1;

按照主键id查询possible_keys为primary，实际用到的索引key为primary，key_len为4；

按照索引role_id查询possible_keys为index_role，实际用到的索引key为index_role，key_len为5；

分析结果：按照role_id比按照id（均为int类型）的key_len大5-4=1，因为role_id可以为null，需要一个标志位；

 -- 按照name查询 varchar(10) not null utf8 一个字符占3个字节 10*3+2(变长)=32
 desc select * from user_info where name='a';
 -- 按照alias_name查询 varchar(10) utf8 一个字符占3个字节 10*3+2(变长)+1(null标志位)=33
 desc select * from user_info where alias_name='aa';

按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为32=10*3+2(变长)；

按照alias_name查询possible_keys为index_alias，实际用到的索引key为index_alias，key_len为33=10*3+2(变长)+1(null标志位)；

分析结果：name与remark均为变长且字符集一致，remark可以为null，33-32=1多占一个标志位；

 -- 按照name查询 varchar(10) not null utf8 一个字符占3个字节 10*3+2(变长)=32
 desc select * from user_info where name='a';
 -- 按照remark查询 varchar(10) not null gbk 一个字符占2个字节 10*2+2(变长)=22
 desc select * from user_info where remark='aaa';

按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为32=10*3(utf8一个字符3个字节)+2(变长)；

按照remark查询possible_keys为index_remark，实际用到的索引key为index_remark，key_len为22=10*2(gbk一个字符2个字节)+2(变长)；

分析结果：name与remark均为变长但字符集不一致，分别为utf8与gbk；符合公式；

 -- 将name修改为char(10) 定长 character set utf8 not null
 alter table user_info modify name char(10)  character set utf8 not null;
 -- 按照name查询 varchar(10) not null utf8 一个字符占3个字节 10*3=30
 desc select * from user_info where name='a';

按照name查询possible_keys为index_name，实际用到的索引key为index_name，key_len为30；

因为将name修改为char(10) 定长 character set utf8 not null，10*3=30；符合公式

九、ref：显示该表的索引字段关联了哪张表的哪个字段

desc select * from user,role where user.role_id=role.id;

通过执行计划可知，role表执行计划ref为study.user.role_id；说明role.id关联user.role_id；

十、rows：根据表统计信息及选用情况，大致估算出找到所需的记录或所需读取的行数，数值越小越好

十一、filtered：返回结果的行数占读取行数的百分比，值越大越好

-- 查看teacher数据
select * from teacher;
-- 查看teacher_card数据
select * from teacher_card;
-- 查询语句
select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';
-- 执行计划
desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

根据上面的知识；可知id相同，由上至下依次执行，分析结果可知：

再查询tc即teacher_card表使用主键和之前的t.tc_id关联；扫描索引（type:ref），返回1条记录，最终返回1条记录，（filtered:100 1/1）。

十二、extra：包含不适合在其他列中显示但十分重要的额外信息。常见的值如下

use filesort：MySQL会对数据使用非索引列进行排序，而不是按照索引顺序进行读取；若出现改值，应优化索引

-- 查看user索引
show index from user;
-- 查询name并排序
desc select name from user order by name;
-- 为name列设置索引，优化
alter table user add index index_name(name);
-- 查询name并排序
desc select name from user order by name;

use temporary：使用临时表保存中间结果，比如，MySQL在对查询结果排序时使用临时表，常见于order by和group by；若出现改值，应优化索引

use index：表示select操作使用了索引覆盖，避免回表访问数据行，效率不错

use where：where子句用于限制哪一行

-- 创建student表
create  table student(
    id int,
    first_name varchar(10),
    last_name varchar(10),
    primary key(id),
    key index_first(first_name)
)engine=innodb default charset=utf8;
-- 插入数据
insert into student values (1,'a','b');
-- 按照first_name查找
desc select first_name,last_name from student where first_name='a';
-- 设置first_name,last_name复合索引
alter table student drop index index_first;
alter table student add index index_name(first_name,last_name);
-- 按照first_name查找
desc select first_name,last_name from student where first_name='a';

分析结果：

当设置first_name为普通索引(单列索引)，按照first_name查询；type:ref、possible_keys:indes_first、key:indes_first、extra:null，用到索引；

当设置first_name，last_name为复合索引(联合索引)，按照first_name查询；type:ref、possible_keys:indes_name、key:indes_name、extra:Using index；type:ref用到索引，因为是复合索引不需要回表扫描，extra:Using index索引覆盖；注意此时key_len为33=10*3(utf8)+2(变长)+1(null标志位)，用到了复合索引的一部分即first_name

当设置first_name，last_name为复合索引(联合索引)，按照last_name查询；type:index、possible_keys:null、key:indes_name、extra:Using where，Using index；type:index而不是ref，扫描索引树，复合索引的最左原则；此时key_len为66=10*3(utf8)+2(变长)+1(null)+10*3(utf8)+2(变长)+1(null标志位)；Using where应where子句进行限制

小结：

根据MySQL执行计划的输出，分析索引使用情况、扫描的行数可以预估查询效率；进而可以重构SQL语句、调整索引，提升查询效率。

本文只是简单介绍一下MySQL执行计划，想全面深入了解MySQL,可优先阅读MySQL官方手册。

总结

加载全部内容