MySQL 命令之 EXPLAIN 执行计划

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

什么是执行计划？

执行计划 是指一条 SQL 语句在经过 MySQL 查询优化器 的优化会后，具体的执行方式。

执行计划通常用于 SQL 性能分析、优化等场景。通过 EXPLAIN 的结果，可以了解到如数据表的查询顺序、数据查询操作的操作类型、哪些索引可以 被命中、哪些索引 实际会命中、每个数据表有多少行记录被查询等信息。

如何获取执行计划？

MySQL 为我们提供了 EXPLAIN 命令，来获取执行计划的相关信息。

需要注意的是，EXPLAIN 语句并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

EXPLAIN 执行计划支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。我们一般多用于分析 SELECT 查询语句，使用起来非常简单，语法如下：

EXPLAIN + SELECT 查询语句；

示例

mysql> EXPLAIN SELECT * FROM (SELECT nid,name FROM tb1 WHERE nid < 10) AS B;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | PRIMARY     |            | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
|  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using WHERE |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

可以看到，执行计划结果中共有 12 列，各列代表的含义总结如下表：

列名	含义
id	SELECT 查询的序列标识符
select_type	SELECT 关键字对应的查询类型
table	用到的表名
partitions	匹配的分区，对于未分区的表，值为 NULL
type	表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
key_len	所选索引的长度
ref	当使用索引等值查询时，与索引作比较的列或常量
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

如何分析 EXPLAIN 结果？

为了分析 EXPLAIN 语句的执行结果，我们需要搞懂执行计划中的重要字段。

id

查询顺序标识符，每个 SELECT 都会自动分配一个唯一的标识符。

列数字越大越先执行，如果数字一样大，那么就从上往下依次执行，id 为 null 的就表示这是一个结果集，不需要使用它来进行查询，例如使用 UNION 连接可能为 null

select_type 查询类型

• SIMPLE：表示不需要 UNION 操作或者不包含子查询的简单 SELECT 查询。有连接查询时，外层的查询为 SIMPLE，且只有一个
• PRIMARY：一个需要 UNION 操作或者含有子查询的 SELECT，位于最外层的单位查询的 select_type 即为 PRIMARY。且只有一个
• UNION：UNION 连接的两个 SELECT 查询，第一个查询是 dervied 派生表，除了第一个表外，第二个以后的表 select_type 都是UNION
• DEPENDENT UNION：与 UNION 一样，出现在 UNION 或 UNION ALL 语句中，但是这个查询要受到外部查询的影响
• UNION RESULT：包含 UNION 的结果集，在 UNION 和 UNION ALL 语句中,因为它不需要参与查询，所以 id 字段为 null
• SUBQUERY：除了 FROM 字句中包含的子查询外，其他地方出现的子查询都可能是 SUBQUERY
• DEPENDENT SUBQUERY：与 DEPENDENT UNION 类似，表示这个 SUBQUERY 的查询要受到外部表查询的影响
• DERIVED：FROM 字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套 SELECT

table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为 null，也可能是以下列出的值：

• <unionM,N> ：表示这个是临时表，本行引用了 id 为 M 和 N 的行的 UNION 结果；
• <derivedN> ：表示这个是临时表，本行引用了 id 为 N 的表所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
• <subqueryN>：表示这个是临时表，本行引用了 id 为 N 的表所产生的的物化子查询结果。

type

依次从好到差：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL，除了 all 之外，其他的 type 都可以使用到索引，除了 index_merge 之外，其他的 type 只可以用到一个索引

• system：表中只有一行数据或者是空表，且只能用于 MyISAM 和 MEMORY 表。如果是 InnoDB 引擎表，type 列在这个情况通常都是 all 或者 index，这是 const 联接类型的一个特例。
  SELECT * FROM (SELECT nid FROM tb1 WHERE nid = 1) AS A;

• const：使用唯一索引或者主键，返回记录一定是 1 行记录的等值 WHERE 条件时，通常 type 是const。其他数据库也叫做唯一索引扫描。
  SELECT nid FROM tb1 WHERE nid = 2;

• eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为 not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现 eq_ref。
  SELECT tb2.nid,tb1.name FROM tb2 left join tb1 on tb2.nid = tb1.nid;

• ref：使用普通索引作为查询条件，查询结果可能找到多个符合条件的行。
  此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询。不像 eq_ref 那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。

• fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，MySQL 不管代价，优先选择使用全文索引

• ref_or_null：与 ref 方法类似，只是增加了 null 值的比较。实际用的不多。

