闲聊MySQL（九）：浅析SQL执行计划

前言

在上一篇中，我们对MySQL中SQL的执行流程与SQL的优化进行了了解，我个人提倡的观点是多用explain检查SQL的执行效率，那么怎么定义一条SQL的执行效率的优劣呢？本篇，我们就来聊一下explain命令，SQL的执行计划。

本篇的示例基于MySQL版本8.0.16。

SQL执行计划

explain想必您一定已经非常的熟悉，根据MySQL官方文档的说法：

The EXPLAIN statement provides information about how MySQL executes statements.
EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

explain命令用来直观反映SQL是如何执行的。

explain命令的使用非常简单，只需要在你希望分析的SQL语句之前加入explain关键字即可，下面我们来看一个简单的SQL explain的执行效果：

上图就是explain的执行结果，我们来逐个分析一下explain执行计划结果集中的属性字段。

Explain输出字段

explain输出的结果集中，各个字段的含义，可以参见如下表格：

字段名称	含义
id	查询的唯一标识（The SELECT identifier）
select_type	查询类型（The SELECT type）
table	数据表名称（The table for the output row）
partitions	匹配到的分区（The matching partitions）
type	执行类型（The join type）
possible_keys	可能使用到的索引（The index actually chosen）
key	实际使用到的索引（The index actually chosen）
key_len	被选中的索引字段长度（The length of the chosen key）
ref	显示上述表的连接匹配条件，即哪些列或常量被用于查询索引列上的值（The columns compared to the index）
rows	预估要扫描的行数（Estimate of rows to be examined）
filtered	根据查询条件过滤行数的百分比（Percentage of rows filtered by table condition）
Extra	额外信息（Additional information）

我们对每个字段的含义来逐个分析一下。

id

查询的序列号，简单的说，一条SQL有几个select就有几个id。根据官方文档的说法，当引用了union的结果集时，这个值有可能为空。

select_type

查询的类型，其取值可以参考下表：

select_type	类型说明
SIMPLE	简单查询（不使用UNION或者子查询）
PRIMARY	最外层的查询
UNION	在UNION中的第二个和随后的SELECT
DEPENDENT UNION	UNION中第二个或之后的SELECT语句，取决于外面的查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT, 取决于外面的查询
DERIVED	衍生表（Derived table）FROM子句中的子查询
MATERIALIZED	物化子查询（Materialized subquery）
UNCACHEABLE SUBQUERY	结果集无法缓存的子查询，必须重新评估外部查询的每一行
UNCACHEABLE UNION	UNION中第二个或之后的SELECT，属于无法缓存的子查询

看完上面的表格，你可能跟我一样，一脸懵逼，但其实在实际开发中，我们最常用到的并没有这么多，最常见的select_type是SIMPLE、PRIMARY、UNION、SUBQUERY，我们来具体看几个栗子。

1、SIMPLE

简单查询。查询不包含子查询和union。

2、PRIMARY

复杂查询中最外层的 select。

3、UNION

联合查询。

4、SUBQUERY

子查询。

其他的查询类型，这里就不展开介绍了，如果您感兴趣，可以自行查找相关资料。

table

该字段表明查询的数据是来自于哪一张表的，其取值不一定是真实表名称，也可能是临时表名称。

partitions

分区信息，只针对使用分区表时，才会显示，非分区表，该字段为NULL。

type

显示关联类型或访问类型，这个是非常重要的字段，也是我们判断一个SQL执行效率的主要依据，我们来详细看一下。

依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

0、NULL：

null在官方文档中没有体现，但也是经常会出现的一个结果。mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

1、system：

The table has only one row (= system table). This is a special case of the const join type.

这是const联接类型的一个特例，表仅有一行满足条件。

2、const

The table has at most one matching row, which is read at the start of the query. Because there is only one row, values from the column in this row can be regarded as constants by the rest of the optimizer. const tables are very fast because they are read only once.

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。

const表很快，因为它们只读取一次！

const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。

3、eq_ref

