了解MySQL中EXPLAIN解释命令

共计 15773 个字符，预计需要花费 40 分钟才能阅读完成。

1 EXPLAIN 概念
EXPLAIN 会向我们提供一些 MySQL 是执行 sql 的信息：

EXPLAIN 可以解释说明 SELECT, DELETE, INSERT, REPLACE, and UPDATE 等语句.
当 EXPLAIN 与可解释的语句一起使用时，mysql 会显示一些来自于优化器的关于 sql 执行计划的信息。即 mysql 解释它是如何处理这些语句的，和表之间是如何连接的。想获取更多关于 EXPLAIN 如何获取执行计划信息的。
当 EXPLAIN 后面是一个会话的 connection_id 而不是一个可执行的语句时，它会展示会话的信息。
对于 SELECT 语句，EXPLAIN 会产生额外的执行计划信息，这些信息可以用 SHOW WARNINGS 显示出来。
EXPLAIN 对于检查设计分区表的查询时非常有用。
FORMAT 选项可以用于选择输出格式，如果没有配置 FORMAT 选项，默认已表格形式输出。JSON 选项让信息已 json 格式展示。

2 EXPLAIN 输出列信息
EXPLAIN 输出的字段信息第一列: 列名, 第二列:FORMAT = JSON 时输出中显示的等效属性名称 , 第三列：字段含义

Column
JSON Name
Meaning

id
select_id
select 标识号

select_type
None
select 类型

table
table_name
这一行数据是关于哪张表的

partitions
partitions
匹配的分区，对于未分区表，该值为空

type
access_type
使用的连接类别, 有无使用索引

possible_keys
possible_keys
MySQL 能使用哪个索引在该表中找到行

key
key
MySQL 实际决定使用的键（索引）

key_len
key_length
MySQL 决定使用的键长度。如果键是 NULL，长度为 NULL

ref
ref
与索引关联的列

rows
rows
mysql 认为执行 sql 时必须被校验的行数

filtered
filtered
表示此查询条件所过滤的数据的百分比

Extra
None
附加信息

2.1 id
SELECT 标识符。SELECT 在查询中的序列号，可以为空。
2.2 select_type
SELECT 类型，所有类型在下表中展示，JSON 格式的 EXPLAIN 将 SELECT 类型公开为 query_block 的属性，除非它是 SIMPLE 或 PRIMARY。JSON 名称 (不适用为 None) 也显示在表中。

select_type Value
JSON Name
Meaning

SIMPLE
None
简单 SELECT(不使用 UNION 或子查询等)

PRIMARY
None
嵌套查询时最外层的查询

UNION
None
UNION 中的第二个或后面的 SELECT 语句

DEPENDENT UNION
dependent (true)
UNION 中的第二个或以后的 SELECT 语句，取决于外部查询

UNION RESULT
union_result
UNION 的结果

SUBQUERY
None
子查询中的第一个选择

DEPENDENT SUBQUERY
dependent (true)
子查询中的第一个选择，取决于外部查询

DERIVED
None
派生表（子查询中产生的临时表）

MATERIALIZED
materialized_from_subquery
物化子查询

UNCACHEABLE SUBQUERY
cacheable (false)
无法缓存结果的子查询，必须对外部查询的每一行进行重新计算

UNCACHEABLE UNION
cacheable (false)
UNION 中属于不可缓存子查询的第二个或以后的选择(请参 UNCACHEABLE SUBQUERY)

