基于UNION ALL构建的CTE查询性能异常缓慢问题求助

问题根源分析 #

你猜的没错，核心问题就是UNION ALL导致数据库执行计划完全走偏：

• 当CTE是单表查询时，数据库能做谓词下推优化：先通过WHERE t.id_=12345定位到目标行，再根据该行的player_id_p1、location、date_去CTE里精准匹配符合条件的行，全程只扫描少量数据，自然快。
• 但加了UNION ALL后，多数数据库对这类CTE的优化能力有限，会先把整个UNION ALL的结果（200万行全量数据）生成临时集合，再和目标行做连接。相当于先全表扫描两次，再对大临时集做匹配，性能直接雪崩。
• 另外，UNION ALL生成的临时集合没有索引，连接时只能做低效的嵌套循环或哈希连接，进一步放大了性能问题。

可行解决办法 #

1. 拆分查询后合并结果（适合当前求和场景） #

既然单独查两部分都快，直接把两个子查询的结果相加，绕开全量UNION ALL：

SELECT 
    t.id_, 
    (
        -- 统计p1作为球员的历史发球数
        SELECT SUM(num_serves_p1)
        FROM table1 
        WHERE player_id_p1 = t.player_id_p1
          AND location = t.location
          AND date_ < t.date_
    ) + (
        -- 统计p2作为球员的历史发球数
        SELECT SUM(num_serves_p2)
        FROM table1 
        WHERE player_id_p2 = t.player_id_p1
          AND location = t.location
          AND date_ < t.date_
    ) AS total_num_serves
FROM table1 AS t
WHERE t.id_ = 12345

这个写法会让数据库分别对两个子查询做谓词下推，完全复用现有索引，速度和单独查询一致。

2. 将UNION ALL逻辑移到连接条件中 #

如果后续需要基于日期排序的结果计算，不用提前生成全量CTE，而是分别关联两部分数据：

SELECT 
    t.id_, 
    SUM(COALESCE(c1.num_serves, 0) + COALESCE(c2.num_serves, 0)) AS total_num_serves
FROM table1 AS t
LEFT JOIN table1 c1 
    ON c1.player_id_p1 = t.player_id_p1
    AND c1.location = t.location
    AND c1.date_ < t.date_
LEFT JOIN table1 c2 
    ON c2.player_id_p2 = t.player_id_p1
    AND c2.location = t.location
    AND c2.date_ < t.date_
WHERE t.id_ = 12345
GROUP BY t.id_

这种写法会先定位t.id_=12345的目标行，再分别去关联c1和c2，只扫描需要的行，不会生成百万级临时集。

3. 强制优化器做谓词下推（依赖数据库支持） #

部分数据库允许通过语法提示，让优化器把CTE逻辑展开，和主查询合并，从而实现谓词下推：

-- PostgreSQL示例：用NOT MATERIALIZED阻止CTE物化
WITH cte AS NOT MATERIALIZED (
    SELECT num_serves_p1 AS num_serves, player_id_p1 AS player_id, location, date_ FROM table1 
    UNION ALL 
    SELECT num_serves_p2 AS num_serves, player_id_p2 AS player_id, location, date_ FROM table1
)
SELECT 
    t.id_, SUM(cte.num_serves)
FROM table1 AS t
JOIN cte 
    ON cte.player_id = t.player_id_p1
    AND cte.location = t.location
    AND cte.date_ < t.date_
WHERE t.id_ = 12345
GROUP BY t.id_

注：不同数据库语法不同，MySQL可尝试WITH cte AS (/*+ NO_MATERIALIZE */ ...)，需要实际测试验证。

4. 预生成物化视图（高频查询场景） #

如果这个查询是高频使用，可以创建物化视图预存UNION ALL结果，并建立复合索引：

-- PostgreSQL物化视图示例
CREATE MATERIALIZED VIEW player_serves AS
SELECT num_serves_p1 AS num_serves, player_id_p1 AS player_id, location, date_ FROM table1 
UNION ALL 
SELECT num_serves_p2 AS num_serves, player_id_p2 AS player_id, location, date_ FROM table1;

-- 建立适配查询的复合索引
CREATE INDEX idx_player_serves ON player_serves(player_id, location, date_);

后续查询直接使用物化视图即可，性能会大幅提升，但需要定期刷新物化视图保证数据时效性。

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