Redshift十亿级地址大表多轮查询性能优化方案咨询

现有两种方案性能对比 #

你提到的两种实现思路性能都不理想，本质均存在多次全表扫描开销：

• 第一种方案每个addrtype对应一个独立CTE，每个CTE都会扫描一次全表，加上后续和主表的JOIN，总扫描次数≥4次，性能最差
• 第二种方案把每个类型的过滤逻辑放到JOIN子查询中，和第一种没有本质区别，每个子查询依然会独立扫描全表，性能没有明显提升

最优实现方案（仅需1次全表扫描） #

直接使用窗口函数单次扫描全表完成优先级排序取数，完全避免多次扫描开销，符合Redshift分布式计算的优化特性：

实现逻辑 #

1. 提前过滤所有ph#为空的无效记录，减少参与计算的数据量
2. 对每条记录按业务要求的addrtype优先级打权重分
3. 按客户ID分区，先按权重分升序、再按插入时间降序排序，取每个客户排名第一的记录即可

代码示例 #

SELECT custid, addrtype, "ph#", insert_date
FROM (
    SELECT 
        custid,
        addrtype,
        "ph#",
        insert_date,
        ROW_NUMBER() OVER (
            PARTITION BY custid
            ORDER BY
                CASE addrtype
                    WHEN 'A1' THEN 1
                    WHEN 'A2' THEN 2
                    WHEN 'A3' THEN 3
                    ELSE 4
                END ASC,
                insert_date DESC
        ) AS record_rank
    FROM addr
    WHERE "ph#" IS NOT NULL
) valid_records
WHERE record_rank = 1;

额外优化建议 #

• 若addr表已将custid设为分布键（DISTKEY），窗口函数的分区计算会在各节点本地完成，无需跨节点数据shuffle，性能可提升3~10倍
• 若表的排序键（SORTKEY）包含ph#、addrtype、insert_date字段，过滤和排序的开销会进一步降低

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