一、为什么单列索引加满还是慢

在建站初期，索引的正确用法通常被总结成一句话：「WHERE 里用到的字段都建索引」。于是很多人的做法是给每个查询字段都加一个单列索引，看着 SHOW INDEX 输出很长，心里踏实。等到文章数量过万、评论过十万，会发现一件怪事：每个字段都有索引，但那条查询还是慢，而且 EXPLAIN 显示只用上了其中一个。

原因是 MySQL 在一条查询里，对同一个表通常只会选择用一个索引（除索引合并 index merge 这个特例，而它往往比不用还慢）。假设表结构是这样的：

CREATE TABLE posts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED NOT NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  views INT UNSIGNED NOT NULL DEFAULT 0,
  title VARCHAR(200) NOT NULL,
  KEY(category_id), KEY(status), KEY(created_at), KEY(views)
);

查询是「某个分类下已发布的文章，按时间倒序取 20 条」：

SELECT id, title, created_at FROM posts
WHERE category_id = 12 AND status = 1
ORDER BY created_at DESC LIMIT 20;

执行计划会告诉你：走 category_id 索引找到这个分类下的所有行（可能是几千行），然后逐行回表检查 status，再把结果全部取出来做一次 filesort 排序，最后才 LIMIT 出 20 条。索引只筛掉了「分类」这一维，剩下的过滤和排序都在内存里做。数据量越大越慢，而且慢的方式很讨厌——rows 只有几千，看起来「不该慢」，但 Extra 里的 Using filesort 暴露了真相。

换成复合索引后，同一个查询的行为完全不同：

ALTER TABLE posts ADD KEY idx_cat_status_time (category_id, status, created_at, id);

有了这个索引，MySQL 可以直接定位到 category_id=12 AND status=1 这一段，而这一段内部的数据天然就是按 created_at 排好序的（因为索引是按列顺序排列的），于是它从后往前读 20 条就结束，既不需要 filesort，也不需要扫描全部匹配行。这就是复合索引的核心价值：它同时解决了过滤和排序两件事。

二、最左前缀：一条不能违反但经常被误解的规则

复合索引 (a, b, c) 等价于三个索引：(a)、(a,b)、(a,b,c)。它不能被当成 (b) 或 (b,c) 使用。这条规则大家都背过，但误解通常出现在三个地方。

误解一：以为「查询里出现了 a 就能用索引」。实际上要能用上 b 这一列，a 必须是等值条件。如果查询是 WHERE a > 100 AND b = 5，那 a 是范围条件，索引走到 a > 100 之后，b 在每一段 a 值内部虽然有序，但整体上不连续，MySQL 无法再用 b 做定位，只能把 a > 100 的所有行取出来逐行过滤 b。这个现象叫做「范围条件截断后续列」。

误解二：以为 IN 也算范围。WHERE a IN (1,2,3) AND b = 5 里，a 有多个取值，某些资料会说这也是「范围」会截断。实际在 MySQL 5.6+ 里，IN 会被当作多个等值查询分别探测（index condition pushdown 与 range 优化处理），b 仍然能被利用。不过当 IN 里的值很多（几十上百个）时，优化器可能失去耐心改成全表扫描。经验值是：IN 的取值个数控制在 10 个以内，索引路径稳定。

误解三：以为顺序完全不能调。最左前缀说的是「使用规则」，不是「建索引时的物理顺序」。所以完全可以把 status 放在第一位：(status, category_id, created_at)。这种顺序在「status 取值很少」的场景下反而更优，下面细讲。

三、列顺序的三条判据

排顺序不是玄学，按这三条判据依次判断，绝大多数情况都有唯一答案。

判据一：等值条件的列放前面，范围条件的列放后面。这是最硬的一条。因为范围条件会截断后续列，所以只要把范围列放在中间，它后面的列就全废了。上面那条查询里三类条件都有：category_id=（等值）、status=（等值）、created_at（排序，等价于范围）。所以正确顺序是把两个等值列放前面，排序列放最后。

判据二：多个等值列之间，基数（区分度）高的放前面——但这条要让位于排序需求。「基数高的放前面」是老规则，它的道理是能更快地把候选集缩小。但在复合索引里，等值列之间的顺序其实不影响索引定位效率：因为都是等值，MySQL 会把所有等值列的值作为整体去定位，顺序只影响「前缀能覆盖多少其他查询」。真正决定顺序的是排序需求——如果 ORDER BY 里有一列，那它必须紧跟在所有等值列之后、范围列之前。

