详解MySQL索引失效、实际使用场景，慢查询优化实战
索引失效情景
平时我们在使用索引时，有时会以为自己的sql语句能命中索引，但索引实际上是失效的， 我们可以用explain关键字来判断，以下总结了几种索引失效情景~
1. 对索引使用左模糊匹配 or 左右模糊匹配
有一张表user, 存在联合索引name-age-male, name age male分别为表的字段, 执行如下sql
select * from user where name like "%test";
通过explain字段分析可得到：
key为NULL，未命中索引！ 在执行该条sql时，筛选条件中的name会匹配到联合索引中的最左项name, 但是我们查询条件中的name的左边是模糊的，只限制了一定要以test结尾，开头可匹配上任意值。因此，索引树拿到该查询条件，不知道该往“哪边走”。索引的有序性被破坏。索引失效！
将模糊匹配换个边，执行如下sql
select * from user where name like "test%";
通过explain字段分析可得到：
key不为NULL，命中索引！ 在执行该条sql时，筛选条件中的name会匹配到联合索引中的最左项name, 与前者不同，name的右边是模糊的，只限制了要以test开头，结尾可匹配上任意值。因此，索引树拿到该查询条件，知道该往“testXXXX的方向走”。没有破坏索引的有序性。索引仍旧生效！
2. 对索引使用函数计算
继续在相同的表user, 相同的联合索引下操作, 执行如下sql
select * from user where lengh(name) = 6;
通过explain字段分析可得到：
key为NULL, 索引失效！ 索引的字段经过计算后，索引的有序性无法得到保证；假设存在一函数，y = f(x), 原先的x可能是有序的，但经过f(x)计算后，结果y的数值顺序可能被完全打乱，有序性被破坏。
PS: MySQL在这里偷了个懒, 即使筛选条件是 x+1=2; x+"123"="test123"; 这种条件，能保证计算结果y依旧是有序的函数，也会直接令索引失效
3. 对索引隐式类型进行转换
有一张表user, 存在索引name和age, 分别执行如下sql
select * from user where name = 12345;
select * from user where name = "12345";
通过explain分别分析两条sql结果：
对比可知，sql(1)的索引失效了，而sql(2)的索引成立！，表中的name字段的类型是varchar，当索引字段为字符串时，输入的参数是整型，会导致索引失效。反之则不会。MySQL在计算的时候，会自动将字符串转换为数字
我们做个证明，通过如下sql
select "10" > 9;
如果是数字转字符串，"10" > 9输出的结果为0；因为 字符串"1"大于字符串"0"
如果是字符串转数字，"10" > 9输出的结果是1；因为 10>9是必然现象
结果如下：
因此，原来的sql(2)等价于
select * from user where name = "12345";
|
|
|/
select * from user where cast(name as signed int) = "12345";
本质上还是对索引列做了函数计算, 从而导致破坏了索引的有序性
4. 多表联查引起的隐式类型转换
存在一张表user, 该表的字符编码为uf8mb4, id为主键, name为索引列字段; 同时还存在另外一个表user_test, 该表的字符编码为uf8, id为主键, name为索引列字段
执行如下sql:
select * from user_test where user_test.name = user.name and user.id = 1;
select * from user_test where user.name = user_test.name and user_test.id = 1;
通过explain分析两句sql 如下：
explain中，有多条数据时，当id一样时，语句从上到下执行，因此，我们可分析出该条sql的执行顺序。
-
先是执行了user.id = 1的查询条件，走了user表的主键索引树，找到所有符合user.id = 1的数据
-
在找到的user表的数据中，取出name字段，准备和user_test表的name字段比较，但这里看，原来user_test表的name字段是有索引的，但索引失效了
-
表user中的字符编码为utf8mb4, 而user_test表的字符编码为utf8, utf8mb4为utf8的超集，因此，对于user_test的name字段来说，需要隐式的执行convert(name using utf8bm4)，在索引列上计算，会导致索引失效
Tips:（S1和S2是两个集合，S1包含了S2的所有元素，且存在S2没有的元素，则称S1为S2的超集）
对于sql(2)来说，我们也可分析出该条sql的执行顺序
- 先是执行了user_test.id = 1的查询条件，走了user_test表的主键索引树，找到所有符合user_test.id = 1的数据
- 在找到的user_test表的数据中，取出name字段，准备和user表的name字段比较。此刻，由于上述所说的编码问题，需要对user_test的name字段做隐式类型转换，但函数是作用在具体数据上，非索引列！ 因此索引未失效。
5. 联合索引不满足最左匹配原则
创建了一个(a, b, c)联合索引，根据最左匹配原则，可以看成建立了a, (a,b), (a,b,c)三个索引，当执行的sql带有以下的筛选条件，均会走索引
Where a=N;
Where a=N and b=N;
Where a=N and b=N and c=N;
如果是查询条件中缺少了a， 则不满足最左匹配原则（联合索引的最左项未被匹配到，后续索引均失效），例如：
Where b=N;
Where c=N;
Where b=N and c=N;
当情况是
Where a=N and c=N;
只有a和c，跳过了b，由于最左匹配原则，a能被匹配到，而b未出现，导致后续的索引不会继续匹配；总体来说索引仍旧生效
6. 联合索引中出现计算符号
创建了一个联合索引（a, b, c），可以看成建立了a, (a,b), (a,b,c)三个索引，当执行的sql，如果有一索引列出现了 > < != 等计算符号，会直接导致后续列的索引直接失效
表user, 存在联合索引name-age-male, name(varchar(32)), age(int(11)), male(tinyint(1))分别为表的字段，执行如下sql:
select * from user where name like "张%" and age > 10 and male = 1;
由于age中出现了 > 计算符号，会导致后面的索引（male）失效，通过explain分析
通过explain的分析结果可知，仅走了name-age索引，male索引失效，通过key_len（实际走的索引列的字节长度）可知：(4×32+3) + (4+1) = 136
Tips: utf8mb4编码-> 1个字符占4个字节, varchar(32)占32个字符, 另外需要1个字节保存是否为NULL, 2个字节记录真实长度; 因此name实际会走 4×32+3个字节
int占4个字节，另外需要1个字节保存是否为NULL，因此age占5个字节
7. where语句中出现or
存在一张表user，有字段name和age, name和age都是普通索引列，执行如下sql
select * from user where name = "test" or age = 123;
通过explain字段分析可知
即使name和age都是普通索引列， 索引也会失效，走全表扫描... （感觉这里更像是Mysql偷了个懒, 可以直接找两颗索引树，取并集）
但是如果一定要走索引，强制走索引，可以将sql修改成如下:
select * from user where name = "test"
union
select * from user where age = 123;
通过explain字段分析可知
通过explain分析我们可知，该条sql的实际操作为分头执行两条索引sql，再对结果取并集
总结上文我们可知，即使是查询条件的列都有索引，若是Mysql强制走索引，后续还需要合并多个结果集，在数据量大的情况可能复杂度暴涨，操作效率也是极低，因此MySQL会直接放弃索引，全表扫描
8. 特殊案例1——or存在走索引可能性
在某一特别条件下，or条件还是会走索引的，看如下sql对比, id是表的主键索引，type是表的普通索引
explain select * from inspection_group_plan where id = xxx or type = "AT_ONCE";
explain select id, type from inspection_group_plan where id = xxx or type = "AT_ONCE";
对比可知，仅在主键索引+二级索引的组合下，此时MySQL只要遍历二级索引树，就可以直接得到筛选条件中的id和type值（二级索引树的叶子节点存储的是主键），并且不能存在回表，（不能为select *），避免为了补齐*中的数据，还需要去主键索引树查询。
9. 特殊案例2——左（右）模糊匹配不一定会导致索引失效
存在一张表user，表中存在id, name, age, male字段，建立（name,age,male）联合索引，当执行以下sql时：
select * from user where name like "%test%";
结果令人意外，索引居然没有失效，原因是：表user中不存在非索引字段，id为主键索引，另外有（name,age,male）联合索引，MySQL会觉得：我直接去遍历二级索引树（记录的东西更少），并且也可以直接获取我们所需要的数据（*表示了id, name, age, male；刚好联合索引树中都有），不用回表，因此MySQL会直接遍历二级索引树！
Tips: explain中的type为index，代表该sql的执行方式为遍历二级索引树；key_len为138，通过上文的计算我们也可得知走的是name_age_male联合索引；Extra中存在Using index, 表明该sql语句用到了覆盖索引，避免回表
如果修改一下联合索引，改为name-age索引，male为非索引字段，执行同样的sql，结果为：
索引直接失效！
小小总结下：
-
如果使用联合索引，一定要遵循最左匹配原则（查询从索引的最左列开始写，不要跳过索引列）
-
联合索引出现范围查询，会导致右侧的索引直接失效，即使有匹配上
-
尽量不要使用左模糊查询，即使有特例，也容易导致索引失效
-
从索引的有序性出发，不要做任何会破坏索引有序性的操作。例如：对索引做函数计算，有可能产生的隐式转换（字符串转数字，表的字符集转换）
-
数据量大时，查询尽可能避免使用到or
索引的实际使用场景
具体会展开说说索引在join，排序，计数发挥的作用
1. 排序
存在一张表datasource, 有一个索引字段device_measurement_name和非索引字段tsdata_id, 执行如下sql
select * from datasource where device_measurement_name like "33%" order by tsdata_id;
分析结果如下：
通过上述结果，我们可以看出该sql大致的执行顺序：
- 先命中索引（key不为NULL），将符合筛选条件的主键都取出来
- 通过回表获取完整的行数据（Extra中不存在using index）
- 分配到一块内存，将获取到的行数据放在里面通过tsdata_id排序（Extra存在Using filesort）字段
如何进一步分析Using filesort？ 三步曲如下：
/*打开optimizer_trace*/
set optimizer_trace = 'enabled=on';
/*执行具体sql*/
select * from datasource where device_measurement_name like "33%" order by tsdata_id;
/*输出optimizer_trace*/
select * from `information_schema`.`optimizer_trace`;
输出的结果如下：
- rows表明符合条件的行数据的数量
- number_of_tmp_files表明用来排序的磁盘文件的数量，MySQL先用自己的内存池排序，如果空间不够，才会向磁盘申请空间。 number_of_tmp_files=15 表明使用了15个磁盘文件来排序。
- sort_buffer_size为排序缓存池的实际空间
- sort_mode为实际放入缓存池排序的数据，本来应是要把整行数据放入内存池排序，但由于数据庞大，MySQL为了节约空间，用行数据的隐藏列rowId替换完整数据，放入内存池排序。算是一个优化。
如果数据天然有序，就完全用不到排序内存池，极大节省了空间和时间。
索引天生就具备有序性， 因此我们给tsdata_id加上索引，再执行相同的sql, 分析结果如下：
PS：Extra中已经没有Using filesort字段，表明该sql已经不再需要额外内存来排序
当然还可以近一步优化，如上图，把select *替换成索引字段，使用覆盖索引避免回表（Extra中出现Using index）
2. Count
count的含义是：统计符合查询条件的记录中，Count中指定的参数不为NULL的个数, 下面分别对比 count(主键)，count(1)，count(普通列)的区别
分以下两种情况，走的索引树不同
假设一张表中除了主键，没有其他索引，执行的sql如下：
select count(*) from audit_log;
分析结果如下：
可以看出，该sql走的是主键索引树，如果再将表中的user_id设置为索引列，再执行同样的sql，分析结果如下：
走的是二级索引树，原因是：二级索引树的叶子节点存储的是对应的主键值，比起主键索引树，占用更小的空间，IO成本更小。遍历二级索引树就能达到count的目的。
和count(主键)一样，走成本最小的二级索引树实现统计效果。
执行sql，通过show waring可看出，MySQL对其做的优化
- 如果该列存在索引，则会遍历对应的二级索引树拿到符合条件的数据，再返回给Server层，由Server层做判NULL操作，再计数+1，分析结果如下：
- 如果该列不存在索引，则会直接全表扫描，再将符合的结果返回处理。分析结果如下：
总结下：从效率的角度比较，count(*) = count(1) > count(主键) > count(普通索引列) > count(无索引列)
3. Join
我们写sql的时候，往往会涉及到多表联查，这时候就会用到join，而join的使用更要小心再小心，一不小心就会导致灾难级别的效率问题
假设有一张表t1, 存在索引字段a,非索引字段b, 有100条数据；还有一张表t2, 也存在索引字段a,非索引字段b, 有1000条数据，执行如下的sql：
select * from t1 straight_join t2 on t1.a = t2.a;
通过explain我们可以得到该sql的执行效率：
对于id相同的sql，执行顺序为从上到下，可看出，t1作为驱动表，t2作为被驱动表。
执行顺序如下：
- 取表t1中的一行数据
- 将取的行数据中的a字段拿出来，去表t2中查询
- 将符合筛选条件的数据拼接在一起
- 重复步骤1-3，直到表t1的数据被取完
从分析结果可看出，从表t1中把a字段取出来后，在表t2里找数据时会命中索引。因此，对表t1是全表扫描，表t2是命中索引树，总扫描行数计算为：
100(t1全表扫描行数) + 100(t2索引精准获取) = 200
可以用上被驱动表t2的索引，又称之为Index Nested-Loop Join (NLJ)
如果join筛选的条件是在非索引字段b的话，则执行sql：
会发现被驱动表t2的Extra，出现了Using Join buffer(Block Nested-Loop (BNL))的字段。
BNL的执行顺序如下：
- 对驱动表t1做全表扫描，将扫描结果放在join buffer内
- 扫描表t2，把t2的每一行都取出来，跟join buffer里的结果做对比
- 满足条件的数据，会放入结果集等待。
如果join buffer的内存无法一次性放下驱动表的数据怎么办？
join buffer的内存大小通过join_buffer_size参数控制，默认为256K。如果无法一次性放下的话，需要分段放，原来的步骤就变成：
- 扫描t1, 直到join buffer内存满
- 扫描t2，和join buffer内的数据做比较
- 将符合条件的结果放入数据集
- 清空join buffer，重复步骤1-3，直到t1被全部扫描完。
于是，扫描行数就变为：
100(t1全表扫描) * 1000(t2全表扫描) = 100000
每在表t1取一行数据，都得对表t2做一次全表扫描，因此如果join出现了BNL，是要尽量去避免的。
MySQL自身对join有做什么优化吗？ 引入batched key access (BKA)和MRR（顺序读盘）
我们执行一条带索引的范围查询语句： select * from t1 where a > 50 and a < 100;
这类型的sql我们都很熟了，会先走普通索引树，将符合条件的主键id都找出来，然后一个个的进行回表查到完整行数据。虽然a的值是按增顺序递增的，从50-100。但id的值很有可能是随机的，就会出现随机访问，性能相对较差
我们表的主键在大多数情况都是按照递增顺序插入的，因此我们可认为，如果按照主键的顺序递增顺序查询的话，对磁盘的读写近似于顺序读，效率远优于随机读
因此，便有了MRR，原来的sql语句的执行顺序变成了：
- 通过普通索引树找到符合条件的主键id
- 将id做递增排序
- 将排序后的id依次去主键索引树查找数据
MRR可通过 set optimizer_switch = 'mrr_cost_based = off' 开启
而BKA策略，就是对NLJ的优化
在被驱动表的索引列和驱动表的数据做查询后，会对获取的主键id做递增排序，再去进行回表补充完整数据。
BKA通过 set optimizer_switch = 'mrr = on,mrr_cost_based = off, batched_key_access = on' 开启
如何避免Join中的BLJ？
通过合理加索引的方式，将BLJ升级成NLJ，例如如下sql:
select * from t1 join t2 on t1.b = t2.b where t2.b < 1000;
可以在t2表上增设b索引，这样就可以解决问题。但是，如果这是一条很低频的sql，还要为了它，在表t2上新设一个索引，这就很浪费了
不创建索引会面临全表扫描的危险（BLJ），而创建了索引会浪费资源，使用临时表解决
- 将表t2中满足条件的数据放在临时表内
- 往临时表的b字段添加索引
- 让表t1和临时表做join
这样操作，sql就变成：
create temporary table temp(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp select * from t2 where b<1000;
select * from t1 join temp on t1.b = temp.b;
总体来说，无论是加索引，或者是临时表，都是要让其在join的过程中，能够触发被驱动表的索引，避免各种全表扫描的性能消耗问题
下面我们推导一个join搜索行数的公式
驱动表的行数为M，无论有索引，在驱动表上都要做全表扫描
被驱动表的筛选条件列有索引，且表的行数为N
查找树的时间复杂度为O(logN), 因此在有索引的被驱动表上查找一次数据，时间为2logN (考虑了 回表)
对于驱动表来说，每获取一行驱动表的数据，都要去被驱动表匹配一次，因此扫描的复杂度为：
M + M × 2 × logN = M(1 + 2logN)
相较于M，logM的涨幅更小（高中的导数知识），M对扫描行数影响更大。因此，我们应当让行数较少的表来做驱动表。（驱动表也必然会全表扫描）
在sql里，驱动表和被驱动表是如何选定的？ A join B, A一定是驱动表吗？B一定是被驱动表吗？选定规则如下：
调换顺序，通过explain的结果可知，首先执行的都是t1，首先执行的表即驱动表。
调换顺序，通过explain的结果可知，首先执行的都是左边的表
调换顺序，通过explain的结果可知，首先执行的都是右边的表
-
如果where筛选的列有索引，无论是join, left join, right join，都会选择where筛选后数据量较小的表做驱动表
执行如下sql，分析结果如下，原本left join会指定t1做为驱动表，但是经过where条件筛选后的a字段，命中索引，且筛选后的数据量小于t1（50小于100），因此选择t2作为驱动表将where条件稍微修改，执行如下sql，分析结果如下，由于筛选后的数据量为200，大于t1（500小于100），因此依旧是选择t1作为驱动表
-
如果where筛选的列没有索引，则原有的join, left join, right join选择驱动表的规则不会发生变化
执行如下sql，b为非索引字段，分析结果如下，原本left join会指定t1做为驱动表，但是经过where条件筛选后的b字段，没有命中索引，即使是筛选后的数据总数小于t1（50<100），依旧选择t2作为驱动表
这里提到了where和join，补充一下select语句关键字的执行顺序，官方提供的标准执行顺序，如下图：
- 先看from字段，需要查询哪张表
- 有join会先执行join相关内容
- 有group by会再执行分组内容
- 分析select后面需要什么数据，做补充
- 最后有order by和limit的分组相关内容，放最后执行
慢查询的分析及优化
下面会举一些我在公司生产环境下碰到的慢查询例子，一些敏感字段已经被我替换掉了~
1. 多张大表的join
一开始多张表的join，在数据量少的时候还感知不到；数据量随着使用时间的增长而不断膨胀，数据量突然暴涨，导致慢sql，最终接口超时。排查后思路如下：
假设存在三张表，分别是emp, emp_log, emp_cert；各自有100W, 200W, 100W数据。三张表仅存在主键索引（id）。
执行如下sql:
执行时间为53秒，除了主键，表的其他字段是没有索引的，没有索引导致的慢查询，我们第一步肯定是要将索引补上，从而避免由于join的数据量呈现指数型暴涨。因此，我们先给emp_log的create_time字段和emp_cert的emp_id加上索引，再执行同样的sql。结果如下：
时间为3.34s，效率已经比没有索引时得到质的提升。通过explain字段进行分析，结果如下：
当id相同时，由上到下依次执行。因此我们可分析出该sql具体的执行顺序为：
- 先执行emp_log这张表的筛选条件：create_time，由于该列存在索引，会走相应的二级索引树，此时遍历的表行数可以从rows中看出大概有92114行。
- 存在一张内连接join生成的临时表cert, 其中emp作为驱动表，而emp_cert作为被驱动表。根据上文的分析，驱动表必然会全表扫描，但emp表存在主键索引树，并且连接筛选条件也只取主键，因此MySQL会遍历代价更小的主键索引树，获取emp的id值。
- 拿着取到的id值，在emp_cert表上做emp_id的等值查询，会走emp_id索引树。
- 最后，将建立的临时表，和筛选后的emp_log再做join连接。并且筛选条件是or，不会走索引的。此时的数据量，是临时表的行数 * 过滤后的92114条emp_log记录。
3.34s还是太久了，根据我们上面分析的最后一步，or会导致索引失效；并且临时表和emp_log的没有索引，join出来的数据量也是惊人的；我们可根据这两点去进行优化。有两种思路可走：
因此，可将sql改造如下：
二度优化后执行的时间仅0.14s，通过explain字段进行分析，结果如下：
id越大的数字越先执行，id相同的数字从上到下依次执行。 我们可看出，id为1和2的基本为镜像执行，顺序如下：
- 无论是上面和下面的表，都是先执行emp_log这张表的筛选条件：create_time，这个和优化前是一样的，肯定逃不掉。
- 随后两个表的两次join，都是直接命中了被驱动表的索引，避免了全表扫描导致的数据量暴涨问题，效率自然就上去了。
- 最后，将两个不同筛选条件出来的表数据取并集，得到结果。
2. limit查询优化
在实际项目中，往往会碰到分页查询，该查询则是涉及到limit的使用。
常见的limit的sql语句为：
select * from user order by id limit offset size;
其中offset是查找到的符合条件数据的偏移量，size是该sql语句要取多少条数据。 limit的语句的基本执行顺序如下：
- 先是计算 offset * size的值，表明这次需要从哪里开始取数据
- 不断遍历全表or索引树，innodb引擎层获得 offset * size + size条数据，返回给Server层
- Server层将前 offset * size条数据丢弃，返回符合条件的数据
可以看出，当offset不为0时，Server层会拿到很多没有用的数据，执行的sql时间也会增加，例如如下sql:
select * from user order by id limit 900000, 10;
limit 900000,10表明需要取 900000+10条数据，并抛弃前900000条，耗时极大。如何优化？
因为前900000条数据是可以直接丢弃，完全用不到的。但是该sql的select后面跟的是*。因此，我们可以将前900000条特殊处理，只取id，可将sql优化成如下：
select * from user where id >= (select id from user order by id limit 900000, 1) limit 10;
先通过子查询，将前900000条数据的id单独取出来，丢弃并取最大一位，这样sql就会优化为如下：
select * from user where id >= 900001 limit 10;
这样只需要直接走主键的范围查询，并直接取前10条数据即可，实测，性能大概可提升一倍。
当排序的规则不是主键id，而是换成了普通索引列，这时候？
对于非主键索引来说，叶子节点存放的是主键id，因此二者的差距是多了个回表的消耗，如果将分页的sql换成：
select * from user order by age limit 900000, 10;
当offset如果很大的时候，多了次回表的操作，可能MySQL就不会选择走索引，会放弃遍历索引树，直接选择全表扫描（MySQL在索引的区分度不高时，或者需要做大量回表的情况，优化器可能会直接放弃走索引，认为代价太高）。因此可以将sql修改成：
select * from user join (select id, age from user order by age limit 900000, 10) user2 on user.id = user2.id;
通过子查询创建出一个临时表，只保存id和age（避免回表），并直接在这个子查询中做好分页。随后，在和原始表做join，这时候只要把原始表的id和临时表的id做对应就好。
好像只要涉及到分页，都逃不开需要查一堆数据直接丢弃的问题，当一张表的数据量特别大时，深度分页肯定是躲不开的。
目前为止看到的比较实用的方法是直接做一个预存，将所有的数据通过主键id排序，然后分批做处理（比如10条1页，20条1页这样），将当前批次的最大id作为下一次筛选的id查询
这样将原来的 order by id limit offset size 变为 id > pre_page_start_id，躲开了查一堆数据丢弃的问题，同时将前端的UI改为上下页的形式，如下:
这样就能保证每一批数据以上一页的最大id为起点，无论翻到多少页，查询速度始终稳定。（类似功能还有抖音和快手的短视频功能，只能上划和下划）
