数据库

索引为何失效？8大典型场景与10步排查清单

索引不是“建了就一直生效”的开关。它是否被使用，取决于 SQL 写法、列类型、联合索引顺序、统计信息和优化器成本判断。本文把最常见的 8 个失效场景拆开讲，再给出一份可以直接照着执行的 10 步排查清单。目标是：当你看到“明明有索引却走了全表扫描”时，能快速判断是写法问题、索引设计问题，还是优化器基于成本做出的合理选择，并在 2026 年的真实业务表上做出可验证的优化。

一、8 大典型场景

  1. 函数或表达式包裹索引列：索引只能看“原值” 典型写法包括 WHERE DATE(create_time) = '2026-10-31'、WHERE id + 1 = 100、WHERE UPPER(name) = 'ABC'。索引里保存的是列的原值，一旦列被函数、运算或 CASE 包裹，优化器通常无法直接利用该索引做范围定位，只能退化为逐行计算后再过滤。更隐蔽的是 JOIN 条件里写 ON t1.id + 0 = t2.id，看起来只是“加个零”，实际已经阻断了索引。修复方向优先是重写 SQL：把日期改成左闭右开范围，例如 create_time >= '2026-10-31 00:00:00' AND create_time < '2026-11-01 00:00:00'；把 id + 1 = 100 改成 id = 99；把 UPPER(name) 的业务需求改成入库时统一大小写，或确认数据库支持后再建函数索引、生成列索引。函数索引不是万能药，它会增加写入维护成本，也会让优化器选择更复杂，先看能否重写，再考虑加索引。

  2. 隐式类型转换：类型不一致，索引可能直接失效 字符串列与数字比较是最常见的坑。例如手机号列是 varchar，SQL 写成 WHERE phone = 13800000000，数据库可能把列转成数字再比较，导致索引无法按原值定位；反过来，数字列与字符串比较，也可能发生转换。字符集或排序规则不一致的 JOIN 同样会触发转换，尤其是多套表结构历史遗留、不同库同步过来的场景。排查时先看列定义、参数类型和执行计划中的 warning，确认比较双方是否同类型、同字符集、同 collation。修复方式很直接：参数类型与列保持一致，字符串参数加引号，JOIN 两侧列的字符集和排序规则统一。不要依赖数据库“帮忙转”，因为转换规则一旦作用在列上，索引就可能从可用变成不可用。

  3. 前导通配符与模糊匹配：LIKE '%关键词' 很难走 B+ 树 B+ 树索引按照最左前缀有序组织，LIKE '关键词%' 还能利用前缀定位，但 LIKE '%关键词' 或 LIKE '%关键词%' 因为前面不确定，通常无法直接使用普通索引。很多业务搜索需求确实是前后模糊，这时不要盲目建普通索引，而应评估全文索引、倒排索引、ngram 分词或独立搜索引擎。若数据量不大，全表扫描可能反而是合理选择；若数据量大且查询频繁，应把检索能力放到合适的存储组件里。还要注意，OR 组合里的 LIKE、多个模糊条件叠加、对拼接字段做 LIKE，都会让优化器更难利用索引。判断标准不是“有没有索引”，而是“过滤后行数是否足够少、扫描成本是否可接受”。

  4. 联合索引最左前缀不匹配：顺序和跳列都很关键 联合索引 (a, b, c) 并不是对 b、c 单独建了索引。查询 WHERE b = ? AND c = ? 通常无法使用该联合索引；WHERE a = ? AND c = ? 跳过了 b，定位能力也会削弱；范围条件之后的列，往往不能继续用于精确缩小范围。排序同样要匹配：WHERE a = ? ORDER BY b, c 可能很合适，但 ORDER BY c, b 或方向不一致就可能产生额外排序。修复时先画清楚索引列顺序，再对照 SQL 中的等值条件、范围条件和排序条件。一般原则是等值列在前、范围列靠后，高频排序尽量纳入索引；但不要为了一个偶尔出现的查询把索引无限加宽，索引越宽，写入维护和空间成本越高。用 key_len 可以辅助判断实际用了联合索引的哪几列。

  5. OR 条件混合与 IN 列表：两边都能用索引才更稳 WHERE a = ? OR b = ? 这种写法，如果 b 上没有可用索引，优化器可能认为走 a 的索引再回表并不划算，最终选择全表扫描；只有 OR 两侧都具备可用索引时，才更可能使用 index merge 或分别定位后合并。IN 通常可以利用索引，但超大列表、类型不一致、选择率很低时，也可能被优化器放弃。实际排查不要凭感觉，要看执行计划里的 possible_keys、key、rows 和 filtered。可尝试把 OR 改写成 UNION ALL 拆分，把大 IN 拆成批量查询，或为高频过滤列补上合适的联合索引。但要注意，改写后的 SQL 语义必须一致，去重、排序和 NULL 处理都不能改变，否则“优化”会变成新的数据问题。

  6. 排序、分组与索引顺序不一致：filesort 和临时表会放大成本 ORDER BY 多列方向不一致、排序列不在索引中、WHERE 条件与 ORDER BY 不能形成同一条索引路径，都可能出现 Using filesort；GROUP BY 也可能产生 Using temporary。比如索引是 (status, create_time)，查询 WHERE status = ? ORDER BY create_time DESC 通常比较友好；但如果 ORDER BY create_time ASC, id DESC，而索引顺序或方向不匹配，就可能需要额外排序。深翻页 LIMIT 100000, 20 也是常见问题：即使走了索引，大量回表仍会拖慢响应。修复方向包括让索引顺序匹配等值条件后的排序、使用覆盖索引减少回表、用延迟关联或游标分页替代大偏移量分页。排序不是一定要消灭，而是要量化它带来的 CPU、内存和 IO 成本。

  7. 统计信息过期、数据倾斜与优化器误判 表刚经历大批量导入、删除或更新后，统计信息可能还没跟上；某个状态值占 99% 时，走索引再回表反而不如全表扫描。优化器是基于成本估算做选择的，不是“看到索引就一定用”。如果它低估或高估了行数，就可能选错计划。排查时更新统计信息，查看直方图、索引基数和大致的值分布，对比 EXPLAIN 预估行数与 EXPLAIN ANALYZE 的真实行数。若确认是误判，优先调整索引设计或改写 SQL，让优化器有更好的选择；hint 只能作为临时止血手段，并且要记录原因、设置复核时间。否则下次数据分布变化，hint 可能从“救火”变成“埋雷”。

  8. 选择性低、表太小或回表成本高：不走索引可能是合理选择 性别、状态、逻辑删除标记等低选择性列，单独建索引后每个值仍然对应大量行，优化器常认为全表扫描更便宜；小表更是如此，几页数据全扫可能比索引查找还快。另一种情况是查询需要大量回表：索引只帮你找到主键，随后还要随机读很多数据页，成本可能高于顺序全扫。此时覆盖索引能减少回表，把高频查询需要的列纳入索引；但覆盖索引会增加索引宽度和写入成本，不能无脑加。要记住，不是所有“没走索引”都是故障，可能是合理的成本选择。判断标准应看响应时间、扫描行数、实际 IO、P95 延迟和稳定性，而不是只看执行计划里的 key 是否为空。

