默认

MySQL索引失效的原因、失效场景

约 3154 字

MySQL索引失效的原因

MySQL索引失效本质是查询语句无法通过索引快速定位数据，优化器判定走索引成本高于全表扫描，最终放弃索引、触发全表扫描。以下是生产环境中所有高频、核心的索引失效场景，附带原理说明与规避方案。

一、索引列运算/函数操作（最高频失效场景）

对索引字段本身进行数学运算、函数调用、类型转换，会导致索引失效。因为索引是基于字段原始值构建的，运算后的值无法匹配索引结构，数据库无法使用索引检索。

失效示例：

-- 索引列做运算
SELECT * FROM user WHERE age + 10 = 30;
-- 索引列使用函数
SELECT * FROM user WHERE DATE(create_time) = '2026-08-30';

优化方案：

将运算、函数转移到常量侧，保证索引列裸用。

SELECT * FROM user WHERE age = 20;
SELECT * FROM user WHERE create_time BETWEEN '2026-08-30 00:00:00' AND '2026-08-30 23:59:59';

二、隐式类型转换导致失效

查询条件中索引字段与匹配值类型不一致，MySQL会自动触发隐式类型转换，等价于对索引列做函数操作，直接失效。

核心规则：

  • 字符串索引列匹配数字：索引失效（高频），如 `varchar` 字段查数字 `WHERE phone = 13800138000`
  • 数字索引列匹配字符串：索引有效，MySQL会将字符串转数字，不操作索引列

失效示例：

-- phone 为 varchar 索引字段，传入数字，触发隐式转换，索引失效
SELECT * FROM user WHERE phone = 13800138000;

优化方案：

保持查询值与字段数据类型完全一致，字符串加引号、数字不加引号。

三、模糊查询左匹配失效（最左前缀原则破坏）

普通B+树索引是前缀匹配索引，仅支持右模糊、精准匹配，左模糊、全模糊会导致索引失效。

失效场景：

-- 左模糊、全模糊，索引失效
SELECT * FROM user WHERE name LIKE '%张三';
SELECT * FROM user WHERE name LIKE '%张三%';

有效场景：

-- 右模糊，符合最左前缀，索引生效
SELECT * FROM user WHERE name LIKE '张三%';

优化方案：

需全模糊/左模糊查询时，使用 全文索引（FULLTEXT） 或 ES搜索引擎替代普通索引。

四、违背联合索引最左前缀原则

联合索引（复合索引）遵循最左前缀匹配原则，创建索引 `(a,b,c)` 时，查询条件必须包含最左侧首个字段a，否则整个联合索引失效。

失效示例（无最左字段）：

-- 联合索引(a,b,c)，查询仅用b、c，无a，索引完全失效
SELECT * FROM table WHERE b=1 AND c=2;

有效场景：

包含最左字段a，可命中索引（可不用后续字段）：`WHERE a=1`、`WHERE a=1 AND b=2`、`WHERE a=1 AND c=2`

补充：

联合索引中间字段断裂，不影响前置索引生效，仅后续字段无法使用索引。例如 `WHERE a=1 AND c=2`，a字段索引生效，c字段索引失效。

五、OR条件使用不当导致索引失效

查询条件中使用 OR 时，若任意一个OR条件字段无索引，整个语句所有索引全部失效，触发全表扫描。

失效示例：

-- age有索引，address无索引，OR导致整体索引失效
SELECT * FROM user WHERE age=18 OR address='北京';

优化方案：

  • 保证OR关联的所有字段均建立索引；
  • 复杂OR逻辑使用 `UNION ALL` 替代。

六、NOT、!=、<>、NOT IN 否定查询

对索引列使用否定查询（不等于、不在范围内），MySQL优化器通常会放弃索引。原因：否定条件匹配的数据量大概率极大，全表扫描成本更低。

失效示例：

SELECT * FROM user WHERE age != 18;
SELECT * FROM user WHERE age NOT IN (18,20);
SELECT * FROM user WHERE name <> '张三';

特殊情况：

若否定条件匹配数据量极少（表数据量大、命中数据极少），部分高版本MySQL会走索引，但不绝对，属于不稳定场景。

七、IS NULL / IS NOT NULL 索引失效

B+树索引不存储NULL值，因此索引列的空值查询存在失效规则：

  • IS NOT NULL：大概率索引失效，非空数据占比极高，优化器选择全表扫描；
  • IS NULL：部分场景可命中索引，但效率极低，不推荐使用。

优化方案：

字段设计时禁止NULL，默认赋值空字符串、0等默认值，规避空值查询。

八、查询数据量过大（索引失效核心场景）

MySQL优化器会计算成本，当查询结果集**超过表总数据的20%~30%**时，判定走索引的回表成本高于全表扫描，直接放弃索引。

常见场景：范围查询过大 `>`、`<`、`BETWEEN` 匹配大量数据、无分页查询全量数据。

示例：

一张10万数据的表，查询 `WHERE id > 1000` 匹配8万条数据，索引直接失效。

九、索引失效的其他小众场景

1. 索引过期/统计信息不准确

MySQL依赖索引统计信息判断执行计划，长期未更新统计信息、表频繁增删改，会导致优化器误判，错误放弃索引。可通过 `ANALYZE TABLE 表名;` 刷新统计信息。

2. 短索引/前缀索引局限性

对长文本字段建立前缀索引（仅截取前N个字符），查询条件超出截取长度时，无法精准匹配，索引失效。

3. 强制全表扫描参数

会话/全局开启 `IGNORE INDEX` 、优化器参数配置异常，会主动禁用索引。

4. 主键索引特殊场景

InnoDB主键索引为聚簇索引，极少失效，但大批量范围删除、更新时，会触发优化器放弃索引。

十、快速排查索引失效的方法

在SQL前加 EXPLAIN，查看执行计划：

  • type=ALL：全表扫描，索引失效；
  • key=NULL：未使用任何索引；
  • rows 数值极大：索引未生效。

总结：索引失效核心避坑口诀

不运算、不转换、左模糊不行；

联合索引守前缀、OR字段全索引；

否定空值少使用、大数据不走索引；

类型一致是基础、统计信息要新鲜。