MySQL 索引失效的常见场景有哪些？

简化版

索引失效常见于对索引列做函数或计算、隐式类型转换、前置模糊匹配、联合索引跳过最左列、范围条件后继续期望完整用索引、低选择性导致优化器放弃索引等。更准确地说，很多场景不是索引“坏了”，而是优化器认为无法利用索引有序性或走索引成本更高。

详细版

常见场景：

对索引列做函数：where date(create_time) = ?。
对索引列做计算：where price + 1 > 100。
隐式类型转换：字符串列用数字比较。
前置模糊：like '%abc'。
联合索引不满足最左前缀：索引 (a,b)，只查 b。
or 两边不是都有合适索引，可能走全表。
范围条件过大，优化器认为全表扫描更划算。
低选择性索引回表成本太高。

优化时要通过 EXPLAIN 看实际计划，而不是只凭经验判断。SQL 改写、补联合索引、调整字段类型、使用生成列或全文索引，都是常见处理方式。

完整版教学

一、索引失效的本质是有序性用不上

B+ 树索引能快，是因为索引 key 有序。优化器可以沿着有序结构快速定位范围。如果 SQL 写法让这个有序性无法使用，索引就难以发挥作用。

例如：

where date(create_time) = '2026-07-19'

索引里存的是原始 create_time，但查询要先对每一行做 date() 计算才能比较。优化器无法直接在 B+ 树上定位某个日期范围。

更好的写法是：

where create_time >= '2026-07-19 00:00:00'
  and create_time <  '2026-07-20 00:00:00'

这类改写的本质是把“对每行做函数判断”变成“在索引上找连续区间”。假设一天有 10 万条记录，全表有 3000 万条记录，函数写法可能需要对大量候选行逐个计算日期；范围写法可以直接定位到当天开始的位置，再扫描当天结束前的连续范围。性能差异来自定位能力，而不是 date() 函数本身有多慢。

记忆钩子：凡是让索引列保持“原样出现在比较左侧”的写法，更容易保留 B+ 树的有序定位能力。

二、隐式转换是很隐蔽的坑

如果字段是 varchar，SQL 却写：

where phone = 13800138000

MySQL 可能发生类型转换，导致无法按字符串索引正常定位，还可能出现比较语义异常。更稳妥的写法是让参数类型和字段类型一致：

where phone = '13800138000'

这类问题在线上很常见，因为 ORM、接口参数和数据库字段类型不一致时很容易埋坑。

隐式转换不仅可能影响索引，还可能影响结果正确性。字符串列按数字比较时，MySQL 可能把列值转换成数字，'00123'、'123'、'123abc' 的比较语义都会变得危险。更稳妥的工程做法是：数据库字段类型、接口参数类型、ORM 绑定类型保持一致，SQL 中不要依赖数据库替你“猜类型”。

字段类型	危险写法	推荐写法
`phone varchar`	`phone = 13800138000`	`phone = '13800138000'`
`id bigint`	`id = '1001abc'`	参数校验后传数字
`create_time datetime`	`date(create_time)=?`	时间范围查询
`price decimal`	`price + 1 > 100`	`price > 99`

三、like 不是都不能用索引

like 'abc%' 可以利用 B+ 树前缀有序性，因为能定位以 abc 开头的一段范围。

like '%abc' 或 like '%abc%' 很难用普通 B+ 树定位，因为前面的字符不确定，无法从索引起点开始找。此时要考虑全文索引、搜索引擎、倒排索引方案，或者改业务检索方式。

可以把字符串索引也看成字典序。'abc%' 对应从 abc 开始到 abd 之前的一段连续范围；'%abc' 则前缀未知，可能是 xabc、helloabc、2026abc，它们在字典序中分散各处，普通 B+ 树无法一次定位。面试里说“like 会失效”太粗糙，应该说“前缀确定的 like 可利用范围，前置通配难以利用普通 B+ 树”。

可定位：abc001, abc002, abcxyz  连续在 abc 前缀范围
难定位：xabc, helloabc, 9abc    分散在不同前缀位置

四、优化器放弃索引不一定错

有时 SQL 明明有索引，优化器却选择全表扫描。这不一定是失效，可能是成本模型判断走索引更贵。

例如某个条件命中表里 80% 的数据，如果走二级索引再大量回表，成本可能比直接全表扫描更高。此时强行使用索引未必更快。

假设表有 100 万行，gender='M' 命中 50 万行。如果走 gender 二级索引，还要拿 50 万个主键回表取完整行；如果全表扫描，顺序读整表反而可能更便宜。优化器选择全表扫描并不是索引“坏了”，而是在当前统计信息和成本模型下认为它更划算。

这也是为什么 FORCE INDEX 要谨慎。它可能短期让某个场景变快，但数据分布变化后反而拖慢查询。优化索引失效类问题，先判断“不能用”还是“不值得用”。

五、如何系统排查

排查索引失效，可以按顺序看：

EXPLAIN 的 key 是否为预期索引；
type 和 rows 是否合理；
是否有函数、计算、隐式转换；
联合索引是否满足最左前缀；
条件选择性是否太差；
Extra 是否有 Using index condition 或 Using where；
统计信息是否过旧。

必要时可以更新统计信息、改写 SQL 或重新设计联合索引。

排查时可以按这个流程做：

EXPLAIN 看 key/rows/Extra
 -> 检查 SQL 写法是否破坏索引列原样
 -> 检查参数类型是否和字段一致
 -> 检查联合索引左前缀和范围截断
 -> 判断选择性和回表成本
 -> 必要时更新统计信息或改索引

六、常见可改写方式

很多索引失效并不需要立刻加新索引，先改写 SQL 就能恢复访问路径。函数可以改范围，计算可以移到常量侧，前置模糊可以改业务检索方式，or 可以拆成 union all 后分别走索引，类型不一致则修参数绑定。

-- 计算列改写
where price + 1 > 100
-- 改为
where price > 99

-- OR 两侧都有索引时可评估拆分
select id from t where a = ?
union all
select id from t where b = ? and a <> ?;

改写前后都要用 Explain 和真实耗时验证，因为不同 MySQL 版本、数据分布和索引组合会影响最终计划。

七、常见误区与追问

误区：只要 SQL 没走索引，就是索引失效。 有时优化器是因为命中比例太高、回表成本太大而主动选择全表扫描。
误区：所有 like 都不能用索引。 like 'abc%' 可以利用前缀范围，like '%abc' 才难以使用普通 B+ 树定位。
误区：范围条件后面的联合索引列完全没价值。 后续列通常不能继续精准定位，但可能用于索引条件下推或过滤。
误区：用 FORCE INDEX 就能根治索引失效。 强制索引可能掩盖统计信息、SQL 写法和索引设计问题，数据分布变化后风险更大。
追问：为什么函数包裹索引列会影响索引？ 因为索引保存的是原始列值的有序结构，函数结果不再对应原始 B+ 树的连续定位区间。
追问：如何判断是写法问题还是优化器成本选择？ 看 SQL 是否破坏有序性，再比较选择性、回表成本、统计信息和 Explain 估算行数。

八、加强记忆

索引失效不是玄学，主线只有两条：B+ 树有序性还能不能被利用，优化器觉得走索引是否划算。函数包列、计算包列、隐式转换、前置模糊、跳过最左列，会破坏定位能力；低选择性和大量回表，会让优化器放弃索引。排查时先 Explain，再看 SQL 写法、参数类型、联合索引顺序、数据分布和统计信息，最后再决定改写 SQL、补索引还是调整统计。

简化版

详细版