1. 为什么要批量更新先看一段最原始的逐条UPDATE做后端开发的几乎没人能躲开MySQL的UPDATE操作。很多人第一版代码都是这样写的循环遍历一个列表拼一条UPDATE语句然后一条一条执行。功能倒是能跑通但数据量一上来性能就肉眼可见地崩了。1.1 一段典型的“低效更新”代码比如业务方给了一个需求把一批订单的状态批量修改为“已发货”订单号列表有几千条。最直觉的写法是这样order_ids [1001, 1002, 1003, ...] # 假设几千条 for order_id in order_ids: cursor.execute( UPDATE orders SET status shipped WHERE order_id %s, (order_id,) ) connection.commit()这段代码的流程是建立一次数据库连接然后逐条执行UPDATE。如果列表里是5000条数据那就是5000次网络往返、5000次SQL解析、5000次日志写入。在开发环境看着没问题一旦数据量涨到几万、几十万响应时间就会成倍增加甚至会拖垮数据库连接池。我之前在某个模拟项目X里做过一次压测同样的需求逐条执行5000次UPDATE耗时大约在12秒到15秒之间而改成批量更新之后耗时降到了1秒以内。这不是一个量级的差距是完全不同的处理思路。1.2 逐条更新的成本到底高在哪里很多人以为慢是因为SQL执行本身其实UPDATE单条语句的执行速度非常快瓶颈主要在这几块网络往返RTT每执行一次UPDATE客户端和MySQL服务端就要通信一次。一次RTT在局域网内可能只有0.2ms但跨地域部署时能达到几十毫秒几千次往返叠加起来非常可观。SQL解析与优化每一条UPDATE都要经过词法分析、语法分析、优化器生成执行计划。单次耗时很小但乘以万级次数就是秒级损耗。事务日志与提交开销如果每执行一条就commit一次InnoDB要把每个事务的redo log刷到磁盘这通常是性能杀手。连接与上下文切换循环中反复调用执行接口应用层和数据库层的上下文切换、游标创建与释放都在消耗CPU。所以批量更新的核心目标就是减少交互次数、缩小事务数量、降低日志刷盘频率把多次单条操作压缩成一次或少数几次大操作。1.3 什么时候真的不能批量什么时候纯属想偷懒必须先说清楚一个边界批量更新不是万能的。以下场景我建议老老实实逐条更新每条记录的WHERE条件完全不同且无法归并成同构的更新条件。比如每条更新的过滤条件涉及不同的关联表、不同的子查询拼成批量语句的难度和风险远大于收益。业务对单条更新结果有强依赖。比如更新后必须立即读取该条记录的最新值并据此决定下一条是否继续这种串行逻辑无法用批量替代。数据量实在太小。如果只有十条八条数据逐条更新和批量更新的耗时差距几乎可以忽略这时候优先考虑代码可读性。但多数情况下我们遇到的问题不是“不能批量”而是“懒得改”。改造成批量更新收益非常明显下面把几种主流方案逐一拆开讲。2. 四种批量更新方案与适用边界MySQL的批量更新没有Oracle的FORALL那样的一等语法但通过SQL技巧可以达到类似效果。我实际用过的方案主要有四种各有各的适用场景。2.1 方案一CASE WHEN结构的多值更新这是最经典、也最常被拿出来说的写法。核心思路是用一个UPDATE语句配合CASE WHEN把多条更新合并在一起UPDATE orders SET status CASE order_id WHEN 1001 THEN shipped WHEN 1002 THEN cancelled WHEN 1003 THEN pending ELSE status END WHERE order_id IN (1001, 1002, 1003);这个方案的本质是把“按条件更新不同值”的需求变成“一个范围条件下的分行判断”。一次SQL完成所有更新网络交互只有一次。它的优点非常突出语法标准兼容性好不需要任何额外配置或特殊权限。支持每条记录更新不同的值不只是统一的“把所有订单状态改成已发货”。WHERE子句可以高效走主键索引数据量大时性能非常稳定。缺点是当数据量特别大时SQL文本会变得很长。比如两万条数据CASE WHEN拼出来可能有几百KB接近MySQL的max_allowed_packet限制。另外构造这个SQL需要应用层做一次字符串拼接如果列表来自用户输入要做好参数验证和转义防止注入。我自己的经验是CASE WHEN方案适合几百到几千条规模的数据更新。在这个量级内它几乎是最简单、可维护性也够好的办法。如果你要更新的不是“不同行不同值”而是统一把一批记录的某个字段设为同一个值那还有更简单的写法UPDATE orders SET status shipped WHERE order_id IN (1001, 1002, 1003);这个就不多说了是统一值场景下的首选性能比CASE WHEN更好因为不需要逐行做分支判断。2.2 方案二INSERT INTO ... ON DUPLICATE KEY UPDATE复用这个方案利用了MySQL的INSERT语法扩展。把待更新的记录先作为“新数据”插入如果主键或唯一索引冲突则转而执行UPDATE动作INSERT INTO orders (order_id, status, update_time) VALUES (1001, shipped, NOW()), (1002, cancelled, NOW()), (1003, pending, NOW()) ON DUPLICATE KEY UPDATE status VALUES(status), update_time VALUES(update_time);用这个方案有几个前置条件表里必须有主键或唯一索引否则ON DUPLICATE KEY根本不会触发。你要更新的列最好就是唯一索引冲突时能定位到的那一行业务逻辑要清晰。这个方案的场景比CASE WHEN更广它不但能更新已有记录还能在某些记录不存在时直接插入新行。这实际上是“存在则更新不存在则插入”的典型用法在同步类业务里特别常见。注意VALUES()函数在MySQL 8.0.20之后标记为deprecated官方推荐使用别名语法。如果你用的是MySQL 8.0以上版本建议写成INSERT INTO ... VALUES (...) AS new ON DUPLICATE KEY UPDATE status new.status。不过8.0.20之前的老版本直接用VALUES()没问题。2.3 方案三临时表JOIN UPDATE当更新的数据量达到数万条甚至更多上面两种拼SQL的方案会面临SQL文本过长、内存占用过高等问题。这时更稳的做法是把数据先导入临时表然后通过JOIN完成更新-- 第一步创建临时表 CREATE TEMPORARY TABLE tmp_order_update ( order_id INT PRIMARY KEY, status VARCHAR(20) ); -- 第二步批量写入更新数据 INSERT INTO tmp_order_update (order_id, status) VALUES (1001, shipped), (1002, cancelled), ...; -- 第三步JOIN更新 UPDATE orders o INNER JOIN tmp_order_update t ON o.order_id t.order_id SET o.status t.status;这个方案的好处是数据写入临时表时可以做批量INSERT速度非常快。真正更新时走的是JOIN执行计划可以选择好的驱动表和连接方式整体性能在大数据量下优于CASE WHEN。临时表是会话私有的不需要担心影响别的连接。但也有成本需要额外的临时表创建和数据写入阶段事务更长占用临时表空间。如果更新工具是像某跨平台系统那样需要跑批任务的可以选择非临时表而用普通表加后缀更新完再删除这样还能留个审计痕迹。2.4 方案四分批循环batch_size的选择逻辑很多人忽略的一点是批量更新不是万条数据一条SQL硬塞。合理的做法是把大批量拆成多个小批量每个小批量执行一次批量更新循环处理。比如要更新10万条数据不要一次性拼一条10万行的CASE WHEN而是分成每批500条或1000条循环执行50到100次。batch_size怎么选我一般看这几个因素SQL文本大小控制单条批量SQL在几十KB以内避免触发max_allowed_packet限制。事务时长单批执行时间越短锁持有时间越短死锁概率越低。内存开销应用层拼接大批量SQL时会占用大量内存分批可以控制峰值。主从延迟大批量事务的binlog在从库重放时会造成明显的延迟分批次提交可以给从库喘息机会。我自己常用的batch_size范围是500到2000。数据量小用上限数据量大用下限。如果你要更新的表和别的表有外键关联batch_size还要再保守一点。3. 踩坑实录一次WHERE条件不带索引引发的批量更新事故批量更新的SQL语法对不等于执行就安全。很多人在本地测试环境看不出问题到生产环境一跑就出事。下面分享一个真实踩过的坑。3.1 事故现场回放某个模拟项目X中我负责维护一张用户积分表表里有几千万条数据。业务方要求把一批老用户的积分统一调整更新条件是根据用户的注册渠道和注册时间范围来圈定用户。我当时写的是UPDATE user_points SET points points 100 WHERE channel app AND register_time 2024-01-01;执行之后发现这条SQL跑了很久都不结束。查了SHOW PROCESSLIST发现SQL状态一直是“Updating”执行时间超过了几分钟。最后只能手动KILL掉。为什么会这样因为user_points表上根本没有任何索引覆盖channel和register_time这两个字段。MySQL优化器只能对整表几千万行做全表扫描找到所有满足条件的记录再逐行更新。这不仅慢而且在这个过程中InnoDB会对扫描到的每一行加锁锁范围迅速扩大导致其他正常的业务更新也被阻塞。3.2 排查链路从慢查询日志到执行计划那次事故的排查过程我觉得是值得复盘的。以下是完整的链路第一步打开慢查询日志定位元凶我检查了MySQL的慢查询日志配置发现当时long_query_time设置为2秒但这条SQL在日志里根本没出现。原因是KILL掉的SQL不会完整记录执行时间。于是我改用performance_schema里的events_statements_history_long找到了这条被中断的UPDATE语句确认它的执行时间确实非常长。第二步用EXPLAIN看执行计划外部文章网络上对这个步骤非常强调我认为确实关键。对这条UPDATE执行EXPLAIN SELECT * FROM user_points WHERE channel app AND register_time 2024-01-01;结果显示type是ALL也就是全表扫描rows预估达到数千万。这说明优化器找不到可用索引。第三步查看表结构确认索引缺失SHOW INDEX FROM user_points;结果显示表里只有主键索引没有任何涉及channel和register_time的二级索引。第四步评估数据分布确定索引方案在加索引之前我先查了一下满足条件的记录数占总量的比例。如果更新范围覆盖了大多数行那加索引也没太大帮助因为优化器可能会选择全表扫描但如果只覆盖少量行二级索引就非常有效。结果发现满足条件的记录只有总数据量的不到5%完全适合加索引。于是我加了组合索引ALTER TABLE user_points ADD INDEX idx_channel_register_time (channel, register_time);重新执行EXPLAIN后type从ALL变成了ref预估扫描行数降到几千。再执行UPDATE秒级完成。3.3 根因分析为什么检查索引和计划不是“可有可无”这次事故的根因说起来就是一句“WHERE条件没走索引”。但为什么会踩到我总结了几点开发态数据量小全表扫描的耗时也在可接受范围内问题没有暴露。当时觉得“这条UPDATE很简单不用看执行计划”于是跳过了EXPLAIN这一步。表结构变更时没有同步审查哪些新查询会用到哪些索引。在批量更新场景里WHERE条件的索引问题会被放大逐条更新时即使全表扫描单条SQL也只扫一遍全表但批量循环更新每条SQL都可能全表扫描一次几万条数据就能把数据库拖垮。所以写任何UPDATE之前先EXPLAIN一下这不是流程形式是保命操作。4. 性能对比与选型决策表实操经验不能只停留在理论上。我在这几种方案之间做过一次比较完整的性能对比数据量分别为1000条、5000条、10000条表记录总数约100万行InnoDB引擎MySQL 5.7环境。结果如下表方案1000条耗时5000条耗时10000条耗时备注逐条UPDATE约2.1秒约10.5秒约21秒每次都单独提交CASE WHEN单条约0.35秒约1.2秒约3.8秒单条SQL无额外消耗INSERT ON DUPLICATE KEY约0.4秒约1.5秒约4.2秒比CASE WHEN稍慢因为要处理冲突临时表JOIN约0.5秒约1.8秒约5.1秒包含建临时表和写入时间需要说明的是这个数据在一个稳定负载的实例上测的网络延迟忽略不计。不同机器配置会有差异但比例关系基本稳定。4.1 各方案在典型场景下的实测数据从上面的表能看出CASE WHEN在1000条级别优势明显因为一次SQL交互搞定解析和提交成本最低。到了10000条耗时开始增加但依然远优于逐条。临时表JOIN在本次测试中耗时略高于CASE WHEN但它的优势不在小数据量而在于大数据量下的稳定性。我之前在另一个需求里测试过更新5万条数据CASE WHEN拼接出来的SQL文本长度约2MB执行时首先因为超过max_allowed_packet直接被拒。调整max_allowed_packet之后能执行但整个过程耗时约22秒且内存占用明显。临时表JOIN方案耗时约9秒全程稳定没有报错。这就是我为什么说方案没有绝对的好坏只有适合的规模。4.2 选型逻辑数据量、并发、可用性约束结合上面的数据我自己选型时会按下面的逻辑判断1000条以内无脑选CASE WHEN单条SQL。简单、直观、性能好。1000到5000条优先CASE WHEN但如果要更新的字段值各不相同且不想拼复杂的SQL可以考虑INSERT ON DUPLICATE KEY。5000到20000条临时表JOIN是更稳妥的选择特别是更新逻辑复杂时。20000条以上必须分批每批1000到2000条配合临时表或CASE WHEN都可以。如果单批太大锁竞争、binlog开销、主从延迟都无法控制。并发也是一个重要变量。上面的对比是在单一线程下测的。如果多个任务并发执行批量更新假设同时启动10个任务去更新同一张表的不同行那单批batch_size要减半甚至更小否则锁等待时间急剧上升系统更容易死锁。4.3 测试方法如何自己复现对比很多人在网上看到别人的性能数据直接照搬到自己的环境结果发现差异很大。原因很简单表结构、索引、数据分布、MySQL版本、硬件配置都不同。所以我不建议完全照搬任何人的数据而是自己动手测。复现方法很简单选一张有百万行数据的表最好有主键索引。准备一个1000、5000、10000条的数据集。分别执行逐条循环、CASE WHEN、INSERT ON DUPLICATE KEY、临时表JOIN各测三轮取平均值。在测试时关闭其他大查询避免干扰。同时监控SHOW PROCESSLIST和SHOW ENGINE INNODB STATUS看是否有锁等待。这样测完你就知道在你自己环境里该用什么方案了比任何博客里的结论都靠谱。5. 批量更新的高级心法死锁、主从延迟与数据一致性批量更新性能达标之后另一类问题开始浮出水面死锁、主从延迟、以及数据一致性。这些在数据量小的时候几乎感知不到数据量一大就变成必须面对的工程问题。5.1 死锁为什么偏爱批量更新很多人对死锁的理解是“两个事务互相持有对方需要的锁”但批量更新里更常见的死锁场景不是简单的双向等待而是锁顺序不一致导致的循环等待。举个例子。事务A要更新ID为1和2的两行事务B要更新ID为2和1的两行。如果A先锁了1B先锁了2然后A申请锁2B申请锁1就死锁了。批量更新时由于单条SQL内部更新多行InnoDB默认会按主键顺序加锁但在多条SQL分批循环更新时如果每批的数据范围有重叠就很容易出现锁顺序交错。我的经验是三个办法第一按主键排序。把要更新的数据在应用层按主键排好序再分批提交。这样所有事务获取锁的顺序一致能很大程度避免死锁。第二保持事务短小。每批数据量不要贪大锁持有时间短了死锁窗口就小。第三对死锁做重试。即使做了上面两步死锁仍可能发生。应用层要捕获Deadlock found when trying to get lock这样的错误然后做有限次数的重试。我在某跨平台系统里就是这么设计的死锁率从最开始的一天几十次降到了一周一两次。5.2 主从延迟与binlog开销还有一个容易被忽略的问题大批量更新在主库执行很快但binlog到了从库重放时可能把从库拖出明显的延迟。原因在于主库执行一个更新10000行的SQL时虽然是单条语句原子执行但InnoDB在生成binlog时是逐行记录变更的取决于binlog_format。从库在重放时同样要执行10000行的逐行变更。如果主库并发写入很高从库单线程复制很容易跟不上。解决思路如果业务允许把大事务拆成小批量提交给从库消化的时间。MySQL 5.7以后有MTS多线程复制但也要根据表结构和并发程度配置合理的工作线程数。监控Seconds_Behind_Master指标一旦持续增长要主动降级写入速度。5.3 如何保证批量更新可回滚、可验证批量更新最怕的事情是语句执行成功了但结果不对而且没法回滚。所以我在生产环境做任何批量更新前都会做四件事先SELECT圈定影响范围。用同样的WHERE条件先SELECT COUNT(*)看影响行数再随机抽几行看更新前的值确认范围和预期一致。用事务包裹并分批执行。把整个批量更新放进一个事务如果中途发现任何异常直接ROLLBACK。更新前后做快照对比。更新前把涉及的主键和旧值导出保存更新后抽样对比验证更新结果。在从库或测试库先跑一遍。哪怕时间紧迫也应该在隔离环境先执行一次确认SQL语法和索引使用都没问题。我把这套方法总结为“三查一备”查范围、查计划、查结果、备数据。线上更新千万条备份永远第一条。没有备份的批量更新就是在拿生产数据赌运气。6. 最后说点实在的批量更新语句不算什么高深技术但它直接影响线上系统的稳定性和响应速度。很多人会写UPDATE但不一定知道怎么把一万次更新压成一条、或者十条更不一定知道批量更新背后还有锁、日志、从库延迟这些连锁反应。回顾这几年做后端开发的经验我对批量更新的理解可以浓缩成三句话第一先确认WHERE条件能否走索引再做任何批量更新的优化。索引不到位其他优化都是空中楼阁。第二根据数据量选择方案不要一套方案走天下。500条和50000条的解法完全不同。第三批量更新必须考虑锁和复制性能和稳定性要同时抓。如果你现在正准备在线上执行一批UPDATE建议先停一下问自己三个问题这条SQL走索引了吗影响范围确认过吗数据备份了吗如果都能答上来那放心执行。如果答不上来先花十分钟做完这三件事再动手这十分钟通常能帮你省下半夜起来处理的麻烦。
