目录
一条SQL语句经过查询优化器的各种基于成本和规则的优化会后生成一个执行成本最低的方式，这个方式就是所谓的执行计划，执行计划展示了接下来具体执行查询的方式，比如单表查询，使用哪个索引效率更高、连接查询哪张表作为驱动表更合适、每张表采用什么访问方法具体执行查询等等。MySQL提供了EXPLAIN语句来帮助我们查看某个SQL语句的具体执行计划。今天我们就来学习一下查询优化的百科全书——EXPLAIN执行计划。
执行计划输出各列的含义详解
如果我们想查看某个查询的执行计划，可以在具体的查询语句前面加一个EXPLAIN或DESC或DESCRIBE均可，就像下面这样。
输出的内容就是执行计划。除了将上述三个关键字任意一个加在SELECT语句前，DELETE、INSERT、UPDATE语句前也可以添加。EXPLAIN语句输出的各个列代表的含义如下：
| 列名 | 描述 |
|---|---|
| id | 执行编号，标识SELECT所属的行，在一个大的查询语句中每个SELECT关键字都对应一个唯一的id，只有唯一的SELECT，每行都将显示1，否则，内层的SELECT语句一般会顺序编号，对应于其在原始语句中的位置。 |
| select_type | SELECT关键字对应的那个查询的类型 |
| table | 访问引用的表名 |
| partitions | 匹配的分区信息 |
| type | 针对单表的访问方法 |
| possible_keys | 可能用到的索引 |
| key | 实际使用到的索引 |
| key_len | 实际使用的索引长度（单位：字节，B） |
| ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
| rows | 预估需要读取的记录条数 |
| filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
| Extra | 额外的信息 |
为了方便说明，我们还是准备两张之前用过的表：
CREATE TABLE `table_query_cost` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `key1` VARCHAR(100), `key2` INT(11) , `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 uq_key2 (`key2`), KEY idx_key3 (`key3`), KEY idx_key_part(`key_part1`, `key_part2`, `key_part3`)) Engine=InnoDB;
创建2张和table_query_cost表结构一样的表t1、t2，并且除id列之外都插入随机值共10000行记录。注意我们不按照EXPLAIN输出列的顺序来介绍各列含义。
table
不管我们的查询语句多复杂，包含多少张表进行关联查询，最后都会下钻到对每个表进行单表访问。MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名。
DESC SELECT * FROM t1;
单表查询，DESC只输出一条记录，table列是t1，表示这条记录是说明对t1表单表访问方法的。
DESC SELECT * FROM t1 INNER JOIN t2;
执行计划有2条记录，table列分别是t1、t2，分别记录对t1和t2的访问方法是什么。
id
查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值。这个id值就是EXPLAIN语句的第一个列。
DESC SELECT * FROM t1 WHERE key1 = 'a';
对于连接查询来说，一个SELECT关键字后边的FROM子句中可以跟随多个表，所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的。
DESC SELECT * FROM t1 INNER JOIN t2;
可以看到，上述连接查询中参与连接的t1和t2表分别对应一条记录，但是这两条记录对应的id值都是1。在连接查询的执行计划中，每个表都会对应一条记录，这些记录的id列的值是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表。所以从上边的EXPLAIN输出中我们可以看出，查询优化器准备让t1表作为驱动表，让t2表作为被驱动表来执行查询。
对于包含子查询的查询语句来说，就可能涉及多个SELECT关键字，所以在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值。
DESC SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2) OR key3 = 'a';
从输出结果中我们可以看到，t1表在外层查询中，外层查询有一个独立的SELECT关键字，所以第一条记录的id值就是1，t2表在子查询中，子查询有一个独立的SELECT关键字，所以第二条记录的id值就是2。
这里需要特别注意，查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。所以如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就可以，出现2次SELECT关键字的子查询，如果执行计划中id列都是1，那就表明查询优化器将子查询转换为了连接查询。
对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是没错的，但有一个情况需要注意：
DESC SELECT * FROM t1 UNION SELECT * FROM t2;
输出的结果可以看出，第三行内容的id值为NULL，table列也不是表名。UNION子句会把多个查询的结果集合并起来并对结果集中的记录进行去重，MySQL使用的是内部的临时表进行去重，UNION子句把id为1的查询和id为2的查询的结果集合并起来并去重，所以在内部创建了一个名为<union1, 2>的临时表，id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。
跟UNION对比起来，UNION ALL就不需要为最终的结果集进行去重，它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户，所以也就不需要使用临时表。
DESC SELECT * FROM t1 UNION ALL SELECT * FROM t2;
所以在包含UNION ALL子句的查询的执行计划中，就没有那个id为NULL的记录。
select_type
一条大的查询语句里边可以包含若干个SELECT关键字，每个SELECT关键字代表着一个小的查询语句，而每个SELECT关键字的FROM子句中都可以包含若干张表（这些表用来做连接查询），每一张表都对应着执行计划输出中的一条记录，对于在同一个SELECT关键字中的表来说，它们的id值是相同的。MySQL为每一个SELECT关键字代表的小查询都定义了一个称之为select_type的属性，意思是我们只要知道了某个小查询的select_type属性，就知道了这个小查询在整个大查询中扮演了的角色。select_type的取值如下：
| 名称 | 描述 |
|---|---|
| SIMPLE | Simple SELECT (not using UNION or subqueries) |
| PRIMARY | Outermost SELECT |
| UNION | Second or later SELECT statement in a UNION |
| UNION RESULT | Result of a UNION |
| SUBQUERY | First SELECT in subquery |
| DEPENDENT SUBQUERY | First SELECT in subquery, dependent on outer query |
| DEPENDENT UNION | Second or later SELECT statement in a UNION, dependent on outer query |
| DERIVED | Derived table |
| MATERIALIZED | Materialized subquery |
| UNCACHEABLE SUBQUERY | A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
| UNCACHEABLE UNION | The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
下面来一一详细介绍一下：
-
SIMPLE：
查询语句中不包含UNION或者子查询的查询都算作是SIMPLE类型。
DESC SELECT * FROM t1;
连接查询也是SIMPLE类型。
DESC SELECT * FROM t1 INNER JOIN t2;
-
PRIMARY：
对于包含UNION、UNION ALL或者子查询的大查询来说，它是由几个小查询组成的，其中最左边的那个查询的select_type值就是PRIMARY。
DESC SELECT * FROM t1 UNION SELECT * FROM t2;
由结果可得，最左边的小查询SELECT * FROM t1对应的是执行计划中的第一条记录，它的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。
DESC SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2) OR key3 = 'a';
可以看到，外层查询的select_type就是PRIMARY，子查询的select_type就是SUBQUERY。需要大家注意的是，由于select_type为SUBQUERY的子查询会被物化，所以只需要执行一遍。
-
DEPENDENT SUBQUERY：
如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY。
DESC SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2 WHERE t1.key2 = t2.key2) OR key3 = 'a';
小提示：select_type为DEPENDENT SUBQUERY的查询可能会被执行多次。
-
DEPENDENT UNION：
包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。
DESC SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2 WHERE key1 = 'a' UNION SELECT key1 FROM t1 WHERE key1 = 'b');
这个大查询里包含了一个子查询，子查询里又是由UNION连起来的两个小查询。从执行计划中可以看出来，SELECT key1 FROM t2 WHERE key1 = 'a'这个小查询由于是子查询中第一个查询，所以它的select_type是DEPENDENT SUBQUERY，而SELECT key1 FROM t1 WHERE key1 = 'b'这个查询的select_type就是DEPENDENT UNION。
-
DERIVED：
采用物化的方式执行的包含派生表的查询，该派生表对应的子查询的select_type就是DERIVED。
DESC SELECT * FROM (SELECT key1, count(*) as c FROM t1 GROUP BY key1) AS derived_t1 where c > 1;
从执行计划中可以看出，id为2的记录就代表子查询的执行方式，它的select_type是DERIVED，说明该子查询是以物化的方式执行的。（回顾一下物化的概念MySQL之SQL优化相关术语必知，Materialize：物化，将子查询结果集中的记录保存到临时表的过程称之为物化。）id为1的记录代表外层查询，它的table列显示的是<derived2>，表示该查询是针对将派生表物化之后的表进行查询的。
-
MATERIALIZED：
当查询优化器在执行包含子查询的语句时，选择将子查询物化之后与外层查询进行连接查询时，该子查询对应的select_type属性就是MATERIALIZED。
DESC SELECT * FROM t1 WHERE key1 IN (SELECT key1 FROM t2);
执行计划的第三条记录的id值为2，说明该条记录对应的是一个单表查询，从它的select_type值为MATERIALIZED可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的id值都为1，说明这两条记录对应的表进行连接查询，需要注意的是第二条记录的table列的值是<subquery2>，说明该表其实就是id为2对应的子查询执行之后产生的物化表，然后将t1和该物化表进行连接查询。
-
UNCACHEABLE SUBQUERY、UNCACHEABLE UNION：
不常用，很少见。
partitions
分区信息，很少见，一般情况下该列的值都是NULL，不再赘述。
type
执行计划的一条记录就代表着MySQL对某个表的执行查询时的访问方法，其中的type列就表明了这个访问方法。
DESC SELECT * FROM t1 WHERE key1 = 'a';
可以看到type列的值是ref，表明MySQL即将使用ref访问方法来执行对t1表的查询。但是我们之前只唠叨过对使用InnoDB存储引擎的表进行单表访问的一些访问方法，完整的访问方法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL。
-
system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system。
CREATE TABLE t(i int) Engine=MyISAM;INSERT INTO t VALUES(1);DESC SELECT * FROM t;
-
cost：根据主键或者唯一二级索引列与常数进行等值匹配时，对单表的访问方法就是const。
DESC SELECT * FROM t1 WHERE id = 666;
-
eq_ref：在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq_ref。
DESC SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
MySQL打算将t1表作为驱动表，t2表作为被驱动表。被驱动表的访问方法是eq_ref，说明在访问t2表的时候可以通过主键的等值匹配来进行访问。
-
ref：当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref。
-
fulltext：全文索引，暂不介绍。
-
ref_or_null：对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null。
DESC SELECT * FROM t1 WHERE key1 = 'a' OR key1 IS NULL;
-
index_merge：一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询。
DESC SELECT * FROM t1 WHERE key1 = 'a' OR key3 = 'a';
执行计划的type列的值是index_merge就可以看出，MySQL打算使用索引合并的方式来执行对t1表的查询。
-
unique_subquery：类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery。
DESC SELECT * FROM t1 WHERE key2 IN (SELECT id FROM t2 where t1.key1 = t2.key1) OR key3 = 'a';
执行计划的第二条记录的type值就是unique_subquery，说明在执行子查询时会使用到id列的索引。
-
index_subquery：index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引。
DESC SELECT * FROM t1 WHERE common_field IN (SELECT key3 FROM t2 where t1.key1 = t2.key1) OR key3 = 'a';
-
range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法。
DESC SELECT * FROM t1 WHERE key1 IN ('a', 'b', 'c');
或：
DESC SELECT * FROM t1 WHERE key1 > 'a' AND key1 < 'b';
-
index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。
DESC SELECT key_part2 FROM t1 WHERE key_part3 = 'a';
上述查询中的搜索列表中只有key_part2一个列，而且搜索条件中也只有key_part3一个列，这两个列又恰好包含在idx_key_part这个索引中，可是搜索条件key_part3不能直接使用该索引进行ref或者range方式的访问，只能扫描整个idx_key_part索引的记录，所以查询计划的type列的值就是index。
小提示：对于使用InnoDB存储引擎的表来说，二级索引的记录只包含索引列和主键列的值，而聚簇索引中包含用户定义的全部列以及一些隐藏列，所以扫描二级索引的代价比直接全表扫描，也就是扫描聚簇索引的代价更低一些。
-
ALL：全表扫描。
DESC SELECT * FROM t1;
一般来说，这些访问方法按照我们介绍它们的顺序性能依次变差。其中除了All这个访问方法外，其余的访问方法都能用到索引，除了index_merge访问方法外，其余的访问方法都最多只能用到一个索引。
possible_keys和key
输出的执行计划中，possible_keys列表示在某个查询语句中，对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些。
DESC SELECT * FROM t1 WHERE key1 > 'z' AND key3 = 'a';
上述执行计划的possible_keys列的值是idx_key1,idx_key3，表示该查询可能使用到idx_key1,idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用idx_key3来执行查询成本较低。
有一点比较特别，就是在使用index访问方法来查询某个表时，possible_keys列是空的，而key列展示的是实际使用到的索引。
DESC SELECT key_part2 FROM t1 WHERE key_part3 = 'a';
小提示：possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。
key_len
key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：
1、对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8mb4，那么该列实际占用的最大存储空间就是100 × 4 = 400个字节。
2、如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。
3、对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。
DESC SELECT * FROM t1 WHERE id = 66;
id列的类型是INT，并且不可以存储NULL值，所以在使用该列的索引时key_len大小就是4。当索引列可以存储NULL值时。
DESC SELECT * FROM t1 WHERE key2 = 66;
看到key_len列就变成了5，比使用id列的索引时多了1。对于可变长度的索引列来说。
DESC SELECT * FROM t1 WHERE key1 = 'a';
key1列的类型是VARCHAR(100)，所以该列实际最多占用的存储空间就是400字节，又因为该列允许存储NULL值，所以key_len需要加1，又因为该列是可变长度列，所以key_len需要加2，所以最后ken_len的值就是403。
之前文章MySQL之InnoDB记录结构学习过InnoDB存储变长字段的实际长度不是可能占用1个字节或者2个字节，但是在计算key_len的时候都是以2字节计算？需要强调的一点是，执行计划的生成是在MySQL server层中的功能，并不是针对具体某个存储引擎的功能，MySQL在执行计划中输出key_len列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列，而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间到底是占用1个字节还是2个字节。
使用到联合索引idx_key_part的查询：
DESC SELECT * FROM t1 WHERE key_part1 = 'a';
执行计划的key_len列中看到值是403，这意味着MySQL在执行上述查询中只能用到idx_key_part索引的一个索引列。
DESC SELECT * FROM t1 WHERE key_part1 = 'a' AND key_part2 = 'b';
执行计划的ken_len列的值是806，说明执行这个查询的时候可以用到联合索引idx_key_part的两个索引列。
ref
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一种，ref列展示的就是与索引列作等值匹配的对象是什么。
比如对象只是一个常数：
DESC SELECT * FROM t1 WHERE key1 = 'a';
可以看到ref列的值是const，表明在使用idx_key1索引执行查询时，与key1列作等值匹配的对象是一个常数。
DESC SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
看到对被驱动表t2的访问方法是eq_ref，而对应的ref列的值是test_cost.t1.id，这说明在对被驱动表进行访问时会用到PRIMARY索引，也就是聚簇索引与一个列进行等值匹配的条件，于t2表的id作等值匹配的对象就是test_cost.t1.id列（注意这里把数据库名也写出来了）。
有的时候与索引列进行等值匹配的对象是一个函数。
DESC SELECT * FROM t1 INNER JOIN t2 ON t2.key1 = UPPER(t1.key1);
执行计划的第二条记录，可以看到对t2表采用ref访问方法执行查询，然后在查询计划的ref列里输出的是func，说明与t2表的key1列进行等值匹配的对象是一个函数。
rows
查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数。
DESC SELECT * FROM t1 WHERE key1 > 'z';
看到执行计划的rows列的值是1，这意味着查询优化器在经过分析使用idx_key1进行查询的成本之后，觉得满足key1 > 'z'这个条件的记录只有1条。
filtered
上篇文章MySQL之连接查询成本提出过一个condition filtering的概念，就是MySQL在计算驱动表扇出时采用的一个策略：
-
如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
-
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
DESC SELECT * FROM t1 WHERE key1 > 'z' AND common_field = 'a';
从执行计划的key列中可以看出来，该查询使用idx_key1索引来执行查询，从rows列可以看出满足key1 > 'z'的记录有1条。执行计划的filtered列就代表查询优化器预测在这1条记录中，有多少条记录满足其余的搜索条件，也就是common_field = 'a'这个条件的百分比。此处filtered列的值是10.00，说明查询优化器预测在1条记录中有10.00%的记录满足common_field = 'a'这个条件。
对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值。
DESC SELECT * FROM t1 INNER JOIN t2 ON t1.key1 = t2.key1 WHERE t1.common_field = 'a';
从执行计划中可以看出来，查询优化器打算把t1当作驱动表，t2当作被驱动表。我们可以看到驱动表t1表的执行计划的rows列为9991， filtered列为10.00，这意味着驱动表t1的扇出值就是9991 × 10.00% = 999.1，这说明还要对被驱动表执行大约999次查询。
2253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
