线上系统出现一条频繁慢查询，接口响应从 200ms 涨到 3s。本文以这条真实问题为例，讲清楚索引优化的完整思路：先定位慢查询，再理解索引结构，最后设计出符合查询模式的索引。

一、先定位慢查询

MySQL 提供了慢查询日志，开启后可以把执行时间超过阈值的 SQL 记录到文件中：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;   -- 超过 1 秒的记录

也可以直接查看当前正在执行的慢查询：

SHOW PROCESSLIST;

拿到问题 SQL 后，第一步是用 EXPLAIN 分析执行计划，重点看 type、key、rows 这几列：

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

如果 type 是 ALL（全表扫描）、rows 又很大，说明索引没有生效，这就是优化对象。

二、理解索引为什么有效

MySQL InnoDB 默认使用 B+ 树索引。B+ 树把数据按排序组织，查询时可以从根节点一路二分定位到叶子节点，复杂度是 O(log n)，而不是全表逐行扫描的 O(n)。

关键能力是：索引本身是有序的。它不仅能加速 WHERE 过滤，还能加速 ORDER BY 和 GROUP BY，因为排序已经在索引里完成了。

三、最左前缀原则

复合索引 (a, b, c) 等价于按 a、ab、abc 三个顺序建立索引。查询只能用上「从最左列开始连续的一组列」：

  • 命中 WHERE a = ? ✅
  • 命中 WHERE a = ? AND b = ? ✅
  • 命中 WHERE b = ? ❌ 无法使用该索引

所以建复合索引时，把「等值条件」的列放在前面，把「范围条件」的列放在后面，这是最常用的经验。

四、针对问题 SQL 设计索引

回到这条查询：

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

user_id 是等值条件，status 是等值条件，created_at 用于排序。设计索引：

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at DESC);

这样一次性让过滤、排序都走索引，避免 MySQL 先查出大量记录再在内存里 filesort。优化后执行计划变为 type=ref、rows 大幅下降，查询耗时从 3s 降到 30ms 以内。

五、几个常见的坑

  1. 函数套列了：WHERE DATE(created_at) = '2026-07-01' 会让索引失效，应改成 created_at >= ... AND created_at < ... 的范围写法。
  2. 隐式类型转换：字符串列与数字比较会触发类型转换，索引失效。
  3. 前缀模糊：LIKE '%abc' 无法使用索引，只有 'abc%' 可以。
  4. 索引冗余：已有 (a, b) 索引时，再建 (a) 索引是多余的，会浪费写入开销。

小结

索引优化不是靠猜，而是先看执行计划，再根据查询的等值、范围、排序条件设计复合索引。理解 B+ 树和最左前缀原则是基础，配合慢查询日志和 EXPLAIN，就能把绝大多数慢查询优化回来。