MySQL快速删除大型数据库中的重复项

Question

我有一个MySQL数据库，数据量很大（>百万行），里面有很多重复的数据。整个数据库中可能有四分之一到一半的数据都是重复的。 我需要快速处理它们（也就是查询执行时间要快）。 下面是数据库表格的样式：
id（索引）| text1 | text2 | text3
text1和text2的组合应该是唯一的，如果有任何重复的组合，只保留一个text3不为空的组合。例如：

1 | abc | def | NULL  
2 | abc | def | ghi  
3 | abc | def | jkl  
4 | aaa | bbb | NULL  
5 | aaa | bbb | NULL  

...变成：

1 | abc | def | ghi   #(doesn't realy matter id:2 or id:3 survives)   
2 | aaa | bbb | NULL  #(if there's no NOT NULL text3, NULL will do)

新的id可以是任何值，它们不依赖于旧表的id。
我尝试过以下方法:

CREATE TABLE tmp SELECT text1, text2, text3
FROM my_tbl;
GROUP BY text1, text2;
DROP TABLE my_tbl;
ALTER TABLE tmp RENAME TO my_tbl;

或者使用SELECT DISTINCT和其他变体。
虽然它们在小型数据库上运行良好，但在我的数据库上，查询执行时间非常长（实际上从未结束，超过20分钟）。

有没有更快的方法呢？请帮助我解决这个问题。

- bizzz

---

请说明：a）是否需要重新编号id字段？ b）我们预计有多少重复项的数量或比例？（这有助于决定在原地操作还是创建新表）c）当前表上存在哪些索引。 - mjv

a) 重新编号ID字段并非必要。 b) 我的估计是，数据库中有1/4到1/2是重复的。 c) ID是唯一的索引。 - bizzz

Answer 1

我相信这个代码可以实现，使用了 on duplicate key + ifnull()：

create table tmp like yourtable;

alter table tmp add unique (text1, text2);

insert into tmp select * from yourtable 
    on duplicate key update text3=ifnull(text3, values(text3));

rename table yourtable to deleteme, tmp to yourtable;

drop table deleteme;

这种方法比任何需要使用 group by、distinct、子查询甚至 order by 的方法都要快得多。它甚至不需要进行文件排序，在大型临时表上，这会影响性能。仍然需要完全扫描原始表，但无法避免。

- ʞɔıu

---

谢谢，它有效！120万行数据在60分钟内被压缩到了60万行，所以每分钟大约写入了10000行。感谢您清晰的解释！:) - bizzz

这真是帮了大忙。谢谢。 - rpearce

对于那些不关心文本3非空部分的人，可以使用INSERT IGNORE（不考虑ON DUPLICATE UPDATE部分），MySQL会忽略错误并只插入它找到的第一个不同的值（忽略后续的重复）。@ʞɔıu（upsideDownNick）简单而有效。 - tony gil

+1 这是一个聪明的解决方案。在我的情况下，客户端大约10分钟后失去与服务器的连接（表中有4500万条记录），导致混乱的锁被打开等问题。您有什么建议如何处理这种情况吗？ - Matt

如果有人感兴趣，我已在 https://dev59.com/9HA75IYBdhLWcg3waIMJ#47392593 上通过进一步的用例扩展了 @ʞɔıu 的回答。 - César Revert-Gomar

Answer 2

我找到了一行简单的代码，正好可以满足我的需求：

ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);

翻译自: http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

- liorq

---

似乎是MySQL的一个bug导致你的查询（特别是IGNORE部分）无法正常工作： 错误代码：1062 键值为“text1”的记录“abc-def”重复。 - bizzz

如果你遇到了那个错误，只需运行set session old_alter_table=1，然后再尝试一次即可。 - Matthew

这在BLOB/TEXT列上无法工作。它会出现错误“在没有键长度的情况下在关键规范中使用了BLOB/TEXT列'name'”。 - Dashrath

从MySQL 5.7.4版本开始，ALTER TABLE的IGNORE子句已被移除，并且使用它将会产生错误。来源。 - Tyler

Answer 3

DELETE FROM dups
WHERE id NOT IN(
    SELECT id FROM (
        SELECT DISTINCT id, text1, text2
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC
    ) as tmp
)

这个查询检索所有记录，按区别字段分组并按ID排序（意味着我们选择第一个非空的text3记录）。然后我们从结果中选择ID（这些是好的ID...它们不会被删除），并删除那些不是这些ID的所有ID。

任何像这样影响整个表的查询都会很慢。你只需要运行它并让它执行，这样你就可以在将来防止它。

在完成这个“修复”之后，我建议为该表应用唯一索引（text1、text2）。以防止将来出现重复。

如果您想采用“创建新表并替换旧表”的方法，您可以使用最内层的select语句来创建您的insert语句。

仅适用于MySQL（假设新表名为my_tbl2，并具有完全相同的结构）：

INSERT INTO my_tbl2
    SELECT DISTINCT id, text1, text2, text3
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC

请查看MySQL INSERT ... SELECT以获得更多信息。

- Kevin Peno

---

抱歉，您的建议都可以删除重复项，但没有选择正确的文本3字段来保留（在存在非空替代项的情况下仍然存在空值）。 - bizzz

Answer 4

删除重复项而不删除外键

