【死记硬背】

MySQL索引失效的情况主要有以下几点：

1 有or必然全部有索引；

2 符合索引没有使用左列字段；

3 like以%开头不走索引；

4 需要类型转换不走索引；

5 where中索引列有运算不走索引；

6 where中索引列使用了函数不走索引；

7 使用<>、not in、not exist、!=条件不走索引；

8 索引列值为NULL的不走索引；

9 索引选择性不足的不走索引；

10 表数据量小或查询结果集占比大；

11 索引列上存在大量重复；

12 查询优化器误判的情况；

【答案解析】

索引失效是指在使用索引进行查询时，索引无法发挥作用，导致查询性能下降。常见的导致索引失效的情况有以下几种：

1 有or必然全部有索引

当where后有or选项时，如果有一种条件没有索引，其他条件都有索引，该SQL也不会走索引，这种情况索引会失效。

2 符合索引没有使用左列字段

对于联合索引，查询条件没有按照索引定义的列顺序指定，或者没有从索引的第一列开始，导致无法利用索引的最左前缀。

3 like以%开头不走索引

使用LIKE '%xxx%'形式的模糊查询时，由于前导通配符导致无法进行有效的索引查找。这种情况下，全文索引或InnoDB的倒排索引可能是更合适的选择。

4 需要类型转换不走索引

当查询条件的数据类型与索引列的数据类型不匹配时，索引可能无法被使用。尤其是在进行隐式数据类型转换、不同字符集的比较或编码问题时，需要特别留意。如下面的SQL：

# name 为索引，下面的使用了索引
explain select * from user where name='123'
#下面的需要类型转换不走索引
explain select * from user where name=123

5 where中索引列有运算不走索引

当查询条件中对索引列应用了数学运算操作时，索引可能无法被使用。因为索引的创建是基于原始列值的，无法直接使用数学运算后的结果进行索引匹配。

6 where中索引列使用了函数不走索引

当查询条件中对索引列应用了函数操作时，索引可能无法被使用。因为索引的创建是基于原始列值的，无法直接使用函数后的结果进行索引匹配。

7 使用<>、not in、not exist、!=条件不走索引

这些逻辑操作符在查询条件中的使用会导致索引失效，因为它们无法充分利用索引的特性。

8 索引列值为NULL的不走索引

如果查询条件包含对索引列的IS NULL或IS NOT NULL检查，某些情况下可能无法利用索引。特别是当索引列允许NULL值且表中有大量NULL值时，数据库可能选择全表扫描。

9 索引选择性不足的不走索引

如果索引列的基数非常低，如性别、状态等列，即使使用了索引，由于选择性差，数据库可能判断全表扫描更高效，从而放弃使用索引。

10 表数据量小或查询结果集占比大

当表数据量很小，或者查询预期返回的结果集占总数据量的比例很大时，数据库可能认为全表扫描比使用索引更快。

11 索引列上存在大量重复

如果索引列上存在大量重复值，即使查询使用了该索引，由于筛选能力有限，数据库可能选择全表扫描。

12 查询优化器误判的情况

在某些复杂查询中，查询优化器可能由于统计信息不准确、成本估算错误等原因，选择了一个非最优的执行计划，导致索引未被使用。

小结

我们要解决索引失效的问题，可以通过合理设计索引、优化查询语句以及避免索引失效的情况发生来提升查询性能。

【温馨提示】

点赞+收藏文章，关注我并私信回复【面试题解析】，即可100%免费领取楼主的所有面试题资料！