还不会看MySQL的EXPLAIN执行计划？这篇文章能帮到你

我们在进行SQL优化的时候，主要是看where后面的字段有没有用到索引。如何看这个查询有没有用到索引，那就看Explain执行计划了。

关于索引相关的知识可以看看这篇文章：

“

👉MySQL为什么选择B+Tree做索引

”

关于Explain执行计划，我相信你在面试的时候肯定被问到过，那么这篇文章我们主要讲讲如何看Explain执行计划。

我们在查询语句前加上Explain，即可获取该语句的执行计划。

EXPLAIN SELECT * from member;

运行结果

详解

下面我将解释每个字段的含义。

Column	Meaning
id	查询id，可理解为查询的顺序
select_type	对应查询的类型
table	查询的表名
partitions	匹配的分区信息
type	访问类型，这个比较重要
possible_keys	可能用到的索引
key	实际用到的索引
key_len	实际使用到的索引的长度
ref	与索引进行等值匹配的信息
rows	预计要读取的行数
filtered	条件过滤后的剩余记录百分比
extra	额外信息

id

id的值为数字，表示查询中执行select子句或者操作表的顺序。

关于id有下列3种情况：

1.如果id值相同，执行顺序为从上至下

explain select * from emp e join dept d on e.deptno = d.deptno join salgrade sg on e.sal between sg.losal and sg.hisal;

2.如果id值不同，值越大执行的优先级越高

explain select * from emp e where e.deptno in (select d.deptno from dept d where d.dname = 'SALES');

select_type

用于分辨查询类型，比如普通查询，连表查询等。官网的解释如下：

select_type Value	Meaning
SIMPLE	Simple SELECT (not using UNION or subqueries)
PRIMARY	Outermost SELECT
UNION	Second or later SELECT statement in a UNION
DEPENDENT UNION	Second or later SELECT statement in a UNION, dependent on outer query
UNION RESULT	Result of a UNION.
SUBQUERY	First SELECT in subquery
DEPENDENT SUBQUERY	First SELECT in subquery, dependent on outer query
DERIVED	Derived table
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
UNCACHEABLE UNION	The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)

上面的解释你肯定看的云里雾里的，我们实际来写SQL看看每种出现的情景。

SIMPLE：简单的查询，不包含子查询和union

explain select * from emp;

PRIMARY：查询中若包含任何复杂的子查询，最外层查询则被标记为PRIMARY

EXPLAIN SELECT * FROM member m WHERE m.`code` =
(SELECT m2.`code` FROM member m2 WHERE m2.`code` < (SELECT m1.`code` FROM member m1 ORDER BY m1.`code` DESC LIMIT 1) ORDER BY m2.`code` DESC LIMIT 1);

UNION：若第二个SELECT出现在UNION之后，则被标记为UNION

explain select * from emp where deptno = 10 union select * from emp where sal >2000;

DEPENDENT UNION：跟UNION类似，此处的DEPENDENT 表示UNION或UNION ALL联合而成的结果会受外部表影响

explain select * from emp e where e.empno  in ( select empno from emp where deptno = 10 union select empno from emp where sal >2000)

UNION RESULT：从UNION表获取结果的SELECT

explain select * from emp where deptno = 10 union select * from emp where sal >2000;

SUBQUERY：在SELECT或者WHERE列表中包含子查询

explain select * from emp where sal > (select avg(sal) from emp);

DEPENDENT SUBQUERY：SUBQUERY的子查询要受到外部表查询的影响

explain select * from emp e where e.deptno in (select distinct deptno from dept d WHERE d.deptno = e.deptno) OR e.deptno = '30';

DERIVED: from子句中出现的子查询，也叫做派生类

EXPLAIN SELECT * FROM (SELECT deptno, count(*) as c FROM emp GROUP BY deptno) AS derived_s1 where c > '5';

UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存

explain select * from emp where empno = (select empno from emp where deptno=@@sort_buffer_size);

UNCACHEABLE UNION：表示union的查询结果不能被缓存

没有写出可验证的SQL😭。

table

对应行正在访问哪一个表，表名或者别名，可能是临时表或者union合并结果集

1. 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名

2. 表名是derivedN的形式，表示使用了id为N的查询产生的衍生表

3. 当有union result的时候，表名是union n1、n2等的形式，n1、n2表示参与union的id

type

表示访问类型，每种类型产生的查询效率不一样，效率从高到低依次为：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

效率最低的为ALL，表示全表扫描了。我们在sql优化时主要看type，并且可以按这个顺序优化。下面我将列举出每个场景的sql。

