MySQL Explain 字段详解

Explain 工具介绍

Explain 一般被称为解释器，通过 Explain 工具，我们能分析我们使用的查询语句或是结构的性能瓶颈，它提供 MySQL 如何执行语句的信息。

使用语法：

explain [extended|partition] select

在 select 关键字前加 explain 关键字，MySQL 会返回该查询的执行计划不是执行这条 SQL。

explain 分析示例

创建表语句

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`(
`id` INT(11) NOT NULL,
`name` VARCHAR(45) DEFAULT NULL,
`gender` CHAR(1) DEFAULT 0,
PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`,`gender`) VALUES(1,'a',0),(2,'b',1),(3,'c',0);

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
`id` INT(11) NOT NULL,
`name` VARCHAR(45) DEFAULT NULL,
`subject` VARCHAR(20) DEFAULT 0,
PRIMARY KEY(`id`),
KEY `idx_name` (`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` (`id`, `name`,`subject`) VALUES(1,'t1',"语文"),(2,'t2',"数学"),(3,'t3',"体育");

DROP TABLE IF EXISTS `teacher_user`;
CREATE TABLE `teacher_user`(
`id` INT(11) NOT NULL,
`teacher_id` INT(11) NOT NULL,
`user_id` INT(11) NOT NULL,
PRIMARY KEY(`id`),
KEY `idx_teacher_user_id` (`teacher_id`,`user_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `teacher_user` (`id`, `teacher_id`,`user_id`) VALUES(1,1,1),(2,3,2),(3,1,2);

使用 explain 工具

EXPLAIN SELECT * FROM USER;

简单使用 explain 工具

Explain 的两个变种

Explain extended：会在explain的基础上额外提供一些查询优化的信息。紧随其后通过show warnings 命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外的还有 filtered 列，它显示的是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表）。
explain partitions：显示查询会访问的分区，输出中增加 partitions 字段。

注意：如果你用的是 MySQL 5.6 ，这两个命令可以用，MySQL 8已经不支持了，使用会报错，不过他们将这两个字段的输出已经整合在默认的 explain 中了，直接后接show warnings就可以看到重构后的命令了。

EXPLAIN SELECT * FROM USER WHERE id=1;
SHOW WARNINGS;

在这里插入图片描述

Explain 字段详解

列	含义
id	select 的序列号，有几个 select 就有几个id，id越大，越先执行
select_type	表示语句是简单查询还是复杂查询，该列的值有：simple、primairy、subquery、derived、union
table	显示 explain 语句正在访问的表
partitions	如果查询是基于分区表的话，会显示查询将要访问的分区
type	关联的类型，也就是 MySQL 决定如何查找表中的行，查找数据行记录的大概范围
possible_keys	可能使用的索引
key	MySQL 实际采用了哪个索引来优化对该表的访问
key_len	索引里使用的字节数，通过该列的值可以算出具体使用了索引的哪些列
ref	在 key 列记录的索引中，表查找值所用到的列或常量
rows	MySQL预估要读取并检测的行数，不是结果集中的行数
filtered	百分比的值，根据 rows*filtered/100 可以估算出将要和 explain中前一个表进行连接的行数
Extra	显示额外的信息

详细描述每一列的含义

id 列

id 列的值显示的是语句的执行顺序，id 列越大执行优先级越高，id 相同则从上往下执行，id 为 NULL 最后执行。

select_type 列

simple：简单查询。查询不包含子查询和 union。
EXPLAIN SELECT * FROM USER WHERE id=1;
primary：复杂查询中最外层的 select。
subquery：包含在 select 中的子查询（不在 from 子句中）。
derived：包含在 from 子句中的子查询。MySLQL 会将结果放在一个临时表中，也称为派生表。

SET SESSION optimizer_switch='derived_merge=off'; 关闭对衍生表的合并
EXPLAIN SELECT (SELECT 1 FROM teacher WHERE id = 1) FROM (SELECT * FROM USER WHERE id = 1) der;

在这里插入图片描述

union：在 union 中的第二个和以后的查询会被标为 union 类型。
union result：从 union 构建的临时表检索结果的查询类型。

EXPLAIN SELECT id FROM teacher UNION SELECT id FROM teacher;

在这里插入图片描述

table 列

这列表示 explain 的一行正在访问哪个表，当 from 子句中有子查询时，table 列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>,1 和 2 表示参与 union 的 select 行 id。

partitions 列

如果查询的是基于分区的表，该字段显示查询将会访问的分区。

type 列

这一列表示关联类型或访问类型，即 MySQL 决定如何查找表中的行，查找数据记录的大概范围。
最优到最差：

system > const > eq_ref > ref > range > index > all

一般来讲，保证查询达到 range 级别，最好达到 ref
NULL：MySQL 能在优化阶段分解查询语句，在执行阶段不需访问表或索引。比如：

EXPLAIN SELECT MAX(id) FROM teacher

在这里插入图片描述
system：该表只有一行，是 const 的特例。
const：该表最多有一个匹配的行，MySQL 能对查询的某部分进行优化并将其转化成一个常量，因为只有一个匹配的行，所以速度非常快。

EXPLAIN SELECT * FROM (SELECT * FROM teacher WHERE id=1) tmp;

在这里插入图片描述
eq_ref：primary key 或 unique key 索引的所有部分被连接使用，最多只会返回一条符合条件的记录。

EXPLAIN SELECT * FROM teacher INNER JOIN teacher_user WHERE teacher.`id`=teacher_user.`teacher_id`;

在这里插入图片描述
ref：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值比较，可能会得到多个结果。

简单查询，name 是普通索引（非唯一索引）

EXPLAIN SELECT * FROM teacher WHERE NAME='t1';

在这里插入图片描述
关联表查询，idx_teacher_user_id 是 teacher_id 和 user_id 的联合索引，使用左边前缀 teacher_id 进行查询。

EXPLAIN SELECT teacher_id FROM teacher LEFT JOIN teacher_user ON teacher.id=teacher_user.teacher_id;

在这里插入图片描述
range：范围扫描通常出现在 in，between，>，<，>=等操作中，使用一个索引来检查给定范围的行。

EXPLAIN SELECT * FROM teacher WHERE id > 1;

在这里插入图片描述
index：扫描全索引就能拿到结果，一般是扫描某个二级索引，对二级索引的叶子节点进行遍历和扫描，所以速度较慢，而二级索引一般比较小，所以比 ALL 快。

EXPLAIN SELECT NAME FROM teacher;

在这里插入图片描述
ALL：全表扫描，扫描聚簇索引的所有叶子节点，通常这种情况需要增加索引进行优化。

EXPLAIN SELECT * FROM teacher;

在这里插入图片描述

possible_keys 列

这列显示的是此次查询可能用到的索引，一个或者多个，有时显示的是 NULL 值，是因为 MySQL 判断表中数据不多，不需要使用索引查询，选择全表查询。

如果该列是NULL，则没用相关索引。这种情况下，可以通过检查 where 子句看是否可以建立一个合适的索引来提高查询性能，再看 explain 的情况。覆盖索引查询的情况该列也为 NULL，但依然进行索引查询。

EXPLAIN SELECT NAME FROM teacher;

在这里插入图片描述

key 列

显示的是 MySQL 实际使用的索引。如果没有使用索引，则该列是 NULL，如果强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 force index、ignore index。

EXPLAIN SELECT NAME FROM teacher IGNORE INDEX(idx_name);

在这里插入图片描述

key_len 列

这一列显示了 MySQL 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。它显示的是索引的最大长度，而不是实际使用长度。

EXPLAIN SELECT * FROM teacher_user WHERE teacher_id=1;

在这里插入图片描述
key_len 计算规则如下：

字符串，char(n) 和 varchar(n)，n 代表字符数，不是字节数，utf-8 的一个字母或数字占用 1 个字节，一个汉字占用 3 个字节
- char(n)：存汉字，长度为 3n 字节
- varchar(n)：存储汉字，长度为 3n + 2 字节，多的 2 字节用来存储字符串长度
数值类型
- tinyint：1 字节
- smallint：2 字节
- int：4 字节
- bigint：8 字节
时间类型
- date：3 字节
- timestamp：4 字节
- datetime：8 字节
如果字段允许为 NULL，需要 1 字节存储 NULL

ref 列

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有 const（常量），字段名（例：teacher.id）

rows 列

这一列是 MySQOL 预估要读取并检测的行数，注意这个不是结果集里的行数。

filtered 列

该列是一个百分比的值，根据 rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数。

Extra 列

常见的值如下：

Using index：使用覆盖索引

EXPLAIN SELECT teacher_id FROM teacher_user WHERE teacher_id=1;

在这里插入图片描述
使用了联合索引 idx_teacher_user_id，覆盖索引查询索引覆盖的列，extra 列中显示 Using index。

Using where：使用 where 语句来处理结果并且查询的列没被索引覆盖。

EXPLAIN SELECT * FROM teacher WHERE SUBJECT='语文';

在这里插入图片描述

Using index condition：查询的列不完全被索引覆盖，where 条件中是一个前导列的范围。
Using temporary：MySQL 需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

EXPLAIN SELECT DISTINCT SUBJECT FROM teacher;

在这里插入图片描述

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是需要考虑索引进行优化。
Select tables optimized away：使用某些聚合函数来访问存在索引的某个字段。

总结

本文在结合查询例子的基础上对 Explain 工具查询的列进行讲解，很多内容都涉及到了，写到这也就差不多了，想要更详细的学习 Explain，可以去官网链接: MySQL 8 参考手册查看更详细的解释。