完蛋！? 我被MySQL索引失效包围了！

前言

一个熟悉的起床闹钟响了，蔬菜学生醒来发现周围是索引故障的原因：性感迷人的索引使用不当，可爱活泼的存储引擎不能识别索引列，任性的优化器不选择索引...

要知道为什么，让我们来看看为什么索引失败了~

在阅读文本之前，您需要了解聚集索引、二级索引、回表和其他知识。如果你不知道，你可以查看之前的文章~

索引失效是什么？

对于MySQL常用的索引，通常是集群索引和二次索引

索引失效是指MySQL在某些情况下使用聚簇索引(全表扫描)而不使用二次索引，导致二次索引失效 (索引失效中的索引是指二次索引)

索引使用不当往往是因为我们对索引了解不够

在聚簇索引中，记录按照主键值升序排序

在二次索引中，记录按照索引列和主键的顺序进行排序。当索引列相等时，主键是有序的

在（age，student_name）在联合索引中，当age相等时，student_name排序，当student_name相等时，主键id排序

当我们熟悉索引存储规则时，我们可以有效地避免索引使用不当

比如 select * from student where student_name like 'c%' 是用不上（age，student_name）联合索引的

当搜索列不有序时，可能会扫描整个二次索引，在这种情况下，可能需要返回表格。因此，MySQL会放弃使用二次索引，直接扫描集群索引，导致索引失效

当我们建立student_name索引时，上述SQL可以使用student_name二级索引

假如把SQL改成select * from student where student_name like '%c%' 也会导致索引无法使用索引

原因与上述相似。左模糊查询导致无法估计扫描范围，导致全表面扫描

还有其他类似的场景order by、group by当需要对场景进行排序时，使用的二次索引不有序，导致索引失效

一般情况下，当我们熟悉索引时，我们不会犯这样的小错误~

当执行器携带查询条件向存储引擎层要求数据时，如果存储引擎层无法识别数据，也会导致索引无法使用

例如，在查询条件中使用表达式 where age + 2 = 10

当存储引擎层的innodb无法识别表达式时，也会导致索引失效

当然，我们通常不会采用这种写法（key_len = 3说明只在联合索引中使用age)

当我们使用索引列函数时，存储引擎层无法识别

比如 explain select * from student where age = '8' limit 1000 函数将被隐藏使用'8'将字符串转换为整形手术8

相当于SQL SELECT * FROM student WHERE age = CAST('8' AS UNSIGNED) LIMIT 1000 在这种情况下，可以使用索引

当对索引列age使用函数时，如：SELECT * FROM student WHERE CAST(age AS CHAR) = '8' LIMIT 1000

无法识别存储引擎层CAST(age AS CHAR)导致无法使用与age相关的索引

隐式使用函数转换类型也是一种容易导致索引失效的场景

即使字段类型相同，也可能发生隐式类型转换，如 utf8(mb3) 向 utf8mb4 进行转换

在联表查询中，索引加速查询通常是为驱动表的相关条件建立的

 select a2,b1 from  a  left join b on a.a2 = b.b2

例如，B在这个SQL中是一个被驱动表，并为相关条件所需的B2建立索引可以加速查询

索引(上图)在正常情况下使用。

但是你知道同样的SQL在什么情况下会变成下图吗？

虽然使用了索引，但没有完全使用，但仍然使用了join buffer，从前后key_len也可以知道它没有完全使用

原因是a2字段的字符集是uft8mb4、当b2为utf8时，从驱动表a获取记录并从驱动表b获取。b2字段的隐藏使用函数转换为utf8mb4，导致存储引擎无法识别

由于这种情况，蔬菜在当地没有问题。因此，不同的字符集导致索引失效

另一个索引失效的场景发生在server层：当优化器认为索引成本过高时，它倾向于使用全表扫描

那么，优化器使用二级索引的成本是多少呢？

使用二次索引时，往往需要回表，导致成本高

因为回表不仅需要查询更多的聚簇索引，因为二级索引的主键值可能会导致随机IO

回表成本高的场景一般发生在查询数据量大的情况下，因为回表数据增加，成本增加

MySQL认为使用二级索引的成本太高，导致索引失效

比如or、is null、is not null查询条件不一定会导致索引失效。当MySQL估计其数据量过大，回表费用过高时，将放弃使用二级索引

或者深分页问题 limit 10000000,10，因为MySQL需要在server层进行limit，会导致前1000万条数据的检查，而且使用检查的数据量太大，如果需要返回，成本会很高，导致深分页问题的索引失效

当MySQL估计成本时，也可能导致索引失效

当需要扫描的记录数量超过一定限制时（show variables like 'eq_range_index_pe_limit'）当使用统计方法预测成本时，很容易出现误差（在业余时间使用analyze） table 重新统计cardinality)

cardinality用于判断重复值。重复值越小，重复值越多。如果重复值太大(cardinality太小)，MySQL就不会偏向于使用索引

索引失效大致分为三种场景：索引使用不当、存储引擎层导致索引失效、索引失效

不熟悉索引存储规则，使用时容易导致索引使用不当，如：左模糊匹配、联合索引最左匹配原则、order by、group by排序等

当存储引擎层无法识别查询条件中的索引列时，索引就会失效，如索引列使用表达式、显示/隐藏函数等

当Server层优化器认为使用二次索引的成本过高时，索引就会失效。成本的主要来源是返回表。如果返回表数据量过大，则成本较高，不偏向于使用索引，如深分页问题（过多的重复值也会导致不偏向使用索引）

当需要扫描的记录数量超过一定限制时，使用统计估计成本会导致误差，误差过大也会导致索引失效

熟悉各种场景导致的索引失效后，小菜准备逐一策略周围的索引失效场景

熟悉的起床闹钟响了，小菜同学汗流浃背地爬了起来:原来只是一场梦。幸运的是，项目中没有那么多索引失效的场景。