MySQL EXPLAIN属性分析
目录
测试数据准备
EXPLAIN 如何使用？
属性分析
id
select_type
type
possible_key、key
key_len
ref
rows
filtered
Extra
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()begindeclare i int;declare id1 int;declare id2 int;declare id3 int;declare id4 int;declare id5 int;set i = 1;while i < 5000 doset 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中的主查询。
SUBQUERY：包含子查询SQL中的内嵌查询。
DERIVED：衍生查询，表示在得到最终查询结果之前会用到临时表。
例：
EXPLAINSELECT cr.cnameFROM (SELECT * FROM course WHERE tid = 2 UNIONSELECT * FROM course WHERE tid = 3 ) cr;
UNION：用到了联合查询。
UNION RESULT：主要是显示哪些表之间存在 UNION 查询。
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;
ref：查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
例：
EXPLAIN SELECT td.tid FROM teacher_detail td WHERE td.phone = '13800000040';
range：使用索引范围扫描。
例：
EXPLAIN SELECT * FROM teacher WHERE tid < 1000;
index：全索引扫描。一般出现在索引条件区分度不高的场景下，未必比全表扫描快。
例：
EXPLAIN SELECT phone FROM teacher_detail WHERE phone LIKE '1380000%';
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
索引的长度(使用的字节数)。跟索引字段的类型、长度有关。
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 非驱动表的索引列同样会产生临时表。
忘是亡心i
素颜马尾好姑娘i
╰+攻爆jí腚メ
太过爱你忘了你带给我的痛
傷城~
迷南。
还没有评论，来说两句吧...