使用DISTINCT处理30GB关联表去重耗时超5小时，求排查优化

我关联多张表生成了一张约30GB的表，需要按特定列的相同值去重。用DISTINCT语句跑了5小时还没完成，怀疑代码有问题。一开始导入了所有列，后来改成只导入需要的列；原本用GROUP BY，以为DISTINCT比GROUP BY更高效就替换了，但效果没变好。

原SQL代码如下：

create table other_reac_demo_other_drug 
select c.primaryid, c.caseid, c.caseversion, c.event_dt, c.mfr_dt, 
       c.init_fda_dt, c.fda_dt, c.age, c.age_cod, c.sex,  c.wt, c.wt_cod, 
       c.rept_dt, c.occp_cod, c.reporter_country, c.occr_country, 
       d.drug_seq, d.role_cod, d.drugname, d.prod_ai, d.nda_num, c.pt 
from world.other_reac_demo as c 
left join world.not_ESA as d on c.primaryid = d.primaryid;

create table final_other_drug_cohort_CV 
select primaryid, event_dt, 
       age, sex, reporter_country,
       drugname, prod_ai,  pt 
from cv_reac_demo_other_drug 
group by primaryid, event_dt, age, sex, reporter_country, drugname, 
         prod_ai, pt;

问题分析 #

• DISTINCT和GROUP BY在多数数据库引擎里执行逻辑几乎一致，优化器经常会把它们转换成相同的执行计划，所以替换后没性能提升很正常。
• 先关联生成30GB大表再去重的方式太浪费资源：关联时只靠primaryid，如果这个字段重复多，会直接导致关联后数据量爆炸，后续去重需要处理的行数陡增。
• 去重用的GROUP BY涉及8个字段，数据库需要对这些字段做排序或哈希聚合，30GB数据量下如果内存不够，会触发磁盘临时表，速度直接拉胯。

优化方案 #

1. 提前过滤+分步去重，砍掉中间数据量 #

别先搞出大表再处理，先对原始表做过滤和去重，再关联，最后再去重，能大幅减少中间数据：

-- 先对两张原始表分别过滤需要的列并去重
WITH filtered_reac AS (
    SELECT DISTINCT primaryid, event_dt, age, sex, reporter_country, pt
    FROM world.other_reac_demo
),
filtered_drug AS (
    SELECT DISTINCT primaryid, drugname, prod_ai
    FROM world.not_ESA
)
-- 关联后再去重生成最终表
CREATE TABLE final_other_drug_cohort_CV
SELECT DISTINCT r.primaryid, r.event_dt, r.age, r.sex, r.reporter_country, d.drugname, d.prod_ai, r.pt
FROM filtered_reac r
LEFT JOIN filtered_drug d ON r.primaryid = d.primaryid;

2. 加合适的索引，让关联和去重飞起来 #

给原始表的关联字段、去重用到的字段组合加复合索引：

• 给world.other_reac_demo建复合索引：(primaryid, event_dt, age, sex, reporter_country, pt)
• 给world.not_ESA建复合索引：(primaryid, drugname, prod_ai)
  这样数据库可以直接用索引过滤数据、完成关联，不用扫描全表，速度能提一大截。

3. 调数据库配置，让聚合尽量在内存完成 #

• 如果用MySQL：调大tmp_table_size和max_heap_table_size（比如设成4G以上，根据服务器内存调整），避免聚合操作落到磁盘临时表。
• 如果用PostgreSQL：调大work_mem参数（比如设成64M或更高），给排序、哈希聚合分配更多内存。

4. 用窗口函数去重，某些场景更高效 #

如果数据库支持（MySQL8.0+/PostgreSQL等），可以用ROW_NUMBER()标记重复行，只保留第一行，适合需要指定保留哪条重复行的场景：

CREATE TABLE final_other_drug_cohort_CV
SELECT primaryid, event_dt, age, sex, reporter_country, drugname, prod_ai, pt
FROM (
    SELECT 
        r.primaryid, r.event_dt, r.age, r.sex, r.reporter_country, d.drugname, d.prod_ai, r.pt,
        -- 按去重字段分组，给每组行编号
        ROW_NUMBER() OVER (PARTITION BY r.primaryid, r.event_dt, r.age, r.sex, r.reporter_country, d.drugname, d.prod_ai, r.pt ORDER BY (SELECT NULL)) AS rn
    FROM world.other_reac_demo r
    LEFT JOIN world.not_ESA d ON r.primaryid = d.primaryid
    -- 提前过滤掉空的primaryid，减少无效数据
    WHERE r.primaryid IS NOT NULL
) t
WHERE rn = 1;

如果业务上需要保留特定行（比如最新的event_dt），把ORDER BY (SELECT NULL)换成ORDER BY r.event_dt DESC就行。

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