MySQL - EXPLAIN详解 - 个人文章

日常工作中，我们有时会通过日志记录下耗时较长的SQL语句，但是光找出这些SQL语句并不意味着完事了，常常需要借助EXPLAIN来查看SQL语句的执行计划，查看SQL语句是否用上了索引，是否进行了全表扫描，这都可以通过EXPLAIN命令得到。

概述

EXPLAIN： 为SELECT语句中使用到的每个表返回一条信息。它按照MySQL在处理语句时读取它们的顺序列出这些表。MySQL使用循环嵌套算法解析所有连接。意味着MySQL从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，等等。

QEP： SQL语句的查询执行计划

注意：

在以前版本的MySQL中，使用EXPLAIN PARTITIONS 与 EXPLAIN EXTENDED 来生成分区和扩展信息。目前为止这些语法仍然是向后兼容的，但未来MySQL会将它们排除出EXPLAIN语法，因为现在EXPLAIN默认就会输出分区和扩展的相关信息。所以PARTITIONS 与 EXTENDED关键字是多余的，不推荐使用，且在使用时会提示警告。

EXPLAIN 输出

本部分着重描述EXPLAIN生成的结果。更多关于 type 和 Extra 列的信息会在下文一一的介绍。

mysql> EXPLAIN SELECT * FROM customer;
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows   | Extra |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL | 936161 |       |
+----+-------------+----------+------+---------------+------+---------+------+--------+-------+
1 row in set

查看结果

id（JSON名： select_id）

SELECT 标识符，SQL执行的顺序的标识，SQL从大到小的执行

id相同时，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
如果id相同，则认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type（JSON名：无）

SELECT 类型，可以是下表显示中的任何类型。

查看结果

table（JSON名： table_name）

mysql> EXPLAIN SELECT t1.* FROM (SELECT mobile FROM customer GROUP BY mobile) t1;
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 936161 |                                 |
|  2 | DERIVED     | customer   | ALL  | NULL          | NULL | NULL    | NULL | 936161 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+--------+---------------------------------+
2 rows in set

type（JSON名： access_type）

查看结果

partitions（JSON名： partitions）

记录与查询匹配的分区。值为NULL表示为非分区表。(5.7才有)

possible_keys（JSON名： possible_keys）

表示MySQL查找表中的行时可选择的索引。请注意，此列完全独立于EXPLAIN输出中显示的顺序。这意味着在possible_keys中的某些键实际上不能按生成的表顺序使用。

如果该列是NULL，则代表没有相关的索引。在这种情况下，可以通过检查WHERE子句看它是否引用了某些列或适合索引的列来提高查询性能。如果是这样，那么就需要创造一个适当的索引，并再次用EXPLAIN检查

key（JSON名：key）

显示MySQL实际决定使用的键（索引），如果MySQL决定使用其中一个possible_keys 索引来查找行，则该索引被列为关键值。

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

对于InnoDB而言，即便是查询也选择主键索引，辅助索引（secondary index）可能会覆盖所选列，因为InnoDB将主键值存储在每个辅助索引中。如果key为NULL，则代表MySQL未发现可用于提高效率的索引。

对于MyISAM的表，运行 ANALYZE TABLE 有助于优化器选择更好的索引。myisamchk --analyze 也是如此。

key_len（JSON名： key_length）

显示MySQL使用索引键的长度。如果key是NULL，则key_len为NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref（JSON名：ref）

被用来标识那些用来进行索引比较的列或者常量

rows (JSON名 : rows)

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

filtered（JSON名： filtered）

给出了一个百分比的值，这个百分比值和 rows 列的值一起使用。(5.7才有)

Extra （JSON名称：无）

MySQL的附加信息，提供了与操作有关联的信息

EXPLAIN JOIN Types 详解

下面将描述从最佳类型到最差类型的连接类型

system

该表只有一行数据。这是const连接类型的特例

const

查询开始时读取，最多匹配出一行记录。由于只有一行，因此该行中列的值会被优化器视为常量。 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

效率仅次于system 和 const，可以用于=运算符进行比较的索引列，比较值可以是一个常量，也可以是一个表达式。

示例代码：

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

ref可以用于使用 =、or <=> 运算符进行比较的索引列。

示例代码：

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 类似，不同的是，它还对包含NULL的行进行额外的搜索。常作用在解析子查询中。

