单表近 7 亿条数据，现在要优化结构，进行去重，求个方案

[FONG2]

以前设计是以请求编号为主键的，现在优化后以用户编号为主键，那么就存在大量重复数据了。
现在用 sql 查一下都得等半天，请问有啥好方法处理么

大家可能歪题了，业务上，新结构表的数据为千万级别的，而且不会爆发剧增

旧表依然在，每个月一清理，大于 6 个月的数据不提供页面即时查询了

流水号即时查询只提供 6 个月内的

大家专注一下怎么去重数据，下周定个方案开撸了

[liuxu]

楼下，多列覆盖索引能救 7 亿吗

[fixend]

去重的规则是啥？随意选择一条？用 insert into `table2` select * from `table1` group by `id` 这样？
ID 必须是建一个非唯一索引，但估计也得跑很久很久很久 * N

[newtype0092]

楼上分个表不是美滋滋？

[fixend]

@liuxu 不行

@newtype0092 不美

[reus]

这还要问？除了加索引你还能干嘛？自己不会试验吗？要不你给个 ssh 让大家连上去给你优化优化？

[Egfly]

把原表 copy 一份，然后建个临时表，慢慢处理完，再替换原表？

[dog82]

写代码清洗插入新表，写 sql 估计不行

[FONG2]

@dog82 写代码，我感觉也得跑几天几夜。。。现在执行一条去重 sql 都跑不出结果

[FONG2]

@reus 之前求了个 datax 迁移数据 很好用！现在也来看看有没有类似工具。索引原来就有，不过没啥效果 量太大

[wysnylc]

弄个大数据框架,慢慢洗
单靠数据库单表是做不到的

[FONG2]

@wysnylc 我也感觉 大数据是唯一出路了。。。

[bugsnail]

不要当这个表有 7 亿，你就当 7 百万数据，然后写个程序处理，写好之后，再把这个表分成 100 份就好，程序处理的结果放另外一张表里，这样估计是最好的处理方法了

[Lonersun]

1 、建立一张新表，用户编号为主键唯一索引；
2 、业务代码从老表读每一条数据，读到直接往新表插入，能插进去说明新表不存在，插不进去说明已存在，直接跳过
3 、跑完后用新表代替老表
如果不考虑数据实时更新的话这样是否可行？

[bojue]

@FONG2 之前听鹅厂的人说，7 亿数据只是备份花了一晚上

[FONG2]

@bugsnail 这样每次得出结果还是得不断去重吧，那么只是每次时间变短，最终时间不也差不多吗？

[ysweics]

1.上大数据清洗
2.好奇是什么数据，如果单表 7 亿数据，那么在单表 1 亿数据的时候就应该考虑后续方案了，盲猜是请求日志记录

[FONG2]

@Lonersun 可行是可行 感觉效率不行
不过我可以配合 datax 试试

[FONG2]

@ysweics yes 流水号 记录

[Leanna]

流水表还是要定期迁数据比较好

[FONG2]

@Leanna 我这很多系统搞不好比我学龄还长 不是出问题要处理 我都不知道有这个表存在

[liuxu]

@Lonersun 这个搞法岂不是得 7 亿个磁盘随机 io，要是是机械硬盘，一次 2-3 毫秒，假设 2 毫秒，700,000,000/86400/500=16 天。。

分析了一下，我建议这样：
1.新建个没有键的表（插入会更快）
2. 从原表通过主键一次性顺序读 1 万-10 万行
3.利用程序语言的 set 数据结构清洗数据
4.清晰后的数据拼接成一条或者几条 insert 插入新表
5.回到 2 循环执行到结束
6.在新表中给用户编号列创建唯一索引

[enjoyCoding]

建一张表 所有列组合不可重复 然后把这张表的数据插入到那张表里,忽略数据库报错可行么

[FONG2]

@liuxu
前 10w 条包含 好几个 张三 去重了
接下来 10w 条还有张三
那插到新表是不是还有重复？

[xuanbg]

重复数据不要了？

[jones2000]

直接把数据导入到 ES 里面， 通过 ES 来查吧， 慢就加节点机器。这点数据对 ES 来说不算什么。

[liuxu]

@FONG2 没想到这点，那这样

1.新建表 new_table，用户编号 user_id 为唯一索引
2. 从原表通过主键一次性顺序读 1 万-10 万行
3.利用程序语言的 set 数据结构清洗数据
4.清洗后的数据拼接成一条或者几条 insert 插入新表
先 select user_id from new_table where user_id not in (清洗后数据中的用户 user_id1,2,3,4...)
然后再 insert 批量插入

5.回到 2 循环执行到结束
6.在新表中给用户编号列创建唯一索引

那程序清洗时用 redis 的 set 数据结构缓存用户序列号，或者就用程序内部缓存，
看看 php 的 swoole，https://wiki.swoole.com/wiki/page/p-table.html，不过这种方案需要的内存很大

[lscho]

@liuxu 你这个第 4 条 not in 性能堪忧啊。。。既然都用上内存缓存了，直接把 userid 存内存不好吗。

[makdon]

感觉这个需求用 map reduce 还是很好做的

[makdon]

@makdon 建一个新库表，从旧表读所有数据，用 map reduce 去重，写去新表。需要考虑去重期间新写入的数据的临时存放。

[liuxu]

@lscho 我最下面不是说了么，也可以清洗程序放自己的内存缓存，就是内存消耗有点大

