我接手这个优化任务的时候第一反应是查代码结果被一条批量插入给气笑了。线上一个数据同步接口用MyBatis往MySQL里灌数据单次插入一千两百条接口响应时间稳定在4分50秒左右偶尔还飙到6分钟。业务方天天催说这个同步任务再这么跑下去下游报表就别出数了。当时我盯着日志里那行Preparing: insert into table_x (col1, col2, ...) values (?, ?, ...), (?, ?, ...), ...后面的问号数量多到屏幕都放不下。我把这条SQL复制出来数了一下一千两百条数据一条SQL里拼了三万多个参数占位符。这种写法MySQL解析起来不慢才怪。5分钟到3秒不是靠某一个魔法参数实现的而是靠三个层面的优化叠加出来的。这篇文章我把完整的排查链路和优化步骤拆开讲每一步都给到可以直接抄作业的配置和代码。1. 5分钟卡在哪先把慢查询的账算明白动手优化之前我先做了两件最基础的事打开MySQL的慢查询日志确认这条大SQL的真实执行耗时然后用SHOW PROFILE和EXPLAIN把执行链路拆开。结果很有意思数据库执行这条插入SQL本身只用了不到200毫秒真正的耗时大头在应用层——MyBatis在循环里逐条解析XML、拼装SQL、设置参数以及JDBC驱动一条条执行时产生的网络往返。1.1 逐条Insert的隐性开销任何一条SQL从应用发到数据库都要经过应用组装 → 网络传输 → 数据库解析 → 执行 → 返回结果这条链路。如果是一千两百条独立的小SQL逐条发送这条链路就要走一千两百遍。即使每次网络往返只要2毫秒光是网络延迟就吃掉了2.4秒再加上每条SQL的解析、事务日志写入、行锁竞争5分钟就这么堆出来了。我用lsof和tcpdump简单抓了一下包发现在优化前程序确实是在一条一条地发Insert语句。JDBC驱动默认的rewriteBatchedStatements是false即使你在MyBatis的Mapper里写了batch模式驱动也会把批处理拆成单条执行这是很多团队踩过却没意识到的坑。1.2 大SQL还有一个隐藏的地雷undo log和redo log把一千两百条数据拼成一条大SQL虽然执行时MySQL解析SQL的成本还算能接受但InnoDB在一条事务里写入大量行时会产生大量的redo log刷盘同时undo log要记录旧版本用于回滚。如果事务提交间隔太短、或者每次批量太大导致单个事务的undo log膨胀数据库会出现明显的抖动。这个场景我在优化前的压测里亲眼见过CPU使用率波动很厉害偶尔还触发磁盘IO瓶颈。换句话说优化方向不是去死磕某一条SQL的写法而是要降低真正引起耗时的那部分开销——逐条执行的次数和单事务的数据量。想明白这一点后面的优化方案就有了清晰的取舍依据。2. 优化一把JDBC驱动最强的批量能力打开这一层是整个优化里ROI最高的一步改一个连接参数就行但绝大多数项目根本不知道它的存在。2.1 rewriteBatchedStatements这个参数MySQL的JDBC驱动有一个默认关闭的参数叫rewriteBatchedStatements。它的作用是当开启时驱动会把连续的、结构相同的批量Insert语句重写成一条多VALUES的复合Insert语句发送到数据库。没开启的时候JDBC的executeBatch()实际上是拆成一条条单语句逐条发送的——这一点坑了无数人。我是在数据库连接串上做的改动运行环境是Spring Boot项目数据库连接池用的Druid。修改后的jdbc-url是这样的jdbc.urljdbc:mysql://127.0.0.1:3306/app_db?useUnicodetruecharacterEncodingutf8useSSLfalseserverTimezoneAsia/ShanghairewriteBatchedStatementstrue就这一个参数配合代码里真正调用批量API实测单批次插入一千两百条的耗时直接从4分50秒降到了35秒左右。这个参数对Insert、Update、Delete的批处理都有效建议只要使用JDBC批量操作的场景都无脑开启。它的副作用几乎可以忽略唯一需要注意的是开启后MyBatis会把多个Insert拼接成一条大SQLSQL文本会变长但只要不超过max_allowed_packet就没事。2.2 调大max_allowed_packet别让SQL被拦腰截断拼成一条大SQL后第一个暴露的问题就是包体超限。MySQL默认的max_allowed_packet是4MB我这边一千二百行、每行20多个字段拼完的SQL文本已经超过4MB了直接报PacketTooBigException。我当时的处理方式# 在MySQL实例上执行session级别立即生效 SET GLOBAL max_allowed_packet 64*1024*1024; SET SESSION max_allowed_packet 64*1024*1024;然后在配置文件my.cnf里持久化[mysqld] max_allowed_packet64M设置完要重启数据库实例才彻底生效生产环境重启前记得先评估连接池和正在跑的任务。这一步不做rewriteBatchedStatements等于白开。我建议按数据量的十倍预估SQL文本大小宁可给足也别让线上偶发报错。3. 优化二放弃XML里拼foreach换成真正的Batch API当时代码里的Mapper方法是用foreach标签在XML里拼接values这种方式本身就是批量插入性能杀手。它的问题在于MyBatis做动态SQL解析时会为每一条记录执行一次OGNL表达式求值然后把所有记录拼接成一整条SQL。数据量一大解析和内存copy的开销会滚雪球。3.1 为什么foreach拼SQL在小数据量下好用大数据量下必炸如果你的业务是一次插入几十条foreach这种方式完全没问题代码简单、直观一眼看懂。但到了几百上千条的规模它有三个硬伤XML解析开销MyBatis需要为每条记录解析foreach集合表达式生成对应的参数映射一千条记录就有上千次解析。参数对象占用所有插入的值都会放到一个巨大的参数数组中占用的堆内存不小触发GC时还会STW卡顿。SQL文本过长拼出来的SQL可能是几十万字符网络传输和数据库解析都不轻松。所以我的第二层优化是把数据插入改成彻底走SqlSessionTemplate的批量模式绕开XML拼接。核心代码如下Autowired private SqlSessionTemplate sqlSessionTemplate; public void batchInsertWithBatchMode(ListMyData dataList, int batchSize) { // 关键点用BATCH执行器这个执行器会把多条SQL累积起来最后统一提交 SqlSession session sqlSessionTemplate.getSqlSessionFactory() .openSession(ExecutorType.BATCH, false); try { MyDataMapper mapper session.getMapper(MyDataMapper.class); for (int i 0; i dataList.size(); i) { mapper.insert(dataList.get(i)); if (i % batchSize 0 i 0) { // 每攒够一批就刷一次避免内存里积压太多未提交SQL session.flushStatements(); } } session.commit(); } catch (Exception e) { session.rollback(); throw e; } finally { session.close(); } }这里有个细节必须解释SqlSessionTemplate默认替我们管理SqlSession但当我们要使用ExecutorType.BATCH时不能直接拿template的Mapper调insert那样不会生效。必须openSession(ExecutorType.BATCH)手动开一个session拿到BATCH执行器。BATCH模式下Mapper里的单条insert方法调用并不会真正执行SQL而是被JDBC驱动累积到批量队列里最后由flushStatements()或commit()一次性发送。这一步配合第一节的rewriteBatchedStatements才真正发挥批量威力。3.2 BATCH模式的正确姿势批大小和刷新时机用过BATCH模式的同学应该知道一个坑flushStatements()太频繁批量就失去意义完全不flush内存又扛不住。我这边压测后确定了一个经验值——每一批500到1000条之间是收益最高的区间。我用的是每500条flush一次完整处理一千两百条也就flush两三次效果很平稳。刚开始我尝试每100条flush一次耗时反而涨了一点因为flush太频繁JDBC驱动的批量队列还没攒够就被强制发送了。批大小、flush频率、事务边界这三角关系建议大家在测试环境用自己的数据量压一遍别直接抄网上的数字。改完这一层单批一千两百条数据的插入耗时从35秒降到了7秒左右。接下来还有最后一步把最后一层时间挤出来。4. 优化三拆事务、控批次、让数据库少做点事到了这一步单次请求的SQL数量和体积都已经优化得差不多了但还有一个始终拖着后腿的地方一条事务塞下太多数据InnoDB在刷redo log和做行锁管理时的成本会成倍上升。我把数据量进一步分级超过一千条就拆分批次每个批次一个独立事务避免单事务数据量过大。4.1 分批插入和事务边界的权衡这里不是简简单单把List切个片就完事。事务边界的选取必须结合业务语义。比如我这个同步任务允许单批次失败重试那么每个批次独立提交就是合适的。如果一个业务要求全部成功或全部失败那就不应该拆事务而是在事务内分批flush。我当时给每个批次定为800条块与块之间独立提交最终代码如下Transactional(rollbackFor Exception.class) public void syncBatch(ListMyData fullList) { int batchSize 800; for (int i 0; i fullList.size(); i batchSize) { ListMyData batchList fullList.subList(i, Math.min(i batchSize, fullList.size())); batchInsertWithBatchMode(batchList, batchList.size()); } }但注意一个细节外层方法加了Transactional会开启一个大事务内层每个批次的独立提交就不生效了真正生效的是一个超大事务。所以要么外层不加事务要么把事务边界直接下放到批次方法内部。我在最终版本里把事务注解移除了改成在batchInsertWithBatchMode方法内部自行commit或rollback这样每个批次的事务边界才清晰可控。为什么批次定为800而不是更大的5000我实测过三组数据列在下面批次大小总耗时事务日志情况我的结论2009秒事务提交频繁redo log小刷多次太碎网络和日志刷写开销分摊不低8003.2秒事务提交适中整体平滑当前场景最优50003.1秒单事务太大undo膨胀有抖动风险收益不明显且有隐患批次越大批量SQL数量越少理论上DB执行越快但单个事务的undo log、行锁竞争会增加。800这个数值在当前表结构和数据量下收益最高再往上边际收益几乎为零风险反而上升。这个结论不一定适合所有场景但思路是可复用的在生产数据量下画出耗时曲线用数据说话而不是凭感觉定批大小。4.2 小心连接池的maxActive限制还有一个小坑大批量数据操作时连接池状态会变得很微妙。我当时的Druid连接池配置了maxActive50批量插入的单连接占用时间并不长但连续大批量插入时如果其他接口也在占用连接连接池很容易被打满表现为wait_time异常升高。后来我把Druid的连接泄漏检测打开并让批量任务的连接使用时间控制在1秒以内情况立刻缓解。给一段可以参考的连接池核心配置spring: datasource: druid: initial-size: 5 min-idle: 5 max-active: 50 max-wait: 60000 validation-query: SELECT 1 test-while-idle: true time-between-eviction-runs-millis: 60000如果批量任务很多建议给批量任务单独的DataSource避免跟在线接口抢连接这是大型项目的常见做法。5. 最终验证3秒是从哪一秒开始算的优化做完了得给出让业务方信服的对比数据。我在同样的环境、同样的机器配置、同样的数据量下做了五轮前后对比平均结果如下优化阶段单批1000条耗时说明原始foreach逐条约290秒大部分耗时在网络往返和逐条SQL执行开启rewriteBatchedStatements约35秒驱动把批量语句重写为多VALUES大SQL改走BATCH模式flush约7秒去掉了XML拼接和OGNL解析开销设定合理批大小事务边界约3秒数据库日志写入和锁竞争显著下降有一个容易被忽略的细节3秒这个数字一开始我认为是纯批量插入的耗时但后来看一下监控发现有一小部分是日志打印和对象转换。也就是说如果你的代码里还在循环里打log、做一堆字段convert这些时间也算进了接口响应里。优化SQL的同时把循环里无谓的logger.info和重复的Bean复制也处理一下耗时能再往下降。我当时顺手做了一件事把数据对象的字段映射从反射拷贝改成了手动getter/setter赋值对象转换的耗时又少了大约400毫秒。反射在单条数据上看不出毛病但套上几千上万条数据开销会成倍放大。6. 优化之后还需要注意的几个稳定性问题优化不是改完配置就结束了。上线前我对几个隐藏问题做了充分验证这里也分享出来。6.1 数据一致性与自增主键回填用BATCH模式执行插入时自增主键的回填行为跟普通单条插入不一样。如果你的业务需要插入后马上拿到自增ID使用BATCH模式会拿不到或者拿到的是错误的ID。这个场景下建议继续用foreach拼接的方式或者插入完成后重新按业务唯一键查询ID。我当时这个同步任务不需要回填主键所以BATCH模式完全没问题。如果你的代码里有useGeneratedKeys切换到BATCH模式之前一定要在上线前测一下这个坑踩到的话影响面非常大。6.2 大事务回滚风险批量插入最大的风险不是慢而是做到一半失败后回滚。优化前5分钟的插入如果在最后一步失败MySQL回滚的耗时跟正向插入差不多也是接近5分钟。这5分钟里事务锁住的那些行其他线程全部阻塞。我见过一个生产事故一个批量回滚拖垮了整个订单服务。所以不要以为批量任务慢才需要优化批量任务更应该控事务大小就是为了让回滚时间可控。800条一个事务单事务回滚时间实测在50毫秒左右业务完全无感。这一点我认为比缩短插入耗时本身更有价值。6.3 动静分离批量任务单独线程池优化完以后我把这个同步接口从业务主线程里拆了出来扔到了独立的线程池里交给一个调度任务每天固定时间跑。原因很简单批量操作即使优化到3秒也还是会占用数据库连接和CPU资源如果用户点击一下就要等这3秒体验很差。把核心插入逻辑异步化主线程立刻返回再配合一个独立的批量任务线程池彻底避免批量任务拖垮在线接口。线程池的参数是根据生产环境压测定的ThreadPoolExecutor batchPool new ThreadPoolExecutor( 2, // 核心线程 4, // 最大线程 30L, TimeUnit.SECONDS, // 空闲回收 new ArrayBlockingQueue(100), // 有界队列 new ThreadPoolExecutor.CallerRunsPolicy() // 满了之后用调用线程执行防止拒绝 );批处理任务一般IO密集度很高线程数不需要开太多2到4个足够。如果开8个反而会把数据库连接和磁盘IO打满影响其他在线服务。这块建议根据实际压测来调整不要盲目调大。6.4 排查工具怎么选整个排查过程我用的工具其实非常基础MySQL的SHOW FULL PROCESSLIST抓慢SQL用自带的information_schema看事务和锁等待。定位网络往返问题用的是Wireshark简单抓包看MySQL报文。这些工具不需要专门装什么平台但能让你很清楚看到瓶颈在网络层还是数据库执行层。这里也提醒一句遇到批量插入慢的问题先判断瓶颈是网络往返次数太多还是单条SQL执行太慢判断错了优化方向就全偏了。7. 这一趟优化下来我沉淀的几个经验整个优化做完我不只是拿到了一组好看的数字更重要的是总结出了几条复用价值很高的经验以后再遇到类似的批量插入慢的问题基本都能按这个思路快速定位。先区分是应用层慢还是数据库层慢。这个看似废话但太多人一上来就调MySQL参数最后发现SQL根本没执行几次。我当时先用SHOW FULL PROCESSLIST观察了20秒发现SQL一条条轮番出现、数据库没闲着但每一条都极短——这是典型的应用层逐条发送问题。先看现象再定位别急着改配置。rewriteBatchedStatements是性价比最高的开关。我用了这么多年MySQL一直到最近才认真研究这个参数之前一直以为JDBC的executeBatch就是真正的批量。这个参数默认关闭很多教程里提都没提但这个开关一打开批量插入性能是天壤之别。建议所有用JDBC做批处理的工程先检查这一个参数再谈其他优化。批大小需要压测后确定不是越大越好。很多人都以为分批时批越大越快实际结果从800到5000几乎没有差别但风险却大了很多。对大多数业务场景来说500到1000是一个兼顾性能和稳定的甜点区间。盲目追求一锅端反而是很多生产事故的温床。我自己现在做批量任务设计时默认的三条准则是每条SQL都不要超过一千行每个事务都不要超过一千条记录每个批量队列flush前都要确认对端可以接受大包。想明白这三点批量插入其实没有那么神秘做到3秒也并不是什么极限操作无非就是把每一层该省的开销都省掉罢了。