MySQL EXPLAIN 一：输出格式详解

小鱼儿 2022-06-10 14:19 167阅读 0赞

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

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

*  [EXPLAIN 输出列][EXPLAIN]
 *  [EXPLAIN 连接类型][EXPLAIN 1]
 *  [EXPLAIN 扩展信息][EXPLAIN 2]
 *  [EXPLAIN 输出解释][EXPLAIN 3]

### EXPLAIN 输出列 ###

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

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

Table 8.1 EXPLAIN Output Columns

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

> #### 注意 ####
> 
> 如果 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 的名称如下表所示。

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

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

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

子查询的缓存不同与 query cache 中查询结果的缓存（见[ Section 8.10.3.1, “How the Query Cache Operates”][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”][Section 8.2.2.2_ _Optimizing Subqueries with Materialization]
 *  partitions (JSON name: partitions)  
    分区的记录将被查询匹配。对于非分区表，该值为 NULL。见[Section 22.3.5, “Obtaining Information About Partitions”][Section 22.3.5_ _Obtaining Information About Partitions]。
 *  type (JSON name: access\_type)  
    连接类型。不同类型的描述见[EXPLAIN Join Types][EXPLAIN 1]
 *  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”][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”][Section 8.9.4_ _Index Hints]。  
    关于 MyISAM 存储引擎，运行 ANALYZE TABLE 帮助优化器选择更好的索引。myisamchk –analyze 可以做到与 ANALYZE TABLE 一样的效果。见 [ Section 13.7.2.1, “ANALYZE TABLE Syntax”][Section 13.7.2.1_ _ANALYZE TABLE Syntax]
 *  key\_len (JSON name: key\_length) 和 [Section 7.6, “MyISAM Table Maintenance and Crash Recovery”][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][EXPLAIN 2]。  
    这里没有单独的 JSON 属性与 Extra 列的值进行对应；然而，本列中可能出现的值作为 JSON 属性公开，或作为 message 属性的文本公开。

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

[https_dev.mysql.com_doc_refman_5.7_en_explain-output.html]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
[SHOW WARNINGS]: https://dev.mysql.com/doc/refman/5.7/en/show-warnings.html
[8.8.3 Extended EXPLAIN Output Format]: https://dev.mysql.com/doc/refman/5.7/en/explain-extended.html
[EXPLAIN PARTITIONS]: https://dev.mysql.com/doc/refman/5.7/en/explain.html
[Tutorial_ Using Visual Explain to Improve Query Performance]: https://dev.mysql.com/doc/workbench/en/wb-tutorial-visual-explain-dbt3.html
[EXPLAIN]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-output-columns
[EXPLAIN 1]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
[EXPLAIN 2]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information
[EXPLAIN 3]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-output-interpretation
[Table 8.1_EXPLAIN Output Columns]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-output-column-table
[Section 13.2.10.7_ _Correlated Subqueries]: https://dev.mysql.com/doc/refman/5.7/en/correlated-subqueries.html
[Section 8.10.3.1_ _How the Query Cache Operates]: https://dev.mysql.com/doc/refman/5.7/en/query-cache-operation.html
[Section 8.2.2.2_ _Optimizing Subqueries with Materialization]: https://dev.mysql.com/doc/refman/5.7/en/subquery-materialization.html
[Section 22.3.5_ _Obtaining Information About Partitions]: https://dev.mysql.com/doc/refman/5.7/en/partitioning-info.html
[Section 13.1.8_ _ALTER TABLE Syntax]: https://dev.mysql.com/doc/refman/5.7/en/alter-table.html
[Section 8.9.4_ _Index Hints]: https://dev.mysql.com/doc/refman/5.7/en/index-hints.html
[Section 13.7.2.1_ _ANALYZE TABLE Syntax]: https://dev.mysql.com/doc/refman/5.7/en/analyze-table.html
[Section 7.6_ _MyISAM Table Maintenance and Crash Recovery]: https://dev.mysql.com/doc/refman/5.7/en/myisam-table-maintenance.html