大家好。众所周知，MySQL 查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式。在日常工作过程中，我们可以使用EXPLAIN语句来查看某个查询语句的具体执行计划， 今天我们就来聊一聊EXPLAIN语句。

如果我们想看看某个查询的执行计划的话，可以在具体的查询语句前边加一个EXPLAIN，就像这样：

我们把EXPLAIN 语句输出的各个列的作用先大致罗列一下：

列名	描述
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT关键字对应的那个查询的类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

下面我们仔细聊一下每个列是干什么的。

一、 执行计划输出中各列详解

为了方便我们讲解，我们依旧先创建两张一模一样的single_table表（下面分别称为t1和t2），并分别插入10000条记录。

CREATE TABLE single_table ( id INT NOT NULL AUTO_INCREMENT, key1 VARCHAR(100), key2 INT, key3 VARCHAR(100), key_part1 VARCHAR(100), key_part2 VARCHAR(100), key_part3 VARCHAR(100), common_field VARCHAR(100), PRIMARY KEY (id), KEY idx_key1 (key1), UNIQUE KEY idx_key2 (key2), KEY idx_key3 (key3), KEY idx_key_part(key_part1, key_part2, key_part3) 
) Engine=InnoDB CHARSET=utf8;

1、table

不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。比如：

这条sql就涉及到s1一个表，所以只输出一条记录。

可以看到这个连接查询的执行计划中有两条记录，这两条记录的table列分别是s1和s2，这两条记录用来分别说明对s1表和s2表的访问方法是什么 。

2、id

我们在平时写查询语句的时候一般都只有一个SELECT关键字，比如：

SELECT * FROM single_table s1 INNER JOIN single_table2 s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'

但是在使用子查询语句或者UNION语句时可能会有多个SELECT关键字，比如：

#子查询
SELECT * FROM single_table s1 WHERE key1 IN (SELECT * FROM single_table2 s2);
#UNION
SELECT * FROM single_table s1 UNION SELECT * FROM single_table2 s2;

查询语句中每出现一个SELECT 关键字，MySQL就会为它分配一个唯一的id 值。这个id值就是 EXPLAIN 语句的第一个列，比如下边这个查询中只有一个 SELECT 关键字，所以 EXPLAIN 的结果中也就只有一 条id 列为1的记录。

对于连接查询来说，一个SELECT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的，比如：

可以看到，在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。所以从上边的EXPLAIN输出中我们可以看出，查询优化器准备让s1表作为驱动表，让s2表作为被驱动表来执行查询。

对于包含子查询的查询语句来说，可能涉及多个SELECT关键字，所以在包含子查询的查询语句的执行计划中，每个SELECT 关键字都会对应一个唯一的id 值，比如：

上述sql中，s1表在外层查询中，外层查询有一个独立的SELECT关键字，所以第一条记录的id值就是 1 ，s2表在子查询中，子查询有一个独立的SELECT关键字，所以第二条记录的id值就是2。
但是这里大家需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，比如说：

可以看到，虽然我们的查询语句是一个子查询，但是执行计划中s1和s2表对应的记录的id值全部是1，这就 表明了查询优化器将子查询转换为了连接查询。

对于包含UNION 子句的查询语句来说，每个SELECT关键字对应一个 id 值也是没错的，不过还是有点儿特别的东西，比方说下边这个查询：

这个语句的执行计划有三条记录，这是因为UNION子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，UNION子句是为了把id为1的查询和id为2的查询的结果集合并起来并去重，在内部创建了一个临时表（表名就是执行计划第三条记录的table 列的名称）。

跟UNION 对比起来， UNION ALL 就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录 合并成一个并返回给用户，所以也就不需要使用临时表。所以在包含UNION ALL子句的查询的执行计划中，就没有上述的第三条记录，比如：

3、select_type

通过上边的内容我们知道，一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。

MySQL 为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，下面我们看一下select_type的各个值是什么意思：

名称	描述
SIMPLE	查询语句中不包含UNION或者子查询的查询都算作是SIMPLE 类型。
PRIMARY	对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type 值就是PRIMARY。
UNION	对于包含UNION 或者 UNION ALL 的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查的select_type值就是UNION。
UNION RESULT	MySQL选择使用临时表来完成 UNION查询的去重工作，针对该临时表的查询的select_type 就是UNION RESULT。
SUBQUERY	如果包含子查询的查询语句不能够转为对应的半连接的形式，并且该子查询是不相关子查询，并且查询 优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY。
DEPENDENT SUBQUERY	如果包含子查询的查询语句不能够转为对应的半连接的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type 就是DEPENDENT SUBQUERY。
DEPENDENT UNION	在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是 DEPENDENT UNION。
DERIVED	对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED。
MATERIALIZED	当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对 应的select_type属性就是MATERIALIZED。
UNCACHEABLE SUBQUERY	不常用。
UNCACHEABLE UNION	不常用。

