前言
最近工作遇到个问题。有一个表的数据是从多个excel导入的,但是导入的数据中可能会出现重复数据,整个数据表的数据量又特别大,查找于是怎么删除重复数据对我来说就是个头疼的问题。
网上查过很多资料,但是可能是因为我这个数据量太大的情况,不太适用,按照他们的方法代码,我先select要删除的数据先查看一下,但是执行半天都没有反应。
后面请教了个大佬,按照她的方法处理了下,很快就完成了。
先上截图
在600多万的数据中删除30多万的数据,只要111秒,可以说是比较快了。
目标
删除import_company表中【code】相同的数据,只保留一条
方法
1) 在表中添加id字段,可以是uuid,也可以是1,2,3,4。只要唯一就可以,用于区分数据的。已经有id的可以直接看下一步
添加id的语句如下,这样就能从1开始生成数字的赋值给id
-- 给表添加id
SET @row_number = 0;
UPDATE `import_company`
SET id = (@row_number := @row_number + 1);
2)给id和要检测重复的字段(code)添加索引
2)查看一下要删除的数据
SELECT code, count(code), min(id) FROM `import_company` GROUP BY `code` HAVING count(code) > 1;
上面筛选出来的就是重复的数据,这里就是我们要删除的数据,这里也能简单计算到要删除的总数量
其中code就是我们要删除的编码,min(id) 是我们要保留的的数据id(因为要保留一条,我这选的是保留最小的id,当然你也可以保留最大id)
3) 创建两个临时表
temp_delete_code 用于存放要删除的code 。字段如下,需要添加索引:
temp_keep_id 用于存放不能删除的id。字段如下,需要添加索引:
这个比较关键:用外部表做承接,并添加索引。可以大大减少查询时间
4) 插入数据
往temp_delete_code表中插入要删除的code
insert into temp_delete_code (delete_code)
SELECT code FROM `import_company` GROUP BY `code` HAVING count(code) > 1;
往temp_keep_id插入不能删除的id(也就是我说的min(id))
insert into temp_keep_id (keep_id)
SELECT min(id) FROM `import_company` GROUP BY `code` HAVING count(code) > 1;
5) 查看要删除的数据
删除是很慎重的操作,因为最好删除数据前都要看一下删除的数据。
因为数据量比较大,如果全部查看的话比较慢,我就看一下有多少条就好了
select count(1) from import_company WHERE `code` in (select delete_code from temp_delete_code) and id not in (select keep_id from temp_keep_id);
这里和在第二步简单计算的结果一致,没啥问题,现在把 `select count(1)` 改成delete开始删除,等sql语句结束就好了
delete from import_company WHERE `code` in (select delete_code from temp_delete_code) and id not in (select keep_id from temp_keep_id);
6)检查
到了这一步就搞定了,但严谨起见还是要检查下
执行第2步的代码
SELECT code, count(code), min(id) FROM `import_company` GROUP BY `code` HAVING count(code) > 1;
如果返回是空,说明删除完毕,表中已无重复数据
收工。
写在最后
主表和临时表一定要加索引,能极大加快查询速度。不要舍不得加,觉得浪费资源,大不了后面再删了就是。