MySQL中Explain关键字输出字段简介

前言

• 本文中MySQL版本：5.7.10
• MySQL 5.7官方参考手册：EXPLAIN Output Format

---

Explain输出字段

id列

此列总是包含一个编号，可以简单理解为SELECT子句执行或者表的读取顺序，对于id号相同的查询按序从上到下执行，对于id号不同的查询按id号从大到小的顺序执行。

id列的编号从1开始，若没有子查询SUBQUERY或者联合查询UNION，则Explain每一行输出的id列都为1；如果存在子查询则内层的子查询的对应id顺序增加，代表当前所处的查询层次；如果存在联合查询UNION，则对应临时表的id列为NULL。

---

select_type列

主要代表当前子句查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。常见值主要有6个：

1. SIMPLE

  表示当前查询为最简单的查询，不包含任何子查询SUBQUERY和联合查询UNION

2. PRIMARY

  如果查询中包含任何复杂的部分，则最外层部分的查询过程被标记为PRIMARY

3. SUBQUERY

  不在FROM子句中的复杂子查询（如SELECT子句、WHERE子句等）都会被标记为SUBQUERY

4. DERIVED

  DERIVED值用来表示包含在FROM子句中的复杂子查询，MySQL会递归执行这些子查询，并将结果放置在临时表中

5. UNION

  对于联合查询UNION中，第二个及之后的SELECT查询都会被标记为UNION。如果UNION查询被FROM子句中的子查询包含，那么UNION查询中的第一个SELECT语句会被标记为DERIVED

6. UNION RESULT

  用来从UNION的匿名临时表中检索结果的SELECT被标记为UNION RESULT

  除了这些值外，SUBQUERY和UNION还可以被标记为DEPENDENT和UNCACHEABLE。DEPENDENT表明当前的子查询为相关子查询，当前查询依赖于外部子查询中检索到的数据；UNCACHEABLE表明SELECT中的某些特性阻止结果被缓存于一个Item_cache中。

---

table列

显示当前查询过程中访问的表

---

partitions列

表明当前查询过程中，查询过的记录来自于当前表的哪些分区。如果为NULL，则表示当前表未分区

---

type列

表示对当前表的访问类型，换言之就是MySQL决定如何查询表中的行记录。下面是一些常见值，依次从最优到最差。

1. system

  当被查询的表中只有一行记录时（等价于系统表），此表的访问类型会被标记为system

2. const

  若当前查询中，被查询表中最多只有唯一匹配行，匹配条件为常量时，当前访问类型会被标记为const，MySQL会将当前查询的结果直接优化成一个常量，常出现在对主键（Primary Key）或者唯一性索引（Unique Key）进行常量匹配查询时。例如，当对表中的某个主键或者唯一性索引中包含的所有字段，通过where子句进行常量匹配时，因为是通过索引进行唯一匹配，查询非常快，MySQL会直接将查询结果视为常量

3. eq_ref

  此访问类型代表了唯一性索引扫描，同时代表当前查询过程返回的是索引字段匹配某单个值的行记录。使用这种索引查找，MySQL知道最多只会返回一条符合查找条件的行记录，这种访问类型常见于使用多个值对主键索引或者唯一性索引字段进行常量匹配时。

4. ref

  此访问类型代表了非唯一性索引扫描，与eq_ref相对，代表当前查询过程是通过非唯一性索引进行表的行记录查询，返回的是索引字段匹配某单个值的行记录，可能会存在多个匹配行，此单个匹配值可以是常量，也可以是前一个查询结果。此类访问类型只会出现在，对非唯一性索引或者唯一性索引的非唯一性前缀进行单值匹配时。

5. range

  此访问类型表示使用索引来检索指定范围的行记录。常见于对表中某个索引（不论是唯一性还是非唯一性）进行行记录检索时，使用多个值进行索引字段的匹配查询，如使用BETWEEN、>、<、IN、OR等。索引字段的范围查询（type=range）要优于索引扫描（type=index），因为范围查询要遍历每一个索引节点，而范围查询只需要遍历部分索引节点即可。

  注意：当对非唯一性索引进行范围查询时候，若此查询需要扫描记录数超过一定数量（大约为全表记录数的20%），则MySQL优化器不再使用索引进行范围查询（type=range），而是直接进行全表扫描（type=ALL）

6. index

  此访问类型表示当前查询是按照某个索引的顺序进行全表扫描。其主要优点在于按照某个或者某些字段的索引顺序进行扫描时，若同时需要对这些字段进行排序，则可以省略排序过程（因为索引自身有序），这也是使用索引优化排序过程的关键所在；缺点在于，如果当前索引不是覆盖索引（covering index），即Extra列中没有显示Using index值时，基于索引顺序检索行记录时，必定会产生磁盘随机I/O（因为行记录是按照主键或RowID排序的），进而导致查询效率十分低下。

