背景

最近在做数据汇总,数据量大 表多 SQL 复杂 最后做出来之后 发布到 测试环境 我进行数据测试 当我看到Table一直在Loading状态 我心中默念 1 2 3 4 5 6… 第十二秒数据出来了 这真的是有点尴尬 虽然数据量大但是最多也就能接受2～3秒的数据响应

我回顾编码过程感觉 Java 层没法优化 通过打印的 sql语句 发现主要是 sql 响应慢 看来是索引没加号 于是一个表一个表的去分析优化 最终响应控制在两秒内 心中直呼 explain 牛X 遂决定写一篇关于EXPLAIN的整理文章

基础知识

我们在去使用 索引 的时候不是 你加上索引就代表索引 就完事的 我们需要了解索引的使用情况 是不是生效 当前索引的”等级是什么” 是不是还可以进行优化 等等一系列问题 所以我们在增加索引之后要去分析SQL 是否使用了建立的索引

EXPLAIN 使用介绍

EXPLAIN是 mysql 必不可少的一个分析工具，主要用来测试sql语句的性能及对sql语句的优化，或者说模拟优化器执行SQL语句。 在select语句之前增加explain关键字，执行后MySQL就会返回执行计划的信息，而不是执行sql。但如果from中包含子查询，MySQL仍会执行该子查询，并把子查询的结果放入临时表中。它显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句

select_type

1、SIMPLE：简单select，不使用union和子查询

2、PRIMARY：查询中包含任何复杂的子部分， 最外层的select被标记为PRIMARY

3、SUBQUERY：子查询中的第一个select

4、UNION：union后面的Select语句

SQL：

 EXPLAIN select id from student s UNION select id from class c

分析图：

5、DEPENDENT UNION：一般是子查询中的第二个select语句（取决于外查询，mysql内部也有些优化）

SQL：

 explain select * from student s where s.c_id in ( select s.c_id from student s1 where c_id = 2 union select s2.c_id from student s2 where s2.id >2000);

分析图：

6、UNION RESULT：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null

SQL：

 EXPLAIN select id from student s UNION select id from class c

分析图：

7、DERIVED：派生表的select（from子句的子查询）

MySQL 5.7 之后好像没有这个状态了

8、uncacheable subquery:表示使用子查询的结果不能被缓存

SQL：

 explain select * from student where c_id = (select id from student where id = and c_id=@@sort_buffer_size);

分析图：

9、unchchaable union:union 的结果不能被缓存

这个没有测试出来SQL语句

type（非常重要）

解释：type显示的是访问类型，访问类型表示我是以哪种方式访问我们的数据，最直接是All，扫描全表，效率最低。访问类型有很多，效率从最好到最坏依次是：

system -> const -> eq_ref -> ref -> fulltext -> ref_or_null -> index_ merge -> unique_subquery -> index_subquery -> range ->index -> All

一般情况下，要保证查询达到range级别，最好达到ref级别。

SQL 演示

1.All

全表扫描，出现这个，数据量比较大的情况下，需要优化

explain select * from emp;

2.index

全索引扫描，效率比All好。两种情况：1.使用了 覆盖索引 。2.使用了索引排序

explain select empno from emp;

3.range

表示利用索引查询限制了范围，在指定范围内查询

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

4.index_subquery

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

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

5.unique_subquery和index_subquery类似，使用的是唯一索引

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

6.index_merge

查询中使用多个索引组合使用

7.ref_or_null

对某个索引需要关联查询，也需要null值的条件

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

8.ref

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

create index idx_3 on emp(deptno);

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

9.eq_ref

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

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

10. const

表里最多就只有一行匹配

11.system

表只有一行记录，等于系统表

extra（重要）

• Using filesort：说明mysql会对数据 使用一个外部的索引排序 ，而不是按照表内的索引顺序进行读取。
• 说明mysql无法利用索引进行排序，只能利用排序算法进行排序，会消耗额外的位置
• explain select * from emp order by sal;
• Using temporary：MySQL在对查询结果排序时使用临时表。
• 建立临时表来保存中间结果，查询完成之后把临时表删除
• 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：Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；
• 使用where进行条件过滤
• explain select * from t_user where id = 1;
• Using join buffer：使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的joinbuffer调大一些。
• impossible where：where子句的值总是false ，不能用来获取任何 元组
• where语句的结果总是false
• SELECT * FROM student WHERE s_name = ‘变成派大星’ and id = ‘2’

possible_keys

显示可能应用在这张表中的索引，但 不一定被查询实际使用

key

实际使用的索引。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。一般来说，索引长度越长表示精度越高，效率偏低；长度越短，效率高，但精度就偏低。并不是真正使用索引的长度，是个预估值。

ref

表示哪一列被使用了，常数表示这一列等于某个常数。

rows

大致找到所需记录需要读取的行数。

filter

表示选取的行和读取的行的百分比，100表示选取了100%，80表示读取了80%。