详细对MySQL中执行计划每个字段的解释
一条查询语句在经过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 | 一些额外的信息 |
执行计划使用的表结构
#两张一模一样表结构的表,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;
不论我们的查询语句有多复杂,里边儿包含了多少个表,到最后也是需要对每个表进行单表访问的,所以规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法,该条记录的table列代表着该表的表名
每一个被查询优化后的查询对应一个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|
+--+-----------+-----+----------+-----+-------------+--------+-------+----+----+--------+-----------+
每条SQL语句可能包含多个查询,select_type就是定义每个查询的类型
SQL语句执行会走了哪几个分区
type列就表明了这个访问方法是个什么类型,在索引的访问方式那一章有做解释
对某个表执行单表查询时可能用到的索引有哪些,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列中的值并不是越多越好,可能使用的索引越多,查询优化器计算查询成本时就得花费更长时间,所以如果可以的话,尽量删除那些用不到的索引
key_len列表示当优化器决定使用某个索引执行查询时,该索引记录的最大长度,它是由这三个部分构成的:
> 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
当使用索引列等值匹配的条件去执行查询时,也就是在访问方法是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列
如果查询优化器决定使用全表扫描的方式对某个表执行查询时,执行计划的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条。
> 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次查询。
上述的EXPLAIN语句输出中缺少了衡量执行计划好坏的重要执行成本属性,通过使用JSON可以查询到执行计划所花费的成本
以上介绍了EXPLAIN中各个字段的含义,通过EXPLAIN可以查询出可以有效的帮助我们了解SQL脚本的执行情况。