EXPLAIN使用介绍

最新推荐文章于 2025-03-21 23:51:00 发布

转载最新推荐文章于 2025-03-21 23:51:00 发布 · 387 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://hujinyang.blog.youkuaiyun.com/article/details/103877385

Mysql 专栏收录该内容

19 篇文章

订阅专栏

本文详细介绍MySQL中的EXPLAIN命令，包括其使用方法及各属性的作用。通过实例解析id、select_type等字段含义，并给出优化建议。

EXPLAIN 语句提供了有关 MySQL 如何执行 SQL 的相关信息，是我们必须掌握的SQL优化神器。

官网对EXPLAIN的解释：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

测试数据准备

建表

DROP TABLE IF EXISTS course;
CREATE TABLE `course`
(
`cid` int NOT NULL AUTO_INCREMENT COMMENT '课程编号',
`tid` int DEFAULT NULL COMMENT '教师编号',
`cname` varchar(20) DEFAULT NULL COMMENT '课程名称',
PRIMARY KEY (cid)
) COMMENT = '课程信息表';

DROP TABLE IF EXISTS teacher;
CREATE TABLE `teacher`
(
`tid` int NOT NULL AUTO_INCREMENT COMMENT '教师编号',
`tname` varchar(20) DEFAULT NULL COMMENT '教师名字',
PRIMARY KEY (tid)
) COMMENT = '教师信息表';

DROP TABLE IF EXISTS teacher_detail;
CREATE TABLE `teacher_detail`
(
`tdid` int NOT NULL AUTO_INCREMENT COMMENT '详情编号',
`tid` int DEFAULT NULL COMMENT '教师编号',
`phone` varchar(20) DEFAULT NULL COMMENT '手机号',
PRIMARY KEY (tdid),
UNIQUE KEY (tid),
KEY(phone)
) COMMENT = '教师详细信息表';

初始化数据

delimiter $$
DROP PROCEDURE IF EXISTS init;
create procedure init()
begin
declare i int;
declare id1 int;
declare id2 int;
declare id3 int;
declare id4 int;
declare id5 int;
set i = 1;

while i < 5000 do
set id1 = i;
set id2 = i + 4;
set id3 = i + 5;
set id4 = i + 6;
set id5 = i + 9;

INSERT INTO `teacher`
VALUES (id1, CONCAT('老师', id1)),
(id2, CONCAT('教师', id2)),
(id3, CONCAT('老师', id3)),
(id4, CONCAT('老师', id4)),
(id5, CONCAT('老师', id5));

INSERT INTO `course`
VALUES (id1, id1,'英语'),
(id2, id2,'数学'),
(id3, id3,'物理'),
(id4, id4,'化学'),
(id5, id5,'语文');

INSERT INTO `teacher_detail`
VALUES (id1,id1, (13800000000 + id1)),
(id2,id2, (13800000000 + id2)),
(id3,id3, (13800000000 + id3)),
(id4,id4, (13800000000 + id4)),
(id5,id5, (13800000000 + id1));

set i = i + 10;
end while;
end
$$

call init();

EXPLAIN 如何使用

EXPLAIN 是MySQL 提供的原生关键字，使用非常简单，只需要在目标 SQL 前加上 EXPLAIN 再执行即可。

例如：

EXPLAIN SELECT * FROM teacher;

下面就依次分析返回结果中的属性。

属性分析

id

id字段体现了SQL的执行顺序。id相同时，先执行排在上面的。

EXPLAIN SELECT * FROM course c INNER JOIN teacher t ON c.tid = t.tid;

id不同时，先执行id较大的。

EXPLAIN SELECT * FROM course c WHERE c.tid = (SELECT td.tid FROM teacher_detail td WHERE td.phone = '13800000040');

select_type

select_type即查询类型，这里只介绍一些常见的类型。

SIMPLE：简单查询，不包含子查询与联合查询UNION。
PRIMARY：包含子查询SQL中的主查询,即复杂查询中最外层的 select
SUBQUERY：包含在 select 中的子查询(不在 from 子句中)
DERIVED：衍生查询，表示在得到最终查询结果之前会用到临时表。
UNION：用到了联合查询。
UNION RESULT：主要是显示哪些表之间存在 UNION 查询。<union2,3>代表 id=2 和 id=3 的查询存在 UNION。

例：

EXPLAIN SELECT cr.cname FROM ( SELECT * FROM course WHERE tid = 2 UNION SELECT * FROM course WHERE tid = 3 ) cr;

table列

这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

type

type代表连接类型，是执行计划中最为重要的属性之一，下面列举最常见的几种type。

效率依次从高到低：system > const > eq_ref > ref > range > index ≈ all

system：表中只有一行记录（等于系统表），const类型的特例。

const：使用主键或唯一索引进行比较。

eq_ref：通常出现在多表的 JOIN 查询，表示对于前表的每一个结果，都只能匹配到后表的一行结果(即一一对应，且后一张表对应字段索引一般是唯一索引)。一般是唯一性索引的查询(UNIQUE 或 PRIMARY KEY)。

例：

EXPLAIN SELECT * FROM course c INNER JOIN teacher t ON c.tid = t.tid;

如上：针对于course的某一条记录，关联时在teacher只有一条记录与之对应。

ref：查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。

例：

EXPLAIN SELECT td.tid FROM teacher_detail td WHERE td.phone = '13800000040';

range：使用索引范围扫描。

例：

EXPLAIN SELECT * FROM teacher WHERE tid < 1000;

index：全索引扫描。一般出现在索引条件区分度不高的场景下，未必比全表扫描快。

例：

all：全表扫描。

总结：一般来说，需要保证查询至少达到 range 级别，最好能达到 ref。index、与all 都是需要优化的。

possible_key、key

可能用到的索引和实际用到的索引。

possible_key 可以有一个或者多个，可能用到的索引不代表一定会用到。

反过来如果possible_key 为 null，key 就一定为空吗？

EXPLAIN SELECT phone FROM teacher_detail WHERE phone LIKE '%3800000040%';

当查询条件无法命中索引，但查询结果为索引字段时，就会出现这种情况，这里相对于用到了覆盖索引。

key_len

索引的长度(使用的字节数)。跟索引字段的类型、长度有关。

key_len计算规则如下:

ref

这一列显示了在索引列中，查找值所用到的列或常量，常见的有const（常量）、字段名、NULL。

rows

MySQL 认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

filtered

这个字段表示存储引擎返回的数据在 server 层过滤后，剩下多少满足查询的记录数量的比例，它是一个百分比。

例如全表扫描后，只有一条记录满足查询条件，那么 filtered 的值就会比较低。

EXPLAIN SELECT * FROM course WHERE tid = 100;

Extra

执行计划给出的额外的信息说明。

Using index：查询使用了覆盖索引，不需要回表。

Using where：两种情况：

查询使用了 where 过滤，但没有用到索引；
有索引的过滤，且不需要回表。

Using index condition：查询用到索引且需要回表。这里说的回表有可能是为了拿数据，有可能是为了进一步过滤（索引下推）。

Using filesort：不能使用索引来排序，用到了临时文件排序。

Using temporary：用到了临时表（内存或磁盘）。例如：

distinct 非索引列；
group by 非索引列；
使用 join 的时候，group by 非驱动表的索引列同样会产生临时表。

转载地址：https://hujinyang.blog.youkuaiyun.com/article/details/103877385