所以在 category_id=12 AND status=1 ORDER BY created_at DESC 里，created_at 必须排第三。如果写成 (category_id, created_at, status)，那么等值列 status 被 created_at（排序）隔开，索引无法保证「同分类同状态下的时间有序」，filesort 又回来了。

判据三：区分度极高的列不要单独放最前，除非它就是查询入口。比如 user_id 基数很大，status 只有三个值。如果查询总是「某个状态下的所有文章」，那么 (status, created_at) 是对的；如果查询总是「某个用户的所有文章」，那 (user_id, created_at) 是对的。同一个表的不同查询需要不同索引，这是正常的，不要试图用一个大索引服务所有查询。

-- 场景 A：后台列表「按状态筛选，按时间倒序」
SELECT id, title FROM posts WHERE status = 1 ORDER BY created_at DESC LIMIT 20;
-- 索引：(status, created_at)

-- 场景 B：前台列表「按分类筛选，按时间倒序」
SELECT id, title FROM posts WHERE category_id = 12 ORDER BY created_at DESC LIMIT 20;
-- 索引：(category_id, created_at)

-- 场景 C：两个条件同时有
SELECT id, title FROM posts WHERE category_id = 12 AND status = 1 ORDER BY created_at DESC LIMIT 20;
-- 索引：(category_id, status, created_at)

这里有一个特别容易搞错的地方：场景 C 的索引不能替代场景 B 的索引。因为 (category_id, status, created_at) 里 category_id 之后的下一列是 status，跳过它直接用 created_at 就断掉了连续性，索引无法保证排序。这是被「最左前缀」这四个字误导最典型的地方：跳过中间列，不等于用上了后面的列。

所以判断一个复合索引能服务哪些查询，方法是「从第一列开始，一列一列往下数，遇到范围条件或排序就停」。数到第几列，这个索引就只对这个前缀有效。只有严格按这个前缀来的查询才能受益，任何中间断开都会退化。

四、覆盖索引：把回表这件事省掉

复合索引的第二个价值是「覆盖索引」——如果查询需要的所有列都在索引里，MySQL 就不需要回表读数据行，直接从索引里取数据返回。这在 InnoDB 上收益非常大，因为 InnoDB 的二级索引叶子节点存的是主键值，取非索引列必须再按主键去聚簇索引里查一次，也就是每行多一次随机 IO。

判断是否覆盖，看 EXPLAIN 的 Extra 列有没有 Using index（注意：是 Using index，不是 Using where; Using index 里的那个意思——两者都表示覆盖，但有 where 说明还有过滤在服务层做，属于半覆盖，效率略低）。

-- 想覆盖这条查询：SELECT id, title, created_at ...
-- 索引要把 title 也带上
ALTER TABLE posts ADD KEY idx_cat_status_time (category_id, status, created_at, title);
-- 或者更省空间的做法：只带 id 和 created_at，title 回表取（1 次随机 IO vs 索引膨胀）

这里有个取舍值得说清楚。把 title 塞进索引确实能让查询完全覆盖，但 VARCHAR(200) 会让索引体积暴涨，写入成本、缓存命中率都会受影响。对「列表页取 20 条」这种场景，索引带 id 就够了（id 本来就在二级索引的叶子节点里），回表 20 次是可以接受的；只有在「一次要读几万行做统计」的场景，才值得用覆盖索引把所有列都带上。

还有一个反直觉的点：SELECT * 永远不可能覆盖索引（除非表只有索引里的列），所以「减少 SELECT *」这件事的价值不只是「少传数据」，还能让覆盖索引生效。这条在优化时经常被忽略。

五、索引下推：让被截断的列还有一次机会

前面说范围条件会截断后续列，但实际上 MySQL 5.6 引入了 index condition pushdown（ICP），给被截断的列留了半条活路。它的做法是把 WHERE 里这些「用不上索引定位」的条件下推到存储引擎层，在遍历索引的过程中顺便判断，避免每一行都回表。

-- 假设索引 (category_id, created_at, views)
SELECT id, title FROM posts
WHERE category_id = 12 AND created_at > '2026-01-01' AND views > 100;