示例代码：

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

index_merge

此连接类型表示使用的是索引合并优化。

示例代码：

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

缺陷：

全文索引不适用于合并。
MySQL不会选择包含多层 AND/ OR 嵌套的复杂子句（修复方式如下）。

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

unique_subquery

只是一个索引查找函数，可以完全替代子查询以提高效率。

示例代码：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery

这种连接类型与 unique_subquery类似，取代了IN子查询。

示例代码：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

只检索在给定范围内的行。输出行中的列指出使用的具体索引。这个类型的ref列是NULL。

示例代码：

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相同，只是索引树被扫描了。当查询只使用到单个索引的部分列时，MySQL就会使用这种Join Types。主要体现在两个方面：

如果查询索引被覆盖了，且满足表中所需的所有数据，这时只扫描索引树。它比ALL扫描的要快，因为索引树比表数据小很多。Extra 列中会给出 Using index字眼的信息。
使用索引读取数据，以索引顺序查找数据行，进行完整的表扫描。使用的索引信息不会出现在Extra列中。

ALL

全表扫描，性能最糟，可以通过添加索引来避免。

EXPLAIN Extra 详解

一下列表表示可能出现在Extra中的值。如果要尽可能快的查询，那么了解下面内容是不错的选择。

const row not found（JSON属性： const_row_not_found）

对空表做类似 SELECT ... FROM tbl_name 的查询操作

Deleting all rows（JSON属性： message）

使用DELETE时，某些存储引擎（MyISAM）支持的一些简单、快速的处理方法。如果引擎使用到此类优化就会显示该内容

Distinct（JSON属性： distinct）

去重搜索是会显示出该内容

FirstMatch(tbl_name) （JSON属性：first_match）

表示 tbl_name 使用的半连接的FirstMatch连接策略。

Full scan on NULL key (JSON property: message)

当查询优化器不能使用索引查询时，那么查询优化后执行回退策略。

Impossible HAVING（JSON属性： message）

HAVING条件过滤没有效果，或者是始终选不出任何列(理解为返回已有查询的结果集)。

Impossible WHERE （JSON属性：message）

WHERE条件过滤没有效果，或者是始终选不出任何列(理解为最终是全表扫描)。

Impossible WHERE noticed after reading const tables （JSON属性：message）

查询了所有const(常量表和系统表)，但发现WHERE查询条件不起作用。

LooseScan(m..n) （JSON属性：message）

使用半连接LooseScan策略。 m 和 n是索引部分的数量

No matching min/max row（JSON属性： message）

没有行满足查询的条件，如 SELECT MIN(...) FROM ... WHERE condition

No matching row in const table（JSON属性：message）

对于连接查询，列未满足唯一索引的条件或表为空。

No matching rows after partition pruning（JSON属性： message）

对于DELETE 或 UPDATE，优化器在分区之后，未发现任何要删除或更新的内容。类似查询 Impossible WHERE。

No tables used（JSON属性： message）

查询没有FROM子句，或者有一个 FROM DUAL子句。

Not exists（JSON属性： message）

MySQL能够对LEFT JOIN查询进行优化，并且在查找到符合LEFT JOIN条件的行后，则不再查找更多的行。

示例代码：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  WHERE t2.id IS NULL;

假定t2.id被定义为 NOT NULL。在这种情况下，MySQL 使用t1.id列的值查找t2表中的行。如果找到匹配的行，且知道 t2.id不可能是 NULL，那么将不在继续查找t2表中剩余id相同的行。换句话说，对于每一行，MySQL只需要进行一次查询，而不管有多少行够与其匹能对应

Plan isn't ready yet （JSON属性：无）

这个值的产生在EXPLAIN FOR CONNECTION，当优化器不能按照被命名的查询连接来创建一个执行器计划时就会出现Plan isn't ready yet。如果执行计划的输出包含了多行，所有行都可以有该值，则取决于优化器来决定完整的执行计划。

Range checked for each record (index map: N)（JSON属性： message）

MySQL没有发现可以使用的教好的索引，但是发现一些索引也许能使用在已有表的列值上。对于已有表格数据的每一行比较，检查是否可以使用range 或 index_merge 方法来检索行。虽然不是最快的，但也比完全不用索引要快的多。

Scanned N databases（JSON属性： message）

