一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访 问方法来具体执行查询等等。

其实除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句前边都可以加上EXPLAIN这个词儿。

table

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

id

查询语句中每出现一个SELECT关键字，设计MySQL的大叔就会为它分配一个唯一的id值。

一个SELECT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的。出现在前边的表表示驱动表，出现在后边的表表示被驱动表。

查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了。重写后两个ID是相同的。

select_type

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

设计MySQL的大叔为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了一个什么角色。

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

• PRIMARY 对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY。查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY。

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

最左边的小查询SELECT * FROM s1对应的是执行计划中的第一条记录，它的select_type值就是PRIMARY。

• UNION 对于包含UNION或者UNION ALL的大查询来说，它是由几个小查询组成的，其中除了最左边的那个小查询以外，其余的小查询的select_type值就是UNION

• UNION RESULT MySQL选择使用临时表来完成UNION查询的去重工作，针对该临时表的查询的select_type就是UNION RESULT。

• SUBQUERY 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键 字代表的那个查询的select_type就是SUBQUERY。

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a'; 

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | s1 | NULL | ALL | idx_key3 | NULL | NULL | NULL | 9688 | 100.00 | Using where |
| 2 | SUBQUERY | s2 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9954 | 100.00 | Using index | 
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+

外层查询的select_type就是PRIMARY，子查询的select_type就是SUBQUERY。需要大家注意的是，由于select_type为SUBQUERY的子查询由于会被物化，所以只需要执行一遍。

• DEPENDENT SUBQUERY 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';

select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。

• DEPENDENT UNION 在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。说的有些绕哈，比方说 下边这个查询:

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');

• DERIVED 对于采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED，

EXPLAIN SELECT * FROM (SELECT key1, count(*) as c FROM s1 GROUP BY key1) AS derived_s1 where c > 1;

+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 9688 | 33.33 | Using where |
| 2 | DERIVED | s1 | NULL | index | idx_key1 | idx_key1 | 303 | NULL | 9688 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+

从执行计划中可以看出，id为2的记录就代表子查询的执行方式，它的select_type是DERIVED，说明该子查询是以物化的方式执行的。id为1的记录代表外层查询，大家注意看它的table列显示的 是，表示该查询是针对将派生表物化之后的表进行查询的。

• MATERIALIZED 当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED

EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2);

+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | s1 | NULL
| 1 | SIMPLE | <subquery2> | NULL
| 2 | MATERIALIZED | s2 | NULL
+----+--------------+-------------+------------+--------+---------------+------------+---------+-------------------+------+----------+-------------+

执行计划的第三条记录的id值为2，说明该条记录对应的是一个单表查询，从它的select_type值为MATERIALIZED可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录 的id值都为1，说明这两条记录对应的表进行连接查询，需要注意的是第二条记录的table列的值是，说明该表其实就是id为2对应的子查询执行之后产生的物化表，然后将s1和该物化表 进行连接查询。

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

• system 当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。

• const const就是当我们根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。但是唯一二级索引查询列为NULL值是个例外，因为可以有多个NULL。

• eq_ref 在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较)，则对该被驱动表的 访问方法就是eq_ref。

EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+
| 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 9688 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | xiaohaizi.s1.id | 1 | 100.00 | NULL | 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------------+------+----------+-------+

从执行计划的结果中可以看出，MySQL打算将s1作为驱动表，s2作为被驱动表，重点关注s2的访问方法是eq_ref，表明在访问s2表的时候可以通过主键的等值匹配来进行访问。

• ref 当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。换句话说：普通二级索引等值连接查询。

• fulltext 全文索引

• ref_or_null 当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null

• index_merge 一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询

• unique_subquery 类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行 等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery，

• index_subquery index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引

• range 如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法。

• index index 当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。

index类型和ALL类型一样，区别就是index类型是扫描的索引树。以下两种情况会触发：

1. 如果索引是查询的覆盖索引，就是说索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。 在这种情况下，explain 的 Extra 列的结果是 Using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。
2. 全表扫描会按索引的顺序来查找数据行。使用索引不会出现在Extra列中

8.0后能用到覆盖索引，就算没有满足最左匹配也会使用索引，extra会显示Using index for skip scan

• all 全表扫描

除了All这个访问方法外，其余的访问方法都能用到索引，除了index_merge访问方法外，其余的访问方法都最多只能用到一个索 引。

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len

key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的:

• 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值。对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8， 那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。
• 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。
• 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。

ref

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

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a'; 
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 
| 1 | SIMPLE | s1 | NULL | ref | idx_key1 | idx_key1 | 303 | const | 8 | 100.00 | NULL | 
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+

