MySQL优化从执行计划开始(explain超详细)

2021/6/13 19:21:24

编程Tag： 优化 mysql 查询索引 id SQL role explain user

本文主要是介绍MySQL优化从执行计划开始(explain超详细)，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

前言

小伙伴一定遇到过这样反馈：这页面加载数据太慢啦，甚至有的超时了，用户体验极差，需要赶紧优化；

反馈等同于投诉啊，多有几次，估计领导要找你谈话啦。

于是不得不停下手里头的活，赶紧进行排查，最终可能是程序处理的问题、也可能是并发量大导致排队问题、也可能是SQL查询性能导致等；而在很多时候，SQL查询缓慢是最直接拖慢系统的罪魁祸首，同样是实现一个功能，有的小伙伴毫秒级呈现效果，有的却要好几秒，而调优需要的花费时间不容小觑，最终可能就体现到个人业务能力上和形象上：哇，真牛逼，分分钟搞定；菜鸟，居然写出这样的SQL；

而对于SQL调优，搜索引擎一查，72般绝技绝对够秀，于是照着开始实操，运气好一下就解决啦，运气差的时候怎么用都不行；所以更重要的是业务场景，要学会分析原因，最后才知道用什么方式解决；而这个系列就来聊聊数据库优化，聊聊原因，聊聊方法。

1. MySQL逻辑结构先知

关于MySQL的逻辑结构，将其理解为四层，就像项目分层一样，每一层处理不同的业务逻辑，先看图后说话：

上图概述：

客户端：这里指连接MySQL各种形式，如.Net中使用的ADO连接、Java使用JDBC连接等；MySQL是客户端和服务器模式，前提先建立连接，才能传输数据，处理相关逻辑；
业务逻辑：在MySQL内部有很多模块组成，分别处理相关业务逻辑；

连接管理：负责连接认证、连接数判断、连接池处理等业务逻辑处理；

查询缓存：当一个SQL进来时，如果开启查询缓存功能，MySQL会优先去查询缓存中检查是否有数据匹配，如果匹配上，就不会再去解析对应的SQL啦，但如果语句中有用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表时，都不会走缓存；对于查询缓存来说，在MySQL8.0已经去除，官方回应的是在一定场景上，查询缓存会导致性能上的瓶颈。

解析器：对于一个SQL语句，MySql根据语法规则需要对其进行解析，并生成一个内部能识别的解析树；

优化器：负责对解析器得到的解析树进行优化，MySQL会根据内部算法找到一个MySQL认为最优的执行计划，后续就按照这个执行计划执行。所以后续我们分析的就是MySQL针对SQL语句选择出来的最优执行计划，结合业务，根据规则对SQL进行优化，从而让SQL语句在MySQL内部达到真正的最优。

执行器：得到执行计划之后，就会找到对应的存储引擎，根据执行计划给出的指令依次执行。
存储引擎：数据的存储和提取最后是靠存储引擎；MySQL内部实现可插拔式的存储引擎机制，不同的存储引擎执行不同的逻辑；
物理文件：数据存储的最终位置，即磁盘上；协同存储引擎对数据进行读写操作。

关于MySql的逻辑结构，以上只是简单描述，业务逻辑层的功能模块远不止上面提到的，小伙伴有兴趣可以专门研究一下，这里的目的就是为了体现SQL语句到服务器上时经过的几个关键步骤，方便后续优化的理解。

2. SQL语句的中关键字执行顺序须知

在编写一条查询语句时，习惯性的从头到尾开始敲出来，应该都是从select 开始吧，但似乎没太注意它们真正的执行顺序；既然要优化，肯定需要得知道一条SQL语句大概的执行流程，结合执行计划，目的就更加清晰啦；上一张一看就明白的图：

关键字简述：

FROM：确定数据来源，即指定表；
JOIN…ON：确定关联表和关联条件；
WHERE：指定过滤条件，过滤出满足条件的数据；
GROUP BY：按指定的字段对过滤后的数据进行分组；
HAVING：对分组之后的数据指定过滤条件；
SELECT：查找想要的字段数据；
DISTINCT：针对查找出来的数据进行去重；
ORDER BY：对去重后的数据指定字段进行排序；
LIMIT：对去重后的数据限制获取到的条数，即分页；

好啦，大概了解MySQL的逻辑结构和SQL查询关键字执行顺序之后，接下来就可以好好说说执行计划啦。

3. 好好说说执行计划

