清理3000万行膨胀表重复数据耗时过长，求最优解决方案

你当前使用的DELETE JOIN语句跑了24小时仍未完成，核心问题有两点：

• unique_col列无索引，导致表自连接时触发全表扫描，3000万行的自连接复杂度为O(n²)，计算量极大
• 批量删除大量数据会生成巨量undo日志，不仅拖慢执行速度，还会长时间锁表，影响业务可用性

最优方案：创建新表导入去重数据（强烈推荐） #

这种方式比直接删除效率高得多，且风险可控：

1. 创建与原表结构一致的新表，同时给unique_col加索引（或唯一约束，从根源避免后续重复数据）

CREATE TABLE `my_table_new` LIKE `my_table`;
-- 给unique_col加前缀索引（varchar(2048)全字段索引体积过大，前缀足够区分重复即可）
CREATE INDEX idx_unique_col ON my_table_new(unique_col(255));
-- 若要彻底防止后续重复，可添加唯一约束：
-- ALTER TABLE my_table_new ADD UNIQUE KEY uk_unique_col(unique_col(255));

2. 导入去重后的数据，保留每个unique_col对应的最大id行（和你原DELETE逻辑一致，删除旧行、保留新行）

INSERT INTO my_table_new
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY unique_col ORDER BY id DESC) AS rn
    FROM my_table
) t
WHERE rn = 1;

若担心一次性插入压力过大，可按id分段分批插入。

3. 替换原表，此操作几乎瞬间完成

RENAME TABLE my_table TO my_table_old, my_table_new TO my_table;

4. 验证数据无误后删除旧表

DROP TABLE my_table_old;

若必须使用DELETE方式的优化版 #

如果无法创建新表，至少先给unique_col加索引，再分批删除，避免长时间锁表：

1. 先添加索引：

CREATE INDEX idx_unique_col ON my_table(unique_col(255));

2. 循环执行分批删除语句（每次删1000条，直到无数据可删）：

SET SESSION wait_timeout=999999;
DELETE t1 FROM my_table t1
INNER JOIN my_table t2
WHERE t1.id < t2.id AND t1.unique_col = t2.unique_col
LIMIT 1000;

关键总结 #

• 原方案的全表自连接+批量删除是效率极低的操作，完全不适合3000万行的大表
• 创建新表的方式本质是用写操作替代删操作，避免undo日志开销，且可并行处理，速度提升数倍甚至数十倍
• 给unique_col加索引是所有去重操作的前提，能大幅降低重复项的查询成本

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