MySQL表上亿级数据量实现删除重复记录

女爷i 2024-02-19 00:22 38阅读 0赞

上周从数据采集部门拿到一批400份的json文件,每个文件里30w+的json对象,对象里有uid,对重复的uid,需要去重下.

电脑配置4核8G

废话不多说,直接上干货.

**1.创建表datatest5**

CREATE TABLE `datatest5` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `val` varchar(16) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `val` (`val`)
    ) ENGINE=InnoDB AUTO_INCREMENT=57667550 DEFAULT CHARSET=utf8;

1.1 id是表的主键自增,val是需要去重的数据,为val建立索引 (建立索引,建立索引,建立索引)!!!

1.2 将12000w的uid从入到datatest5表中.(刚开始的时候20分钟跑完,建立索引后大概3个小时多,而且磁盘一直80%多)

**2. 设置innodb缓冲池大小**

2.1

show variables like "%_buffer%";-- 先查看下

2.2

SET GLOBAL innodb_buffer_pool_size=8388608; -- 系统值(保存起来,结束后修改回来)
    SET GLOBAL innodb_buffer_pool_size=109051904;-- 修改值

**3. 查询datatest5表总数据量**

SELECT COUNT(0) ct from datatest5; -- 127351895

**4. 查询分组后的表条数也就是我们的需求**

SELECT COUNT(DISTINCT val) ct FROM datatest5;-- 97267435

**5.创建datatest5\_1表数据为重复的val和其对应id最大值**

CREATE TABLE datatest5_1 AS ( SELECT a.val, MAX(a.id) maxid FROM datatest5 a, ( SELECT val FROM datatest5 GROUP BY val HAVING count(val) > 1 ) b WHERE a.val = b.val GROUP BY val ) ;

**6.删除原datatest5表中所有重复的数据**

DELETE a FROM datatest5 a,datatest5_1 a5 WHERE a.id<a5.maxid AND a.val=a5.val ;

**7.统计表datatest5中的数据**

SELECT COUNT(0) ct from datatest5 ; -- 97267435

**8.对比第7步和第4步的结果,若一样就证明删除重复数据成功.**

一共用时53分钟.

注意:

1.表一定要给重复值的字段建立索引

2.数据清洗完之后磁盘还是占用80%多的话,可以恢复innodb缓冲池大小或者将数据转存文件并删除datatest5表就好了.

借鉴文章

[https://blog.qxzzf.com/135][https_blog.qxzzf.com_135]

[https://blog.csdn.net/wuyang528378/article/details/49762875][https_blog.csdn.net_wuyang528378_article_details_49762875]

[https://zhidao.baidu.com/question/313665822.html][https_zhidao.baidu.com_question_313665822.html]

[https_blog.qxzzf.com_135]: https://blog.qxzzf.com/135
[https_blog.csdn.net_wuyang528378_article_details_49762875]: https://blog.csdn.net/wuyang528378/article/details/49762875
[https_zhidao.baidu.com_question_313665822.html]: https://zhidao.baidu.com/question/313665822.html