我接手过的系统里凡是运营后台带“批量”两个字的功能十有八九最后都要落到数据库的批量 UPDATE 上。比如刚才还在群里有人问勾选了几百个商品要改价格一条条 UPDATE 太慢了有没有办法一条 SQL 全改完这是个特别典型的场景——每条数据要更新的值还不一样有的是改价有的是改状态有的只是改个排序权重。处理这种需求绕不开两条主流路线一条是应用层循环着逐条 UPDATE另一条是把多条不同值的更新拼成一条带 CASE WHEN 的大 SQL 一次性执行。两条路我都走过也在线上踩过不少坑数据量从几十条到几万条都试过性能和稳定性差别非常明显。这篇就把两种方式的实现细节、性能底细、适用边界以及我踩过的那些问题一次性讲清楚最后再把临时表 JOIN 和 INSERT ON DUPLICATE KEY UPDATE 这两个变种也拉出来对比一下帮你按场景做选择。1. 先说清楚批量UPDATE到底卡在哪1.1 “批量更新”分两种别混为一谈很多人口中的“批量更新”实际上指的是两种完全不同的事。第一种很简单——把所有满足条件的行改成同一个值比如把状态为 0 的订单全部改成已取消一条UPDATE orders SET status 2 WHERE status 0就完了。这种需求根本不存在性能焦虑也没人专门讨论。真正让后端头疼的是第二种一批记录每条都有自己独立的新值。同样勾选了 2000 个商品商品 A 要改成 19.9 元商品 B 要改成 29.9 元商品 C 要改成 39.9 元……值各不相同。SQL 语法本身对“一次给多行分别赋不同值”的支持非常弱你没法写一条标准的 UPDATE 说“第 1 行改成这样第 2 行改成那样”。所以只能靠应用层去组织这些更新指令组织方式就演化出了两种主流方案。1.2 逐行不同值更新时的两个性能瓶颈先看最直观的做法既然是每条数据值不一样那就一条一条来循环里每行执行一次 UPDATE。for row in rows: cursor.execute( UPDATE t_product SET price %s WHERE id %s, (row[price], row[id]) )这写法在数据量小的时候没人觉得有问题可一旦数据量上来瓶颈就藏不住了。第一个瓶颈是网络交互次数N 条记录就是 N 次完整的 SQL 发送、解析、执行、返回每一条都要一个网络往返。本地数据库可能还好一次往返零点几毫秒跨机房的话一次可能是几毫秒甚至几十毫秒2000 条乘下来光网络耗时就在几秒到几十秒之间。第二个瓶颈是锁和事务的持有时间如果把这 2000 条 UPDATE 包在一个事务里循环期间这些行锁一直握在手里不释放其他会话想更新同一行就只能等。所以批量差异更新的本质问题是一个取舍问题是减少应用层与数据库的交互次数把复杂度集中到单条 SQL 里还是保持 SQL 简单用交互次数换可维护性这两种取舍正好对应了标题里的两种方式。2. 方式一逐条UPDATE循环执行2.1 最朴素的实现长什么样逐条方式的代码很好理解任何语言里都是“遍历 执行 UPDATE”。我见过的大多数第一版实现都是这种。// Java 示例常规 for 循环逐条更新 for (Product p : productList) { jdbcTemplate.update( UPDATE t_product SET price ?, status ? WHERE id ?, p.getPrice(), p.getStatus(), p.getId() ); }这个写法的优点一眼就能看出来逻辑直白每条更新的值、条件都清清楚楚哪条出错了立刻能定位到具体数据每条 UPDATE 都是独立的小操作影响行数也可以分别拿到方便做校验。对于只有十几二十条数据的低频后台操作这完全足够不会有人因此说你写得差。提示逐条 UPDATE 最大的问题不在于 SQL 本身而在于循环里的网络往返。本地测试时感觉不到一旦放到线上环境、数据库和应用服务器之间隔着网络延迟立刻被放大。2.2 性能画像2000 条数据要跑多久我特意做过一次对比测试。同一台测试库2000 条商品记录每条价格不同分别用逐条循环和后面要讲的 CASE WHEN 方式执行。结果是这样的逐条循环因为走的是连接池每条大概 0.8 毫秒到 1.5 毫秒2000 条合计约 2 到 3 秒而 CASE WHEN 方式一条 SQL 执行完总耗时 40 毫秒左右。差距大概 50 到 70 倍。这个差距几乎全部来自 SQL 交互次数。数据库执行 2000 条简单 UPDATE 本身并不慢慢的是 2000 次网络往返以及 2000 次 SQL 解析和 2000 次事务提交的资源开销。如果连接还开了自动提交那压力更大等于做了 2000 次独立的提交。如果手动开启事务并在循环结束后统一提交网络往返仍然躲不掉锁的持有时间还会被拉长。2.3 哪些场景下逐条仍是更合适的选择我不是来劝大家彻底抛弃逐条方式的——它是“笨”但有些场景它就是更稳。比如更新数量非常少只有几条怎么写都无所谓逐条反而更清晰再比如每行更新的条件根本无法归纳成同一个模式必须依赖程序的复杂判断才能确定更新哪些字段用 CASE WHEN 去拼一个通用 SQL 反而会累死还有一种情况是业务上必须要逐条检查影响行数比如更新失败的要单独记录下来重试逐条方式处理这种逻辑非常自然。逐条方式只要加上一个事务壳并把提交时机控制好在几百条数据量级下完全够用。真正要避免的是“逐条 自动提交”这种最差组合。# 更稳的写法手动控制事务统一提交 conn.begin() try: for row in rows: cursor.execute( UPDATE t_product SET price %s WHERE id %s, (row[price], row[id]) ) conn.commit() except Exception: conn.rollback() raise2.4 逐条更新的隐藏成本清单除了时间和锁逐条更新还有一个常被忽略的成本——日志量。每条 UPDATE 都会产生对应的 binlog 记录2000 条循环产生 2000 条独立事务记录而批量方案如果放在一个事务里产生的 binlog 内容总量可能相差不大但事务记录数量天差地别。主从复制时从库要处理的事务条目越少延迟往往越低。另外逐条更新中途一旦某条失败如果没有事务包裹前面已经更新成功的数据就无法自动回滚数据会处于一半新一半旧的状态手工恢复非常费劲。3. 方式二CASE WHEN拼接一条SQL搞定批量更新3.1 核心语法一个CASE搞定多行多列逐条方式的问题在于交互次数多那自然想得到能不能把 N 条 UPDATE 合并成一条MySQL 里确实有办法就是借助 CASE 表达式在一条 UPDATE 语句里按条件对不同行赋不同的值。UPDATE t_product SET price CASE id WHEN 1 THEN 19.9 WHEN 2 THEN 29.9 WHEN 3 THEN 39.9 END WHERE id IN (1, 2, 3);这条 SQL 的意思很直接当 id 等于 1 时把 price 改成 19.9等于 2 时改成 29.9等于 3 时改成 39.9。一次网络往返一条语句全部改完。还可以在同一个 SET 里同时更新多个字段用逗号分隔。UPDATE t_product SET price CASE id WHEN 1 THEN 19.9 WHEN 2 THEN 29.9 END, status CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 END WHERE id IN (1, 2);这里有个细节同一个 id 在多个字段的 CASE 里要分别出现一次这是因为 CASE 表达式是逐字段独立计算的。如果某些行的某个字段不需要更新要在 CASE 里加上ELSE 原字段名把旧值保留住否则它会被置成 NULL。这个坑后面细说。3.2 应用层怎么安全地拼出这条SQL写起来最能体现“应用层拼 SQL”的功力。以 Python 为例核心就是把 id 和新值拼成 CASE 的 WHEN-THEN 对。ids [] when_clauses [] for row in rows: ids.append(str(row[id])) # 注意name 是字符串需要转义或参数化 when_clauses.append( fWHEN {row[id]} THEN {escape_str(row[name])} ) sql ( UPDATE t_user SET name CASE id .join(when_clauses) f END WHERE id IN ({,.join(ids)}) )拼接字符串听上去简单但非常容易踩 SQL 注入和类型转换的坑。id 应该强转成整数再拼name 这类字符串必须做转义。如果团队的规范不允许手动拼 SQL可以用支持动态 SQL 到 ORM 框架从语法层面约束传入参数。实际上很多 ORM 框架能自己生成这种批量更新语句但仍需先在测试环境检查它生成的 SQL 是否执行了全表扫描。3.3 为什么它快一次解析、一次往返、一次提交CASE WHEN 方案快的核心是它把“N 次交互”压缩成了“1 次交互”。数据库只需要解析一次 UPDATE规划一次执行计划然后按主键索引逐行定位、逐行更新。网络耗时从 N 个 RTT 变成 1 个 RTTSQL 解析开销也从 N 次变成 1 次整体耗时自然掉了一个数量级。InnoDB 引擎内部仍然是一行一行更新的但那是引擎内部的事不需要应用层参与省掉的时间非常可观。拿我测试的标准1000 条数据逐条方式在本地网络里也要 1 秒以上CASE WHEN 方式基本在 30 到 60 毫秒。2000 条也就是 100 毫秒以内级别的表现。对这个量级接口的响应时间已经不再是问题。3.4 必须处理好的三个风险点CASE WHEN 不是万能的它有三道坎。第一道坎是 SQL 长度。每增加一条数据SQL 文本就会变长。1000 条可能 50KB10000 条可能 500KB。MySQL 的max_allowed_packet参数决定了客户端和服务器之间能传输的最大包体积默认通常是 64MB但生产环境为了安全可能会调小。一旦 SQL 超过限制直接报packet too large。所以数据量大的时候不能一把梭要分批拼接。第二道坎是锁范围。一条 UPDATE 语句会更新 WHERE 条件命中的所有行执行期间这些行都要加行锁。语句本身很快的话锁持有时间很短但如果数据量特别大执行了几秒甚至十几秒其他并发更新就会阻塞。所以量越大越要拆批。第三道坎是数据一致性中的 NULL 陷阱。前面提到的ELSE必须给足否则未匹配的行会把字段更新成 NULL。比如你只想改 3 条记录的 price但业务代码拼出来只写了这 3 条记录的 CASE目标是执行UPDATE t_product SET price ... WHERE id IN (1,2,3)如果是逐条方式其他行的 price 完全不受影响。但 CASE WHEN 方式如果写成只对 id1,2,3 赋值而缺少 ELSE其他行并不会被更新因为 WHERE 已经限定只处理这 3 行了。真正的风险在于WHERE 条件万一没加或者 IN 范围比预期大未列出的行就会变成 NULL这是生产事故级别的问题。小心CASE WHEN 拼完之后一定要检查生成的 SQL 里有没有 WHERE 条件。宁可在代码里写断言检查WHERE not in sql就抛异常也不要拿脸去试。4. 两个常用变种临时表JOIN与INSERT ON DUPLICATE KEY UPDATE4.1 临时表JOIN大数据量下更稳的升级版CASE WHEN 在几千条以内很舒服但当数据量到了一万、两万甚至更多单条 SQL 体积过于庞大解析和网络传输开始变得沉重。这时可以考虑临时表 JOIN 的方式思路是换个角度把要更新的新值先批量 INSERT 进一张临时表然后用一次 UPDATE JOIN 把临时表的新值回写到目标表。-- 1. 建临时表 CREATE TEMPORARY TABLE tmp_product_price ( id INT PRIMARY KEY, price DECIMAL(10,2) ) ENGINEInnoDB; -- 2. 批量插入新值可以分多批执行 INSERT INTO tmp_product_price (id, price) VALUES (1, 19.9), (2, 29.9), (3, 39.9); -- 3. 用 JOIN 完成批量更新 UPDATE t_product p JOIN tmp_product_price t ON p.id t.id SET p.price t.price;临时表是会话级的连接断开后自动删除用完手写 DROP 也行。这种方式的好处是每一批 INSERT 都不长UPDATE JOIN 语句本身短小精悍而且临时表里通常只有当前批次的数据JOIN 用主键索引很快。数据量大时这是我最推荐的方式因为它的每一步都在可控范围内。4.2 INSERT ON DUPLICATE KEY UPDATE主键/唯一键下的简洁选择如果待更新的目标表本身有主键或者唯一键而且你需要更新的字段不算太复杂还有另一个思路把新数据直接 INSERT 进目标表利用唯一键冲突触发 UPDATE。INSERT INTO t_product (id, price, status) VALUES (1, 19.9, 2), (2, 29.9, 3), (3, 39.9, 1) AS new ON DUPLICATE KEY UPDATE price new.price, status new.status;注意VALUES()函数在 MySQL 8.0.20 版本开始被标记为废弃推荐用上面这种AS new别名方式引用新值。如果还在用老写法升级数据库后日志会刷一堆 deprecation 警告。这个方式有个前提目标表的其他字段要么有默认值要么允许 NULL因为你 INSERT 时没写的列会被填默认值冲突后 UPDATE 也只会更新 ON DUPLICATE KEY UPDATE 里指定的列其他列不受影响。如果只想更新部分字段其他列希望保持原值需要把原值也写进 INSERT 语句或用更复杂的写法容易出错需要谨慎。4.3 四种方式横向对比方式网络往返单条SQL体积可读性适用量级主要风险逐条UPDATEN次小最好百条以内网络耗时高锁持有时间长CASE WHEN1次大中千条以内分批SQL过长容易漏WHERE临时表JOIN3~N次小较好万条级步骤多临时表管理需注意INSERT ON DUPLICATE KEY1次较大较好依赖主键/唯一键未写列会受影响版本差异需留意选择逻辑我一般这么判断百条以内逐条加事务就行别折腾千条以内 CASE WHEN 最省事上万条用临时表 JOIN稳字当头如果恰好是更新全字段且表有主键INSERT ON DUPLICATE KEY UPDATE 可以顺手用。5. 实操复盘与问题排查5.1 一次线上批量改价的重构过程有一回做某电商后台的改价功能运营一次性最多选 500 个商品改价第一版实现就是最简单的 for 循环逐条 UPDATE。单机测试的时候没有任何异常但一上预发环境接口平均响应时间接近 3 秒运营频频反馈“转圈好久”。我当时的处理步骤先是在测试环境模拟了 500 条数据的两种方式对比耗时差距确认后把代码改造成拼接 CASE WHEN 的写法。为了保证安全加了三道保险第一先用SELECT COUNT(*) FROM t_product WHERE id IN (...)核对本次影响行数第二拼完 SQL 后检查 WHERE 条件存在第三把 500 条数据拆成 5 批每批 100 条执行批间稍微停顿几十毫秒避免单条 SQL 过长。改造后接口耗时降到了 200 毫秒以内操作体验明显改善。5.2 常见问题速查表现象可能原因排查方法解决方案UPDATE 后部分行没变化id 类型不一致比如字符串和整数比较SELECT * FROM ... WHERE id 1检查拼接时统一转成整型提示 packet too large单条 SQL 超过 max_allowed_packetSHOW VARIABLES LIKE max_allowed_packet分批执行或调大参数锁等待超时事务大、执行时间长查询performance_schema.data_lock_waits拆批、缩短事务更新后字段变成 NULLCASE 里缺 ELSE 或 WHEN 没覆盖全对比更新前后数据快照补 ELSE 保留原值或精确 WHERE8.0 日志警告 VALUES() deprecated版本升级后旧写法被弃用查看错误日志改为AS new别名写法主从复制延迟变大单事务更新量太大binlog 集中看从库Seconds_Behind_Master分批执行降低单事务体积5.3 生产环境执行的几条建议无论选哪种方式生产环境执行批量 UPDATE 前我都建议按下面这个检查清单过一遍先在测试库把 SQL 的 EXPLAIN 执行计划看一眼确认用到的是索引而不是全表扫描然后统计 WHERE 条件的命中行数和业务预期对比再确认当前是业务低峰期避开大促和定时任务高峰最后加上事务失败要能回滚。分批更新时批间可以加一个小的不可见的延迟比如 100 毫秒这条对主从延迟特别有效尤其当数据的单行值很大、binlog 体积不小的时候。我个人在实际操作中最大的体会是批量更新没有银弹核心就是控制三个东西交互次数、SQL 体积、锁的持有时间。逐条和 CASE WHEN 两种方式代表了两个极端而临时表 JOIN 是它们之间的平衡点。你只要把这几条原则记在心里遇到批量更新的需求基本都能在几分钟内判断出该用哪一种。最后再分享一个小技巧上线前先在测试库开general_log把应用实际执行的批量 SQL 捞出来人工看一遍多花五分钟能省掉半夜被叫起来处理事故的整个晚上。