文章目录
前言
在上一篇中,我们对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
andconst
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 aPRIMARY KEY
orUNIQUE 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 aPRIMARY KEY
orUNIQUE
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, andkey_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. Thekey_len
contains the longest key part that was used. Theref
column isNULL
for this type.
range
can be used when a key column is compared to a constant using any of the=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE
, orIN()
operators.
只检索给定范围的行,使用一个索引来选择行。key
列显示使用了哪个索引。key_len
包含所使用索引的最长关键元素。
在该类型中ref
列为NULL
。
当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN
或者IN
操作符,用常量比较关键字列时,可以使用range
。
11、index
The
index
join type is the same asALL
, 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 saysUsing index
. An index-only scan usually is faster thanALL
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 theExtra
column.
index
类型和ALL
类型一样,区别就是index
类型是扫描的索引树。以下两种情况会触发:
- 如果索引是查询的覆盖索引,就是说索引查询的数据可以满足查询中所需的所有数据,则只扫描索引树,不需要回表查询。 在这种情况下,explain 的
Extra
列的结果是Using index
。仅索引扫描通常比ALL快,因为索引的大小通常小于表数据。 - 全表扫描会按索引的顺序来查找数据行。使用索引不会出现在
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 avoidALL
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 becauseInnoDB
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 thekey
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 tablea
using thesearch
-key. It estimates to get 174 rows out of using that index, which is the number inrows
. 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 infiltered
.
说的大概是啥意思呢?当我们执行一个查询语句时,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 theLEFT 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
range
orindex_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
andORDER 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
orindex
.
表示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