600万条数据删除重复记录SQL查询优化：保留单条重复项提速

你现在用的自连接删除查询，在600万条数据量下会产生海量笛卡尔积计算，这是跑几个小时的主要原因。下面给你几个更高效的实现方法：

方案一：用窗口函数快速标记重复记录（MySQL 8.0+ 及支持窗口函数的数据库适用） #

用ROW_NUMBER()给每组重复的记录（按MName、SName、Date/Time分组）编号，保留编号为1的那条（这里按id排序，保留id最小的，你也可以改排序规则保留最大的），直接删掉编号大于1的记录，这种方式不用做大规模自连接，速度快很多：

WITH dup_records AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (
            PARTITION BY MName, SName, `Date/Time` 
            ORDER BY id
        ) AS row_num
    FROM r.test
)
DELETE FROM r.test
WHERE id IN (SELECT id FROM dup_records WHERE row_num > 1);

如果你的数据库不支持CTE（公共表表达式），换成嵌套子查询也可以：

DELETE FROM r.test
WHERE id IN (
    SELECT id FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (
                PARTITION BY MName, SName, `Date/Time` 
                ORDER BY id
            ) AS row_num
        FROM r.test
    ) AS temp_table
    WHERE row_num > 1
);

方案二：临时表存唯一记录再替换原表（适合低版本MySQL） #

这种方法逻辑简单，而且避免了逐行删除的开销，适合超大表：

1. 先建一个和原表结构一样的临时表，把每组重复记录里要保留的那条（比如id最小的）插进去：

CREATE TABLE r.test_temp LIKE r.test;
INSERT INTO r.test_temp
SELECT * FROM r.test
WHERE id IN (
    SELECT MIN(id)
    FROM r.test
    GROUP BY MName, SName, `Date/Time`
);

2. 清空原表，把临时表的数据导回去：

TRUNCATE TABLE r.test;
INSERT INTO r.test SELECT * FROM r.test_temp;

3. 最后删掉临时表就行：

DROP TABLE r.test_temp;

方案三：给重复字段加索引优化原查询（应急用） #

如果一定要用你原来的查询逻辑，先给MName, SName, Date/Time这三个字段加个联合索引，能减少自连接时的匹配时间，多少能快一点，但还是不如前两种方案高效：

CREATE INDEX idx_duplicate_fields ON r.test(MName, SName, `Date/Time`);

重要提醒 #

• 不管用哪种方法，先备份数据！删错了可没地方哭。
• 生产环境尽量在业务低峰期操作，别影响正常服务。

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