SQL索引失效的15种真实场景与修复方案

一、索引为什么会“罢工”：先讲清底层逻辑

B+树索引的核心价值在于“有序”。索引键按顺序组织成一棵平衡多叉树，查询时通过键值比较逐层下潜，把扫描范围从整张表收缩到少数几个数据页，定位代价近似对数级。这套机制要成立，有两个前提：

1. 查询条件能转化为对索引键的连续区间判断；
2. 索引列在比较时保持原始值形态，没有被函数、运算或类型转换“加工”过。

任何一条被破坏，优化器都无法利用有序性收缩范围，只能放弃索引、退化为全表扫描。数据量小的时候感知不明显，一旦达到千万级，毫秒级的索引定位可能劣化为秒级甚至更久的全表扫描，直接演变成线上慢查询。

下面把15种高频失效场景归纳为五大类，逐一拆解成因、错误写法与修复方案。示例基于主流关系型数据库的通用SQL语法，以订单表orders、用户表user为背景。

二、表达式类：索引列被“动了手脚”（场景1~5）

场景1：对索引列使用函数。 索引中存储的是列的原始值，函数计算只能发生在数据读出之后。用“计算结果”去匹配一棵按“原始值”排序的树，有序性完全派不上用场。

-- 错误：在 create_time 索引列上套用日期函数
SELECT * FROM orders WHERE DATE(create_time) = '2026-05-24';
-- 正确：把运算移到常量一侧，改写为范围条件
SELECT * FROM orders
WHERE create_time >= '2026-05-24 00:00:00'
  AND create_time <  '2026-05-25 00:00:00';

修复口诀是“加工常量，不加工列”。日期查询优先改写为左闭右开的范围条件，既恢复索引可用性，又能规避时间精度造成的边界遗漏。

场景2：对索引列做算术或拼接运算。 本质与场景1相同，只是把函数换成了运算符。WHERE age + 1 = 20 应改写为 age = 19；拼接条件同理。凡是参与计算的列都不再是“原始值”，索引即告失效。写SQL时先问自己一句：这个条件里，索引列有没有被算过？

场景3：隐式类型转换。 字符串与数字比较时，数据库通常会把字符串一侧转成数字，等价于对索引列套了一层转换函数：

-- 错误：user_id 是整型，传入字符串触发隐式转换
SELECT * FROM user WHERE user_id = '10086';
-- 正确：传参与字段类型严格一致
SELECT * FROM user WHERE user_id = 10086;

反向场景更隐蔽也更危险：手机号、订单号这类以字符串存储的列，若用数字字面量去查，同样触发转换，还可能引入精度问题。原则只有一条：传参类型与字段类型严格一致。

场景4：隐式字符集/排序规则转换。 两表JOIN时，若关联字段的字符集或排序规则不一致，其中一侧会被隐式转换后再参与比较，该侧索引随之失效。这类问题在分批建设、多团队协作的系统里高发，SQL单看毫无破绽，只有对比两表结构才能发现。预防手段是建表规范：同一业务链路的表统一字符集与排序规则，关联字段类型逐一对齐。

场景5：前导模糊匹配。 LIKE '%xxx' 无法确定起始边界，等于在一棵有序树上做“任意位置匹配”，有序性无从发挥；LIKE 'xxx%' 可转化为前缀范围，正常走索引。

-- 左模糊：索引失效
SELECT * FROM orders WHERE order_no LIKE '%240524';
-- 右前缀：可利用索引
SELECT * FROM orders WHERE order_no LIKE '240524%';

业务上确实需要“包含匹配”时，应考虑全文检索或搜索引擎类方案，而不是让B+树硬扛。

三、联合索引类：最左前缀没吃透（场景6~8）

场景6：查询条件缺失最左列。 联合索引 (a, b, c) 的排序规则是先按a排、a相同再按b排、b相同再按c排。若查询条件里只有b和c，最左列a没有边界，整棵树对b、c而言是“全局无序”的，无从定位。设计原则：把最常作为等值条件且区分度高的列放在联合索引最左侧。

