如何按每批N条规则分批用关联表数据更新大量数据库记录

场景说明 #

现有两张存在主键/外键关联关系的数据库表：

• A表总记录数为100000条
• B表总记录数为1000条
  需求为分批次完成A表的全量更新：每100条A表记录为一个批次，对应使用1条B表记录的指定列值更新该批次A表的对应列，全量更新共执行1000批次。

实现思路 #

• 对B表按主键排序，为每条记录生成从0开始的连续整数作为批次标识
• 对A表按主键排序，每100条记录划分为一个批次，生成与B表规则一致的批次标识
• 以批次标识为关联条件，逐批次执行关联更新，避免全量更新触发锁表、长事务等问题

MySQL 示例代码 #

首先测试查询验证关联逻辑是否正确：

SELECT A.id, A.target_col, B.target_col 
FROM (
    SELECT *, FLOOR((@row_num:=@row_num+1)/100) AS batch_id 
    FROM A, (SELECT @row_num:=-1) t 
    ORDER BY A.id
) A
INNER JOIN (
    SELECT *, (@batch_num:=@batch_num+1) AS batch_id 
    FROM B, (SELECT @batch_num:=-1) t 
    ORDER BY B.id
) B ON A.batch_id = B.batch_id
LIMIT 200;

确认逻辑正确后执行批量更新，如需逐批次提交避免长事务，可加循环逻辑按batch_id逐个批次执行更新：

UPDATE (
    SELECT *, FLOOR((@row_num:=@row_num+1)/100) AS batch_id 
    FROM A, (SELECT @row_num:=-1) t 
    ORDER BY A.id
) A
INNER JOIN (
    SELECT *, (@batch_num:=@batch_num+1) AS batch_id 
    FROM B, (SELECT @batch_num:=-1) t 
    ORDER BY B.id
) B ON A.batch_id = B.batch_id
SET A.target_col = B.target_col;

注意事项 #

操作前请务必备份全量数据，优先在测试环境验证更新逻辑符合预期后再操作生产环境

• 执行更新前可临时关闭A表的非必要索引，更新完成后再重建，大幅提升批量更新效率
• 可根据数据库实际负载调整单批次更新条数，避免事务过大占用过多数据库资源
• 建议选择业务低峰期执行该操作，降低对在线业务的影响

内容的提问来源于stack exchange，提问作者Ram