理解查询执行计划

依据表、列、索引和 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 会对查询结果使用一个外部索引排序，而不是按照索引次序从表中读取行。

参考资料

最后更新于1年前