数十万级数据量下按(a,b)去重保留最大ID的删除SQL替代方案

所有方案都满足「保留每个(a,b)组合下id最大的最新记录，删除其余重复项」的核心需求，且规避了原NOT IN、自连接方案的性能缺陷。 #

方案1：窗口函数分区标记（通用场景性能最优） #

这个方案仅需一次全表遍历即可完成标记，中间结果集大小和原表行数完全一致，不会因重复率升高出现结果膨胀，是几十万到百万级表的首选方案。

WITH keep_records AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY a, b ORDER BY id DESC) AS sort_rank
  FROM foo
)
DELETE FROM foo
USING keep_records
WHERE foo.id = keep_records.id
  AND keep_records.sort_rank > 1;

• 性能特点：不管重复率多少，执行成本和原表行数线性相关，不存在笛卡尔积式的中间表膨胀问题。

方案2：NOT EXISTS半连接（索引加持下的轻量实现） #

这个方案的逻辑非常直接：只要当前记录的(a,b)组合存在id比它更大的同组记录，就直接标记为待删除项，查询采用半连接模式，找到匹配项就停止检索，不会遍历全组所有记录。

DELETE FROM foo f
WHERE EXISTS (
  SELECT 1 FROM foo
  WHERE a = f.a
    AND b = f.b
    AND id > f.id
);

• 性能特点：如果提前建立(a, b, id)联合索引，这个查询可以完全走索引扫描，不需要全表排序，执行速度比窗口函数方案更快，且能规避NOT IN写法对NULL值敏感、易清空全表的隐患。

方案3：批量分批删除（生产大表场景首选） #

如果表数据量达到百万级以上，一次性执行大事务删除容易出现锁表时间过长、WAL日志暴涨、IO打满影响线上业务的问题，这种场景适合将删除操作拆成小批次循环执行：

-- 循环执行以下语句，直到每次执行影响的行数为0
WITH batch_delete AS (
  SELECT id FROM foo f
  WHERE EXISTS (
    SELECT 1 FROM foo
    WHERE a = f.a AND b = f.b AND id > f.id
  )
  LIMIT 1000
)
DELETE FROM foo
USING batch_delete
WHERE foo.id = batch_delete.id;

• 性能特点：每批次只处理1000条待删除记录，事务粒度小，锁持有时间短，IO可以平滑消化，不会对线上业务造成明显影响。

关键注意事项 #

• 索引是性能核心：不管用哪个方案，提前建立(a, b, id DESC)的联合索引，能将查询性能提升一个量级。等重复数据清理完成后，可直接将该索引调整为唯一约束，从数据库层面强制(a,b)组合的唯一性，从根源上避免后续产生重复数据。
• 原写法的性能根因：
  • 原NOT IN写法会生成庞大的待比对ID列表，查询优化器无法生成高效执行计划，逐行比对的开销会随数据量急剧上升；且子查询一旦返回NULL，会导致全表被误删。
  • 传统自连接写法会让同组记录两两匹配，当某个(a,b)组合存在N条重复时，会生成N*N行的中间结果，重复率越高中间表体积越大，性能会指数级下降。

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