我做后端开发和数据库维护这些年隔三差五就会接到一类让人头疼又绕不开的需求给某个大表批量改数据。业务说得很轻松——“把这些历史订单标记成已归档就行了”“把用户状态统一改一下”“把几个月前的脏数据修掉”。但等你打开控制台看到这张表已经有大几百万行甚至上千万行数据时心里就会明白一条简单的UPDATE语句根本不可能扛住这个量级。要么锁等待超时要么把主从延迟直接干到报警线更严重的时候几秒钟之内核心业务全被拖死。今天这篇文章要聊的就是“针对可能需要在某一个表修改大量数据的场景下的优化方案”我会用一张真实的订单表做例子把批量更新里那些容易踩的坑、真正好用的拆分思路、外键怎么处理、批大小怎么算一层层拆开讲清楚。这篇文章既适合被批量更新折磨过的后端工程师也适合正在规划一次历史数据变更的DBA。我不会只讲空洞的理论而是会给出可以直接抄走的SQL和脚本再把每一步决策背后的原因讲明白。看完之后遇到类似需求你会知道为什么不能一条UPDATE梭哈分批到底怎么分才合理中途挂了怎么续跑外键约束会不会在背后坑你一把。1. 典型场景与隐性问题1.1 哪些情况会触发“大表批量修改”“需要在某一个表修改大量数据”这个场景在真实业务里出现频率比我一开始预想的高得多。最常见的就是历史数据归档比如电商订单表里超过一年的订单要统一标记为“已归档”或者把已经完成的老订单从热表中逻辑摘除。其次是数据订正上游业务跑批出了bug下游表里几百万行状态不对需要在最短时间内修正。还有一类是字段回填新版本上线增加了一个冗余字段需要根据已有数据把老行补全。这几类需求有个共同点改动范围大不是几行几十行而是几十万行起步、上百万行很常见。而且它们往往发生在线上正式环境旁边还有实时业务在读这张表、写这张表。这时候你面对的不只是“把数据改对”这一个目标而是要同时保证线上业务不感知、不阻塞、不报错。很多人对这个规模的直觉是“数据库跑一下不就行了”但数据库的更新机制决定了它没那么好说话。行要逐行加锁改了之后要记undo日志事务提交要写redo日志如果开了binlog还得写binlog从库还要把这些变更全部重放一遍。行数一旦上百万这些动作全部会变成实打实的资源开销。我自己在实际项目中见过最典型的翻车现场一条UPDATE语句直接更新了三百多万行跑了不到十秒就把线上连接池打满紧接着DBA收到一堆锁等待告警。最后那笔更新回滚又花了更长时间整个下午都在补锅。从那之后我就明白大表批量更新的首要原则不是“跑得快”而是“别把系统搞挂”。1.2 一条UPDATE梭哈会引爆什么问题链先来看看一条大UPDATE到底会引发什么样的问题链只有把这些问题想清楚后面的优化方案才有方向。第一锁的问题。InnoDB的行锁不是一次性拿到的而是执行过程中逐行获取并一直持有到事务结束。一次性更新上百万行就意味着事务要长时间占着上百万行的锁。这个过程中任何其他事务想修改其中任意一行都会进入LOCK WAIT直到超时。如果更新的条件范围比较大间隙锁还可能进一步扩大影响范围。所以一条大UPDATE不只是慢的问题它会变成一把巨大的锁拦在业务前面。第二事务膨胀的问题。每改一行InnoDB都要在undo log里记一份旧值镜像用于回滚和MVCC。单事务更新行数过多undo会迅速膨胀后台purge线程来不及清理整个实例的IO和内存压力都会上来。更糟糕的是如果中途发现写错了或者业务要求回滚回滚本身就是一次灾难——它要反向执行同样量级的工作时长甚至比正常执行还长。第三binlog与主从延迟问题。大多数核心库都开的是ROW格式的binlog这种情况下每一行变更都会记录完整的前镜像和后镜像。三百万行更新binlog可能直接膨胀几个GB甚至十几GB。主库写完这些binlog从库要一条条拉过去重放主从延迟会瞬间飙到几千秒。这是生产中非常常见的故障模式。第四如果表结构里带外键问题就更复杂。更新外键列时InnoDB需要对父表和子表都做一致性检查逐行验证引用关系额外的开销非常大。就算更新的是普通字段只要表上存在外键其他相关DML在插入删除时也会被约束检查拖慢。关于外键的具体应对我放在第三章专门讲。所以优化方案的核心不是怎么让一条UPDATE变得更快而是怎么把这个“超大事务”拆成一堆“小事务”让每批的锁存续时间、日志产生量、从库重放压力都变成可控的同时保留断点续跑和容错能力。2. 优化方案的总体设计思路2.1 核心思路拆小、控速、幂等、可观测批量更新的优化方案有很多变种但万变不离其宗核心思想概括成四个词就是拆小、控速、幂等、可观测。“拆小”指的是把一次更新几百万行的操作拆成每批几千行的小事务。每个事务执行时间控制在毫秒到秒级锁的持有时间大幅缩短其他业务顶多卡顿一下不会被堵死。undo log的膨胀速度也要平缓得多purge线程能跟得上。“控速”指的是批与批之间主动加一点间隔比如sleep(0.1)到sleep(1)不等。这不是偷懒而是给redo落盘、binlog同步、从库重放留出缓冲时间。特别是主从架构下如果批次推得太密从库重放压力仍然会积累延迟照样会慢慢拉高。“幂等”要求你的更新条件里始终带着状态判断比如WHERE status 0而不是只写WHERE created_at 2023-01-01。这样即使脚本中途挂了、重复跑、或者一批更新因为异常被重试都不会把已经改过的行再改一遍也不会产生不可逆的副作用。“可观测”可能是很多人最容易忽略的。批量更新不是出一条SQL就完事建议每批都打印处理行数、耗时、当前游标位置。如果这个过程能落到一张进度表里那断点续跑就非常容易做。我在实践里的做法是更新前先把总行数、最小ID、最大ID查出来每批结束都更新一条进度记录哪怕中途宕机了也能从进度表拿到断点继续跑。这四条原则看上去简单但如果你能坚持做到批量更新的成功率会从“听天由命”变成“基本可控”。2.2 三种常见分批执行模型说完原则落地的时候你还需要选一个具体的分批模型。我常用的有三种分别适合不同情况。第一种是“按主键范围分批”。假设表的主键是自增ID先查一批范围内满足条件的ID然后UPDATE ... WHERE id BETWEEN ? AND ? AND status 待处理。这种方式的优点是走主键索引效率高、锁范围清晰、天然按顺序扫描基本不会跟业务产生太多交叉冲突。缺点是如果满足条件的行在ID空间里分布非常稀疏那一次范围扫描可能只更新到很少的行效率偏低。第二种是“先查ID列表再精确更新”。每次用索引条件查出最多N个满足条件的ID然后对这批ID做UPDATE ... WHERE id IN (...)。这种方式定位准确不管满足条件的行怎么分布都能稳定每批更新N行适合条件过滤性差、满足条件行分散的场景。缺点是需要额外的一次查询而且如果ID列表很长拼SQL时要小心max_allowed_packet限制。第三种是“临时表JOIN更新”。把每批要更新的主键ID写入临时表然后通过UPDATE 主表 INNER JOIN 临时表 ON 主表.id临时表.id SET ...来更新。这种方式在批量数据处理工具里非常常见好处是SQL语义清晰、更新条件可以写得很复杂而且临时表可以复用。缺点是每批都要重复建临时表、导数据步骤比前两种重一些适合数据量特别大、需要反复多次处理的场景。三种模型没有绝对的优劣关键是匹配你的实际场景和脚本习惯。我个人的选择标准是如果过滤条件能走索引且满足行相对连续就优先用第一种如果满足条件的行很零散就用第二种如果更新逻辑复杂到一条UPDATE写不清楚再用第三种。2.3 方案选型对比与取舍为了让大家快速决策我把三种模型放在一起对比一下。方案适合场景优点需要注意按主键范围分批满足条件的行在主键上相对连续扫描效率高、锁范围可控、脚本简单行分布稀疏时单批次有效行少先查ID列表再IN更新行分布零散、条件过滤性强每批更新行数稳定、定位精确多一次查询、ID数量受报文限制临时表JOIN更新更新逻辑复杂、数据量极大SQL清晰、可复用、便于维护步骤重、临时表有额外IO这里要特别提醒一句不管选哪种方案批内更新语句一定要走索引。如果过滤条件没法用索引那你去查第一批ID的时候就是一次全表扫描分批这件事就失去意义了。另外如果这张表本身有外键或者你更新的字段是其他表的外键引用列那方案选型还要考虑约束检查的开销这一点我在第三章单独展开。3. 实操从建表到跑完一个安全的批量更新3.1 准备测试环境建库建表与造数讲再多思路不如动手跑一遍。先把演示环境搭出来。我们直接用MySQL建一个库和一张订单表模拟那种需要批量改状态的场景。CREATE DATABASE IF NOT EXISTS biz_db DEFAULT CHARSET utf8mb4; USE biz_db; CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, status TINYINT NOT NULL DEFAULT 0 COMMENT 0-待处理 1-正常 2-已归档, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, created_at DATETIME NOT NULL, KEY idx_status_created (status, created_at) ) ENGINEInnoDB;表结构不算复杂但足够说明问题。status字段是要被批量修改的目标列created_at是我们筛选条件的过滤列。造数据的SQL我就不全文贴了核心思路是用递归CTE或者存储过程循环插入保证created_at均匀分布总量控制在两三百万行左右这样跑优化方案时效果足够明显。INSERT INTO orders (user_id, status, amount, created_at) SELECT FLOOR(RAND() * 100000) 1, IF(RAND() 0.8, 0, 1), ROUND(RAND() * 1000, 2), TIMESTAMP(2022-01-01) INTERVAL FLOOR(RAND() * 15000) DAY FROM information_schema.columns a CROSS JOIN information_schema.columns b LIMIT 2000000;造完数之后我们的模拟需求是把created_at 2023-01-01且status 0的历史订单全部改成status 2已归档。用SQL看一眼量级SELECT COUNT(*), MIN(id), MAX(id) FROM orders WHERE status 0 AND created_at 2023-01-01;确认好行数之后别急着UPDATE先看看这条过滤条件能不能用上索引。用EXPLAIN看一眼执行计划确保我们建的那个联合索引idx_status_created真的被用上了。这一步很关键否则后面每批查ID都会全表扫描分批方案会跑得很痛苦。3.2 基于主键分批更新的完整脚本我比较推荐的方式是先用Python写一个分批更新脚本逻辑直观也方便打印进度和做断点续跑。依赖的库是pymysql没有的话先装一下。import pymysql import time conn pymysql.connect( host127.0.0.1, userapp_user, passwordyour_password, databasebiz_db, autocommitFalse ) batch_size 5000 offset 0 while True: # 第一步查出当前需要处理的一批主键ID with conn.cursor() as cur: cur.execute( SELECT id FROM orders WHERE status 0 AND created_at 2023-01-01 ORDER BY id LIMIT %s OFFSET %s , (batch_size, offset)) rows cur.fetchall() if not rows: break ids [r[0] for r in rows] id_list ,.join(str(i) for i in ids) # 第二步只更新这一批注意条件里保留了 status 0 with conn.cursor() as cur: start time.time() cur.execute(f UPDATE orders SET status 2 WHERE id IN ({id_list}) AND status 0 ) affected cur.rowcount conn.commit() cost_ms (time.time() - start) * 1000 print(fbatch: offset{offset} size{len(ids)} affected{affected} cost{cost_ms:.1f}ms) offset batch_size time.sleep(0.2) conn.close()这个脚本里有两个细节值得说一下。第一先用SELECT id查主键再用IN做更新看起来多了一次查询但实际是值得的。因为每批更新都是精确命中不会出现扫了一大段ID却只更新到零星几行的情况。第二更新条件里始终保留status 0这是幂等设计的关键。就算脚本跑了两次第二次也不会再动已经变成status 2的行。如果不想用外部脚本MySQL里也能写存储过程完成同样的分批逻辑。核心是用游标循环每批取batch_size个ID然后UPDATE并提交。存储过程的优点是直接在数据库里跑不依赖外部环境缺点是调试比较麻烦输出进度也不如脚本直观。3.3 批大小怎么定参数计算与经验值批大小是批量更新里最容易被轻视但又最影响效果的一个参数。设太大会让单事务锁范围过大失去分批意义设太小又会让总循环次数变多整体耗时变长。我一般会从两个角度来确定批大小。第一个角度是单行成本。如果表行平均很小比如一行就几个整数和短字符串那么5000到10000行一批完全没问题。如果表里有TEXT、BLOB这种大字段单行成本会显著变高批大小就要降到500到1000。第二个角度是单批事务耗时。理想情况下一批UPDATE的执行时间控制在500毫秒到1秒之间。如果发现单批执行超过了2秒就要把批大小调小。实际操作里我习惯先取一个保守值跑几批看打印出来的耗时再微调。比如批大小设5000连续跑5批都稳定在几百毫秒那就可以继续跑下去如果某几批耗时跳到2秒以上就降一半重来。批与批之间的sleep间隔也需要看主从延迟情况来调。没有从库的测试环境0.2秒就够生产环境如果从库延迟已经偏高间隔拉到1秒甚至更长都是合理的。把总时长预期拉长一点换取系统的平稳这笔交易非常划算。这里还有一个容易被忽略的点单批更新的ID列表长度受max_allowed_packet限制。如果批大小设到几万生成的IN (...)语句会很长可能直接超过报文上限导致报错。所以批大小不是越大越好5000左右是一个经过了大量生产验证的稳妥值。3.4 有外键的表怎么处理外键是我特别想提醒的一点很多人在批量更新时压根没想过它会给自己挖坑。先说结论如果批量更新涉及的列不是外键列比如我们这里的status字段那么外键带来的直接影响相对有限因为更新普通列不会触发子表引用检查。但如果批量更新要修改的字段本身是外键列比如订单表里的user_id要批量替换成新用户ID麻烦就大了。当你更新外键列时InnoDB需要逐行验证新值在父表里是否存在还要检查在被引用表中有没有子表记录引用了旧值。这种依赖检查在单行更新时几乎感知不到但在百万行批量更新时就是致命的性能瓶颈。更麻烦的是如果存在子表引用更新操作会直接报错中断你根本没法通过简单的分批来绕过去。我处理这类问题的优先级是这样的。第一先质疑需求本身这个外键列真的必须批量改吗能不能通过新建一个映射关系字段不动外键列很多时候业务只是想要新老ID对应关系没必要物理修改外键列。第二如果确实要改先确认新值全部存在于父表并用子表对旧值的引用情况做一轮统计。第三在维护窗口内可以考虑暂时关闭外键检查执行更新完成后立刻恢复检查并做一次一致性核对。SET FOREIGN_KEY_CHECKS 0; -- 执行分批更新脚本 SET FOREIGN_KEY_CHECKS 1;但这里必须强调FOREIGN_KEY_CHECKS0是连接级别的设置只影响当前会话而且关闭外键检查期间如果程序里出现非法引用数据一致性就可能在不知不觉中被破坏。所以这个手段只能在明确知道自己在做什么、且有完善的校验兜底时使用。我的建议是能不动外键列就尽量不动能新建冗余字段就不要物理改外键列。3.5 索引检查与执行计划确认无论用哪种方案执行计划检查都应该是动手前的固定动作。很多人写批量更新一味关注SQL怎么拆却忽略了基础索引问题结果第一批查询就全表扫描耗时直接起飞。我们的模拟场景里过滤条件是status 0 AND created_at 2023-01-01所以建联合索引(status, created_at)是合适的。执行EXPLAIN的时候应该能看到走的是这个联合索引而不是全表扫描。EXPLAIN SELECT id FROM orders WHERE status 0 AND created_at 2023-01-01 ORDER BY id;还要注意如果更新语句里用ORDER BY id LIMIT的方式取IDMySQL的优化器在处理联合索引和排序时有时会选择不同的索引路径。我看到过不少案例WHERE条件能走索引但一旦加上ORDER BY id就触发了额外的文件排序。这种情况下可以考虑把联合索引调整成(status, created_at, id)或者让优化器按主键顺序读取减少排序开销。另一个细节是如果目标更新列本身在二级索引上比如我们的status列上有idx_status_created那么每次UPDATE把status从0改成2InnoDB不仅要改聚簇索引里的行还要维护两个二级索引项删除旧key、插入新key。这种索引维护成本是隐性的但当批量达到百万行时它带来的额外IO非常可观。所以批量更新尽量避开索引列、避免更新后触发大量索引结构调整也是一条实用的优化原则。4. 常见翻车现场与排查方法4.1 锁等待超时与死锁批量更新跑着跑着突然报Lock wait timeout exceeded这种情况我太熟了。通常原因有两个一是同一时间有其他业务正在更新你正在处理的行两边互相等锁二是你自己脚本里的批次之间没有正确提交导致事务积压后面的批次去等前面批次的行锁。排查锁问题时最直接的手段是看information_schema.innodb_trx和performance_schema.data_lock_waits两张表找出当前持有锁和等待锁的事务。如果发现锁是被自己的脚本持有的多半是提交逻辑写错了检查一下conn.commit()是不是放在每批更新之后。如果锁是业务产生的那就说明你在业务高峰期动了不该动的大表建议把批量更新的时间窗口挪到业务低峰或者在脚本里加大批间间隔。死锁比锁超时更难缠一点。批量更新里出现死锁通常是因为有两个以上的会话按不同顺序更新同一批行。解决方法也很朴素让所有批处理都按照主键ID从小到大更新保证加锁顺序一致。如果脚本里用了两个并发任务把它们合并成单线程跑或者至少在分批逻辑上都遵循同一个主键递增方向。我的经验是批量更新脚本要加一个“该批失败自动重试”的逻辑。捕获到死锁错误后先回滚当前事务随机等个几百毫秒再把这一批重新执行一遍。因为死锁回滚后锁已经释放重试大概率能成功。重试次数一般限制在3次以内如果连续失败就要停下来人工介入了。4.2 主从延迟失控批量更新过程中从库延迟飙高是最常见也是最吓人的故障表现。一条三百万行的UPDATE主库可能只跑了十几分钟但从库重放binlog可能需要几个小时。在这段时间里任何依赖从库读的业务都会读到明显滞后的旧数据。这里要先理解根因从库重放是单线程的非并行复制配置下面对大量ROW格式binlog事件它的处理速度通常赶不上主库的生产速度。我们优化主库执行方式的同时实际上也是在帮从库减压。分批更新的意义就在于每批只有5000行级别的binlog事件从库能很快追上批间再配合sleep从库重放基本能跟着主库走。如果从库延迟还是控制不住需要检查从库的复制线程状态看是否被其他大查询阻塞了或者延迟是不是在批量更新之前就已经积累了。生产环境里我还会做一步预防批量更新开始前记录一下Seconds_Behind_Master基线值跑完再对比一次。如果发现延迟在持续攀升就把sleep阈值调大甚至暂停一段时间等待从库追上再继续下一批。这是“削峰填谷”的思路宁可总耗时拉长一点也不能让从库落后太多。4.3 binlog写爆磁盘这条很多人是在磁盘告警之后才发现的。ROW格式binlog会把每行修改的前镜像、后镜像都记下来批量更新期间binlog文件增长速度会远超你的直觉。假设一行数据前后镜像加起来平均200字节一百万行更新就是200MB左右的binlog如果行宽再大一点几个GB很正常。所以在批量更新开始之前必须确认binlog所在磁盘还有足够的剩余空间同时估算一下可能产生的增量。如果磁盘余量紧张有两个选择一是把批大小调小并加大批间sleep延长整体执行时间让binlog生成速度慢下来为归档任务争取时间二是和DBA协商提前调整binlog的保留时长或max_binlog_size但这类参数变更要在评估清楚后做。另一个很容易忽略的点是批量更新超大表期间如果磁盘真的写满了MySQL会直接hang住甚至拒绝写入这种情况比跑得慢要严重得多。我个人的底线是批量更新前磁盘剩余空间至少留出预计binlog增量的两倍以上。宁可多留也不要赌。4.4 中断恢复与幂等设计批量更新跑了一半脚本崩溃了、服务器重启了、连接被网络打断了怎么办绝大多数人第一反应是从头再跑一遍。如果你没有做幂等设计这个决定可能让事情更糟——已经改成status2的行会被再次扫描虽然结果不变但日志和锁的开销会白费如果更新的不是幂等字段重复执行甚至可能产生错误数据。应对方案就是我们反复强调的幂等条件。脚本里WHERE条件始终带着status 0意味着重跑时已经成功的行自动被排除掉脚本会自动从剩余行继续处理。配合进度输出你可以清楚看到是否真的有推进。更进一步我会在脚本里维护一个简单的进度记录表每次更新完一批就把当前offset或者最后成功ID写进去。下次启动时先读进度表从断点继续而不是从头来过。对于超大数据量来说这能省下大量无谓的重复扫描。4.5 这批数据更新不完怎么办有些场景下行数实在太多你可能预估整个批处理要跑十几个小时远超一次维护窗口的容限。这时候不要硬跑考虑把更新任务进一步拆成“按时间分区”的子任务比如按created_at按月拆成多个子批次每个子批次单独跑。这样的好处是即使某个子批次失败也不会影响其他月份的数据排查范围会小很多。如果表本身比较大而且未来的历史归档需求是常态化的我更建议把“批处理”升级成“定期任务”。每月固定时间跑一次增量归档每次只处理上个月新增的历史数据量级就完全可控了。这也是应对“批量修改大表”这类需求的终极思路——不要一次性扛下所有压力而是把压力均匀分散到时间轴上。5. 我的实操心得与坑位提醒5.1 先查后改永远别跳过预案我现在的习惯是接到任何大表批量更新的需求动手之前一定先做三件事查总数、看索引、留备份。查总数是让心里有数知道对手盘有多大看索引是确认执行计划不会全表扫描留备份是在万一改错或者业务反悔时手里还有牌可以打。这三件事加起来可能也就花十几分钟但能把风险从“灾难级”降到“事故级”。另外强烈建议先在测试环境或者生产环境的一张从表上完整走一遍脚本流程。哪怕是取一个小范围数据验证逻辑都能发现很多预想不到的问题比如索引没生效、SQL写错了、外键报错、批大小不合理等等。把坑留在测试环境踩不要在生产上踩。5.2 一个最实用的小技巧最后分享一个我屡试不爽的小技巧批量更新脚本里每批都打印当前进度和单批耗时并且把日志输出到文件。不要小看这个看似简单的动作它能让你在脚本跑到一半出现异常时快速判断问题出在哪一批、影响范围有多大。如果脚本顺利跑完这些日志本身就是一份完整的变更记录可以用于事后审计或者回滚判定。我自己有一次在生产环境跑一个千万级表的数据回填就是因为日志里发现某几批耗时异常飙升及时停掉脚本排查最终定位到另外一个定时任务在同一时间窗口动了同一张表。如果没有日志我大概率会等到锁超时才反应过来到那时候整晚的维护窗口都废了。批量更新的优化从来不只是SQL层面的优化它还包括流程、观察和容错。把这些做到位再大的数据量在你手里也是可控的。