[kethylar]

单条数据大吗，不大全部弄出来放到文本文件也就几个 G 到几十个 G 而已，然后当作文本文件写脚本处理完之后再批量写到新表完事

[nilai]

迁移到 tidb 中

[walkman660]

7 亿条数据分成 N 份，找个性能好点得服务器并行处理每份数据去重
结果再每 2 份合并服务器上并行去重

理论上要比一次排序去重快
7 亿条数据一次处理资源上很容易遇到瓶颈

[fs20]

单表 7 亿，不愿意分表，单从 sql 查询效率角度而已，就没考虑过分区?

[micolore]

写代码分批取，然后再进行汇总，再进行去重应该不会很久。这是纯测试方案，没考虑线上一系列乱七八糟的因素。

[CRDarwin]

数据导出来，直接丢在 hive 里

[wangyzj]

首先考虑需求把
我觉得用户 id 作为主键还去重那不就是会扔掉不少数据？
这是为啥

[hantsy]

看使用场景了，一般比如银行，可能查自己半年内的记录吧。可以另外用一个数据库，保存完整数据。主数据库只保留近期数据。当然这个看需求是否满足，一般查询，仅使用主数据库查询快，历史记录数据针对用户想自定日期义查询。

[goldenalex]

去重
优化查询方法
优化数据结构

rm -rf /* 考虑一下？

[woscaizi]

把主键从请求编号变为用户编号；
这样同一个用户的多次请求怎么办？

[zouqiang]

实现一个 Bloom Filter

[saulshao]

首先建议要分表，不然你建立的这个新表仍然没法保证查询效率。并且如果考虑数据增长，最终你也没法处理这么巨大的数据量。

[joesonw]

建个新表, 弄个队列写个处理慢慢导呗. 插数据的时候也顺手丢队列.

[william2ct]

先不说怎么去重，我就问问，是不是国资企业

[FONG2]

@william2ct 答对了 但是没有奖励

[FONG2]

@woscaizi
@saulshao
@wangyzj
@xuanbg
看 APPEND 吧

[stevenkang]

建议用 SQL 跑吧，慢点就慢点，用程序跑更慢，亲身经历。

之前一台游戏日志数据库，单表 2 亿级别，用程序跑从晚上 2 点跑到早上 8 点多。
后面改成 SQL 将查询结果直接 insert 到另外一张表，一条 SQL 语句就搞定，时间缩短到 1-2 个小时就能跑完。

[ConradG]

标准手势是拆分后归并排序

[nwg]

定时任务 批量查询--判断重复--插入新分表

[fashy]

没太明白意思,是说优化后是根据用户编号去重么?一个用户编号只保存一条记录?
假设请求编号和用户编号都是 long 类型可以表示的话,(字符串的话 hash 可能会有冲突),用两个 bitmap 怎么样?
7 亿 /1024/1024/1024/8,一个 bitmap1g 左右内存就能放下了
第一个放每行记录的用户编号,重复的忽略,不重复的把该行记录的请求编号放到第二个 bitmap 对应的位置
最终第二个 bitmap 里面被标记的 index(原表里面的主键),就可以认为是去重后的数据记录了
然后批量读取这些 index 对应的数据,直接往新表里面插
最坏的情况,请求编号和用户编号都是唯一的,去重过程也就只需要 2G 左右的内存

[myCupOfTea]

hash 分区啊

[myCupOfTea]

我之前的项目是收集几千个网站数据的,按照网站编码 hash 分了 1000 个区,反正每次查询都需要带上网站编码,飞快
分区首先要有一个指标可以用来区分数据,其次每次查询必须带上它,那你就可以用分区呢

[xupefei]

bloom filter 过一遍，疑似重复的条目存下来用哈希表检查。

[cs419]

3 、5 台机器的集群
spark 或者 flink 跑一下
估计顶多一两个小时 就搞定了

[nicebird]

后台慢慢跑喽

[pabno]

不知道去重后的数据量有多少？
如果比较少的话，可以考虑创建一张新表，根据去重规则创建唯一索引，写给程序不断的从旧表 load 数据并插入到新表中，使用 ingore insert 。
如果数据比较多的话，依然可以使用这种方式，只不过要做多一层分表

[laminux29]

看了一下楼上的回答，把问题都限定在数据库产品与对应的操作方法上了。

其实大家应该跳出这个局限性。

题主的 7 亿条数据，本质是，一堆已经限定在特定硬件，特定算法所构建的环境里的数据结构了。

因此，按照数据结构与算法的基础处理方法，得先了解硬件特性，然后再来构造处理算法。而且还要考虑目前数据已经处在环境里的数据结构的特殊性。

在题主还没有交代物理设备与具体需求之前，我觉得大家不应该急着给出解决方案。

[encro]

题目没出清楚：

“以前设计是以请求编号为主键的，现在优化后以用户编号为主键，那么就存在大量重复数据了。”

主键能重复？还是你的主键意思是主要查询条件？

感觉以前类似你 7 亿订单，用订单 ID 查询即可，现在要建立一个用户表 5000 万用户数据，根据用户 ID 查询出用户信息，然后需要用用户 ID 去查用户所有订单。如果是这样可以根据用户 ID 建立分区表或者直接根据用户 ID 或者时间分表。

或者直接采用 es，想查几个月就几个月。