MySQL 批量 UPDATE 怎么分批提交：索引范围、锁持有与失败重试边界

线上给历史订单补写状态时，最容易出问题的不是 UPDATE 语句本身，而是一条事务把几十万行锁在手里。批量修改更稳的做法是先用索引确定一小段主键范围，在同一个范围内更新并提交；如果这一批失败，只回滚这一批，再从明确的边界重试。

把“大 UPDATE”拆成可推进的索引批次，关键不是盲目调小 LIMIT，而是让每批范围可定位、可提交、可重试。

为什么一条 UPDATE 会把锁持有时间拉长

InnoDB 会为被修改的记录建立锁请求；如果另一个事务想改同一批记录，就会进入等待，直到持锁事务提交或回滚。大事务不仅修改行多，日志刷盘和二级索引维护也集中发生，最后才释放锁，等待会被放大。

MySQL 8.4 的单表 UPDATE 支持 `ORDER BY` 和 `LIMIT`，但 `LIMIT` 是“找到多少匹配行就停止”的限制。它适合控制单次工作量，却没有替你保存下一批从哪里开始。要做可恢复的批处理，必须把边界放进 WHERE 条件。

用主键范围建立稳定的批次边界

假设要给 `account_balance` 表补齐 `status`，表上有递增主键 `id`，业务条件是把待处理记录从 `pending` 改为 `ready`。最小可用写法如下：

START TRANSACTION;

UPDATE account_balance
SET status = 'ready', updated_at = CURRENT_TIMESTAMP
WHERE id > 120000
  AND id 

这里的 批次范围 是 `(120000, 125000]`，索引范围 由主键支持，COMMIT 释放这一个范围内的锁。下一批只需把上界向前推进。即使区间中有已经处理过的行，状态条件也会让语句保持幂等。

为什么不建议只写 LIMIT 1000

LIMIT 1000 没有排序时，下一次到底拿到哪些匹配行由执行计划和数据状态决定；即便加了 ORDER BY id，应用仍然需要记住最后一个已处理的 id。把游标边界显式写入条件，排障时才能回答“这一批改了哪一段”。

如果过滤条件不能走索引，批次看起来很小，数据库仍可能扫描大量记录。先用 EXPLAIN 确认访问路径，再决定主键范围大小；不要把批次大小当成索引缺失的补药。

批次大小怎么定：先看锁等待，再看吞吐

可以从 1000 或 5000 行开始，在业务低峰执行一批，观察单批耗时、锁等待和复制延迟。单批耗时接近业务请求的容忍上限时，先缩小范围；如果锁等待很少但日志和磁盘还有余量，再逐步放大。

失败时只回滚当前批次，按边界继续重试

批处理程序至少要保存 `last_id`、`upper_id`、受影响行和提交状态。只有看到提交成功，才能把 `last_id` 推进到当前上界；如果执行报错或锁等待超时，就执行 `ROLLBACK`，保留原来的边界，换一个时间窗口或更小的范围重试。

START TRANSACTION;

UPDATE account_balance
SET status = 'ready'
WHERE id > 125000
  AND id 

图里的 ROLLBACK 只负责撤销未提交的当前批次；前一批已经完成的 COMMIT 不会被它带回去。重试时仍使用状态条件，形成清晰的 重试批次，避免网络重试把已经是 `ready` 的行当成新任务。

上线前的三项核对

• 确认范围列有可用索引，并用一批真实数据检查扫描行数；
• 确认每批只有一次提交，日志中能关联批次上下界和受影响行；
• 准备锁等待、死锁和复制延迟的观察入口，遇到异常先暂停推进，不要盲目并发补偿。

相关问题

批次一定要按主键吗？

不一定，但应选择单调、可定位且有索引的列。主键通常最容易保存断点；时间列可能重复，使用时要增加唯一键作为并列排序边界。

UPDATE 影响行数为零是不是失败？

不一定。可能是这一范围没有待处理记录，也可能是状态条件已经被前一次重试满足。应结合匹配条件和批次边界判断，而不是仅凭零行就重复执行。

锁等待变多时应该先加并发吗？

不应该。先缩小批次、缩短事务并确认阻塞者；增加并发通常会让同一索引范围的竞争更明显。

小结

可靠的批量 UPDATE 是一条能不断推进的流水线：索引确定范围，事务完成这一小段修改，提交结果推动断点，异常只回滚当前批次。这样既能控制锁持有时间，也能让失败重试和上线复核有据可查。