MySQL中UNION ALL合并快速查询执行极慢问题咨询

• 外层排序与LIMIT触发全量结果拉取
  当前写法将ORDER BY created_at DESC LIMIT 10放在UNION ALL的最外层，MySQL（尤其5.x版本）的优化器不会将LIMIT限制下推到两个独立子查询分支中：会先执行完两个分支的全量关联查询，把所有返回行拼成超大临时表，再对临时表的全量数据做全局排序，最后截取前10条结果。
  两个子查询单独执行1秒的耗时，是不带全局全量排序场景下的查询开销；一旦需要全量拉取关联结果，只要comments、reply两个表关联后的总行数达到十万甚至百万级，临时表构建、全局排序的开销就会指数级上涨。这一逻辑在MySQL 8.0.20之前的版本中没有自动优化路径，高版本也会在外层排序字段与分支字段不匹配时触发全量拉取。
• 内存临时表溢出带来的额外IO开销
  当全量关联结果的总大小超过MySQL配置的tmp_table_size、max_heap_table_size阈值时，原本在内存中完成的临时表构建、排序操作会被转写到磁盘完成。磁盘IO的开销是内存操作的几十上百倍，直接导致查询耗时从秒级涨到几十秒，甚至因为占满服务器IO带宽把网站接口拖到卡死。
• 原有查询索引在全量构建场景下失效
  两个子查询单独执行时，优化器会选择覆盖索引做高效关联查询；但当需要把全量行存入临时表时，原本的索引覆盖扫描路径不再适用，需要额外回表提取所有需要的字段值，进一步放大查询开销。

• 核心优化：将排序与LIMIT逻辑下推到两个子查询分支
  由于最终只需要按时间倒序取10条数据，完全不需要拉取两个表的全量关联结果。可以让两个分支各自先按created_at倒序，提前截取足够数量的候选行（比如每个分支取20条，避免时间边界值截断导致结果不准），再在外层对小体量的候选集做全局排序截取前10条。优化后的参考结构：

SELECT user_id, description, post_id, created_at, size_name, user_name, avatar, title, slug
FROM (
  -- 评论分支：提前按创建时间倒序截取候选行
  SELECT p.user_id, p.description, p.post_id, p.created_at, 
         ps.username AS size_name, ps.username AS user_name, ps.avatar AS avatar, 
         pz.title AS title, pz.slug AS slug 
  FROM comments p 
  JOIN users ps ON ps.id = p.user_id 
  JOIN posts pz ON pz.id = p.post_id
  ORDER BY p.created_at DESC
  LIMIT 20

  UNION ALL

  -- 回复分支：提前按创建时间倒序截取候选行
  SELECT p2.user_id, p2.description, p2.post_id, p2.created_at, 
         ps2.username AS size_name, ps2.username AS user_name, ps2.avatar AS avatar, 
         pz2.title AS title, pz2.slug AS slug 
  FROM reply p2 
  JOIN users ps2 ON ps2.id = p2.user_id 
  JOIN posts pz2 ON pz2.id = p2.post_id
  ORDER BY p2.created_at DESC
  LIMIT 20
) AS union_result
-- 外层仅对共40条候选数据做全局排序
ORDER BY created_at DESC
LIMIT 10

调整写法后，每个分支的关联查询依然可以保持原来1秒内的执行速度，外层排序的总数据量只有40行，几乎不产生额外开销。

• 为业务表补充匹配的联合索引
  给comments和reply表分别创建结构匹配的联合索引，消除排序和关联时的回表开销：
• comments表创建联合索引：idx_created_post_user (created_at, post_id, user_id)
  索引直接按照created_at的排序方向存储，查询时不需要额外做排序操作，同时索引中包含post_id、user_id两个关联外键，关联users、posts表时不需要回表查字段值。
• reply表创建同结构联合索引：idx_created_post_user (created_at, post_id, user_id)
• 规避临时表磁盘溢出
  检查MySQL配置中tmp_table_size和max_heap_table_size的设置，确保内存临时表的可用容量可以覆盖候选集大小，让排序、临时表构建操作全程在内存中完成，避免磁盘IO带来的性能损耗。

内容的提问来源于stack exchange，提问作者Naklov