通过上面的逻辑结构，当一个SQL发送到MySQL执行时，需要经过内部优化器进行优化，而使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL的，即SQL的执行计划；根据explain提供的执行计划信息分析SQL语句，然后进行相关优化操作。接下来的示例演示用到五张表：USER(用户表)、MENU(菜单表)、ROLE(角色表)、USER_ROLE(用户角色关系表)、ROLE_MENU(角色菜单关系表)、ADDR(用户地址表，这里认为和用户一一对应)、FRIEND(朋友表，一对多关系)，它们的关系这里就不详细说了吧，小伙伴肯定都明白，这是管控菜单权限的五张基础表和两个基础信息表；

演示用的版本是MySql5.5，各版本之间会有不同，所以小伙伴用的版本测试结果不一样的时候，千万别骂我渣哦；其实重要的是查看的思路，整体是大同小异。(求原谅…)

通过explain会输出如下信息，很多小伙伴只关注红框标注部分(即索引)，但其实是不够的，接下来就一个一个好好说说。

id

这个id和咱们平时表结构设计的主键ID不太一样，这里的id代表了每一条SQL语句执行计划中表加载的顺序，分为三种情况：

id相同的时候：这时是从上到下依次执行；
```
EXPLAIN SELECT t.ID,t.USER_NAME,r.ROLE_NAME FROM USER t 
	JOIN USER_ROLE tr ON t.ID = tr.USER_ID
	JOIN ROLE r ON tr.ROLE_ID = r.ID
```
执行如下语句，得如下结果：

如上图所示，id一样，从上到下依次执行，所对应表加载顺序为t->tr->r(这里的表是别名)；

id不同的时候：当id不同的时，id越大的越先执行；
```
EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
	WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm 
		WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1))
```
子查询会导致id递增，结果如下：

如上图所示，id递增啦，所对应表的加载顺序为ur->rm->t(这里的表是别名)；

id相同和不同同时存在时：id相同的认为是同一组，还是从上往下加载；不一样的情况还是越大越优先执行
```
EXPLAIN SELECT t.ROLE_ID,m.ID,m.MENU_NAME,m.MENU_URL FROM 
	(SELECT ROLE_ID FROM USER_ROLE WHERE USER_ID=3) t,ROLE_MENU rm,MENU m
	WHERE t.ROLE_ID=rm.ROLE_ID
	AND rm.MENU_ID=m.ID
```
执行结果如下：

如上图所示，id有一样的，也有不同的，则对应表的加载顺序为USER_ROLE->derived2 (衍生表)->rm->m；衍生表表名后面的2代表的是id，所以可以通过衍生表表名后面的id知道是哪一步产生的，即derived2衍生表是id为2的这一步产生的。
select_type

select_type 是表示每一步的查询类型，方便分析人员很直接的看到当前步骤执行的是什么查询，有多种类型，见下图：

1> SIMPLE：简单的SELECT查询，不包含子查询或UNION的那种；
```
EXPLAIN SELECT * FROM USER;
```
输出结果如下：

2> PRIMARY：查询语句中包含其他子查询或UNION操作，那最外层的SELECT就被标记为该类型；

如上图所示，查询中包含子查询，最外层查询被标记为PRIMARY；

3> SUBQUERY：在SELECT或WHERE中包含的子查询会被标记为该类型；

见PRIMARY图，当存在子查询时，会将子查询标记为SUBQUERY

4> MATERIALIZED：被物化的子查询，即针对对应的子查询将其物化为一个临时表；
```
EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
	WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm 
		WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1));
```
测试物化用的是MySQL8.0，和5.*版本有所不同，输出结果如下：

如上图所示，将子查询物化为一个临时表subquery2，这个功能是可以通过设置优化器对应的开关的。

5> DERIVED：在FROM之后的子查询会被标记为该类型，同样会把结果放在一个临时表中；
```
EXPLAIN SELECT tm.MENU_NAME,rm.ROLE_ID FROM 
	(SELECT * FROM MENU WHERE ID >3 ) tm ,ROLE_MENU rm 
	WHERE tm.ID=rm.MENU_ID AND rm.ROLE_ID=1
```
输出结果：

如图所示，FROM后面跟的子查询就被标记为DERIVED，对应步骤产生的衍生表为derived2。高版本好像对其进行了优化，8.0版本这种形式认为是简单查询。

