索引失效场景
先看一下表结构和表数据截图,我们把number、age字段建立索引
1. or语句对索引影响
图一,查询年龄为10的数据,正常走了索引
图二,or语句包含非索引字段name,age没有走索引
图三,or语句的两个字段均有索引时,age和number正常走了索引
总结:当使用了or语句时,or作用的字段均建立了索引情况下,sql才会正常走索引
2.类型转换对索引的影响
图四,number字段类型定义是varchar,查询时去掉了引号转换成int类型,导致索引失效
图五,age字段类型定义是int,查询时加上了引号转换成varchar类型,但是正常走了索引
总结:当涉及到类型转换时,如果向上转换会正常走索引,向下转换会是索引失效
3.like语句中通配符%对索引的影响
图六,使用like语句时,查询条件的结尾加上通配符%时,索引正常
图七,使用like语句时,查询条件的开头和结尾都加上通配符%时,索引失效
图八,使用like语句时,查询条件的开头加上通配符%时,索引失效
总结:在like语句中,只有通配符%在查询条件的结尾时,才能正常走索引
4.索引列用到了运算或者函数
图九,当索引列做了运算或者函数,索引会失效
总结:索引列用到了运算或者函数会导致索引失效。所以,当不得不需要使用运算或者函数时,可以将运算作用在条件上,比如:age = 10+1 而不是 age-1 = 10(这里只是举个例子,当然能够算出来 age = 11更好哦)
5.当系统判定全表扫描比索引更优时
这里为了方便举例,我们将第一张图的数据中,age全部改为12
然后用 age = 12作为条件查询,
下图,我们发现,possible_keys 是 index_age,但是key是null。type是ALL,进行了全表扫描。
6.复合索引最左匹配原则
我们现在将索引换成复合索引,如下图
下面的一系列图,可以发现当number不在where语句中的时候,索引失效了
总结:当建立了 A,B,C复合索引时,where语句中出现A、A|B、A|B|C、A|C时(A必须出现)才会正常走索引,这就是复合索引最左匹配原则。