MySQL 5.7.x EXPLAIN使用详解

EXPLAIN输出列

EXPLAIN语句提供有关mysql如何执行语句的信息。EXPLAIN可与SELECT、DELETE、INSERT、REPLACE、UPDATE语句配合使用。
EXPLAIN会为SELECT语句中使用的每个表返回一行信息。它会按照mysql在处理该语句时读取的顺序，在输出中列出所有表。mysql使用嵌套循环连接方法解析所有连接。处理完所有表后，mysql会输出选定的列，并回溯整个表列表，直到找到包含更多匹配行的表。然后从该表中读取到下一行，并继续处理下一个表。
EXPLAIN输出包含分区信息。此外，对于select语句，EXPLAIN会生成扩展信息。这些信息可以在EXPLAIN后使用SHOW WARNINGS命令显示出来。
注意
在较久的mysql版本中，分区和扩展信息是使用EXPLAIN PARTITIONS和EXPLAIN EXTENDED生成的。为了向后兼容，这些语法仍然可以识别，但现在默认启用分区和扩展输出，因此PARTITIONS和EXTENDED关键字已不再使用，已被弃用。使用PARTITIONS和EXTENDED关键字会导致警告。
注意
不能在同一个EXPLAIN语句中同时使用已弃用的PARTITIONS和EXTENDED关键字。此外这两个关键字不能和FORMAT选项一起使用。
注意
mysql workbench具有可视化解释功能，可提供EXPLAIN输出的可视化表示。

EXPLAIN生成的输出列

这里请注意，JSON名称只有使用FORMAT=JSON时输出中显示的等效属性名称，JSON名称默认是不显示的。
同时需要注意，为NULL的JSON属性不会显示在JSON格式的EXPLAIN输出中。

列	JSON名称	意义
id	select_id	select标识符
select_type	None	select类型
table	table_name	输出行的表
partitions	分区	匹配的分区
type	access_type	连接类型
possible_keys	possible_keys	可选的索引
key	key	实际选择的索引
key_length	key_length	所选键的长度
ref	ref	与索引比较的列
rows	rows	待检查的行数预估
filtered	filtered	按表条件过滤的行数百分比
extra	None	附加信息

以下是上表中各字段的具体解释

id（JSON name：select_id）

select标识符。这是查询中select语句的序列号。如果改行引用其他行的并集结果，则该值可以为null。

select_type（JSON name：none）

select的类型，可以是下表中所示的任意类型。JSON格式的EXPLAIN会将SELECT类型公开为query_block的属性，除非它是SIMPLE或PRIMARY。JSON名称（如适用）也会显示在列表中。

select_type值	JSON名称	意义
SIMPLE	None	简单SELECT（不使用UNION或子查询）
PRIMARY	None	最外层SELECT
UNION	None	UNION中第二个或后续的SELECT语句
DEPENDENT UNION	dependent(true)	UNION中第二个或后续的SELECT语句，依赖于外部查询
UNION RESULT	union_result	UNION的结果
SUBQUERY	None	子查询中第一个SELECT
DEPENDENT SUBQUERY	dependent(true)	子查询中第一个SELECT，依赖于外部查询
DERIVED	None	派生表
MARTERIALIZED	materialized_from_subquery	物化子查询
UNCACHEABLE SUBQUERY	cacheable(false)	无法缓存结果的子查询，必须针对外部查询的每一个行重新计算结果
UNCACHEABLE UNION	cacheable(false)	UNION中属于不可缓存子查询的第二个或后续的SELECT
DEPENDENT通常表示使用相关子查询。
DEPENDENT SUBQUERY的求值与UNCACHEABLE SUBQUERY的求值不同。对于DEPENDENT SUBQUERY，子查询只会针对其外部的上下文中变量得每组不同值重新求值一次。对于UNCACHEABLE SUBQUERY，子查询会针对外部上下文的每一行重新求值。
子查询的可缓存性与查询缓存中查询结果的缓存不同。子查询缓存在查询执行期间发生，而查询缓存在查询执行完成后用于存储结果。
当您在EXPLAIN中指定FORMAT=JSON时，属性中没有与select_type直接等效的属性；query_block属性对应于给定的SELECT语句。与上述大多数SELECT子查询类型等效的属性均可用，并在适当时显示。
SIMPLE或PRIMARY没有JSON等效属性。
非SELECT语句的select_type值显示受影响表的语句类型。例如，对于DELETE语句，select_type为DELETE。

