SQL 中”建了索引却不走”通常不外两类根因：一类是索引列的原始值或顺序被破坏，B+ 树在物理上无法被遍历；另一类是索引能用、但优化器算了一笔账，认为回表成本比全表扫描还高，主动放弃。据 CSDN 一篇索引失效根因分析总结，前者（函数、计算、类型转换、字符集转换、前导通配符、跳过最左列、范围截断）是绝对失效，与数据量无关；后者（回表过多、OR 一侧无索引、否定条件、统计信息过期）是成本决策，是否失效取决于数据分布。排查的唯一标准是 EXPLAIN，看 type 是否为 ALL、key 是否为 NULL。
一、根因一：B+ 树有序性被破坏
索引存的是列的原始值，且按前缀有序排列。查询条件一旦改变这个原始值，或在树上找不到起点，索引就物理不可用。这类失效应能无关、必然发生。
| 失效场景 | 错误写法 | 失效原因 | 修复写法 |
|---|---|---|---|
| 索引列用函数 | WHERE DATE(create_time)='2026-06-01' |
函数改变了列原值 | 改成范围：BETWEEN '2026-06-01 00:00:00' AND '2026-06-01 23:59:59' |
| 索引列做运算 | WHERE age + 1 = 20 |
计算后的值在树中无序 | 计算移到右侧：age = 19 |
| 隐式类型转换 | phone 为 VARCHAR，WHERE phone = 13800138000 |
相当于对列 CAST，破坏有序性 |
保持类型一致：phone = '13800138000' |
| 前导模糊 | WHERE name LIKE '%张' |
前缀未知，无法定位起点 | 用右匹配：LIKE '张%' |
| 违反最左前缀 | 联合索引 (a,b,c) 只查 b |
跳过最左列找不到入口 | 补齐前导列或调整索引顺序 |
| 范围截断后续列 | (a,b) 中 a > 1 AND b = 2 |
范围区间内 b 无序 |
等值列放范围列前，建 (b,a) |
| 字符集不一致 | 两表关联字段编码不同 | CONVERT 作用在索引列 |
统一字符集与排序规则 |
据腾讯云开发者社区的解析，这七类可统一归于一句话：被索引字段的隐式转换、表达式计算、函数计算，都会破坏 B+ 树叶子节点的有序性，执行器无法判断原索引树还能否被检索，于是放弃使用。MySQL 8.0 引入了 Skip Scan，对”跳过最左列”有例外优化，但不能依赖。
二、根因二：优化器的成本决策
即使索引可用，优化器基于代价估算也可能选全表扫描。这类失效不绝对，换批数据或更新统计信息就可能反转。
回表成本过高。当索引筛选后仍需回表取大量行、且这些行在磁盘上分散时，随机 IO 成本可能超过顺序全扫。据 youngju.dev 的 PostgreSQL 案例，用 EXPLAIN 看 Heap Fetches 是否过大，或建覆盖索引（INCLUDE）避免回表，可恢复索引使用。
OR 一侧无索引。据 CSDN 多个面试题解析，WHERE a=1 OR b=2 只要 b 没有索引，整个查询可能直接全表扫描；应给两侧都建索引、或拆成 UNION。
否定条件（!=、<>、NOT IN、IS NOT NULL）。这类条件通常命中大多数行，优化器认为走索引回表不如全扫。可改写正向范围或用 LEFT JOIN ... IS NULL 替代 NOT IN。
统计信息过期。据 CSDN 根因分析，统计偏差会让优化器误判成本，用 ANALYZE TABLE 更新即可。
三、低基数列与 NULL 的影响
据 cnblogs 与 CSDN 的整理，当索引列选择性极差（如 gender 只有两个值）时，索引过滤效果弱，优化器倾向全表扫描——这类低基数列通常不适合建单列索引。索引列含大量 NULL 也可能影响优化器选择，核心业务字段建议设 NOT NULL 并给默认值。另据 youngju.dev，列的 correlation（物理顺序与索引顺序的相关性）接近 1 时，即便范围查询也利于用索引，接近 0 时则相反。
四、用 EXPLAIN 验证的四步法
据 CSDN 与腾讯云资料，排查固定动作如下：
- 在语句前加
EXPLAIN（PostgreSQL 加ANALYZE看真实执行）。 - 看
type：目标是ref/range/index，出现ALL即全表扫描。 - 看
key：若为NULL，说明没用上索引。 - 看
rows与Extra：rows过大、Extra出现Using filesort/Using temporary提示需调整索引或写法。
五、修复索引失效的通用顺序
- 把索引列上的函数、运算、类型转换全部移除或移到等号右侧。
- 模糊查询改右匹配，避免前导
%。 - 联合索引查询补齐最左列，等值列排范围列前。
- 统一关联字段的类型、字符集、排序规则。
- 用覆盖索引消除回表；低基数列不建单列索引。
- 跑
EXPLAIN确认key非空、type非ALL，必要时ANALYZE TABLE。
常见问题（FAQ）
LIKE 以 % 开头一定不能走索引吗？
普通 B+ 树索引下确实失效；PostgreSQL 可用 pg_trgm 扩展建 GIN 索引支持子串搜���。
!= 和 NOT IN 永远不走索引吗？
不是。它们属成本决策类，若命中行很少、统计准确，优化器仍可能选索引，应以 EXPLAIN 为准。
隐式转换为什么毁索引？
类型不匹配时引擎对列做 CAST，等价于对列用函数，破坏了索引有序性，与显式函数失效同理。
