MySQL 索引优化实战：让慢查询快 100 倍的思考方式

前言

90% 的 MySQL 性能问题出在索引上。本文不讲教条，而是给出"拿到一条慢 SQL 后怎么想"的排查路径，每个结论都配 EXPLAIN 验证方法。

一、先看懂 EXPLAIN

任何优化从这条命令开始：

EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

重点看四列：

type 从优到差大致是：const > eq_ref > ref > range > index > ALL。目标是让关键查询至少到 ref 或 range。

二、联合索引：最左前缀是第一性原理

联合索引 (user_id, status, created_at) 相当于按"用户→状态→时间"排序的电话簿：

-- ✅ 能用到索引
WHERE user_id = 1001
WHERE user_id = 1001 AND status = 'paid'
WHERE user_id = 1001 AND status = 'paid' AND created_at > '2026-01-01'

-- ❌ 用不到（缺少最左列）
WHERE status = 'paid'
-- ⚠️ 只能用 user_id 一段（中间断档）
WHERE user_id = 1001 AND created_at > '2026-01-01'

两条推论：

1. 等值列放前面，范围列放最后。范围条件之后的列无法继续走索引排序；
2. ORDER BY 想走索引消除 filesort，排序列必须紧跟在等值条件之后，方向一致。

上面的慢 SQL，正确索引就是 (user_id, status, created_at)：等值定位 + 范围过滤 + 天然有序，配合 LIMIT 20，扫描行数从百万级降到 20 行。

三、索引失效的六大常见姿势

1. 对索引列做运算或函数：WHERE YEAR(created_at) = 2026 → 改写成范围 created_at >= '2026-01-01' AND created_at < '2027-01-01'；
2. 隐式类型转换：字符串列用数字比较 WHERE phone = 13800138000 → 加引号；
3. 前导模糊匹配：LIKE '%abc' 无法走 B+ 树，LIKE 'abc%' 可以；后缀匹配考虑反转存储或全文索引；
4. OR 两侧有一侧无索引：考虑用 UNION 拆分；
5. 不满足最左前缀：见上文；
6. 优化器判断回表代价过高：结果集占比超过 20%~30% 时，全表扫描反而更快，这是"明明有索引却不走"的真相。

四、回表、覆盖索引与延迟关联

回表：二级索引叶子节点存的是主键，查出主键后还要回聚簇索引取整行。SELECT * 是回表大户。

覆盖索引：查询的列全部包含在索引里，免回表：

-- 索引 (user_id, status, created_at)
SELECT id, user_id, status, created_at FROM orders
WHERE user_id = 1001 AND status = 'paid';   -- Extra: Using index ✅

深分页优化：LIMIT 1000000, 20 会扫过前 100 万行再丢弃。用延迟关联：

SELECT o.* FROM orders o
JOIN (SELECT id FROM orders WHERE user_id = 1001
      ORDER BY created_at DESC LIMIT 1000000, 20) t
ON o.id = t.id;

子查询走覆盖索引只取主键，最后一刻才回表取 20 行，耗时常常从数秒降到几十毫秒。

五、索引不是越多越好

• 每个索引都是一棵要维护的 B+ 树：写入变慢、占磁盘、优化器选错概率上升；
• 区分度低的列不要单独建索引（如 status 只有 3 个值），放进联合索引作前导等值条件才有意义；
• 唯一索引在写入时多一次冲突检查，能用业务保证唯一性的场景权衡后再加；
• 定期清理从未被使用的索引（sys.schema_unused_indexes）。

总结

慢查询排查的标准动作：

1. EXPLAIN 看 type / key / rows / Extra；
2. 按"等值在前、范围在后、排序跟随"设计联合索引；
3. 用覆盖索引消灭回表，用延迟关联优化深分页；
4. 改完再 EXPLAIN 对比 rows 和耗时，用数据说话。

记住核心心智模型：索引就是排好序的数据结构，你的 SQL 越符合"有序"的直觉，它就越快。