结论先行
- IN 在 MySQL 8.0 优化器里不是「硬编码为等值」，也不是「永远为范围」——它是按 IN 值个数动态二分的：
IN (单值)→ 优化器内部会把它归约为等值，访问类型显示为ref，后续索引列可以用于 ORDER BY 免 filesort；IN (多值)→ 访问类型显示为range，IN 列被视作范围列，其后所有列都不能再用于排序，只能 post-filter。
- 这个判断可以直接在 EXPLAIN 里验证，不需要猜：看
type字段是ref还是range，或者EXPLAIN FORMAT=JSON里range_analysis.selected_ranges的数组长度。 - 可复现的列序规则（覆盖你 A/B/C 三种方案）：
- 想让 ORDER BY 列免 filesort → ORDER BY 列必须紧跟在所有等值列（含 IN(1)）之后、任何真范围列（>,<,BETWEEN,多值 IN,LIKE 'prefix%'）之前；
- 教科书「等值→IN→范围→排序」的顺序，在多值 IN 场景下必然让 ORDER BY 列落在范围列之后 → 必然 filesort；
- 教科书顺序在 IN(1) 场景下恰好成立（因为 IN(1) 被归约为等值），这就是你观察到「1 个值时方案 A 反而更快」的真正原因——不是代价模型切了，是 IN 列从「范围列」身份降级成了「等值列」身份，有序性约束松绑了。
- B 方案的免 filesort 是「硬收益」，A 方案的精确范围是「软收益」：在你 3200 万行 + 单 warehouse 约 200 万消息量级下，filesort 的代价（rows × log(rows) + 排序缓冲 IO）远大于 B 多读的 30% 行（ICP 会兜住大部分过滤），所以 B 应该稳赢，翻转只在 IN(1) 时出现。
一、IN 到底算等值还是范围：源码与 EXPLAIN 的可复现证据
MySQL 8.0 优化器在 sql_select.cc 的 make_join_readinfo 阶段会对 IN 谓词做归约：
- IN 列表只有 1 个值 → 转成
col = v的等值谓词，走ref访问； - IN 列表 ≥2 个值 → 保留为
col IN (...)，走range访问（多个等值区间合并成 range）。
验证方法（可直接抄）：
-- 1. 看访问类型（最直观）
EXPLAIN
SELECT tracking_no, carrier, delivery_time
FROM t_delivery
WHERE warehouse_id = 1024 AND status IN (3, 5, 7)
AND created_at > '2025-10-01'
ORDER BY delivery_time DESC LIMIT 50;
-- type 列：ref → IN 被当等值；range → IN 被当范围
-- 你的场景 IN(3) → type=range，IN(1) → type=ref
-- 2. 看 range 展开细节
EXPLAIN FORMAT=JSON
SELECT ... WHERE warehouse_id = 1024 AND status IN (3, 5, 7) ...;
-- 搜 "range_analysis" 节点里的 "selected_ranges" 数组长度：
-- IN(3) → 3 个 range（每个 range 内部 created_at 仍是范围扫描）
-- IN(1) → 退化为 ref，selected_ranges 不出现
-- 3. 看 ORDER BY 是否被索引吸收
-- EXPLAIN 的 Extra 列：
-- "Using index; Backward index scan" → ORDER BY 走索引，免 filesort
-- "Using filesort" → 没走
关键判据：EXPLAIN.type 是 ref 还是 range，就是你问的「IN 被当等值还是范围」的直接答案，不用去翻 optimizer_trace 的 cost 数字。
二、可复现的列序判定流程（覆盖你的 A/B/C）
拿到一条 WHERE ... ORDER BY ... LIMIT N，按下面的顺序排索引列：
Step 1: 列出所有「真等值」列（= 单值、IN 单值）→ 放最前面
（等值列之间的顺序不影响有序性，但影响 range scan 精度，
选择性低的放前面能让后续 range scan 更窄）
Step 2: 放 ORDER BY 列（方向与查询一致，8.0 用 DESC 索引避免 reverse scan）
Step 3: 放「真范围」列（>、<、BETWEEN、IN 多值、LIKE 'prefix%'）
—— 它们只能做 post-filter（或 ICP），不能参与排序
Step 4: 剩余列（纯过滤、不参与 WHERE/ORDER BY 的）→ 看是否需要覆盖索引
关键判据：如果 ORDER BY 列在索引里位于任何真范围列之后 → 必然 filesort，改列序或改 SQL。
对照你的三个方案：
| 索引 | 等值前缀 | 范围列 | ORDER BY 列位置 | IN(3) 能否免 filesort | IN(1) 能否免 filesort | |------|---------|--------|----------------|----------------------|----------------------| | A: (wh, status, created_at, dt) | wh | status(IN3) + created_at | dt 在范围之后 | ❌ | ✅（status 降级为等值） | | B: (wh, dt, status, created_at) | wh | status/created_at 变 post-filter | dt 紧邻等值前缀 | ✅ | ✅ | | C: (wh, status, dt) | wh | status(IN3) 是范围 | dt 在范围之后 | ❌ | ✅ |
你观察到的「IN(1) 时 A 反超 B」的根因：不是代价模型在两种方案之间切了，而是 A 的 status 列从「范围列」身份降级成了「等值列」身份，dt 落在等值前缀之后，免了 filesort。此时 A 的 range scan 精确度 > B（B 仍需 post-filter 掉 status 不匹配的 2/3 行），所以 A 赢。B 在 IN(3) 下赢，是因为 A 必然 filesort 而 B 不必——两个方案在不同 IN 值数量下各占上风，这是结构性的，不是估算抖动。
三、量化判据：什么时候 B 的免 filesort 收益 > A 的精确范围收益
给你一个可复现的成本模型（8.0 的 filesort 用 priority queue，cost 近似 rows × log2(rows)；精确范围扫描 cost 近似 rows × 回表代价）：
filesort_cost(B) ≈ rows_matched_by_index × log2(rows_matched_by_index)
range_scan_cost(A) ≈ rows_matched_by_precise_range × 回表代价
经验阈值（我按 3200 万行 + NVMe + buffer pool 命中 99% 的基线给的）：
rows_matched< 1 万：filesort 几乎免费（priority queue 只维护 LIMIT 50 个元素），A/B 差距 < 20%；rows_matched在 1 万 ~ 50 万：filesort cost 开始主导，B 领先 A 约 30~80%；rows_matched> 50 万：filesort cost 显著大于 ICP 过滤的额外行读，B 领先 A 超过 2×（你的 180 万行正落在这个区间，实测 A=420ms / B=310ms 也印证了这一点，但差距还没到 2× 是因为你的 status 选择性没那么差）。
从 INFORMATION_SCHEMA 拿数字的方法：
-- 1. 看 status 列的基数（估选择性）
SELECT TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX, CARDINALITY
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_NAME = 't_delivery'
AND INDEX_NAME LIKE 'idx_wh%';
-- 2. 看实际命中行数（最准）
EXPLAIN ANALYZE
SELECT tracking_no, carrier, delivery_time
FROM t_delivery
WHERE warehouse_id = 1024 AND status IN (3, 5, 7)
AND created_at > '2025-10-01'
ORDER BY delivery_time DESC LIMIT 50;
-- 关注 "rows_examined" vs "rows_returned" 的比值：
-- 比值 > 10 → post-filter 浪费大，B 的代价被低估了
-- 比值 < 3 → post-filter 浪费小，B 的免 filesort 是净赚
-- 3. 看 IN 多值下 range 展开的区间数
EXPLAIN FORMAT=JSON ...;
-- 搜 "selected_ranges" 数组长度 = IN 值个数（每个值一个 range）
判定式（建索引前拍板用）：
if (IN 值个数 == 1) {
// IN 列算等值，A 方案（教科书顺序）可行且更精确
pick(A);
} else if (rows_matched_by_precise_range > 100_000) {
// 范围列挡在 ORDER BY 前面 → 必然 filesort，B 方案免 filesort 净赚
pick(B);
} else {
// 行数小，filesort 几乎免费，选精确范围更稳
pick(A);
}
四、IN 值数量不稳定导致列序翻转的工程兜底
你提到「IN 值数量不稳定让我没法拍板」——这是真实痛点，给三个可落地的兜底：
- 应用层归一化：在 SQL 生成层把
IN (v1, v2, ..., vn)按 n 走不同代码路径：- n == 1 → 用
status = v1，索引走 A 方案； - n ≥ 2 → 用
IN (...)，索引走 B 方案； - 代价：两套 SQL 模板，但 EXPLAIN 可预测，P99 稳定。
- n == 1 → 用
- 双索引：A 和 B 各建一个，让优化器按 cost 选（8.0 优化器会自己挑更优的），代价是写放大 +1，查询侧无感知。
- 拆 IN 为多等值 UNION ALL：
IN (3,5,7)拆成 3 个status = v的 UNION ALL，每段都走 A 方案的精确 range scan，代价是 3 次索引扫描（但每段只扫 50 行就停），适合 IN 值 ≤ 5 的场景。
我推荐方案 1：IN 值数量是业务侧可控的，把它从「优化器的猜测对象」变成「代码的显式分支」，P99 曲线就平了。
五、一句话
IN 在 MySQL 8.0 优化器里是按值个数动态二分的：1 个值 = 等值、≥2 个值 = 范围。EXPLAIN 的 type 列（ref vs range）就是直接证据，不用翻 optimizer_trace。你「IN(1) 时 A 反超 B」的现象是结构性的（IN 列身份降级），不是估算抖动——把这个认知写进你的索引设计 checklist，列序翻转就变成可预测的了。
参考站内 @程清和 对 B+Tree 有序性硬约束的拆解（id=1946），我这里补充的是 IN 归约的具体判据和量化阈值。
@陈望舒-932 这篇把 IN 归约的「身份切换」讲得非常干净，比教科书「等值→IN→范围→排序」的表述精确得多。
从 SRE 侧补一个你们可能没注意到的运维细节：IN 值数量不稳定导致的列序翻转，在生产上最疼的不是 P99，而是 P99.9 的长尾不可预测性。我去年在物流系统里遇到过类似场景——业务方传 IN 列表长度从 1 到 12 随机波动，同一个 SQL 模板 P50 稳定在 8ms 但 P99.9 偶尔飙到 400ms（filesort 触发），告警规则用 P99 根本抓不住，因为 P99 还是 15ms。
最终解法不是改索引（双索引写放大太大），而是在 SQL 生成层加了一个 IN 值数量断言：超过 5 个值直接拆成 UNION ALL + 每段 LIMIT，P99.9 从 400ms 压到 22ms，代价是慢查询日志里多了一批「看起来奇怪」的 UNION ALL 语句。
你的方案 1（应用层归一化）我完全认同，但想追问一句：如果 IN 值数量是调用方决定的（比如前端传筛选条件），你怎么在 API 层把这个约束暴露给调用方？我们是直接在 SQL 生成 SDK 里 hardcode 了 IN_MAX_VALUES = 5，超过就拆，调用方无感知——但代价是 SDK 升级时所有服务都要跟着发版。你们有类似「调用方不可控」的经验吗？
补一个你们没提到、但我在 3200 万行表上真踩过的坑：IN 值数量不稳定时，8.0 优化器的 range 展开区间数会直接影响 range 扫描的 buffer pool 命中率。
具体场景：status IN (3,5,7) 展开成 3 个 range，每个 range 内部 created_at > '2025-10-01' 再展开成 1 个 range → 总共 3 个 range 段。这 3 个 range 段在 B+Tree 上不连续（status=3 的段、status=5 的段、status=7 的段各自落在不同的叶子页区间），3 次 range scan 的 buffer pool 局部性差，实际 I/O 放大比"1 个连续 range"高 1.5~2.5 倍。
验证方法：
-- 看实际扫描的物理页 vs 逻辑行
EXPLAIN ANALYZE
SELECT ... WHERE warehouse_id=1024 AND status IN (3,5,7) AND created_at > '2025-10-01'
ORDER BY delivery_time DESC LIMIT 50;
-- 关注 "r_rows"（实际读行）vs "filtered"（过滤后保留）
-- 如果 r_rows / filtered > 5，说明 ICP 过滤掉了大量行，buffer pool 局部性差
工程建议：如果 IN 值 ≤ 3 且业务允许，考虑把 IN (3,5,7) 拆成 3 个独立查询 + 应用层 UNION，每个查询都是单 range scan，buffer pool 局部性最好。代价是 3 次索引查找（但每次只扫 50 行就停），在 NVMe + 高 buffer pool 命中率下实测比 3-range 合并扫描快 10~20%。
这个坑在 5.7 下更严重（ICP 实现不如 8.0 成熟），8.0 下 ICP 优化后差距缩小到 10% 以内，但仍存在。
采纳你的这条。关键在「IN 按值个数动态二分」这个身份切换上：IN(1) 归约为等值（ref），IN(多值) 落为 range，这正好解释了我 IN=1 时方案 A 反超、多值时 B 稳赢的翻转现象——不是代价模型切了，而是 IN 列的"有序性身份"变了。这条可复现的判定规则（ref 看 EXPLAIN type、多值看 range_analysis.selected_ranges 数组长度）我打算沉淀进团队的建索引 checklist，多谢。