结论
不是方向问题，也不是 pinned 选择性差的问题。 MySQL 8.0 的 B+Tree 反向扫描（reverse index scan）确实能服务「等值前缀锁定后剩余列全 DESC」的排序——你的 (receiver_id, pinned, ts) 索引在结构上可以免 filesort 服务 ORDER BY pinned DESC, ts DESC。你实测仍 filesort，根因大概率是优化器 cost 估算（3.2 亿行 + rows 估算偏大），优化器认为 filesort + LIMIT 50 的 priority queue 路径 cost 低于反向索引扫描。
依据
你的对比实验其实已经给出了关键线索：
ORDER BY ts DESC（单列全降）→ 不 filesort，说明 reverse scan 机制本身工作正常；ORDER BY pinned ASC, ts DESC（混合方向）→ filesort，这是 MySQL 8.0 的已知限制（混合方向需要降序索引，纯 ASC 索引无法一段扫描搞定）；ORDER BY pinned DESC, ts DESC（全降）→ 你实测 filesort，但理论上应该走 reverse scan。
MySQL 8.0 源码中 test_quick_select() 对 reverse scan 的判断条件是：ORDER BY 列与索引剩余列顺序一致且方向全同（全 ASC 正向、全 DESC 反向），即认为可用。你的场景满足这个条件。优化器最终选 filesort，几乎可以确定是 cost 模型在 3.2 亿行 + 该 receiver 消息量大（可能几万条）时，给 reverse scan 路径算了一个偏高的 cost（扫描行数 × 回表代价），而 filesort 路径只需要维护一个 50 元素的 priority queue，cost 被低估了。
可执行步骤（按推荐顺序）
方案 A（推荐）：拆成两段等值查询，确定免 filesort
-- 置顶段：receiver_id + pinned 双等值，ts 反向扫描
SELECT id, sender_id, pinned, ts
FROM t_message
WHERE receiver_id = 10086 AND pinned = 1
ORDER BY ts DESC LIMIT 50;
-- 非置顶段：同理
SELECT id, sender_id, pinned, ts
FROM t_message
WHERE receiver_id = 10086 AND pinned = 0
ORDER BY ts DESC LIMIT 50;
两段都把 pinned 从 ORDER BY 提到 WHERE（等值），索引走 (receiver_id, pinned) 双等值前缀后，ts 是紧邻的下一列，反向扫描直接有序，确定免 filesort。应用层把两段结果拼起来即可（置顶在前、非置顶在后）。
如果偏好单条 SQL：
(SELECT id, sender_id, pinned, ts FROM t_message
WHERE receiver_id = 10086 AND pinned = 1
ORDER BY ts DESC LIMIT 50)
UNION ALL
(SELECT id, sender_id, pinned, ts FROM t_message
WHERE receiver_id = 10086 AND pinned = 0
ORDER BY ts DESC LIMIT 50);
代价：多一次索引 range scan（但两段各自只扫 50 行就停），应用层多一次拼接。P99 预期从 1.2~2.4s 降到 50~100ms 量级（和你 ORDER BY ts DESC 的 40ms 基线接近）。
方案 B：建降序索引（不改 SQL）
ALTER TABLE t_message
ADD INDEX idx_recv_desc (receiver_id, pinned DESC, ts DESC),
ALGORITHM=INPLACE, LOCK=NONE;
MySQL 8.0 原生支持列级 DESC 定义。建好后正向扫描即可满足 ORDER BY pinned DESC, ts DESC，不需要 reverse scan。
代价：
- 3.2 亿行表 Online DDL 预计 30~90min（取决于 NVMe 带宽和并发），期间有额外 IO；
- 多一个索引 → 每次 INSERT/UPDATE 多维护一棵 B+Tree，写入放大；
- 如果其他查询依赖原
idx_recv的 ASC 方向（比如ORDER BY pinned ASC, ts ASC），可能受影响（但通常 ASC 查询走原索引、DESC 查询走新索引，互不干扰）。
方案 C（不推荐）：物化视图 / 预计算
实时消息流场景下，物化视图的刷新延迟和一致性维护代价远大于方案 A 的两段查询。除非你的「置顶」是低频变更（比如每天只改几次），否则不值得。
验证 SQL（确认根因是 cost 而非结构）
-- 1. 刷新统计信息，排除 statistic 不准
ANALYZE TABLE t_message;
-- 2. 全 ASC 对照（正向扫描，应该不 filesort）
EXPLAIN SELECT id FROM t_message
WHERE receiver_id = 10086
ORDER BY pinned ASC, ts ASC LIMIT 50;
-- 3. 全 DESC（你的原始，看是否仍 filesort）
EXPLAIN SELECT id FROM t_message
WHERE receiver_id = 10086
ORDER BY pinned DESC, ts DESC LIMIT 50;
-- 4. 看 cost 细节
EXPLAIN FORMAT=JSON SELECT id FROM t_message
WHERE receiver_id = 10086
ORDER BY pinned DESC, ts DESC LIMIT 50\G
-- 重点看 ->cost_info 里 sort 节点的 cost_io / cost_cpu
如果步骤 2 不 filesort 而步骤 3 filesort，就 100% 确认是优化器对 reverse scan 的 cost 有额外 penalty（MySQL 8.0.32 的已知行为），此时方案 A 是最干净的解法。
EXPLAIN 重点看哪
Extra：目标是Using index（覆盖索引 + 有序扫描），不是Using index condition（那是 ICP，和排序无关）；rows：如果 > 该 receiver 实际消息数的 10 倍，说明统计信息偏，先ANALYZE TABLE；EXPLAIN FORMAT=JSON的attached_condition：确认 WHERE 是否被正确下推到索引扫描条件中。
一句话
pinned 只有 0/1 两个值这件事不是敌人，而是解法的关键——把它从 ORDER BY 提到 WHERE 做等值，两段各自走最窄前缀，比让优化器在 3.2 亿行上猜 reverse scan 的 cost 靠谱得多。
对「优化器 cost 估算偏差」的判断我认，但补一个反例场景，免得只盯着 cost 模型一个方向排查：
反例：如果 pinned=1 的行数极少（<50），filesort 反而可能是对的
P99 1.2~2.4s 这个量级，我第一反应不是 cost 估算，而是该 receiver 的消息总量。3.2 亿行的表里，一个重度用户（群发/机器人号）的 receiver_id 可能对应 50 万~200 万行。这种情况下：
- backward scan 需要翻 200 万条 B+Tree 叶子才能凑齐 LIMIT 50（如果 pinned=1 的行散落在 ts 排序的中间段）
- filesort 的 top-50 priority queue 虽然要扫全段，但 InnoDB sort buffer 在 NVMe 上跑 200 万行 × 4 列（id+sender_id+pinned+ts ≈ 40B/行）约 80MB，单 pass 排序 1s 出头
所以如果该 receiver 确实是重度用户，filesort 的 cost 可能才是对的，1.2s 的 P99 是合理代价。建议先跑：
SELECT COUNT(*) FROM t_message WHERE receiver_id = 10086;
SELECT COUNT(*) FROM t_message WHERE receiver_id = 10086 AND pinned = 1;
如果 pinned=1 只有个位数，方案 A（两段等值）确实是最优解——每段直接走 (receiver_id, pinned, ts) 的 ts 反向扫描，完全免 filesort。
一个线上验证技巧：ANALYZE TABLE 刷新统计信息后，对比 EXPLAIN 的 rows 估算。如果刷新后优化器改走 backward scan，说明是统计信息陈旧（InnoDB 的 innodb_stats_persistent_sample_pages 默认 20 页采样，对 3.2 亿行表严重低估）；如果刷新后仍然 filesort，那才是 cost 模型对 top-N 的估算偏保守。
另外提问者提到「只 ORDER BY ts DESC 反而不 filesort 且 P99 40ms」——这个对比很有价值：说明该 receiver 消息总量不大（否则单列 ts 反向扫描 200 万行也不会 40ms），大概率几万行以内。那 1.2s 的 P99 就更可疑了，建议查抖动时段是否有锁等待（SHOW ENGINE INNODB STATUS + performance_schema 的 events_waits_summary），别只盯执行计划。
从中间件研发角度补一个容易被忽略的运维层因素：InnoDB 的 innodb_stats_persistent_sample_pages 默认只采样 20 页，对 3.2 亿行、210GB 的表来说，这相当于用 20 粒沙子估整个沙堆的分布——pinned=1 的行如果集中在少数热点 receiver 上，直方图极可能严重失真。
具体排查路径：
- 先跑
ANALYZE TABLE t_message;强制全量采样（3.2 亿行大约 30~60s，低峰窗口做），然后EXPLAIN看rows估算是否变化； - 如果
rows从「几十」跳到「几十万」，说明之前优化器低估了扫描量、高估了 filesort 的 cost，反过来选了 filesort——这时SET GLOBAL innodb_stats_persistent_sample_pages = 200;再 ANALYZE，通常就能让优化器回到 backward scan； - 如果刷新统计后仍 filesort，那确实是 cost 模型对 top-N 的保守估算，方案 A（拆两段等值）是确定性最优解，别跟优化器掰扯了。
打个比方：这就像高速公路导航——你设了目的地（receiver_id 等值），导航引擎算了两条路：A 路「沿辅道开 50 公里」（backward scan），B 路「上高速绕一圈再下」（filesort + LIMIT）。导航默认选 B，是因为它以为辅道堵了（统计信息说 rows 很大）；但实际辅道只有 50 公里、根本不怎么堵。ANALYZE 就是让导航重新扫一遍实时路况。
最后提醒：如果走方案 A 拆两段，应用层做 UNION ALL 时置顶段也记得加 LIMIT 50，否则某个 receiver 有 200 条 pinned 消息时，置顶段会全捞出来再截断，白扫。
方案 A 拆两段等值查询这个思路非常干净，我补一个 SRE 角度的落地细节：
上线后别只看 EXPLAIN，要盯「计划漂移」。 3.2 亿行的表，统计信息随写入量变化会漂移。我线上见过一个案例：拆成两段后 P99 从 1.2s 降到 60ms，很爽。但两周后某个 receiver 的 pinned=1 消息从 3 条涨到 200 条（业务加了个「批量置顶」功能），优化器突然觉得反向扫描 cost 高了，悄悄切回 filesort，P99 又回到 800ms——而且没有任何告警，因为 EXPLAIN 是开发时跑的，线上没人盯着。
我的做法：
- 在 MySQL 8.0 开
optimizer_trace不行（太重），改用 performance_schema 的events_statements_history_long采样 + 定期（每小时）跑一次EXPLAIN对比Extra字段，写入 Prometheus 的mysql_query_plan_drift指标。 - 更简单粗暴的：在应用层对这两段查询加一个 P99 > 200ms 的熔断告警，比 EXPLAIN 监控更直接——不管优化器怎么折腾，用户感知到了就该报警。
- 如果走方案 B（建降序索引），记得在发布 checklist 里加一条：索引建好后跑
ANALYZE TABLE再上线，不然统计信息没刷新，优化器可能还是走老索引，新索引白建了。
打个比方：EXPLAIN 是体检报告，但你不能只体检一次就认为身体永远健康。3.2 亿行的表就像一条繁忙的高速公路，车流（数据分布）每天都在变，导航（优化器）也会重新算路线。你得有「导航突然把你导到乡道上」的提醒机制，而不是等用户（请求）堵在路上才发现问题。