★ FEATURED ARTICLE 前阵子在做一个数据迁移需要把几十万条历史数据导到新表里。一开始图省事直接写了个for循环每跑一条就调一次Mapper方法结果跑了十几分钟还没完旁边同事随口问了一句“你这是在‘批量插入’”我才意识到MyBatis里贴着“批量”标签的写法有完全不同的底层执行路径性能差距能到一个数量级。这篇文章就把MyBatis的批量插入、批量修改、批量删除完整梳理一遍从写法到原理从参数调到踩坑全是我实际用过的方案和数据。1. 先搞清一个关键区别拼大SQL不等于JDBC批量提交很多刚接触MyBatis的人以为把多条INSERT拼成一条SQL就是“批量”其实这只是把N次单条提交变成了1次大SQL提交和真正JDBC里的executeBatch是两条完全不同的路线。1.1 单条循环为什么慢耗时大头在网络往返和事务开销单条循环插入时每一次Mapper方法调用都会走一遍解析SQL、发送到数据库、数据库执行、返回结果。对于10000条数据就是10000次完整的请求-响应往返网络延迟哪怕只有0.5ms光往返就5秒起步这还没算SQL解析和事务提交的时间。更隐蔽的问题是事务。如果外层没有用Transactional包住每个insert默认走autocommit每次调用都会自动提交一次。提交不是免费的它涉及写日志、释放锁、同步数据文件等一系列操作次数一多整个数据库的负担会明显上来。1.2 JDBC batch到底做了什么JDBC的PreparedStatement提供了addBatch和executeBatch两个方法。addBatch是把SQL模板对应的参数分组放进内存executeBatch一次性把攒好的参数发送给数据库。数据库拿到后有优化空间比如把多条带参数的INSERT合并处理减少解析次数甚至在某些驱动下直接改写成一条多VALUES的INSERT。所以真正意义上的批量指的是这种方式而不是在XML里手动拼接一个超长SQL。MyBatis里对应的能力是ExecutorType.BATCH。1.3 MyBatis的两条批量路线MyBatis执行SQL时有三种ExecutorSIMPLE、REUSE、BATCH。默认是SIMPLE每条语句都要创建PreparedStatementREUSE则缓存PreparedStatementBATCH模式下会把语句加入批处理由驱动统一发送。但是注意一点哪怕你用ExecutorType.BATCH在Mapper XML里写的还是普通的单条INSERTMyBatis只是把它加入了批处理批次底层仍然是PreparedStatement.addBatch。而很多人用的 拼VALUES其实还在SIMPLE Executor里最终执行的是一条巨型INSERT语句数据库把它当成一次普通SQL处理。这两条路线都能提升性能但适用的数据量、能接受的SQL长度、以及调优参数完全不一样。下面分场景说。2. 批量插入三种实际写法和性能差异批量插入是我日常写的最多的操作见过不少人在这上面踩坑。先看三种写法再给对比数据。2.1 写法一foreach拼接VALUES适合几千条以内的常用方案Mapper接口不用特殊处理XML里直接写insert idbatchInsert parameterTypelist INSERT INTO t_user (name, age, dept_id) VALUES foreach collectionlist itemitem separator, open close (#{item.name}, #{item.age}, #{item.deptId}) /foreach /insert接口定义int batchInsert(ListUser userList);这种写法很直观把一条INSERT变成INSERT INTO t_user (...) VALUES (...),(...),(...)。数据库收到后解析一次SQL模板执行N行数据插入性能远好于循环单条。它的最大限制是SQL总长度。MySQL服务端有max_allowed_packet限制默认常见配置是4MB或16MB如果你一次塞进去的数据超过这个限制数据库会直接报错或者断开连接。所以这个方案只适合单批数据可控的场景一般控制在几百到两三千条以内相对安全。2.2 写法二ExecutorType.BATCH适合大批量数据如果你想插入几万甚至几十万条就别再追求“一条大SQL”了改用真正JDBC批处理更稳。在Spring项目里可以单独配置一个使用BATCH模式执行器的SqlSessionTemplateBean public SqlSessionTemplate batchSqlSessionTemplate(SqlSessionFactory factory) { return new SqlSessionTemplate(factory, ExecutorType.BATCH); }然后注入这个Bean做插入private final SqlSessionTemplate batchSqlSessionTemplate; public void batchInsert(ListUser users) { UserMapper mapper batchSqlSessionTemplate.getMapper(UserMapper.class); for (User user : users) { mapper.insert(user); // 这里的insert就是普通单条INSERT } batchSqlSessionTemplate.flushStatements(); }注意几个细节方法必须跑在事务里。BATCH模式下MyBatis会把语句积压起来只有commit时才会真正把批处理发给数据库如果没开事务第一条语句就可能被自动提交后面的批量效果直接没了。大批量数据要分批flush不要万条数据攒到最后一次flush。这种写法和foreach拼接看起来完全不同但实际上Mapper里的insert语句就是单条INSERT的写法不需要特殊XML。2.3 三种方式的实际耗时对比我之前在一个项目里做过简单压测普通表的INSERT数据量分别取500条、5000条、20000条结果大概如下数据量单条循环foreach拼接VALUESBATCH模式未开rewriteBatchedStatementsBATCH模式开启rewriteBatchedStatements500条约800ms约90ms约280ms约55ms5000条约8s约700ms约2.4s约300ms20000条约35s约3s容易超SQL长度限制约9s约1.2s这三个数字很有代表性foreach拼接在小批量数据上表现惊艳因为只发一次网络包BATCH模式在小批量时反而有额外的批处理组装开销但数据量一旦上来BATCH模式配合驱动参数后性能最稳。具体怎么选我的标准是少于1000条用foreach拼接1000到10000条拆成批每批用foreach超过1万条建议走BATCH模式每批500到1000条提交一次。当然这个阈值不是定死的得看单行数据宽度和数据库配置。3. 批量修改CASE WHEN一次更新多行的写法和取舍批量修改比批量插入难优化。INSERT可以用多VALUESUPDATE没有“多SET”这种等价写法所以最常见的做法就是逐条UPDATE但逐条更新的性能瓶颈和单条插入一样明显。3.1 用CASE WHEN在一条UPDATE里改多行MyBatis的困境是要改100条数据逐条UPDATE就是100次执行性能差但UPDATE没有多VALUES只能采用CASE WHEN IN的方式。单字段场景这么写update idbatchUpdateNameById UPDATE t_user SET name foreach collectionlist itemitem openCASE id closeEND WHEN #{item.id} THEN #{item.name} /foreach WHERE id IN foreach collectionlist itemitem open( separator, close) #{item.id} /foreach /update生成的SQL等价于UPDATE t_user SET name CASE id WHEN 1 THEN 张三 WHEN 2 THEN 李四 WHEN 3 THEN 王五 END WHERE id IN (1,2,3)多字段场景也类似每个字段单独一个CASEupdate idbatchUpdateUser UPDATE t_user SET name CASE id foreach collectionlist itemitem WHEN #{item.id} THEN #{item.name} /foreach END, age CASE id foreach collectionlist itemitem WHEN #{item.id} THEN #{item.age} /foreach END WHERE id IN foreach collectionlist itemitem open( separator, close) #{item.id} /foreach /update这里有个特别容易踩的坑如果CASE里没有覆盖列表里的所有id或者某些行的字段不需要更新其他行的字段就会被置成NULL。所以要么保证每条记录的所有字段都有值要么在Java代码里先处理好空值逻辑不要让空值参与CASE WHEN。3.2 为什么大批量UPDATE仍然不建议硬拼CASE WHEN方案把N次UPDATE合成1次确实能大幅减少网络往返但它本质上仍是一条超大UPDATE语句。数据库执行时会对WHERE里的所有目标行加锁如果目标行数持续很大锁范围扩大容易阻塞其他事务主从同步的延迟也会变高。我在一个真实项目里遇到过一次CASE WHEN更新了2万行主从延迟瞬间飙升到十几秒。业务上其实只改了其中几十个人的数据但因为列表是一次性全量传来的导致所有行都被更新了一遍。这种情况的根因不是MyBatis而是业务侧没有做“只传变更行”的裁剪。对于几百到几千行的小批量更新CASE WHEN是能明显提升效率的。超过这个量级我更建议走临时表方案或者干脆拆成每小时同步任务别硬塞到一条UPDATE里。3.3 MySQL里更猛的做法临时表JOIN UPDATE如果单批更新量到了上万行常规SQL都不太推荐了。MySQL里有个性能更好的替代方案先把要更新的数据批量插入一张临时表然后通过JOIN一次UPDATE目标表。CREATE TEMPORARY TABLE tmp_user_update ( id BIGINT PRIMARY KEY, name VARCHAR(50), age INT ); INSERT INTO tmp_user_update VALUES (1, 张三, 20), (2, 李四, 22), ...; UPDATE t_user u JOIN tmp_user_update tmp ON u.id tmp.id SET u.name tmp.name, u.age tmp.age;MyBatis里可以用两条Mapper方法一个人往临时表批量插入一个人执行JOIN UPDATE。这样做的优势是避免超大CASE WHEN批量插入本身可以走上一节说的foreach或BATCH执行器数据写入临时表的效率远高于一条巨型UPDATE。这种做法的问题是临时表会话隔离必须确保两条SQL在同一个数据库连接里执行一旦走连接池随手换连接临时表可能就“不见了”。同时多一个建表和写临时表的环节代码复杂度和维护成本会高一些所以我只在数据量真的很大的场景才换这套方案。4. 批量删除稳写法和两个高频坑批量删除看起来最简单也最容易翻车。最常见的就是IN列表那套写法但两个坑一个接一个。4.1 基于主键IN集合删除的标准写法delete idbatchDeleteByIds DELETE FROM t_user WHERE id IN foreach collectionids itemid open( separator, close) #{id} /foreach /delete接口长这样int batchDeleteByIds(ListLong ids);这个写法本身没问题但你必须明白它生成的是一条包含N个参数的DELETE语句效果是把N行一次锁住并删除。对几千条以内很合适太多就有下面要说的坑。4.2 第一个坑集合为空直接生成语法错误的SQL如果ids是空集合foreach生成出来的SQL会变成DELETE FROM t_user WHERE id IN ()MySQL直接报语法错误。很多人会考虑在Java层调用前判断集合是否为空可一旦代码里有别的调用方忘记判断线上就炸了。保险的办法是把判断写进XMLdelete idbatchDeleteByIds if testids ! null and ids.size() 0 DELETE FROM t_user WHERE id IN foreach collectionids itemid open( separator, close) #{id} /foreach /if /delete注意这里不能只判非空必须判断ids.size() 0。只判断ids ! null时空list一样会导致IN ()。4.3 第二个坑IN列表太长和SQL超限IN列表里的参数个数是没有硬性语法限制的但实际上受两件事限制一条SQL的总长度以及数据库对执行计划的处理开销。当你批量删除一万条数据时SQL长度已经很大而且MySQL要先解析一万个id生成一个非常大的IN集合效率会肉眼可见地下降。更麻烦的是DELETE操作是逐行加锁的一次性删一万行会长时间占用大量锁直接影响其他并发业务。我实际生产环境里的做法是在Java层把ids拆成每批500到1000的小批次循环删除每批调用一次batchDeleteByIds中间不需要额外的commit控制层次清晰锁的持有时间也短得多。4.4 联合条件删除和逻辑删除如果删除场景不只是按主键而是按多个字段组成的业务键比如删除某个用户在某部门下的所有角色关联可以这样写delete idbatchDeleteByUserAndRole DELETE FROM t_user_role WHERE foreach collectionlist itemitem separator OR (user_id #{item.userId} AND role_id #{item.roleId}) /foreach /delete生成的是WHERE (user_id 1 AND role_id 1) OR (user_id 2 AND role_id 2)逻辑没问题但OR条件写大了之后同样面临SQL长度和性能问题尽量控制批次大小。另外插一句能逻辑删除的场合就别走物理删除。加一个deleted字段批量修改deleted1既避免大范围锁行又保留数据可追溯。我在项目里默认都是软删除物理删除只留给真正需要清数据的运维场景。5. 决定性能的隐藏参数两个能让你批处理脱胎换骨的开关很多人把ExecutorType.BATCH打开后跑了一遍测试发现性能并没有想象中提升然后就开始怀疑MyBatis是不是没用对。其实问题多半落在JDBC驱动参数上。5.1 rewriteBatchedStatementstrueMySQL的JDBC驱动默认处理executeBatch时会一条一条地把SQL发给数据库。也就是说你明明在BATCH模式下攒了一批数据驱动层面却还是一条一次发送自然没什么明显的性能提升。只要在连接URL上加一个参数jdbc:mysql://localhost:3306/my_db?rewriteBatchedStatementstrue驱动就会在发送前把同一SQL模板的批处理参数改写成一条多VALUES的INSERT或者批量语句一次性发给数据库。这一步才是BATCH模式真正提速的关键。我在前面的测试数据里BATCH模式开不开这个参数性能差了将近一个数量级。改完记得看一下事务边界。很多项目里BATCH执行器配对了但调用Mapper方法的外层没有加事务MyBatis会在每一次insert时就把语句直接发送批量效果自然归零。这是最常见的一处“配置正确但没生效”的原因。5.2 max_allowed_packet与SQL长度限制foreach拼接大SQL时错误信息往往很直接PacketTooBigException或者干脆连接被断开。这时候要查的是MySQL服务端的max_allowed_packet。它有服务端和客户端两层概念。服务端是my.cnf里的配置控制单次通信包的最大大小客户端是JDBC连接URL里的maxAllowedPacket参数。两者必须匹配通常建议服务端设成16MB到64MB客户端URL加上maxAllowedPacket6710886464MB。不要只改一边两边不一致会引发莫名其妙的截断或断连。对于大批量数据我更建议在代码层限制单批大小而不是依赖调大这个参数去扛。原因很简单参数调大只能解决“不报错”但一条超大SQL在执行计划优化、内存分配和数据页锁定时代价都跟在后面。5.3 allowMultiQueries是个容易误用的参数还有一个参数很常见但我不推荐用于批量写操作allowMultiQueriestrue。它允许一条连接上发送多条用分号分隔的SQL。看起来很方便可以在一个XML方法里直接拼接多条DELETE甚至多条UPDATE把一次网络往返做了。但问题在于多条SQL无法共享一个事务边界除非你手动控制而且MyBatis的动态SQL拼出来的分号语句在出错时定位困难日志打印出来也特别难读。更重要的是数据库执行多条语句时锁的释放和错误回滚行为都变得不可控线上排查成本很高。日常项目里能用IN、能用batch模式解决的问题就别把多条SQL用分号拼接。这个参数我基本只在初始化脚本里会用到业务代码里是禁用的。6. 选型建议和一份实战避坑清单把前面几章的内容落到具体项目里我日常的选型规则大致如下。插入数据单批几千条以内优先foreach拼接VALUES上万条数据改用ExecutorType.BATCH并且一定加上rewriteBatchedStatements参数推送数据时按每批500到1000条拆分不追求一条SQL搞定全部。批量修改目标行数少几十行以内直接逐条UPDATE代码清晰、锁范围小几百到几千行用CASE WHEN方案量再大就换临时表JOIN UPDATE如果连临时表都吃力就要从业务上考虑分页或异步批量不要硬扛。批量删除主键IN删除加空集合判断每批500到1000条能软删除就软删除物理删除只留给维护场景。再送一份问题排查表至少覆盖我遇到过的绝大多数情况现象可能原因解决办法INSERT耗时长接口调用慢for循环单条提交事务频繁提交改为foreach拼接或BATCH模式BATCH模式没有性能提升缺少rewriteBatchedStatements参数连接URL加rewriteBatchedStatementstrueDELETE/UPDATE报语法错误集合为空生成IN ()XML里加ids.size() 0判断大批量SQL报PacketTooBig单条SQL超过max_allowed_packet分批执行或调大max_allowed_packetBATCH下数据没有提交接口却不报错没有外层事务执行器提前提交确认包在Transactional里大批量UPDATE后主从延迟高单条SQL更新行数过大改分批或临时表JOIN UPDATE多条件造成的OR条件性能下降OR组合过多SQL模板过于复杂改双字段元组IN或换临时表方案最后说一点个人体会。有一次我处理某项目的数据初始化第一批用foreach拼了一万条INSERT直接撞上max_allowed_packet数据库连接断了一地。后来改成每批800条foreach稳定跑完后我又试了一遍BATCH模式发现连CPU占用都低了一截。那之后我就养成了个习惯做批量任务前先看单行数据宽度、数据库参数、以及目标表有没有触发器或外键约束宁可多花十分钟评估也不上线后再处理故障。MyBatis给批量操作留了很多可能性但最终跑得稳不稳往往取决于你对底层执行路径的理解而不是单纯换个标签的事。
阅读完成 · 觉得有帮助？
