MySQL中多次UNION复用同一耗时子查询的性能优化咨询

嘿，这个场景我碰到过好多次——重复调用同一个耗时的复杂子查询，光是想想都替数据库累得慌😅。咱们来聊聊几个靠谱的优化方案，帮你把整体执行时间砍下来：

1. 用CTE（公共表表达式）复用子查询 #

这是最简洁的方案，大多数现代数据库（MySQL 8.0+/PostgreSQL/SQL Server等）都支持。把那个复杂的子查询定义一次，后面的UNION分支直接复用这个结果集，数据库只会执行一次子查询，而不是每次UNION都跑一遍。

示例代码：

WITH reusable_subquery AS (
    SELECT * 
    FROM A 
    INNER JOIN B ON A.join_col = B.join_col  -- 替换成你的实际关联条件
    WHERE YOUR_COMPLEX_WHERE_CONDITIONS      -- 你的复杂过滤逻辑
)
SELECT * FROM reusable_subquery ORDER BY column1 DESC LIMIT 10
UNION ALL  -- 注意：如果结果集不会有重复行，用UNION ALL比UNION快（省去去重步骤）
SELECT * FROM reusable_subquery ORDER BY column2 DESC LIMIT 10
UNION ALL
SELECT * FROM reusable_subquery ORDER BY column3 DESC LIMIT 10;

小提示：如果你的数据库支持物化CTE（比如PostgreSQL），可以加上MATERIALIZED关键字强制数据库把CTE结果存起来，彻底避免重复计算：WITH reusable_subquery AS MATERIALIZED (...)。

2. 用临时表存储子查询结果 #

如果你的数据库版本比较老（比如MySQL 5.x不支持CTE），或者CTE的优化效果不如临时表，那就用临时表来存复杂子查询的结果。临时表是会话级的，只会在当前连接中存在，会话结束后自动销毁，不用担心污染数据库。

示例代码：

-- 先把复杂子查询的结果存入临时表
CREATE TEMPORARY TABLE temp_subquery_result AS
SELECT * 
FROM A 
INNER JOIN B ON A.join_col = B.join_col
WHERE YOUR_COMPLEX_WHERE_CONDITIONS;

-- 基于临时表执行UNION查询
SELECT * FROM temp_subquery_result ORDER BY column1 DESC LIMIT 10
UNION ALL
SELECT * FROM temp_subquery_result ORDER BY column2 DESC LIMIT 10
UNION ALL
SELECT * FROM temp_subquery_result ORDER BY column3 DESC LIMIT 10;

-- 可选：如果需要提前清理，手动删除临时表
-- DROP TEMPORARY TABLE temp_subquery_result;

3. 先优化子查询本身 #

别光顾着复用，先给那个复杂子查询“瘦个身”！比如：

• 给A和B的关联字段加索引，减少关联时的查找时间；
• 给WHERE条件里的过滤字段加复合索引，让过滤操作更快；
• 别用SELECT *，只取实际需要的列，能大幅减少数据传输和存储的开销；
• 给排序用的column1、column2等字段加索引，让ORDER BY ... LIMIT 10的排序步骤更快（尤其是在临时表/CTE上）。

4. 替换UNION为UNION ALL（如果可以的话） #

如果你的各个分支的结果集不会有重复行，一定要把UNION换成UNION ALL！UNION会自动去重，这需要额外的排序和对比操作，换成UNION ALL能省掉这部分开销，性能提升很明显。

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