本章用以下 single_table 表作为例子详细介绍 Explain。
假设有两个和 single_table 表构造一模一样的 s1 、 s2 表，并且两个表里各有 10000 条记录。

  1. create table single_table (
  2.     id INT NOT NULL AUTO_INCREMENT,
  3.     key1 VARCHAR(100),
  4.     key2 INT,
  5.     key3 VARCHAR(100),
  6.     key_part1 VARCHAR(100),
  7.     key_part2 VARCHAR(100),
  8.     key_part3 VARCHAR(100),
  9.     common_field VARCHAR(100),
  10.     PRIMARY KEY (id),
  11.     KEY idx_key1 (key1),
  12.     UNIQUE KEY idx_key2 (key2),
  13.     KEY idx_key3 (key3),
  14.     KEY idx_key_part(key_part1, key_part2, key_part3)
  15. ) Engine=InnoDB CHARSET=utf8;

执行计划中各列详解

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

table

不论我们的查询语句有多复杂，一条查询语句涉及了多少个表，到最后都是需要对每个表进行单表访问的，
所以设计 MySQL 的人规定 Explain 语句输出的每条记录都对应着某个单表的访问方法，
该条记录的 table 列代表着该表的表名。

id

在一个查询语句中每个 select 关键字都对应一个唯一的 id。
下边这两种情况，在一个查询语句中会出现多个 select 关键字：

  • 查询中包含子查询的情况
  • 查询中包含 union 语句的情况

连接查询
对于连接查询来说，一个 select 关键字后边的 from 子句中跟随多个表，
所以在连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的 id 值是相同的，出现在前边的表是驱动表，出现在后边的表是被驱动表。
比如：explain select * from s1 inner join s2;
图片.png
所以从上图可以看出，查询优化器准备让 s1 表作为驱动表，让 s2 表作为被驱动表来执行查询。

查询中包含子查询
对于查询中包含子查询的情况，在执行计划中，每个 select 关键字都会对应一个唯一的 id 值，
比如：explain select * from s1 where key1 in (select key1 from s2) or key3 = ‘a’;
图片.png
从上图可以看出， s1 表在外层查询中，外层查询有一个独立的 select 关键字，

需要注意的是：查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。
所以，如果我们想知道查询优化器对某个包含子查询的语句是否进行了重写，直接查看执行计划就好了，
如果写的子查询语句，执行计划中 外层查询和内层查询的 id 相同，则证明：子查询转换为连接查询了。
比如：explain select * from s1 where key1 in (select key3 from s2 where common_field = ‘a’);
图片.png

查询中包含 Union 子句
对于查询中包含 Union 子句的情况，在执行计划中，每个 select 关键字都会对应一个唯一的 id 值，
不过查询中包含 Union 子句有一个特别的点，
比如：explain select from s1 union select from s2;
图片.png
这个语句的执行计划的第三条记录是什么？为什么 table 列值为 ？
下面对上面的疑问进行解答。
Union 子句会把多个查询的结果集 合并 并 去重，MySQL 使用的是内部的临时表。
正如上图所示，为了把 id 为 1 的查询和 id 为 2 的查询的结果集 合并 并 去重，
所以在内部创建了一个名为 的临时表。

跟 union 对比起来，union all 就不需要对最终的结果集去重，所以不需要使用临时表。
所以查询中包含 union all 子句的执行计划中，就没有那个 id 为 null 的记录，
如下所示：explain select from s1 union all select from s2;
图片.png

select_type

设计 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 类型。
连接查询也是 simple 类型，
有可能写的含有子查询的查询语句被查询优化器优化为 semi-join，可以通过看 select_type 值是否为 simple 辨别
比如：explain select * from s1 where key1 in (select key1 from s2);
图片.png

primary
对于包含 union 或者 union all 或者子查询的大查询来说，大查询是由几个小查询组成的，
其中最左边的 / 最外层的那个小查询的 select_type 值为 primary，
比如：explain select from s1 union select from s2;
图片.png

union
对于包含 union 或者 union all 的大查询来说，大查询是由几个小查询组成的，
其中除了最左边的那个小查询以外，其余的小查询的 select_type 值都是 union，例子在上图。

union result
MySQL 选择使用临时表来完成 union 子句的去重工作，针对该临时表的查询的 select_type 值为 union result，例子在上图。