可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数。 有点时候查询时会和一个函数对比，那么ref就是func。

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered

目标行数所占百分比。

extra

顾名思义，Extra列是用来说明一些额外信息的，我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句。MySQL提供的额外信息有好几十个。

• No tables used 当查询语句的没有FROM子句时将会提示该额外信息，比如: mysql> EXPLAIN SELECT 1;

*Impossible WHERE 查询语句的WHERE子句永远为FALSE时将会提示该额外信息，比方说: mysql> EXPLAIN SELECT * FROM s1 WHERE 1 != 1;

*No matching min/max row 当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息，比方说: mysql> EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = ‘abcdefg’;

• Using index 当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息

• Using index condition(索引下推） 有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询: SELECT * FROM s1 WHERE key1 > ‘z’ AND key1 LIKE ‘%a’; 其中的key1 > ‘z’可以使用到索引，但是key1 LIKE ‘%a’却无法使用到索引，在以前版本的MySQL中，是按照下边步骤来执行这个查询的: 先根据key1 > ‘z’这个条件，从二级索引idx_key1中获取到对应的二级索引记录。 根据上一步骤得到的二级索引记录中的主键值进行回表，找到完整的用户记录再检测该记录是否符合key1 LIKE ‘%a’这个条件，将符合条件的记录加入到最后的结果集。 但是虽然key1 LIKE ‘%a’不能组成范围区间参与range访问方法的执行，但这个条件毕竟只涉及到了key1列，所以设计MySQL的大叔把上边的步骤改进了一下: 先根据key1 > ‘z’这个条件，定位到二级索引idx_key1中对应的二级索引记录。 对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足key1 LIKE ‘%a’这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。 对于满足key1 LIKE ‘%a’这个条件的二级索引记录执行回表操作。 我们说回表操作其实是一个随机IO，比较耗时，所以上述修改虽然只改进了一点点，但是可以省去好多回表操作的成本。设计MySQL的大叔们把他们的这个改进称之为索引条件下推(英文名:Index Condition Pushdown)。

• Using where 当我们使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件时，在Extra列中会提示上述额外信息。

• Using join buffer (Block Nested Loop) 在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度，也就是我们所讲的基于块的嵌套循环算法。

• Not exists 当我们使用左(外)连接时，如果WHERE子句中包含要求被驱动表的某个列等于NULL值的搜索条件，而且那个列又是不允许存储NULL值的，那么在该表的执行计划的Extra列就会提示Not exists额外 信息。

• Using intersect(…)、Using union(…)和Using sort_union(…) 如果执行计划的Extra列出现了Using intersect(…)提示，说明准备使用Intersect索引合并的方式执行查询，括号中的…表示需要进行索引合并的索引名称;如果出现了Using union(…)提 示，说明准备使用Union索引合并的方式执行查询;出现了Using sort_union(…)提示，说明准备使用Sort-Union索引合并的方式执行查询。

• Zero limit 当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息

• Using filesort 有一些情况下对结果集中的记录进行排序是可以使用到索引的，但是很多情况下排序操作无法使用到索引，只能在内存中(记录较少的时候)或者磁盘中(记录较多 的时候)进行排序，设计MySQL的大叔把这种在内存中或者磁盘上进行排序的方式统称为文件排序(英文名:filesort)。注意的是，如果查询中需要使用filesort的方式进行排序的记录非常多，那么这个过程是很耗费性能的，我们最好想办法将使用文件排序的执行方式改为使用索引进行排序。

• Using temporary 在许多查询的执行过程中，MySQL可能会借助临时表来完成一些功能，比如去重、排序之类的，比如我们在执行许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果不能有效利用索引来 完成查询，MySQL很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表，在执行计划的Extra列将会显示Using temporary提示。 注意：MySQL会在包含GROUP BY子句的查询中默认添加上ORDER BY子句，如果我们并不想为包含GROUP BY子句的查询进行排序，需要我们显式的写上ORDER BY NULL。

Json格式的执行计划

我们上边介绍的EXPLAIN语句输出中缺少了一个衡量执行计划好坏的重要属性 —— 成本。不过设计MySQL的大叔贴心的为我们提供了一种查看某个执行计划花费的成本的方式: 在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON。

Extented EXPLAIN

最后，设计MySQL的大叔还为我们留了个彩蛋，在我们使用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将如何执行查询语句的一个参考依据而已。

optimizer trace 表的神奇功效

对于MySQL 5.6以及之前的版本来说，查询优化器就像是一个黑盒子一样，你只能通过EXPLAIN语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。

在MySQL 5.6以及之后的版本中，设计MySQL的大叔贴心的为这部分小伙伴提出了一个optimizer trace的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由 系统变量optimizer_trace决定。