场景7：范围查询中断后续列。 条件 a = 1 AND b > 10 AND c = 5 中，a、b都能利用索引，但b一旦是范围匹配，c在索引内部就不再有序，无法继续用于定位，只能作为覆盖索引内的过滤条件。因此联合索引应遵循“等值列在前，范围列在后”。

场景8：排序与索引顺序不匹配。 ORDER BY列的组合顺序、方向与索引定义不一致时，索引无法消除排序动作，数据库需要额外的排序步骤。让排序子句尽量贴合索引列顺序，是零成本的性能优化。

四、逻辑条件类：写法合法但优化器不买账（场景9~11）

场景9：OR连接了无索引的列。 WHERE a = 1 OR d = 2，只要d上没有可用索引，整个条件就只能全表扫描——OR语义要求两边的行都要取到，任何一边走全表，另一边的索引就白搭。常见修复是改写为两条SQL用UNION ALL拼接，让各条件命中各自索引，同时保证两段条件互斥或做去重处理。

场景10：否定条件。 !=、NOT IN、NOT EXISTS 语义上无法圈定连续区间，通常伴随大量扫描；能否走索引完全取决于优化器对选择率的估算，不能作为性能依赖。业务上可优先考虑反向改写为肯定条件。

场景11：NULL判断。 允许NULL的列上，IS NULL一般仍可使用索引；而IS NOT NULL命中的行占比往往很高，优化器倾向全表。更根本的做法是设计上尽量给列设置NOT NULL约束和默认值，减少NULL语义带来的不确定性。

五、优化器决策类：索引在，但不用（场景12~14）

场景12：回表代价过高。 二级索引只存索引列与主键，SELECT * 需要按主键回表取其余列。当预估命中行数占比高时，随机回表的代价可能超过顺序全表扫描，优化器会“理性地”放弃索引。修复思路有二：只查需要的列；为高频查询设计覆盖索引，让所需列全部落在索引里，免掉回表。

场景13：统计信息失真。 优化器依赖统计信息估算行数与代价。表经历大批量写入、删除后，统计信息可能严重偏离真实分布，导致执行计划突然劣化。应定期收集统计信息（多数数据库提供ANALYZE类语句），执行计划突变时优先核查这一项。

场景14：低区分度列单独建索引。 性别、状态这类取值寥寥的列，单独建索引后每个值都对应海量行，走索引再回表反而更慢。正确姿势是与高区分度列组成联合索引，或配合覆盖索引使用。

六、连接查询类（场景15）

场景15：JOIN条件中加工索引列。 ON DATE(t1.create_time) = t2.stat_date 这类写法，是场景1在JOIN里的翻版：被加工一侧的索引失效，连接方式随之劣化，驱动顺序也可能被打乱。修复思路一致——把加工移到常量侧或改写为范围判断，保证连接键以原始形态参与比较。

七、如何快速定位索引失效

1. 看执行计划：使用EXPLAIN类语句观察四个关键点——访问类型（出现全表扫描级别的ALL时要警惕）、实际选用的索引、预估扫描行数、附加信息（是否出现额外排序、是否用到覆盖索引）。
2. 对比验证：用改写后的SQL对比执行计划与真实耗时，确认收益后再上线。
3. 建立基线：新查询上线前，在贴近生产的数据量上验证。很多失效在小数据量下不暴露，一到线上才爆雷。

八、研发规范清单：预防优于救火

• WHERE、JOIN、ORDER BY中不出现对索引列的函数、运算与拼接；
• 传参类型与字段类型严格一致，字符串列一律用字符串字面量查询；
• 模糊查询只允许右前缀，任意位置匹配走检索方案；
• 联合索引遵循最左前缀，等值列在前、范围列在后；
• 统一建表规范：字符集、排序规则、空值约束一次定好；
• 新增SQL先过执行计划，纳入代码评审清单；
• 大批量数据变更后主动收集统计信息。

九、写在最后

索引失效可以浓缩成一句话：优化器只在“能利用有序性且代价划算”时才走索引。写SQL时守住三条底线——不加工索引列、不破坏有序性、不抬高回表代价，绝大多数慢查询都能在开发阶段被拦截，而不是等到线上告警再去救火。