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

一尘不染

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

mysql

我有大的（>百万行）MySQL数据库被重复弄乱了。我认为这可能是充满它们的整个数据库的1/4到1/2。我需要快速摆脱它们（我是指查询执行时间）。外观如下：
id（索引）| text1 | text2 | text3
text1＆text2组合应该是唯一的，如果有重复项，则仅应保留一个text3 NOT NULL组合。例：

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分钟）

有什么更快的方法吗？请帮我解决这个问题。

阅读 289

2020-05-17

共1个答案

一尘不染

我相信使用重复键+ 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;

应该比任何需要分组依据或不重复或子查询甚至排序依据的速度都要快得多。这甚至不需要文件排序，这将破坏大型临时表的性能。仍然需要对原始表进行全面扫描，但这是不可避免的。

2020-05-17