4、partitions

一般情况下我们的查询语句的执行计划的partitions列的值都是NULL，这里我们就不讲了。

5、type

执行计划中的一条记录就代表着MySQL对某个表的执行查询时的访问方法，其中的type列就表明了这个访问方法是什么，比如：

可以看到type 列的值是ref ，表明MySQL即将使用ref访问方法来执行对s1表的查询。我们之前说过对使用InnoDB存储引擎的表进行单表访问的一些访问方法，例如：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery， range，index，ALL。下面我们再看一下这些方法：

方法名	描述	举例sql
system	当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的 访问方法就是system。	EXPLAIN SELECT * FROM t;（表t的存储引擎的统计数据必须是精确的）
const	是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。	EXPLAIN SELECT * FROM single_table s1 WHERE id = 5
eq_ref	在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的，则对该被驱动表的访问方法就是 eq_ref。	EXPLAIN SELECT * FROM single_table s1 INNER JOIN single_table2 s2 ON s1.id = s2.id;
ref	当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。	EXPLAIN SELECT * FROMsingle_table s1 WHERE key1 = 'a';
fulltext	全文索引。	这里不讲了。
ref_or_null	当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null。	EXPLAIN SELECT * FROMsingle_table s1 WHERE key1 = 'a' OR key1 IS NULL;
index_merge	MySQL打算使用索引合并的方式来执行对表的 查询时，那么对该表的访问方法就可能是index_merge。	EXPLAIN SELECT * FROMsingle_table s1WHERE key1 = 'a'OR key3 = 'a';
unique_subquery	unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS 子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery。	EXPLAIN SELECT * FROM single_table s1 WHERE key2 IN (SELECT id FROM single_table2 s2 where s1.key1 = s2.key1) OR key3 = 'a';
index_subquery	index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引。	EXPLAIN SELECT * FROM single_table s1 WHERE common_field IN (SELECT key3 FROM single_table2 s2 where s1.key1 = s2.key1) OR key3 = 'a';
range	如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法。	EXPLAIN SELECT * FROM single_table s1 WHERE key1 IN ('a', 'b', 'c');
index	当可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。	EXPLAIN SELECT key_part2 FROM single_table s1 WHERE key_part3 = 'a';
ALL	全表扫描。	这里不讲了。

6、possible_keys和key

在EXPLAIN 语句输出的执行计划中， possible_keys 列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些，比如：

上述执行计划的possible_keys 列的值是 idx_key1,idx_key3 ，表示该查询可能使用到idx_key1,idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用 idx_key3来执行查询比较划算。

不过有一点比较特别，就是在使用index访问方法来查询某个表时，possible_keys列是空的，而key列展示的是实际使用到的索引。

注意：possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

7、 key_len

我们知道在使用某个索引来执行查询时，首先要搞清楚扫描区间和边界条件，这里我们可以通过key_len来了解查询sql的边界条件。例如：

我们可以看到，这条sql的key_len为303，那么303是什么意思呢？
key_len是由三个部分构成的：

1. 该列的实际数据最多占用的存储空间长度。key1字段类型是varchar(100)，s1表使用utf8字符集，所以多占用的存储空间长度是3 * 100 = 300字节。
2. 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。key1列可以为空。所以需要+1。
3. 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。key1列是变长字段。所以需要+2。

所以，key_len的值就是3 * 100 + 1 +2 = 303。

这意味着 MySQL 在执行上述查询中只能用到key1列的索引，再看key1列的查询条件是 key1 > ‘a’ and key1 < ‘b’，所以这个搜索条件就是形成范围区间的边界条件。

8、ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、 unique_subquery、 index_subquery其中之一时，ref 列展示的就是与索引列作等值匹配的东东是什么，比如只是一个常数或者是某个列。例如：

可以看到ref 列的值是const ，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数，当然有时候更复杂一点：

可以看到对被驱动表s2的访问方法是eq_ref，而对应的ref列的值是s1.id ，这说明在对被驱动表进行访问时会用到PRIMARY索引，也就是聚簇索引与一个列进行等值匹配的条件，于s2表的id作等值匹配的对象就是s1.id 列。

有的时候与索引列进行等值匹配的对象是一个函数，比如：

可以看到对s2表采用ref访问方法执行查询，然后在查询计划的ref列里输出 的是func ，说明与s2表的key1列进行等值匹配的对象是一个函数。

9、rows

如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。比如：

我们看到执行计划的rows列的值是1 ，这意味着查询优化器在经过分析使用idx_key1进行查询的成本之后，觉得满足key1 > ‘z’ 这个条件的记录只有1条。

10、filtered

