MySQL索引失效场景及原理

1.对索引使用左或者左右模糊匹配

select * from user where name = "张%"

首先进行索引扫描，

用"张"去和B+树根节点中存储的索引中的第一个字进行二分查找，因此不会失效。

而如果是

select * from user where name = "%三"

则因为无法与索引节点进行比较，进行二分查找，因此索引会失效。

2.对索引使用函数运算

select * from user where substring(name,2,1) = "三"
select * from user where len(name) = 2

因为索引保存的是索引字段的原始值，因此对索引进行函数运算之后，就无法与索引节点中的值进行比较，因此失效。
3.索引隐式转换

对于索引是字符类型的，如果使用时候没有加引号，如phone字段是字符类型，但是使用时候没有加引号

select * from user where phone = 13312341234

MySQL会将字符串转换为数字即

select * from user where CAST(phone AS signed int) = 13312341234

对phone字段进行了函数运算，因此索引失效

4.未遵循最左匹配原则

在联合索引中，数据是按照索引第一个字段排序，第一列数据相同时按照第二列字段排序，以此类推。

如果没有遵循这个规则，如查询条件中只有第二个字段，因此无法将该字段与索引中已经按照第一列字段排序的结果进行比较，因此失效。

5.条件or左右两端有一个条件未使用索引

select * from user where phone = 13312341234 or name = 张三

or前后连接了两个条件，意味着会查询满足任意条件的数据，如果其中一个没有索引，那么显然该字段无法使用索引。

6.数据分布的影响

如果MySQL评估使用索引比全表更慢，则不使用索引。比如user表中name字段创建了索引，同时表中name字段为空，

执行

select * from user where age is null;

那么不会使用索引。