One row is read from this table for each combination of rows from the previous tables. Other than the system and const types, this is the best possible join type. It is used when all parts of an index are used by the join and the index is a PRIMARY KEY or UNIQUE NOT NULL index.

对于每个来自于前面的表的行组合，从该表中读取一行。除了system与 const之外，这可能是最好的联接类型了。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

eq_ref可以用于使用 = 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

4、ref

All rows with matching index values are read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY KEY or UNIQUE index (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this is a good join type.

简单点解释，相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。

ref是我们日常开发中较为常见的情况，也是原则上期望要达到的级别，查询命中到索引。

如果使用的键仅仅匹配少量行，该联接类型是不错的。

ref可以用于使用=或<=>操作符的带索引的列。

5、fulltext

The join is performed using a FULLTEXT index.

使用全文索引的时候才会出现。

6、ref_or_null

This join type is like ref, but with the addition that MySQL does an extra search for rows that contain NULL values. This join type optimization is used most often in resolving subqueries.

这个查询类型和ref很像，但是 MySQL 会做一个额外的查询，来看哪些行包含了NULL。这种类型常见于解析子查询的优化。

7、index_merge

This join type indicates that the Index Merge optimization is used. In this case, the key column in the output row contains a list of indexes used, and key_len contains a list of the longest key parts for the indexes used.

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。

我是的确没有遇见过这个类型。

8、unique_subquery

This type replaces eq_ref for some IN subqueries of the following form:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery is just an index lookup function that replaces the subquery completely for better efficiency.

unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

9、index_subquery

This join type is similar to unique_subquery. It replaces IN subqueries, but it works for nonunique indexes in subqueries of the following form:

value IN (SELECT key_column FROM single_table WHERE some_expr)

与unique_subquery类似，就不说了。

10、range

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column is NULL for this type.

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() operators.

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。
在该类型中ref列为NULL。
当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range。

11、index

The index join type is the same as ALL, except that the index tree is scanned. This occurs two ways:

1、If the index is a covering index for the queries and can be used to satisfy all data required from the table, only the index tree is scanned. In this case, the Extra column says Using index. An index-only scan usually is faster than ALL because the size of the index usually is smaller than the table data.

2、A full table scan is performed using reads from the index to look up data rows in index order. Uses index does not appear in the Extra column.

index类型和ALL类型一样，区别就是index类型是扫描的索引树。以下两种情况会触发：

1. 如果索引是查询的覆盖索引，就是说索引查询的数据可以满足查询中所需的所有数据，则只扫描索引树，不需要回表查询。 在这种情况下，explain 的 Extra 列的结果是 Using index。仅索引扫描通常比ALL快，因为索引的大小通常小于表数据。
2. 全表扫描会按索引的顺序来查找数据行。使用索引不会出现在Extra列中。

12、ALL

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

全表扫描，不解释了。

possible_keys

这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有值，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，MySQL认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key

这一列显示MySQL真正使用的索引是什么。也有可能key的值不存在与possible_keys中，这种情况可能是possible_keys中没有特别合适的索引，MySQL选择了其他的索引进行查询。

For InnoDB, a secondary index might cover the selected columns even if the query also selects the primary key because InnoDB stores the primary key value with each secondary index.

在前面的文章中，我们介绍过InnoDB的索引结构，参见闲聊MySQL：（七）InnoDB之索引结构 ，InnoDB中，二级索引的B+Tree的叶子节点存储的是聚簇索引的位置，如果查询的字段可以命中二级索引指向的字段，可以省去回表的操作，也称之为覆盖索引。这也是查询优化中常用的的一个策略。

如果没有选择索引，key的值是NULL。

要想强制MySQL使用、或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

key_len列显示MySQL决定使用的索引键的长度。

如果索引键是NULL，则长度为NULL。

ref

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

说实话这一列的解释，MySQL官方文档给出的说明，真心没看懂，引用一下别的文章的说法，仅供参考：

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

rows

该列表明MySQL估计要读取并检查的行数，需要注意的是，这个不是结果集里的行数。

filtered

The filtered column indicates an estimated percentage of table rows that will be filtered by the table condition.

该列表明返回结果的行占需要读到的行(rows列的值)的百分比。MySQL官方文档说明的不是特别清楚，引用其他地方的一个解释，会比较清晰：

To filter here means to apply a condition on a set of rows that were selected by a type-search as potential rows, and to only keep rows that fulfill the condition:

MySQL will first try to use an index, e.g. do a range scan on your table a using the search-key. It estimates to get 174 rows out of using that index, which is the number in rows. This step is not yet called filtering.

After that, these 174 rows have to be checked against additional conditions (usually in your where-clause). MySQL now estimated that only 32 rows, so 18% of these 174 rows, will remain after that filter has been applied. This 18% is the value in filtered.

说的大概是啥意思呢？当我们执行一个查询语句时，MySQL首先会根据索引去扫描出一批数据行，然后再在这些数据行中，根据查询条件进行过滤，实际返回的行数 / 扫描出的结果行的百分比，就是filter的值。

Extra

该列表明了一些额外的信息来说明MySQL如何解析查询的。对于判断一个SQL的执行性能，也是非常重要的判断依据。

根据MySQL官方文档的说明，该字段的取值非常的多，我们就不一一进行解释说明，只拿一些较为常见的来说明。

1、Distinct

MySQL is looking for distinct values, so it stops searching for more rows for the current row combination after it has found the first matching row.

一旦MYSQL找到了与行相联合匹配的行，就不再搜索了。

2、Not exists

MySQL was able to do a LEFT JOIN optimization on the query and does not examine more rows in this table for the previous row combination after it finds one row that matches the LEFT JOIN criteria.

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了。

3、Range checked for each record

MySQL found no good index to use, but found that some of indexes might be used after column values from preceding tables are known. For each row combination in the preceding tables, MySQL checks whether it is possible to use a rangeor index_merge access method to retrieve rows. This is not very fast, but is faster than performing a join with no index at all.

没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。

这是使用索引的最慢的连接查询之一。

4、Using filesort

MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause. The keys then are sorted and the rows are retrieved in sorted order.

MYSQL需要进行额外的步骤，来发现如何对返回的行排序。

它根据连接类型、以及存储排序键值、和匹配条件的全部行的行指针来排序全部行。

表明你的SQL可能需要进行一定的优化。

5、Using index

The column information is retrieved from the table using only information in the index tree without having to do an additional seek to read the actual row. This strategy can be used when the query uses only columns that are part of a single index.

列数据是从仅仅使用了索引中的信息，而没有读取实际的行动的表返回的。

这发生在，对表的全部的请求列都是同一个索引的部分的时候。

这是非常棒的一种情况，不需要进行回表查询。

6、Using temporary

To resolve the query, MySQL needs to create a temporary table to hold the result. This typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

MYSQL需要创建一个临时表来存储结果。

这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。

表明你的SQL可能需要进行一定的优化。

7、Using where

A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index.

表示MySQL服务器在存储引擎收到记录后进行“后过滤”（Post-filter），如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。这个一般发生在MySQL服务器，而不是存储引擎层。一般发生在不能走索引扫描的情况下或者走索引扫描，但是有些查询条件不在索引当中的情况下。

结语

本篇，我们详细介绍了MySQL的SQL执行计划，explain是非常常用的一个命令，可以帮助我们判断SQL的执行性能的优劣，还是那句话，多用explain看一下代码中的SQL，排除隐患。
道路千万条，干掉慢SQL第一条。

感谢您的阅读。如果您对文章中哪些表述有异议，请留言给我，我会与您交流讨论。

更多精彩文章， 请关注我的个人公众号：老宣说
让我们一起共同学习成长！
感谢您的支持！

参考资料：

mysql官方文档：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

MySQL Explain解析：https://www.awaimai.com/2141.html

MySQL Explain解析腾讯云社区：https://cloud.tencent.com/developer/article/1093229

What is the meaning of filtered in MySQL explain? : https://dba.stackexchange.com/questions/164251/what-is-the-meaning-of-filtered-in-mysql-explain