MySQL 联合索引怎样支持混合升降序排序

我在给列表查询补索引时，最容易先想到的是“排序要倒序，就在 ORDER BY 后面写 DESC”。真正决定联合索引能不能直接提供顺序的，是每个键段的方向、列的排列和前面的过滤条件是否与查询形状一致。MySQL 支持在同一个联合索引里混合写 ASC 与 DESC，因此可以把“先按租户升序、再按时间倒序”这样的排序契约直接表达出来。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/descending-indexes.html

先给结论：优先把等值过滤列放在联合索引前面，再按 ORDER BY 的列顺序写出方向；建好后用 EXPLAIN 看 key、Extra 和树形计划里的扫描方向。看到 reverse 不等于索引失效，真正要继续追的是是否出现 Using filesort，以及扫描行数是否符合预期。

先把查询写成一份排序契约

假设订单列表固定先按 tenant_id 升序，再按创建时间倒序，同一时间用 id 升序稳定收尾。这个需求不能只写成“有三个字段”，而要明确每个字段扮演的角色：租户用于分组，时间用于主要排序，id 用来消除同一时间的并列。

-- 这条查询先按租户分组，再在每个租户内按时间倒序、编号升序排列
SELECT id, tenant_id, created_at, total_amount
FROM orders
WHERE tenant_id IN (10, 20)
ORDER BY tenant_id ASC, created_at DESC, id ASC
LIMIT 50;

排序方向是查询契约的一部分。把 id 作为稳定的最后键段，能减少翻页或重复刷新时同一批记录顺序抖动的问题；它不是为了让索引看起来更复杂，而是为了让排序结果有可解释的收尾规则。

用 ASC 和 DESC 定义混合方向索引

MySQL 8.4 文档说明，降序索引会按降序保存键值，混合方向的多列索引可以支持不同列方向的排序。对应上面的查询，可以先尝试下面这份 InnoDB BTREE 联合索引：

-- 键段顺序与 ORDER BY 一致，第二列倒序，第三列升序
CREATE INDEX idx_orders_tenant_created_id
ON orders (tenant_id ASC, created_at DESC, id ASC);

-- 过滤和排序都沿用同一组列，便于观察索引是否提供了顺序
SELECT id, tenant_id, created_at, total_amount
FROM orders
WHERE tenant_id IN (10, 20)
ORDER BY tenant_id ASC, created_at DESC, id ASC
LIMIT 50;

不要把“索引包含这些列”理解成“任何排序都能直接使用它”。联合索引首先受左前缀约束，其次还要看过滤条件是否把前面的键段固定住，最后才是排序方向能否连续匹配。比如查询把 tenant_id 固定为一个值时，后面的 created_at DESC, id ASC 就更容易成为可用的排序段。

左前缀和范围条件决定后续排序能否接上

在实际项目里，最容易误判的是“索引里已经有排序列，为什么还是排序”。一个简化判断是：等值条件通常能让后续键段继续参与定位或排序；一旦在排序列之前出现范围条件，后面的键段就不能只凭字段名判断，还要结合具体计划。

-- 等值租户下，时间和 id 仍然是同一份排序契约的一部分
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 50;

-- 先出现时间范围时，后续 id 是否继续承担排序要交给 EXPLAIN 判断
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 10
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC, id ASC
LIMIT 50;

这两条语句都可以使用相同的索引候选，但第二条有时间范围，不能仅凭“联合索引顺序正确”就承诺没有额外排序。还要留意查询是否对排序列套了函数、是否跨表排序、是否混入索引之外的表达式，以及返回列是否让回表成本变得更高。

用 EXPLAIN 区分正向扫描、反向扫描和 filesort

我通常把执行计划拆成三个问题：优化器选了哪个索引？扫描方向是什么？有没有额外排序？普通格式先看 key 和 Extra，需要进一步确认时再看 FORMAT=TREE。

-- 先确认实际使用的索引，以及 Extra 是否出现 Using filesort
EXPLAIN
SELECT id, tenant_id, created_at, total_amount
FROM orders
WHERE tenant_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 50;

-- 树形计划可辅助观察索引名后的 reverse 线索
EXPLAIN FORMAT=TREE
SELECT id, tenant_id, created_at, total_amount
FROM orders
WHERE tenant_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 50;

如果 key 是预期的联合索引，说明优化器至少把它列入了访问路径；如果 Extra 仍然有 Using filesort，说明结果还需要额外排序。树形输出里，索引名后出现 (reverse) 表示反向索引扫描，它和“没有使用索引”不是一回事。反向扫描是否值得接受，要结合扫描量、返回行数和查询延迟来判断。

上线前别忘了存储引擎和维护代价

降序键段不是所有索引类型都适用。MySQL 8.4 文档将降序索引能力限定在 InnoDB 的 BTREE 场景，并列出了 HASH、FULLTEXT、SPATIAL 等边界；带降序键段的索引也不能拿来做没有 GROUP BY 的 MIN()/MAX() 优化。设计时应把这些限制和业务表的写入特征一起考虑。

-- 用 SHOW INDEX 查看每个键段的顺序标记，A 表示升序，D 表示降序
SHOW INDEX FROM orders;

-- 修改前先保留真实查询形状，避免为了一个排序方向复制过多索引
EXPLAIN FORMAT=TREE
SELECT id, created_at, total_amount
FROM orders
WHERE tenant_id = 10
ORDER BY created_at DESC, id ASC
LIMIT 50;

SHOW INDEX 的 Collation 列可以帮助核对索引键段的方向。另一方面，每新增一个索引都要承担存储空间、写入维护和统计信息成本，尤其是订单、日志这类写入频繁的表。不要因为查询带了 DESC 就盲目复制一份索引，应先确认查询模式确实稳定、收益足以覆盖维护代价。

我的实际落地清单

1. 先写出完整的 WHERE 和 ORDER BY，标记每一列是等值过滤、范围过滤还是排序键。
2. 把高确定性的等值过滤列放在前面，再按排序列顺序写出 ASC 或 DESC。
3. 给时间排序补一个稳定收尾键，避免同一时间值导致分页顺序不稳定。
4. 确认表使用 InnoDB，索引类型为 BTREE，并检查是否触碰 HASH、FULLTEXT、SPATIAL 等限制。
5. 用 EXPLAIN 看 key、Extra 和树形计划，不把 reverse 误判成失效。
6. 若仍出现 Using filesort，回头检查范围条件、函数表达式、跨表排序和未覆盖的排序列。
7. 最后按真实读写比例评估新增索引的空间、写入和统计信息维护成本。

一句话总结：混合升降序排序不是单独给 ORDER BY 加关键字，而是让查询的过滤列、排序列和方向在联合索引里形成一致契约，再用 EXPLAIN 验证优化器是否真的按这份契约执行。

常见问题

联合索引里的 ASC 可以省略吗？

可以，未写方向时默认按升序理解；但在需要表达混合方向时，显式写出每个键段的方向更容易核对设计意图。

看到 reverse 就一定要重建索引吗？

不一定。reverse 说明优化器采用了反向扫描，仍可能利用索引提供顺序。是否需要调整，要看是否出现 filesort、扫描行数和实际延迟。

为什么索引包含排序列，EXPLAIN 仍然显示 filesort？

常见原因包括左前缀没有匹配、前面出现范围条件、排序列被函数包裹、跨表排序或排序表达式超出索引能提供的顺序。应按执行计划逐项排查。