索引失效的11种常见场景及其详细原因

前言

索引的重要性我们都知道，但索引失效是十分常见的情况，也是面试的重点，这篇文章介绍了11种常见场景及其详细原因。

提示：11种肯定不是最全的，但是是比较常见且好记忆的。然后提醒大家，除这11种以外的场景，很可能是可能失效或者效率降低，而不是完全失效。就包括这篇的11种都存在可能或者部分失效的情况。

一、索引失效

1. 联合索引的使用不满足最左前缀原则

原因：索引基于B+树结构，数据按索引顺序排列。数据库必须先定位最左列的值，才能沿着树结构快速找到后续列的数据。若最左列未被使用，索引无法有效定位，导致失效。类似多级目录，必须先找到第一级目录才能查找下一级。

例如： 联合索引 (a, b, c)
select * from table where b = 10; – 不满足最左前缀，索引失效
select * from table where a = 10 and b = 20; – 满足最左前缀，索引生效。

2. sql语句中出现函数或者运算
3. 查询类型和字段类型不一致（出现转换函数）
提示：这两个都属于sql语句中出现函数或运算
原因：函数或运算会改变索引的原始值，使数据库无法利用索引结构的排序和定位特性，导致索引失效。3就本质上是出现函数，改变了原始值，通常是cast函数。

例如：
select * from table where date(create_time) = ‘2023-01-01’; – 函数，索引失效
select * from table where int_column = ‘123’; – 字符串转数字，隐式转换导致索引失效
select * from table where cast(varchar_column as int) = 123; – 显式转换，索引失效。

4. 范围查询字段后的索引全部失效

原因：假设复合索引是 (a, b, c)，它就像是三级目录：
先按 a 排序，同一个 a 值下，再按 b 排序，再细分按 c 排序，也就是说，必须找到唯一的指定上级目录，才能继续排序。

而范围查询回返回很多个上级目录，就会导致下面没有确定的上级目录，索引失效。

例如：where a > 10 and b = 5 and c = 3，这里 a > 10 是范围查询，相当于告诉数据库：“我需要所有 a 大于 10 的目录”。因为第一级目录不唯一，数据库无法再用索引去快速定位第2级目录 b 的具体位置。

5. or两侧字段存在任一字段无索引，全部失效

原因：MySQL 优化器为了保证查询效率和简化执行计划，通常不支持“部分走索引，部分全表扫描”的混合策略

例如：select * from table where col1 = 10 or col2 = 20; – col2无索引，索引失效。

6. 使用like左模糊查询

原因：B+树索引是按照字符串的前缀顺序排列的，当 % 在开头时，数据库无法根据固定的前缀快速定位数据，只能进行全表扫描。

例如：select * from table where name like ‘%abc’; – 左模糊，索引失效
select * from table where name like ‘abc%’; – 右模糊，索引生效

7. 当一个字段存在联合索引和单独索引，优先使用联合索引

原因：联合索引覆盖的列更多，能提供更精准的过滤条件和排序支持，减少回表次数。联合索引的选择性通常高于单列索引，有助于提高查询效率。

例如： 单列索引 idx_a(a)，联合索引 idx_ab(a, b)
select * from table where a = 10 and b = 20; – 优先用联合索引 idx_ab

8. 数据量小(500-1000行)

原因：选择性低，MySQL优化器可能认为全表扫描效率更高

例如：select * from small_table where status = 1; – 优化器可能全表扫描

9. 使用<>（sql中的 ≠），not等否定条件

原因：满足条件的数据分散，索引效率低，MySQL优化器认为全表扫描效率更高

例如：select * from table where col <> 5; – 大范围，索引失效

10. 对于查询null/not null时，大概率使用占比小的一方的索引

原因：优化器会根据数据分布，选择占比更小的一方进行索引扫描，以减少扫描量和提高效率

例如：select * from table where col is null; – 如果NULL较少，用索引
select * from table where col is not null; – 如果非NULL较少，用索引

11. group by 后没有 where 限制，且数据量大时，优化器可能选择全表扫描

原因：无过滤条件时，查询需要处理大量数据行，索引虽然能按顺序扫描，但仍需回表或额外计算

例如：select col1, count(*) from table group by col1; – 大数据量无过滤，可能全表扫描

二、记忆法

A. 记忆结构法

1. 索引结构相关（4条）
    最左前缀没用上
    sql用函数或运算
    类型不一致隐式转换
    范围查询后续索引失效

2. 条件表达式相关（3条）
    or条件字段无索引
    like左模糊查询
    <>、not等否定条件

3. 优化器选择相关（3条）
    数据量小，优化器选全表
    查询null时选小数据量索引
    group by无过滤且数据大选全表

4. 索引优先级相关（1条）
    联合索引优先于单列索引

B. 10字记忆法

模范最空函，群联小或否
提示：函包括两个，即2和3

模：使用like左模糊查询
范：范围查询字段后的索引失效
最：不满足最左前缀原则
空：查询null/not null时选占比小的索引
函：SQL中出现函数或运算
群：group by无where且数据量大时全表扫描
联：联合索引优先于单列索引
小：数据量小时优化器选全表扫描
或：or两侧字段任一无索引索引失效
否：使用<>、not等否定条件

三、误区

1.联合索引的使用如果中间跳过了某些字段，则只有被跳过的字段索引失效

纠正：联合索引必须按照从最左侧起的连续字段使用，中间不能跳字段，否则从跳过的字段起索引就部分失效或者全部失效。

原因：例如联合索引 (a, b, c)
select * from table where a = 10 and b = 20; – 满足最左前缀，索引生效。
select * from table where a = 10 and c = 20; – 只有a索引生效，这里虽然explain执行计划的 type 会显示 ref 但是实际上这只是告诉我们a索引生效了，并不是a和c都生效了，因为缺失b的情况，a是无法直接作为c的上级目录的。

！注意：如果查看执行计划的 extra 字段显示Using index condition：这是说明 MySQL使用了索引条件下推（ICP），在索引扫描阶段就提前过滤了非前缀条件，就是 c 的索引不参与定位（不生效于 B+ 树路径查找），但生效于叶子节点过滤（通过 ICP），简单来说就是索引的排序能力没用上，但过滤能力还在，这种情况下c就属于部分失效。

2.使用select * 会导致索引失效

纠正：使用 select * 本身不会导致索引失效。

原因：索引的使用与查询字段（select 后面的列）无直接关系，索引主要影响的是 where、join、order by、group by 等子句中的条件字段。

只要查询条件能利用索引，数据库就会尽量用索引查找符合条件的行。

使用 select * 只会导致查询返回所有列，可能增加回表（从数据页读取完整行）的成本，但不影响索引是否被使用。

3.如果order by语句中没有加where或limit关键字，该sql语句将不会走索引

纠正：索引是否被用来优化 order by，主要取决于查询条件和排序字段的关系，不是简单看有没有 where 或 limit

原因：如果 order by 的字段正好是索引的最左前缀，且查询返回的数据量大，数据库可以利用索引避免排序，直接按索引顺序读取数据。

但是如果查询没有条件限制（比如没有 where），且没有 limit 限制返回行数，mysql 通常认为全表扫描并按照索引顺序读数据和索引优化的效果差不多，可能不一定强制用索引。

简而言之，order by 是否用索引和是否有 where/limit 没直接必然关系，关键是索引能否匹配排序字段及查询条件。