UNION 子查询修好了 OR 索引合并，却把 ORDER BY 索引打掉了

某个列表接口慢查询告警，EXPLAIN 看到 index_merge sort_union，按教科书将 WHERE a=? OR b=? 改写成 UNION 子查询，OR 的问题确实消失了——但接口 TTFB 没降，反而从 3s 涨到了 18s。这是一个"第一步修对了，第二步却踩新坑"的典型案例。

现象

列表接口响应时间秒级起跳，分页首页要 18s，但 COUNT(*) 版本只需 0.3s
每个 UNION 分支单独跑很快，都能走各自的复合索引 range 扫描
EXPLAIN 第一行：table = <derived3>，type = ALL，rows ≈ 42000，Extra: Using temporary; Using filesort
ORDER BY last_updated_time DESC 字段上明明有复合索引 (a, last_updated_time)，但 MySQL 根本没用

根因

改写后的查询结构大致是这样：

SELECT * FROM t
WHERE id IN (
  SELECT id FROM (
    SELECT id FROM t WHERE a = ?
    UNION
    SELECT id FROM t WHERE b = ?
  ) AS uc
)
ORDER BY last_updated_time DESC
LIMIT 15

MySQL 在执行时必须先物化（materialize） UNION 的派生表 uc 到一张临时表，再用临时表的 id 回表 t，最后对合并结果做 filesort。

物化这一步是关键：临时表只存 id，不携带任何顺序信息。外层 ORDER BY last_updated_time DESC 想走 (a, last_updated_time) 这个复合索引，但执行路径已经从"按索引扫描 t"变成了"按临时表 id 随机回表 t"，索引的有序性在物化过程中完全丢失。

坑：派生表物化会让外层 ORDER BY 的索引完全失效

WHERE id IN (SELECT id FROM (<UNION>) AS x) 这个模式会强制产生派生表物化（<derived_N> ALL）。不管外层 ORDER BY 的字段上有多好的索引，MySQL 都无法通过一张只有 id 的临时表来维持顺序——filesort 无可避免。

仅把 UNION 改成 UNION ALL 不解决问题：它只去掉了一次去重 sort，派生表物化依然存在。给外层加 FORCE INDEX 同样无效：索引根本没有被纳入那条执行路径。

正解

将外层 ORDER BY + LIMIT 下推到每个 UNION 分支内部，让每段都能各自利用自己的复合索引：

(
  SELECT * FROM t
  WHERE a = ?
  ORDER BY last_updated_time DESC
  LIMIT :offset_plus_limit          -- 必须 >= offset + page_size
)
UNION ALL
(
  SELECT * FROM t
  WHERE b = ? AND a <> ?            -- 显式去重，排除第一段已覆盖的行
  ORDER BY last_updated_time DESC
  LIMIT :offset_plus_limit
)
ORDER BY last_updated_time DESC
LIMIT :offset, :page_size

正解：ORDER BY + LIMIT 下推到各 UNION 分支

每段各自 ORDER BY + LIMIT K（K ≥ offset + page_size），走 (a, last_updated_time) / (b, last_updated_time) 的 range 扫描，不产生派生表
使用 UNION ALL（不是 UNION），省去一次额外去重排序
第二段加 AND a <> ? 显式防重
外层 ORDER BY + LIMIT 只对两段各自最多 K 行做内存合并排序，代价极小
EXPLAIN 确认：<derived_N> ALL 消失，Using temporary; Using filesort 消失

快速诊断

EXPLAIN <你的查询>;

看第一行：table 是否 <derived_N>，type 是否 ALL，Extra 是否包含 Using temporary; Using filesort。三者同时出现 → 确认中招。

一句话外卖

UNION 子查询物化会抹掉所有索引有序性，外层 ORDER BY 的索引救不了你——要想用索引排序，ORDER BY LIMIT 必须下推到每个分支内部。