MySQL EXPLAIN 一： 输出格式详解

本文详细介绍 MySQL 数据库的 EXPLAIN 语句，旨在帮助 MySQL 开发人员更好的通过 EXPLAIN 语句分析 Sql 语句的执行计划。如有不正之处，欢迎批评指正。

为了保证内容的权威性，我选择了翻译 Mysql5.7 官方文档8.8.2 EXPLAIN Output Format 章节的内容。

英文地址：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

翻译内容如下：

8.8.2 EXPLAIN 数据格式

EXPLAIN 语句提供关于 MySQL 执行语句的信息。它同 SELECT、DELETE、INSERT、REPLACE、UPDATE语句一起使用，如：EXPLAIN SELECT * FROM TABLE。

EXPLAIN 为 SELECT 语句中使用到的每个表返回一行信息。它在输出中按照MySQL在处理语句时读取它们的顺序列出这些表。MySQL 使用 nested-loop join 解析所有的连接（joins）。这意味着 MySQL 从第一个表中读取一行记录，然后在第二个表、第三个表查找匹配行，以此类推。当所有的表被处理完成后， MySQL 输出选中的列，并且通过表数据（the table list）回溯有更多匹配行的表。从这个表中读取下一行数据，继续通过后续表进行行匹配。

EXPLAIN 语句的输出包括分区信息。同时，EXPLAIN 语句为 SELECT 语句生成扩展信息，并且随着 EXPLAIN 的SHOW WARNINGS 一起显示（见8.8.3 Extended EXPLAIN Output Format）。

注意

在旧的 MySQL 版本中，分区和扩展信息是通过 EXPLAIN PARTITIONS 和 EXPLAIN EXTENDED 语句生成的。为了向后的兼容性，这些语法还有可以使用的，但是现在分区和扩展信息的输出在默认情况下是启用的，所以 PARTITIONS 和 EXTENDED 关键词是多余的、不被推荐的。使用它们会导致一个警告，并且在 MySQL 将来的发行版本中，它们将要被从 EXPLAIN 语法中移除。

注意

MySQL Workbench 有一个可视化的解释能力，它为 EXPLAIN 语句的输出提供一个可视化的表现。见Tutorial: Using Visual Explain to Improve Query Performance。

• EXPLAIN 输出列
• EXPLAIN 连接类型
• EXPLAIN 扩展信息
• EXPLAIN 输出解释

EXPLAIN 输出列

这个章节描述 EXPLAIN 语句生成的输出列。之后的章节提供 type 和 Extra 列的附加信息。

EXPLAIN 语句的每个输出行都提供了一个表的信息。在 Table 8.1“EXPLAIN Output Columns”中总结了输出行包含的值，并加以详细的说明。表（Table 8.1）的第一列显示列名，第二列提供了等效的 JSON 属性名称，在输出格式为 JSON 时使用。

Table 8.1 EXPLAIN Output Columns

Column	JSON Name	含义
id	select_id	select 标识符
select_type	None	select 类型
table	table_name	输出行描述的表的表名
partitions	partitions	匹配的分区
type	access_type	连接类型
possible_keys	possible_keys	可供选择使用的索引
key	key	实际使用的索引
key_len	key_length	实际使用的索引的长度
ref	ref	与索引进行比较的列，也就是关联表使用的列
rows	rows	将要被检查的估算的行数
filtered	filtered	被表条件过滤的行数的百分比
Extra	None	附件信息

注意

如果 JSON 属性的值为 NULL ，在 JSON-formatted 格式的 EXPLAIN 输出中将不被显示。

• id (JSON name: select_id)
  SELECT 标识符，该值为 SELECT 在查询中的顺序号，如果当前行引用的是其他行的联合查询结果，该值将被设置为 NULL。在这种情况下，表中的列将显示一个类似于<union M,N>的值，标识该行为 id 值为 M 和 N 的联合。

• select_type (JSON name: none)
  SELECT 的类型，它可以是下表中展示的任意一个值。如果该值为 SIMPLE 或者 PRIMARY,一个 JSON 格式的 EXPLAIN 将以一个 query_block 的属性显示 SELECT 的类型。JSON 的名称如下表所示。

select_type Value	JSON Name	含义
SIMPLE	None	简单的 SELECT (没有使用 UNION 或者 subqueries)
PRIMARY	None	最外层的 SELECT
UNION	None	在 UNION 查询中的第二个或者之后的 SELECT 语句
DEPENDENT UNION	dependent (true)	在 UNION 查询中的第二个或者之后的 SELECT 语句，依赖外层的查询
UNION RESULT	union_result	联合查询的结果
SUBQUERY	None	在子查询中的第一个 SELECT
DEPENDENT SUBQUERY	dependent (true)	在 subquery （子查询）中的第一个 SELECT，依赖于外层查询
DERIVED	None	衍生的表 SELECT(由 FROM 中的 subquery 引起)
MATERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable (false)	子查询的结果不能被缓存，必须重新评估外部查询的每一行
UNCACHEABLE UNION	cacheable (false)	在 UNION(联合查询)中的第二个或者之后的多个 select 都属于不可缓存的子查询（见 UNCACHEABLE SUBQUERY）

依赖通常意味着一个相关子查询的使用。见Section 13.2.10.7, “Correlated Subqueries”

相关子查询的评估不同于不可缓存的子查询评估。关于相关子查询，子查询只重新评估一次外部上下文环境的每一组变量的不同值。关于不可缓存的子查询，子查询重新评估外部上下文环境的每一行数据。

