JDBC批量更新性能优化：从原理到实战避坑指南

接到过一个线上任务日终批量给一张千万级的用户表打标签几千条数据逐条 update跑了快二十分钟还带超时。后来换成 JDBC Batch Update压到几十秒收工这是第一次直观感受到批量提交的差距。JDBC 批处理不是什么新东西但不少人把它用成了“循环里调 addBatch 再 executeBatch”的机械操作参数没配、边界没处理性能照样拉胯。这篇就把 Batch Update 从原理、实操到排坑完整梳理一遍适合正在写数据同步任务、批处理作业或者被慢 SQL 和连接超时折磨过的同学。1. 批量更新为什么能快这么多1.1 先算算单条提交的损耗要说清楚 Batch Update 的价值得先看单条 executeUpdate 到底干了多少事。假设一条UPDATE t_user SET status ? WHERE id ?从应用发到 MySQL完整链路包括应用把 SQL 文本拼好通过 JDBC 驱动编码成网络报文走 TCP 发到数据库。MySQL 服务端接收报文解析 SQL 文本做语法检查、权限校验。优化器生成执行计划存储引擎扫描索引定位记录更新数据并写 undo log、redo log。服务端把执行结果编码成报文再通过网络回传给应用。应用侧 JDBC 驱动解析报文封装成 ResultSet 或 update count返回给业务代码。一次请求就是一次完整的网络往返network round trip再加上服务端一整套解析、优化、执行流程。如果业务循环里执行 10000 次 update就是 10000 次网络往返。哪怕单次只要 1 毫秒光网络开销就 10 秒实际上远不止SQL 解析和事务提交的开销会叠加得更严重。我在本地做过一次不严谨的对比测试MySQL 8.0本地回环网络单条 executeUpdate 提交 10000 条更新耗时大概在 8 秒到 15 秒之间浮动。而用接下来要讲的批量方式同样数据量基本在 100 毫秒到 300 毫秒区间。差距是两个数量级根本不是调优 SQL 能追回来的。1.2 批量更新的底层原理JDBC Batch Update 的核心就两句话客户端攒批服务端少跑。addBatch()把参数预绑定到一个批处理缓冲区不立即发送。executeBatch()把缓冲区里所有的预编译 SQL 一起发送到数据库数据库顺序执行最后统一返回。clearBatch()清空缓冲区为下一批做准备。跟逐条提交相比批量模式把 N 次网络往返压缩成了第一次。SQL 文本也只解析一次配合 PreparedStatement 预编译执行计划可以复用。数据库侧虽然还是会逐条执行 SQL但省掉了大量的协议交互和解析开销这是吞吐量提升的核心来源。所以理解 Batch Update 有个关键点它并不是把多条 SQL 变成一条而是把多次发送变成一次发送把多次解析变成一次解析。回忆一下真正把多条 INSERT 合并成一条多 VALUES 的是 MySQL 驱动里的rewriteBatchedStatements参数这个后面细说。1.3 常见的误区和无效批处理很多同学说“我用了 Batch Update 但没变快”八成是踩了这几个坑批处理循环里没有判空执行最后一批数据永远没有executeBatch()只在close()的时候被丢弃。autoCommit没有手动关闭每个executeBatch()内部被当成独立事务事务提交开销还是在。批量更新 SQL 里用了动态拼接参数本质上是Statement而不是PreparedStatement没有预编译的省心效果。MySQL 驱动没有开启rewriteBatchedStatements批量插入时实际还是一对一发送只是省了部分网络。现象就是代码改成批处理了但耗时没有明显变化。2. 标准实操从逐条提交换成批量更新2.1 最基础的批量更新写法先看一个传统写法这是很多线上任务的最初版本String sql UPDATE t_user SET status ? WHERE id ?; try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { for (User user : userList) { ps.setInt(1, user.getStatus()); ps.setLong(2, user.getId()); ps.executeUpdate(); } }换成批量更新非常简单把executeUpdate()换成addBatch()循环结束后一次性executeBatch()String sql UPDATE t_user SET status ? WHERE id ?; try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement(sql)) { for (User user : userList) { ps.setInt(1, user.getStatus()); ps.setLong(2, user.getId()); ps.addBatch(); } ps.executeBatch(); }这个版本能正确工作但还远不是最优。问题在于一次性把所有数据都 addBatch如果数据量大客户端内存会被打爆PreparedStatement 内部缓冲的参数数据积压太多。executeBatch()一旦抛异常整个批的状态不好恢复。没有关闭autoCommit批内每一条 SQL 仍然处于一个隐式事务中提交开销省得不够彻底。2.2 分批提交与事务边界处理生产环境不可能把十万条数据一次塞进缓冲稳妥的做法是控制每个批次的量分批执行并清空缓冲。下面这个模式是我个人常用的public void batchUpdateInTransaction(ListUser users, int batchSize) { String sql UPDATE t_user SET status ? WHERE id ?; Connection conn null; PreparedStatement ps null; try { conn dataSource.getConnection(); conn.setAutoCommit(false); ps conn.prepareStatement(sql); int count 0; for (User user : users) { ps.setInt(1, user.getStatus()); ps.setLong(2, user.getId()); ps.addBatch(); count; if (count % batchSize 0) { ps.executeBatch(); conn.commit(); ps.clearBatch(); } } ps.executeBatch(); // 处理剩余不足一批的数据 conn.commit(); } catch (Exception e) { if (conn ! null) { try { conn.rollback(); } catch (SQLException ex) { /* log */ } } throw new RuntimeException(批量更新失败, e); } finally { if (ps ! null) { try { ps.close(); } catch (SQLException ignored) { } } if (conn ! null) { try { conn.setAutoCommit(true); conn.close(); } catch (SQLException ignored) { } } } }几点说明关闭autoCommit后executeBatch()不会自动提交。需要手动commit()失败时rollback()。这样批次内任何一条失败整个批可以回滚不会出现一半更新了、一半没更新的状态。为什么到量就 commit而不等全部执行完一是释放数据库锁资源避免大事务持锁时间过长二是降低 binlog、undo log 积压量减少对数据库实例的冲击。每次commit()前必须executeBatch()每次executeBatch()前最好clearBatch()避免下一批数据把旧数据再发一遍。顺序别搞反先执行再清否则会丢数据。2.3 处理 executeBatch 的返回结果executeBatch()返回的是一个int[]数组长度等于本次批中的 SQL 条数每个元素代表对应 SQL 影响的行数。注意两个规则如果某条 SQL 没有返回行数比如 DDL此处可能是SUCCESS_NO_INFO -2。Statement.EXECUTE_FAILED常量值是 -3表示某条执行失败。正常情况下我们只需要确认返回数组的长度和批大小一致或者遍历确认没有负数即可。不要以为每个元素都是正数就是对的某些驱动在部分成功时也会返回混合状态。一个稳妥的检查逻辑int[] results ps.executeBatch(); for (int i 0; i results.length; i) { if (results[i] Statement.EXECUTE_FAILED) { throw new SQLException(第 i 条更新失败); } }3. 驱动参数比代码更决定性能3.1 真正的大招rewriteBatchedStatementsMySQL JDBC 驱动有个很关键的参数rewriteBatchedStatements。默认值是 false。如果保持默认用 PreparedStatement 执行批量 INSERT驱动会在客户端把所有 SQL 一条一条发给服务器只是省了应用和驱动之间的交互开销并没有真正减少网络往返。把这个参数设为 true 后MySQL 驱动会把批量的 INSERT 语句合并成一条多 VALUES 的 INSERT比如-- 原始批量 INSERT INTO t_user (name, age) VALUES (?, ?) INSERT INTO t_user (name, age) VALUES (?, ?) INSERT INTO t_user (name, age) VALUES (?, ?) -- 驱动重写后 INSERT INTO t_user (name, age) VALUES (?, ?), (?, ?), (?, ?)这一下就把 N 次执行变成一次执行性能又上一个数量级。实测在插入 10 万条数据的场景下rewriteBatchedStatementstrue的耗时大约是 false 的 1/5 到 1/10。JDBC URL 拼接方式jdbc:mysql://localhost:3306/test?useUnicodetruecharacterEncodingutf8rewriteBatchedStatementstrue一个重要注意点这个参数主要针对 INSERT 生效最多对 UPDATE 的支持要看驱动版本和 SQL 写法。MySQL 驱动在部分版本会把批量 UPDATE 重写为UPDATE ... WHERE id ? OR id ? OR ...或者CASE WHEN形式但限制比较多。如果是 UPDATE 场景不要过度依赖这个参数重点还是放在控制事务边界和批次大小上。3.2 useServerPrepStmts 与预编译的取舍另一个常配的参数是useServerPrepStmts。让它为 true 时PreparedStatement 的预编译动作在 MySQL 服务端进行SQL 模板带?占位符传给服务端服务端缓存执行计划后续只是替换参数执行。好处是避免每条 SQL 都重复解析坏处是某些特殊 SQL 在服务端预编译时会报错个别场景反而变慢。推荐组合是rewriteBatchedStatementstrueuseServerPrepStmtstruecachePrepStmtstrueprepStmtCacheSize250prepStmtCacheSqlLimit2048cachePrepStmts控制客户端是否缓存预编译状态prepStmtCacheSize设置缓存条数prepStmtCacheSqlLimit限制被缓存 SQL 的最大长度。这套是针对大批量写入作业基本必配的。运行时可以通过SHOW GLOBAL STATUS LIKE Prepared_stmt_count观察服务端预编译语句数量是否有明显变化确认是否真的走了服务端预编译。3.3 batchSize 到底选多大批次大小没有绝对标准但有一个经验区间500 到 2000 是比较稳的起步值。选值的核心权衡在于批次太小网络往返次数依然多事务提交次数也多开销占比高。批次太大客户端攒的参数数据占内存单次事务过大锁持有时间长undo log 增长量大稍有问题就整个事务回滚重来代价很高。我个人的习惯是MySQL 本地网络环境选 1000跨机房高延迟环境选 500。也可以动态压测看看从 100、500、1000、2000、5000 几个档位观察数据库 CPU 和耗时曲线通常 1000 上下性价比最高。对于 PostgreSQL 或 Oracle经验值略有差异参考值也在 1000 左右。4. 生产环境踩坑实录4.1 BatchUpdateException 的完整处理批量更新时最常见的异常就是java.sql.BatchUpdateException。和普通SQLException不同这个异常会携带批处理中成功执行了一部分、剩余失败的信息。异常堆栈里能拿到getUpdateCounts()返回的就是前文提到的int[]。通过它可以看出哪几条成功了、哪几条失败了try { ps.executeBatch(); } catch (BatchUpdateException e) { int[] counts e.getUpdateCounts(); for (int i 0; i counts.length; i) { if (counts[i] Statement.EXECUTE_FAILED) { System.out.println(第 i 条失败); } } throw e; }注意一个很容易被忽略的点BatchUpdateException抛出时当前事务是否还能继续取决于驱动和数据库的实现。MySQL 下如果某条 SQL 是因为主键冲突、唯一键冲突、字段长度超限等原因失败事务仍然可以继续。但如果是因为连接断开、锁等待超时这类严重错误事务基本处于不可用状态继续执行可能只会拿到更奇怪的异常。所以处理策略要区分唯一键冲突这类业务性失败可以考虑跳过继续连接类、锁等待类系统性失败必须回滚并退出任务。我的做法是捕获异常后先看SQLState或错误码MySQL 的 1062 是唯一键冲突可以跳过1054、1146 这种是结构性问题直接回滚退出。4.2 Flink JDBC 连接器的批量写入异常Flink 项目里经常遇到 JDBC sink 批量写入报错这和底层 JDBC Batch Update 有直接关系。Flink JDBC connector 内部就是把addBatch攒起来达到sink.buffer-flush.max-rows或sink.buffer-flush.interval后执行executeBatch()。最常见的问题有两个第一个是批内数据量太大单条 SQL 的重写版本超过 MySQLmax_allowed_packet限制报错Packet for query is too large。比如sink.buffer-flush.max-rows设了 10000单行数据又带了大字段批量重写后可能直接超出 64MB 上限。解决办法是调小批次行数或者调大 MySQL 服务端和客户端两边的max_allowed_packet。第二个是 Flink checkpoint 和 JDBC 事务的冲突。当 JDBC sink 开启exactly-once时事务生命周期会被拉长如果作业重启频繁数据库侧会出现大量空闲事务和连接泄漏。现象就是Communications link failure或者Cannot commit when autocommit is enabled。解决办法是合理设置 checkpoint 间隔同时在 sink 的setCommitStrategy上选用合适的提交时机避免事务无限期持有。顺便多说一句很多 Flink 作业写 MySQL 慢根源并不在 Flink 而在 JDBC URL 参数。连接串上没配rewriteBatchedStatementstrue的话sink 虽然攒批了但驱动实际上还在逐个发送吞吐全靠数据库硬扛。4.3 MySQL 连接与批量更新的经典配置坑再整理几个连接配置和批处理配合时容易踩的坑都是线上见过的问题第一个是连接串忘记加rewriteBatchedStatements这是最普遍的现象是批量插入没提速。做数据导入的同学可以先检查连接 URL再看代码。第二个是max_allowed_packet不匹配。有些服务端配置是 64MB客户端驱动默认反而小的多。批量 SQL 被驱动重写后体积变大一旦超限会被服务端直接断开报错格式类似Communications link failure或者Packet for query is too large (xxxx xxxx)。排查时用SELECT max_allowed_packet;确认服务端值业务侧连接也显式设置成一致的值。第三个是连接池的connectionTestQuery和批处理冲突。某些连接池会定期发送SELECT 1测试连接这本身没问题。但如果批处理事务没提交就归还连接池子回收时自动 rollback业务侧以为提交了结果丢了。解决方法是保证commit()在连接归还前完成不要在不开事务的状态下调executeBatch()然后放任不管。第四个是字符集问题。批量写入报Incorrect string value时要注意characterEncodingutf8实际是 MySQL 的 utf8mb3四字节 emoji 存不进去要改成utf8mb4。这个不是批量更新特有的但批量导入时一条脏数据会中断整个批影响面比单条写入大得多。4.4 我平时用批量更新的几点体会批量更新这东西功能人人会写真正的差距在细节。我用下来最深刻的几条经验批次大小不要贪。总有人觉得一次十万条比一百个一千条要快实际上大事务对数据库的影响完全抵消了批量带来的收益。锁等待、undo 膨胀、从库延迟分分钟让一个大任务变成事故现场。批量更新必须配事务边界。一个批就是一个事务事务提交前持锁所有写操作要等到 commit 才生效。如果有人在批处理循环里开了隐式提交或者切了 autocommit性能直接打回原形。执行计划要看实际效果。要不要配useServerPrepStmts不同版本的驱动表现不一样。理性方式是压测别盲信网上某一个参数组合。观察指标包括耗时、数据库 QPS、网络流量、CPU 占用这些比猜参数靠谱得多。最后分享一个排查思路当你觉得“已经用了批量但还很慢”先把 SQL 日志和驱动版本看清楚。很多时候问题不在 Batch 本身而在连接 URL 参数、batchSize 或者 SQL 写法的某一个小细节上。代码写对了只是第一步参数配好、事务控好才是生产环境真正的批量更新。