我们在分析连接查询的成本时提出过一个condition filtering（条件过滤）的概念，就是MySQL在计算驱动表扇出时采用的一个策略：

如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。

如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。

从执行计划的key列中可以看出来，该查询使用idx_key1索引来执行查询，从rows 列可以看出满足key1 > ‘z’ 的记录有1条。执行计划的 filtered 列就代表查询优化器预测在这1条记录中，有多少条记录满足其余的搜索条件，也就是common_field = ‘a’ 这个条件的百分比。此处 filtered 列的值是 10.00，说明查询优化器预测这条记录10.00%的可能满足 common_field = ‘a’ 这个条件。

对于单表查询来说，这个filtered 列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的 filtered 值，比方：

从执行计划中可以看出来，查询优化器打算把s1当作驱动表，s2当作被驱动表。我们可以看到驱动表s1表的执行计划的rows 列为9823， filtered 列为10.00 ，这意味着驱动表 s1的扇出值就是 9823× 10.00% = 982.3，这说明还要对被驱动表执行大约982次查询。

11、Extra

Extra 列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。下面我们介绍一些平时常见的或者比较重要的额外信息。

额外信息	描述	举例sql
No tables used	当查询语句的没有FROM 子句时将会提示该额外信息。	EXPLAIN SELECT 1
Impossible	WHERE查询语句的WHERE 子句永远为 FALSE 时将会提示该额外信息。	EXPLAIN SELECT * FROM single_table s1 WHERE 1 != 1;
No matching min/max row	当查询列表处有MIN或者MAX 聚集函数，但是并没有符合WHERE 子句中的搜索条件的记录时会提示该额外信息。	EXPLAIN SELECT MIN(key1) FROM single_table s1 WHERE key1 = 'abcdefg';
Using index	当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息。	EXPLAIN SELECT key1 FROM single_table s1 WHERE key1 = 'a';
Using index condition	有些搜索条件虽然出现了索引列，但是不能充当边界条件来形成扫描区间，这时会提示该额外信息。	EXPLAIN SELECT * FROM single_table s1 WHERE key1 > 'z' AND key1 LIKE '%b';
Using where	当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在 Extra 列中会提示上述额外信息。	EXPLAIN SELECT * FROM single_table s1 WHERE common_field = 'a';
Zero limit	当我们的LIMIT 子句的参数为 0 时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息。	EXPLAIN SELECT * FROM single_table s1 LIMIT 0;
Using filesort	有一些情况下对结果集中的记录进行排序是可以使用到索引的，如果某个查询需要使用文件排序的方式执行查询，就会在执行计划的Extra 列中显示 Using filesort 提示。	EXPLAIN SELECT * FROM single_table s1 ORDER BY common_field LIMIT 10;
Using temporary	如果查询中使用到了内部的临时表，在执行计划 的Extra 列将会显示 Using temporary提示。	EXPLAIN SELECT common_field, COUNT(*) AS amount FROM single_table s1 GROUP BY common_field;
Start temporary, End temporary	当sql通过建立临时表来实现为外层查询中的记 录进行去重操作时，驱动表查询执行计划的Extra列将显示Start temporary提示，被驱动表查询执行计划的Extra 列将显示 End temporary 提示。	EXPLAIN SELECT * FROM single_table s1 WHERE key1 IN (SELECT key3 FROM single_table2 s2 WHERE common_field = 'a');
LooseScan	在将In子查询转为semi-join 时，如果采用的是 LooseScan 执行策略，则在驱动表执行计划的Extra列就是显示LooseScan 提示。	EXPLAIN SELECT * FROM single_table s1 WHERE key3 IN (SELECT key1 FROM single_table2 s2 WHERE key1 > 'z');
FirstMatch(tbl_name)	在将In子查询转为semi-join 时，如果采用的是FirstMatch 执行策略，则在被驱动表执行计划的Extra列就是显示FirstMatch(tbl_name) 提示。	EXPLAIN SELECT * FROM single_table s1 WHERE common_field IN (SELECT key1 FROM single_table2 s2 where s1.key3 = s2.key3);

二、Json格式的执行计划

我们上边介绍的EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。不过MySQL提供了一种查看某个执行计划花费的成本的方式：

在EXPLAIN 单词和真正的查询语句中间加上FORMAT=JSON。

这样我们就可以得到一个json 格式的执行计划，里边儿包含该计划花费的成本，比如：