6> UNION：UNION操作中，查询中处于内层的SELECT；
```
EXPLAIN SELECT * FROM USER_ROLE T1 WHERE T1.USER_ID=1
UNION
SELECT * FROM USER_ROLE T2 WHERE T2.USER_ID=2
```
输出结果如下：

如上图所示，将第二个SELECT标注为UNION ，即对应加载的表为T2。

**7> UNIOIN RESULT：**UNION操作的结果，对应的id为空，代表的是一个结果集；

见UNIOIN图，UNIOIN RESULT代表的是UNION之后的结果，对应id为空。
table

table代表对应步骤加载的是哪张表，中间会出现一些临时表，比如subquery2、derived2等这种，最后的数字代表产生该表对应步骤的id。
type

代表访问类型，MySQL内部将其分为多类型，常用的类型从好到差的顺序展示如下：

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

而在实际开发场景中，比较常见的几种类型如下：const->eq_ref->ref->range->index->ALL(顺序从好到差)，通常优化至少在range级别或以上，比如ref算是比较不错的啦；

上面说到的从好到差指的是查询性能。

1>const：表示通过索引一次就找到数据，用于比较primary key或者unique索引，很快就能找到对应的数据；

2>eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描；

3>ref：非唯一索引扫描，返回匹配的所有行，如建立一个朋友维护表，维护用户对应的朋友，而在用户ID建立非唯一索引；

4>range：使用一个索引检索指定范围的行，一般在where语句中会出现between、<、>、in等范围查询；

5>index：全索引扫描，只遍历索引树；

6>ALL：全表扫描，找到匹配行。与index比较，ALL需要扫描磁盘数据，index值需要遍历索引树。
possible_keys

显示可能被用到的索引，但在实际查询中不一定能用到；查询涉及到字段，如果存在索引，会被列出，但如果使用的是覆盖索引，只会在key中列出；
key

实际使用到的索引，如果为NULL代表没有使用到索引；这也是平时小伙伴判断是否用上索引的关键。

key_len

key_len表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到，非常重要；key_len是根据表定义计算而得。这里测试在USER表中对USER_NAME创建一个非唯一索引，如下：

这里key_len是这么计算的，前提是指定的字符串集是utf8，可变长且允许为空，计算过程如下：

128(设置的可变长度)*3(utf8占3字节)+1(允许为空标识占一个字节)+2(长度信息占两个字节)=387；

key_len针对不同类型字段的计算规则不一样，这里用USER(用户表)简单计算为例：

字段	Key_len	说明
ID(int，不为空)	4	int为4个字节，不为空
USER_NAME(varchar(128)，utf8，可为空)	128*3+1+2=387	可变为128，utf8每个占3字节，1个字节标识可控，两个字节标识长度

不同类型占用的字节不一样，字符集不一样占用的字节也不一样，允许为空的字段需要1个字节做标识，可变长度的字段需要2个字节标识长度。小伙伴照着这个思路就可以计算其他类型啦。

ref

显示索引的哪些列被引用了，通常是对应字段或const；
rows

根据表统计信息和索引的使用情况，大概估算出找到所需记录数据所扫描的数据行数；不是所需数据的行数。
Extra

这个字段里包含一些其他信息，但也是优化SQL的重要参考，通常会出现以下几种信息：

Using index：表示查询语句中用到了覆盖索引，不访问表的数据行，查询效率比较好。

如果用SELECT *进行查询，就不会有Using index，关于索引的介绍下篇好好说说。

Using filesort：代表MySQL会使用一个外部索引对数据进行排序(文件排序)，而不是使用表内索引。这种情况在SQL查询需要避免，最好不要在Extra中出现此类型：

通常会是使用ORDER BY语句导致，上图中使用无索引的字段进行排序会出现，同样如果使用有索引的字段，但用法不对也会出现，比如使用组合索引不规范时。

Using temporary：产生临时表保存中间结果，这种SQL是不允许的，遇见数据量大的场景，基本就跑不动啦；

这种类型常常因为ORDER BY 和 GROUP BY导致，所以在进行数据排序和分组查询时，要注意索引的合理利用。

Using where：使用where过滤数据，小伙伴试一把。

Using join buffer：表示使用到了表连接缓存；当表数据量大，可能导致buffer过大，查询效率比较低，这种情况注意在表连接字段上正确使用索引。

如果表连接查询慢时，在连接字段上加个索引试试，药到病除；

**impossible where：**代表where后面的条件永远为false，匹配不到数据；

用到的表及数据从Gitgub中获取：https://github.com/zyq025/SQL_Optimize