7. ALL

  此访问类型表示当前查询的访问类型为全表扫描，即按照表中行记录顺序进行全表扫描。此访问类型在所有访问类型中查询效率最差，一般若出现此访问类型，则表示当前查询过程急需优化，通常是对特定字段建立索引来避免全表扫描。当然实际过程中，有时也难以避免全表扫描。

---

possible_keys列

表示当前表中与当前查询相关的索引，即MySQL可以通过其检索行记录的索引。若possible_keys为NULL，也并不代表当前表中没有索引，只是代表当前查询中没有可以使用的索引。

---

key列

表示当前查询过程中实际使用的索引。注意：当使用覆盖索引时，key中指定的索引，可能并不会在possible_keys中列出。

---

key_len列

表示当前查询过程中所使用的索引的字节长度，如果key列为NULL（表明未使用索引），则key_len列同样为NULL。

---

ref列

表示当前查询过程中进行索引匹配时所使用的字段或者常量。若为const则表明在进行索引查找时，使用的是某个常量值进行匹配；若为某个表的字段，则表明在进行索引查找时，使用的是其字段对应值进行匹配。

---

rows列

表示MySQL认为执行当前SQL查询所需要读取的行记录数。对于存储引擎为InnoDB的表而言，这一列的值只是个估计值，并不一定准确。

---

filtered列

表示经过当前查询条件过滤之后剩余记录数占已读取记录数的百分比，是一个悲观的估计值。此列值的范围为0~100，若等于100，则表明未发生过滤，越小则表明过滤的数据行越多。rows*filtered/100的值为当前查询结果的实际行记录数的一个估计值。

---

Extra列

此列记录有MySQL处理当前查询的一些重要的额外信息，可以有多个值。其中常见值的解释如下：

1. Using filesort

  此值表明MySQL必须通过额外的排序操作来满足查询ORDER BY子句中的排序条件。通常出现于ORDER BY排序条件中的字段未创建索引的情况

2. Using index

  此值表明MySQL将使用覆盖索引（Covering Index）来获取查询字段数据，避免访问具体的数据行。这是索引优化查询的方式之一。注意：所谓覆盖索引（Covering Index）简单理解就是查询字段的值被索引的字段完全覆盖，可以直接通过检索索引对应数据结构就可以获取查询字段的值

3. Using Index Condition

  此值表明MySQL执行当前查询过程时使用了索引下推（ICP，Index Condition Pushdown）优化，即在扫描索引时直接判断某个行记录是否可以使用where条件进行过滤，也就是将where的部分过滤放在了存储引擎层执行，从而减少MySQL上层对于记录的索取，进而提高整体性能。

4. Using join buffer (Block Nested Loop)

  此值表明MySQL在执行当前查询过程中使用了BNL联接算法，主要是针对联接条件外部表对应字段未建立索引的情况。即使用join buffer（联接缓冲区）批量缓存外部表中的相关列，一次性对内部表的每条记录进行多次匹配，进而大大减少嵌套查询中内部表的扫描次数。

  注意：嵌套查询中外部表不一定是外部查询的表，也有可能是子查询对应的表

5. Using join buffer (Batched Key Access)

  此值表明MySQL在执行当前查询过程中使用了BKA联接算法，主要针对联接条件中外部表对应字段使用的是辅助索引（二级索引）且查询字段未被此索引覆盖的情况。和BNL算法类似，同样先使用join buffer（联接缓冲区）批量缓存外部表中的相关列，进而减少内部表的扫描次数，但是在获取查询字段对应的具体数据时并不是按照联接字段的辅助索引（二级索引）的顺序获取，而是通过批量地将索引键值发送给Multi-Range Read（MRR）接口，MRR会将辅助索引按照对应的RowID进行排序，然后访问对应的具体数据行，最后将结果集返回给客户端。

6. Using MRR

  此值表明MySQL在执行当前查询过程中使用了Multi-Range Read（MRR）优化。即将辅助索引进行缓存，并在缓存中将其按照RowID进行排序，最终根据RowID的顺序来访问具体的数据行。MRR优化的主要作用就是通过按照RowID的顺序访问具体的数据行，尽量减少随机I/O的次数，主要适用于range，ref，eq_ref类型的查询。

7. Using temporary

  此值表明MySQL为了处理当前查询过程，需要创建临时表来保存结果。常见于使用GROUP BY、ORDER BY、或UNION等查询语句中。

8. Using where

  此值表明MySQL服务器在存储引擎检索到查询字段对应的最终行记录后进行了过滤。注意：并不是所有的带where子句的查询的Extra列中都会标记为“Using where”，如对某个索引的最左前缀进行where等值常量匹配时，则不会被标记为“Using where”。

---

参考资料

MySQL 5.7 官方参考手册：EXPLAIN Output Format

《高性能MySQL（第三版）》附录D EXPLAIN

《MySQL技术内幕:InnoDB存储引擎（第2版）》5.6.6Multi-Range Read优化、5.6.7Index Condition Pushdown（ICP）优化

《MySQL技术内幕:SQL编程》5.5.2Block Nested-Loops Join算法、5.5.3Batched Key Access Join算法

MySQL高级知识（四）——Explain