EXPLAIN详解

新时代农民工0101
104 阅读5分钟

EXPLAIN用于查看某个查询语句的具体执行计划。执行计划各列描述 image.png

表结构

CREATE TABLE `single_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key1` varchar(100) DEFAULT NULL,
  `key2` int(11) DEFAULT NULL,
  `key3` varchar(100) DEFAULT NULL,
  `key_part1` varchar(100) DEFAULT NULL,
  `key_part2` varchar(100) DEFAULT NULL,
  `key_part3` varchar(100) DEFAULT NULL,
  `common_field` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_key1` (`key1`),
  UNIQUE KEY `uk_key2` (`key2`),
  KEY `idx_key3` (`key3`),
  KEY `idx_key_part` (`key_part1`,`key_part2`,`key_part3`)
) ENGINE=InnoDB CHARSET=utf8;

执行计划各列详解

id

在查询语句中每出现一个SELECT关键字，就会分配一个唯一ID。ID相同从上往下执行，ID不同，ID值越大，优先级越高

  • 连接查询：每个表对应一条记录，这些记录ID值相同
explain select * from s1 inner join s2;

image.png 出现在前面的是驱动表，出现在后面的是被驱动表

  • 子查询：每个SELECT对应一个ID
explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';

image.png

注意：查询优化器可能会对子查询进行重写，转换成半连接 直接查看执行计划，可以知道查询优化器是否对某个包含子查询的语句进行了重写

mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key3 FROM s2 WHERE common_field = 'a');
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref               | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
|  1 | SIMPLE      | s2    | NULL       | ALL  | idx_key3      | NULL     | NULL    | NULL              | 9954 |    10.00 | Using where; Start temporary |
|  1 | SIMPLE      | s1    | NULL       | ref  | idx_key1      | idx_key1 | 303     | xiaohaizi.s2.key3 |    1 |   100.00 | End temporary                |
+----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)

执行计划s1和s2表对应的id都为1，表明查询优化器将子查询转换为连接查询。

  • UNION子句：ID为NULL
explain select * from s1 union select * from s2;

image.png

select_type

  • SIMPLE：不包含UNION、子查询，但可以是单表查询，连接查询
  • PRIMARY、UNION 和 UNION RESULT：
    包含UNION、UNION ALL或子查询，最左边的那个查询的select_type为PRIMARY； 包含UNION、UNION ALL查询，除最左边的小查询外，其余小查询select_type为UNION； UNION RESULT是MySQL使用临时表完成UNION查询的去重工作；
使用union，s1的select_type为PRIMARY,s2的select_type为union，
UNION RESULT是MySQL使用临时表完成UNION查询的去重工作
explain select * from s1 union select * from s2;

image.png

使用union all，s1的select_type为PRIMARY,s2的select_type为union
explain select * from s1 union all select * from s2;

image.png

子查询的select_type为PRIMARY
explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';

image.png

  • SUBQUERY：包含不相关子查询的查询语句不能转换为半连接，查询优化器会将该子查询物化，子查询只执行一遍，方便后续直接从物化表查询，那么该子查询对应的select_type为SUBQUERY
explain select * from s1 where key1 in (select key1 from s2) or key3 = 'a';

image.png

  • DEPENDENT SUBQUERY：如果包含相关子查询的查询语句不能转换为半连接，那么该子查询被查询优化器转换为相关子查询。
explain select * from s1 where key1 in (select key1 from s2 where s1.key2 = s2.key2) or key3 = 'a';

image.png select_type为DEPENDENT SUBQUERY的子查询会被执行多次

  • DERIVED：包含派生表的查询中，以物化派生表的方式执行查询
explain select * from (select key1, count(*) as c from s1 group by key1) as derived_s1 where c > 1;

image.png table列为derived2，表示该查询时针对将派生表物化后的表进行查询

  • MATERIALIZED：将子查询物化后与外层查询进行连接查询
explain select * from s1 where key1 in (select key1 from s2);

image.png 执行计划的第三条记录的id值为2，说明该条记录对应的是一个单表查询，从它的select_type值为MATERIALIZED可以看出，查询优化器是要把子查询先转换成物化表。然后看执行计划的前两条记录的id值都为1，说明这两条记录对应的表进行连接查询，需要注意的是第二条记录的table列的值是subquery2，说明该表其实就是id为2对应的子查询执行之后产生的物化表，然后将s1和该物化表进行连接查询。

table

EXPLAIN语句输出的每条记录都对应某个单表的访问方法，该条记录的table列代表该表的表名

type

针对单表访问的方法

possible_keys和key

  • possible_keys可能用到的索引
  • key实际用到的索引
explain select * from s1 where key1 > 'z' and key3 = 'a'

image.png 经过查询优化器计算使用不同索引的使用成本后，最后决定使用idx_key3来执行查询
注意：possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len

通过key_len，可以直接看出形成扫描区间的边界条件。由三部分组成：
1）该列实际数据最多占用存储空间长度：
int+4字节，utf8的varchar(n)+3n字节
2）该列是否允许存储NULL值，是+1个字节
3）如果是变长类型的列，+2个字节

explain select * from s1 where key1 > 'z' and key3 = 'a'

image.png

ref

与索引列进行等值匹配的结果

rows

预估读取需要读取的记录条数

filtered

某个表经过搜索条件过滤后剩余记录数的百分比。在单表查询意义不大，在连接查询中表示驱动表对应的执行计划的filtered值

explain select * from s1 inner join s2 on s1.key1 = s2.key1 where s1.common_field = 'a'

image.png 把s1作为驱动表，s2作为被驱动表。驱动表s1的扇出值为9895X10%=989.5，说明还要对被驱动表s2执行大约989次查询

Extra

一些额外的信息

  1. Using where 某个搜索条件在server层进行判断
  2. Using join buffer(Block Nested Loop) 在连接查询出现
  3. Using filesort 在内存或磁盘中对结果集中的记录进行排序【耗时】
  4. Using temporary 建立内部临时表执行查询
  5. Using Index 使用覆盖索引执行查询，不需要回表
  6. Using Index condition索引条件下推，减少回表操作
explain select * from s1 where key1 > 'z' and key1 like '%a';
explain select * from s1 where key1 > 'z';

image.png 实际使用到idx_key1索引，扫描区间是(z,+oo)，在存储引擎层每查询一条记录，先判断key1 > 'z' and key1 like '%a'（多判断一次key1 > 'z'没关系的），不成立直接查找下一条记录，成立则回表读取完整的记录。

avatar
文章
阅读
粉丝