文章详情
以下为资讯详情页模板：正文区域由后台内容渲染，图片与正文将自动替换为对应文章内容。
大概一年多前我在负责一个数据初始化模块要往MySQL里灌300万行左右的业务数据。最开始图省事直接在Mapper里写了一个foreach拼接的INSERT结果一跑就报max_allowed_packet相关错误。换成SqlSession的BATCH模式后情况好了一阵但数据量再上去又冒出了主键拿不到、日志打印导致内存暴涨这些幺蛾子。我当时特别想知道大家天天说的Mybatis批量插入foreach、SqlSession批量、还有直接拼SQL文本这三种方式在真实场景下到底差多少、各自适合什么量级。正好最近又做了一次完整测评把过程和结论整理出来给同样被批量插入折磨过的朋友一个参考。下文涉及具体秒数的地方都在同一台4C8G云主机、MySQL 8.0.33、JDK 17环境下实测得到不同机器会有浮动但三者的相对差距和结论基本是稳定的。1. 三种插入方式的技术形态先搞清楚它们到底在做什么很多文章把批量插入混为一谈其实这三种方式从代码形态到底层执行链路差异很大。不先把形态定义清楚后面讨论效率都是空谈。1.1 foreach插入的实际SQL形态与连接参数依赖这是Mybatis里最常见的写法在Mapper XML里这样写insert idbatchInsert parameterTypejava.util.List INSERT INTO t_user (name, phone, register_time) VALUES foreach collectionlist itemitem separator, (#{item.name}, #{item.phone}, #{item.registerTime}) /foreach /insert这段XML最终生成的是一条SQL形如INSERT INTO t_user (name, phone, register_time) VALUES (a,1,...), (b,2,...), ...注意网上还有一种写法是把多条INSERT语句用分号拼在一起foreach collectionlist itemitem separator; INSERT INTO t_user (name, phone, register_time) VALUES (#{item.name}, ...) /foreach这种写法依赖MySQL连接URL中的allowMultiQueriestrue参数因为MySQL服务器默认不允许一次请求携带多条语句。它有明显安全隐患也会让SQL日志变得很难看不推荐。本文后面提到的foreach方式默认指第一种单条多VALUES的写法。foreach方式最大的特点应用层不需要改Java代码和普通单条插入调用同一个Mapper方法只是传入的List变大了。Mybatis会把这个List解析成一个巨大的参数集合再拼到SQL里。1.2 ExecutorType.BATCH到底批量在哪一层SqlSession批量插入的代码形态是这样的SqlSession batchSession sqlSessionFactory.openSession(ExecutorType.BATCH); try { UserMapper mapper batchSession.getMapper(UserMapper.class); for (int i 0; i totalCount; i) { User user buildUser(i); mapper.insert(user); if (i 0 i % batchSize 0) { batchSession.flushStatements(); } } batchSession.commit(); } finally { batchSession.close(); }这里的循环仍然是逐条调用mapper.insert(user)但关键在于ExecutorType.BATCH。Mybatis拿到这个执行器后不会把每条INSERT立刻发送给数据库而是先缓存到JDBC的batch队列里直到调用flushStatements()或commit()才真正执行。很多人误以为BATCH模式就是Mybatis帮你把SQL拼成一条大的其实不是。Mybatis只是把每条INSERT交给JDBC驱动的addBatch()真正的批量发送是MySQL驱动层做的。而驱动层的表现又取决于一个容易被忽略的参数rewriteBatchedStatements。1.3 这里说的sql插入是指什么标题里的sql插入在不同文章里指代不同。有人拿它指JDBC原生拼接SQL执行也有人指Mybatis的sql标签。我这里先做个澄清Mybatis的sql标签本质是公共SQL片段复用比如把一段重复的列名抽出来sql iduserColumnsname, phone, register_time/sql insertINSERT INTO t_user (include refiduserColumns/) VALUES .../insert它和本文讨论的“拼大SQL批量插入”完全是两回事。本文后面提到的sql插入是指绕开Mybatis的参数映射直接在应用层用StringBuilder把完整VALUES拼好再通过JDBC的Statement.execute()整段执行StringBuilder sb new StringBuilder(INSERT INTO t_user (name, phone, register_time) VALUES ); for (int i 0; i batchSize; i) { if (i 0) { sb.append(,); } sb.append(().append(escape(buildName(i))).append(,) .append(escape(buildPhone(i))).append(,2024-01-01 00:00:00)); } statement.execute(sb.toString());为什么要单独测这种写法因为有时候Mybatis的XML解析和参数绑定会成为瓶颈有人会上极端方案完全绕开Mapper直接拼SQL推给数据库。这种做法在效率上确实有可取之处但代价也非常明显后面会专门讲。2. 测试准备数据、环境与那些容易被忽略的配置项在做效率对比之前必须先搭一个尽量公平的测试环境。否则改了一个参数结论就完全反过来那就没有参考价值了。2.1 测试环境与数据口径我用的测试环境如下项目配置操作系统Linux 云主机配置4C8G数据库MySQL 8.0.33JDK17连接池HikariCP 默认配置测试表t_user5个字段加4个索引这里有个很关键的决策表结构不能太简单。很多人测试批量插入用一张只有两个字段的无索引表测出来每秒几十万行真实业务中毫无参考价值。我把表建模成接近实际业务的形态字段包含用户名、手机号、注册时间等还叠加了4个索引插入时索引维护的开销无法回避。数据口径上每条记录的字段长度保持一致避免某批数据刚好特别短导致结果失真。每次执行前先TRUNCATE TABLE清空数据每种方式连跑3次取最好成绩这样可以尽量排除冷缓存、GC抖动带来的偶然误差。2.2 MySQL连接URL与驱动参数对结果的影响测试中所有方式都用同一个连接池连接URL是jdbc:mysql://localhost:3306/test?useUnicodetruecharacterEncodingutf8rewriteBatchedStatementstrue重点说下rewriteBatchedStatements。这个参数在MySQL Connector/J中默认是false。在false状态下你通过JDBC调executeBatch()驱动仍然会把batch里的SQL逐条发给服务器只是少了应用层逐条调用的网络往返。也就是说BATCH模式的很大一部分优势在没有这个参数时根本发挥不出来。实测中我把这个参数开关各测了一组差距相当惊人数据在下一节里给出。另外还有一个参数useServerPrepStmts也值得关注它决定PreparedStatement在服务端还是客户端做预编译。高并发批量插入场景下客户端预编译反而更稳这个属于进阶调优后文会展开。2.3 测试脚本设计防止缓存与并发干扰测试脚本本身也有几个坑。第一一定要做预热。JVM有JIT接口第一次调用通常偏慢我会先插入5000行作为预热不计入结果。第二日志必须关闭。Mybatis的SQL日志如果开着会把整条大批量SQL文本都打印出来10万行的VALUES拼起来可能几百MB日志刷盘时间甚至超过插入本身严重干扰测试。第三测试期间数据库不能有其他任务在跑binlog和复制链路的干扰也要考虑我直接把复制从库停掉了只测主库单机写入。准备工作做完下面才是重头戏。3. 实测数据不同数据量下的耗时差异与直观结论3.1 10万行场景下的对比结果先看10万行数据这是很多业务系统日常导入的常见量级。我拿5种方式做了对比插入方式10万行耗时备注逐条插入SIMPLE执行器基线约25秒网络往返开销巨大foreach拼接每批5000行约2.8秒代码最简洁BATCH模式rewriteBatchedStatementsfalse约8秒驱动逐条发送SQLBATCH模式rewriteBatchedStatementstrue约2秒驱动重写为多VALUESsql拼接每批5000行约2.5秒内存占用偏高这个结果有几个值得注意的点。第一逐条插入和批量插入之间是两个数量级的差距15万到25秒对比明显根本原因不在数据库服务端而在应用与数据库之间的网络交互次数。第二BATCH模式不开启rewriteBatchedStatements时性能连foreach都不如这解释了很多团队“我用了BATCH但没变快”的困惑。第三sql拼接在这个量级确实不比foreach差但提升也很有限后面会分析它的真实代价。3.2 50万行与100万行场景各自开始暴露问题数据量涨上去之后几种方式的差距进一步拉开同时各自的问题也开始现形。我直接看50万和100万两个量级插入方式50万行耗时100万行耗时主要问题暴露点逐条插入约130秒约260秒以上完全不可接受foreach每批5000约15秒约32秒批次稍微调大就开始接近SQL长度极限BATCHrewritetrue约7秒约14秒一切正常最稳BATCHrewritefalse约38秒约80秒驱动层退化为逐条发送sql拼接每批5000约12秒约26秒拼接过程GC压力大内存峰值高100万行时BATCH开启rewrite后大约14秒接近每秒7万行的写入速度对于一个带索引的业务表来说已经是很不错的值。foreach虽然也能跑完但每批5000行意味着要发200个请求XML解析和参数绑定的开销累积起来就很可观。sql拼接方式在100万行时出现明显的内存抖动StringBuilder在拼接过程中频繁分配大对象GC日志里能看到明显的停顿。3.3 数据背后的第一个结论测试做下来第一个直观结论是BATCH模式 rewriteBatchedStatementstrue 在10万到100万这个区间内是稳定最优解。foreach在数据量较小时和BATCH差距不大但到百万级别会被拉开sql拼接除了内存风险外速度也很难超越BATCH。第二个结论是连接参数对结果的影响可能比插入方式本身还大。同一个BATCH模式开不开rewriteBatchedStatements性能差接近5倍。这就意味着网上那些脱离参数环境得出的“BATCH模式很慢”的结论很可能就是踩了这个隐藏开关的坑。4. 执行链路的底层拆解为什么快、为什么慢、为什么会报错只知道快慢不够还得知道快在哪、慢在哪。这一节从执行链路层面把三种方式的本质差异讲清楚。4.1 foreach方式的开销XML解析、网络包与max_allowed_packetforeach方式的核心问题是“一条SQL的长度”。MySQL服务端对单次请求的SQL文本长度有硬限制由参数max_allowed_packet控制默认通常是64MB但在不少云数据库实例上会被调成4MB或16MB。你可以用下面的SQL查看SHOW VARIABLES LIKE max_allowed_packet%;假设每条VALUES约80字节批5000行就是400KB批1万行就是800KB。听起来不大但别忘了Binlog、undo log、redo log都会因为这条大SQL产生额外存储主从复制时从库也要执行这条长SQL网络传输和从库解析成本都会成倍增加。很多人在实战中遇到PacketTooBigException就是单批行数乘上每行长度超过了max_allowed_packet。解决办法不是无限调大这个参数而是控制单批大小。我实测下来foreach单批控制在3000到5000行之间比较合适超过1万行很容易出问题而且即使不报错MySQL解析超长SQL的CPU消耗也会显著上升。4.2 BATCH模式依赖的JDBC批处理机制rewriteBatchedStatements的影响BATCH模式的链路要仔细捋一下。应用层循环调用mapper.insert()Mybatis BATCH执行器把这些INSERT都收集起来最后通过JDBC的PreparedStatement.addBatch()加入批量队列再executeBatch()一次性提交给MySQL驱动。关键就在这里MySQL的JDBC驱动拿到一个batch后如果rewriteBatchedStatementsfalse就会老老实实把每一条INSERT拆开逐条发给服务器。这对应用层来说确实是一次网络调用但对服务器来说还是收到了几十上百条独立INSERT服务端的执行开销和逐条插入没本质区别。当rewriteBatchedStatementstrue时驱动会在客户端把一批同构的INSERT重写成一条带多个VALUES的大SQL比如你addBatch了1000条驱动就组装成一条1000个VALUES的INSERT发过去。这一步其实就是我们在foreach方式里做的事情但省掉了Mybatis的XML解析、参数列表构建这一层而且流程发生在驱动内部比在Mybatis层拼SQL更接近数据库协议底层所以性能是最好的。4.3 sql拼接方式的OOM隐患与SQL长度边界sql拼接方式表面上就是“手动做了foreach”它比foreach少了解析XML和参数映射的开销但多了一个必须自己处理的坑转义。VALUES里的字符串如果有单引号、反斜杠直接拼进SQL就会语法错误甚至形成注入点。必须对每个字符串做转义处理这就无形中增加了CPU和内存开销。更麻烦的是拼接过程中StringBuilder会不断扩容一批5000行的SQL文本就有400KB到800KB如果业务字段更多一两千万的数据文件在内存里反复拷贝GC压力非常大。我之前在一次测试中观察到sql拼接方式插入100万行时老年代占用比BATCH方式高出一倍多Full GC次数明显增多。这就是它的真实代价代码层面看似绕开了框架开销实际上把内存和安全的成本转移给了应用自身。数据量小的时候无所谓一旦上量随时可能在你毫无准备的时候把应用拖垮。5. 踩坑记录批量插入实战中的典型问题与调优手段这一节全是实战中踩过的坑一个个说。5.1 坑一foreach里拼多条INSERT时没配allowMultiQueries曾有同事A在某项目里用foreach拼了多条INSERT自测时报语法错误。原因是把SQL写成了foreach collectionlist itemitem separator; INSERT INTO t_user (name) VALUES (#{item.name}) /foreach生成出来的SQL长这样INSERT INTO t_user (name) VALUES (a); INSERT INTO t_user (name) VALUES (b); ...MySQL默认不允许在一次请求里发送多条语句必须连接URL上加allowMultiQueriestrue。这个参数一开等于把多语句执行能力开放给所有请求一旦应用里存在SQL拼接漏洞攻击面会明显扩大。我的建议是能不用就不用改成单条多VALUES的写法顺带还能省一个连接参数。5.2 坑二BATCH模式下拿不到自增主键这是最让人头疼的坑。在BATCH模式下useGeneratedKeys的生效情况和普通插入不一样。我在百万行测试里需要拿到每行的自增主键去做关联数据结果发现flush之后批量插入返回的主键值要么为空要么无法一一对应。排查下来的结论BATCH模式下一次executeBatch()会执行多条INSERT驱动能返回的auto_increment信息并不保证能精确映射到每条记录上。解决方式有几种如果业务必须拿主键就在每条INSERT之后单独执行一次SELECT LAST_INSERT_ID()但这等于又退回了逐条模式或者改造表结构插入前在应用层生成业务主键不依赖数据库自增再或者用foreach方式插入这种方式的useGeneratedKeys是能正确回填的。我的建议是先确认业务是否真的需要在插入过程中立即拿到主键很多时候这个需求可以用事后批量查询替代。5.3 坑三日志中的SQL打印让应用直接OOMMybatis开启SQL日志后如果执行了一万行的批量INSERT日志框架会把这条SQL文本完整打印出来。一条包含1万行VALUES的SQL文本轻松超过1MB如果日志同时输出到文件和控制台IO开销和内存开销都是灾难级的。我之前在测试时开着DEBUG日志跑50万行数据还没等插入完成应用内存就涨到了几个GB最后OOM。排查时第一反应是数据量太大导致的后来才发现是日志刷屏惹的祸。批量插入场景下要么关闭Mapper的日志输出要么对日志做截断处理。这是一条很多人忽略但实际上影响巨大的经验。5.4 把max_allowed_packet和事务粒度调对的顺序调优顺序也很重要。一次批量插入的常见链路是先怀疑单批大小再怀疑SQL长度最后才想到连接参数。我的建议是先确认rewriteBatchedStatements已经开启再根据max_allowed_packet来定单批大小最后确认事务边界。关于事务粒度很多人喜欢把100万行包在一个事务里要么全成功要么全回滚。这种方式在数据量大时有两个问题一是undo log占用巨大会拖慢数据库二是万一中间失败回滚时间可能比插入时间还长。我的实践是每2到5万行提交一个事务这样既能保证失败重试的粒度可控也不会让数据库事务压力过大。6. 选型建议不同业务场景下我最终采用的方案结合实测结果和踩坑经验我在不同场景下有不同的选型。6.1 万行以内无脑foreach数据量在1万以内foreach是最优选择。代码简洁阅读成本低不需要处理SqlSession的获取和关闭也不涉及额外的连接参数。有人可能会说BATCH性能更好但在万行量级foreach耗时可能就是1秒BATCH是0.3秒这点差距在真实业务里根本感知不到而复杂度和维护成本却实打实增加了。6.2 十万量级BATCH模式是标准答案10万量级尤其是需要稳定写入带索引的业务表时我优先选择ExecutorType.BATCHrewriteBatchedStatementstrue每次flush 1000到2000条。这里的flush频次要说明一下不是越大越好因为每次flush的batch过大驱动重写生成的SQL会很长又回到了SQL长度问题。实测中1000到2000条是稳定性很好的区间。如果你用的是Spring还需要注意一个细节Spring的SqlSessionTemplate默认执行器是ExecutorType.SIMPLE即使你的Mapper方法里写了batch逻辑也可能不生效。要在配置里指定执行器类型mybatis: configuration: default-executor-type: batch或者在Java代码里手动获取BATCH类型的SqlSession。6.3 百万量级分片加批次的组合拳100万行以上我会用分片加批次的组合。整体思路是把100万行切分成若干个5万行的大片每个大片内再用BATCH模式以1000行为一个批次flush每个大片完成后提交一次事务。这样数据库不会在同一时间接收到过大的网络包和SQL文本应用内存也一直维持在一个稳定水位。表中有大量索引时也可以考虑先评估索引总量如果插入时间完全不可接受业务上又允许可以临时删除非必要索引插完再重建。这个操作代价不小但有时候是唯一可行的方案。我实际用过一次在5个索引的表中插入200万行删除3个非必要索引后总耗时从十几分钟降到几十秒重建索引的时间也都回来了。6.4 最后再分享几个写代码时的小技巧代码层面的几个细节顺手写在这里。批量插入前一定要确认连接池配置的自动提交状态用BATCH模式时最好在事务内部操作否则每批flush都会隐式提交事务边界完全失控。插入完成后在低峰期执行一次ANALYZE TABLE更新统计信息对后续查询计划有帮助。最后建表时的行格式和字符集也会影响批量插入速度utf8mb4和utf8的差别在超大批量时会放大能用utf8就别盲目用utf8mb4前提是业务确实不需要生僻字或Emoji。测完这一轮之后我对批量插入的整体认识更清晰了。最核心的一点是性能和代码形态的关系没有想象中那么大真正决定上限的是数据库参数、驱动参数、分片策略和事务边界的整体配合。以后再有人问Mybatis批量插入怎么选我第一句会先问你的rewriteBatchedStatements开了吗如果没开先把参数调对再回头讨论用哪种方式。特别提醒：考试时间、报名批次等安排如有调整，以河南省应急管理厅及官方考点最新通知为准。
