MySQl 专栏持续更新 不说晦涩难懂的东西 尽量输出容易理解 和 使用的SQL技巧 和 初中级开发不是很常用的但很有用的知识

欢迎查看👉🏻👉🏻👉🏻SQL 专栏 查漏补缺 指教一二

每一次写博客对技术都会有更深入的理解 积少成多 百天计划我也想看看自己有多少成长 祝君好运 工作顺利

背景

最近在做数据汇总 数据量大 表多 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 s1.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 = 1 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%。

结尾

下一篇会整理索引失效的场景通过SQL例子的形式展示出来