MySQL的EXPLAIN执行计划

MySQL为什么使用B+tree做索引？

 

https://mp.weixin.qq.com/s?__biz=Mzg4NDMyNTIwMw==&mid=2247485750&idx=1&sn=a04f919b389284f5384861c958268adb&chksm=cfb8a70af8cf2e1cfd07afda40609f6e014a7595b62ae0b65380756aeb5e1cdd1016032c65a5&token=430082365&lang=zh_CN&scene=21#wechat_redirect

”

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

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

EXPLAIN SELECT * from member;

运行结果

 

详解

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

ColumnMeaningid查询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 ValueMeaningSIMPLESimple SELECT (not using UNION or subqueries)PRIMARYOutermost SELECTUNIONSecond or later SELECT statement in a UNIONDEPENDENT UNIONSecond or later SELECT statement in a UNION, dependent on outer queryUNION RESULTResult of a UNION.SUBQUERYFirst SELECT in subqueryDEPENDENT SUBQUERYFirst SELECT in subquery, dependent on outer queryDERIVEDDerived tableUNCACHEABLE SUBQUERYA subquery for which the result cannot be cached and must be re-evaluated for each row of the outer queryUNCACHEABLE UNIONThe 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进行条件过滤。