create table tmp like mytable;
ALTER TABLE tmp ADD UNIQUE INDEX(text1, text2, text3, text4, text5, text6);
insert IGNORE into tmp select * from mytable;
delete from mytable where id not in ( select id from tmp);

- Gadelkareem

---

这应该是正确的答案。简单而且实用。 - PKHunter

Answer 5

如果您可以创建一个新表，在text1 + text2字段上加入唯一键。然后使用“INSERT IGNORE”语法将内容插入到该表中，忽略错误。

select * from my_tbl order by text3 desc

• 我认为按照 text3 降序排序会将 NULL 值放在最后，但请再确认一下。

所有这些列上的索引都可以大大提高性能，但是现在创建它们可能会相当慢。

- Scott Saunders

---

它会将空值放在最后，但这并不满足要求，即“保留第一个text3中没有空值的记录”。为此，您需要按ID升序排序，并在语句中添加WHERE text3 IS NOT NULL。 - Kevin Peno

这是一个很好的观点。但是这个要求与他的样例输出相矛盾： 2 | aaa | bbb | NULL 也许他会告诉我们他真正想要什么。 - Scott Saunders

我重新阅读了他的请求。看起来他并不在意，只要非空值保持不变就可以了。所以你的例子很合适。 :) - Kevin Peno

谢谢，工作正常。处理1.2百万行数据大约需要3个小时；平均每分钟写入4000行。它会保留具有最大text3字段的重复数据，并对应我的数据库逻辑。 - bizzz

Answer 6

对于有很多重复项的大型表格，您可能希望避免将整个表格复制到另一个位置。一种方法是创建一个临时表格，保存您想要保留的行（对于每个具有重复项的关键字），然后从原始表格中删除重复项。

这里提供了一个示例链接。

- user1931858

Answer 7

您可以使用这个简单的查询来删除所有重复的条目。 它将选择所有重复的记录并将它们删除。

 DELETE i1 
FROM TABLE i1
LEFT JOIN TABLE i2
  ON i1.id = i2.id
 AND i1.colo = i2.customer_invoice_id
 AND i1.id < i2.id
WHERE i2.customer_invoice_id IS NOT NULL

- kamran Sheikh

Answer 8

我知道这是一个旧的帖子，但我有一个有点混乱的方法，它更快且可定制，在速度方面，我会说10秒而不是100秒（10：1）。

我的方法确实需要所有你试图避免的混乱的东西：

• 按组分组（和Having）
• 带ORDER BY的group concat
• 2个临时表
• 使用磁盘上的文件！
• 以某种方式（php？）在之后删除文件

但是当你谈论数百万（或者像我一样数千万）时，这是值得的。

无论如何，这并不多，因为评论是葡萄牙语，但这是我的示例：

编辑：如果我收到评论，我会进一步解释它的工作原理：）

START TRANSACTION;

DROP temporary table if exists to_delete;

CREATE temporary table to_delete as (
    SELECT
        -- escolhe todos os IDs duplicados menos os que ficam na BD
        -- A ordem de escolha dos IDs é dada por "ORDER BY campo_ordenacao DESC" em que o primeiro é o que fica
        right(
            group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ','),
            length(group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ',')) 
                - locate(",",group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ','))
        ) as ids,

        count(*) as c

    -- Tabela a eliminar duplicados
    FROM teste_dup

    -- campos a usar para identificar  duplicados
    group by test_campo1, test_campo2, teste_campoN
    having count(*) > 1 -- é duplicado
);

-- aumenta o limite desta variável de sistema para o máx 
SET SESSION group_concat_max_len=4294967295;

-- envia os ids todos a eliminar para um ficheiro
select group_concat(ids SEPARATOR ',') from to_delete INTO OUTFILE 'sql.dat';

DROP temporary table if exists del3;
create temporary table del3 as (select CAST(1 as signed) as ix LIMIT 0);

-- insere os ids a eliminar numa tabela temporaria a partir do ficheiro
load data infile 'sql.dat' INTO TABLE del3
LINES TERMINATED BY ',';

alter table del3 add index(ix);

-- elimina os ids seleccionados
DELETE teste_dup -- tabela 
from teste_dup -- tabela

join del3 on id=ix;

COMMIT;

- JDuarteDJ

Answer 9

我对MySQL没有太多经验。如果它有分析函数，请尝试：

delete from my_tbl
 where id in (
     select id 
       from (select id, row_number()
                            over (partition by text1, text2 order by text3 desc) as rn
               from my_tbl
               /* 可选：where text1 like 'a%'  */
             ) as t2
       where rn > 1
     )

可选的where子句意味着您需要多次运行它，每个字母运行一次等。在text1上创建一个索引？

在运行此操作之前，请确认“text desc”将在MySQL中将null排序为最后。

- redcayuga

---

抱歉，错误代码：1064，附近有“(partition by...)”。 - bizzz

我猜 MySql 没有分析函数。我稍后再试。 - redcayuga

你能运行以下代码吗：create table dups as SELECT text1, text2 , max(case when text3 is null then 1 else 0) as has_null3 , max(case when text3 is not null then 1 else 0) as has_not_null3 , min(case when text3 is not null then id else null) as pref_id FROM my_tbl GROUP BY text1, text2 having count(*) > 1这将给我们重复的text1/2列表和一些“首选”id。如果执行时间太长，很可能会这样，可以添加“where text1 like 'a%'”或类似的条件。 - redcayuga