subquery
如果包含子查询的查询语句不能够转为对应的 semi-join 的形式，并且该子查询是不相关子查询，
并且查询优化器决定采用「将该子查询物化，物化后不是连接，而是直接判断在不在物化表中」的方案来执行该子查询时，
该子查询的第一个 select 关键字代表的那个查询的 select_type 值为 subquery，

将 s1 表和 s2 表进行半连接 (semi-join) 的意思就是： 对于 s1 表的某条记录来说，我们只关心在 s2 表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配，最终的结果集中只保留 s1 表的记录。
将子查询结果集中的记录保存到临时表的过程被称为物化 (Materialize)

比如：explain select * from s1 where key1 in (select key1 from s2) or key3 = ‘a’;
（我在 MySQL 8 版本测试该查询的 select_type 值为 dependent subquery，但是小册子上说是 subquery）
图片.png
由于 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’;
图片.png
select_type 值为 dependent subquery 的查询可能会被执行多次。

dependent union
在包含 union 或者 union all 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 值为 dependent union。
比如：

  1. explain select * from s1 
  2. where key1 in (
  3.     select key1 from s2 where key1 = 'a' union 
  4.     select key1 from s1 where key1 = 'b' );

图片.png
这个查询比较复杂，大查询里包含了一个子查询，子查询里又是由 union 连起来的两个小查询。
从执行计划中可以看出来，select key1 from s2 where key1 = ‘a’; 这个小查询是子查询的中第一个查询，
所以它的 select_type 值为 dependent subquery。
select key1 from s1 where key1 = ‘b’; 这个小查询的 select_type 就是 dependent union。

derived
对于采用物化的方式，执行包含派生表的查询，该派生表对应的子查询的 select_type 值为 derived，
比如：explain select from (select key1, count() as c from s1 group by key1) as derived_s1 where c > 1;
图片.png
从执行计划中可以看出，id 为 2 的记录代表子查询的执行方式，它的 select_type 是 derived，
说明该子查询是以物化的方式执行的。
id 为 1 的记录代表外层查询的执行方式，table 列显示的是 ，
表示该查询是针对将派生表物化之后的表进行查询的。

materialized
当查询优化器在执行包含子查询的语句时，选择将子查询物化，之后与外层查询进行连接查询时，
该子查询对应的 select_type 值为 materialized，
比如：explain select * from s1 where key1 in (select key1 from s2);
（我在 MySQL 8 版本测试该查询的执行计划无 id = 2 的那一行记录，可能是我的表的数据量太少导致的，以下表格基于小册子上说的制作）

id select_type table type Extra
1 simple s1 all Using where
1 simple eq_ref null
2 materialized s2 index Using index

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

select_type 的值还可能是 uncacheable subquery、uncacheable union，由于不常用，小册子没用介绍。

partitions

没用介绍过。
一般情况下，我们的查询语句的执行计划的 partitions 值都是 null。

type

执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法，
该 type 的值就是该表的访问方法。
所有的访问方法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL。
下面详细介绍这些访问方法：
单表访问方法

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

const
const 访问方法：根据主键 或者 唯一二级索引与常数的等值匹配查询。
比如：explain select * from s1 where id = 5;
图片.png

eq_ref
在连接查询时，如果被驱动表是通过主键 或者 唯一二级索引列等值匹配的方式进行访问的
（如果该主键 或者 唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则该被驱动表的访问方法是 eq_ref，
比如：explain select * from s1 inner join s2 on s1.id = s2.id;
图片.png
从执行计划的结果中可以看出，MySQL打算将 s1 作为驱动表， s2 作为被驱动表，s2 的访问方法是 eq_ref ，表明在访问 s2 表的时候是通过主键的等值匹配进行访问的。

ref
ref 访问方法：根据普通二级索引（非唯一二级索引）与常数的等值匹配查询。
比如：explain select * from s1 where key1 = ‘1’;
图片.png

fulltext
全文索引，没有介绍。

ref_or_null
ref_or_null 访问方法：根据普通二级索引与常数的等值匹配，并且该普通二叉索引列的值可以是 null 值的查询。
首先要保证该普通二级索引允许为 null 值
比如：explain select * from s1 where key1 = ‘1’ or key1 is null;
图片.png

