MySQL 事务内批量更新为何越来越慢：索引维护成本与分批提交边界

订单归档任务原来几十秒就能完成，数据量涨到几十万行后却开始占着事务不放，线上还偶尔冒出锁等待。把 UPDATE 改成更快的写法通常不是第一步：先确认每批扫描了多少行、维护了哪些索引，以及事务到底持锁多久，才能决定该加索引、拆批次，还是调整提交边界。

先从变慢的归档任务还原现场

假设表 orders 保存近两年的订单，后台每天把已完成且超过 180 天的记录标记为归档：

UPDATE orders
SET archived = 1, archived_at = NOW()
WHERE status = 'DONE'
  AND paid_at 

这条语句的过滤条件并不复杂，但它可能同时带来四种成本：扫描候选行，改写聚簇记录，更新包含 status、paid_at 或 archived 的二级索引，以及把 undo/redo 和 binlog 写入磁盘。数据少时这些成本被掩盖，数据一多就会表现为“同一条 SQL 越跑越慢”。

先记录一轮基线，不要马上把批次改成一个更大的数字：

EXPLAIN UPDATE orders
SET archived = 1, archived_at = NOW()
WHERE status = 'DONE'
  AND paid_at 

重点看 key、rows、filtered 和实际执行耗时。rows 只是估算的扫描量，不能直接等同于被更新的行数；还要把慢日志中的实际耗时、受影响行数和锁等待放在一起看。

索引不是越多越快：先划清维护边界

如果归档任务每次都用 status、archived 和 paid_at 筛选，可以先检查是否存在匹配访问路径：

SHOW INDEX FROM orders;
EXPLAIN UPDATE orders
SET archived = 1, archived_at = NOW()
WHERE status = 'DONE'
  AND archived = 0
  AND paid_at 

复合索引的列顺序要贴合真实筛选和排序，而不是把所有字段都塞进去。更重要的是，正在被更新的列如果出现在二级索引里，每修改一行都可能触发索引项调整。一个“让筛选更准”的索引，可能把写放大成本推高。

用实际受影响行数判断索引收益

可以在低峰期复制一小段主键范围做验证，比较加索引前后的单批耗时、扫描行数和锁等待。不要只看 EXPLAIN 里的估算数字，也不要用全表生产任务做第一次试验。

如果筛选字段选择性很差，例如绝大多数订单都是 DONE，优化器即使看到索引也可能认为全表或大范围扫描更划算。这时先缩小时间窗口或按主键切批，常常比继续堆索引更可控。

按主键分批提交，控制锁和日志的峰值

归档类任务通常不需要一个事务覆盖全部历史记录。可以用自增主键保存游标，每次取一个有限范围，更新后立即提交：

UPDATE orders
SET archived = 1, archived_at = NOW()
WHERE id > :last_id
  AND id 

应用层每批保存 next_id，下一批从它继续。这样做的关键不是“每批固定 1000 行”这个数字，而是把单批时间控制在可接受范围内：例如先从 500～2000 个主键窗口开始，测出单批 P95，再根据锁等待和 redo 压力调整。

为什么不建议用 OFFSET 分页更新

LIMIT ... OFFSET ... 在数据持续变化时会越过或重复访问记录，偏移量越大也越需要扫描前面的结果。用主键范围或先取出稳定的主键列表，才能让下一批的位置可复现。

批次失败时要能安全重跑

把条件写成 archived = 0 让已完成批次天然跳过，并记录每批的起止主键、受影响行数和错误信息。任务中断后，从最近一个确认提交的游标继续，不要把“调用成功”误当成“事务已经提交”。

用四个指标验收，而不是只看总耗时

• 单批耗时：关注 P95，避免少数大批次拖住下一轮调度。
• 受影响行数：与预期归档量核对，发现突然为零或暴涨都要停下来查条件。
• 锁等待：观察 performance_schema.data_lock_waits 及业务请求延迟，确认拆批没有把冲突转移到更频繁的提交。
• 日志压力：结合 InnoDB 日志写入和磁盘延迟看是否出现新的峰值。

如果单批耗时已经稳定，但业务仍变慢，问题可能在归档任务与在线更新争用同一批订单，而不是 SQL 语法。此时应缩小主键窗口、避开高峰，或让任务只处理有明确时间边界的记录。

常见问题：批量更新的三个边界

分批提交会不会破坏事务一致性？

会改变事务边界。若业务要求整批原子完成，就不能简单拆成多个提交；归档标记这类可重试操作通常可以接受逐批提交，但必须让条件幂等，并明确中断后的恢复规则。

批次越小是不是一定越安全？

不是。批次太小会增加往返、提交和日志刷盘次数。应以锁等待、单批 P95 和吞吐量为依据，从较小窗口开始逐步放大。

加了索引后为什么写入反而变慢？

因为更新命中了索引维护路径。先确认索引是否真的减少扫描，再核对被更新字段是否参与索引，以及查询收益是否值得长期写放大。

把这次优化收敛成可回退的作业规则

比较稳妥的归档流程是：先用一小段主键范围做基线，确认筛选路径；再按主键窗口逐批更新、逐批提交；最后把游标、受影响行数、P95、锁等待和错误写进任务记录。索引调整也要单独验证，保留变更前后的执行计划和回退方案。这样下一次数据规模变化时，看到的是一组可比较的信号，而不是一句“这条 UPDATE 又慢了”。