子查询的缓存不同与 query cache 中查询结果的缓存（见 Section 8.10.3.1, “How the Query Cache Operates”）。子查询缓存发生在查询执行期间，而 query cache 用来存储查询执行完成后的结果。

当你为 EXPLAIN 指定了 JSON 格式，它的输出中没有单一的属性直接等同于 select_type，query_block 属性对应一个给定的 SELECT。它的属性等同于大多数的 SELECT 子查询类型，并且只显示可用的（例如 materialized_from_subquery），并在适当的时候进行显示。JSON 属性中没有与 SIMPLE 或者 PRIMARY等同的属性。

对于 non-SELECT 语句，select_type 显示受影响的表的语句的类型。例如：对于 DELETE 语句，select_type 的值为 DELETE。

• table (JSON name: table_name)
  输出的行所指向的表的名称，可以是下列值之一：
  
  • <union M,N>：该行指的是 ID 值为 M 和 N 的行的联合。
  • <derived N>：该行指的是衍生表结果，该结果为 ID 值为 N 的行。可能会生成一个衍生表，例如：在 FROM 语句中存在一个 from a subquery。
  • <subqueryN>：该行指的是一个物化子查询的结果，该结果为 ID 值为 N 的行。见Section 8.2.2.2, “Optimizing Subqueries with Materialization”
• partitions (JSON name: partitions)
  分区的记录将被查询匹配。对于非分区表，该值为 NULL。见Section 22.3.5, “Obtaining Information About Partitions”。
• type (JSON name: access_type)
  连接类型。不同类型的描述见EXPLAIN Join Types
• possible_keys (JSON name: possible_keys)
  MySQL 可以选择 possible_keys 列指示的索引查找表中的行数据。注意，possible_keys 列完全独立于 EXPLAIN 输出中显示的表的顺序。这意味着 possible_keys 列中的一些 key 可能无法使用在实践中生成的表的顺序。
  如果该列的值是 NULL（或未定义JSON格式输出），说明表里没有相关的索引。在这种情况下，您可以通过检查 WHERE 子句来检查其是否引用一些列或合适的包含索引的列，从而提高查询的性能。如果是这样的话，创建一个和适当的索引并且通过 EXPLAIN 再次检查查询语句，见 Section 13.1.8, “ALTER TABLE Syntax”。
  使用 SHOW INDEX FROM tbl_name 语句查看表（tbl_name ）中已有的索引。
• key (JSON name: key)
  key 列表明 MySQL 实际决定使用的索引。如果 MySQL 决定通过 possible_keys 中的某个索引查找行，那个索引被列在 key 列中。
  key 可以命名一个不包含在 possible_keys 列中的索引。这可能发生在 possible_keys 中没有为查询行数据找到合适的索引，但是所有被查询选择的列可能包含其他的索引列。也就是说，命名的索引覆盖了选中的列，因此尽管它不用于确定要检索的行，但是索引扫描比数据行扫描更有效。
  关于 InnoDB存储引擎，即使要查询一个主键，次要索引可能会 cover （覆盖）选中的列，这是因为 InnoDB 使用每个次要索引存储主键值。如果 key 值为 NULL，表明 MySQL 没有找到执行高效查询的索引。
  可以在你的查询语句中使用 FORCE INDEX、USE INDEX、IGNORE INDEX，强制 MySQL 使用或者忽略 possible_keys 列中的索引。见 Section 8.9.4, “Index Hints”。
  关于 MyISAM 存储引擎，运行 ANALYZE TABLE 帮助优化器选择更好的索引。myisamchk –analyze 可以做到与 ANALYZE TABLE 一样的效果。见 Section 13.7.2.1, “ANALYZE TABLE Syntax”
• key_len (JSON name: key_length) 和 Section 7.6, “MyISAM Table Maintenance and Crash Recovery”。
  key_len 列指示 MySQL 决定使用的 key 的长度。key_len 值能够使你判定 MySQL 实际使用了 multiple-part key 中的多少个 part。如果 key 列的值为 NULL，则 key_len 列值也为 NULL。
• ref (JSON name: ref)
  ref 列显示列或常量，这些列或者常量与 key 列中的索引进行比较，以便从表中选择行。
  如果 ref 的值为 func，那么这个值通常是一些函数执行的结果。如果想要确定是哪个函数，在 EXPLAIN 的后面增加 SHOW WARNINGS 语句可以查看扩展的 EXPLAIN 输出。该函数实际上可能是一个算术运算符。
• rows (JSON name: rows)
  这个 rows 就是MySQL 认为在执行查询是必须进行逐行检查的行的数据量。
  对于 InnoDB 类型的表，这个数字只是一个估计，并不总是精确的。
• filtered (JSON name: filtered)
  这个 filtered 就是一个估算的将要被表的查询条件过滤的 rows 的百分比。rows 显示估算的被检查的行的数量， rows × filtered / 100 显示将要与前一个表进行连接的行数。
• Extra (JSON name: none)
  这列包含关于 MySQL 如何解析查询语句的附加信息。该列的不同值的描述见 EXPLAIN Extra Information。
  这里没有单独的 JSON 属性与 Extra 列的值进行对应；然而，本列中可能出现的值作为 JSON 属性公开，或作为 message 属性的文本公开。

本节的内容先翻译到这里，关于连接类型、扩展信息、输出解释三部分，会在后续三篇博客中进行翻译。