MySql（六）：Explain详解

2022-11-19 18:41:35

Explain 命令

模拟优化器执行Sql语句，分析查询语句或结构的性能瓶颈，在select查询前加上explain关键词，MySql会在本次查询上设置一个标记，只返回执行计划的信息，而不是执行这条Sql语句。

注意：如果from中包含子查询，仍会执行该子查询，将结果放入临时表中。

Explain的两个变种

在早期的MySql版本(5.7之前)中explain的filtered和partitions列，是需要通过下面两个命令查看。

explain extended：

会在explain基础上额外提供一些查询优化的信息。

explain紧随其后使用show warnings命令可以得到优化后的查询语句，从而看出优化器优化了什么；show warnings优化后的语句为mysql内部执行的语句(伪Sql)，不一定在数据库中能执行。

额外还有filtered列，是一个半分比的值，rows*filtered/100可以估算出将要和explain中前一表进行连接的行数(前一个表指explain中的id值比当前表id值小的表)。

explain partitions:

相比explain多了partitions字段，查过查询是基于分区表的话，会显示查询将现实的分区。

数据准备，先创建如下表：

create table `actor`(
    `id` int(11) NOT NULL,
    `name` varchar(45) DEFAULT NULL,
    `update_time` datetime DEFAULT NULL,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// insert
insert into `actor` (id, name) values (1, 'edward1');

create table `film`(
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(10) DEFAULT NULL,
    PRIMARY KEY(`id`),
    KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// 插入数据
insert into `film` (name) values ('film1');
insert into `film` (name) values ('film2');
insert into `film` (name) values ('film3');
insert into `film` (name) values ('film4');

create table `film_actor`(
    `id` int(11) NOT NULL,
    `film_id` int(11) NOT NULL,
    `actor_id` int(11) NOT NULL,
    `remark` varchar(255) DEFAULT NULL,
    PRIMARY KEY(`id`),
    KEY `idx_film_actor_id` (`film_id`, `actor_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

// 插入
insert into film_actor (id, film_id, actor_id, remark) values (1, 1, 1, '科幻大片');

Explain中的列

官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

1、id列：

id列的序号是select的序号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的。

id列越大执行优先级越高，id不一定唯一，相同则从上往下执行，id为NULL最后执行。

2、select_type

查询类型，select_type表示对应行是简单还是复杂查询。

simple：简单查询，查询不包含子查询和union(关联)；

explain select * from film where id = 2;

primary：复杂查询中的最外层select；

subquery：包含在select中的子查询(不在from子句中)；

derived：包含在from子句中的子查询/衍生查询。MySql会将结果放在一个临时表中，也称为派生表(derived的英文含义)，但是5.7会对养生表查询进行合并优化，所以需要关闭优化才会在执行计划中看到衍生查询。

用下面例子了解primary、subquery、derived类型：

//关闭mysql5.7新特性对衍生表的合并优化
set session optimizer_switch = 'derived_merge=off'; 
explain select (select 1 from `actor` where id = 1) from (select * from `film` where id = 1) der;

// 执行计划：
// 3、derived：(select * from `film` where id = 1) der 生成派生表<derived3>
// 2、subquery：(select 1 from `actor` where id = 1) 子查询
// 1、primary：复杂查询最外层select，它查询的table为派生表<derived3>

// 还原
set session optimizer_switch = 'derived_merge=on';

union：在union中的第二个和随后的select

explain select 1 union all select 1;

3、table列

指执行计划查询的哪个表。from子句中包含衍生查询时会生成派生表(derived)，衍生表名一般为derievd+衍生查询的id。

4、partitions

指分区信息。数据库优化有分库、分表、分区；这里的分区值表的分区信息。

5、type列

表示sql查询的关联类型或访问类型，即MySql决定如何查找表中的行，查找数据行记录的大概范围。

查询效率从最优到最差：system > const > eq_ref > ref > range > index > ALL

一般查询得保证达到range级别，最好达到ref。

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或者索引。例如：在索引列选最小值，可以单独查询索引来完成，不需要在执行时访问表。

explain select min(id) from film;
// id为主键，使用min函数为找到最小值，mysql则可以直接找到主键索引叶子节点第一个即为最小主键值，
// 在执行之前的优化阶段就可以拿到结果，所以不用继续查表。

system、const：mysql能对查询的某个部分进行优化并将其转化为一个常量(可以看show warnings的结果)。指使用primary key或unique key的索引列进行常数等值查询时，所以表最多有一个匹配行，读取1次，类似常量查询，速度比较快。system是const的特例，表中只有可能一条元祖匹配时为system。(就算film中只有一条记录时，select * from film where id = 1 为const，select * from film 为ALL)

explain select * from (select * from film where id = 1) tmp;

// select * from film where id = 1 为const
// select * from derived2 为system，因为派生表的数据只可能有一条结果集

show warnings;
// 在Mysql优化阶段后的语句结果相当于常量查询，直接从dual空表查询
// 所以执行阶段之间从dual空表查询

eq_ref：两张表进行关联查询时，使用某个表的primary key或unique key索引列进行连接使用，因为主键和唯一索引最多只会返回一条符合条件的记录。这可能是const之外最好的联接类型了，简单的select查询不会出现这种type。

explain select * from `film_actor` left join `film` on film_actor.film_id = film.id;

ref：相比eq_ref，（查询条件走索引但不是唯一）不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值比较，可能会找到多个符合条件的行。

1、简单select查询，name是普通索引(非唯一索引)。

explain select * from film where name = 'film1';

2、关联表查询，idx_film_actor_id是film_actor的film_id和actor_id的联合索引，这里使用了film_actor的左边前缀film_id部分。

explain select film_id from film left join film_actor on film.id = film_actor.film_id;

range：范围扫描通常出现在in()、between、>、<、>=、<=等操作中。使用一个索引来检索给定范围的行。

explain select * from actor where id > 1;

index: 同样为全表扫描数据，一般是扫描某个二级索引(非主键索引/聚簇索引)，且能使用索引覆盖拿到结果。这种扫描不是从索引树的根节点开始查找，而是从二级索引的叶子节点遍历和扫描。虽然也是用索引，但该查询效率不高(索引的全扫描而不是从根节点匹配查找)，出现这种情况需要优化。

为什么会扫描二级索引？因为查询结果集在二级索引和主见索引中都能查到时，Mysql会选择二级索引，因为二级索引存储的数据较聚簇索引小，不包含其他数据，读取比ALL快。

explain select * from film;

explain select * from actor;

explain select id,film_id from film_actor;

explain select * from film_actor;

explain select id,actor_id from film_actor;

ALL: 即全表扫描，扫描聚簇索引(主键索引)的所有叶子节点。出现这种情况需要增加索引来进行优化。

explain select * from actor;

6、possible_key列

查询语句可能使用哪些索引来查找。

可能存在possible_key出现索引列，但是实际key为NULL的情况，这是因为表中数据量不多，mysql认为索引对查询帮助不大，选择了全表扫描。

也肯能存在possible_key为NULL，但是实际使用key为二级索引的情况，如上type=index情况。

7、key列

显示mysql实际采用哪个索引列对查询进行优化。如果没有使用索引则为NULL，如果想强制mysql使用或者忽略某些索引，在查询中可以使用force index、ignore index。

8、key_len列

显示mysql在索引列使用的字节数，也可以通过这个值算出具体使用了联合索引中的哪些列。

如：film_actor的联合索引idx_film_actor_id由film_id和actor_id两个int组成，每个int为4个字节。通过结果中的key_len=4可推断查询使用了第一个列film_id来执行索引查询。

explain select * from film_actor where film_id = 1;

key_len的计算规则：

a、字符串

char(n)：n字节长度；
varchar(n)：字符编码不同计算不同，如果是utf8，则长度为3n+2字节，加的2个字节用来存储字符串长度；不过现在数据库字符一般都使用utf8mb4，长度为4n+2字节，因为utf8不支持表情，一个表情会占4个字节。

b、数值类型

tinyint：1个字节；
smallint: 2个字节；
int：4个字节；
bigint：8个字节。

c、时间类型

date：3字节；
timestamp：4字节；
datetime：8字节。

d、如果字段允许为NULL，需要1个字节记录是否为NULL。

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来左索引。

9、ref列

显示在key列记录的索引中，查询的值所用到是常量或关联查询的列名。常见的有：const(常量)、字段名(如film.id)。

10、rows列

mysql估计要读取并检测的行数，需要扫描的行树不等于结果集返回的行树。

11、filtered列

返回的结果行树占需要读取行树的比例。Filtered列的值越大越好，Filtered列的值依赖于统计信息。

12、Extra列

展示额外的信息。常用如下：

a、Using Index：

使用索引覆盖：mysql的执行计划有用到索引，且结果集的数据能够从这个索引树中直接获取，则会使用索引覆盖。索引覆盖一般指非聚簇索引，不需要拿到非聚簇索引的主键再回表查聚簇索引里面的其他字段数据。

explain select actor_id from film_actor wher film_id = 1;

b、Using where：

使用where语句来处理结果，并且查询的列没有索引。

explain * from actor where name = 'edward1';

c、Using index condition:

查找的列不完全被索引覆盖，where条件中的列是组合索引中的前导列的范围。

explain select * from film_actor where film_id > 1;

d、Using temporary:

mysql需要创建一张临时表处理查询。出现这种情况一般需要进行索引优化。

// actor.name没有索引，需要创建临时表来distinct。
// 1、扫描所有结果，将name存放在临时表中
// 2、再更加临时表进行去重
explain select distinct name from actor;

// film.name有索引，则直接extra为Using index，不需创建临时表来distinct。
// 直接扫描索引树的叶子节点进行去重
explain select distinct name from film;

e、Using filesort:

文件排序，使用外部排序而不是索引排序，数据小时从内存中排序，否则需要在磁盘中完成排序。一般也考虑使用索引来优化，因为索引树本身就是有序的。

explain select * from actor order by name;

explain select * from film order by name;

f、Select tables optimized away:

使用某些聚合函数（如：max、min）来访问存在索引的某个字段。

码农公寓