MySQL 混合方向排序：降序索引与工单优先级列表实验

工单列表常有这种排序：高优先级在前，同优先级里等待最久的在前。对应 SQL 是 priority DESC, created_at ASC。给两列都建升序索引，再让数据库“反着读”，不能同时得到这两个方向。

反向扫描会翻转整个后缀的顺序，不会只翻转第一列。MySQL 8.4 的 InnoDB 可以真正存储降序键，混合方向索引因此有了实际意义。它解决的是排序顺序是否匹配，不是给每个 DESC 都加一棵索引。

把需求写成一条确定的顺序

工单表先按项目隔离，然后按优先级降序、创建时间升序、主键升序输出。最后一列用于打破同时间的并列，避免列表顺序漂移。

以下实验使用独立 MySQL 8.4 库，字段、索引和后续查询保持一致。

CREATE DATABASE IF NOT EXISTS mixed_order_lab
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE mixed_order_lab;

CREATE TABLE support_ticket (
    id BIGINT NOT NULL,
    project_id INT NOT NULL,
    priority SMALLINT NOT NULL,
    created_at DATETIME(6) NOT NULL,
    subject VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_all_asc
        (project_id ASC, priority ASC, created_at ASC, id ASC)
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 6000;
INSERT INTO support_ticket
    (id, project_id, priority, created_at, subject)
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT n, MOD(n, 5), MOD(n, 4),
       TIMESTAMP('2026-01-01 00:00:00')
           + INTERVAL MOD(n, 7) DAY
           + INTERVAL MOD(n, 60) SECOND,
       CONCAT('ticket-', n)
FROM seq;

ANALYZE TABLE support_ticket;

SELECT id, priority, created_at
FROM support_ticket
WHERE project_id = 3
ORDER BY priority DESC, created_at ASC, id ASC
LIMIT 10;

数据里故意保留了相同优先级、相同创建时刻的工单。少量没有并列的数据，很容易掩盖排序定义不完整的问题。

把同一个项目的数据抽出来看，全升序索引中的有效顺序是：优先级从低到高，每组内部时间从早到晚。反着读取则变为优先级从高到低，每组内部时间也从晚到早。后者已经背离“等待最久的优先”这条规则。

用计划核对，不靠索引名称猜

EXPLAIN
SELECT id, priority, created_at
FROM support_ticket FORCE INDEX (idx_all_asc)
WHERE project_id = 3
ORDER BY priority DESC, created_at ASC, id ASC
LIMIT 10;

这里使用 FORCE INDEX 是为了把演示限制在这一条访问路径上。需要观察 Extra 是否出现 Using filesort，也可以用 EXPLAIN FORMAT=TREE 查看排序节点。实际业务 SQL 的成本判断应再用不带提示的版本核对，不能把诊断用的强制索引原样当上线方案。

filesort 这个名字容易吓人，它表示需要额外排序，不等于每次都把结果写到磁盘。小结果集的内存排序可能很便宜；查询返回范围、记录宽度和并发量，才决定这次排序是否值得专门优化。

现在增加与业务顺序一致的索引：

CREATE INDEX idx_priority_wait
ON support_ticket
    (project_id ASC, priority DESC, created_at ASC, id ASC);

ANALYZE TABLE support_ticket;

EXPLAIN
SELECT id, priority, created_at
FROM support_ticket FORCE INDEX (idx_priority_wait)
WHERE project_id = 3
ORDER BY priority DESC, created_at ASC, id ASC
LIMIT 10;

SHOW INDEX FROM support_ticket;

固定 project_id = 3 后，索引后缀能够按所需方向连续读取。检查 SHOW INDEX 的列次序和方向，再核对计划里的排序节点，证据比“索引名称里写了 desc”可靠。

本例选择的列都包含在这个索引中，避免把回表成本混进第一次对照。真实页面若还要取 subject 或更大的正文，应该另跑相应查询。优化器可能为了读取成本改选其他路径，索引具备排序能力与优化器一定使用它，不是一回事。

这个索引也能反着读，但方向必须整体反转

EXPLAIN
SELECT id, priority, created_at
FROM support_ticket FORCE INDEX (idx_priority_wait)
WHERE project_id = 3
ORDER BY priority ASC, created_at DESC, id DESC
LIMIT 10;

它是后缀顺序的整体反向，因此可以观察反向索引扫描。与之相对，下面这个需求只反转了时间，没有相应反转另外两列：

EXPLAIN
SELECT id, priority, created_at
FROM support_ticket FORCE INDEX (idx_priority_wait)
WHERE project_id = 3
ORDER BY priority DESC, created_at DESC, id ASC
LIMIT 10;

不要因为三列名称完全相同，就认为同一索引必然免排序。顺序定义包括列顺序和方向，两项都要匹配。

项目筛选也有边界。project_id = 3 固定一个值，与 project_id IN (3, 4) 需要把两个项目放进一个全局列表，不是同一种读取过程。索引按项目分段保存，跨段结果未必已经按全局优先级和时间排列。执行计划有排序并不奇怪，问题在于需求是否真的需要跨项目混排。

下一页游标的比较方向也必须跟着变

若列表采用游标翻页，不能直接套用全降序列表的“小于上一页最后一个时间”。设上一页最后一条记录为优先级 2、时间 2026-01-04 00:00:10、主键 3000：

SET @last_priority = 2;
SET @last_created = '2026-01-04 00:00:10.000000';
SET @last_id = 3000;

SELECT id, priority, created_at
FROM support_ticket
WHERE project_id = 3
  AND (
      priority < @last_priority
      OR (
          priority = @last_priority
          AND (
              created_at > @last_created
              OR (created_at = @last_created AND id > @last_id)
          )
      )
  )
ORDER BY priority DESC, created_at ASC, id ASC
LIMIT 10;

这是按业务顺序展开的边界条件：较低优先级在后；同优先级里更晚时间在后；同时间里更大主键在后。它描述的是顺序关系，不承诺在任何数据规模下都形成同样理想的访问计划，仍要检查实际读取行数。

如果翻页期间工单优先级会变化，记录可能跨越游标边界。降序索引解决不了可变排序字段的分页一致性，需要由页面接受短暂移动，或用快照、固定批次等协议约束读取范围。

最后不要把所有索引都改成混合方向。每增加一棵索引，就增加存储和写入维护；含降序键的 InnoDB 二级索引还存在 change buffering 支持方面的限制，实际影响要结合实例设置判断。只为一个很少执行、只排序几十行的后台页面增加索引，未必划算。

实验清理：

DROP DATABASE mixed_order_lab;

设计这类索引时，先把“谁在前、谁在后、并列怎么办”写清楚。列名对上只是开始，方向对上才构成同一条顺序。

排序优化要和过滤范围一起评估

假设页面又增加了“只看未关闭工单”的条件，而索引里没有这个状态列。数据库可能按正确顺序读了许多已经关闭的记录，才凑够十条返回值。计划没有额外排序，扫描工作却仍然很大。排序节点消失只是一个局部结果，不是整个查询已经便宜的证明。

这时该讨论的是访问协议：状态是否稳定、过滤比例有多高、查询能否限定时间范围、增加状态前缀会影响哪些其他请求。不能看到过滤条件就机械地把所有列塞进同一棵联合索引。索引宽度和写入负担会随之增加，不同筛选组合也未必共用一条理想路径。

核验结果集时，至少保留同优先级、同时间的样本，再对照无提示查询与强制索引查询返回的有序主键序列。两条 SQL 都返回十条记录还不够，要确认它们返回的是同样的十条、同样的先后顺序。性能调整不能悄悄把业务优先级改掉。

对于偶尔跨项目的后台报表，接受一次范围可控的排序可能更简单。给高频单项目列表保留专用访问路径，让低频宽查询走另一种计划，常常比试图让一棵索引满足所有页面更容易维护。