跳到主要内容

索引优化与失效场景

索引「建了」不等于「用了」。本节整理最左前缀、常见失效模式，以及用 EXPLAIN 验证的方法。

1. 最左前缀原则​

联合索引 (a, b, c) 的 B+ 树按 a → b → c 排序。优化器能利用的是从最左开始的连续前缀：

WHERE / ORDER 条件是否可用索引前缀大致可用部分
a = ?是a
a = ? AND b = ?是a, b
a = ? AND b = ? AND c = ?是a, b, c
a = ? AND c = ?部分通常只用到 a，c 在索引内过滤或回表后过滤
b = ? / c = ?否（正常情况）跳过最左列
a > ? AND b = ?部分范围后的列往往无法再用于缩小索引范围

等值 + 范围： 尽量把等值列放前面、范围列放后面。例如高频条件是 user_id = ? AND status = ? AND created_at > ?，索引倾向 (user_id, status, created_at) 而不是把 created_at 夹在中间。

2. 常见索引失效或效果变差的场景​

2.1 对索引列做运算或函数​

-- 差：对列包函数
WHERE YEAR(created_at) = 2026
WHERE DATE(created_at) = '2026-01-01'

-- 好：写成范围，保留列「裸露」
WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

2.2 隐式类型转换​

-- phone 是 VARCHAR，却用数字比较，可能无法走索引
WHERE phone = 13800138000

-- 保持字符串比较
WHERE phone = '13800138000'

2.3 前导模糊匹配​

WHERE name LIKE '%张%'   -- 通常无法用 B+ 树定位
WHERE name LIKE '张%'    -- 可用前缀

2.4 OR 连接条件​

若 OR 两侧并非都能走合适索引，优化器可能退化为全表扫描。可考虑改写为 UNION，或保证两侧各有可用索引。

2.5 低区分度 + 回表成本过高​

例如 gender 只有两三种取值：即使有索引，优化器也可能认为扫全表更便宜。此时索引价值低，甚至可以不加。

2.6 不匹配的排序​

ORDER BY 与索引顺序不一致时，可能出现 Using filesort。联合索引可同时服务过滤与排序：

-- 索引 (user_id, created_at) 友好
WHERE user_id = 1 ORDER BY created_at DESC

3. 用 EXPLAIN 验证​

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND created_at > '2026-01-01';

重点关注字段（MySQL 5.7/8.0 常见输出）：

字段期望/解读
type越好越靠前：const / eq_ref / ref / range；尽量避免 ALL（全表）
key实际选用的索引名
key_len用到的索引长度，可粗判联合索引用了几列
rows估计扫描行数（估算值，但趋势有用）
ExtraUsing index ≈ 覆盖索引；Using where；Using filesort；Using temporary 需警惕

MySQL 8.0 可进一步使用 EXPLAIN ANALYZE 看真实执行时间与行数。

4. 设计与维护原则（可执行清单）​

  1. 先统计查询，再建索引：从慢查询日志、APM、业务接口入手。
  2. 能联合则少而精：(a, b) 通常可覆盖单独查 a 的场景，减少冗余单列索引。
  3. 控制单表索引数量：写入放大明显时（如订单流水表）更要克制。
  4. 避免重复索引：如已有 (a, b)，再单独建 (a) 往往多余。
  5. 长事务 + 大量更新 会拖长锁与 undo，间接放大索引维护成本（见事务与锁章节）。
  6. 定期回顾：用 sys.schema_unused_indexes（若可用）或监控发现长期未使用索引并评估删除。

5. 小案例​

需求： 用户订单列表：user_id = ?，按时间倒序，只要最近状态。

-- 查询
SELECT id, status, created_at, amount
FROM orders
WHERE user_id = ?
ORDER BY created_at DESC
LIMIT 20;

-- 候选索引
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);
-- 若几乎总是只取这些列，可考虑覆盖：
-- (user_id, created_at, status, amount) —— 需权衡索引宽度

6. 与并发章节的交界​

  • 二级索引上的查找范围，也是行锁、间隙锁作用的区间基础。
  • 「锁住了过多间隙」有时正是因为查询走了过大的索引范围或没走到预期索引。

继续阅读：事务基础 → 隔离级别 → 锁机制。