MySQL 索引失效的常见原因和排查方法

MySQL 索引失效的本质只有两种：一是 SQL 写法让 B+ 树无法定位数据（函数包裹、隐式类型转换、前导模糊匹配），二是优化器估算走索引的代价高于全表扫描（区分度低、回表行数大、统计信息过期）。排查入口固定为 EXPLAIN + SHOW INDEX，先看 type 和 key，再看 rows、filtered 和 Extra。

排查索引失效先看 EXPLAIN 的哪几个字段？

结论：EXPLAIN 输出的 type、key、key_len、rows、Extra 五个字段，按顺序看就能定位绝大多数索引问题。

• type：const 是主键或唯一索引等值命中，ref/eq_ref 是普通索引等值命中，range 是范围扫描，index 是全索引扫描，ALL 是全表扫描——出现 ALL 基本就是索引失效。
• key：实际使用的索引名，为 NULL 说明没用上任何索引。
• key_len：联合索引用到了第几列。InnoDB 下 int 占 4 字节、bigint 占 8 字节、datetime 占 5 字节、varchar(n) 用 utf8mb4 时占 4n+2 字节，字段可空再各加 1 字节。
• rows：预估扫描行数，和实际返回行数差距大说明统计信息过期。
• Extra：出现 Using filesort 或 Using temporary 说明排序、分组没走索引；Using index 是覆盖索引；Using index condition 是索引下推（ICP，Index Condition Pushdown，MySQL 5.6 引入，把条件下推到存储引擎层过滤以减少回表）。

MySQL 8.0.18 及以上可以直接用 EXPLAIN ANALYZE SELECT ...，输出真实执行耗时和实际行数，比估算值更可靠。

哪些 SQL 写法会让索引失效？

结论：常见失效写法有 6 类，都能通过改写 SQL 绕开。

1. 索引列上做函数或运算：WHERE DATE(create_time) = '2024-01-01' 失效，改成 WHERE create_time >= '2024-01-01 00:00:00' AND create_time < '2024-01-02 00:00:00'。
2. 前导模糊匹配：LIKE '%abc' 无法使用索引，LIKE 'abc%' 可以用 range 扫描。
3. OR 连接非索引列：WHERE a = 1 OR b = 2，只要 b 没索引，整体退化为全表扫描，可拆成两条 SQL 用 UNION ALL 合并。
4. 联合索引不满足最左前缀：索引为 (a, b, c) 时，WHERE b = 1 AND c = 2 用不上该索引。
5. 范围查询后续列失效：WHERE a = 1 AND b > 10 AND c = 3 中，c 无法参与索引定位，只能靠 ICP 在引擎层过滤。
6. 排序方向与索引不一致：索引 (a, b) 遇到 ORDER BY a ASC, b DESC 会触发 Using filesort（MySQL 8.0 支持降序索引，可建成 (a ASC, b DESC)）。

隐式类型转换为什么最容易被忽略？

结论：当索引列是 varchar 而传入的是数字时，MySQL 会做 CAST(user_id AS signed) 转换，等价于在索引列上套了函数，索引直接失效。

user_id 定义为 varchar(32) 时写 WHERE user_id = 10086，EXPLAIN 的 type 会变成 ALL；改成 WHERE user_id = '10086'，type 立刻变成 ref。反过来，int 列传字符串 WHERE id = '100' 仍能走索引，因为常量会被转成数字。

字符集不一致同样致命：utf8 的表 JOIN utf8mb4 的表，关联列索引失效，用 SHOW CREATE TABLE 核对并统一字符集与排序规则。

走了索引就一定快吗？

结论：不一定，区分度低的列即使能走索引，优化器也可能主动选择全表扫描更快。

用 SELECT COUNT(DISTINCT status) / COUNT(*) FROM orders 评估区分度，比值接近 1 才适合建索引；性别、状态这类只有几个值的列，单列索引价值有限，更适合放进联合索引做前缀。

另外，SELECT * 走二级索引需要回表，如果查询列全部包含在索引里（覆盖索引，Extra 显示 Using index），性能差别明显。

一套完整的索引失效排查流程是什么？

结论：按「看执行计划 → 看索引定义 → 看列定义 → 更新统计信息 → 强制索引验证 → 慢日志兜底」六步走，能覆盖全部场景。

1. 执行 EXPLAIN 和 SHOW WARNINGS，后者会显示优化器改写后的 SQL。
2. SHOW INDEX FROM orders\G，确认索引是否存在、列顺序与基数。
3. 核对列类型、字符集、排序规则（information_schema.COLUMNS）。
4. ANALYZE TABLE orders; 刷新统计信息后重新 EXPLAIN。
5. 用 SELECT ... FORCE INDEX(idx_name) 强制走索引对比耗时，区分「用不了」和「优化器不想用」。
6. 开启慢查询日志：slow_query_log = ON、long_query_time = 1，用 mysqldumpslow 或 pt-query-digest 汇总慢 SQL 再逐条 EXPLAIN。

排查时优先改 SQL，其次调索引，最后才考虑 FORCE INDEX——强制索引会随数据量变化而变成新的性能陷阱。