针对10亿条记录的MySQL大表按指定字段去重方案问询

云上老周

2026-7-2

针对10亿条记录的MySQL批量去重优化方案

需求明确

基于UniqueFormId、ProductionStatusNo、ProductionStatus、StatusDate、ProductionFacility五个字段去重，保留每组重复数据中Id最小的记录，表数据量达10亿条，需高效批量处理。

现有方案的问题分析

方法一（关联删除+LIMIT）：
直接全表关联会触发海量IO和内存消耗，即使加了LIMIT，每次执行仍需扫描全表找重复匹配，效率极低，且长时间锁表会严重影响在线业务。
方法二（GROUP BY临时表替换）：
10亿数据下的多字段GROUP BY会导致内存溢出、磁盘IO暴涨，临时表需存储全量去重后数据，磁盘占用大；且DROP原表风险极高，一旦临时表创建失败，会直接丢失数据。
方法三（ROW_NUMBER()临时表替换）：
窗口函数比GROUP BY效率稍高，但仍需全表扫描生成排名，临时表存储全量数据的磁盘开销依旧巨大，同样存在DROP原表的数据安全风险。

优化方案推荐

方案一：分批次批量删除（适合在线业务，低风险）

核心思路：通过预存需保留的记录ID，按Id范围分批处理，避免全表扫描和大面积锁表。

预存需保留的最小ID
创建临时表存储所有重复分组的最小Id，避免每次删除都重新计算：

CREATE TABLE temp_keep_ids (
    UniqueFormId varchar(450) NOT NULL,
    ProductionStatusNo int NOT NULL,
    ProductionStatus varchar(45) NOT NULL,
    StatusDate date DEFAULT NULL,
    ProductionFacility varchar(450) NOT NULL,
    MinId int NOT NULL,
    PRIMARY KEY (UniqueFormId, ProductionStatusNo, ProductionStatus, StatusDate, ProductionFacility)
) ENGINE=InnoDB;

-- 分批次插入，避免一次性内存溢出，示例每次插100万组
INSERT INTO temp_keep_ids
SELECT UniqueFormId, ProductionStatusNo, ProductionStatus, StatusDate, ProductionFacility, MIN(Id) AS MinId
FROM detail
GROUP BY UniqueFormId, ProductionStatusNo, ProductionStatus, StatusDate, ProductionFacility
HAVING COUNT(*) > 1
LIMIT 1000000;
-- 重复执行上述INSERT直到所有分组插入完成

按Id范围分批删除
每次处理一个Id区间的记录，只删除不在保留列表中的数据：

SET @start_id = 0;
SET @batch_size = 1000000; -- 可根据数据库资源调整批次大小

WHILE @start_id < (SELECT MAX(Id) FROM detail) DO
    DELETE d1
    FROM detail d1
    LEFT JOIN temp_keep_ids t
        ON d1.UniqueFormId = t.UniqueFormId
        AND d1.ProductionStatusNo = t.ProductionStatusNo
        AND d1.ProductionStatus = t.ProductionStatus
        AND d1.StatusDate = t.StatusDate
        AND d1.ProductionFacility = t.ProductionFacility
        AND d1.Id = t.MinId
    WHERE d1.Id BETWEEN @start_id AND @start_id + @batch_size
      AND t.MinId IS NULL; -- 排除需保留的最小Id记录
    
    SET @start_id = @start_id + @batch_size;
    COMMIT; -- 每批次提交，避免事务过大导致锁表
END WHILE;

方案二：优化临时表替换（适合离线业务，效率更高）

如果业务允许短暂离线，可通过分段处理临时表的方式降低资源消耗，同时避免数据丢失风险：

创建与原表结构一致的临时表
直接继承原表的索引结构，省去后续重建索引的时间：
```
CREATE TABLE temp_detail LIKE detail;
```

分时间段插入去重后数据
按StatusDate分段处理，每次处理一个小时间窗口的数据，降低内存和IO压力：

-- 示例处理2023年1月的数据，循环执行覆盖所有时间段
INSERT INTO temp_detail
SELECT *
FROM (
    SELECT 
        d.*,
        ROW_NUMBER() OVER (PARTITION BY UniqueFormId, ProductionStatusNo, ProductionStatus, StatusDate, ProductionFacility ORDER BY Id) AS RowNum
    FROM detail d
    WHERE d.StatusDate BETWEEN '2023-01-01' AND '2023-01-31'
) AS ranked
WHERE RowNum = 1;

安全替换原表
用表名交换替代DROP，避免原表丢失：

RENAME TABLE detail TO detail_old, temp_detail TO detail;
-- 确认数据无误后，再删除旧表
DROP TABLE detail_old;

前置索引优化（提升所有方案效率）

创建覆盖去重字段的联合索引，让分组、窗口函数查询直接走索引扫描，无需回表：

CREATE INDEX idx_dup_group ON detail (UniqueFormId, ProductionStatusNo, ProductionStatus, StatusDate, ProductionFacility, Id);

注：创建索引需在业务低峰期或离线状态执行，10亿数据创建索引耗时较长，但后续去重效率会提升数倍。

关键注意事项

操作前必须完成全量物理备份（如用Percona XtraBackup），避免数据丢失。
在线业务优先选择分批次删除方案，批次大小需根据数据库CPU、IO资源调整，避免锁表超时。
离线业务优先选择优化后的临时表替换方案，效率更高，但需暂停表的写入操作。
处理过程中实时监控数据库资源指标，避免CPU、IO、内存耗尽。

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

针对10亿条记录的MySQL大表按指定字段去重方案问询

需求明确 #

现有方案的问题分析 #

优化方案推荐 #