MySQL EXPLAIN 详解
EXPLAIN 简介
The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.
如官方文档所述,EXPLAIN 指令提供了 MySQL 如何执行一条 SQL 语句的信息。通过使用 EXPLAIN,我们可以查看 SQL 语句的执行方式,进而排查 SQL 语句的瓶颈,然后进行优化。本文使用的 MySQL 版本是 5.7。
首先我们定义需要使用的示例表结构:
-- 队伍
CREATE TABLE `team` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL DEFAULT '',
`level` INT UNSIGNED NOT NULL DEFAULT 1,
`code1` INT UNSIGNED NOT NULL DEFAULT 0,
`code2` INT UNSIGNED NOT NULL DEFAULT 0,
`code3` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
INDEX `idx_code` (`code1`,`code2`,`code3`)
);
-- 成员
CREATE TABLE `member` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`team_id` INT UNSIGNED NOT NULL,
`no` INT UNSIGNED NOT NULL,
`name` VARCHAR(64) NOT NULL DEFAULT '',
`join_time` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_teamId` (`team_id`),
UNIQUE INDEX `uk_no` (`no`)
);
-- 成员详情
CREATE TABLE `member_detail` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`no` INT UNSIGNED NOT NULL,
`mission` VARCHAR(64) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE INDEX `uk_no` (`no`)
);
-- 插入数据
INSERT INTO team VALUES (NULL, '霸天虎', 1, 0, 0, 1),(NULL, '汽车人', 100, 0, 0, 2);
INSERT INTO member VALUES (NULL, 1, 1001, '威震天', 1618641132),(NULL, 1, 1002, '红蜘蛛', 1618641133),(NULL, 2, 1003, '擎天柱', 1618641134),(NULL, 2, 1004, '大黄蜂', 1618641135),(NULL, 2, 1005, '梅根福克斯', 1618641136);
INSERT INTO member_detail VALUES (NULL, 1001, '消灭宇宙'),(NULL, 1002, '篡位'),(NULL, 1003, '保护地球'),(NULL, 1004, '找山姆'),(NULL, 1005, '负责好看');
使用 EXPLAIN 的方式很简单,即 EPLAIN + SQL 语句
, 例如:
EXPLAIN SELECT * FROM member WHERE team_id = 1;
执行结果如下:
该结果即展示了 MySQL 执行该条 SQL 的执行计划,下面将逐一解释每个字段的含义。
字段详解
id
SELECT
查询的序列标识。当使用 JOIN
查询时,涉及多个 select:
- 当 id 相同时,执行顺序
由上向下
。
EXPLAIN SELECT t.name, m.name FROM team t, member m WHERE m.team_id = t.id;
- 当 id 不同时,表示包含子查询,id 值会递增。
id 值越大,优先级越高,越先执行。
EXPLAIN SELECT * FROM team t WHERE t.id = (SELECT MAX(team_id) FROM member m);
select_type
查询的类型,常见的值有:
SIMPLE
:简单查询,不包含UNION
或者子查询。PRIMARY
:查询中如果包含子查询或其他部分,外层的SELECT
将被标记为PRIMARY
。SUBQUERY
:子查询中的第一个SELECT
。UNION
:在UNION
语句中,UNION
之后出现的SELECT
。DERIVED
:在FROM
中出现的子查询将被标记为DERIVED
。UNION RESULT
:UNION
查询的结果。
table
当前查询执行的数据表。
partitions
查询所匹配记录所在的分区,对于未分区的表,值为 NULL
。
type
查询执行的类型,描述了查询是如何执行的。所有值的顺序从最优到最差排序为:
system
> const
> eq_ref
> ref
> fulltext
> ref_or_null
> index_merge
> unique_subquery
> index_subquery
> range
> index
> ALL
具体含义如下:
system
表中只有一行数据,是 const
的一种特例。
const
表中最多只有一行匹配的记录,一次查询就可以找到,常用于使用主键
或唯一索引
的所有字段作为查询条件。
EXPLAIN SELECT * FROM team t WHERE id = 1;
eq_ref
当连表查询时,前一张表的行在当前这张表中只有一行与之对应。是除了 system
与 const
之外最好的 join 方式,常用于使用主键
或唯一索引
的所有字段作为连表条件。
EXPLAIN SELECT * FROM member m, member_detail d WHERE m.no=d.no;
ref
使用普通索引作为查询条件,查询结果可能找到多个符合条件的行。
EXPLAIN SELECT * FROM member m WHERE m.team_id=1;
fulltext
查询使用到了全文索引。
ref_or_null
类似于 ref
,MySQL 在执行时还查询了值为 NULL
的行。
index_merge
当查询条件使用了多个索引时,表示开启了 Index Merge 优化,此时执行计划中的 key
列列出了使用到的索引;ken_len
列列出了所使用的索引的长度。
EXPLAIN SELECT * FROM member WHERE id=1 or team_id=1;
unique_subquery
替代了 eq_ref
。在一些使用 IN
子查询中,使用唯一索引 ,例如。
value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery
与 unique_subquery
类似,在 IN
子查询中,使用普通索引,例如:
value IN (SELECT key_column FROM single_table WHERE some_expr)
range
对索引列进行范围查询,执行计划中的 key
列表示哪个索引被使用了,key_len
列表示使用的索引的长度。
EXPLAIN SELECT * FROM member WHERE no>1001;
index
查询遍历了整棵索引树,与 ALL
类似,只不过扫描的是索引,而索引一般在内存中,速度更快。
EXPLAIN SELECT id FROM member;
ALL
查询遍历全表,很可能要读磁盘,速度最慢。
EXPLAIN SELECT * FROM member;
possible_keys
列出查询中可能被使用到的一个或多个索引,不一定被查询实际使用。
key
查询中实际使用到的索引。如果为 NULL
,则表示未建立索引或索引失效。
key_len
表示查询索引时使用的字节数,在满足需求的前提下越短越好。
ref
表示在查询索引时,哪些列或者常量被用来与索引的值进行比较。
rows
展示 MySQL 估算出的查询时需要遍历的行数,越少越好。
filtered
表示估算的经过查询条件删选出的列数的百分比。例如 rows
是 1000,filtered
是 50(50%),则实际筛选出的列数为 1000 * 50% = 500。
Extra
这一列包含了 MySQL 在查询时的一些额外信息。
特别注意,当 Extra
列包含 Using filesort
或 Using temporary
,MySQL 的性能会存在问题,需要尽可能避免。
Extra
列常见的信息如下:
Using filesort
在排序时使用了外部的索引排序,没有用到表内索引进行排序。
EXPLAIN SELECT * FROM team WHERE code1=1 ORDER BY code3;
Using temporary
MySQL 需要创建临时表来存储查询的结果,常见于 ORDER BY
和 GROUP BY
。
EXPLAIN SELECT count(*) FROM team GROUP BY code2;
对于这种情况,可以优化 GROUP BY
:
EXPLAIN SELECT count(*) FROM team GROUP BY code1,code2;
Using index
表明查询使用了覆盖索引,不用回表,查询效率非常高。
EXPLAIN SELECT code2 FROM team WHERE code1=1;
Using where
表明查询使用了 WHERE
子句进行条件过滤。一般在没有使用到索引的时候会出现。
需要注意的是如果不是特意进行全表扫描,例如 select * from member
,那么如果 Extra
中没有 Using where
,且 type
列的值是 ALL
或者 index
,则表示查询中有错误,需要排查。
EXPLAIN SELECT * FROM team WHERE level=1;
Impossible WHERE
表示 WHERE
子句的结果总是 false 且无法查到任意行。
EXPLAIN SELECT * FROM member WHERE team_id=1 and team_id=2;
Using join buffer (Block Nested Loop)
连表查询的方式,表示当被驱动表的没有使用索引的时候,MySQL 会先将驱动表读出来放到 join buffer
中,再遍历被驱动表与驱动表进行查询,具体见 MySQL JOIN 的内容。
EXPLAIN SELECT * FROM team t INNER JOIN member m ON t.level=m.join_time;
Using join buffer (Batched Key Access)
连表查询的方式,与 Using join buffer (Block Nested Loop)
类似。
MySQL 在链表查询时使用了 BKA
算法。
转载自:https://juejin.cn/post/6953444668973514789