索引失效的场景以及原因

背景

在日常sql优化的过程中，往往会发现一些索引失效的问题，例如预期中该sql应该使用索引，但使用Explain命令后发现并未使用，因此做下记录，记录下索引失效的场景以及原因

场景

数据量小的时候，索引可能会失效

在数据量小的时候，如果MySQL优化器觉得使用索引耗费的时间比全表扫描慢，就会不使用索引而走全表扫描，此时索引失效了。

在复合索引的情况下，不满足最左匹配原则，索引会失效

复合索引的情况下，非聚集索引的结构是以最左字段为基础构建的，因此在不满足最左匹配的原则下，mysql不会去使用该索引而会使用全表扫描的方式查找，此时索引失效了

在复合索引的情况下，满足最左匹配原则，但是使用了范围匹配，会导致索引部分失效

例如创建了index(a,b,c)复合索引，如果在满足最左匹配的情况下，使用该where子句 where a = 1 and b>3 and c = 2,该情况下，a,b字段都使用了索引，但是c并未使用索引，因为复合索引是基于a字段构建的(a,b,c)为每个节点记录的值，该值在a相等的情况下,b是有序的,因此在b使用范围查询时还是查询的索引，但是之后的C就是无序的了，因此索引会失效,这也是为什么在复合索引创建时，我们要把范围匹配的字段往后放的原因

对索引使用内置函数,索引会失效

在对索引使用内置函数后，会导致mysql不会使用索引，因为索引是基于函数处理前的值构建。

在使用like的时候，索引可能会失效

字符串类型的字段构建索引时也是类似的顺序，以第一个字符开始构建,类似复合索引的构建顺序。

在这种情况下，Like "str%" 是会使用索引的，因为按照前缀去匹配。

但是 like '%str'这种情况就会导致索引失效

在使用or时， 索引可能会失效

出于mysql优化器的优化策略。在使用or的情况下可能导致查询的成本高于一次全表扫描，就会导致索引失效。此时可以针对or使用的所有字段建立索引来优化