前阵子做一个数据迁移任务要把一份80万行的订单明细从Excel灌进MySQL一开始我用JdbcTemplate写了个循环insert跑完一次要将近20分钟。后来在连接串上加了rewriteBatchedStatementstrue同样的数据整体耗时直接从十分钟级别压到了几秒那种“删库存清缓存”的改动带来的反差感真的每次都会被震惊到。今天就把这个参数的原理、配置方式、还有实战中踩过的坑完整拆一遍。后端同学、做ETL同步的、写过临时数据导入脚本的人都应该看看这个参数。它能让你手头那套“看起来能跑但慢得要死”的批量插入代码瞬间脱胎换骨。1. “批量插入”慢的真正原因多数人没看到驱动这层1.1 你写的“批量”可能只是循环的变种我见过太多的批量插入代码其实是这样的for (OrderDetail order : orderList) { jdbcTemplate.update(insertSql, order.getId(), order.getSku(), order.getPrice()); }这种写法的问题在于每执行一次updateJdbcTemplate都会走一遍“获取连接、发送SQL、等待数据库执行、返回结果”的完整链路。在MySQL本地连接上一次往返可能只有0.5毫秒看着不多但10万条就是10万次网络往返算下来光网络等待就干掉50秒这还没算SQL编译、执行计划生成、日志写盘这些开销。所以第一层优化就是把update换成batchUpdate。jdbcTemplate.batchUpdate(insertSql, orderList, 1000, (ps, i) - { ps.setString(1, orderList.get(i).getId()); ps.setString(2, orderList.get(i).getSku()); ps.setBigDecimal(3, orderList.get(i).getPrice()); });很多人以为到这里优化就做完了。但实测里你会发现batchUpdate确实比循环快一些但远没有到“秒级导入”的程度原因在于你忽略了JDBC驱动默认的批处理行为。1.2 MySQL远程连接时“一次批”可能仍是一次“逐条执行”这里有一个很隐蔽的默认值问题。MySQL的Connector/J驱动里rewriteBatchedStatements参数默认是false。在这个默认状态下你虽然调用了executeBatch()但驱动并不会把你的1000条INSERT合并成一条大SQL发给MySQL。它做的依然是把每条INSERT往服务端逐个发送只是复用了同一个PreparedStatement对象而已。也就是说网络往返次数还是1000次数据库端还是执行了1000条独立的INSERT语句。所以单纯靠batchUpdate减少的是应用层的循环启动开销并没有消灭真正占大头的东西网络round-trip和单条SQL的解析执行开销。1.3 参数为什么常年被忽略这个参数默认值写在驱动文档的角落里Spring也好、HikariCP也好都不会帮你在连接串上自动加。业务代码层面看不出差异只有做性能剖析或者对比压测的时候才会发现。我见过不少项目把“批处理优化”理解成JdbcTemplate提供了batchUpdate方法就算结束了直到数据量从几万涨到几十万导入时间从几秒变成几分钟才开始回头查连接参数。更麻烦的是有些框架比如某些老版本ORM底层把批量参数写死成了RETURN_GENERATED_KEYS模式这时即使你在URL上加了rewriteBatchedStatementstrue也没办法完全生效后面第5章会专门讲这个问题。2. rewriteBatchedStatements到底改了什么2.1 一条参数把N条INSERT合并成一条多VALUES把rewriteBatchedStatementstrue加进JDBC URL之后驱动会扫描当前批里所有语句如果发现它们的SQL模板一致就会尝试把参数值内联到SQL里合并成一条INSERT INTO ... VALUES (...), (...), (...)的语句发送到MySQL。比如代码里的模板是这样的INSERT INTO order_detail (order_id, sku, price) VALUES (?, ?, ?)驱动实际发给MySQL的可能是INSERT INTO order_detail (order_id, sku, price) VALUES (10001, A1001, 29.90), (10002, A1002, 39.90), (10003, A1003, 49.90)合并之后1000条INSERT变成了一条大SQL、一次网络发送、一次服务端解析执行。这个改动直接让耗时量级往下掉。2.2 三层收益要分开看第一层是网络传输成本。批大小1000时10万条数据只需要发送100次SQL如果不合并就是10万次。网络round-trip的差距在跨机房、公网数据库场景下会表现得更加夸张。第二层是MySQL服务端的解析开销。单条SQL和N条SQL语法解析、权限检查、优化器生成执行计划的次数完全不同合并后服务端只需要解析一次CPU占用也会明显下降。第三层是日志同步成本。这里要稍微提醒一句重写后SQL语句条数变少statement级别的日志确实会减少但若binlog是row格式日志内容仍然按行记录并不会因为合并而大幅缩水。所以别指望这个参数能解决所有磁盘IO问题它解决的主要是网络和解析。2.3 重写不是无条件生效的这个参数不是“加上了就一定合并”。驱动内部会检查一堆条件最常见的不生效场景有三个批量里的SQL模板不一致。比如你在同一个executeBatch里混入了INSERT和DELETE驱动就没办法合并只能按原样逐条执行。批量执行时指定了返回自增主键RETURN_GENERATED_KEYS。因为合并成多VALUES后MySQL只能返回这批语句的第一条自增ID驱动没法把每条记录的自增ID精确对应回去所以这时重写会失效或行为受限。autoCommit为true时执行批处理。官方文档明确建议批量插入要在autoCommitfalse的环境下执行否则驱动会在每条SQL后自动提交重写带来的收益会被事务提交的额外开销吃掉大半数据安全上也有隐患。2.4 一条多VALUES在事务里意味着什么当重写生效后你的批处理变成了一条超大INSERT语句。如果你在一个事务里执行多个批次那么整个事务里的SQL都会被正常管理如果中途有某一批失败这一批的多VALUES语句会整体回滚。这带来的副作用是错误粒度变粗了。原来1000条单条INSERT时哪一条失败你能精确定位合并后只要这一批里有一条数据触发约束冲突驱动会报错但你不能立刻知道具体是哪个值出了问题只能自己用日志或者查重逻辑缩小范围。3. Spring Boot JdbcTemplate落地配置3.1 三种直连配置方式最直接的方式是在application.yml的JDBC URL里加上参数spring: datasource: url: jdbc:mysql://127.0.0.1:3306/your_db?useUnicodetruecharacterEncodingutf8rewriteBatchedStatementstrue如果连接池用的是HikariCP除了改URL也可以通过>spring: datasource: url: jdbc:mysql://127.0.0.1:3306/your_db hikari: >spring: datasource: druid: connection-properties: rewriteBatchedStatementstrue比较推荐做法是直接在URL上配置。这样不管连接池怎么换驱动本身的参数都不会丢排查问题时也一眼能看到。3.2 写对批量插入代码配置参数只是第一步代码必须真正走到PreparedStatement批处理路径。JdbcTemplate的batchUpdate是满足条件的但要注意别把String的普通SQL和参数写反了。推荐写法一是JdbcTemplate自带的batchUpdate(String sql, Collection batchArgs, int batchSize, BatchPreparedStatementSetter)int batchSize 1000; jdbcTemplate.batchUpdate( INSERT INTO order_detail (order_id, sku, price) VALUES (?, ?, ?), orderList, batchSize, (ps, i) - { OrderDetail item orderList.get(i); ps.setString(1, item.getOrderId()); ps.setString(2, item.getSku()); ps.setBigDecimal(3, item.getPrice()); });参数batchSize的作用是把几百万元的列表按指定条数切成多个子批每个子批内部先addBatch()再executeBatch()。传入1000就是每1000条执行一次批提交整个列表会循环执行。推荐写法二是NamedParameterJdbcTemplate配合SqlParameterSourceUtils.createBatchString sql INSERT INTO order_detail (order_id, sku, price) VALUES (:orderId, :sku, :price); SqlParameterSource[] batch SqlParameterSourceUtils.createBatch(orderList); namedParameterJdbcTemplate.batchUpdate(sql, batch);这种写法代码很简洁但createBatch会一次性把你传进来的整个集合转成参数数组如果列表有几十万条内存里会同时构建几十万个参数对象。我建议数据量大时还是手动分批或者至少限制单批的大小。3.3 事务怎么配置更稳妥要跑到秒级最好让整个导入过程跑在一个事务里同时autoCommit设置成false。Spring里最简单的是在Service方法上加Transactional。这样Spring会在方法开始时关闭自动提交在方法结束时统一提交正好满足驱动重写和性能的需求。但这里有个权衡几条大事务等于把大量INSERT的undo信息都堆积在一起数据量特别大时MySQL的内存和undo表空间压力会很明显。我的习惯是每1万条或2万条数据作为一个事务边界手动提交一次既能吃到批量合并的红利又不会让单事务无限膨胀。3.4 完整可参考的导入片段下面是一个能在日常项目里直接改着用的片段Service public class DataImportService { private final JdbcTemplate jdbcTemplate; public DataImportService(JdbcTemplate jdbcTemplate) { this.jdbcTemplate jdbcTemplate; } Transactional public void batchImport(ListOrderDetail totalList) { int batchSize 1000; jdbcTemplate.batchUpdate( INSERT INTO order_detail (order_id, sku, price) VALUES (?, ?, ?), totalList, batchSize, (ps, i) - { OrderDetail item totalList.get(i); ps.setString(1, item.getOrderId()); ps.setString(2, item.getSku()); ps.setBigDecimal(3, item.getPrice()); }); } }注意Transactional放在这里是为了保证整个方法处于一个事务里如果你不想让单事务太长可以自己循环分批并在每个批后调用事务管理器提交或者用编程式事务控制。4. 实测记录同样的代码参数加不加区别有多大4.1 测试环境说明我简单搭了一个模拟项目X做对照实验。环境是Spring Boot 2.xMySQL 8.0跑在一台配置普通的机器上Java应用和MySQL同机房网络表结构是8个字段插入数据量10万条每条记录大小约200字节。测试分四组纯循环、batchUpdate不开启参数、batchUpdate开启参数、开启参数后调整批大小。需要说明的是这个数据只是我当时环境下的表现你的库表结构、硬件、网络延迟不同绝对数值会有差异但相对量级和趋势是稳定的。4.2 四组对照结果执行方式10万条耗时观察到的现象纯循环update不开任何参数约310秒JDBC连接持续占用MySQL服务端CPU使用率明显偏高JdbcTemplate batchUpdate不开启rewrite约78秒比循环快一些但依然有很多网络等待batchUpdate开启rewriteBatchedStatements约2.5秒网络等待几乎消失CPU使用率降了一大截开启参数批大小调整为5000约2.1秒每秒能处理大概5万条接近本地磁盘和MySQL执行上限从78秒到2.5秒只靠一个连接串参数就做到这个差距非常有说服力。如果把数据量从10万提到100万差别会更夸张纯循环基本跑不动而开启参数后大约在20秒到30秒之间能跑完。4.3 批大小和SQL大小要联动调整别以为批大小越大越好。我试过一次把批大小调到20000结果插入很快报错原因就是合并后的SQL超过了MySQL的max_allowed_packet限制。MySQL默认的max_allowed_packet通常是4MB或16MB具体取决于版本和安装配置。如果每条记录200字节1万条的批大约2MB看起来不大但如果记录里有很多text字段或者较长的字符串一个批很快就超过限制。遇到这种报错有两个解决办法一是调小批大小比如从5000降到1000二是修改MySQL的max_allowed_packet。生产环境我建议优先调小批大小避免把数据库单包限制放得过大引发其他风险。本地测试时可以临时执行SET GLOBAL max_allowed_packet 67108864;把上限调到64MB看效果。4.4 为什么有时开参数也达不到秒级如果你加了参数效果还是很差建议按这个顺序排查检查代码是否真的走了batchUpdate而不是update。rewriteBatchedStatements只对批处理生效逐条插入是不会被重写的。检查批量里的SQL模板是否一致。如果循环里对每条SQL拼接了不同的条件比如把where条件一起拼进去了驱动识别不了“同构”就不会合并。检查事务设置。autoCommit为true时重写效果会打折扣还容易造成部分成功部分失败的脏数据。检查连接池有没有真正把参数传给驱动。有些连接池封装层次较深只改URL不一定有效最好在日志里看SQL实际执行情况或者用驱动连接监控确认。5. 常见问题与排查技巧实录5.1 参数加了看着没生效最常见原因就是没走批处理API或者走的批处理路径被框架“伪批处理”了。我排查过一个项目业务代码用的是JdbcTemplate的batchUpdate(String sql, ListObject[] batchArgs)这个接口看起来是批处理但它内部是把参数包装后逐条绑定吗实际上这个重载在驱动里依然是executeBatch正常情况下参数可以生效。关键还是看日志。可以在MySQL开启通用日志观察连接发来的SQL是逐条单VALUES还是合并后的多VALUES。如果全是单VALUES说明驱动根本没识别为可重写的批量需要回到代码路径上找问题。5.2 批量插入拿不到自增IDrewriteBatchedStatements和多VALUES语句天生就不擅长返回每一条记录的自增ID。如果你需要批量插入后拿到每条数据的主键驱动只能返回批里第一条的自增ID或者直接给你Statement.SUCCESS_NO_INFO也就是-2。场景需要插入后立刻用到主键时我一般的做法是放弃批量合并改用单条插入返回主键或者走两步先批量插入再根据业务唯一键查出来。第二种方案多一次查询但能保住秒级导入的优势。5.3 executeBatch返回值全是-2这是开启重写后很正常的现象。合并执行后驱动无法统计每一条单独的受影响行数所以int[]结果里很多元素会是-2。Spring的batchUpdate一般会忽略这个返回值但如果你自己写JDBC底层代码注意别拿这个返回值做震了逻辑判断。如果你依赖受影响行数做后续决策可以在SQL里加上useAffectedRowstrue参数或者在业务层换一种判断方式比如先查总数。不要指望重写后还能拿到精确的逐条影响行数。5.4 批中一条数据出错导致整批回滚合并成多VALUES后错误定位会变难。应对方法是把批大小调小并在日志里打印当前批的前几条数据和SQL模板摘要。另外如果业务允许部分成功可以循环捕获每个子批的异常记录失败批次后继续执行但这时要注意Transactional不要标注在包含循环的整个方法上否则会连坐全部回滚。5.5 和allowMultiQueries不要混用有朋友看到rewriteBatchedStatements能合并SQL会联想到连接串里的allowMultiQueriestrue想把它们一起打开。allowMultiQueries允许一条语句中用分号拼接多个SQL这和批处理完全不是一回事两者相互独立不需要一起打开。反而乱开allowMultiQueries会增加SQL注入面不建议在生产环境使用。5.6 其他数据库有没有类似的参数这个参数是MySQL驱动特有的换数据库就要换思路。PostgreSQL JDBC驱动有reWriteBatchedInsertstrue作用和MySQL的rewriteBatchedStatements类似配置在连接参数里。SQL Server则是用表值参数或者TVP方式实现高性能插入Oracle用批量绑定。如果我这个MySQL方案在你的场景里跑通了顺着这个思路去查对应数据库的批量参数基本都能找到对应的优化手段。6. 除了参数还能让导入再快一点6.1 预处理数据减少数据库端解析批量插入的SQL模板尽量保持简单。如果代码里每批都拼接不同的列或者使用了大量的函数、表达式驱动可能无法识别为同模板语句也就没法合并。我一般会在进入批处理前把数据清洗、格式转换、枚举映射全部在Java内存里做完数据库只做纯插入。6.2 关闭非必要的实时索引如果你是往一张已经有很多索引的大表里导入数据索引维护会是比SQL执行更昂贵的开销。项目允许时可以临时禁用或者删除非核心索引导入完成后再重建。二级索引对批量插入的影响很大有些场景下先导数据再建索引耗时能从分钟级降到秒级。6.3 检查连接池和MySQL服务端配置连接池的maximumPoolSize不需要太大批量任务通常会占用少数连接持续工作过多连接反而会让MySQL的线程调度压力变大。MySQL这边innodb_buffer_pool_size要尽量大于你的目标数据表体积innodb_flush_log_at_trx_commit如果对数据丢失容忍度较高可以适当调整为0或2减少每次事务提交的刷盘开销。6.4 极端场景再上并行参数优化到极限还不够时再考虑多线程并行导入。并行前务必先想清楚数据库的IO上限和锁竞争我见过并行插入把MySQL磁盘IO打满结果比单线程还慢的情况。稳妥点的做法是先增加批大小和事务边界粒度过小时再上2到4个线程观察效果不要一上来就开几十个线程。我个人在实际操作中的体会是rewriteBatchedStatements是性价比最高的一个优化点一行配置下去立竿见影。但它不是银弹批大小、事务边界、数据预处理都影响最终效果。如果你手头正好有个导入任务很慢先别急着上并行、换框架把这条参数加上再去看MySQL的监控曲线你会回来感谢这个发现的。
