如何优化多表相似关联ID查询的性能？

原查询性能瓶颈分析 #

原查询在关联两个关联表时，嵌套的IN子查询会重复计算目标ID（1、2、5）对应的co_id集合；同时INNER JOIN会生成大量中间结果集，后续的COUNT(DISTINCT)又要对这些数据重复去重统计，当待匹配ID增多时，计算量会呈指数级上升，直接导致查询变慢。

优化方案 #

核心思路

先一次性预计算目标ID对应的共享co_id集合，再分别统计每个test_id的匹配co_id数量，避免全表关联产生冗余数据，最后基于统计结果排序输出。

完整优化后SQL #

WITH target_co1 AS (
    -- 预计算test_corelation_1中目标ID共享的co_id
    SELECT co_id
    FROM test_corelation_1
    WHERE test_id IN (1, 2, 5)
    GROUP BY co_id
),
target_co2 AS (
    -- 预计算test_corelation_2中目标ID共享的co_id
    SELECT co_id
    FROM test_corelation_2
    WHERE test_id IN (1, 2, 5)
    GROUP BY co_id
),
test_match_counts AS (
    SELECT
        t.id,
        -- 统计test_corelation_1中匹配的co_id数量，无匹配则显示0
        COALESCE(tc1.match_count, 0) AS match_count_1,
        -- 统计test_corelation_2中匹配的co_id数量，无匹配则显示0
        COALESCE(tc2.match_count, 0) AS match_count_2
    FROM test t
    LEFT JOIN (
        SELECT test_id, COUNT(co_id) AS match_count
        FROM test_corelation_1
        WHERE co_id IN (SELECT co_id FROM target_co1)
        GROUP BY test_id
    ) tc1 ON t.id = tc1.test_id
    LEFT JOIN (
        SELECT test_id, COUNT(co_id) AS match_count
        FROM test_corelation_2
        WHERE co_id IN (SELECT co_id FROM target_co2)
        GROUP BY test_id
    ) tc2 ON t.id = tc2.test_id
    -- 过滤出至少有一个匹配的记录，若需保留无匹配记录可删除此条件
    WHERE tc1.match_count IS NOT NULL OR tc2.match_count IS NOT NULL
)
SELECT id, match_count_1, match_count_2
FROM test_match_counts
ORDER BY (match_count_1 + match_count_2) ASC;

额外性能优化建议 #

• 给test_corelation_1和test_corelation_2创建复合索引：(test_id, co_id)，加速目标co_id的查询和后续分组统计
• 如果目标ID是动态传入的，可改用临时表存储这些ID，避免重复解析IN列表
• 若数据库版本支持，可将CTE替换为物化视图，进一步提升重复查询的性能

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