MySQL 高级：explain 分析 SQL，索引失效 & 常见优化场景
本篇，我们先着重讲解如何分析，具体如何找到 SQL，后续的实战篇，我们再来详细谈一谈。
🎐【一、explain】分析 SQL
explain 中，包含了如下几个字段（不同版本可能会有所差异）：
看完是不是很懵，感觉好多要记忆的，别着急，下边我们通过实际案例，来加深记忆
id
id 字段是 select 查询的序列号，是一组数字，表示的是查询中执行 select 子句或者是操作表的顺序。id 情况有三种 ：
此处只是单表查询，id 只有一个
id 一样，则从上到下
id 不同，则 id 值越大，优先级越高
此处是嵌套子查询，最内部的子查询，自然是最先执行的
简而言之：
id 值越大，优先级越高；
id 值一样，则从上到下；
select_type
PRIMARY,SUBQUERY
DERIVED(需要临时表，自然比上述效率低)
type
结果值从最好到最坏以此是：
一般至少要达到 range 级别，最好达到 ref 。
const
唯一索引，非关联查询
eq_ref，ref
eq_ref 跟 const 的区别是：两者都利用唯一索引，但前者是关联查询，后者只是普通查询？eq_ref 跟 ref 的区别：后者是非唯一索引
index，all
都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。
不走索引就会遍历全表
possible_keys，key
possible_keys : 显示可能应用在这张表的索引， 一个或多个。
key ：实际使用的索引， 如果为 NULL， 则没有使用索引。（可能是没有走索引，需要分析）
key_len : 表示索引中使用的字节数， 在不损失精确性的前提下， 长度越短越好 。
单列索引，那么需要将整个索引长度算进去；
多列索引，不是所有列都能用到，需要计算查询中实际用到的列。
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。
当使用常量等值查询，显示 const
当关联查询时，会显示相应关联表的关联字段
如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为 func
其他情况为 null
id 是索引，而且是 id=1，一个常数，故 ref = const
user_id 不是索引，ref 直接为 null
t1.id 是索引，且=号后边不是常量，故显示 t1.id，即显示相应关联表的关联字段
rows
扫描行的数量，一般越小越好
用索引 rows 就为 1，无论是唯一索引还是非唯一索引
其他情况一般是全表扫描，rows 等于表的行数。
filtered
表里符合条件的记录数的所占的百分比。
extra
其他的额外的执行计划信息，在该列展示 ，需要把前两个优化为 using index。
using where
不同版本好像不一样
5.7：表示 MySQL 首先从数据表（存储引擎）中读取记录，返回给 MySQL 的 server 层，然后在 server 层过滤掉不满足条件的记录，即无法直接在存储引擎过滤掉。简单来说，就是查询时 where 中用的不是索引。
现在，我们知道怎么用 explain 来分析 SQL 语句了，自然可以来剖析我们的 SQL 语句的性能，不过早有先人给我们总结了几个需要优化的场景-->索引失效
🎐【二、索引失效】的几个场景
0. SQL 准备
1. 不满足最左前缀
所谓最左前缀，可以想象成一个爬楼梯的过程，假设我们有一个复合索引：name，status，address，那这个楼梯由低到高依次顺序是：name，status，address，最左前缀，要求我们不能出现跳跃楼梯的情况，否则会导致我们的索引失效：
按楼梯从低到高，无出现跳跃的情况--此时符合最左前缀原则，索引不会失效
出现跳跃的情况
直接第一层 name 都不走，当然都失效
走了第一层，但是后续直接第三层，只有出现跳跃情况前的不会失效（此处就只有 name 成功）
同时，这个顺序并不是由我们 where 中的排列顺序决定，比如：
where name='小米科技' and status='1' and address='北京市'
where status='1' and name='小米科技' and address='北京市'
这两个尽管 where 中字段的顺序不一样，第二个看起来越级了，但实际上效果是一样的
其实是因为我们 MySQL 有一个 Optimizer（查询优化器），查询优化器会将 SQL 进行优化，选择最优的查询计划来执行。
2. 范围查询之后
范围查询之后的索引字段，会失效！！！但本身用来范围查询的那个索引字段依然有效，如图中的 status。
而图中 address 失效了，对比一下长度便可看出来。
3. 索引字段做运算
对索引字段做运算，使用函数等都会导致索引失效。
4. 字符串不加' '
索引字段为字符串类型，由于在查询时，没有对字符串加单引号，MySQL 的查询优化器，会自动的进行类型转换，造成索引失效。
5. 避免 select *
危害
消耗更多的 CPU 和 IO 以网络带宽资源
可减少表结构变更带来的影响
无法使用覆盖索引
🎈覆盖索引
尽量使用覆盖索引（索引列完全包含查询列），减少 select *
当查询列中包含了非索引项，虽然我们还是能够利用到索引，但是为了获取非索引项字段，我们需要回表去查询数据，效率会比较低。
6. or 分割开的条件
用 or 分割开的条件， 如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name 字段是索引列 ， 而 createtime 不是索引列，中间是 or 进行连接是不走索引的 ：
因为有一个不走索引，又是 or 条件，两个都要判断一下，相当于不管如何，都还是得去走全表查询，没有利用到索引。
7. 以 %开头的 Like 模糊查询
可以联系字典树 Trie 的匹配吧。
比如要找‘abc’，如果是 %bc，一开始的根都找不到了，自然没办法利用到索引树
而如果是 ab%，还能利用到前两个。
%开头的失效，%结尾的还能利用索引（实际上这里就相当于字符串的最左前缀原则，可以这么理解）
解决方法：使用覆盖索引
当真的需要两边都使用 %来模糊查询时，只有当 作为模糊查询的条件字段（例子中的 name）以及 想要查询出来的数据字段（例子中的 name & status & address）都在索引列上时，才能真正使用索引。
8. MySQL 认为全表更快
此处是由于数据的特殊性，‘北京市’所占的比例很高，还不如全表扫描
8.1 is null 和 is not null
本质上跟上边是一样的
MySQL 底层会自动判断，如果全表扫描快，则直接使用全表扫描，不走索引。如果表中该索引列数据绝大多数是非空值，则使用 is not null 的时候走索引，使用 is null 的时候不走索引（还不如全表扫描快），全表扫描；反之亦然。
如果表中 is null 的比较多，那自然就直接全表扫描，如果 is null 的很少，会走索引。
8.2 in 和 not in
为了方便测试，我们单独建了一个 status 索引，观察该表数据，status 中 2 很少，而 1 很多。
所以 in('1')的话，不如走全表，没有用到索引 in('2')就会走索引
总结
我们建立索引的时候，对于数据分布均匀且重复的字段，我们一般不考虑对其添加索引，因为此时 MySQL 会认为全表更快，会走全表扫描而非索引，导致我们的索引失效。
9. !=或者<>
使用不等式也会导致索引失效
相关习题
说完几个索引失效的场景，下边呢，是我们具体的应用场景，在如下几种特定情况下，我们需要采取不同的 SQL 优化方式，或采用索引，或利用外部条件
相关视频解析：
🎎【三、优化场景】1. 大批量插入数据
环境准备
load 命令
适当的设置可以提高导入的效率。
对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：
1） 主键顺序插入
因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果 InnoDB 表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
脚本文件介绍 :
sql1.log ----> 主键有序 sql2.log ----> 主键无序
插入主键顺序排列数据：
主键无序：
出现了权限问题
执行：set global local_infile=on;
但又出现了另一个问题：
其实我们开启之后， 需要退出重新连接，再次连接时便可以正常操作了
如果还是不行的话，连接的时候可以这样连接：
2）关闭唯一性校验
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行 SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
🎪2. order by 排序
环境准备
两种排序方式
using index
直接能在索引列完成查询，无需回表，此时需要保证所查询的字段都是索引字段，才会是 using index
但这个不太现实，不可能说我们要查的，都是索引的字段，所以很多情况下，我们并没有办法把 using filesort 优化为 using index，只能退而求其次，尽量从 filesort 的角度去优化，通过外部条件。
🎑using filesort
何时会出现：
order by 的字段不是索引
order by 字段是索引字段，但是 select 中没有使用覆盖索引
order by 中同时存在 ASC 升序排序和 DESC 降序排序
order by 中用到的是复合索引，但没有保持复合索引中字段的先后顺序（即违背了最左前缀原则）
比如图中的 select id,age,salary from emp order by salary,age；
为什么呢？这里我们得回顾一下复合索引是如何存储的，比如：我们建立一个复合索引(name，status，address)，索引中也是按这个字段来存储的，类似图中表格这样：
复合索引树(只存储索引列和主键用于回表)，而且是先按 name 排序，name 相同了再按 status 排序，以此类推
所以如果我们不按照索引的先后顺序来 order by 的话，就跟索引树中的排序规则不一样了，索引此时排好的序，我们都没办法合理利用到，自然 MySQL 不会去走索引了。
🎏Filesort 的优化
两种扫描算法
对于 Filesort ， MySQL 有两种排序算法：以这条 SQL 语句为例，我们来看看他是怎么执行的：
1） 两次扫描算法 ：MySQL4.1 之前，使用该方式排序。
①首先根据 where 条件，过滤得到相应的满足 age=1 的 salary，取出排序字段 salary 和对应的行指针信息(用于回表)，然后在排序区 sort buffer 中排序，如果 sort buffer 不够，则在临时表 temporary table 中存储排序结果。
②完成排序之后，再根据行指针回表读取所有字段，而次该操作可能会导致大量随机 I/O 操作，是我们需要改进的地方。
这就是所谓的两次扫描，第一次扫描，我们拿到的只是排序字段，然后在 sort buffer 排好序；第二次扫描，才去回表读取所有字段，最终返回。
该如何优化呢？为什么要分成两次，有没有一种可能是空间不够呢？那我们如果有足够的空间，以空间换时间，是不是就可以开辟出一种新的方法，只需要一次扫描即可
2）一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高，典型的以空间换时间的思想。
具体使用哪种算法呢？MySQL 通过比较系统变量 max_length_for_sort_data 的大小和 Query 语句取出的字段总大小， 来判定使用那种排序算法，如果 max_length_for_sort_data 更大，那么使用一次扫描算法；否则使用两次扫描算法。
优化方案
① 增大前者 max_length_for_sort_data 可以适当 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率，这是典型的空间换时间的思想。
编辑切换为居中
添加图片注释，不超过 140 字（可选）
② 减小后者 Query 语句取出的字段总大小如果内存实在不够富裕的话，我们可以减少查询的字段，避免 select *
③ 提高 sort_buffer_size ：由上文可知，通过增大该参数，可以让 MySQL 尽量减少在排序过程中对须要排序的数据进行分段，避免需要使用到临时表 temporary table 来存储排序结果，再把多次的排序结果串联起来。
可惜，MySQL 无法查看它用了哪个算法。如果增加了 max_Length_for_sort_data 变量的值，磁盘使用率上升了，CPU 使用率下降了，并且 Sort_merge_passes 状态变量相对于修改之前开始很快地上升，也许是 MySQL 强制让很多的排序使用了一次扫描算法。
具体的实战修改过程，需要结合 MySQL 中另一个工具--trace 分析优化器，来分析执行计划，后续有机会，我们再来详细聊一聊！
3. group by 分组
由于 GROUP BY 实际上也同样会进行排序操作，而且与 ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
1. 使用索引
先来看看无索引的情况：using temporary；using filesort
创建索引
2. 加上 order by null 禁止排序
如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行 order by null 禁止排序。如下 ：
3. 需要排序 (则跟 order by 的优化大体相同)
4. 优化子查询
Mysql4.1 版本之后，开始支持 SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。
但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代的！！
示例 ，查找有角色的所有的用户信息 :
执行计划为 :
优化后 :
连接(Join)查询之所以更有效率一些 ，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。
5. 优化 OR 条件
对于包含 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。
我们此处有一个 id 主键索引，和一个 age，salary 复合索引：
单列+复合中的某一个
单列+单列（两个一样）
实际上等效于 range，此处只是提供一个示例
解决：使用 union 优化！！！
优化前
type：index_merge
优化后
type：一个是 const，一个是 ref，都比 index 快
6. 使用 SQL 提示
SQL 提示，是优化数据库的一个重要手段，简单 来说，就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
use index
在查询语句中表名的后面，添加 use index 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。
ingore index
如果用户只是单纯的想让 MySQL 忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。
force index
强制走索引，即使 MySQL 认为全表更快，我们用 force 也可以强制走索引。
跟 use 的区别
use 只是提供一个参考，具体用不用还得看 MySQL 的优化器怎么想的
✨7. 优化 limit 分页
一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要 MySQL 排序 前 2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。
比如我们有这样一条语句，select * from tb_item limit 2000000,10 ;
此时默认是根据 id 排序的。
优化思路一
在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。
优化思路二
该方案适用于主键自增的表，可以把 Limit 查询转换成某个位置的查询 。(局限性：主键不能断层)
如果要根据其他字段来排序的话，此方法就无法做到了。
8. 优化 insert 操作
一次连接，多次插入
比如我们需要插入三条数据：
此时需要建立三次连接，每次连接都要消耗资源，为了提高单次连接的执行效率，我们会采取：
同时，insert 的时候最好是保持数据的有序插入
🎊总结
explain 分析 SQL 中，其中比较重要的主要是 type，key，ref 以及 extra，我们不需要死记硬背，多拿几条语句去 explain 比对比对，更有利于我们辅助记忆。
索引失效的几个场景，借用 b 站热评：
全值匹配我最爱，最左前缀要遵守；带头大哥不能死，中间兄弟不能断；索引列上少计算，范围之后全失效；Like 百分写最右，覆盖索引不写星；不等空值还有 or，索引失效要少用；VAR 引号不可丢，SQL 高级也不难！
优化基本原则：巧用索引，减少连接次数。
本文的 order by，还没有过多深入其底层原理，只是大概提及到了有 sort_buffer 这么个东西，以及对应的两种扫描算法，order by 底层到底是如何实现的，怎么通过本文的方法，去优化诸如以下这种语句：
这个 xxx，不管是不是索引，按这样去查询的话，大概率都是 using filesort 且不会用到索引的，除非说我们去 limit xx，这个 xx 还要很小，才会使用到索引。
这些，才是我们更进一步的底梁柱，笔记大家都有，只是谁整理得好看一点，多了一些自己的思考罢了。
如果感觉本文对你有帮助，点赞关注支持一下，想要了解更多 Java 后端，大数据，算法领域最新资讯可以关注我公众号【架构师老毕】私信 666 还可获取更多 Java 后端，大数据，算法 PDF+大厂最新面试题整理+视频精讲
领取资料添加小助理vx：bjmsb2020 2020-12-19 加入
Java领域;架构知识;面试心得;互联网行业最新资讯
评论