table（JSON name：table_name）

输出行所引用的表的名称，可以是以下值之一：

• <unionM,N>：该行表示id值为M和N的行的并集。
• <derivedN>：该行表示id值为N的行的派生表结果。例如，派生表可能由FROM子句中的子查询生成。
• <subqueryN>：该行表示id值为N的行的具体化子查询的结果。

partitions（JSON name：partitions）

查询将匹配的记录所在的分区。对于非分区表，该值为NULL。

type（JSON name：access_type）

EXPLAIN输出的type列描述了表的连接方式。在JSON格式的输出中，这些值作为access_type属性的值出现。以下列表描述了连接类型，关于不同类型描述，请查看XEXPLAIN join typesXX

possible_keys（JSON name：possible_keys）

possible_keys列指示mysql可以从哪些索引中查找此表中的行。请注意，此列与EXPLAIN输出中显示的表顺序完全无关。这意味着possible_keys中的某些键在实际生成的表顺序下可能无法使用。
如果此列为NULL（或在JSON格式的输出中为undefined），则表示没有相关索引。在这种情况下，您可以通过检查WHERE子句来检查它是否引用了合适索引的列，从而提高查询的性能。如果是，请创建合适的索引，然后再此使用EXPLAIN检查查询。
要查看表中有哪些索引，请使用SHOW INDEX FROM tbl_name。

key（JSON name：key）

key列指示mysql实际决定使用的键（索引）。如果mysql决定使用某个possible_keys索引来查找执行，则该索引将被列为键值。
key可能指向一个可能不存在的possible_keys值中的索引。如果所有possible_keys索引都不适合查找行，但查询所选的所有列都是其他索引的列，则会发生这种情况。也就是说，指定的索引涵盖了所选的列，因此尽管它不用于确定要检索哪些行，但索引扫描比数据行扫描更加高效。
对于InnoDB，即使查询也选择了主键，二级索引也可能覆盖所选列，因为InnoDB将主键值与每个二级索引一起存储。如果key为NULL，则表示mysql未找到可用于更高效执行查询的索引。
要强制mysql使用或忽略possible_keys列中列出的索引。请在查询中使用 FORCE INDEX、 USE INDEX或IGNORE INDEX
对于MyISAM表，运行ANALYZE TABLE有助于优化器选择更合适的索引。对于MyISAM表，myisamchk --analyze也能起到同样的作用。

key_len（JSON name：key_length）

key_len列指示mysql决定使用的键的长度。key_len得知可用于确定mysql实际使用的多部分键的个数。如果key列为NULL，则key_len列也为NULL。
由于key存储格式的原因，可以为NULL的列的键长度比可以为NOT NULL的列的键的长度大1。

ref（JSON name：ref）

ref列显示哪些列与常量与键列中指定的索引进行比较，以便从表中选择行。
如果值为func，则使用的值是某个函数的结果。要查看是哪个函数，请在EXPLAIN后使用SHOW WARNINGS来查看扩展的EXPLAIN输出。该函数实际上可能是一个运算符，例如算术运算符。

rows（JSON name：rows）

rows列表示mysql认为执行查询时必须检查的行数。对于InnoDB表，此数字是估计值，可能并不总是准确的。

filtered（JSON name：filtered）

filtered列表示根据表格条件筛选的表格行的预估百分比。最大值是100，表示未进行任何行筛选。值从100开始递减，表示筛选次数增加。rows表示以检查的行数预估，rows x 已筛选表示与下表连接的行数。例如，如果 rows 为 1000，filtered 为 50.00（50%），则需要与下表连接的行数为 1000 × 50% = 500。