ALL：全表扫描，一般情况下出现这样的sql语句而且数据量比较大的话那么就需要进行优化。

explain select * from emp;

index：全索引扫描这个比all的效率要好，主要有两种情况，一种是当前的查询时覆盖索引，即我们需要的数据在索引中就可以索取，或者是使用了索引进行排序，这样就避免数据的重排序。

EXPLAIN SELECT * from member ORDER BY id;
EXPLAIN SELECT code from member;

range：表示利用索引查询的时候限制了范围，在指定范围内进行查询，这样避免了index的全索引扫描，适用的操作符：=、<>、 >、>=、<、<=、IS NULL、BETWEEN、LIKE、or、IN()。

EXPLAIN SELECT id from  member WHERE CODE = 99 OR CODE = 100;

index_subquery：利用索引来关联子查询，不再扫描全表。

没有写出可验证的SQL😭。

unique_subquery：该连接类型类似与index_subquery，使用的是唯一索引:该连接类型类似与index_subquery,使用的是唯一索引。

没有写出可验证的SQL😭。

index_merge：在查询过程中需要多个索引组合使用。

没有写出可验证的SQL😭。

“

以上3种都只模拟出index类型的。

”

ref_or_null：对于某个字段即需要关联条件，也需要null值的情况下，查询优化器会选择这种访问方式。

EXPLAIN SELECT * from member WHERE code is NULL or code = '13';

ref：使用了非唯一性索引进行数据的查找。

EXPLAIN SELECT * from member WHERE code = '99';

eq_ref ：使用唯一性索引进行数据查找。

explain select * from emp,bonus where emp.empno = bonus.empno;

const：这个表至多有一个匹配行。常见的通过主键索引获取一条数据type为const。

EXPLAIN SELECT * from member WHERE id = '1';

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现。

possible_keys

表示当前查询可能会用到的索引，实际会根据优化器有所改变。

EXPLAIN SELECT code from member;

key

实际使用到的索引。

key_len

表示索引的长度，一般越短越好。

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。复杂的查询可能不是常数。

explain select * from emp where emp.job in (select job from bonus);

rows

一个很重要的参数，预计找出所需记录需要读取的行数，一般越少越好。

filtered

在rows一样的情况下，filtered越大，扇出值越小，效率可能也越高。

Extra

额外信息。常见的几种类型如下：

using filesort：说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置。

explain select * from emp order by sal;

using temporary：建立临时表来保存中间结果，查询完成之后把临时表删除。

explain select ename,count(*) from emp where deptno = 10 group by ename;

using index：这个表示当前的查询是覆盖索引的，直接从索引中读取数据，而不用访问数据表。如果同时出现using where 表名索引被用来执行索引键值的查找，如果没有，表面索引被用来读取数据，而不是真的查找。

EXPLAIN SELECT id from  member WHERE CODE in (99, 100);

using where：使用where进行条件过滤。

见上面的例子。

好了关于Explain的字段介绍以及出现场景就介绍到这里啦，有没有收获呢。

福利

文中用的member表

CREATE TABLE `member` (
  `id` varchar(255) CHARACTER SET utf8mb4 NOT NULL COMMENT '主键id',
  `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '姓名',
  `code` int(11) DEFAULT NULL COMMENT '编号',
  `create_date` date DEFAULT NULL COMMENT '创建时间',
  UNIQUE KEY `idx_id` (`id`) USING BTREE,
  KEY `idx_code_name` (`code`,`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成员表';

其余的表后台回复【sql】即可获取建表文件sql和sql数据。

 

往期推荐

• 我写出这样干净的代码，老板直夸我

• 云南丽江旅游攻略

• 使用ThreadLocal怕内存泄漏？

• Java进阶之路思维导图

• 程序员必看书籍推荐

• 3万字的Java后端面试总结（附PDF）

扫码二维码，获取更多精彩。或微信搜Lvshen_9，可后台回复获取资料

 

1.回复"java" 获取java电子书；

2.回复"python"获取python电子书；

3.回复"算法"获取算法电子书；

4.回复"大数据"获取大数据电子书；

5.回复"spring"获取SpringBoot的学习视频。

6.回复"面试"获取一线大厂面试资料

7.回复"进阶之路"获取Java进阶之路的思维导图

8.回复"手册"获取阿里巴巴Java开发手册(嵩山终极版)

9.回复"总结"获取Java后端面试经验总结PDF版

10.回复"Redis"获取Redis命令手册，和Redis专项面试习题（PDF）

11.回复"并发导图"获取Java并发编程思维导图（xmind终极版）

另：点击【我的福利】有更多惊喜哦。