二、10 步排查清单

  1. 先锁定真实 SQL 与执行计划 从慢日志、APM 或数据库监控里拿到真实 SQL 和真实参数，不要用改写后的“近似 SQL”排查，因为参数不同、数据分布不同，执行计划可能完全不同。执行 EXPLAIN 或 EXPLAIN ANALYZE，保存 type、possible_keys、key、key_len、rows、filtered、Extra 等关键信息。先建立基线：这条 SQL 正常时是什么计划，现在变成了什么计划，变化发生在哪一天、哪次发布或哪次数据导入之后。没有基线的优化很容易变成猜谜。

  2. 看访问类型与实际扫描行数 type 从 const、eq_ref、ref、range 到 index、ALL，越靠后越要警惕，但不要只看 type。结合 rows 与 filtered 估算实际扫描量，再用 EXPLAIN ANALYZE 看真实行数和耗时。如果预估行数与真实行数差了几个数量级，优先转去排查统计信息和数据倾斜。还要区分“扫描了很多行但很快”和“扫描行数不多但很慢”：前者可能是顺序 IO，后者可能是随机回表或锁等待。执行计划只是地图，真实耗时才是路况。

  3. 检查索引列是否被表达式包裹 逐列检查 WHERE、JOIN、ORDER BY、GROUP BY 中的列。函数、运算、类型转换、CASE、COALESCE、字符串拼接都可能阻断索引。能改写就改写成等值或范围条件；不能改写再评估函数索引、生成列索引或调整数据模型。特别留意 ORM 自动生成的 SQL，很多“隐形函数”来自框架的类型处理、日期格式化和软删除条件。把最终 SQL 打印出来看，不要只审查代码里的查询构造器。

  4. 检查隐式转换与字符集排序规则 核对列类型与参数类型，字符串参数必须加引号；JOIN 两侧的字符集和 collation 要一致。用 SHOW WARNINGS 或执行计划提示确认是否发生转换。常见场景包括 varchar 手机号传数字、日期列传字符串、枚举列传整数、不同库同步后 collation 不一致。修复时优先统一模型和参数类型，而不是在 SQL 里到处 CAST。因为 CAST 参数有时还能用索引，CAST 列则通常会让索引失效，两者不要混为一谈。

  5. 检查联合索引最左前缀与列顺序 把索引列顺序画出来，对照 SQL 的等值、范围和排序条件。等值列优先，范围列靠后；跳列会削弱定位能力；范围条件后的列通常难以继续用于精确缩小范围。用 key_len 判断实际用了索引的哪几列，再结合 rows 看过滤效果。不要为了一个偶尔的查询把索引无限加宽，也不要重复建前缀相同的索引。索引设计要围绕高频、稳定、选择性好的查询，而不是围绕某一次慢查询临时堆料。

  6. 检查 LIKE、OR、IN 与范围组合 确认 LIKE 是否前导 %，OR 两侧是否都有可用索引，IN 列表是否过大或类型不一致，范围条件是否让后续列失效。必要时用 UNION ALL 拆分 OR，用覆盖索引减少回表，用游标分页替代深翻页。对于模糊搜索，先判断业务是否真的需要前后模糊；如果只是前缀搜索，普通索引仍有机会；如果是全文检索，就使用合适的全文或倒排能力。任何改写都要用相同参数做对照测试，确认结果集、排序和去重语义不变。

  7. 检查排序和分组是否产生额外开销 看 Extra 是否出现 Using filesort、Using temporary。让 ORDER BY、GROUP BY 的顺序尽量与索引一致，注意多列排序方向。若无法避免，评估是否把排序放到应用层、缩小结果集或接受成本，但一定要量化。深翻页场景要特别关注回表次数，可以通过延迟关联先取主键再取详情，或使用基于游标的分页。排序优化不是把 filesort 字样消灭就结束，而是降低总延迟和资源占用。

  8. 检查统计信息与数据分布 对频繁变更的表按数据库语法执行 ANALYZE，查看直方图、索引基数和大致的值分布。对比 EXPLAIN 预估行数与真实行数，判断优化器是否误判。统计信息不准时，先修正统计，再谈加索引；数据倾斜严重时，考虑改写查询、拆分热点值或调整索引策略。不要忽视批量导入、归档删除和分区切换对统计信息的影响，这些操作之后往往是执行计划突变的窗口期。

  9. 评估选择性、覆盖索引与回表成本 计算列的选择性：不同值越多、过滤后行数越少，索引越有价值。低选择性列单独建索引收益有限；把高频查询需要的列纳入覆盖索引，可以减少回表，但会增加索引宽度和写入成本。用实际 IO、扫描行数和响应时间验证，不要只看“是否命中索引”。如果优化器选择全表扫描但响应稳定、资源可控，也要承认这是合理计划；如果全扫导致 P95 抖动，就要从索引、SQL 和数据模型三方面一起改。

  10. 最小复现、对照实验与上线观察 在测试库用相同数据分布复现，一次只改一个变量：SQL、索引或统计信息。对比执行计划、扫描行数、P95 延迟和写入开销。上线后观察慢日志、索引使用率和资源曲线，保留回滚方案。hint 只能作为临时止血，不要长期掩盖设计问题。最后把结论写进团队知识库：哪类写法会阻断索引、哪些索引对应哪些查询、统计信息何时更新。这样下一次“索引失效”才不会从零开始猜。

三、收尾 takeaways 索引失效很少是单一原因。先判断是“写法阻断索引”，还是“优化器认为不走索引更便宜”。按 8 个场景定位，再按 10 步清单验证，最后用真实数据分布和执行计划说话。记住：优化的目标不是让每个查询都命中索引，而是让关键查询稳定、可解释、可回滚。