批量更新大表数据是数据库运维和后端开发中绕不开的场景，比如修正历史脏数据、调整字段默认值、做数据迁移后的清洗。很多同学写起来很随意，直接一条UPDATE把几百万行全部改掉，结果语句执行了几十分钟，期间业务表被大量行锁阻塞，接口大面积超时，主从延迟一路飙升。这篇文章就来聊聊为什么大表更新这么危险，以及如何通过分片更新的方式把风险降到最低。
一、为什么大表上直接UPDATE会出事
要理解分片更新的价值，得先明白大事务到底带来了哪些问题。一条UPDATE语句在InnoDB里是一个原子事务，它要修改的每一行都会被加上行锁，直到整个事务提交才释放。也就是说，你要改500万行，这500万行的锁会在事务开始后逐步累积，全部持有到语句结束。期间任何业务请求只要碰到这些行，就会被阻塞等待。
第二个问题是undo log的膨胀。每一行修改都要记录回滚信息，500万行的更新会产生一个巨大的undo链路，一方面占用大量回滚段空间，另一方面如果事务中途失败回滚，回滚时间可能比正向执行还长，这段时间锁依然被持有，相当于雪上加霜。
第三个问题在主从架构上尤其明显。MySQL的binlog是按事务为单位传输的，一个大事务会作为一个完整的binlog event组传到从库。从库必须把整个事务执行完才能应用后续日志，直接表现就是主从延迟瞬间跳到几十分钟，读从库的业务拿到的全是旧数据。
另外还有一个容易被忽略的点：长事务会阻碍MVCC版本的清理。InnoDB靠purge线程清理旧的undo版本，但有长事务持有旧的Read View时，被引用的版本不能删除，会导致undo表空间持续增长，严重时把磁盘打满。所以大事务的危害不只是锁，它是一连串连锁反应的起点。
二、分片更新的核心思路与写法
分片更新的思路很朴素：把一次要改的500万行拆成几千个小批次，每个批次只改几千行，改完立刻提交释放锁。这样任何时刻持有锁的行数都是可控的，主从复制也变成了持续的小事务流水，从库可以近乎实时地跟上。
最常见的做法是按主键范围分批。先查出待更新数据的主键范围，然后每次取一段主键区间去更新。以MySQL为例，假设要把orders表里status为0的老数据刷成1：
-- 每批更新主键在 [low, low+step) 区间内的目标行 UPDATE orders SET status = 1 WHERE id >= 1000000 AND id < 1005000 AND status = 0; -- 应用层循环推进 low += step，直到超过上界
这里有几个细节值得注意。首先，WHERE条件里务必保留原始的过滤条件status = 0，因为批次内可能有一部分行并不需要改，多加这个条件可以减少无效行锁的获取。其次，主键范围切分的前提是id分布相对均匀，如果id有大量空洞（比如大量删除过数据），按固定step切分会导致某些批次空转，可以改成先查每批的边界：
-- 每次取本批次的最小id和最大id，避免空批次 SELECT id FROM orders WHERE id > :last_id AND status = 0 ORDER BY id LIMIT 5000; -- 用查到的最大id作为本批UPDATE的上界
每批之间建议加一个小间隔，比如sleep 50到100毫秒，给主从复制留出追赶的时间，也给业务请求留出获取锁的窗口。批次大小通常建议在1000到10000行之间，具体取决于单行的更新开销和业务对响应时间的要求。批太大回到大事务问题，批太小则总耗时拉长、日志切换频繁，需要结合实际压测调整。
三、不同分片策略的对比与选型
按主键范围分片是最通用的方案，但不是唯一的。实际工作中还有几种常见策略，各有适用场景。
第一种是按时间字段分片，适合数据天然按时间组织的表，比如日志表、订单表。按天或按小时切片，每片数据量可控，而且执行顺序可以从旧数据开始，优先清理历史数据。缺点是时间字段上必须有索引，否则每次切片定位本身就会全表扫描。
第二种是按更新时间戳游标推进，也就是每次只更新“还没改过”的行。给表加一个updated_at标记字段，每批更新时把当前批次的时间戳写进去，下一批WHERE条件带上updated_at早于本次任务开始时间的过滤。这种方式的天然优点是任务可重入，中断后重启不会重复处理已完成的行。缺点是多了一次DDL或者字段变更的成本，对不能随便加字段的存量表不太友好。
第三种是利用工具自动化分片，比如MySQL生态里常用的pt-online-schema-change和gh-ost，它们虽然主要用于在线改表，但内部同样依赖分批处理的思想。如果团队有成熟的工具链，直接用工具比自己写脚本更稳妥，工具会自动处理限速、监控和异常恢复。三种方式的对比如下：
| 策略 | 适用场景 | 主要优点 | 注意事项 |
|---|---|---|---|
| 主键范围分片 | 有自增主键的绝大多数表 | 实现简单、通用性强 | 主键分布不均时需用游标法取边界 |
| 时间字段分片 | 日志表、按时间归档的数据 | 切片天然均匀、便于断点续跑 | 时间字段必须有索引 |
| 在线工具 | 复杂变更、需要自动限速和监控 | 自动化程度高、可观测性好 | 需要额外权限和工具部署成本 |
四、生产环境操作前的检查清单
写好脚本只是第一步，生产环境执行前的准备工作往往决定了这次变更是否安全。第一件事是确认待更新行的规模，先用SELECT COUNT大致估算总量，据此推算批次数量和预计总耗时，如果预估时间跨到业务高峰期，一定要调整执行时间窗口。
第二件事是确认更新条件的索引命中情况。分片UPDATE的WHERE条件如果只有主键范围没有过滤索引，每批扫描本身是可以接受的，因为主键定位本身很快；但如果过滤条件是其他字段且没有索引，就可能每批都做范围外的额外扫描。用EXPLAIN检查执行计划，确认走了理想的索引。
EXPLAIN SELECT COUNT(*) FROM orders WHERE id >= 1000000 AND id < 2000000 AND status = 0; -- 确认type为range，key为主键，避免全表扫描
第三件事是设置好熔断和监控。脚本里要加上错误重试和失败退出的逻辑，遇到锁等待超时（InnoDB默认50秒的innodb_lock_wait_timeout）不要无脑重试；同时监控主从延迟指标（Seconds_Behind_Master），一旦延迟超过阈值就暂停任务等待追平。低峰期执行、分批限速、随时可停，是这类任务安全运行的三大原则。
最后提醒一点：无主键的表在分片更新时要格外小心。没有主键意味着没有稳定的行定位手段，只能依赖业务唯一键或先补充主键。另外从库应用UPDATE时如果没有主键，会退化为全行匹配，主从延迟会比预期严重得多，这也是为什么规范一直强调每张表都必须有显式主键。把分片更新的脚本固化成团队的标准工具，配合审批流程执行，大表变更就从高危操作变成了可控的日常操作。