> For the complete documentation index, see [llms.txt](https://gitbook.fantasticmao.cn/tech/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://gitbook.fantasticmao.cn/tech/shu-ju-ku/mysql/li-jie-cha-xun-zhi-xing-ji-hua.md).

# 理解查询执行计划

依据表、列、索引和 WHERE 子句中的条件，MySQL 优化器会选择使用多种技术来提高 SQL 语句的查询效率。例如，当对一张很大的表进行查询时，MySQL 不必查询所有的行。当对多张表进行联合查询时，MySQL 不必考虑所有的行组合情况。MySQL 优化器选择执行最有效的查询方案的一系列操作被称为「查询执行计划」，也被称为 EXPLAIN 计划。

## 使用 EXPLAIN 来优化查询

EXPLAIN 语句提供有关于 MySQL 如何执行 SQL 语句的信息：

* EXPLAIN 对 SELECT、DELETE、INSERT、REPLACE 和 UPDATE 语句有效；
* 当 EXPLAIN 被用于可解释的 SQL 语句时，MySQL 会展示来自优化器中的有关语句执行计划的信息。这也意味着，MySQL 会解释它将如何处理该语句，包括有关如何连接表和以何种顺序进行连接；
* 当 EXPLAIN 被用于 FOR CONNECTION connection\_id，而不是可解释的 SQL 语句时，MySQL 会展示该连接中正在执行的语句执行计划的信息；
* 对于 SELECT 语句，EXPLAIN 会提供额外的执行计划信息，这些信息可以通过 SHOW WARNINGS 来显示；
* EXPLAIN 对于涉及分区表的检查查询来说非常有用；
* FORMAT 选项可以用于选择输出格式，值为 TRADITIONAL 时表示以表格的格式展示，值为 JSON 时表示以 JSON 的格式展示。

通过使用 EXPLAIN，开发者可以知道应该在表的何处添加索引，以便于 SQL 语句通过使用索引来更快地查找数据。开发者还可以使用 EXPLAIN 来检查 MySQL 优化器是否以最佳的顺序来连接表。

## EXPLAIN 输出格式

EXPLAIN 会为 SELECT 语句中所使用的每个表返回一行信息。其中这些表的排列顺序与 MySQL 在处理 SQL 语句时所读取的顺序相同。这意味着 MySQL 会先读取第一张表中的行，然后在第二张表中查找匹配的行，然后在第三张表中查找，以此类推。

### EXPLAIN 输出的列

EXPLAIN 输出的每行信息都对应了一张表，每行都会包含如下的信息：

* id 表示 SELECT 的标识符，是一次查询当中的 SELECT 语句的序号。MySQL 将 SELECT 查询分为简单和复杂类型，复杂类型可以分成三类：简单子查询、派生查询（在 FROM 子句中的子查询）、UNION 查询；
* select\_type 表示 SELECT 的类型，部分值的含义如下表：
  * **SIMPLE** 表示简单 SELECT（没有使用 UNION 查询或者子查询）；
  * **PRIMARY** 表示复杂查询中的最外层的 SELECT；
  * **UNION** 表示 UNION 查询中的第二个或者之后的 SELECT；
  * **DEPENDENT UNION** 表示 UNION 查询中的第二个或者之后的 SELECT，依赖于外部查询；
  * **UNION RESULT** 表示 UNION 查询的结果；
  * **SUBQUERY** 表示子查询中的第一个查询；
  * **DEPENDENT SUBQUERY** 表示子查询中的第一个查询，依赖于外部查询；
  * ......
* table 表示该行查询所涉及的表名；
* partitions 表示该行查询所涉及的分区；
* type 表示 MySQL 关联该行数据的类型，更多内容请见 \[关联类型]\(#EXPLAIN 关联类型)；
* possible\_keys 表示该行查询可以使用的索引；
* key 表示该行查询真实使用的索引；
* key\_len 表示该行查询真实使用的索引的字节数；
* ref 表示之前的表在 key 列所记录的索引中查找值时使用的列或者常量；
* rows 表示该行查询所涉及的行的总数估值；
* filtered 表示表中符合某个条件（WHERE 子句或者关联条件）的行的百分比估值；
* Extra 包含了 Mysql 解析该查询的额外信息，更多内容请见 \[额外信息]\(#EXPLAIN 额外信息)。

### EXPLAIN 关联类型

EXPLAIN 输出的 type 列描述了表是如何被关联的。下文列出了从最好到最差情况的关联类型：

* system 是一种当表中只有一行数据时的特殊的 const 类型；
* const 表示表中最多只有一行数据可以被匹配，并且该行需要在查询刚开始就被读取。由于查询结果只会有一行，所以在后续的优化过程中，优化器会将该行中列的值视为常量。当将 PRIMARY KEY 或者 UNIQUE Index 与常量进行比较的时候，MySQL 会使用 const 类型；
* eq\_ref 表示当前查询只会返回一行。当使用 PRIMARY KEY 或者 UNIQUE NOT NULL Index 进行查找时，MySQL 会使用 eq\_ref 类型；
* ref 表示当前查询会返回所有与索引值匹配的行。当仅使用索引的最左前缀或者索引不是 PRIMARY KEY 或者 UNIQUE Index 时，MySQL 会使用 ref 类型；
* fulltext 表示当前查询使用了 FULLTEXT 索引；
* ref\_or\_null 与 ref 类型相似，除了当前查询会额外包含 NULL 值以外；
* index\_merge 表示当前查询使用了索引合并优化；
* unique\_subquery 表示当前查询会在 IN 子句中，将 eq\_ref 替换为某些形式的子查询；
* index\_subquery 与 unique\_subquery 类型相似，除了它是用于处理非唯一索引以外；
* range 表示当前查询使用了索引来选择行，并且只会返回检索范围内的行。当在索引上使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN() 条件时，MySQL 会使用 range 类型；
* index 与 ALL 类型相似，除了当前查询会扫描索引树以外。
* all 表示全表扫描。

### EXPLAIN 额外信息

EXPLAIN 输出的 Extra 列描述了 MySQL 解析该查询的额外信息。如果开发者想要使查询更快地执行，则需要留意 Extra 列中出现 Using filesort 和 Using temporary 的情况。

Extra 列最重要的几个值如下：

* Using index 表示 MySQL 将使用覆盖索引，以避免访问表；
* Using Where 表示 MySQL 将在存储引擎检索后再进行过滤；
* Using temporary 表示 MySQL 在对查询结果排序时会使用一个临时表；
* Using filesort 表示 MySQL 会对查询结果使用一个外部索引排序，而不是按照索引次序从表中读取行。

## 参考资料

* [EXPLAIN Output Format](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain_ref)
* [《高性能MySQL》](https://book.douban.com/subject/23008813/)