V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
FONG2
V2EX  ›  数据库

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

  •  
  •   FONG2 · 2020-05-29 10:37:09 +08:00 · 5695 次点击
    这是一个创建于 607 天前的主题,其中的信息可能已经有所发展或是发生改变。
    以前设计是以请求编号为主键的,现在优化后以用户编号为主键,那么就存在大量重复数据了。
    现在用 sql 查一下都得等半天,请问有啥好方法处理么
    第 1 条附言  ·  2020-05-29 17:37:19 +08:00
    大家可能歪题了,业务上,新结构表的数据为千万级别的,而且不会爆发剧增

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

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

    大家专注一下怎么去重数据,下周定个方案开撸了
    58 条回复    2020-05-30 15:12:18 +08:00
    liuxu
        1
    liuxu  
       2020-05-29 10:46:19 +08:00
    楼下,多列覆盖索引能救 7 亿吗
    fixend
        2
    fixend  
       2020-05-29 10:54:12 +08:00
    去重的规则是啥?随意选择一条?用 insert into `table2` select * from `table1` group by `id` 这样?
    ID 必须是建一个非唯一索引,但估计也得跑很久很久很久 * N
    newtype0092
        3
    newtype0092  
       2020-05-29 10:54:46 +08:00
    楼上分个表不是美滋滋?
    fixend
        4
    fixend  
       2020-05-29 10:56:14 +08:00
    @liuxu 不行

    @newtype0092 不美
    reus
        5
    reus  
       2020-05-29 10:57:36 +08:00 via Android
    这还要问?除了加索引你还能干嘛?自己不会试验吗?要不你给个 ssh 让大家连上去给你优化优化?
    Egfly
        6
    Egfly  
       2020-05-29 10:59:17 +08:00
    把原表 copy 一份,然后建个临时表,慢慢处理完,再替换原表?
    dog82
        7
    dog82  
       2020-05-29 11:01:59 +08:00
    写代码清洗插入新表,写 sql 估计不行
    FONG2
        8
    FONG2  
    OP
       2020-05-29 11:08:08 +08:00
    @dog82 写代码,我感觉也得跑几天几夜。。。现在执行一条去重 sql 都跑不出结果
    FONG2
        9
    FONG2  
    OP
       2020-05-29 11:09:27 +08:00
    @reus 之前求了个 datax 迁移数据 很好用!现在也来看看有没有类似工具。索引原来就有,不过没啥效果 量太大
    wysnylc
        10
    wysnylc  
       2020-05-29 11:10:39 +08:00
    弄个大数据框架,慢慢洗
    单靠数据库单表是做不到的
    FONG2
        11
    FONG2  
    OP
       2020-05-29 11:11:13 +08:00
    @wysnylc 我也感觉 大数据是唯一出路了。。。
    bugsnail
        12
    bugsnail  
       2020-05-29 11:13:56 +08:00
    不要当这个表有 7 亿,你就当 7 百万数据,然后写个程序处理,写好之后,再把这个表分成 100 份就好,程序处理的结果放另外一张表里,这样估计是最好的处理方法了
    Lonersun
        13
    Lonersun  
       2020-05-29 11:14:43 +08:00
    1 、建立一张新表,用户编号为主键唯一索引;
    2 、业务代码从老表读每一条数据,读到直接往新表插入,能插进去说明新表不存在,插不进去说明已存在,直接跳过
    3 、跑完后用新表代替老表
    如果不考虑数据实时更新的话这样是否可行?
    bojue
        14
    bojue  
       2020-05-29 11:17:53 +08:00
    @FONG2 之前听鹅厂的人说,7 亿数据只是备份花了一晚上
    FONG2
        15
    FONG2  
    OP
       2020-05-29 11:20:07 +08:00
    @bugsnail 这样每次得出结果还是得不断去重吧,那么只是每次时间变短,最终时间不也差不多吗?
    ysweics
        16
    ysweics  
       2020-05-29 11:22:06 +08:00
    1.上大数据清洗
    2.好奇是什么数据,如果单表 7 亿数据,那么在单表 1 亿数据的时候就应该考虑后续方案了,盲猜是请求日志记录
    FONG2
        17
    FONG2  
    OP
       2020-05-29 11:23:34 +08:00
    @Lonersun 可行是可行 感觉效率不行
    不过我可以配合 datax 试试
    FONG2
        18
    FONG2  
    OP
       2020-05-29 11:28:21 +08:00
    @ysweics yes 流水号 记录
    Leanna
        19
    Leanna  
       2020-05-29 11:34:26 +08:00
    流水表还是要定期迁数据比较好
    FONG2
        20
    FONG2  
    OP
       2020-05-29 11:39:56 +08:00
    @Leanna 我这很多系统搞不好比我学龄还长 不是出问题要处理 我都不知道有这个表存在
    liuxu
        21
    liuxu  
       2020-05-29 11:43:35 +08:00
    @Lonersun 这个搞法岂不是得 7 亿个磁盘随机 io,要是是机械硬盘,一次 2-3 毫秒,假设 2 毫秒,700,000,000/86400/500=16 天。。

    分析了一下,我建议这样:
    1.新建个没有键的表(插入会更快)
    2. 从原表通过主键一次性顺序读 1 万-10 万行
    3.利用程序语言的 set 数据结构清洗数据
    4.清晰后的数据拼接成一条或者几条 insert 插入新表
    5.回到 2 循环执行到结束
    6.在新表中给用户编号列创建唯一索引
    enjoyCoding
        22
    enjoyCoding  
       2020-05-29 11:46:51 +08:00
    建一张表 所有列组合不可重复 然后把这张表的数据插入到那张表里,忽略数据库报错可行么
    FONG2
        23
    FONG2  
    OP
       2020-05-29 11:53:32 +08:00 via iPhone
    @liuxu
    前 10w 条包含 好几个 张三 去重了
    接下来 10w 条还有张三
    那插到新表是不是还有重复?
    xuanbg
        24
    xuanbg  
       2020-05-29 11:56:05 +08:00
    重复数据不要了?
    jones2000
        25
    jones2000  
       2020-05-29 11:56:35 +08:00
    直接把数据导入到 ES 里面, 通过 ES 来查吧, 慢就加节点机器。这点数据对 ES 来说不算什么。
    liuxu
        26
    liuxu  
       2020-05-29 12:20:37 +08:00
    @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
        27
    lscho  
       2020-05-29 12:53:27 +08:00 via iPhone
    @liuxu 你这个第 4 条 not in 性能堪忧啊。。。既然都用上内存缓存了,直接把 userid 存内存不好吗。
    makdon
        28
    makdon  
       2020-05-29 12:54:47 +08:00
    感觉这个需求用 map reduce 还是很好做的
    makdon
        29
    makdon  
       2020-05-29 12:55:55 +08:00
    @makdon 建一个新库表,从旧表读所有数据,用 map reduce 去重,写去新表。需要考虑去重期间新写入的数据的临时存放。
    liuxu
        30
    liuxu  
       2020-05-29 13:02:08 +08:00
    @lscho 我最下面不是说了么,也可以清洗程序放自己的内存缓存,就是内存消耗有点大
    kethylar
        31
    kethylar  
       2020-05-29 13:07:24 +08:00 via Android
    单条数据大吗,不大全部弄出来放到文本文件也就几个 G 到几十个 G 而已,然后当作文本文件写脚本处理完之后再批量写到新表完事
    nilai
        32
    nilai  
       2020-05-29 13:21:04 +08:00
    迁移到 tidb 中
    walkman660
        33
    walkman660  
       2020-05-29 14:32:03 +08:00
    7 亿条数据分成 N 份,找个性能好点得服务器并行处理每份数据去重
    结果再每 2 份合并服务器上并行去重

    理论上要比一次排序去重快
    7 亿条数据一次处理资源上很容易遇到瓶颈
    fs20
        34
    fs20  
       2020-05-29 15:00:04 +08:00
    单表 7 亿,不愿意分表,单从 sql 查询效率角度而已,就没考虑过分区?
    micolore
        35
    micolore  
       2020-05-29 15:06:33 +08:00
    写代码分批取,然后再进行汇总,再进行去重应该不会很久。这是纯测试方案,没考虑线上一系列乱七八糟的因素。
    CRDarwin
        36
    CRDarwin  
       2020-05-29 15:13:02 +08:00
    数据导出来,直接丢在 hive 里
    wangyzj
        37
    wangyzj  
       2020-05-29 15:15:55 +08:00
    首先考虑需求把
    我觉得用户 id 作为主键还去重那不就是会扔掉不少数据?
    这是为啥
    hantsy
        38
    hantsy  
       2020-05-29 15:19:41 +08:00
    看使用场景了,一般比如银行,可能查自己半年内的记录吧。可以另外用一个数据库,保存完整数据。主数据库只保留近期数据。当然这个看需求是否满足,一般查询,仅使用主数据库查询快,历史记录数据针对用户想自定日期义查询。
    goldenalex
        39
    goldenalex  
       2020-05-29 15:29:10 +08:00
    去重
    优化查询方法
    优化数据结构

    rm -rf /* 考虑一下?
    woscaizi
        40
    woscaizi  
       2020-05-29 15:38:55 +08:00
    把主键从请求编号变为用户编号;
    这样同一个用户的多次请求怎么办?
    zouqiang
        41
    zouqiang  
       2020-05-29 15:39:49 +08:00
    实现一个 Bloom Filter
    saulshao
        42
    saulshao  
       2020-05-29 15:55:32 +08:00
    首先建议要分表,不然你建立的这个新表仍然没法保证查询效率。并且如果考虑数据增长,最终你也没法处理这么巨大的数据量。
    joesonw
        43
    joesonw  
       2020-05-29 16:12:13 +08:00
    建个新表, 弄个队列写个处理慢慢导呗. 插数据的时候也顺手丢队列.
    william2ct
        44
    william2ct  
       2020-05-29 17:34:56 +08:00
    先不说怎么去重,我就问问,是不是国资企业
    FONG2
        45
    FONG2  
    OP
       2020-05-29 17:38:13 +08:00
    @william2ct 答对了 但是没有奖励
    FONG2
        46
    FONG2  
    OP
       2020-05-29 17:40:05 +08:00
    @woscaizi
    @saulshao
    @wangyzj
    @xuanbg
    看 APPEND 吧
    stevenkang
        47
    stevenkang  
       2020-05-29 17:44:51 +08:00
    建议用 SQL 跑吧,慢点就慢点,用程序跑更慢,亲身经历。

    之前一台游戏日志数据库,单表 2 亿级别,用程序跑从晚上 2 点跑到早上 8 点多。
    后面改成 SQL 将查询结果直接 insert 到另外一张表,一条 SQL 语句就搞定,时间缩短到 1-2 个小时就能跑完。
    ConradG
        48
    ConradG  
       2020-05-29 17:50:49 +08:00
    标准手势是拆分后归并排序
    nwg
        49
    nwg  
       2020-05-29 18:16:12 +08:00
    定时任务 批量查询--判断重复--插入新分表
    fashy
        50
    fashy  
       2020-05-29 18:35:42 +08:00
    没太明白意思,是说优化后是根据用户编号去重么?一个用户编号只保存一条记录?
    假设请求编号和用户编号都是 long 类型可以表示的话,(字符串的话 hash 可能会有冲突),用两个 bitmap 怎么样?
    7 亿 /1024/1024/1024/8,一个 bitmap1g 左右内存就能放下了
    第一个放每行记录的用户编号,重复的忽略,不重复的把该行记录的请求编号放到第二个 bitmap 对应的位置
    最终第二个 bitmap 里面被标记的 index(原表里面的主键),就可以认为是去重后的数据记录了
    然后批量读取这些 index 对应的数据,直接往新表里面插
    最坏的情况,请求编号和用户编号都是唯一的,去重过程也就只需要 2G 左右的内存
    myCupOfTea
        51
    myCupOfTea  
       2020-05-29 19:54:19 +08:00
    hash 分区啊
    myCupOfTea
        52
    myCupOfTea  
       2020-05-29 19:56:13 +08:00
    我之前的项目是收集几千个网站数据的,按照网站编码 hash 分了 1000 个区,反正每次查询都需要带上网站编码,飞快
    分区首先要有一个指标可以用来区分数据,其次每次查询必须带上它,那你就可以用分区呢
    xupefei
        53
    xupefei  
       2020-05-30 04:55:34 +08:00
    bloom filter 过一遍,疑似重复的条目存下来用哈希表检查。
    cs419
        54
    cs419  
       2020-05-30 06:39:25 +08:00
    3 、5 台机器的集群
    spark 或者 flink 跑一下
    估计顶多一两个小时 就搞定了
    nicebird
        55
    nicebird  
       2020-05-30 10:10:19 +08:00
    后台慢慢跑喽
    pabno
        56
    pabno  
       2020-05-30 11:37:33 +08:00
    不知道去重后的数据量有多少?
    如果比较少的话,可以考虑创建一张新表,根据去重规则创建唯一索引,写给程序不断的从旧表 load 数据并插入到新表中,使用 ingore insert 。
    如果数据比较多的话,依然可以使用这种方式,只不过要做多一层分表
    laminux29
        57
    laminux29  
       2020-05-30 13:52:07 +08:00
    看了一下楼上的回答,把问题都限定在数据库产品与对应的操作方法上了。

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

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

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

    在题主还没有交代物理设备与具体需求之前,我觉得大家不应该急着给出解决方案。
    encro
        58
    encro  
       2020-05-30 15:12:18 +08:00
    题目没出清楚:

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

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

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

    或者直接采用 es,想查几个月就几个月。
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1941 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 00:36 · PVG 08:36 · LAX 16:36 · JFK 19:36
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.