优化含CASE WHEN的SELECT查询：交易匹配慢查询问题求助

问题根源 #

你当前查询变慢的核心原因是嵌套EXISTS子句会对每一条acctng_trans_id IS NULL的记录执行一次无索引（或全表）扫描的子查询，相当于把单表查询变成了N次重复关联，数据量越大性能下跌越明显。

优化方案 #

1. 预计算反向交易标记（替代逐行子查询）

把反向交易的判断逻辑从CASE里拆出来，一次性计算所有存在反向交易的store_trans_id集合，再关联到主查询中，避免逐行重复计算：

-- 先预查询出所有存在反向交易的门店交易ID
WITH reversed_trans AS (
    SELECT DISTINCT c1.store_trans_id
    FROM compare c1
    INNER JOIN compare c2 
        ON c1.store_trans_id = c2.store_trans_id
        AND c1.store_amount = -c2.store_amount
    WHERE c1.acctng_trans_id IS NULL
)
SELECT DISTINCT c.*,
    CASE
        WHEN c.store_trans_id IS NOT NULL AND c.acctng_trans_id IS NOT NULL THEN 'Match'
        WHEN c.acctng_trans_id IS NULL AND EXISTS (SELECT 1 FROM reversed_trans rt WHERE rt.store_trans_id = c.store_trans_id) THEN 'Store item transaction reversed'
        ELSE 'Research further'
    END AS Comparison
FROM compare c;

2. 用窗口函数批量判断反向交易

如果反向交易的特征是同一store_trans_id下金额总和为0（正负金额完全抵消），可以用窗口函数一次性计算每个交易ID的总金额，直接判断是否存在反向交易，性能更优：

SELECT DISTINCT c.*,
    CASE
        WHEN c.store_trans_id IS NOT NULL AND c.acctng_trans_id IS NOT NULL THEN 'Match'
        WHEN c.acctng_trans_id IS NULL AND total_trans_amount = 0 THEN 'Store item transaction reversed'
        ELSE 'Research further'
    END AS Comparison
FROM (
    SELECT 
        *,
        -- 按门店交易ID分组，计算该ID下的总交易金额
        SUM(store_amount) OVER (PARTITION BY store_trans_id) AS total_trans_amount
    FROM compare
) c;

3. 添加组合索引提速

不管用哪种方案，给compare表创建以下组合索引是关键，能让连接、分组、子查询的速度大幅提升：

CREATE INDEX idx_compare_store_trans_amount ON compare(store_trans_id, store_amount);

额外建议 #

如果compare是CTE临时表，部分数据库（如SQL Server）对CTE的索引支持有限，可以考虑把compare的结果写入临时表并创建索引，再进行后续查询，进一步优化性能。

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