index_merge
一般情况下对于某个表的查询只能使用到一个索引，
但小册子在介绍单表访问方法时，特意强调了在某些场景下可以使用 Intersection、Union、Sort-Union 这三种索引合并的方式来执行查询。
比如：explain select * from s1 where key1 = ‘a’ or key3 = ‘a’;
图片.png

unique_subquery
类似于两表连接中被驱动表的 eq_ref 访问方法，
unique_subquery 访问方法：是针对在一些包含 in 子查询的查询语句中，查询优化器将 in 子查询转换为 exists 子查询，并且转换为 exists 子查询后使用主键进行等值匹配。
比如：explain select * from s1 where key2 in (select id from s2 where s1.key1 = s2.key1) or key3 = ‘a’;
type 值为 unique_subquery，所以该查询语句的 in 子句将被转换为 exists 子句，并会用到 s2表中 id 列的索引
图片.png

index_subquery
index_subquery 与 unique_subquery 类似，
只不过访问 in 子查询转换为 exists 子查询后，访问表时使用的是普通的索引，不是主键索引。
比如：explain select * from s1 where common_field in (select key3 from s2 where s1.key1 = s2.key1) or key3 = ‘a’;（我在 MySQL 8 版本测试该查询的执行计划 type 值不为 index_subquery，可能是我的表的数据量太少导致的，以下表格基于小册子上说的制作）

id select_type table type Extra
1 primary s1 all Using where
2 dependent subquery s2 index_subquery Using where

range
range 访问方法：使用索引（聚簇索引、二级索引）获取某些 范围区间 的记录。
比如：explain select from s1 where key1> ‘a’ and key1 < ‘c’;
图片.png
或者
比如：explain select from s1 where key1 in (‘a’, ‘b’, ‘c’);
图片.png

index
index 访问方法：只扫描某个二级索引的 B+ 树的叶子节点全部记录进行查询，不进行回表操作。
比如：explain select key_part2 from s1 where key_part3 = ‘a’;
存在名为 idx_key_part 的联合索引，索引列为 (key_part1, key_part2, key_part3)
图片.png

all
all 访问方式：扫描聚簇索引的B+树的叶子节点的全部记录。
比如：explain select * from s1;
图片.png

一般来说，这些访问方法按照我们介绍它们的顺序 性能依次变差。
其中除了 all 这个访问方法外，其余的访问方法都能用到索引，
除了 index_merge 访问方法外，其余的访问方法都最多只能用到一个索引。

possible_keys & key

possible_keys 表示在某个查询语句中，对某个表执行单表查询时，可能用到的索引有哪些，
key 表示实际用到的索引有哪些。
比如：explain select * from s1 where key1 > ‘z’ and key3 = ‘a’;
图片.png
上图中 possible_keys 值为 idx_key1, idx_key3，表示该查询可能会使用到 idx_key1,idx_key3 其中的某个索引，
然后 key 值为 idx_key3 ，表示经过查询优化器计算使用不同索引的成本后，最后决定使用 idx_key3 索引来执行查询比较划算。

有一个特殊的情况：某个查询的访问方法为 index 时，possible_keys 值可能会为 null，但是 key 列展示的是实际使用到的索引。
possible_keys 列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长的时间去比较，所以如果可以的话，尽量删除那些用不到的索引。

key_len

key_len 列表示当使用某个索引执行查询时，使用到的索引列的最大长度，单位为字节，
key_len 值是由这三个部分构成的：

  • 对于固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，

对于指定字符集的变长类型的索引列来说，比如：某个索引列的类型是 varchar(100)，
使用的字符集是 utf8，那么该列实际占用的最大存储空间就是 100 × 3 = 300 个字节。

  • 如果该索引列可以存储 null 值，则 key_len 比不可以存储 null 值时多 1 个字节。
  • 对于变长字段来说，都会有 2 个字节的空间来存储该变长列的实际长度。

对于固定长度类型的索引列来说，比如：explain select * from s1 where id = 5;
图片.png
由于 id 列的类型是 int，MySQL 中的 int 数据类型占 4 个字节，并且不可以存储 null 值，所以在使用该列的索引时 key_len 大小就是 4 。

当索引列可以存储 null 值时，比如：explain select * from s1 where key2 = 5;
图片.png
key2 列的数据类型也为 int，并且可以存储 null 值，可以看到 key_len 列就变成了 5。

