【进阶】explain 执行计划详解

前言

MySQL 使用 explain 关键字可以模拟优化器执行 sql 语句，我们就能够知道 MySQL 会如何处理咱

们的sql，可以根据 explain 的分析结果和 MySQL 底层数据结构优化 sql。文章内容基于 MySQL

5.7.24分析，不同MySQL版本可能有差别。如果用的是MySQL 5.6.X，差别应该不会很大。

MySQL 索引底层数据结构和算法：https://blog.csdn.net/yhl_jxy/article/details/88392411

MySQL 索引优化原则：https://jpeony.blog.csdn.net/article/details/88636685

一 explain 详解

数据脚本准备，有一个 teacher（教师表），class（班级表），class_teacher（班级与对应上课老

师关系表）。这里不涉及到实际业务，只是通过实例分析 explain 相关内容。

# 教师
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(11) NOT NULL comment '教师编号',
  `teacher_name` varchar(45) DEFAULT NULL comment '教师姓名',
  `entry_time` date DEFAULT NULL comment '入职时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` (`id`, `teacher_name`, `entry_time`)
VALUES (1,'a','2010-09-22'), (2,'b','2011-10-26'), (3,'c','2013-12-25');

# 班级
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT comment '班级编号',
  `class_name` varchar(10) DEFAULT NULL comment '班级名称',
  PRIMARY KEY (`id`),
  KEY `idx_class_name` (`class_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `class` (`id`, `class_name`)
VALUES (1,'class1'),(2,'class2'),(3,'class3');

# 班级教师关系表
DROP TABLE IF EXISTS `class_teacher`;
CREATE TABLE `class_teacher` (
  `id` int(11) NOT NULL comment '主键',
  `class_id` int(11) NOT NULL comment '班级编号',
  `teacher_id` int(11) NOT NULL comment '教师编号',
  `remark` varchar(255) DEFAULT NULL comment '备注',
  PRIMARY KEY (`id`),
  KEY `idx_class_teacher_id` (`class_id`,`teacher_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `class_teacher` (`id`, `class_id`, `teacher_id`, `remark`)
VALUES (1, 1, 1, '一班的1号教师'), (2, 1, 2, '一班的2号教师'), (3, 2, 1, '二班的1号教师');

先用 explain 关键字执行下，看看能看到啥？ 

mysql> explain select * from teacher;

• id ：编号
• select_type ：查询类型
• table ：表名
• partitions ：分区
• type ：类型
• possible_keys ：预测用到的索引
• key ：实际使用的索引
• key_len ：实际使用索引的长度
• ref ：表之间的引用
• rows ：通过索引查询到的数据量
• filtered ：过滤
• Extra ：额外的信息

下面详细分析每一列的含义。

1、id 列（重要）

id 列的编号一般情况下是 select 的序列号，一般有几个 select 就有几个 id，并且 id 的顺序按

select 出现的顺序增长。

id 值越大执行优先级越高，id 值相同则从上往下执行，id 为 null 最后执行。

mysql> explain select (select id from teacher limit 1) from class;

MySQL 先执行 id 为 2 的表，即先执行子查询从 teacher 表获取一条数据，然后再执行 id 为 1

的表 class。

2、select_type 列

select_type 表示查询类型，分为以下几类查询。
1）simple

简单查询（不包含子查询，不包含关联查询，比如 join，union）。

mysql> explain select * from teacher;

2）primary

包含子查询最外层的 select。

3）subquery

包含子查询中的子查询。

mysql> explain select (select 1 from teacher where id = 1) from class;

 subquery 子查询 teacher 表，外层 select 为 primary。

4）derived

包含在 from 子句中的查询。MySQL 会将结果放在一张临时表中以供查询使用。

mysql> explain select * from (select 1) tmp;

 5）union

在 union 中第二个及之后的 select。

6）union result

从 union 临时表检索结果的 select。

mysql> explain select 1 union select 2;

select 1 为 primary。select 2 为 union。

两个的结果合并为 union result，供 select 检索。

这里也可以顺便看下 id 列，1，2，null，执行顺序 2 --> 1 --> null。

先执行 select 2，然后执行 select 1，最后执行执行从两个笛卡尔积检索数据。

3、table 列

table 为访问的表名。根据上面 id 或 select_type 实例观察，总结表名的规则。

1）from 子查询时，table 为子表名或 格式，表示当前查询依赖 id=N 的查询，

于是先执行 id=N 的查询。

mysql> explain select * from (select 1) tmp;

 在 table 列可以看到 ，表示主查询依赖于 是 DERIVED(select_type)+id，

这里就是 derived2。

2）当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行 id。

mysql> explain select 1 union select 2;

  

4、partitions 列

如果查询基于分区表，将会显示访问的是哪个区。

5、type 列（重要）

type 列表示关联类型或访问类型，MySql决定如何查找表中的行，以及查询表数据行记录的大概范

围。type常见类型从最优到最差：system > const > eq_ref > ref > range > index > ALL，

如果你的 sql 通过 explain 分析，看到 type 是All，一般是需要优化了。

一般得保证查询时能达到 range 级别，如果能做到 ref 级别，就更好了。

1）null

MySQL 优化器能够在优化阶段分解查询语句，在执行阶段就不用访问表或索引。

mysql> explain select max(id) from teacher;

2）system，const

system（忽略）：只有一条数据的系统表 或 衍生表只有一条数据的主查询才会出现，可以忽略

掉，没有太大意义。

cons（偶尔能达到）：只能查询到一条数据的 SQL，用于 primary key 或 unique索引（查询类型

与索引类型有关）。

mysql> explain select * from (select * from class where id = 1) tmp;

3）eq_ref

primary key 或 unique key 索引被连接（join）使用 ，最多只会返回一条符合条件的记录。

对于每个索引键的关联查询，返回匹配唯一行数据（有且只有1个，不能多，不能少）。

简单的select 查询不会出现这种 type。

mysql> explain select * from class_teacher left join class on class_teacher.class_id = class.id;

这里也可以看到 id 列都是 1，当 id 列值一样时，从上到下执行表。所以先执行 class_teacher

表，后执行 class 表。 

4）ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相

比较，可能会找到多个符合条件的行。

mysql> explain select * from class where class_name = 'class1';

5）range

range 使用索引列检索指定范围，where 后面是一个范围查询(between and，in ，>， <， >=)。

mysql> explain select * from teacher where id between 1 and 5;

6）index

查询全部索引中的数据，这通常比 ALL 快一些。

mysql> explain select * from class;

7）ALL

查询全表数据。没有索引，或索引失效，查询全部数据。

mysql> explain select * from teacher;

6、possible_keys 列

possible_keys 显示可能用到的索引，该列值可能为空，也可能不为空。

1）不为 null 时

当不为 null 时，会显示可能用到的索引。

2）为 null 时

当该列值为 null 时，表示不会用到索引。当出现这种情况时，看下 where 条件是否用到了索引字

段，需要分析为什么没用索引或是否需要添加索引。

7、key 列（重要）

key 列值显示 MySQL 实际使用到的索引。如果没有使用索引，该列值为 null。

8、key_len 列

MySQL 索引使用的字节数，通过该值计算出使用了索引中的哪些列。

比如 class_teacher表中，有联合索引 idx_class_teacher_id，由 class_id 和 teacher_id 字段组

成。使用一个索引字段或使用两个索引字段来观察 ken_len 的值。

mysql> explain select * from class_teacher where class_id = 1;

mysql> explain select * from class_teacher where class_id = 1 and teacher_id = 1;

 从上面 sql 可以看到，key_len 值分别为 4 和 8，这个值是 int=4 个字节算出来的，

因为我们的 class_id 和 teacher_id 都是 int 类型，所以 key_len 存的是使用到的索引字段类型

字节长度。第一个查询 sql 只用 class_id 索引字段，所以 key_len 为 4，第二个查询 sql 用到了

class_id 和 teacher_id，所以 key_len 计算为 8，但是如果字段允许为 null，则需要加 1。

key_len 的计算规则如下：

key_len计算规则如下：

1）字符串

char(n)：n 字节长度；

varchar(n)：2 字节存储字符串长度，如果是 utf-8，则长度 3n + 2；

2）数值类型

tinyint：1字节

smallint：2字节

int：4字节

bigint：8字节　　

3）时间类型

date：3字节

timestamp：4字节

datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL。（这是为什么会比正常计算多1的原因）。

索引最大长度是 768 字节，当字符串过长时，MySQL 会做一个类似左前缀索引的处理，将前半部

分的字符提取出来做索引。

9、ref 列

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，一般比较常见为 cons t或字段

名称。

mysql> explain select * from class_teacher where class_id = 1 and teacher_id = 1;

key 列联合索引 class_id 和 teacher_id，使用到两个索引字段，所以为 const,const。 

10、rows 列（重要）

MySQL 估计 sql 要查询出来的数据条数。

mysql> explain select * from class_teacher where class_id = 1;

 MySQL 预估可能会查询出两条数据，有时候不准。

11、filtered 列

filtered 的值指返回结果的行占需要读到的行(rows 列的值)的百分比。

mysql> select teacher_name from teacher;

这条 sql 能查出三条数据。

mysql> explain select teacher_name from teacher where teacher_name = 'a';

 这条 sql 能查出一条数据，通过 explain 可以看到 filtered 为 33.33。

即 filtered = 1 / 3 * 100/100 = 33.33%，保留两位小数。

但是我换一个条件查询。

mysql> explain select teacher_name from teacher where id = 1;

 这个 filtered 是 100，理论上应该也是 33.33，但是因为 where 条件 id 是主键，或者别的索引

列，它直接根据索引查找，就不需要跟全局比较。如果索引上查找某个值，它人为你只会去查询一

条数据，并且你也只能查询出一条数据，所以是100。

12、Extra 列（重要）

Extra 显示额外信息，就像你做 excel 一样，最后搞个备注列。备注很多时候很重要，

Extra 列也很重要，通常有如下几类重要信息需要关注。

1）Using index

Extra 显示 Using Index，说明用到了索引，是性能高的表现。一般出现在查询的列被索引列覆

盖，并且 where 筛选条件是索引的前导列。

mysql> explain select class_id from class_teacher where class_id = 1;

2）Using where

Extra 显示 Using where，表示没有用到索引，查询的列未被索引列覆盖，where 筛选条件非索引

的前导列。

mysql> explain select teacher_name from teacher where teacher_name = 'a';

3）Using where Using index

Extra 显示 Using whre Using index，表示查询的列被索引列覆盖，并且 where 筛选条件是索引列

之一但不是索引的前导列，说明无法直接通过索引查找查询到符合条件的数据。

mysql> explain select class_id from class_teacher where teacher_id = 1;

4）NULL

Extra 显示 null，表示查询的列未被索引列覆盖，并且 where 筛选条件是索引的前导列，说明用到

了索引，但是部分字段未被索引列覆盖，必须通过“回表”来实现，所以不是纯粹地用到了索引，也

不是完全没用到索引。

mysql> explain select * from class_teacher where class_id = 1;

5）Using index condition

Extra 显示 Using index condition 与 Using where 类似，查询的列不完全被索引列覆盖，

where 条件中是一个前导列的范围。

mysql> explain select * from class_teacher where class_id > 1;

6）Using temporary

Extra 显示 Using temporaty 表示 MySQL 需要创建一张临时表来处理查询。

中间 MySQL 处理过程需要多处理一个临时表，一般这种情况是需要优化处理的。

优化处理一般使用索引优化手段会多些。

在 teacher.teacher_name 没有索引，如果对该字段去重处理 distinct，则 MySQL 会把查询结果集

建临时表，然后再去重处理，中间多了创建临时表的过程，效率低；

mysql> explain select distinct teacher_name from teacher;

咱们在开始建表的时候，做了一个 class.class_name 建立了 idx_class_name 索引，

通过 class 的 class_name 建立索引类比 teacher 上没有建立索引的效果。

mysql> explain select distinct class_name from class;

从比较结果知道，name 上建立索引的 distinct 会走索引去重，而不会创建临时表，索引效率高。

7）Using filesort

Extra 显示 Using filesort 表示 MySQL 会对查询结果使用外部索引排序，

而没有按索引次序从表里读取行，没有用索引。此时MySQL 会根据联接类型扫描所有符合条件的

记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。MySQL绕着弯干这么

多事，效率很低，如果你的 order by 出现 Using filesort，一般需要优化了。

在 teacher.teacher_name 未创建索引，会扫描 teacher 整个表，保存排序关键字 teacher_name和

对应的 id，然后排序 teacher_name 并检索行记录。

mysql> explain select * from teacher order by teacher_name;

在 class.class_name 建立了 idx_class_name索引，此时查询时 Extra 是 Using index，使用了索

引列排序，效率高效。

explain select * from class order by class_name;

二 explain 总结

1、重点关注 id、type、key、Extra 列，Extra 最复杂。

2、explain 需要在实践中不断使用，不断体会，才能自己找到精髓，与自己融为一体。

理论+实战才是唯一标准。