表示处理INFORMATION_SCHEMA表查询时服务器执行的扫描次数。关于N的值可以是0，1，或者是all.

详情参考：https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimization.html

Start temporary，End temporary（JSON属性： message）

说明在半连接复制清除策略中使用了临时表

unique row not found（JSON属性： message）

对于类似于SELECT ... FROM tbl_name的查询,表中找不到满足条件唯一索引或主键索引的列。

Using filesort（JSON属性： using_filesort）

MySQL必须做一个额外的传递才能找出按排序的顺序检索数据。通过连接类型存储的排序关键字和WHERE查询条件等一起确定的。然后对键进行排序，并按排序顺序检索行。

Using index（JSON属性： using_index）

只需通过索引树就可以从表中获取列的信息，无需额外去读取真实的行数据。如果查询使用的列值仅仅是一个简单索引的部分值，则会使用这种策略来优化查询。对于innoDB数据库中的表有一个自定义的聚簇索引，该索引能够起作用，即使是Using index并没有出现在Extra列中。这种情况下的type字段为index并且key字段的值为PRIMARY。

Using index condition（JSON属性： using_index_condition）

表的读取首先通过读入索引值来判断是否需要全表扫描。在这种方式中，如果有需要的话。索引信息将被用来服务(压入)全表扫描的。

Using index for group-by（JSON属性：using_index_for_group_by）

类似于Using index的表查询方法，指MySQL发现索引能够被用来查找 group by 或 DISTINCT的列，而不需要任何真实的表查询。另外，索引使得每个分组查找都更有效，只有少量的索引值需要读取。

Using join buffer (Block Nested Loop)， Using join buffer (Batched Key Access) （JSON属性：using_join_buffer）

从已有连接中找被读入缓存的数据，并且通过缓存来完成与当前表的连接。(Block Nested Loop)说明使用了块循环算法,(Batched key Access)说明使用了批量接入关键字算法。也就是说，在EXPLAIN输出记录中，从已经查找过的表中将输出的列缓存下来，并在需要时批量的找出与当前数据对比，这时就会出现Using join buffer。

在JSON格式的输出中，using_join_buffer的值要么是Block Nested Loop,要么是Batched Key Access.

Using MRR（JSON属性： message）

使用多范围读取的优化策略来读取表中的数据。

示例代码：（假设有一个索引： (key_part1, key_part2)）

SELECT * FROM t
  WHERE key_part1 >= 1000 AND key_part1 < 2000
  AND key_part2 = 10000;

对于MRR，通过配置系统变量read_rnd_buffer_size来作为它的缓冲区，并通过它来确定每次最大处理字节数。

Using sort_union(...)，Using union(...)，Using intersect(...)（JSON属性： message）

表示在index_merge的连接类型中索引合并是怎么样完成的，及使用了怎样特别的算法。

Using temporary（JSON属性： using_temporary_table）

为了执行查询，MySQL需要创建一个临时表来存储已有的结果。如果发现查询中group by和order by是不同的列，则会有该类型产生。

Using where（JSON属性： attached_condition）

WHERE条件用于赛选出与下一个表匹配的数据然后返回给客户端。除非故意做的全表扫描，否则连接类型是ALL或者是index，且在Extra列的值中没有Using Where，则该查询可能是有问题的。

Using where with pushed condition（JSON属性：message）

该内容只适用在NDB的表中。意味着NDB集群中正在使用“pushed down”优化策略，保证了通过网络只发送有用的数据，且比未优化的情况下提高了5-10倍的速度。

Zero limit（JSON属性： message）

查询条件中有LIMIT 0 并且没有任何可以选择的记录。

说点什么

关注微信公众号：battcn 后台回复 mysql 即可获得 《打造扛得住的MySQL数据库架构》

个人QQ：1837307557
battcn开源群（适合新手）：391619659

MySQL - EXPLAIN详解

概述

EXPLAIN 输出

EXPLAIN JOIN Types 详解

EXPLAIN Extra 详解

说点什么

峰峰峰峰峰

引用和评论

一起来学SpringBoot | 第十三篇：RabbitMQ延迟队列

在Java程序中监听mysql的binlog

Mysql数据库sql优化案例

MySQL 回收表碎片实践教程

高效工程师的七个习惯

Mysql梳理1

mysql梳理2