• unique_subquery：用于 WHERE 中的 IN 形式子查询，子查询返回不重复值唯一值

• index_subquery：用于 IN 形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。

• range：对索引列进行范围查询，执行计划中的 key 列表示哪个索引被使用了。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个。

• index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见 and ，or 的条件使用了不同的索引，官方排序这个在 ref_or_null 之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如 range

• index：表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据。index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据。 当是这种情况时, Extra 字段 会显示 Using index。比如, 我们查询的 name 字段恰好是一个索引, 因此我们直接从索引中获取数据就可以满足查询的需求了, 而不需要查询表中的数据。 因此这样的情况下, type 的值是 index, 并且 Extra 的值是 Using index。

• all：这个就是全表扫描数据文件，然后再在 server 层进行过滤返回符合要求的记录。这个类型的查询是性能最差的查询之一，最不建议使用

possible_keys

possible_keys 列表示 MySQL 执行查询时可能用到的索引。如果这一列为 NULL ，则表示没有可能用到的索引；这种情况下，需要检查 WHERE 语句中所使用的的列，看是否可以通过给这些列中某个或多个添加索引的方法来提高查询性能。

key（重要）

key 列表示 MySQL 实际使用到的索引。如果为 NULL，则表示未用到索引。select_type 为 index_merge 时，这里可能出现两个以上的索引，其他的 select_type 这里只会出现一个

key_len

key_len 列表示 MySQL 实际使用的索引的最大长度；当使用到联合索引时，有可能是多个列的长度和。在满足需求的前提下越短越好。如果 key 列显示 NULL ，则 key_len 列也显示 NULL 。

注意，MySQL 的 ICP 特性使用到的索引不会计入其中。另外，key_len 只计算 WHERE 条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到 key_len 中。

ref

如果是使用的常数等值查询，这里会显示 const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

MySQL 估计为了找到所需的行而要读取的行数，只是预估值，数值越小越好。

extra（重要）

这列包含了 MySQL 解析查询的额外信息，通过这些信息，可以更准确的理解 MySQL 到底是如何执行查询的。常见的值如下：

• distinct：在 SELECT 部分使用了 distinc 关键字
• no tables used：不带 FROM 字句的查询或者 From dual 查询
• using filesort：排序时无法使用到索引时，就会出现这个。常见于 order by 和 group by 语句中
• using index：查询时不需要回表查询，直接通过覆盖索引就可以获取查询的数据。
• using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x之后的版本优化关联查询的BNL，BKA特性。主要是减少内表的循环数量以及比较顺序地扫描查询。
• using intersect：表示使用and的各个索引的条件时，该信息表示是从处理结果获取交集
• using UNION：表示使用or连接各个使用索引的条件时，该信息表示从处理结果获取并集
• using sort_UNION 和 using sort_intersection：与前面两个对应的类似，只是他们是出现在用 and 和 or 查询信息量大时，先查询主键，然后进行排序合并后，才能读取记录并返回。
• using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
• using WHERE：表示存储引擎返回的记录并不是所有的都满足查询条件，需要在 server 层进行过滤。查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra 列显示 using index condition。mysql 服务器将在存储引擎检索行后再进行过滤，许多 WHERE 条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带 WHERE 子句的查询都会显示“Using WHERE”。有时“Using WHERE”的出现就是一个暗示：查询可受益于不同的索引。
• firstmatch(tb_name)：5.6.x开始引入的优化子查询的新特性之一，常见于 WHERE 字句含有 IN() 类型的子查询。如果内表的数据量比较大，就可能出现这个
• loosescan(m..n)：5.6.x之后引入的优化子查询的新特性之一，在 IN() 类型的子查询中，子查询返回的可能有重复记录时，就可能出现这个

常见的值如下：

• Using filesort：在排序时使用了外部的索引排序，没有用到表内索引进行排序。
• Using temporary：MySQL 需要创建临时表来存储查询的结果，常见于 ORDER BY 和 GROUP BY。
• Using index：表明查询使用了覆盖索引，不用回表，查询效率非常高。
• Using index condition：表示查询优化器选择使用了索引条件下推这个特性。
• Using where：表明查询使用了 WHERE 子句进行条件过滤。一般在没有使用到索引的时候会出现。
• **Using join buffer (Block Nested Loop)**：连表查询的方式，表示当被驱动表的没有使用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查询。

这里提醒下，当 Extra 列包含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，需要尽可能避免。

filtered

使用 explain extended 时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用 explain extended 了。这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。