MySQL中的执行计划
详细对MySQL中执行计划每个字段的解释
1.概述
一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划，这个执行计划展示了接下来具体执行查询的方式，比如多表连接的顺序是什么，对于每个表采用什么访问方法来具体执行查询等等。
如果需要查询具体的执行计划，可通过在查询语句前追加EXPLAIN进行查看，例如：
flink_data_qnh> EXPLAIN SELECT 1
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+
|1 |SIMPLE |NULL |NULL |NULL|NULL |NULL|NULL |NULL|NULL|NULL |No tables used|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+--------------+ 除了以SELECT开头的查询语句，其余的DELETE、INSERT、REPLACE以及UPDATE语句前边都可以加上EXPLAIN这个词儿，用来查看这些语句的执行计划，不过这里只对SELECT进行解释
列名 | 描述 |
|---|---|
id | 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id |
select_type | SELECT关键字对应的那个查询的类型 |
table | 表名 |
partitions | 匹配的分区信息 |
type | 针对单表的访问方法 |
possible_keys | 可能用到的索引 |
key | 实际上使用的索引 |
key_len | 实际使用到的索引长度 |
ref | 当使用索引列等值查询时，与索引列进行等值匹配的对象信息 |
rows | 预估的需要读取的记录条数 |
filtered | 某个表经过搜索条件过滤后剩余记录条数的百分比 |
Extra | 一些额外的信息 |
2. 执行计划输出中各列详解
执行计划使用的表结构
#两张一模一样表结构的表，s1,s2
CREATE TABLE single_table
(
id INT NOT NULL AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY idx_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part (key_part1, key_part2, key_part3)
) Engine = InnoDB
CHARSET = utf8;2.1 table
不论我们的查询语句有多复杂，里边儿包含了多少个表，到最后也是需要对每个表进行单表访问的，所以规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法，该条记录的table列代表着该表的表名
2.2 id
每一个被查询优化后的查询对应一个id值，即有些查询语句存在子查询，但是被查询优化器转换成了连接查询，那么仍然视作一个查询
# 连接查询
> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------------------------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------------------------+
|1 |SIMPLE |s1 |NULL |ALL |NULL |NULL|NULL |NULL|9827|100 |NULL |
|1 |SIMPLE |s2 |NULL |ALL |NULL |NULL|NULL |NULL|9827|100 |Using join buffer (hash join)|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------------------------+
# 子查询
> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----------+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----------+
|1 |PRIMARY |s1 |NULL |ALL |idx_key3 |NULL |NULL |NULL|9827|100 |Using where|
|2 |SUBQUERY |s2 |NULL |index|idx_key1 |idx_key1|303 |NULL|9827|100 |Using index|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----------+2.3 select_type
每条SQL语句可能包含多个查询，select_type就是定义每个查询的类型
2.4 partitions
SQL语句执行会走了哪几个分区
2.5 type
type列就表明了这个访问方法是个什么类型，在索引的访问方式那一章有做解释
2.6 possible_keys和key
对某个表执行单表查询时可能用到的索引有哪些，key列表示实际用到的索引有哪些
>EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+
|1 |SIMPLE |s1 |NULL |ref |idx_key1,idx_key3|idx_key3|303 |const|1 |5 |Using where|
+--+-----------+-----+----------+----+-----------------+--------+-------+-----+----+--------+-----------+上述执行计划的possible_keys列的值是idx_key1,idx_key3，表示该查询可能使用到idx_key1,idx_key3两个索引，然后key列的值是idx_key3，表示经过查询优化器计算使用不同索引的成本后，最后决定使用idx_key3来执行查询
另外需要注意的一点是，possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引
2.7 key_len
key_len列表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度，它是由这三个部分构成的：
- 对于使用固定长度类型的索引列来说，它实际占用的存储空间的最大长度就是该固定值，对于指定字符集的变长类型的索引列来说，比如某个索引列的类型是VARCHAR(100)，使用的字符集是utf8，那么该列实际占用的最大存储空间就是100 × 3 = 300个字节。
- 如果该索引列可以存储NULL值，则key_len比不可以存储NULL值时多1个字节。
- 对于变长字段来说，都会有2个字节的空间来存储该变长列的实际长度。
> EXPLAIN SELECT * FROM s1 WHERE id = 5;
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+
|1 |SIMPLE |s1 |NULL |const|PRIMARY |PRIMARY|4 |const|1 |100 |NULL |
+--+-----------+-----+----------+-----+-------------+-------+-------+-----+----+--------+-----+由于id列的类型是INT，并且不可以存储NULL值，所以在使用该列的索引时key_len大小就是4
2.8 ref
当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配具体的值
> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+
|1 |SIMPLE |s1 |NULL |ref |idx_key1 |idx_key1|303 |const|1 |100 |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+-----+----+--------+-----+const标识匹配的是一个常量
> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
+--+-----------+-----+----------+------+-------------+-------+-------+----------+----+--------+-----+
|id|select_type|table|partitions|type |possible_keys|key |key_len|ref |rows|filtered|Extra|
+--+-----------+-----+----------+------+-------------+-------+-------+----------+----+--------+-----+
|1 |SIMPLE |s2 |NULL |ALL |PRIMARY |NULL |NULL |NULL |9827|100 |NULL |
|1 |SIMPLE |s1 |NULL |eq_ref|PRIMARY |PRIMARY|4 |test.s2.id|1 |100 |NULL |
+--+-----------+-----+----------+------+-------------+-------+-------+----------+----+--------+-----+可以看到对被驱动表s2的访问方法是eq_ref，而对应的ref列的值是xiaohaizi.s1.id，这说明在对被驱动表进行访问时会用到PRIMARY索引，也就是聚簇索引与一个列进行等值匹配的条件，于s2表的id作等值匹配的对象就是xiaohaizi.s1.id列
2.9 rows
如果查询优化器决定使用全表扫描的方式对某个表执行查询时，执行计划的rows列就代表预计需要扫描的行数，如果使用索引来执行查询时，执行计划的rows列就代表预计扫描的索引记录行数
> EXPLAIN SELECT * FROM s1 WHERE key1 > 'blue'
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE |s1 |NULL |ALL |idx_key1 |NULL|NULL |NULL|9827|49.99 |Using where|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+我们看到执行计划的rows列的值是1，这意味着查询优化器在经过分析使用idx_key1进行查询的成本之后，觉得满足key1 > ‘z’这个条件的记录只有1条。
2.10 filtered
- 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估计出满足搜索条件的记录到底有多少条。
- 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估计出满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
> EXPLAIN SELECT * FROM s1 WHERE key1 > 'blue' AND common_field = 'a';
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
|1 |SIMPLE |s1 |NULL |ALL |idx_key1 |NULL|NULL |NULL|9827|5 |Using where|
+--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+从执行计划的key列中可以看出来，该查询使用idx_key1索引来执行查询，从rows列可以看出满足key1 >’blue’的记录有9827条。执行计划的filtered列就代表查询优化器预测在这9827条记录中，有多少条记录满足其余的搜索条件，也就是common_field = ‘a’这个条件的百分比。此处filtered列的值是5.00，说明查询优化器预测在9827条记录中有5%的记录满足common_field = ‘a’这个条件。
对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值，比方说下边这个查询：
> EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field ='a';
+--+-----------+-----+----------+----+-------------+--------+-------+------------+----+--------+-----------+
|id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra |
+--+-----------+-----+----------+----+-------------+--------+-------+------------+----+--------+-----------+
|1 |SIMPLE |s1 |NULL |ALL |idx_key1 |NULL |NULL |NULL |9827|10 |Using where|
|1 |SIMPLE |s2 |NULL |ref |idx_key1 |idx_key1|303 |test.s1.key1|317 |100 |NULL |
+--+-----------+-----+----------+----+-------------+--------+-------+------------+----+--------+-----------+从执行计划中可以看出来，查询优化器打算把s1当作驱动表，s2当作被驱动表。我们可以看到驱动表s1表的执行计划的rows列为9827，filtered列为10.00，这意味着驱动表s1的扇出值就是9827× 10.00% =982.7，这说明还要对被驱动表执行大约982次查询。
2.11 Extra
- No tables used：当查询语句的没有FROM子句时将会提示该额外信息
- Impossible WHERE：查询语句的WHERE子句永远为FALSE时将会提示该额外信息
- No matching min/max row：当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录时，将会提示该额外信息
- Using index：当我们的查询列表以及搜索条件中只包含属于某个索引的列，也就是在可以使用索引覆盖的情况下，在Extra列将会提示该额外信息
- Using index condition：有些搜索条件中虽然出现了索引列，但却不能使用到索引
- Using where：当我们使用全表扫描来执行对某个表的查询，并且该语句的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提示
3. Json格式的执行计划
上述的EXPLAIN语句输出中缺少了衡量执行计划好坏的重要执行成本属性，通过使用JSON可以查询到执行计划所花费的成本
- 在EXPLAIN单词和真正的查询语句中间加上FORMAT=JSON。
4.总结
以上介绍了EXPLAIN中各个字段的含义，通过EXPLAIN可以查询出可以有效的帮助我们了解SQL脚本的执行情况。
参考
- 《MySQL是怎么样运行的》