如何通过创建组合索引优化MySQL大表OrderBy排序，避免Using Filesort？

本文共计1118个文字，预计阅读时间需要5分钟。

直接说结论：

为什么加索引能消除 Using filesort

MySQL 的 B+ 树索引天然有序。当 ORDER BY 的字段顺序和索引定义完全一致（包括方向），优化器就能直接按索引叶子节点顺序扫描，跳过内存或磁盘排序步骤——此时 EXPLAIN 的 Extra 字段会显示 Using index，而不是 Using filesort。

常见错误现象：

• 只给单个排序字段建索引，但 ORDER BY a, b 用了两个字段 → 仍触发 Using filesort
• 索引是 (a, b)，但查询写成 ORDER BY b, a → 不满足最左前缀，无法利用
• 索引是 (a ASC, b ASC)，但查询写成 ORDER BY a DESC, b ASC → 方向不一致，部分版本（如 MySQL 8.0+）可能仍用反向扫描，但低版本或复杂条件会退化

联合索引字段顺序怎么定

核心原则：**ORDER BY 字段从左到右，必须严格匹配索引定义的前缀**。

比如查询是：

SELECT id, age, phone FROM tb_user ORDER BY age, phone;

那就必须建索引：

CREATE INDEX idx_user_age_phone ON tb_user(age, phone);

不能反过来建 (phone, age)，也不能漏掉 age 只建 (phone)。

如果还有 WHERE 条件，优先把过滤字段放前面，再接排序字段。例如：

SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age;

推荐索引：idx_name_age（name 在前，age 在后），这样既能高效过滤，又能覆盖排序。

要不要把 SELECT 列也加进索引里

这是性能关键点，不是“要不要”，而是“值不值得”。

如果索引包含所有 SELECT 字段（即构成“覆盖索引”），MySQL 就不用回表查聚簇索引，能省一次随机 IO。比如：

SELECT id, age, phone FROM tb_user ORDER BY age, phone;

索引 idx_user_age_phone 已含 age 和 phone，但没含 id（主键自动包含在二级索引中，所以实际已覆盖）。这种情况下，Using index 就是真正意义上的“索引覆盖 + 索引排序”。

但如果查询是：

SELECT * FROM tb_user ORDER BY age;

而你只建了 idx_age，那即使排序走索引，仍要回主键索引捞所有字段——IO 成本没降多少，只是省了排序本身。

所以建议：

• 明确常用查询的字段集，把高频 SELECT 列（尤其是非主键列）一并加入联合索引末尾
• 避免无脑把所有字段都塞进去，索引越宽，写入开销越大，缓存效率越低
• 用 SHOW INDEX FROM tb_user 看索引长度，单个索引总长别超过 3072 字节（InnoDB 默认限制）

升序/降序混合时容易踩的坑

MySQL 5.7 及更早版本，**联合索引中所有字段必须同向（全 ASC 或全 DESC）才能支持 ORDER BY 混合方向**；MySQL 8.0+ 支持混合方向索引，但需显式声明。

比如你要支持：

ORDER BY age DESC, phone ASC

在 MySQL 8.0+ 中，必须建：

CREATE INDEX idx_user_age_desc_phone_asc ON tb_user(age DESC, phone ASC);

如果只建 (age, phone)，即使执行计划显示 Using index，也可能实际是 Using index; Backward index scan（仅适用于全 DESC 场景），对混合方向无效。

容易被忽略的地方：

• EXPLAIN 不报错，也不提示“没用上”，但 Extra 里没有 Using index → 实际走了 filesort
• 索引建了，但查询里 ORDER BY 字段顺序或大小写和索引定义稍有出入（比如字段别名、表达式包装），也会导致失效
• 字符集或排序规则（collation）不一致时，索引可能无法用于排序，哪怕字段名和顺序都对