1. 一次半夜批量更新翻车实录问题从来不在SQL语法做后端开发这些年我处理过不少跟批量更新有关的线上事故。坦白讲绝大多数事故的根因不是SQL写错了而是更新方式选错了。我第一次真正重视批量更新方式这个问题是在一次商品价格批量调整的线上故障里。那天运营提了个需求全站一万两千多条商品要调整价格要求在半小时内生效。同事小张化名很自然地写了一段循环for 循环里逐条 UPDATE外面包了一个大事务。for (Product p : productList) { jdbcTemplate.update(UPDATE products SET price ? WHERE id ?, p.getNewPrice(), p.getId()); }代码看起来没有任何问题测试环境两千条数据跑得也还算快。结果一上生产直接出了乱子接口响应时间从 30ms 飙到 3 秒多数据库连接池被打满大量请求排队等待连接连带着其他业务一起遭殃。事后复盘问题根本不是语法而是逐条更新这种方式本身。我来拆解一下它到底慢在哪逐条 UPDATE 在默认 autocommit 模式下每条语句都要经历一次网络往返、一次SQL解析、一次事务提交刷盘。一万两千条就是一万两千次完整流程。哪怕单条只要 2ms累加起来也要 24 秒再加上连接池排队实际延迟被放大到一个完全不可接受的程度。这里有个很多人忽略的细节单条 UPDATE 的快和批量更新整体的慢并不矛盾。你单独看任何一条SQL执行计划都很快毫秒级就返回了。但批量场景下真正的瓶颈不在数据库执行本身而是三个看不到的开销网络往返次数。每条语句一次独立请求RTT 再低也架不住上万次累加。事务提交的开销。每条语句独立提交意味着日志刷盘、锁释放、行版本清理都要重复做。连接池的串行排队。循环里拿到连接后长时间占用池子一旦被占满所有新请求只能阻塞等待。这就是为什么批量更新从来不是写对 SQL的问题而是选对方式的问题。下面我把实际工程里能用的批量更新方式全部拆开讲一遍包括它们各自的原理、适用边界和实测数据。2. 主流批量更新方案拆解每种写法的原理与边界2.1 基础款CASE WHEN 单语句更新CASE WHEN 是最早被用来替代循环的方案也是新手最容易接受的方案。它的思路是把应用层循环合并成一条 SQL用 CASE WHEN 把每条记录的更新逻辑写进同一个 UPDATE配合 WHERE id IN (...) 圈定范围。UPDATE products SET price CASE id WHEN 1001 THEN 199 WHEN 1002 THEN 188 WHEN 1003 THEN 159 -- ... 更多 WHEN END WHERE id IN (1001, 1002, 1003, ...);原理上这条 SQL 在数据库内部只需要解析一次、生成一次执行计划然后逐行判断 id 命中哪个分支把对应的新值赋给 price。相比逐条循环省掉的是上万次网络往返和上万次执行计划生成。这个方案的优点非常直观实现简单、不需要额外表、事务边界天然统一。但它有两个容易被低估的限制。第一个限制是 SQL 文本长度。假设一条记录平均占用 40 字节更新一万条拼出来的 SQL 就是 400KB。MySQL 的 max_allowed_packet 默认值在 8.0 里是 64MB400KB 虽然不会直接爆掉但这么长的 SQL 在执行前的解析、网络传输上都会产生不小的 CPU 开销。我实测过单条 SQL 超过两万个 WHEN 分支时解析时间会肉眼可见地变长。第二个限制是灵活性。它适合那种每条记录的新值都是明确写死的场景。如果更新逻辑本身还要依赖另一张表或者新值要经过子查询计算CASE WHEN 写起来就非常别扭可读性也会迅速恶化。提示CASE WHEN 方案最适合几百到几千行的小批量更新。行数超过一万建议先拆批不要一条 SQL 硬扛。2.2 进阶款临时表 JOIN 全量更新当要更新的数据量级到几万甚至几十万时CASE WHEN 生成的超长 SQL 就不再是最优选了。这时候我更推荐临时表 JOIN的方案它也是我处理大数据量更新的默认选择。思路分三步先把待更新的数据id 和新值写入一张临时表然后给临时表加上主键或索引最后用 UPDATE JOIN 把临时表的数据合并到目标表。-- 第一步创建临时表并写入待更新数据 CREATE TEMPORARY TABLE tmp_products_price ( id INT PRIMARY KEY, new_price DECIMAL(10,2) ) ENGINEInnoDB; -- 第二步批量插入可以分多批也可以配合程序批量写入 INSERT INTO tmp_products_price (id, new_price) VALUES (1001, 199), (1002, 188), (1003, 159), ...; -- 第三步JOIN 更新 UPDATE products p INNER JOIN tmp_products_price t ON p.id t.id SET p.price t.new_price;这个方案的核心逻辑是把数据的传输格式从长文本变成紧凑的行记录。临时表里每一行就是一条记录MySQL 底层存取数据的效率远高于解析大段文本。更重要的是JOIN 更新时可以走索引数据库优化器可以根据表中数据分布选择最高效的执行路径这是 CASE WHEN 做不到的。我通常会把写入临时表这一步也做成批量插入每次 500 到 1000 行。这样既避免了临时表写入单次数据量过大也让整个更新流程的每个环节都是可控的。如果用 PostgreSQL写法会更直接不需要建临时表用 VALUES 列表配合 FROM 即可UPDATE products p SET price t.new_price FROM (VALUES (1001, 199), (1002, 188), (1003, 159)) AS t(id, new_price) WHERE p.id t.id;临时表方案也不是没有代价。它需要多一次建表和插入操作对于几千行的小数据量来说这些额外操作的耗时可能比直接 CASE WHEN 还要高。所以它真正的优势区间是中大规模数据量尤其是数据行数达到一条 SQL 拼不过来的量级时。2.3 投机取巧款INSERT ... ON DUPLICATE KEY UPDATE几乎每个团队里都有人用过这种写法利用数据库的冲突则更新语义实现批量更新。它在 MySQL 里叫 ON DUPLICATE KEY UPDATE在 PostgreSQL 里对应 ON CONFLICT DO UPDATE。INSERT INTO products (id, price) VALUES (1001, 199), (1002, 188), (1003, 159) ON DUPLICATE KEY UPDATE price VALUES(price);这条 SQL 的语义是如果 id 在表里不存在就插入新记录如果存在触发唯一键冲突则把 price 更新为新值。听上去很方便但它有一个非常隐蔽的坑它不只是更新它还会插入。很多人在不需要插入的业务里误用了它结果每次批量更新完总会多出一些本不该出现的记录。比如你要更新的 id 列表来自上游系统的数据其中有两个 id 在本地表根本不存在ON DUPLICATE KEY UPDATE 会默默把它们插入进来而这些孤儿记录往往没有任何业务上下文后续排查起来非常痛苦。另外一个坑是 MySQL 8.0.20 之后 VALUES() 函数正式被废弃。上面这条 SQL 在新版本里虽然还能跑但会报出一堆警告官方建议改成别名写法INSERT INTO products (id, price) VALUES (1001, 199), (1002, 188) AS new ON DUPLICATE KEY UPDATE price new.price;所以我的建议是这个方案只用在真正新增或更新的业务场景比如同步上游商品信息时新的就插入、旧的就更新。如果纯粹是只更新已存在行不要用这种写法老老实实走前两种方案。2.4 方言专属款MERGE / UPDATE ... FROM不同的数据库对批量更新有自己的专属语法。SQL Server 和 Oracle 有 MERGEPostgreSQL 有 UPDATE ... FROMMySQL 则不支持 MERGE。-- SQL Server / Oracle 的 MERGE 写法 MERGE INTO products p USING (SELECT 1001 AS id, 199 AS new_price UNION ALL SELECT 1002, 188 UNION ALL SELECT 1003, 159) t ON p.id t.id WHEN MATCHED THEN UPDATE SET p.price t.new_price;这一类语法在执行计划上通常和临时表 JOIN是等价的都是先构建一个数据集再做匹配更新。它们的区别主要在工程层面。MERGE 的缺点是语法冗长而且 SQL Server 里 MERGE 在并发场景下有著名的死锁和隔离级别问题很多 DBA 明确禁止在生产使用 MERGE。相比之下SQL Server 的 UPDATE ... FROM 写法更低调也更安全。从实践角度看除非你的团队长期只用一种数据库并且已经做过充分测试否则我不建议把 MERGE 作为团队的默认方案。它会把迁移成本抬高将来无论是 MySQL 迁 PostgreSQL还是换云数据库方言专属语法都会变成改造成本的一部分。2.5 削峰款队列加异步合并更新上面讲的都是同步更新也就是应用发起请求、数据库立刻执行、等待结果。但现实工程里很多批量更新并不需要立刻生效。比如订单超时关单、优惠券过期、用户标签重新计算这类任务延迟几秒甚至几分钟完全能接受完全没必要在业务链路里同步更新。这类场景我会优先考虑队列加异步合并的架构。核心思路是业务方只需把更新请求发给消息队列后台的消费组件攒批每 N 秒或者攒够 N 条取出来合并做一次批量更新。// 伪代码消费端每 5 秒攒一批合并成一次批量更新 ListProduct batch new ArrayList(); while (true) { Product p queue.poll(5, TimeUnit.SECONDS); if (p ! null) { batch.add(p); if (batch.size() 500) { batchUpdate(batch); // 内部走 CASE WHEN 或 JOIN batch.clear(); } } else if (!batch.isEmpty()) { batchUpdate(batch); batch.clear(); } }这个方案最大的价值不是 SQL 写法本身而是把峰值压力摊平。比如运营批量改价格一万多个请求如果在同一秒打到数据库哪怕都是简单 UPDATE也够数据库喝一壶。但如果把它们放到队列里每 5 秒更新 500 条数据库的负载曲线会平缓得多主从延迟也不会出现明显尖峰。代价是引入了额外的中间件和延迟窗口。所以它只适合最终一致性能接受的业务。如果上游要求你改完必须立刻查到新值那就不能用异步还是回到同步方案。2.6 工程兜底款分批循环更新最后一种不是独立的 SQL 方案而是工程上的组合策略。很多场景下单条 SQL 批量更新会把事务搞太大锁范围太广主备延迟飙高。这时候就算你有 CASE WHEN 或临时表方案也得拆批执行。所谓分批循环更新就是设定一个阈值比如每批 500 条把一个大批量更新拆成多个小批量更新循环执行。每批内部用 CASE WHEN 或临时表 JOIN批与批之间独立事务。int pageSize 500; for (int i 0; i productList.size(); i pageSize) { ListProduct subList productList.subList(i, Math.min(i pageSize, productList.size())); batchUpdateByCaseWhen(subList); // 每批单独提交 }分批的价值有三个。第一每批事务都很短锁持有时间被控制在毫秒级不会长时间阻塞其他业务的读写。第二主从延迟可控不会因为一个大事务导致从库长时间落后。第三出错时重试粒度小一批失败只需要重跑这一批不用整个任务推倒重来。我在实际项目里把分批和前面几种方案组合着用。比如线上数据量 50 万行的全量更新我的常规做法是每批 1000 条走临时表 JOIN批与批之间 sleep 50ms让数据库喘口气。这样跑完全程主库负载不高从库延迟曲线也稳。3. 实测对比一万行数据六种方式到底差多少前面讲的都是原理可能有人觉得差不多就行。但批量更新的方案选择性能差距可以到十倍以上。我整理了一份自己的压测数据环境是 MySQL 8.0.28、4 核 8G、普通 SSD商品表 20 万行随机挑选 1 万行更新 price 字段。每种方案跑 5 次取中位数。更新方式耗时中位数说明逐条 UPDATEautocommit约 8.2 秒网络往返和独立提交是主要开销逐条 UPDATE 单事务约 1.9 秒提交合并了但网络和解析没省CASE WHEN 单条万行约 0.65 秒网络往返大幅减少但 SQL 解析偏重临时表 JOIN约 0.48 秒传输格式紧凑执行计划最优ON DUPLICATE KEY UPDATE约 0.58 秒多了一步冲突判断分批 500 条 CASE WHEN约 0.72 秒比整批略慢但锁和延迟更稳这组数字最有意思的对比是前两行同样的业务逻辑仅仅是去掉 autocommit、改用单事务性能就从 8.2 秒降到 1.9 秒提升四倍多。这说明在小数据量下面每条语句独立提交的代价远高于 SQL 本身的执行代价。再对比 CASE WHEN 和临时表 JOIN差距虽然没有那么夸张但背后的原因值得说一说。CASE WHEN 的耗时主要花在解析超长 SQL 上临时表 JOIN 则把大量文本开销换成了紧凑的行格式传输MySQL 在 InnoDB 存储引擎上做 JOIN 更新时走主键索引的匹配速度比逐行判断 CASE 分支更快。ON DUPLICATE KEY UPDATE 比 CASE WHEN 慢一点因为它除了更新还要做冲突检测。不过它的优势在于更新加插入一步完成如果业务本身就是新增或更新语义综合收益反而更高。分批的方案单批看肯定不是最快的多了循环和等待的开销但在真实生产环境里我通常更看重它带来的稳定性锁等待时间、主从延迟、失败恢复成本。表格里的耗时只是单一维度真正的选型要看下一节的整体决策。4. 选型决策不同业务场景到底该用哪一款聊完原理和实测我给出一个可以直接套用的选型框架。它不是教条而是我在多次事故和重构中总结出来的判断顺序。第一看数据量级。几百条以内CASE WHEN 是最省事的代码简单、事务单一、性能足够。几千到一两万条CASE WHEN 依然可用但要注意 SQL 长度建议拆成几批。几万条以上直接上临时表 JOIN别犹豫。第二看业务语义。如果每次更新的数据来源是新增或更新的同步逻辑用 ON DUPLICATE KEY UPDATE 或 ON CONFLICT 能省掉一次判断。如果纯更新已存在记录别用 UPSERT避免制造孤儿数据。第三看一致性要求。强一致场景必须同步更新选 CASE WHEN 或临时表 JOIN最终一致可接受优先考虑队列异步合并降低数据库压力。第四看运维约束。如果团队有慢查询告警阈值比如超过 1 秒就报警一条执行 0.65 秒的万行 CASE WHEN 可能刚好在阈值之下但到了两万行就可能突破。如果 DBA 明令禁止大事务那就必须走分批。如果主从延迟有硬性指标大语句方案要谨慎评估。第五看团队技术栈。如果团队主要用 MySQL临时表 JOIN 是最通用的答案。如果团队在 PostgreSQL 上UPDATE ... FROM 的 VALUES 写法更简洁。如果团队有跨库迁移计划尽量少用方言特性把批量更新逻辑收敛到一个公共组件里。我用一个表格把这套逻辑总结成快速判断表方便大家直接对照业务特征推荐方案不推荐方案行数几百字段简单CASE WHEN临时表过度设计行数几万字段简单临时表 JOIN单条超长 CASE WHEN新增或更新语义UPSERT逐条 SELECT 判断强一致性低延迟同步拆分更新异步队列最终一致削峰需求队列 合并更新同步大事务SQL Server 存量环境UPDATE ... FROMMERGE并发问题这套框架在每个团队里可能还要微调但核心原则是一致的不要只盯着 SQL 执行时间要把网络开销、事务边界、锁范围、运维监控、团队维护成本全部放进决策模型。5. 批量更新避坑清单每一条都是用事故换来的批量更新这个主题看起来很基础但坑是真的多。我把自己踩过的、帮别人擦过屁股的坑整理成一份清单每一条都有真实的线上教训。5.1 连接池被打满的隐藏触发点很多人以为连接池打满是因为并发高其实在批量更新场景里最常见的触发点是单个请求长占连接。循环逐条更新时一个请求可能占着连接好几秒如果同时有几个这样的请求进来连接池瞬间见底。我见过一个系统连接池 50 个业务高峰期同时来了 6 个批量更新请求每个要占 2 秒多结果 50 个连接全部被占完其他所有业务全部排队。规避措施很简单批量更新必须拆小、快进快出同时给批量更新单独配置一个小的连接池避免它拖垮主业务的连接。5.2 大事务带来的锁与主从延迟一条 SQL 更新一万行InnoDB 会在这万行上加行锁如果是范围条件还可能升级成间隙锁。锁持有的时间越长其他事务被阻塞的概率越高。主从同步层面一个大事务在从库上要执行同样久期间从库数据落后读多写少的系统立刻就会出问题。我的经验是单事务更新行数控制在 2000 行以内相对安全具体数值要结合行宽和索引情况测试。超过这个量级先拆批。5.3 字段被意外置空批量更新最经典的生产事故之一是批量操作里混入了 null 值。比如运营给的 Excel 里价格列有几个空格程序把它解析成 null然后 CASE WHEN 或临时表 JOIN 更新时直接把原本的价格覆盖成了 null。规避办法有两个层面程序层面对批量更新的每个字段做严格判空和类型校验数据库层面给关键业务字段设置 NOT NULL 约束让错误在写入前就暴露。5.4 审计日志和触发器被绕过ON DUPLICATE KEY UPDATE 和 REPLACE INTO 在语义上都不是单纯的 UPDATE。REPLACE INTO 在 MySQL 里是先删后插如果表上有自增主键它会把自增 ID 消耗掉还会触发 DELETE 相关的触发器而不是 UPDATE 触发器。如果你的系统依赖 binlog 或审计日志做数据分析REPLACE INTO 会让行记录的历史主键消失审计链路直接断掉。我遇到过一次某团队用 REPLACE INTO 做批量更新结果下游数仓的商品变更流水表里全是 INSERT 记录原本的 UPDATE 流水全没了数据对账对了一个星期。后来统一改成 ON DUPLICATE KEY UPDATE 或临时表 JOIN审计才恢复正常。5.5 字符集和隐式转换让索引失效临时表 JOIN 时如果临时表的 id 字段类型或字符集和目标表不一致MySQL 会做隐式转换导致索引失效JOIN 更新变成全表扫描。比如目标表 id 是 int临时表 id 建成了 varchar表面上数据能匹配上实际执行计划可能不是你想的那样。每次建临时表前我都习惯用 SHOW CREATE TABLE 确认字段类型和源表保持一致。5.6 分批更新的边界处理分批更新最容易出 bug 的地方是边界。比如 list.size() 恰好是 500 的整数倍时最后一轮 subList 会不会越界分页条件用 id 范围和 limit 组合时更新过程中数据发生变化会不会跳过某条。我习惯在分批更新后加一个总数校验统计实际受影响行数之和和预期值对比不一致就告警。这个习惯帮我抓出过好几次隐藏 bug。写完这些我想说一句真心话批量更新看起来是个老掉牙的话题但我每次团队里出现相关线上事故最终定位到根因时绝大部分都能归到选型不合理或边界没处理这两类问题上。把上面的方案原理、实测数据和避坑清单消化掉足够你应对日常开发里 95% 的批量更新需求。如果遇到特别极端的场景比如千万级数据更新欢迎顺着这套思路去研究更细的并行方案和分库分表策略但核心判断逻辑依然不变先确认业务约束再选更新方式最后用监控数据验证。