表信息（后面演示用）：
mysql> show create table t_a;
——+
| t_a | CREATE TABLE `t_a` (
`id` bigint(20) NOT NULL DEFAULT ‘0’,
`age` int(20) DEFAULT NULL,
`code` int(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code` (`code`),
KEY `age_key` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+——-+———————————–
——+
1 row in set (0.03 sec)

2.3 table
显示这一行的数据是关于哪张表的，有时是真实的表名字，有时也可能是以下几种结果

<unionM,N>: 指 id 为 M,N 行结果的并集
<derivedN>: 该行是指 id 值为 n 的行的派生表结果。派生表可能来自例如 from 子句中的子查询。
<subqueryN>: 该行是指 id 值为 n 的行的物化子查询的结果。

2.4 partitions
查询的记录所属于的分区，对于未分区表，该值为 NULL。
2.5 type
连接使用了哪种类别, 有无使用索引, 常用的类型有：system, const, eq_ref, ref, range, index, ALL（从左到右，性能越来越差），详情查看 EXPLAIN Join Types

NULL：MySQL 在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
system：这个表（也可能是查询出来的临时表）只有一行数据 (= system table). 是 const 中的一个特例

const：表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const 表很快，因为它们只读取一次！const 用于查询条件为 PRIMARY KEY 或 UNIQUE 索引并与常数值进行比较时的所有部分。在下面的查询中，tbl_name 可以用于 const 表：
SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1= 1 和 primary_key_part2=2；

eq_ref：对于前几个表中的每一行组合，从该表中读取一行。除了 system 和 const，这是最好的连接类型。当连接使用索引的所有部分，并且索引是主键或唯一非空索引时，将使用它。eq_ref 可以用于使用 = 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。在下面的例子中，MySQL 可以使用 eq_ref 联接去处理 ref_tables：
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;

ref 对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是 UNIQUE 或 PRIMARY KEY（换句话说，如果联接不能基于关键字查询结果为单个行的话），则使用 ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。ref 可以用于使用 = 或 <=> 操作符的带索引的列。在下面的例子中，MySQL 可以使用 ref 联接来处理 ref_tables：
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_tables：
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

index_merge：该联接类型表示使用了索引合并优化方法。在这种情况下，key 列包含了使用的索引的清单，key_len 包含了使用的索引的最长的关键元素。
SELECT * FROM ref_table
WHERE idx1=expr1 OR idx2 =expr2;

unique_subquery：该类型替换了下面形式的 IN 子查询的 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)

index：该联接类型与 ALL 相同，除了只有索引树被扫描。这通常比 ALL 快，因为索引文件通常比数据文件小。当查询只使用作为单索引一部分的列时，MySQL 可以使用该联接类型。
ALL：对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记 const 的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用 ALL，使得行能基于前面的表中的常数值或列值被检索出。

2.6 possible_keys
possible_keys 列指出 MySQL 能使用哪个索引在该表中找到行。注意，该列完全独立于 EXPLAIN 输出所示的表的次序。这意味着在 possible_keys 中的某些键实际上不能按生成的表次序使用。
如果该列是 NULL，则没有相关的索引。在这种情况下，可以通过检查 WHERE 子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用 EXPLAIN 检查查询
2.7 key
key 列显示 MySQL 实际决定使用的键（索引）。如果没有选择索引，键是 NULL。要想强制 MySQL 使用或忽视 possible_keys 列中的索引，在查询中使用 FORCE INDEX、USE INDEX 或者 IGNORE INDEX。
2.8 key_len
key_len 列显示 MySQL 决定使用的键长度。如果键是 NULL，则长度为 NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好
2.9 ref
ref 列显示使用哪个列或常数与 key 一起从表中选择行。
2.10 rows
rows 列显示 MySQL 认为它执行查询时必须检查的行数。
2.11 Extra
该列包含 MySQL 解决查询的详细信息, 下面详细.

Distinct：一旦 MYSQL 找到了与行相联合匹配的行，就不再搜索了
Not exists：MYSQL 优化了 LEFT JOIN，一旦它找到了匹配 LEFT JOIN 标准的行，就不再搜索了
Range checked for each：没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL 检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
Using filesort：看到这个的时候，查询就需要优化了。MYSQL 需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using index：列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
Using temporary：看到这个的时候，查询需要优化了。这里，MYSQL 需要创建一个临时表来存储结果，这通常发生在对不同的列集进行 ORDER BY 上，而不是 GROUP BY 上
Using where：使用了 WHERE 从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型 ALL 或 index，这就会发生，或者是查询有问题

参考：
MySQL5.7 EXPLAIN Output Format

了解MySQL中EXPLAIN解释命令

Just My Socks（注册教程 内含优惠码）

Just My Socks（注册教程内含优惠码）