在这个查询里，created_at 是范围条件，views 用不上索引定位；但有了 ICP，views > 100 会在扫描索引时判断，只对满足条件的行回表。所以「范围条件截断后续列」并不等于「后续列完全无用」——它只是不能用来做定位，但仍可以用来做过滤。

ICP 默认开启，用 SET optimizer_switch 可查。看 EXPLAIN 的 Extra 列有 Using index condition 就是生效了。它不能替代正确的列顺序——它省的是回表，不是扫描量。索引定位能筛到 100 行，和只能定位到 100 万行再逐行过滤，差距是几个数量级。

六、五个常见错误

错误一：给每个字段都建单列索引，指望它们被合并使用。index merge 确实存在，但它要求多个索引各自选择性都不错，且优化器认为合并比单用一个更划算。实践中它经常不如一个正确的复合索引，甚至不如全表扫描。看到 Extra 里出现 Using union(...) / Using intersect(...)，基本可以直接判断「这里缺一个复合索引」。

错误二：把低基数列放最前面当「万能索引」。(status, ...) 里 status 只有 3 个值，索引树第一层就 3 个分支，每个分支下挂几万行。这种索引如果查询不带 status 等值条件，是完全没有用的；即使带了，也只是把范围缩小到「三分之一」，还不如直接从高基数列入手。低基数列要放在最前面，前提是它总是出现在查询里。

错误三：索引过多导致写入变慢。每个索引在 INSERT / UPDATE / DELETE 时都要同步维护。10 个索引的表，一次写入要改 11 处 B+ 树。个人站的文章表通常写少读多，5 个以内的索引是合理范围；评论表、日志表这种写多的表，要多想一想每个索引是不是真有必要。

错误四：以为 ORDER BY 的类型不匹配也能用索引。ORDER BY created_at 如果 created_at 是 DATETIME 而查询参数是字符串，MySQL 会做隐式转换，索引排序失效。同理，ORDER BY a DESC, b ASC 这种混排需要索引方向也匹配（MySQL 8.0 支持降序索引，5.7 不支持，会用 filesort）。

错误五：改了索引但没验证真实执行计划。小数据量时 MySQL 会倾向于全表扫描（因为回表成本 vs 全扫成本差不多），所以你在测试库上看到的执行计划和线上完全不同。验证必须在接近真实数据量的环境里做，或者用 EXPLAIN 加上 FORCE INDEX 对比两条路径的实际耗时。

七、验证：怎么确认索引真的有用

-- 1) 看执行计划：重点看 key、rows、Extra
EXPLAIN SELECT id, title FROM posts
WHERE category_id = 12 AND status = 1
ORDER BY created_at DESC LIMIT 20;

-- 2) 8.0 可以直接看实际耗时
EXPLAIN ANALYZE SELECT ...;

-- 3) 看索引统计：基数是否符合预期
SHOW INDEX FROM posts;
-- Cardinality 是估算值，ANALYZE TABLE 可刷新

-- 4) 看索引被用了多少次（5.7+）
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

sys.schema_redundant_indexes 这个视图非常值得跑一次：它会告诉你哪些索引是「另一个索引的前缀」，也就是冗余的。比如有了 (category_id, status, created_at) 之后，(category_id) 就是冗余的，可以删。索引少了，写入更快、缓冲池里能装更多有效索引页，是一举两得的事。

验证时要注意一个陷阱：rows 是估算值，不是实际扫描行数。如果 rows 显示 100 但查询还是慢，可能是估算错了（统计信息陈旧），也可能是回表次数太多（覆盖索引能救）。真正的判据是 Handler_read_* 系列状态变量，或者 8.0 的 EXPLAIN ANALYZE。

八、小结

复合索引的列顺序只有一条铁律：等值在前，排序居中，范围在后。其他所有规则（高基数优先、低基数优先、覆盖索引）都是在具体场景下的权衡，不是普适原则。

然后记住两个判断动作：看 Extra 里有没有 Using filesort（有就是排序没吃到索引），看 key 是不是你期望的那个索引（不是就说明优化器选了别的路径，要查 rows 估算是否偏差）。这两个动作比背任何参数都实用。

最后一句实在话：不要试图为一个表设计一个「完美索引」。索引是为查询服务的，脱离你的十条高频 SQL 去谈索引设计，都是纸上谈兵。先把这些 SQL 抓出来（performance_schema 或用慢查询日志按总耗时排序），再逐个看它们的 WHERE 与 ORDER BY，索引自然就出来了。