Extra（JSON name：none）

此列包含有关mysql如何解析查询的附加信息。有关不同值表述，请参阅https://dev.mysql.com/doc/refman/5.7/en/explain-output.html的EXPLAIN Extra Information。
没有与Extar列对应的单个JSON属性；但是，此列中可能出现的值会以JSON属性或信息属性的文本形式公开。

EXPLAIN join types

• system
  该表只有一行（=系统表）。这是const连接类型的一种特殊情况。
• const
  该表最多包含一行匹配行，该行在查询开始时读取。由于只有一行，因此该行中列的值可以被优化器的其余部分视为常量。const表速度非常快，因为它们只读取一次。
  当您将PRIMARY KEY或UNION索引的所有部分与常量值进行比较时，请使用const。在以下查询中，tbl_name可以用做const表：

SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref
  对于前几个表中的行，每个组合都会从此表中读取一行。除了系统类型和const类型之外，这是最佳的连接类型。当连接使用索引的所有部分，并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时，使用此类型。
  eq_ref可用于使用=运算符进行比较的索引列。此值可以是常量，也可以是使用在此表之前读取的表中的列的表达式。在以下示例中mysql可以使用eq_ref连接来处理。

SELECT * FROM ref_table,other_table WHRER ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHRER ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• ref
  对于来自前表中的每个行组合，都会从此表中读取所有具有匹配索引值的行。如果连接仅使用键的最左前缀，或者键不是PRIMARY KEY或UNIQUE索引（换句话说，如果连接无法根据键值选择单行），则使用ref。如果使用的键仅匹配少数行，则这是一种不错的连接类型。
  ref可用于使用=或<=>运算符进行比较的索引列。在以下示例中，mysql可以使用ref join来处理ref_table：

SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• fulltext
  连接操作使用FULLTEXT索引执行。
• ref_or_null
  此连接类型类似于ref，但mysql会额外搜索包含NULL值的行。此连接类型优化常用于解析子查询。在以下示例中，mysql可以使用ref_or_null连接来处理ref_table：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

• index_merge
  此连接类型表示使用了索引合并优化。在这种情况下，输出行的key列包含所用索引的列表，而key_len包含所用索引的最长键部分的列表。

• unique_subquery
  此类型替代了以下形式的某些IN子查询的eq_ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery只是一个索引查找函数，它完全取代了子查询，以提高效率。

• index_subquery
  此连接类型与unique_subquery类似。它取代了IN子查询，但适用于以下形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range
  仅检索指定范围内的行，并使用索引选择行。输出行中的key列指示使用了哪个索引。key_len包含已使用的最长键部分。对于此类型，ref列为NULL。
  当使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE 或 IN()运算符将键列与常量进行比较时，可以使用range：

SELECT * FROM tbl_name WHERE key_column=10;
SELECT * FROM tbl_name WHERE key_column BETWEEN 10 AND 20;
SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name WHERE key_part1=10 AND key_part2 IN (10,20,30);

• index
  索引连接类型与ALL相同，但会扫描索引树。扫描方式有两种：

1. 如果索引是查询的覆盖索引，并且可用于满足表所需的所有数据，则仅扫描索引树。在这种情况下，“Extra”列会显示“Using index”。仅索引扫描通常比ALL快，因为索引大小通常小于表数据。
2. 全表扫描是指使用索引中的读取操作按索引顺序查找数据行。“使用索引”不会显示在Extra列中。
   当查询仅使用属于单个索引的列时，mysql可以使用此连接类型。

• ALL
  对先前表中每个行的组合都会进行全表扫描。如果该表是第一个未被标记为const的表，则通常不太好，而在其他所有情况下，通常都非常糟糕。通常，您可以通过添加索引来避免全表扫描，这些索引允许基于常量值或先前表中的列值从表中检索行。
  type从最佳类型到最差类型依次是：
  system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL