去年线上有个状态变更接口一次性要把3000多条订单的状态改成“已完成”。我当时图省事直接在Service里写了个for循环循环调用Mapper的单条update。结果上线没几分钟接口就超时了数据库会话被打满运维直接来找我喝茶。那次之后我把MyBatis批量更新SQL的几种玩法、底层执行流程和常见坑都翻了个底朝天今天把这些经验整理成一篇完整的实战笔记。这篇内容适合谁看已经会写单条update、但一遇到“要更新几千行数据”这种场景就不知道选哪种方案的Java开发也想搞清楚foreach拼接和case when到底哪个更快、哪个更稳的同学都可以直接照着往下看。我会从最坑的写法讲起再给能落地的方案最后附上我实测的性能数据和排查思路。1. 先说结论三种主流的批量更新SQL玩法对比1.1 为什么for循环update是最容易踩的坑很多人在没接触过批量更新之前第一反应都是“那就循环调用update方法”。这种写法在数据量小的时候没有任何问题10条、20条数据数据库毫秒级就返回了谁都不会觉得慢。可一旦数据量到了几百上千问题就来了你每执行一次updateMyBatis都要经过一次完整的JDBC流程——创建PreparedStatement、绑定参数、发送到数据库、等数据库返回结果、再关闭Statement。这里有个关键点MyBatis默认的ExecutorType是SIMPLE。在SIMPLE模式下每一次update调用都会重新创建StatementHandler也就是完整走一遍prepare、execute、close。哪怕你把数据库连接池配得很好连接可以复用但网络往返是省不掉的。假设一次单条update从应用服务器到数据库服务器的网络RT是10ms1000条数据就是10秒这还没算上事务提交和数据库侧的锁等待。更隐蔽的坑是事务。很多Spring项目里直接在一个事务方法里做for循环更新看起来是一条一条执行实际上这些update都在同一个事务里。第一条更新拿到行锁后续的更新可能被同一张表上的其他写操作阻塞。也就是说你不仅要等自己的1000次网络往返还要等别人释放锁。所以线上那种“一个批量接口拖垮整个库”的事故多半就是这个原因。从MyBatis源码的角度看BaseExecutor的update方法会走到doUpdateSimpleExecutor的doUpdate就是每次new一个PreparedStatementHandler然后执行。这从机制上就决定了它不适合大批量操作。我后面会讲到的ExecutorType.BATCH走的则是另一个分支它在BatchExecutor里攒一批语句最后一次性executeBatch这才是JDBC层面真正意义上的批处理。1.2 批量更新的三种正确玩法先把结论摆出来后面再逐个拆细节。我常用的批量更新SQL方案就三种方案SQL形态核心优点核心缺点适用场景foreach拼接多条UPDATE一条Statement里包含多条分号分隔的UPDATE写法直观每条update独立易读需要开启allowMultiQueriesSQL文本过长有隐患中小批量单次几百条case when拼单条UPDATE一条UPDATE用CASE WHEN批量赋值只用一次网络往返性能稳定动态条件容易导致索引错位写起来费劲大批量字段固定的更新ExecutorType.BATCHJDBC Batch批量发送底层走预编译批量执行性能最好受驱动程序影响大返回结果难直接使用超大批量不关心单条结果这三种方案我都用过。这里先强调一下for循环不叫批量更新SQL它只是“循环执行单条SQL”不管是从网络开销还是数据库压力上看都不适合批量场景。下面的内容我按难度从低到高把每种方案的细节、坑、以及为什么这样设计讲一遍。2. foreach拼接多条UPDATEMyBatis XML里的细节与陷阱2.1 最直观的写法与allowMultiQueries先看最直观的写法。在Mapper接口里定义一个方法int updateBatch(Param(list) ListOrder orderList);对应的XMLupdate idupdateBatch foreach collectionlist itemitem separator; update t_order set status #{item.status} where id #{item.id} /foreach /update这段XML生成出来的SQL是update t_order set status ? where id ?;update t_order set status ? where id ?;...注意这里用的是分号分隔。问题来了MySQL的JDBC驱动默认不允许一条Statement里出现多条分号分隔的SQL除非你在JDBC连接串上显式加上allowMultiQueriestrue。jdbc:mysql://localhost:3306/test?allowMultiQueriestrue这个参数我第一次用的时候漏掉了结果线上直接报SQLSyntaxErrorException当时还以为是XML写错了。排查了半天才发现是连接参数的问题。另外allowMultiQueriestrue是有安全隐患的。MyBatis的#{}参数化虽然能防止SQL注入但如果你在XML里不小心写了${}拼接又开了多语句支持那就等于给SQL注入开了大门。所以我的原则是能用#{}绝不用${}同时这个参数只给确实需要多语句更新的数据源开启不要全项目所有数据源都默认打开。2.2 参数传递的Param约定用foreach的时候很多人会忽略Param(list)这个注解。如果方法签名是int updateBatch(ListOrder orderList)而不加ParamMyBatis会怎么处理它会把这个List包成一个key为list、collection的Map但不同MyBatis版本对这个默认key的处理细节会有差异。为了保险建议显式写上Param。还有一点foreach除了item还有个容易被忽视的index属性。在List场景下index就是循环下标在Map场景下index是key。很多时候你不一定需要index但如果你的SQL里要根据循环次数拼接某些东西比如计算批次序号那就很有用。foreach collectionlist itemitem indexindex separator; update t_order set sort_no #{index} where id #{item.id} /foreach这里#{index}直接取的是List里元素的下标。这种写法在需要按位置更新的时候很顺手但要注意index是从0开始的。2.3 连接串参数rewriteBatchedStatements到底影响什么接下来是另一个高频困惑。网上很多文章说“批量更新慢加上rewriteBatchedStatementstrue就好了”。但很多人加了之后发现没效果为什么因为他用的是foreach拼多条SQL而不是JDBC Batch。rewriteBatchedStatements这个参数只对JDBC的addBatch()/executeBatch()生效。它的原理是当驱动发现你在用批量执行会尝试把多条同构SQL重写成一条多VALUES的插入或一条带有多个分号的多语句从而减少网络往返。注意MySQL驱动8.0版本里这个参数默认值是false如果你的批量插入性能上不去很大的原因就是它。但如果你用的是ExecutorType.BATCH方式这个参数才会真正发挥威力。如果你只是用foreach在XML里拼多条SQL那么rewriteBatchedStatements跟你没关系你真正需要的是allowMultiQueries。我见过有个项目把这两个参数搞混了线上批量更新一直慢加了rewriteBatchedStatements没反应后来还是一段一段拆SQL才解决。所以先把概念理清楚一个管多语句解析一个管JDBC Batch重写别指望一个参数解决所有问题。3. case when一条SQL批量更新多行索引错位与NULL值的血泪教训3.1 XML模板与代码构造如果你要更新的数据字段相对固定我更推荐用case when拼单条SQL。核心思想很简单一次UPDATE用CASE WHEN给不同id设不同值。update idupdateBatchCase update t_order set status foreach collectionlist itemitem opencase id closeend separator when #{item.id} then #{item.status} /foreach where id in foreach collectionlist itemitem open( close) separator, #{item.id} /foreach /update生成的SQL长这样update t_order set status case id when 1 then COMPLETED when 2 then PENDING when 3 then CANCELLED end where id in (1,2,3)这个方案只有一个网络往返没有分号不需要allowMultiQueries也不存在SQL注入风险。从性能角度讲它是三种方案里最稳的。对应的方法签名和前面一样Param(list) ListOrder list所以collectionlist天然匹配。3.2 动态if在CASE WHEN中为什么危险这是我踩过最深的一个坑。一开始我以为可以这样写只有某个字段不为空才把它放进CASE WHEN里省得更新多余字段。update idupdateBatchCase update t_order trim prefixset suffixOverrides, trim prefixstatus case id suffixend, foreach collectionlist itemitem if testitem.status ! null when #{item.id} then #{item.status} /if /foreach /trim trim prefixremark case id suffixend, foreach collectionlist itemitem if testitem.remark ! null when #{item.id} then #{item.remark} /if /foreach /trim /trim where id in foreach collectionlist itemitem open( close) separator, #{item.id} /foreach /update看逻辑好像没问题——status只更新非空的remark只更新非空的。但你要想清楚一件事case when的分支顺序是按#{item.id}的值排列的而when 1 then和when 2 then是靠位置对应的。如果第一条数据的status是null它就不会生成when 1 then而第二条数据的id是2它生成when 2 then。在MySQL看来case后面的表达式是id当id1时匹配不到分支就会返回null但整个CASE的数量少于id in列表的数量于是后面的字段就会错位。举个例子list里有id1,2,3三条id1的status为nullid2的status为“OK”。那么生成的SQL是update t_order set status case id when 2 then OK when 3 then CANCEL end, remark case id when 1 then 备注1 when 2 then 备注2 when 3 then 备注3 end where id in (1,2,3)id1的status本应不更新结果变成了null因为case id when没有匹配到分支时默认返回null。这是一个很典型的“你以为跳过更新实际置空”的坑。所以记住case when批量更新里除非你能保证每条数据的字段都不为null否则不要用if动态裁剪分支。要更新哪个字段就把这个字段的case完整写出来值可以传null分支不能缺。3.3 多列联动更新的安全写法如果你确实要同时更新多个字段安全做法是每个字段都写成完整的case并且foreach中不套if让每个id都生成一个when分支。即使某些字段值为null也要生成when #{item.id} then null保证分支一一对应。update idupdateBatchCase update t_order trim prefixset suffixOverrides, trim prefixstatus case id suffixend, foreach collectionlist itemitem when #{item.id} then #{item.status} /foreach /trim trim prefixremark case id suffixend, foreach collectionlist itemitem when #{item.id} then #{item.remark} /foreach /trim /trim where id in foreach collectionlist itemitem open( close) separator, #{item.id} /foreach /update这样生成的SQL里status和remark的case分支数量一定相同并且顺序都跟id列表一致。唯一需要你保证的是list里的元素顺序要稳定。如果你在前端传来的是一个Map遍历顺序不可控那就先把list按id排个序再拼接SQL。否则id顺序乱了case when的赋值就会串行。还有一个细节case when这种写法更新的字段不适合太多。如果一个实体有30个字段你写30个caseSQL会变得非常长数据库解析开销也随之上升。一般超过5个字段我就会考虑拆成两批或者换用ExecutorType.BATCH。4. 批量更新执行慢的排查链路参数数量、SQL长度与缓存干扰4.1 从一条报错日志开始线上批量更新出问题最常见的几类报错我都遇到过。这里记录一条完整的排查链路你可以照着走一遍。第一类SQLSyntaxErrorException。多半是SQL拼接错了。如果用的是foreach多语句方案优先检查连接串有没有allowMultiQueriestrue如果用的是case when方案检查是不是哪个字段的case分支数量不一致导致SQL语法上没问题但语义错位。第二类PacketTooBigException。这是MySQL驱动明确抛出的异常意思是发送给服务器的包超过了max_allowed_packet限制。默认情况下MySQL的max_allowed_packet是4MB或64MB取决于版本和云厂商配置。如果你的批量更新一次拼了几万条SQL文本轻松超过这个值。解决办法不是盲目改数据库配置而是把list分批每批500~1000条执行一次。第三类运行时没有报错但数据库监控显示更新耗时很长。这时要做的第一件事是把MyBatis的SQL日志打出来。在application.yml里加mybatis: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl然后看打印出来的SQL是不是超长。超长SQL带来的问题不只是网络传输慢MySQL解析SQL也是要花时间的。一条几百KB的SQL和一条几KB的SQL解析成本完全不在一个量级。拿到SQL之后用EXPLAIN看执行计划EXPLAIN update t_order set status case id when 1 then x end where id in (2,3,4)重点看type是不是range或refExtra里有没有Using temporary。如果where条件里的id列表过长优化器可能放弃索引扫描转而全表扫描。我在一个千万级表上实测过id in几百个id依然走主键range性能没问题但如果id列表里的值本身没索引比如更新用的不是主键而是某个普通业务编号那就很容易全表扫。4.2 分页插件和二级缓存在这里扮演什么角色很多人会把分页插件和MyBatis缓存跟批量更新联系在一起其实这两者更多是“干扰项”。先说PageHelper。PageHelper只拦截查询SQL对update没有任何影响。但如果你在批量更新的同一段代码里前面刚执行过一个不分页的查询而PageHelper的ThreadLocal里还有上一页的参数没有清理那么下一次查询可能被意外分页。这种问题在批量更新场景里不直接发生但我在排查“批量更新后查询结果不对”时遇到过。所以建议用PageHelper时在finally里显式清理PageHelper.clearPage()别把脏线程状态带到下一个操作里。再说二级缓存。MyBatis二级缓存默认是关闭的如果你没开这段可以跳过。如果开了要记住一个行为即使你用的是case when单条SQL批量更新只要命中了更新语句该Statement对应的二级缓存区域会被清空。更烦人的是如果批量更新和查询在同一个事务里更新后查询会绕过缓存直接走数据库这本来没什么但有些人会误以为“缓存导致查询变慢”。实际上这是正确行为更新后缓存必须失效否则会读到旧数据。最需要注意的是“二级缓存失效时间与事务提交不一致”的问题。MyBatis的二级缓存在事务提交时才写入但更新语句在事务未提交时也会触发缓存清空。如果你的Spring事务把多个更新和查询裹在一起更新后的查询可能会因为缓存清空而查库等到事务提交后再去读缓存。这个顺序不会造成脏数据但会影响你对“为什么批量更新后查询慢了”的感知。4.3 分批策略与事务边界批量更新最忌讳一把梭。无论用哪种方案我都会把list按固定大小分批。这里给出一个简单的工具方法public T void doBatchUpdate(ListT dataList, int batchSize, ConsumerListT action) { if (dataList null || dataList.isEmpty()) { return; } int size dataList.size(); for (int from 0; from size; from batchSize) { int to Math.min(from batchSize, size); ListT batch dataList.subList(from, to); action.accept(batch); } }调用时doBatchUpdate(orderList, 500, batch - orderMapper.updateBatchCase(batch));为什么选500这个数字我做了多组压测在MySQL 8.0、单条SQL不超过20KB的前提下500条一批在“性能”和“稳定性”之间最平衡。超过1000条时虽然总耗时可能差不多但遇到长事务的概率明显上升而且一旦失败回滚500条的代价比2000条小得多。事务边界上我的建议是“一个批次一个事务”。Spring里可以这样处理注入TransactionTemplate在updateBatch内部给每个批次定义隔离级别和传播行为。如果整体业务要求这批数据要么全部成功要么全部失败那就整体一个事务但你要接受长事务带来的锁持有时间。没有绝对正确只有场景适配。5. MySQL和Oracle的批量更新差异从实测数据看方案抉择5.1 MySQL上的性能对比我在自己的测试环境做过一组对比。数据量1000行单表字段不算多id、status、remarkMySQL 8.0.33JDBC驱动8.0.33网络同一内网单条update的RT大概在1~3ms。结果如下方案耗时说明for循环单条update2800ms左右每次update一次网络往返耗时线性增长foreach多条UPDATEallowMultiQueries680ms左右一次发送多条省去了大部分网络往返case when单条UPDATE420ms左右只有一次网络往返SQL解析耗时适中ExecutorType.BATCH310ms左右走JDBC Batch配合rewriteBatchedStatementstrue数据仅供参考不同机器、不同数据量差距会很大但趋势是明确的for循环最慢Batch最快。有个有意思的现象foreach多条UPDATE在1000条时只花了680ms但一旦数据量到5000条耗时直接飙到3秒以上而且传给MySQL的包非常大。case when在5000条时也就1秒出头这是因为MySQL对大SQL的解析是有优化空间的而多语句则要逐条执行逐条回包开销更大。所以我的建议是如果你只能选XML写法优先case when如果追求极致性能上ExecutorType.BATCH。5.2 Oracle要用PL/SQL块的约束如果你的项目用的是Oracle很多MySQL经验要推翻。Oracle默认不允许JDBC执行多条分号分隔的SQL就算你拼了也不认。要批量更新常见方式有两种。第一种用SQL Developer或PL/SQL块包起来update idupdateBatchOracle BEGIN foreach collectionlist itemitem separator; update t_order set status #{item.status} where id #{item.id} /foreach ;END; /update这里注意separator;仍然用但最终SQL会被BEGIN ... END;包裹。这种写法的问题很明显如果你拼了1000条updatePL/SQL块里的代码量会非常大数据库编译PL/SQL的成本也不低。实测下来500条以内还能接受1000条以上Parser就会明显变慢。第二种是Oracle更推荐的MERGE INTOMERGE INTO t_order t USING ( SELECT 1 AS id, COMPLETED AS status FROM dual UNION ALL SELECT 2, PENDING FROM dual UNION ALL SELECT 3, CANCELLED FROM dual ) s ON (t.id s.id) WHEN MATCHED THEN UPDATE SET t.status s.status对应MyBatis写法可以用foreach拼UNION ALL但要注意Oracle的dual表查询如果list里有重复id会导致笛卡尔积尽量保证id唯一。MERGE INTO的好处是单条SQL、无分号、没有多语句限制性能在数据量较大时也很稳。5.3 结合场景选型到这里方案选型就很清晰了场景首选方案理由MySQL数据量2000字段少case when单条简单、稳定、无需额外参数MySQL数据量2000字段多ExecutorType.BATCH性能最优避免超长SQLMySQL单条更新逻辑差异大foreach多条每条update可独立拼接不同字段Oracle数据量500PL/SQL块写法直观能直接用#{}Oracle数据量500MERGE INTO解析成本低性能稳定这里再提醒一句如果用ExecutorType.BATCHMyBatis返回的更新行数可能会是负数因为JDBC Batch执行后返回的int[]里有的驱动实现会返回SUCCESS_NO_INFO。如果你需要精确的受影响行数做业务判断Batch方案就不适合。6. 写在最后的实用建议什么时候不要用批量更新6.1 需要对每条结果做业务判断时批量更新SQL虽然快但它牺牲了单条结果的可观测性。如果你在更新后需要知道“这一行到底有没有被更新”或者要根据每条update的影响行数决定是否继续后续逻辑那就不适合用批量更新。case when单条SQL只能得到总行数foreach多语句虽然能拿到每条结果但接口设计上很别扭。我见过一个做对账系统的同事拿case when批量更新了状态然后想判断“这条单子是不是从未知状态变成了成功状态”结果发现没法区分是没匹配到行还是值没变化。所以对账类、审计类、需要精确感知数据变化的场景老老实实一条条来别为了性能牺牲正确性。6.2 批量更新的条数控制与兜底每次批量更新前最好对list.size()做一次校验。如果业务上允许一次性传5000条我绝不会在执行时一次性全干而是按500一批拆掉。这个拆批不仅是避开了SQL长度限制更重要的是隔离失败影响范围。如果一个批次失败了你只需要回滚这一批其他批次已经提交的部分可以保留也可以根据你的重试策略继续跑。兜底方案也简单给每个批次套try-catch记录失败批次的下标和数据主键方便事后补偿。如果你是做支付、订单这类高一致性业务建议批次之间不要自动提交而是用外层事务控制让失败批次触发整体回滚或告警。6.3 我常用的最终落地方案就我自己而言现在写MyBatis批量更新SQL默认组合是MySQL用case when单条超过2000条或者字段超过5个时改成ExecutorType.BATCHOracle用MERGE INTO除非数据量实在太小才用PL/SQL块。每次接新需求时先确认数据量上限和字段是否可能为null再决定SQL方案。最后再分享一个小技巧批量更新SQL写完一定先在本地把log-impl打开看一眼打印出来的完整SQL长什么样。这一步能帮你发现很多“程序以为对但数据库不认”的问题。真的这个习惯救过我很多次。内容到这里就结束了这些坑和方案都是我实际踩过、压过、线上验证过的希望能帮你少走一段弯路。
MyBatis批量更新SQL实战：三大方案对比与避坑指南
去年线上有个状态变更接口一次性要把3000多条订单的状态改成“已完成”。我当时图省事直接在Service里写了个for循环循环调用Mapper的单条update。结果上线没几分钟接口就超时了数据库会话被打满运维直接来找我喝茶。那次之后我把MyBatis批量更新SQL的几种玩法、底层执行流程和常见坑都翻了个底朝天今天把这些经验整理成一篇完整的实战笔记。这篇内容适合谁看已经会写单条update、但一遇到“要更新几千行数据”这种场景就不知道选哪种方案的Java开发也想搞清楚foreach拼接和case when到底哪个更快、哪个更稳的同学都可以直接照着往下看。我会从最坑的写法讲起再给能落地的方案最后附上我实测的性能数据和排查思路。1. 先说结论三种主流的批量更新SQL玩法对比1.1 为什么for循环update是最容易踩的坑很多人在没接触过批量更新之前第一反应都是“那就循环调用update方法”。这种写法在数据量小的时候没有任何问题10条、20条数据数据库毫秒级就返回了谁都不会觉得慢。可一旦数据量到了几百上千问题就来了你每执行一次updateMyBatis都要经过一次完整的JDBC流程——创建PreparedStatement、绑定参数、发送到数据库、等数据库返回结果、再关闭Statement。这里有个关键点MyBatis默认的ExecutorType是SIMPLE。在SIMPLE模式下每一次update调用都会重新创建StatementHandler也就是完整走一遍prepare、execute、close。哪怕你把数据库连接池配得很好连接可以复用但网络往返是省不掉的。假设一次单条update从应用服务器到数据库服务器的网络RT是10ms1000条数据就是10秒这还没算上事务提交和数据库侧的锁等待。更隐蔽的坑是事务。很多Spring项目里直接在一个事务方法里做for循环更新看起来是一条一条执行实际上这些update都在同一个事务里。第一条更新拿到行锁后续的更新可能被同一张表上的其他写操作阻塞。也就是说你不仅要等自己的1000次网络往返还要等别人释放锁。所以线上那种“一个批量接口拖垮整个库”的事故多半就是这个原因。从MyBatis源码的角度看BaseExecutor的update方法会走到doUpdateSimpleExecutor的doUpdate就是每次new一个PreparedStatementHandler然后执行。这从机制上就决定了它不适合大批量操作。我后面会讲到的ExecutorType.BATCH走的则是另一个分支它在BatchExecutor里攒一批语句最后一次性executeBatch这才是JDBC层面真正意义上的批处理。1.2 批量更新的三种正确玩法先把结论摆出来后面再逐个拆细节。我常用的批量更新SQL方案就三种方案SQL形态核心优点核心缺点适用场景foreach拼接多条UPDATE一条Statement里包含多条分号分隔的UPDATE写法直观每条update独立易读需要开启allowMultiQueriesSQL文本过长有隐患中小批量单次几百条case when拼单条UPDATE一条UPDATE用CASE WHEN批量赋值只用一次网络往返性能稳定动态条件容易导致索引错位写起来费劲大批量字段固定的更新ExecutorType.BATCHJDBC Batch批量发送底层走预编译批量执行性能最好受驱动程序影响大返回结果难直接使用超大批量不关心单条结果这三种方案我都用过。这里先强调一下for循环不叫批量更新SQL它只是“循环执行单条SQL”不管是从网络开销还是数据库压力上看都不适合批量场景。下面的内容我按难度从低到高把每种方案的细节、坑、以及为什么这样设计讲一遍。2. foreach拼接多条UPDATEMyBatis XML里的细节与陷阱2.1 最直观的写法与allowMultiQueries先看最直观的写法。在Mapper接口里定义一个方法int updateBatch(Param(list) ListOrder orderList);对应的XMLupdate idupdateBatch foreach collectionlist itemitem separator; update t_order set status #{item.status} where id #{item.id} /foreach /update这段XML生成出来的SQL是update t_order set status ? where id ?;update t_order set status ? where id ?;...注意这里用的是分号分隔。问题来了MySQL的JDBC驱动默认不允许一条Statement里出现多条分号分隔的SQL除非你在JDBC连接串上显式加上allowMultiQueriestrue。jdbc:mysql://localhost:3306/test?allowMultiQueriestrue这个参数我第一次用的时候漏掉了结果线上直接报SQLSyntaxErrorException当时还以为是XML写错了。排查了半天才发现是连接参数的问题。另外allowMultiQueriestrue是有安全隐患的。MyBatis的#{}参数化虽然能防止SQL注入但如果你在XML里不小心写了${}拼接又开了多语句支持那就等于给SQL注入开了大门。所以我的原则是能用#{}绝不用${}同时这个参数只给确实需要多语句更新的数据源开启不要全项目所有数据源都默认打开。2.2 参数传递的Param约定用foreach的时候很多人会忽略Param(list)这个注解。如果方法签名是int updateBatch(ListOrder orderList)而不加ParamMyBatis会怎么处理它会把这个List包成一个key为list、collection的Map但不同MyBatis版本对这个默认key的处理细节会有差异。为了保险建议显式写上Param。还有一点foreach除了item还有个容易被忽视的index属性。在List场景下index就是循环下标在Map场景下index是key。很多时候你不一定需要index但如果你的SQL里要根据循环次数拼接某些东西比如计算批次序号那就很有用。foreach collectionlist itemitem indexindex separator; update t_order set sort_no #{index} where id #{item.id} /foreach这里#{index}直接取的是List里元素的下标。这种写法在需要按位置更新的时候很顺手但要注意index是从0开始的。2.3 连接串参数rewriteBatchedStatements到底影响什么接下来是另一个高频困惑。网上很多文章说“批量更新慢加上rewriteBatchedStatementstrue就好了”。但很多人加了之后发现没效果为什么因为他用的是foreach拼多条SQL而不是JDBC Batch。rewriteBatchedStatements这个参数只对JDBC的addBatch()/executeBatch()生效。它的原理是当驱动发现你在用批量执行会尝试把多条同构SQL重写成一条多VALUES的插入或一条带有多个分号的多语句从而减少网络往返。注意MySQL驱动8.0版本里这个参数默认值是false如果你的批量插入性能上不去很大的原因就是它。但如果你用的是ExecutorType.BATCH方式这个参数才会真正发挥威力。如果你只是用foreach在XML里拼多条SQL那么rewriteBatchedStatements跟你没关系你真正需要的是allowMultiQueries。我见过有个项目把这两个参数搞混了线上批量更新一直慢加了rewriteBatchedStatements没反应后来还是一段一段拆SQL才解决。所以先把概念理清楚一个管多语句解析一个管JDBC Batch重写别指望一个参数解决所有问题。3. case when一条SQL批量更新多行索引错位与NULL值的血泪教训3.1 XML模板与代码构造如果你要更新的数据字段相对固定我更推荐用case when拼单条SQL。核心思想很简单一次UPDATE用CASE WHEN给不同id设不同值。update idupdateBatchCase update t_order set status foreach collectionlist itemitem opencase id closeend separator when #{item.id} then #{item.status} /foreach where id in foreach collectionlist itemitem open( close) separator, #{item.id} /foreach /update生成的SQL长这样update t_order set status case id when 1 then COMPLETED when 2 then PENDING when 3 then CANCELLED end where id in (1,2,3)这个方案只有一个网络往返没有分号不需要allowMultiQueries也不存在SQL注入风险。从性能角度讲它是三种方案里最稳的。对应的方法签名和前面一样Param(list) ListOrder list所以collectionlist天然匹配。3.2 动态if在CASE WHEN中为什么危险这是我踩过最深的一个坑。一开始我以为可以这样写只有某个字段不为空才把它放进CASE WHEN里省得更新多余字段。update idupdateBatchCase update t_order trim prefixset suffixOverrides, trim prefixstatus case id suffixend, foreach collectionlist itemitem if testitem.status ! null when #{item.id} then #{item.status} /if /foreach /trim trim prefixremark case id suffixend, foreach collectionlist itemitem if testitem.remark ! null when #{item.id} then #{item.remark} /if /foreach /trim /trim where id in foreach collectionlist itemitem open( close) separator, #{item.id} /foreach /update看逻辑好像没问题——status只更新非空的remark只更新非空的。但你要想清楚一件事case when的分支顺序是按#{item.id}的值排列的而when 1 then和when 2 then是靠位置对应的。如果第一条数据的status是null它就不会生成when 1 then而第二条数据的id是2它生成when 2 then。在MySQL看来case后面的表达式是id当id1时匹配不到分支就会返回null但整个CASE的数量少于id in列表的数量于是后面的字段就会错位。举个例子list里有id1,2,3三条id1的status为nullid2的status为“OK”。那么生成的SQL是update t_order set status case id when 2 then OK when 3 then CANCEL end, remark case id when 1 then 备注1 when 2 then 备注2 when 3 then 备注3 end where id in (1,2,3)id1的status本应不更新结果变成了null因为case id when没有匹配到分支时默认返回null。这是一个很典型的“你以为跳过更新实际置空”的坑。所以记住case when批量更新里除非你能保证每条数据的字段都不为null否则不要用if动态裁剪分支。要更新哪个字段就把这个字段的case完整写出来值可以传null分支不能缺。3.3 多列联动更新的安全写法如果你确实要同时更新多个字段安全做法是每个字段都写成完整的case并且foreach中不套if让每个id都生成一个when分支。即使某些字段值为null也要生成when #{item.id} then null保证分支一一对应。update idupdateBatchCase update t_order trim prefixset suffixOverrides, trim prefixstatus case id suffixend, foreach collectionlist itemitem when #{item.id} then #{item.status} /foreach /trim trim prefixremark case id suffixend, foreach collectionlist itemitem when #{item.id} then #{item.remark} /foreach /trim /trim where id in foreach collectionlist itemitem open( close) separator, #{item.id} /foreach /update这样生成的SQL里status和remark的case分支数量一定相同并且顺序都跟id列表一致。唯一需要你保证的是list里的元素顺序要稳定。如果你在前端传来的是一个Map遍历顺序不可控那就先把list按id排个序再拼接SQL。否则id顺序乱了case when的赋值就会串行。还有一个细节case when这种写法更新的字段不适合太多。如果一个实体有30个字段你写30个caseSQL会变得非常长数据库解析开销也随之上升。一般超过5个字段我就会考虑拆成两批或者换用ExecutorType.BATCH。4. 批量更新执行慢的排查链路参数数量、SQL长度与缓存干扰4.1 从一条报错日志开始线上批量更新出问题最常见的几类报错我都遇到过。这里记录一条完整的排查链路你可以照着走一遍。第一类SQLSyntaxErrorException。多半是SQL拼接错了。如果用的是foreach多语句方案优先检查连接串有没有allowMultiQueriestrue如果用的是case when方案检查是不是哪个字段的case分支数量不一致导致SQL语法上没问题但语义错位。第二类PacketTooBigException。这是MySQL驱动明确抛出的异常意思是发送给服务器的包超过了max_allowed_packet限制。默认情况下MySQL的max_allowed_packet是4MB或64MB取决于版本和云厂商配置。如果你的批量更新一次拼了几万条SQL文本轻松超过这个值。解决办法不是盲目改数据库配置而是把list分批每批500~1000条执行一次。第三类运行时没有报错但数据库监控显示更新耗时很长。这时要做的第一件事是把MyBatis的SQL日志打出来。在application.yml里加mybatis: configuration: log-impl: org.apache.ibatis.logging.stdout.StdOutImpl然后看打印出来的SQL是不是超长。超长SQL带来的问题不只是网络传输慢MySQL解析SQL也是要花时间的。一条几百KB的SQL和一条几KB的SQL解析成本完全不在一个量级。拿到SQL之后用EXPLAIN看执行计划EXPLAIN update t_order set status case id when 1 then x end where id in (2,3,4)重点看type是不是range或refExtra里有没有Using temporary。如果where条件里的id列表过长优化器可能放弃索引扫描转而全表扫描。我在一个千万级表上实测过id in几百个id依然走主键range性能没问题但如果id列表里的值本身没索引比如更新用的不是主键而是某个普通业务编号那就很容易全表扫。4.2 分页插件和二级缓存在这里扮演什么角色很多人会把分页插件和MyBatis缓存跟批量更新联系在一起其实这两者更多是“干扰项”。先说PageHelper。PageHelper只拦截查询SQL对update没有任何影响。但如果你在批量更新的同一段代码里前面刚执行过一个不分页的查询而PageHelper的ThreadLocal里还有上一页的参数没有清理那么下一次查询可能被意外分页。这种问题在批量更新场景里不直接发生但我在排查“批量更新后查询结果不对”时遇到过。所以建议用PageHelper时在finally里显式清理PageHelper.clearPage()别把脏线程状态带到下一个操作里。再说二级缓存。MyBatis二级缓存默认是关闭的如果你没开这段可以跳过。如果开了要记住一个行为即使你用的是case when单条SQL批量更新只要命中了更新语句该Statement对应的二级缓存区域会被清空。更烦人的是如果批量更新和查询在同一个事务里更新后查询会绕过缓存直接走数据库这本来没什么但有些人会误以为“缓存导致查询变慢”。实际上这是正确行为更新后缓存必须失效否则会读到旧数据。最需要注意的是“二级缓存失效时间与事务提交不一致”的问题。MyBatis的二级缓存在事务提交时才写入但更新语句在事务未提交时也会触发缓存清空。如果你的Spring事务把多个更新和查询裹在一起更新后的查询可能会因为缓存清空而查库等到事务提交后再去读缓存。这个顺序不会造成脏数据但会影响你对“为什么批量更新后查询慢了”的感知。4.3 分批策略与事务边界批量更新最忌讳一把梭。无论用哪种方案我都会把list按固定大小分批。这里给出一个简单的工具方法public T void doBatchUpdate(ListT dataList, int batchSize, ConsumerListT action) { if (dataList null || dataList.isEmpty()) { return; } int size dataList.size(); for (int from 0; from size; from batchSize) { int to Math.min(from batchSize, size); ListT batch dataList.subList(from, to); action.accept(batch); } }调用时doBatchUpdate(orderList, 500, batch - orderMapper.updateBatchCase(batch));为什么选500这个数字我做了多组压测在MySQL 8.0、单条SQL不超过20KB的前提下500条一批在“性能”和“稳定性”之间最平衡。超过1000条时虽然总耗时可能差不多但遇到长事务的概率明显上升而且一旦失败回滚500条的代价比2000条小得多。事务边界上我的建议是“一个批次一个事务”。Spring里可以这样处理注入TransactionTemplate在updateBatch内部给每个批次定义隔离级别和传播行为。如果整体业务要求这批数据要么全部成功要么全部失败那就整体一个事务但你要接受长事务带来的锁持有时间。没有绝对正确只有场景适配。5. MySQL和Oracle的批量更新差异从实测数据看方案抉择5.1 MySQL上的性能对比我在自己的测试环境做过一组对比。数据量1000行单表字段不算多id、status、remarkMySQL 8.0.33JDBC驱动8.0.33网络同一内网单条update的RT大概在1~3ms。结果如下方案耗时说明for循环单条update2800ms左右每次update一次网络往返耗时线性增长foreach多条UPDATEallowMultiQueries680ms左右一次发送多条省去了大部分网络往返case when单条UPDATE420ms左右只有一次网络往返SQL解析耗时适中ExecutorType.BATCH310ms左右走JDBC Batch配合rewriteBatchedStatementstrue数据仅供参考不同机器、不同数据量差距会很大但趋势是明确的for循环最慢Batch最快。有个有意思的现象foreach多条UPDATE在1000条时只花了680ms但一旦数据量到5000条耗时直接飙到3秒以上而且传给MySQL的包非常大。case when在5000条时也就1秒出头这是因为MySQL对大SQL的解析是有优化空间的而多语句则要逐条执行逐条回包开销更大。所以我的建议是如果你只能选XML写法优先case when如果追求极致性能上ExecutorType.BATCH。5.2 Oracle要用PL/SQL块的约束如果你的项目用的是Oracle很多MySQL经验要推翻。Oracle默认不允许JDBC执行多条分号分隔的SQL就算你拼了也不认。要批量更新常见方式有两种。第一种用SQL Developer或PL/SQL块包起来update idupdateBatchOracle BEGIN foreach collectionlist itemitem separator; update t_order set status #{item.status} where id #{item.id} /foreach ;END; /update这里注意separator;仍然用但最终SQL会被BEGIN ... END;包裹。这种写法的问题很明显如果你拼了1000条updatePL/SQL块里的代码量会非常大数据库编译PL/SQL的成本也不低。实测下来500条以内还能接受1000条以上Parser就会明显变慢。第二种是Oracle更推荐的MERGE INTOMERGE INTO t_order t USING ( SELECT 1 AS id, COMPLETED AS status FROM dual UNION ALL SELECT 2, PENDING FROM dual UNION ALL SELECT 3, CANCELLED FROM dual ) s ON (t.id s.id) WHEN MATCHED THEN UPDATE SET t.status s.status对应MyBatis写法可以用foreach拼UNION ALL但要注意Oracle的dual表查询如果list里有重复id会导致笛卡尔积尽量保证id唯一。MERGE INTO的好处是单条SQL、无分号、没有多语句限制性能在数据量较大时也很稳。5.3 结合场景选型到这里方案选型就很清晰了场景首选方案理由MySQL数据量2000字段少case when单条简单、稳定、无需额外参数MySQL数据量2000字段多ExecutorType.BATCH性能最优避免超长SQLMySQL单条更新逻辑差异大foreach多条每条update可独立拼接不同字段Oracle数据量500PL/SQL块写法直观能直接用#{}Oracle数据量500MERGE INTO解析成本低性能稳定这里再提醒一句如果用ExecutorType.BATCHMyBatis返回的更新行数可能会是负数因为JDBC Batch执行后返回的int[]里有的驱动实现会返回SUCCESS_NO_INFO。如果你需要精确的受影响行数做业务判断Batch方案就不适合。6. 写在最后的实用建议什么时候不要用批量更新6.1 需要对每条结果做业务判断时批量更新SQL虽然快但它牺牲了单条结果的可观测性。如果你在更新后需要知道“这一行到底有没有被更新”或者要根据每条update的影响行数决定是否继续后续逻辑那就不适合用批量更新。case when单条SQL只能得到总行数foreach多语句虽然能拿到每条结果但接口设计上很别扭。我见过一个做对账系统的同事拿case when批量更新了状态然后想判断“这条单子是不是从未知状态变成了成功状态”结果发现没法区分是没匹配到行还是值没变化。所以对账类、审计类、需要精确感知数据变化的场景老老实实一条条来别为了性能牺牲正确性。6.2 批量更新的条数控制与兜底每次批量更新前最好对list.size()做一次校验。如果业务上允许一次性传5000条我绝不会在执行时一次性全干而是按500一批拆掉。这个拆批不仅是避开了SQL长度限制更重要的是隔离失败影响范围。如果一个批次失败了你只需要回滚这一批其他批次已经提交的部分可以保留也可以根据你的重试策略继续跑。兜底方案也简单给每个批次套try-catch记录失败批次的下标和数据主键方便事后补偿。如果你是做支付、订单这类高一致性业务建议批次之间不要自动提交而是用外层事务控制让失败批次触发整体回滚或告警。6.3 我常用的最终落地方案就我自己而言现在写MyBatis批量更新SQL默认组合是MySQL用case when单条超过2000条或者字段超过5个时改成ExecutorType.BATCHOracle用MERGE INTO除非数据量实在太小才用PL/SQL块。每次接新需求时先确认数据量上限和字段是否可能为null再决定SQL方案。最后再分享一个小技巧批量更新SQL写完一定先在本地把log-impl打开看一眼打印出来的完整SQL长什么样。这一步能帮你发现很多“程序以为对但数据库不认”的问题。真的这个习惯救过我很多次。内容到这里就结束了这些坑和方案都是我实际踩过、压过、线上验证过的希望能帮你少走一段弯路。
字段精度问题全解析)