对于可变长度的索引列来说，比如：explain select * from s1 where key1 = ‘a’;
图片.png
key1 列的数据类型是 varchar(100)，所以该列实际最多占用的存储空间就是 300 字节，
因为该列允许存储 null 值，所以 key_len 需要再加 1，
因为该列是可变长度列，所以 key_len 需要再加2，所以最后 ken_len 的值就是 303。

有的同学可能有疑问：在前面介绍 InnoDB 行格式的时候说，存储变长字段的实际长度可能占用 1 个字节 或者 2 个字节么？为什么现在固定占用 2 个字节呢？
这里需要强调的是，执行计划的生成是在 MySQL server 层中的功能，并不是针对具体某个存储引擎的功能，设计 MySQL 的人在执行计划中输出 key_len 列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列，而不是为了准确的说明针对某个具体存储引擎存储变长字段的实际长度占用的空间。

ref

当使用索引列等值匹配的条件去执行查询时，
也就是在访问方法是 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 其中之一时，
ref 列展示的就是与索引列作等值匹配的是什么，是一个常数或者是某个列。
比如：explain select * from s1 where key1 = ‘a’;
图片.png
可以看到 ref 列的值是 const，表明在使用 idx_key1 索引执行查询时，与 key1 列作等值匹配的对象是一个常数，

比如：explain select * from s1 inner join s2 on s1.id = s2.id;
图片.png
对被驱动表 s2 的访问方法是 eq_ref ，对应的 ref 列的值是 test.s1.id，
说明，与 s2 表的 id 列作等值匹配的对象就是 s1 表的 id 列。

有的时候与索引列进行等值匹配的对象是一个函数，
比如：explain select * from s1 inner join s2 on s2.key1 = upper(s1.key1);

id select_type table type ref Extra
1 simple s1 all null null
1 simple s2 ref func Using index condition

说明与 s2 表的 key1 列进行等值匹配的对象是一个函数。

rows

如果使用全表扫描的方式对某个表执行查询，执行计划的 rows 列就代表预计需要扫描的行数，
如果使用索引对某个表执行查询时，执行计划的 rows 列就代表预计扫描的索引记录行数。
比如：explain select * from s1 where key1 > ‘akey1’;
图片.png
我们看到执行计划的 rows 列的值是 5，这意味着：查询优化器在经过分析使用 idx_key1 进行查询的成本之后，觉得满足 key1 > ‘akey1’ 这个条件的记录只有 5 条。

filtered

之前的章节在分析连接查询的成本时提出过一个 condition filtering 的概念，
就是 MySQL 在计算驱动表扇出时采用的一个策略：

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

