第二大脑

主题

2026-08-24
880
3.3m

面试题蒸馏卡：数据库索引失效定位 ​

问题：数据库索引失效的发生场景？怎么定位原因？

一句话机制 ​

索引本质是 B+Tree 的有序结构；一旦查询条件让"索引列的值"在比较前被改变，或破坏了 B+Tree 的有序性，优化器就无法用索引做范围/等值定位，退化为全表扫描（type=ALL）。

发生场景（哪些写法让索引"用了等于没用"） ​

写法原因
WHERE DATE(create_time)='2026-08-20'对索引列套函数
WHERE amount + 1 > 10对索引列做运算
WHERE phone = 13800138000（phone 是 varchar）隐式类型转换，字符串列被转数值
两表 join 字符集/排序规则不同隐式转换，索引失效
LIKE '%xxx' / 左右模糊前导模糊无法走 B+Tree 前缀
联合索引 (a,b,c)，WHERE b=1不满足最左前缀
联合索引 (a,b)，WHERE a>1 AND b=2范围查询后列失效
WHERE a=1 OR b=2（b 无索引）OR 含非索引列，整句放弃索引
!= <> NOT IN IS NOT NULL多数情况优化器不选索引
数据量小 / 区分度极低 / 回表代价高优化器认为全表更快

怎么定位原因 ​

sql
EXPLAIN SELECT ... ;
-- 看 type=ALL（全表）→ key=NULL（没用索引）→ rows（扫描行数大）
-- Extra: Using filesort / Using where / Using index
EXPLAIN FORMAT=JSON SELECT ...;   -- 看 cost_info、是否 reversed
SHOW WARNINGS;                    -- 看优化器改写后的"真实 SQL"
-- 看优化器为什么没选：
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SHOW INDEX FROM t;                -- 看 Cardinality 区分度

配套手段：慢查询日志 + pt-query-digest；统计信息过时执行 ANALYZE TABLE t。

怎么处理 ​

  • 改写：把列上的函数/运算挪到常量侧（create_time >= '...' AND create_time < '...'）
  • 统一类型、字符集、排序规则（库/表/列三级一致）
  • 调整联合索引顺序满足最左前缀；用覆盖索引避免回表
  • 更新统计信息；极个别场景 FORCE INDEX（谨慎，易掩盖真问题）

追问链 ​

  1. 最左前缀为什么重要？ 联合索引的 B+Tree 按 (a,b,c) 顺序建，先按 a 排序、a 相同才比 b——跳过 a 直接查 b 无法二分定位。
  2. 隐式类型转换底层发生了什么？ MySQL 对索引列做了 CAST，等价于"对列套了函数"，索引失效。
  3. LIKE 'abc%' 能走索引吗？ 能，前缀匹配仍可用 B+Tree 前缀；只有前导 % 不行。
  4. 优化器什么时候会主动放弃索引？ 估算 cost：当回表行数多、或表很小、或区分度低时，全表扫描更便宜。
  5. 怎么证明索引"真的"失效，而非慢在别处？ EXPLAIN 的 type=ALL + key=NULL + rows 大，且实际执行时间与扫描行数成正比。

常见误解 ​

  • ❌ "用了索引字段就一定走索引" —— 函数/运算/隐式转换立刻失效。
  • ❌ "OR 一定失效" —— 若 OR 两边都有独立索引，MySQL 走 index merge。
  • ❌ "覆盖索引只是快一点" —— 覆盖索引免回表，甚至让某些本该失效的查询（纯索引列过滤）变快。

关联 ​

最近更新