《MySQL是怎样运行的 —— 从跟上理解MySQL》—— 第十五章
一条查询语句在经过MySQL
查询优化器的各种基于成本和规则的优化会后生成一个执行计划
,这个执行计划展示了接下来具体执行查询的方式。MySQL
中提供了EXPLAIN
语句来帮助我们查询某个查询语句的具体执行计划。
查看某个查询的执行计划的话,可以在具体的查询语句前面加一个EXPLAIN
,在输出的结果中会包含以下列:
列名 | 描述 |
---|---|
id |
在一个大的查询语句中每个SELECT 关键字都对应一个唯一的id |
select_type |
SELECT 关键字对应的那个查询的类型 |
table |
表名 |
partitions |
匹配的分区信息 |
type |
针对单表的访问方法 |
possible_keys |
可能用到的索引 |
key |
实际上使用的索引 |
key_len |
实际使用到的索引长度 |
ref |
当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
rows |
预估的需要读取的记录条数 |
filtered |
某个表经过搜索条件过滤后剩余记录条数的百分比 |
Extra |
一些额外的信息 |
一、执行计划输出中各列详解
1.1 table
不论查询语句有多复杂,里边儿包含了多少个表,到最后也是需要对每个表进行单表访问的,MySQL
规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名。
1.2 id
查询语句一般都以SELECT
关键字开头,比较简单的查询语句里只有一个SELECT
关键字。但是下面两种情况下在一条查询语句中会出现多个SELECT
关键字:
- 查询中包含子查询的情况
- 查询中包含
UNION
语句的情况
查询语句中每出现一个SELECT
关键字,MySQL
就会为它分配一个唯一的id
值。
对于连接查询来说,一个SELECT
关键字后边的FROM
子句中可以跟随多个表,所以在连接查询的执行计划中,每个表都会对应一条记录,但是这些记录的id值都是相同的。在连接查询的执行计划中,每个表都会对应一条记录,这些记录的id列的值是相同的,出现在前面的表表示驱动表,出现在后边的表表示被驱动表。
对于包含子查询的查询语句来说,就可能涉及多个SELECT
关键字,所以在包含子查询的查询语句的执行计划中,每个SELECT
关键字都会对应一个唯一的id
值。
⚠️注意:查询优化器可能对涉及子查询的查询语句进行重写,从而转换为连接查询。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a'); |
可以看到,虽然查询语句是一个子查询,但是执行计划中s1
和s2
表对应的记录的id
值全部是1
,这就表明了查询优化器将子查询转换为了连接查询。
对于包含UNION
子句的查询语句来说,每个SELECT
关键字也是对应一个id
值,不过还是有点儿特别的东西:
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2; |
UNION
子句会把多个查询的结果集合并起来并对结果集中的记录进行去重,怎么去重呢?MySQL
使用的是内部的临时表。正如上面的查询计划中所示,UNION
子句是为了把id
为1
的查询和id
为2
的查询的结果集合并起来并去重,所以在内部创建了一个名为<union1, 2>
的临时表(就是执行计划第三条记录的table
列的名称),id
为NULL
表明这个临时表是为了合并两个查询的结果集而创建的。
跟UNION
对比起来,UNION ALL
不需要为最终的结果集进行去重,它只是单纯的把多个查询的结果集中的记录合并成一个并返回给用户,所以也就不需要使用临时表。所以在包含UNION ALL
子句的查询的执行计划中,就没有那个id
为NULL
的记录。
1.3 select_type
MySQL
为每一个SELECT
关键字代表的小查询都定义了一个称之为select_type
的属性,意思是只要知道了某个小查询的select_type
属性,就知道了这个小查询在整个大查询中扮演了一个什么角色。
select_type
的取值:
名称 | 描述 |
---|---|
SIMPLE |
Simple SELECT (not using UNION or subqueries) |
PRIMARY |
Outermost SELECT |
UNION |
Second or later SELECT statement in a UNION |
UNION RESULT |
Result of a UNION |
SUBQUERY |
First SELECT in subquery |
DEPENDENT SUBQUERY |
First SELECT in subquery, dependent on outer query |
DEPENDENT UNION |
Second or later SELECT statement in a UNION, dependent on outer query |
DERIVED |
Derived table |
MATERIALIZED |
Materialized subquery |
UNCACHEABLE SUBQUERY |
A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query |
UNCACHEABLE UNION |
The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY) |
SIMPLE
:查询语句中不包含UNION
或者子查询的查询都算作是SIMPLE
类型PRIMARY
:对于包含UNION
、UNION ALL
或者子查询的大查询来说,它是由几个小查询组成的,其中最左边的那个查询的select_type
值就是PRIMARY
UNION
:对于包含UNION
或者UNION ALL
的大查询来说,它是由几个小查询组成的,其中除了最左边的那个小查询以外,其余的小查询的select_type
值就是UNION
UNION RESULT
:MySQL
选择使用临时表来完成UNION
查询的去重工作,针对该临时表的查询的select_type
就是UNION RESULT
SUBQUERY
:如果包含子查询的查询语句不能够转为对应的semi-join
的形式,并且该子查询是不相关子查询,并且查询优化器决定采用将该子查询物化的方案来执行该子查询时,该子查询的第一个SELECT
关键字代表的那个查询的select_type
就是SUBQUERY
DEPENDENT SUBQUERY
:如果包含子查询的查询语句不能够转为对应的semi-join
的形式,并且该子查询是相关子查询,则该子查询的第一个SELECT
关键字代表的那个查询的select_type
就是DEPENDENT SUBQUERY
DEPENDENT UNION
:在包含UNION
或者UNION ALL
的大查询中,如果各个小查询都依赖于外层查询的话,那除了最左边的那个小查询之外,其余的小查询的select_type
的值就是DEPENDENT UNION
DERIVED
:对于采用物化的方式执行的包含派生表的查询,该派生表对应的子查询的select_type
就是DERIVED
MATERIALIZED
:当查询优化器在执行包含子查询的语句时,选择将子查询物化之后与外层查询进行连接查询时,该子查询对应的select_type
属性就是MATERIALIZED
1.4 type
执行计划的一条记录就代表着MySQL
对某个表的执行查询时的访问方法,其中的type
列就表明了这个访问方法的种类。
system
:当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的,比如MyISAM、Memory,那么对该表的访问方法就是system
。const
:当根据主键或者唯一二级索引列与常数进行等值匹配时,对单表的访问方法就是const
eq_ref
:在连接查询时,如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话,所有的索引列都必须进行等值比较),则对该被驱动表的访问方法就是eq_ref
ref
:当通过普通的二级索引列与常量进行等值匹配时来查询某个表,那么对该表的访问方法就可能是ref
fulltext
:全文索引ref_or_null
:当对普通二级索引进行等值匹配查询,该索引列的值也可以是NULL
值时,那么对该表的访问方法就可能是ref_or_null
index_merge
:一般情况下对于某个表的查询只能使用到一个索引,但单表访问方法时在某些场景下可以使用Intersection
、Union
、Sort-Union
这三种索引合并的方式来执行查询:mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
| 1 | SIMPLE | s1 | NULL | index_merge | idx_key1,idx_key3 | idx_key1,idx_key3 | 303,303 | NULL | 14 | 100.00 | Using union(idx_key1,idx_key3); Using where |
+----+-------------+-------+------------+-------------+-------------------+-------------------+---------+------+------+----------+---------------------------------------------+
1 row in set, 1 warning (0.01 sec)从执行计划的
type
列的值是index_merge
就可以看出,MySQL
打算使用索引合并的方式来执行对s1
表的查询。unique_subquery
:类似于两表连接中被驱动表的eq_ref
访问方法,unique_subquery
是针对在一些包含IN
子查询的查询语句中,如果查询优化器决定将IN
子查询转换为EXISTS
子查询,而且子查询可以使用到主键进行等值匹配的话,那么该子查询执行计划的type
列的值就是unique_subquery
index_subquery
:index_subquery
与unique_subquery
类似,只不过访问子查询中的表时使用的是普通的索引range
:如果使用索引获取某些范围区间
的记录,那么就可能使用到range
访问方法index
:当可以使用索引覆盖,但需要扫描全部的索引记录时,该表的访问方法就是index
ALL
:全表扫描
1.5 possible_key和key
在EXPLAIN
语句输出的执行计划中,possible_keys
列表示在某个查询语句中,对某个表执行单表查询时可能用到的索引有哪些,key
列表示实际用到的索引有哪些。
特别的地方:在使用index
访问方法来查询某个表时,possible_keys
列是空的,而key
列展示的是实际使用到的索引。
⚠️注意:possible_keys列中的值并不是越多越好,可能使用的索引越多,查询优化器计算查询成本时就得花费更长时间,所以如果可以的话,尽量删除那些用不到的索引。
1.6 key_len
key_len
列表示当优化器决定使用某个索引执行查询时,该索引记录的最大长度,它是由这三个部分构成的:
- 对于使用固定长度类型的索引列来说,它实际占用的存储空间的最大长度就是该固定值,对于指定字符集的变长类型的索引列来说,比如某个索引列的类型是
VARCHAR(100)
,使用的字符集是utf8
,那么该列实际占用的最大存储空间就是100 × 3 = 300
个字节。 - 如果该索引列可以存储
NULL
值,则key_len
比不可以存储NULL
值时多1个字节。 - 对于变长字段来说,都会有2个字节的空间来存储该变长列的实际长度(Server层,并不是针对具体某个存储引擎的功能)。
1.7 ref
当使用索引列等值匹配的条件去执行查询时,也就是在访问方法是const
、eq_ref
、ref
、ref_or_null
、unique_subquery
、index_subquery
其中之一时,ref
列展示的就是与索引列作等值匹配的东西,比如只是一个参数或者是某个列。
1.8 rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时,执行计划的rows
列就代表预计需要扫描的行数,如果使用索引来执行查询时,执行计划的rows
列就代表预计扫描的索引记录行数。
1.9 filtered
在分析连接查询的成本时提出过condition filtering
的概念,就是MySQL
在计算驱动表扇出时采用的一个策略:
- 如果使用的是全表扫描的方式执行的单表查询,那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
- 如果使用的是索引执行的单表扫描,那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a'; |
从执行计划的key
列中可以看出来,该查询使用idx_key1
索引来执行查询,从rows
列可以看出满足key1 > 'z'
的记录有266
条。执行计划的filtered
列就代表查询优化器预测在这266
条记录中,有多少条记录满足其余的搜索条件,也就是common_field = 'a'
这个条件的百分比。此处filtered
列的值是10.00
,说明查询优化器预测在266
条记录中有10.00%
的记录满足common_field = 'a'
这个条件。
对于单表查询来说,这个filtered
列的值没什么意义,我们更关注在连接查询中驱动表对应的执行计划记录的filtered
值。
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a'; |
从执行计划中可以看出来,查询优化器打算把s1
当作驱动表,s2
当作被驱动表。我们可以看到驱动表s1
表的执行计划的rows
列为9688
,
filtered
列为10.00
,这意味着驱动表s1
的扇出值就是9688 × 10.00% = 968.8
,这说明还要对被驱动表执行大约968
次查询。
1.10 Extra
Extra
列是用来说明一些额外信息的,我们可以通过这些额外信息来更准确的理解MySQL
到底将如何执行给定的查询语句。
常见的 || 重要的额外信息:
No tables used
:当查询语句的没有FROM
子句时将会提示该额外信息Impossible WHERE
:查询语句的WHERE
子句永远为FALSE
时将会提示该额外信息No matching min/max row
:当查询列表处有MIN
或者MAX
聚集函数,但是并没有符合WHERE
子句中的搜索条件的记录时,将会提示该额外信息Using index
:当我们的查询列表以及搜索条件中只包含属于某个索引的列,也就是在可以使用索引覆盖的情况下,在Extra
列将会提示该额外信息。Using index condition
:有些搜索条件中虽然出现了索引列,但却不能使用到索引
SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
其中的
key1 > 'z'
可以使用到索引,但是key1 LIKE '%a'
却无法使用到索引。在以前版本的
MySQL
中,是按照下面步骤来执行这个查询的:- 先根据
key1 > 'z'
这个条件,从二级索引idx_key1
中获取到对应的二级索引记录。 - 根据上一步骤得到的二级索引记录中的主键值进行回表,找到完整的用户记录再检测该记录是否符合
key1 LIKE '%a'
这个条件,将符合条件的记录加入到最后的结果集。
但是虽然
key1 LIKE '%a'
不能组成范围区间参与range
访问方法的执行,但这个条件毕竟只涉及到了key1
列,所以MySQL
把上面的步骤改进了一下:- 先根据
key1 > 'z'
这个条件,定位到二级索引idx_key1
中对应的二级索引记录。 - 对于指定的二级索引记录,先不着急回表,而是先检测一下该记录是否满足
key1 LIKE '%a'
这个条件,如果这个条件不满足,则该二级索引记录压根儿就没必要回表。 - 对于满足
key1 LIKE '%a'
这个条件的二级索引记录执行回表操作。
回表操作其实是一个随机
IO
,比较耗时,所以上述修改虽然只改进了一点点,但是可以省去好多回表操作的成本。MySQL
把这个改进称之为索引条件下推
(Index Condition Pushdown
)。如果在查询语句的执行过程中将要使用
索引条件下推
这个特性,在Extra
列中将会显示Using index condition
。- 先根据
Using where
:当使用全表扫描来执行对某个表的查询,并且该语句的WHERE
子句中有针对该表的搜索条件时,在Extra
列中会提示上述额外信息。当使用索引访问来执行对某个表的查询,并且该语句的WHERE
子句中有除了该索引包含的列之外的其他搜索条件时,在Extra
列中也会提示上述额外信息。Using join buffer (Block Nested Loop)
:在连接查询执行过程中,当被驱动表不能有效的利用索引加快访问速度,MySQL
一般会为其分配一块名叫join buffer
的内存块来加快查询速度,也就是基于块的嵌套循环算法
。Not exists
:当使用左(外)连接时(右(外)连接可以被转换为左(外)连接),如果WHERE
子句中包含要求被驱动表的某个列等于NULL
值的搜索条件,而且那个列又是不允许存储NULL
值的,那么在该表的执行计划的Extra
列就会提示Not exists
额外信息。Using intersect(...)
、Using union(...)
和Using sort_union(...)
:如果执行计划的Extra
列出现了Using intersect(...)
提示,说明准备使用Intersect
索引合并的方式执行查询,括号中的...
表示需要进行索引合并的索引名称;如果出现了Using union(...)
提示,说明准备使用Union
索引合并的方式执行查询;出现了Using sort_union(...)
提示,说明准备使用Sort-Union
索引合并的方式执行查询。Zero limit
:当LIMIT
子句的参数为0
时,表示压根儿不打算从表中读出任何记录,将会提示该额外信息。Using filesort
:有一些情况下对结果集中的记录进行排序是可以使用到索引的Using temporary
:在许多查询的执行过程中,MySQL
可能会借助临时表来完成一些功能,比如去重、排序之类的,比如在执行许多包含DISTINCT
、GROUP BY
、UNION
等子句的查询过程中,如果不能有效利用索引来完成查询,MySQL
很有可能寻求通过建立内部的临时表来执行查询。如果查询中使用到了内部的临时表,在执行计划的Extra
列将会显示Using temporary
提示。执行计划中出现
Using temporary
并不是一个好的征兆,因为建立与维护临时表要付出很大成本的,所以最好能使用索引来替代掉使用临时表Start temporary, End temporary
:查询优化器会优先尝试将IN
子查询转换成semi-join
,而semi-join
又有好多种执行策略,当执行策略为DuplicateWeedout
时,也就是通过建立临时表来实现为外层查询中的记录进行去重操作时,驱动表查询执行计划的Extra
列将显示Start temporary
提示,被驱动表查询执行计划的Extra
列将显示End temporary
提示。LooseScan
:在将In
子查询转为semi-join
时,如果采用的是LooseScan
执行策略,则在驱动表执行计划的Extra
列就是显示LooseScan
提示FirstMatch(tbl_name)
:在将In
子查询转为semi-join
时,如果采用的是FirstMatch
执行策略,则在被驱动表执行计划的Extra
列就是显示FirstMatch(tbl_name)
提示
二、Json格式的执行计划
上面介绍的EXPLAIN
语句输出中缺少了一个衡量执行计划好坏的重要属性
—— 成本。
MySQL
提供了一种查看某个执行计划花费的成本的方式:在EXPLAIN
单词和真正的查询语句中间加上FORMAT=JSON
。
EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G |
执行结果:
EXPLAIN: { |
解释cost_info
s1
表:
"cost_info": { |
read_cost
是由下面这两部分组成的:
IO
成本检测
rows × (1 - filter)
条记录的CPU
成本rows和filter都是前面介绍执行计划的输出列,在JSON格式的执行计划中,rows相当于rows_examined_per_scan,filtered名称不变。
eval_cost
计算流程:检测
rows × filter
条记录的成本。
prefix_cost
就是单独查询s1
表的成本,也就是read_cost + eval_cost
data_read_per_join
表示在此次查询中需要读取的数据量
s2
表:
"cost_info": { |
由于s2
表是被驱动表,所以可能被读取多次,这里的read_cost
和eval_cost
是访问多次s2
表后累加起来的值,主要关注里边儿的prefix_cost
的值代表的是整个连接查询预计的成本,也就是单次查询s1
表和多次查询s2
表后的成本的和:968.80 + 193.76 + 2034.60 = 3197.16
三、Extented EXPLAIN
使用EXPLAIN
语句查看了某个查询的执行计划后,紧接着还可以使用SHOW WARNINGS
语句查看与这个查询的执行计划有关的一些扩展信息:
mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.common_field IS NOT NULL; |
可以看到SHOW WARNINGS
展示出来的信息有三个字段,分别是Level
、Code
、Message
。最常见的就是Code
为1003
的信息,当Code
值为1003
时,Message
字段展示的信息类似于查询优化器将我们的查询语句重写后的语句。
比如上面的查询本来是一个左(外)连接查询,但是有一个s2.common_field IS NOT NULL
的条件,这就会导致查询优化器把左(外)连接查询优化为内连接查询,从SHOW WARNINGS
的Message
字段也可以看出来,原本的LEFT JOIN
已经变成了JOIN
。