V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
cmxzj
V2EX  ›  问与答

大表更新

  •  
  •   cmxzj · 2022-07-21 16:50:08 +08:00 · 1617 次点击
    这是一个创建于 616 天前的主题,其中的信息可能已经有所发展或是发生改变。

    一张表 A ,数据量 1.4 亿,一张表 B ,数据量 1 千 3 百万。更新语句 update A set A.cola=(select colb from B where A.colc=B.cold) where exists (select 1 from B where A.colc=B.cold and colb is not null) 这是我目前能想到的语句了...如何优化呢,链接字段都有索引

    17 条回复    2022-07-22 14:08:45 +08:00
    ecloud
        1
    ecloud  
       2022-07-21 16:57:38 +08:00
    我印象中判断 null/not null 非常消耗性能,你看能不能把 null 给个缺省值
    morty0
        2
    morty0  
       2022-07-21 16:59:17 +08:00
    分片更新
    wxf666
        3
    wxf666  
       2022-07-21 17:08:53 +08:00
    这样?语句等价不?

    update A join B on A.colc = B.cold and B.colb is not null
    set A.cola = B.colb
    yangxx
        4
    yangxx  
       2022-07-21 17:22:18 +08:00
    分批次更新,根据 id 分几批去更新
    L0L
        5
    L0L  
       2022-07-21 17:33:21 +08:00
    查出来,然后批次更新吧;不然这样压力全在数据库,万一单点的库,万一堵塞,服务直接不能用了。
    wxf666
        6
    wxf666  
       2022-07-21 17:43:17 +08:00
    @L0L 数据库新手求问,能不能不查出来(有传输数据的损耗),直接 update set limit ?

    分批的作用,就是为了不一直堵塞,是嘛?
    gy123
        7
    gy123  
       2022-07-21 18:13:56 +08:00
    @wxf666 可以用 limit 限制每次更新直到全部更新~因为你这么大数据量直接更新是个大事务,不走主键和索引甚至会造成长时间锁表...还是分批吧
    v2eb
        8
    v2eb  
       2022-07-21 18:29:21 +08:00 via Android
    第一个子查询换 join 连接
    第二个子查询代码层判断
    本地测试下分批处理的单次耗时和总计耗时
    v2eb
        9
    v2eb  
       2022-07-21 18:33:11 +08:00 via Android
    其他索引列多嘛, 能不能批量删除再新增
    cmxzj
        10
    cmxzj  
    OP
       2022-07-21 18:34:29 +08:00
    如果想完成操作最快的方法能是啥,不用考虑其他服务使用这个表的情况,想知道最快的操作。当然没有权限 disable 各种 log 就是
    guisheng
        11
    guisheng  
       2022-07-21 18:44:50 +08:00 via iPhone
    查出来批次修改。批次查批次更新。
    wxf666
        12
    wxf666  
       2022-07-21 19:03:39 +08:00
    @gy123 咋会不走主键和索引呢

    #3 的语句,我觉得流程应该是:

    1. 扫描表 B ,过滤掉 B.colb 为 null 的行
    2. 表 B 剩余的行,每行查索引 A.colc ,看是否存在值 B.cold
    3. 若存在,从 A.colc 覆盖索引获取 表 A 的主键 ID ,再定位到表 A 的行记录,更新数据

    如果说 update set limit 存在不足,就是表 B 已更新的行,每次都还要再检查一遍吧

    加个 update set where B.id > ? limit 就好,但 mysql 好像没有 update returning ,无法确定上一次更新了哪些行
    wxf666
        13
    wxf666  
       2022-07-21 19:07:13 +08:00
    @cmxzj 我感觉 #3 的语句应该是很快的?分批操作,实际没有减少数据量,反而增加了数据传输的损耗
    wxf666
        14
    wxf666  
       2022-07-21 19:24:47 +08:00
    @v2eb 第二个子查询( select 1 from B where A.colc=B.cold and colb is not null ),

    为什么不能在扫描表 B 的时候,顺带过滤掉呢?

    难道是有 B.colb is not null ,某个索引就失效了吗?

    按理说,表 B 是驱动表,应该是全表扫描的?

    就算要分批查询,也应先过滤掉再取出来,而不是取出来再过滤掉?
    L0L
        15
    L0L  
       2022-07-21 19:49:50 +08:00
    @wxf666 我也不是特别懂,实际还是要和不同类型的数据库有关系;平常用 Oracle 比较多,如果大批量更新的话,链接等待时长比较长,占用资源比较多;如果是有传输问题的话,比较稳定的逻辑场景的话,我会考虑使用简单的 produce 来做。
    BoringBB
        16
    BoringBB  
       2022-07-21 20:18:26 +08:00
    不确定下面那个写法是不是等价的
    https://imgur.com/a/ZzGPngg
    liuhouer
        17
    liuhouer  
       2022-07-22 14:08:45 +08:00
    这种场景利用 cdc 来做啊,现在大数据 cdc 的工具特别多
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1146 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 22:58 · PVG 06:58 · LAX 15:58 · JFK 18:58
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.