mysql常见基础面试题（MySQL执行计划explain的应用）

mysql常见基础面试题（MySQL执行计划explain的应用）select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序本文档主要内容参考为官方文档在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。可以使用explain SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。官网doc地址：https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

一次工作中，写了一段sql语句，每次执行都超时，最后把超时时间设成了30分钟，然而最后还是没有执行出结果。

找了位大牛，只改了一行代码，原本怎么也执行不出来的代码瞬间出结果！当时那个崇拜的心情，敬佩的眼神，吃惊的神情，我已经可以脑补出当时的画面了。有没有人想知道改的什么吗？留言提问，在线解答，哈哈。

其实当时，就是大牛用explain看了下我的sql语句，发现没有走索引，然后就改了那行代码，让走了索引，然后就达到了神奇的效果。

######进入正题######

在企业的应用场景中，为了知道优化SQL语句的执行，需要查看SQL语句的具体执行过程，以加快SQL语句的执行效率。

可以使用explain SQL语句来模拟优化器执行SQL查询语句，从而知道mysql是如何处理sql语句的。

官网doc地址：https://dev.mysql.com/doc/refman/5.5/en/explain-output.html

本文档主要内容参考为官方文档

id

select查询的序列号，包含一组数字，表示查询中执行select子句或者操作表的顺序

explain select * from emp;

rows表示行数，表示需要做多少行的行数关联。

explain formatted select * from emp;

• 如果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;

• 如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

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

• id相同和不同，同时存在； 相同的可以认为是一组，从上往下顺序执行，在所有组中，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 where e.deptno in (select d.deptno from dept d where d.name = ‘SALES’);

select_type

主要用来分辨查询的类型，是普通查询、联合查询还是子查询

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

explain select * from emp;

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

explain select staname ename supname from (select ename staname mgr from emp) t join emp on t.mgr = emp.empno;

--union：若第二个select出现在union之后，则被标记为union

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

这里会把select * from emp where sal > 2000标记为union，前边的标记为PRIMARY。

一段sql中如果包含了查询，或union则外层的语句会被标记为PRIMARY，内层会被标记为UNION，DEPENDENT SUBQUERY

--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)

当看到一个sql语句时，大致可以根据sql语句的执行顺序来推测，explain的结果，例上，后边语句是一个union，同时也是一个子查询，所以select_type下边两个是union subquery，同时外边的结果又依赖于里边的结果，所以要加dependent.

--union result：从union表获取结果的select

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

关于最后一行，id为NULL，select_type为UNION RESULT，这里的意思是后边的语句执行后是一个结果了，是可以供外部语句直接使用的，所以不会有单独的id进行标识。

--subquery：在select或者where列表中包含子查询

explain select * from emp e where e.deptno in (select distinct deptno from dept)

该语句id为2的结果为什么是SUBQUERY，而上边的语句却是DEPENDENT QUERY，这是因为select avg(sal) from emp查出来的是一个值即常量，而DEPENDENT QUERY查出来的却是一个结果集，需要与结果行与前对比。

--dependent subquery：subquery的子查询要受到外部表查询的影响。

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

derived衍生结果集，可以理解为结果集，临时表，为缩小数据范围，提高查询效率而生成的中间结果集

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

查询缓冲区中是否有对应的数据，如果没有命中则说明没有对应的缓存即UNCACHEABLE SUBQUERY

select @@autocommit；#自动提交

select @@sort_buffer_size; #缓存的大小

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

重点掌握 PRIMARY UNION DEPENDENT UNION SUBQUERY DERIVED

table

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

• 如果是具体的表名，则表明从实际的物理表中获取数据，当然也可以是表的别名
• 表名是derived N的形式，表示使用了id为N的查询产生的衍生表

• 当有union result的时候，表名是union n1 n2等形式，n1 n2表示参与union的ID

当前用的比较多的语法分析工具

https://calcite.apache.org/

什么是Apache Calcite?

Apache Calcite是一款开源SQL解析工具，可以将各种SQL语法解析成的抽象语法术AST(Abstract Syntax Tree)，之后通过操作AST就可以把SQL中所要表达的算法与关系体现在具体代码之中。

type

type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想到的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：

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

一般情况下，得保证查询至少达到range级别，最好能达到ref

--all

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

explain select * from emp;

--index

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

explain select empno from emp;

其中empno是一个主键列。

--range

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

explain select * from emp where empno between 7000 and 7500;

range的前题是where中的条件是索引列，如果不是索引列，则还是ALL

--index_subquery

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

explain select * from emp where emp.job in (select job from t_job)

--unique_subquery

该连接类型类似于index_subquery 使用的是唯一索引

explain select * from emp e where e.deptno in (select distinct deptno from dept)

index_subquery与unique_subquery的区别是index_subquery是普通列索引，unique_subquery是主键索引。

--index_merge

在查询过程中需要多个索引组合使用，没有模拟出来

--ref_or_null

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

explain select * from emp e where e.mgr is null or e.mgr = 7369;

--ref

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

create index idx_3 on emp(deptno);

explain select * from emp e dept d wehre e.deptno = d.deptno;

--eq_ref

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

explain select * from emp emp2 where emp.empno = emp2.empno;

--const

这个表至多有一个匹配行

explain select * from emp where empno = 7369;

--system

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

possible_keys

显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则索引将被列出，但不一定被查询实际使用

explain select * from emp dept where emp.deptno = dept.deptno and emp.deptno = 10;

key

实际使用的索引，如果NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。

explain select * from emp dept wehre emp.deptno = dept.deptno and emp.deptno = 10;

key_len

表示索引中使用的字节数，可以通过key_len计算查询中使用的索引长度，在不损失精度的情况下长度越短越好。

explain select * from emp dept where emp.deptno = dept.deptno and emp.deptno = 10;

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

explain select * from emp dept where emp.deptno = dept.deptno and emp.deptno = 10;

rows

根据表的统计信息及索引使用情况，大致估算出所需要读取的行数，此参数很重要，直接反应了sql查找的数据量，在完成目的的情况下截止少越好。

explain select * from emp;

extra

包含额外的信息

--using filesort

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

explain select * from emp order by sal;

当使用索引列时，extra为空值，说明已经预读到缓存中了。

--using temporary

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

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

--using index

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

explain select deptno count(*) from emp group by deptno limit 10;

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

explain select * from t_user where id = 1;

--using join buffer:使用连接缓存

explain select * from emp dept where emp.deptno = dept.deptno ;

--impossible where：where语句的结果总是false

explain select * from emp where empno = 7469;

这节的内容着实有点儿长，在写的时候想过拆分为几节课，但又怕拆分后大家就懒得看下一篇了，所以就放在一篇里写了。

大家有没有看到满满的干货呢？还等什么赶快收藏，关注吧。

我是疯狂程序员，致力于打造高质量的程序员技术类文章，喜欢学习的朋友支持下吧。