比方：explain select * from s1 where key1 > ‘a’ and common_field = ‘a’;
图片.png
该查询使用 idx_key1 索引来执行查询，
从 rows 列可以看出：满足 key1 > ‘a’ 的记录有 6 条。
执行计划的 filtered 列就代表查询优化器预测在这 6 条记录中，有多少条记录满足其余的搜索条件，也就是 common_field = ‘a’ 这个条件的百分比。
此处 filtered 列的值是 16.67，说明查询优化器预测在这 6 条记录中有 16.67% 的记录满足 common_field = ‘a’` 这个条件。

对于单表查询来说，这个 filtered 列的值没太大意义，我们更关注在连接查询中，驱动表对应的执行计划记录的的 filtered 值。
比如：explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = ‘a’;
图片.png
从执行计划中可以看出，查询优化器打算把 s1 当作驱动表， s2 当作被驱动表。
驱动表 s1 表的执行计划的 rows 列为 6，filtered 列为 16.67，这意味着：驱动表 s1 的扇出值就是 6 × 16.67% = 1，这说明还要对被驱动表执行大约 1 次查询。

Extra

Extra 列用来说明一些额外的信息。
我们可以通过这些额外信息来更准确的理解 MySQL 到底将如何执行给定的查询语句。
MySQL 提供的额外信息有好几十个，本小节只挑一些常见的或者比较重要的额外信息介绍。

No tables used
当查询语句没有 from 子句时，该额外信息会被显示。
比如：explain select 1;
图片.png

Impossible where
当查询语句的 where 子句永远为 false 时，该额外信息会被显示。
比如：explain select * from s1 where 1 != null;
图片.png

No matching min / max row
当查询列表处有 min 或者 max 聚集函数，但是并没有符合 where 子句中的搜索条件的记录时，该额外信息会被显示。
比如：explain select min(key1) from s1 where key1 = ‘abcdefg’;
图片.png

Using index
当查询列表以及搜索条件中只包含属于某个索引的列，也就是：在可以使用索引覆盖的情况下，
该额外信息会被显示。
比如：explain select key1 from s1 where key1 = ‘a’;
图片.png
这个查询中只需要用到 idx_key1 索引而不需要回表操作。

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)。
如果在查询语句的执行过程中使用索引条件下推这个特性，Extra 列将会显示 Using index condition。
比如：explain select * from s1 where key1 > ‘z’ and key1 like ‘%a’;
图片.png

Using where
当使用全表扫描执行对某个表的查询，并且该查询的 where 子句中有针对该表的搜索条件时，该额外信息会被显示。
比如：explain select from s1 where common_field = ‘a’;
图片.png
或者
当使用索引访问执行对某个表的查询，并且该查询的 where 子句中有除了该索引包含的列之外的其他搜索条件时，该额外信息也会被显示。
比如：explain select from s1 where key1 = ‘a’ and common_field = ‘a’;
图片.png
这个查询虽然使用 idx_key1 索引执行查询，但是搜索条件中除了包含 key1 的等值匹配外，还包含 common_field 的搜索条件。

Using join buffer (Block Nested Loop)
在连接查询执行过程过，当被驱动表不能有效的利用索引加快访问速度的时候，
MySQL 一般会为其分配一块名叫 join buffer 的内存块来加快查询速度，
也就是前面章节介绍的基于块的嵌套循环算法。
比如：explain select * from s1 inner join s2 on s1.common_field = s2.common_field;
图片.png

Not exists
在左外连接的查询语句里，如果 where 子句中包含要求被驱动表的某个列等于 null 值的搜索条件，而且那个列又是不允许存储 null 值的，该额外信息会被显示。
比如：explain select * from s1 left join s2 on s1.key1 = s2.key1 where s2.id is null;
图片.png
上述查询中 s1 表是驱动表，s2 表是被驱动表，s2.id 列是不允许存储 null 值的，而 where 子句中又包含s2.id is null 的搜索条件，这意味着：必定是驱动表的记录在被驱动表中找不到匹配 on 子句条件的记录，才会把该驱动表的记录加入到结果集。
所以对于某条驱动表中的记录来说，如果能在被驱动表中找到 1 条符合 on 子句条件的记录，那么该驱动表的记录就不会被加入到结果集。
也就是说：没有必要到被驱动表中找到全部符合 on 子句条件的记录，这样可以节省性能。
右外连接可以被转换为左外连接，所以就不介绍右外连接的情况了。

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

Zero limit
当我们的 limit 子句的参数为 0 时，表示不打算从表中读出任何记录，该额外信息会被显示。
比如：explain select * from s1 limit 0;
图片.png

Using filesort
有一些情况下，对结果集中的记录进行排序操作是可以使用到索引的。
但是很多情况下，对结果集中的记录进行排序操作无法使用到索引，
只能在内存中（记录较少的时候）或者磁盘中（记录较多的时候）进行排序，
设计 MySQL 的人把这种在内存中或者磁盘上进行排序的方式统称为文件排序 (filesort)。
如果某个查询使用文件排序的方式进行排序操作，该额外信息会被显示。
比如：explain select * from s1 order by common_field limit 10;
图片.png
如果查询中需要使用 filesort 的方式进行排序的记录非常多，那么这个过程很耗费性能，
最好想办法将使用文件排序的执行方式改为使用索引进行排序。
16 | “order by”是怎么工作的？

Using temporary
在许多查询的执行过程中，MySQL 可能会借助临时表来完成一些功能，比如：去重、排序等。
我们在执行包含 distinct、group by、union 等子句的查询过程中，如果不能有效利用索引完成查询，MySQL 很有可能通过建立内部的临时表来执行查询。
如果查询的执行过程中使用了内部的临时表，该额外信息会被显示。
比如：explain select distinct common_field from s1;
图片.png
或者
比如：explain select common_field, count(*) as amount from s1 group by common_field;

id select_type table type ref Extra
1 simple s1 all null Using temporary; Using filesort

有一点需要注意的是：上述执行计划的 Extra 列不仅包含 Using temporary 额外信息，
还包含了 Using filesort，这是因为 MySQL 会在包含 group by 子句的查询中默认添加上 order by 子句。
也就是说：上述的查询和下边这个查询等价：explain select common_field, count(*) as amount from s1 group by common_field order by common_field;
如果我们并不想为包含 group by 子句的查询进行排序，需要我们显式的写上 order by null。

执行计划中出现 Using temporary 并不是一个好的征兆，因为建立与维护临时表要付出很大成本的，
所以我们最好能使用索引来替代临时表。

Start temporary, End temporary
前边介绍子查询的时候说过，查询优化器会优先尝试将 in 子查询转换成 semi-join，
而 semi-join 又有好多种执行策略，
当执行策略为 DuplicateWeedout 时，也就是通过建立临时表来实现为外层查询中的记录进行去重操作，
执行计划中驱动表查询的 Extra 列将显示 Start temporary 额外信息，
被驱动表查询的 Extra 列将显示 End temporary 额外信息。
比如：explain select * from s1 where key1 in(select key3 from s2 where common_field = ‘a’);

id select_type table type key ref Extra
1 simple s2 all null null Using where; Start temporary
1 simple s1 ref idx_key1 s2.key3 End temporary

LooseScan
在将 in 子查询转为 semi-join 时，如果采用的是 LooseScan 执行策略，
则执行计划中驱动表查询的 Extra 列将显示 LooseScan 额外信息。
比如：explain select * from s1 where key3 in (select key1 from s2 where key1 > ‘z’);
图片.png

FirstMatch(tbl_name)
在将 in 子查询转为 semi-join 时，如果采用的是 FirstMatch 执行策略，
则执行计划中驱动表查询的 Extra 列将显示 FirstMatch(tbl_name) 额外信息，
比如：explain select * from s1 where common_field in (select key1 from s2 where s1.key3 = s2.key3);
图片.png

Json 格式的执行计划

上边介绍的 explain 语句输出中缺少了一个衡量执行计划好坏的重要属性 — 成本。
设计 MySQL 的人为我们提供了一种查看某个执行计划花费的成本的方式：在 explain 单词和真正的查询语句中间加上 fromat = json。
这样我们就可以得到一个 json 格式的执行计划，里面包含了该计划花费的成本。
比如：explain format = json select * from s1 inner join s2 on s1.key1 = s2.key2 where s1.common_field = ‘a’;

  1. {
  2.     "query_block": {
  3.           // 整个查询语句只有 1 个 select 关键字，该关键字对应的 id 号为 1
  4.         "select_id": 1, 
  5.         "cost_info": {
  6.               // 整个查询的执行成本预计为 1.95
  7.             "query_cost": "1.95"
  8.         }, 
  9.           // 几个表之间采用嵌套循环连接算法执行
  10.           // 以下是参与嵌套循环连接算法的各个表的信息,涉及 s1 表和 s2 表
  11.         "nested_loop": [
  12.           {
  13.             // s1 表的信息
  14.             "table": {
  15.               // s1 表在前，说明它是驱动表
  16.               "table_name": "s1", 
  17.               // 访问方法
  18.               "access_type": "ALL", 
  19.               // 可能会使用的索引
  20.               "possible_keys": [
  21.                 "idx_key1"
  22.               ], 
  23.               // 查询一次 s1 表大致需要扫描 6 条记录
  24.               "rows_examined_per_scan": 6, 
  25.               // 驱动表 s1 的扇出是 1
  26.               "rows_produced_per_join": 1, 
  27.               // condition filtering 代表的百分比
  28.               "filtered": "16.67", 
  29.               "cost_info": {
  30.                 // 下文详细介绍
  31.                 "read_cost": "0.75", 
  32.                 // 下文详细介绍
  33.                 "eval_cost": "0.10", 
  34.                 // 单次查询 s1 表总共的成本
  35.                 "prefix_cost": "0.85", 
  36.                 // 此次查询需要读取的数据量
  37.                 "data_read_per_join": "1K"
  38.               }, 
  39.               // 执行查询中涉及到的列
  40.               "used_columns": [
  41.                 "id", 
  42.                 "key1", 
  43.                 "key2", 
  44.                 "key3", 
  45.                 "key_part1", 
  46.                 "key_part2", 
  47.                 "key_part3", 
  48.                 "common_field"
  49.               ], 
  50.               // 对 s1 表访问时，针对单表查询的条件
  51.               "attached_condition": "((`test`.`s1`.`common_field` = 'a') and (`test`.`s1`.`key1` is not null))"
  52.             }
  53.           }, 
  54.           {
  55.             // s2 表的信息
  56.             "table": {
  57.               // s2 表在后，说明它是被驱动表
  58.               "table_name": "s2", 
  59.               "access_type": "eq_ref", 
  60.               "possible_keys": [
  61.                 "idx_key2"
  62.               ], 
  63.               // 实际使用的索引
  64.               "key": "idx_key2", 
  65.               // 使用到的索引列
  66.               "used_key_parts": [
  67.                 "key2"
  68.               ], 
  69.               // key_len
  70.               "key_length": "5", 
  71.               // 与 key2 列进行等值匹配的对象
  72.               "ref": [
  73.                 "test.s1.key1"
  74.               ], 
  75.               "rows_examined_per_scan": 1, 
  76.               "rows_produced_per_join": 1, 
  77.               "filtered": "100.00", 
  78.               // s2 表使用索引进行查询的搜索条件
  79.               "index_condition": "(cast(`test`.`s1`.`key1` as double) = cast(`test`.`s2`.`key2` as double))", 
  80.               "cost_info": {
  81.                 "read_cost": "1.00", 
  82.                 "eval_cost": "0.10", 
  83.                 // 单次查询 s1、多次查询 s2 表总共的成本
  84.                 "prefix_cost": "1.95", 
  85.                 "data_read_per_join": "1K"
  86.               }, 
  87.               "used_columns": [
  88.                 "id", 
  89.                 "key1", 
  90.                 "key2", 
  91.                 "key3", 
  92.                 "key_part1", 
  93.                 "key_part2", 
  94.                 "key_part3", 
  95.                 "common_field"
  96.               ]
  97.             }
  98.           }
  99.         ]
  101.     }
  102. }

先来看 s1 表的 cost_info

  1. "cost_info": {
  2.   // 下文详细介绍
  3.   "read_cost": "0.75", 
  4.   // 下文详细介绍
  5.   "eval_cost": "0.10", 
  6.   // 单次查询 s1 表总共的成本
  7.   "prefix_cost": "0.85", 
  8.   // 此次查询需要读取的数据量
  9.   "data_read_per_join": "1K"
  10. },

read_cost 由下边这两部分组成的：

  • IO 成本
  • 检测 rows × (1 - filter) 条记录的 CPU 成本 （rows 即这里的 rows_examined_per_scan）

eval_cost：检测 rows × filter 条记录的成本。
prefix_cost：单独查询 s1 表的成本，也就是：read_cost + eval_cost

再来看 s2 表的 cost_info

  1. "cost_info": {
  2.   "read_cost": "1.00", 
  3.   "eval_cost": "0.10", 
  4.   // 单次查询 s1、多次查询 s2 表总共的成本
  5.   "prefix_cost": "1.95", 
  6.   "data_read_per_join": "1K"
  7. },

由于 s2 表是被驱动表，所以可能被读取多次，
这里的 read_cost 和 eval_cost 是多次访问 s2 表后累加的值，
prefix_cost 的值代表整个连接查询预计的成本，也就是单次查询 s1 表和多次查询 s2 表后的总共成本，
也就是：s2.prefix_cost = s2.read_cost + s2.eval_cost + s1.prefix_cost

Extented Explain

在我们使用 explain 语句查看了某个查询的执行计划后，紧接着还可以使用 show warnings 语句查看与这个查询的执行计划有关的一些扩展信息。
比如：explain select s1.key1, s2.key1 from s1 left join s2 on s1.key1 = s2.key1 where s2.common_field is not null;
图片.png
然后执行：show warnings;
图片.png
/ select#1 / select test.s1.key1 AS key1,test.s2.key1 AS key1 from test.s1 join test.s2 where ((test.s2.key1 = test.s1.key1) and (test.s2.common_field is not null))

可以看到 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 将如何执行查询语句的一个参考依据。