大表更新 - V2EX

This topic created in 1570 days ago, the information mentioned may be changed or developed.

一张表 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) 这是我目前能想到的语句了...如何优化呢，链接字段都有索引

Where

colb

select

语句

17 replies • 2022-07-22 14:08:45 +08:00

ecloud

Jul 21, 2022

我印象中判断 null/not null 非常消耗性能，你看能不能把 null 给个缺省值

morty0

Jul 21, 2022

分片更新

wxf666

Jul 21, 2022

这样？语句等价不？

update A join B on A.colc = B.cold and B.colb is not null
set A.cola = B.colb

yangxx

Jul 21, 2022

分批次更新，根据 id 分几批去更新

L0L

Jul 21, 2022

查出来，然后批次更新吧；不然这样压力全在数据库，万一单点的库，万一堵塞，服务直接不能用了。

wxf666

Jul 21, 2022

@L0L 数据库新手求问，能不能不查出来（有传输数据的损耗），直接 update set limit ？

分批的作用，就是为了不一直堵塞，是嘛？

gy123

Jul 21, 2022

@wxf666 可以用 limit 限制每次更新直到全部更新~因为你这么大数据量直接更新是个大事务,不走主键和索引甚至会造成长时间锁表...还是分批吧

v2eb

Jul 21, 2022 via Android

第一个子查询换 join 连接
第二个子查询代码层判断
本地测试下分批处理的单次耗时和总计耗时

v2eb

Jul 21, 2022 via Android

其他索引列多嘛, 能不能批量删除再新增

cmxzj

Jul 21, 2022

如果想完成操作最快的方法能是啥，不用考虑其他服务使用这个表的情况，想知道最快的操作。当然没有权限 disable 各种 log 就是

cameco

Jul 21, 2022 via iPhone

查出来批次修改。批次查批次更新。

wxf666

Jul 21, 2022

@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

Jul 21, 2022

@cmxzj 我感觉 #3 的语句应该是很快的？分批操作，实际没有减少数据量，反而增加了数据传输的损耗

wxf666

Jul 21, 2022

@v2eb 第二个子查询（ select 1 from B where A.colc=B.cold and colb is not null ），

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

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

按理说，表 B 是驱动表，应该是全表扫描的？

就算要分批查询，也应先过滤掉再取出来，而不是取出来再过滤掉？

L0L

Jul 21, 2022

@wxf666 我也不是特别懂，实际还是要和不同类型的数据库有关系；平常用 Oracle 比较多，如果大批量更新的话，链接等待时长比较长，占用资源比较多；如果是有传输问题的话，比较稳定的逻辑场景的话，我会考虑使用简单的 produce 来做。

BoringBB

Jul 21, 2022

不确定下面那个写法是不是等价的
https://imgur.com/a/ZzGPngg

liuhouer

Jul 22, 2022

这种场景利用 cdc 来做啊，现在大数据 cdc 的工具特别多