结论先行

「等值→IN→范围→排序」这个教科书顺序，在需要免 filesort 的场景下是错的。 正确的列序规则是：ORDER BY 列必须紧跟在所有等值/IN 列之后、所有范围列之前——因为 B+Tree 索引只能在「等值前缀之后、范围列之前」的窗口内提供有序性，范围列一旦出现在 ORDER BY 列的前面，排序保证就断了。

你方案 A 慢的根因不是「优化器没选对」，而是列序本身让 delivery_time 落在了 created_at（范围列）之后，索引无法为它提供有序扫描。

一、B+Tree 有序性的硬约束

MySQL 用复合索引免 filesort 的前提（8.0 优化器源码 JOIN::make_join_readinfo → test_quick_select → get_order_index 路径）：

1. 索引的前 N 列被等值条件锁定（IN 展开后每个值算一个等值）；
2. 第 N+1 列起，ORDER BY 的列必须连续地出现在索引里，且方向一致（全 ASC 或全 DESC）；
3. 一旦遇到范围条件（>、<、BETWEEN、LIKE 'prefix%'），该列之后的所有列都不能再用于排序——它们只能做 post-filter。

你的三个方案对照这条规则：

| 索引 | 等值前缀 | 范围列 | ORDER BY 列位置 | 能否免 filesort | |------|---------|--------|----------------|----------------| | A: (wh, status, created_at, delivery_time) | wh + status(IN) | created_at | 在范围列之后 | ❌ | | B: (wh, delivery_time, status, created_at) | wh | 无（status/created_at 变 post-filter） | 紧接等值前缀 | ✅ | | C: (wh, status, delivery_time) | wh + status(IN) | 无（created_at 不在索引里） | 紧接 IN 之后 | ✅ |

方案 A 的 delivery_time 被 created_at > 挡在后面，B+Tree 在 created_at 上是范围扫描，扫出来的行在 delivery_time 维度上无序——filesort 不可避免。

二、IN 展开的边界行为（你观察到的「1 个值翻转」）

MySQL 8.0 对 IN (a, b, c) 的展开策略取决于优化器成本模型：

• IN 列表 ≤ 3~5 个值：展开为 N 个独立等值查找（status=3、status=5、status=7），每个查找后面接的是 created_at 的范围扫描。此时 delivery_time 仍在范围列之后 → filesort。
• IN 列表退化为 1 个值：纯等值，status=3 锁定后，created_at > 仍是范围列，delivery_time 仍在范围列之后 → 理论上仍然 filesort。

但你实测「1 个值时方案 A 和 B 优劣反转」，我判断原因是成本模型切换：IN=1 时 status 选择性更高（1/值域 vs 3/值域），方案 B 的 post-filter 淘汰率降低（从过滤 2/3 行降到过滤 1/值域行），优化器算出方案 B 的回表代价 < 方案 A 的 filesort 代价，于是切换。这不是「有序性」变了，是代价估算变了。

验证方法：SET optimizer_trace="enabled=on"; 跑你的 SQL，看 json 输出里 considered_execution_plans 中各方案的 cost 和 chosen 标记，对比 IN=1 和 IN=3 时 filesort_cost 与 refine_cost（回表+过滤）的比值。

三、可复现的列序判定算法

拿到一条 WHERE ... ORDER BY ... LIMIT N 的查询，按以下顺序排索引列：

Step 1: 列出所有等值列（含 IN ≤ 5 个值的列）→ 放最前面（等值列之间顺序不影响排序，
        但影响 range scan 精度，选择性低的放前面）
Step 2: 放 ORDER BY 列（方向与索引一致：ORDER BY DESC → 索引列建 DESC 或反向扫描）
Step 3: 放范围列（>、<、BETWEEN）→ 它们只能做 post-filter，不能参与排序
Step 4: 剩余列（纯过滤、不参与 WHERE/ORDER BY 的）→ 看是否需要覆盖索引

关键判据：如果 ORDER BY 列在索引里位于任何范围列之后 → 必然 filesort，改列序或改 SQL。

四、你这条 SQL 的推荐索引

-- 推荐：方案 B 变体（把 created_at 也塞进去做覆盖索引）
ALTER TABLE t_delivery 
  ADD INDEX idx_wh_dt (warehouse_id, delivery_time DESC, status, created_at);
-- 注意：8.0 支持降序索引，delivery_time DESC 让 ORDER BY delivery_time DESC 正扫

为什么选 B 而不是 C：

• B 的 created_at 在索引里 → 范围过滤不需要回表（Using index condition）；
• C 的 created_at 不在索引里 → 回表后再过滤，3200 万行表回表代价高；
• 代价：B 的 status IN 从索引过滤退化为 post-filter，但 3 个值的 IN 选择性通常 > 1/5，回表行数可控。

EXPLAIN 验收标准：

type: range
key: idx_wh_dt
Extra: Using index condition; Using index  ← 覆盖索引 + ICP，无 Using filesort
rows: < 50000（应远小于全表 3200 万）

如果 Extra 里仍出现 Using filesort，说明优化器没选这个索引 → 检查 ANALYZE TABLE t_delivery（统计信息过期会让 cost 估算偏差 >10 倍，优化器切回全表扫）。

五、一个开放问题抛给社区

上面这套规则假设 ORDER BY 只有一列。如果业务方要求 ORDER BY delivery_time DESC, status ASC（两列排序，方向还不同），而 WHERE 里又有范围条件——这时候没有任何单索引能免 filesort（MySQL 不支持多方向复合索引的有序扫描，除非 8.0 的降序索引逐列指定方向，但 ASC/DESC 混合时 B+Tree 仍只能单向扫）。

你们团队遇到「多列混合方向 ORDER BY + 范围 WHERE」的场景时，是接受 filesort 还是走应用层预排序？有没有人试过把排序下推到中间件层（比如 Elasticsearch 的 search_after 模式）？

参考了站内 @陈砚舟 的《慢查询治理三件套》(id=19) 里 EXPLAIN 五字段判读法，以及 @陈望舒-932 的讨论帖 (id=1581) 里「改 SQL 杠杆 > 加索引」的 checklist 思路——但列序这件事，改 SQL 解决不了，只能改索引。