root@localhost 8.4.0 [hanzy]>  EXPLAIN FORMAT=JSON SELECT * FROM single_table s1 INNER JOIN single_table2 s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G;
*************************** 1. row ***************************
EXPLAIN: {"query_block": {"select_id": 1,   # 整个查询语句只有1个SELECT关键字，该关键字对应的id号为1 "cost_info": {"query_cost": "1350.35"  # 整个查询的执行成本预计为1350.35},"nested_loop": [  # 几个表之间采用嵌套循环连接算法执行# 以下是参与嵌套循环连接算法的各个表的信息 {"table": {"table_name": "s1",  # s1表是驱动表"access_type": "ALL",   # 访问方法为ALL，意味着使用全表扫描访问 "possible_keys": [  # 可能使用的索引 "idx_key1"],"rows_examined_per_scan": 9823,  # 查询一次s1表大致需要扫描9823条记录"rows_produced_per_join": 982,  # 驱动表s1的扇出是982"filtered": "10.00",  # condition filtering代表的百分比 "cost_info": {"read_cost": "908.32",  # 稍后解释"eval_cost": "98.23",  # 稍后解释"prefix_cost": "1006.55",  # 单次查询s1表总共的成本"data_read_per_join": "1M"  # 读取的数据量 },"used_columns": [   # 执行查询中涉及到的列 "id","key1","key2","key3","key_part1","key_part2","key_part3","common_field"],# 对s1表访问时针对单表查询的条件 "attached_condition": "((`hanzy`.`s1`.`common_field` = 'a') and (`hanzy`.`s1`.`key1` is not null))"}},{"table": {"table_name": "s2",  # s2表是被驱动表 "access_type": "eq_ref",  # 访问方法为eq_ref"possible_keys": [  # 可能使用的索引 "idx_key2"],"key": "idx_key2",  # 实际使用的索引 "used_key_parts": [  # 使用到的索引列 "key2"],"key_length": "5",  # key_len"ref": [   # 与key2列进行等值匹配的对象 "hanzy.s1.key1"],"rows_examined_per_scan": 1,  # 查询一次s2表大致需要扫描1条记录 "rows_produced_per_join": 982,  # 被驱动表s2的扇出是982"filtered": "100.00",  # condition filtering代表的百分比 # s2表使用索引进行查询的搜索条件 "index_condition": "(cast(`hanzy`.`s1`.`key1` as double) = cast(`hanzy`.`s2`.`key2` as double))","cost_info": {"read_cost": "245.58",   # 稍后解释"eval_cost": "98.23",  # 稍后解释"prefix_cost": "1350.36",  # 单次查询s1、多次查询s2表总共的成本 "data_read_per_join": "1M"  # 读取的数据量},"used_columns": [  # 执行查询中涉及到的列 "id","key1","key2","key3","key_part1","key_part2","key_part3","common_field"]}}]}
}
1 row in set, 2 warnings (0.01 sec)

我们使用#后边跟随注释的形式为大家解释了EXPLAIN FORMAT=JSON 语句的部分输出内容，现在我们先看 s1 表的 “cost_info” 部分：

 "cost_info": {"read_cost": "908.32",  "eval_cost": "98.23",  "prefix_cost": "1006.55", "data_read_per_join": "1M" },

read_cost 是由下边这两部分组成的： IO 成本和检测rows × (1 - filter) 条记录的CPU成本。

eval_cost 是这样计算的：检测 rows × filter 条记录的成本。

prefix_cost 就是单独查询 s1 表的成本，也就是：read_cost + eval_cost。

data_read_per_join 表示在此次查询中需要读取的数据量。

对于s2 表的"cost_info" 部分是这样的：

"cost_info": {"read_cost": "245.58",  "eval_cost": "98.23",  "prefix_cost": "1350.36",  "data_read_per_join": "1M" 
},

由于s2 表是被驱动表，所以可能被读取多次，这里的read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，大家主要关注里边儿的prefix_cost 的值代表的是整个连接查询预计的成本，也就是单次查询s1 表和多次查询s2 表后的成本的和。

三、Extented EXPLAIN

在我们使用EXPLAIN 语句查看了某个查询的执行计划后，紧接着还可以使用SHOW WARNINGS语句查看与这个查询的执行计划有关的一些扩展信息，比如：

大家可以看到SHOW WARNINGS 展示出来的信息有三个字段，分别是 Level 、 Code 、 Message 。我们最常见的就 是Code为1003的信息，当 Code值为1003时， Message字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。比如我们上边的查询本来是一个左（外）连接查询，但是有一个s2.common_field IS NOT NULL 的条件，这就会导致查询优化器把左（外）连接查询优化为内连接查询，从SHOW WARNINGS 的Message 字段也可以看出来，原本的LEFT JOIN已经变成了 JOIN 。

但是大家一定要注意，我们说Message 字段展示的信息类似于查询优化器将我们的查询语句重写后的语句，并不是等价于，也就是说Message 字段展示的信息并不是标准的查询语句，在很多情况下并不能直接运行，它只能作为帮助我们理解查MySQL将如何执行查询语句的一个参考依据而已。

好了，到这里我们就讲完了，大家有什么想法欢迎留言讨论。也希望大家能给作者点个关注，谢谢大家！最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！