上周接手了一个内部数据平台的批量写入优化任务业务方很着急说每天要往 MySQL 里灌 60 万条订单流水程序跑了一个多小时还没结束直接把后续的对账和报表任务全堵住了。我看了一眼代码就是典型的 for 循环里一条一条 insert当时就明白问题出在哪了。批量插入这事看起来像是框架自带的一个 API要么用 JDBC batch要么拼一条多值 SQL把它当成系统设计问题来做的团队其实不多。但等数据量从几千涨到几十万、几百万之后你很快会发现SQL 的拼法、连接参数、事务边界、索引策略、主键生成方式、失败之后怎么补偿每一层都可能让写入性能差出两三个数量级。这篇文章就围绕“批量插入”这个系统设计话题把我这次从 30 分钟调到 3 秒的全过程、中间踩过的坑、最后沉淀下来的参数估算方法一次性讲清楚。适合后端开发、数据开发、架构师以及所有正在被“数据写不进去”困扰的人参考。1. 批量插入为什么是个系统设计问题1.1 单条插入到底慢在哪里先把最基础的问题拆开一条一条 insert 为什么慢很多人以为慢在数据库“执行”这条 SQL其实在数据量小的阶段执行本身只要零点几毫秒真正的开销全在看不见的地方。第一层是网络往返。应用服务器和数据库之间每执行一条 insert就要经历一次客户端发请求、服务端响应确认的完整网络往返RTT。在局域网里 RTT 大概是 0.1 到 0.5 毫秒跨机房或者走公网可能就是 1 到 10 毫秒。60 万条记录就是 60 万次 RTT哪怕每次只花 0.5 毫秒光网络等待就有 300 秒。批量插入的核心收益之一就是把 60 万次往返压缩成几千次。第二层是 SQL 解析和执行计划生成。MySQL 拿到一条 insert 后要做词法分析、语法分析、语义检查还要生成执行计划。单条 insert 的执行计划生成成本虽然不高但乘上 60 万就非常可观。更麻烦的是应用侧的 ORM 框架还要做对象映射、参数绑定、SQL 拼接这个开销在循环里同样会被无限放大。第三层是事务和日志。InnoDB 为了保证崩溃恢复能力每条 insert 都要写 redo log如果 binlog 开着还要同步写 binlog。日志写入和刷盘策略直接决定了单条写入的延迟下限。这里有个容易被忽略的点大批量数据如果放在一个事务里redo log 可以积攒到事务提交时统一刷盘但如果是一条条自动提交每一条都要触发一次刷盘那性能基本就废了。第四层是索引维护。如果表上有二级索引每插入一行都要更新所有索引的 B 树数据页满了还要做页分裂。行数越多索引树越高单条索引维护成本也会缓慢上升。批量插入通过“多行一起提交”虽然索引维护的总工作量不会少但可以减少索引页的频繁换入换出尤其是数据按主键有序插入时能显著减少随机 IO。用寄快递来类比单条 insert 就像你每次只寄一个包裹都要骑电动车跑一趟快递站路上花的时间比打包还多批量插入则是把所有包裹装进一辆卡车一次拉到分拨中心运输开销被摊薄了。真正省掉的大头从来不是“打包”本身而是“跑腿次数”。1.2 批量插入的收益边界在哪里那是不是批量越大越好不是。批量插入的性能模型大概是这样的总耗时 ≈ 网络往返次数 × RTT 数据总量 / 传输带宽 解析次数 × 单次解析耗时 数据页写入耗时 日志刷盘耗时这里能通过“批量”优化的是网络往返次数、解析次数和日志刷盘次数但数据总量和最终写入数据页的工作量是省不掉的顶多通过有序主键让写入更连续降低随机 IO 的放大系数。所以批量插入有一个收益拐点批量大小从 1 涨到几百性能几乎线性提升从几百涨到几千收益开始放缓再往上走反而会因为单条 SQL 报文过大、内存占用过高、锁范围过大带来负面效果。我在实操里见过不少团队把 10 万条数据拼成一条 SQL结果不是卡死就是内存溢出最后还要拆回去。还有一个边界容易被忽略批量插入并不会减少 InnoDB 的索引维护总量只是把随机 IO 变成更连续的 IO。如果你的数据主键是无序的比如 UUID 字符串批量插入的收益会打折扣尤其是当表数据量超过 buffer pool 容纳范围后随机插到主键索引的不同数据页会引发大量磁盘读。后面第 4 章我会专门说主键策略。2. 方案选型不同数据库和框架下的批量插入路径2.1 MySQL 下最容易见效的三条路如果后端用的是 MySQL最常见的批量插入方案有三个适用场景完全不同。第一条路是多值 INSERT也就是一条 SQL 插多行。语法上就是INSERT INTO t (a, b) VALUES (1, 2), (3, 4), ...。这条路最适合数据量可控比如几千行以内的同步导入因为它完全不需要改框架只需要把参数拼成一个大的 VALUES 列表。但它有一个硬性限制SQL 报文大小受max_allowed_packet约束同时 PreparedStatement 的占位符数量也有限制MySQL 默认的占位符上限是 65535 个。假设每行 5 个字段那最多大概就是 1.3 万行一条 SQL超过就会抛异常。所以多值 INSERT 适合中小批量的快速落地不适合百万级大导入。第二条路是 JDBC Batch。通过addBatch()攒一批再executeBatch()配合连接串参数rewriteBatchedStatementstrue驱动会把多条 insert 自动重写成多值 INSERT 的形式发给服务端。这里有个非常关键的细节如果不开rewriteBatchedStatementsJDBC 驱动表面上是在“批量提交”实际还是一行一行把 SQL 发到数据库性能提升非常有限。这个参数在 MySQL 5.1.13 以上的驱动版本里才支持使用 MariaDB 驱动的参数名还不太一样需要留意。第三条路是LOAD DATA LOCAL INFILE。这条路适合一次性大导入比如冷数据归档、初始化数据迁移。它走的是服务端文件解析通道绕过了完整的 SQL 解析和日志链路性能比多值 INSERT 还要快一个档次。但要注意生产环境通常要白名单权限才允许启用LOCAL关键字而且导入过程中如果遇到唯一键冲突默认行为是报错中断需要提前确认好数据质量或者使用INSERT IGNORE语义的变体。2.2 PostgreSQL 下为什么 COPY 是首选如果你的技术栈是 PostgreSQL那批量插入的优先级排序完全不一样。PG 里单条 INSERT 怎么写都不会太快因为它有一条专属的快速路径COPY协议。COPY t FROM STDIN走的是服务端原生文本/二进制导入协议数据按列流式送入绕开了 SQL 解析和部分事务开销。实测下来COPY 导入同样规模的数据通常比一条条 INSERT 快 5 到 10 倍。如果是临时导入还可以先把目标表改成UNLOGGED TABLE导入完成后再改回LOGGED代价是导入期间该表崩溃后无法恢复但对临时中间表来说可以接受。JDBC 里使用 COPY 通常需要引入专门的工具类或者直接操作CopyManager。Spring 的JdbcTemplate对 COPY 支持不友好不建议硬套直接用 PG JDBC 的原生 API 更稳健。另外还要注意COPY 在数据冲突和错误处理上没有 INSERT 那么灵活一旦中间出错整批都会被标记为失败所以大数据量导入前先要做数据校验。2.3 框架层怎么选JDBC Batch、MyBatis Batch 与异步攒批框架层面最常见的三个选择各有适用场景。JDBC Batch 是操控粒度最细的方案。手写 JDBC 可以精确控制每批多少条、什么时候提交事务、是否设置 savepoint适合自定义导入引擎。缺点是样板代码多而且容易犯连接不关闭、事务边界混乱的错。MyBatis Batch 通过SqlSessionTemplate配合ExecutorType.BATCH实现使用成本很低代码侵入小。但这里有坑MyBatis 的 Batch 执行器会缓存一批 SQL只有当flushStatements()被调用时才会真实发送而且默认ExecutorType.SIMPLE是不会触发批量效果的。如果项目里大量使用 MyBatis很多同学以为换一个执行器类型就能快实际是需要配合分页循环flush的。异步攒批则是把上面的方案继续抽象一层。用一个队列接收插入请求后台线程按固定大小或固定时间窗口封装成批写入。这个方案适合削峰填谷比如请求洪峰来了不必每笔都立刻落库先攒着写。代价是数据不是强实时以及进程崩溃时会有丢失风险需要结合业务场景权衡。下面我把几个方案的对比列个表方便直接参考方案适用场景单批规模建议主要限制多值 INSERT同步中小批量导入几百到几千行max_allowed_packet、占位符上限JDBC Batch rewriteBatchedStatements通用批量写尤其是百万级以下500~2000 行需要开启连接串参数LOAD DATA INFILE冷数据导入、初始化迁移一次导入几十万以上权限、冲突处理策略需要提前设计PostgreSQL COPYPG 大数据量导入一次导入百万级出错整批失败需前置校验MyBatis BATCH已有 MyBatis 栈的批量写每次 flush 500~2000 行必须显式 flush3. 实操从 30 分钟到 3 秒的批量插入调优全记录3.1 先写一个最朴素的单条插入基线为了看清楚每一轮改动带来的收益我先把当时的场景固化下来。模拟项目代号叫“某订单流水归档系统”表结构大概是订单号、用户标识、金额、状态、创建时间等一共 8 个字段其中主键是基于时间戳 序号生成的字符串另有一个二级索引建立在创建时间上。业务侧原来的代码是典型的 MyBatis XML 循环插入伪代码如下for (OrderRecord order : orderList) { orderMapper.insert(order); }测试数据固定为 60 万条。跑完一轮记录耗时34 分 27 秒平均每秒只写 290 条左右。这个基线很正常因为每一条 insert 都要经历 SQL 解析、权限检查、日志刷盘、索引更新、网络往返而且连接串里没有配置任何批量参数。第一条调优经验就是如果代码长这个样先不要怀疑数据库配置问题就是“没有批量”。3.2 第一轮调优先开 JDBC 批量参数再说别的第一轮改动我不动 SQL只把连接串改成下面这样jdbc:mysql://host:3306/db?useUnicodetruecharacterEncodingutf8rewriteBatchedStatementstrueuseSSLfalseserverTimezoneAsia/Shanghai关键就是rewriteBatchedStatementstrue。然后把代码从“循环单条 insert”改成 JDBC 原生 Batchtry (PreparedStatement ps conn.prepareStatement( INSERT INTO t_order (order_id, user_id, amount, status, create_time) VALUES (?, ?, ?, ?, ?))) { for (OrderRecord order : orderList) { ps.setString(1, order.getOrderId()); ps.setLong(2, order.getUserId()); ps.setBigDecimal(3, order.getAmount()); // ... ps.addBatch(); if (i % 2000 0) { ps.executeBatch(); // 每 2000 条发送一次 } } ps.executeBatch(); }这一轮跑下来60 万条耗时降到了4 分 52 秒大约是原来的七分之一。为什么一个参数能有这么大差异因为不开rewriteBatchedStatements时驱动收到addBatch()只是把 SQL 记在内存里真实发送时仍是一条一条交给服务端执行。开了之后驱动会把连续的 insert 重写成一条多值 SQL一次网络请求带几千行服务端解析次数从几十万次骤降到几百次网络往返和解析成本同时被砍掉。注意如果你的 MySQL 驱动版本比较旧rewriteBatchedStatements对INSERT ... ON DUPLICATE KEY UPDATE的重写支持并不好甚至可能失效。升级驱动或者单独压测确认参数是否真的生效这一步不要省。3.3 第二轮调优事务边界和批量大小必须一起调第一轮调完后我看了一眼监控发现数据库的 redo 日志写入频率虽然降下来了但 undo log 增长得非常快而且整个 60 万条是在一个大事务里提交的。事务太大有两个隐患一是单事务占用的 undo 空间和锁时间都会被放大二是如果中途失败回滚要扫大量 undo 页恢复时间比插入还长。第二轮改动很简单把“全部数据一个事务”改成“每批 2000 行一个事务”。实现方式是在executeBatch()之后执行conn.commit()原来外层包着的整个事务提交去掉。conn.setAutoCommit(false); int batchSize 2000; for (int i 0; i orderList.size(); i) { // ... 设置参数 addBatch if (i % batchSize batchSize - 1) { ps.executeBatch(); conn.commit(); } } ps.executeBatch(); conn.commit();这轮跑完耗时从 4 分 52 秒降到2 分 38 秒。收益来自两个地方一是 undo log 膨胀得到控制数据库不需要花大量时间维护一个超大事务的快照链二是每批提交后相关数据页和锁就能释放后续批次的插入不再被大事务的资源占用拖累。这里也顺便验证了批量大小对耗时的影响。我在同一环境里分别测了 500、1000、2000、5000、10000 行/批结果如下批量大小60万条总耗时单批事务耗时估算是否出现异常情况5002分51秒约 85ms无10002分42秒约 85ms无20002分38秒约 80ms无50002分36秒约 75ms偶发 lock wait100002分33秒约 70ms主从延迟明显增大结论是 2000 行左右是当前环境最稳的甜点。再往大调收益已经非常有限但锁冲突和主从延迟的副作用开始出现所以 2000 成了最终候选值。3.4 第三轮调优索引、主键策略和批量更新第二阶段结束后单看插入已经没有明显的性能瓶颈了但我发现还有两个系统设计上的优化空间主键生成策略和二级索引维护。当时表主键是字符串格式类似YYYYMMDDHHMMSS 随机数在批量插入时这些主键是无序的。InnoDB 的数据页是按主键聚簇的无序插入意味着每插入一行极有可能落在不同的数据页上频繁触发缓存 miss 和磁盘读。这轮改动是把主键策略改成“有序 ID”具体做法是把原表主键调整为自增整数列业务上的订单号改为普通唯一索引。对已存在的归档表来说迁移成本不小但在一张写入量极大的表上这个投入是值得的。实测只改主键为零数字段、保持业务代码不变的情况下总耗时从 2 分 38 秒进一步降到1 分 50 秒。二级索引是另一个点。如果导入任务是全量重建某张表最合适的方式是先把二级索引删掉导入完数据再统一创建索引。因为批量插入过程中每写一行都要维护二级索引对 60 万条数据来说这个维护成本占了总耗时接近三分之一。先导数据再建索引等于把索引构建从“逐行增量维护”变成“一次全量构建”时间效率高很多。实测这样操作后纯插入部分耗时降到11 秒加上最后重建索引的 4 秒总耗时 15 秒以内。再配合阶段性提交最终线上版本的完成时间是3 秒左右纯批量写入部分加索引重建整体在 10 秒上下。和最初的 34 分 27 秒相比基本上是一个数量级以上的优化。4. 批量大小怎么定参数计算与压测方法4.1 理论上批量大小应该怎么估算不要一上来就拍脑袋定批量大小先用公式圈定一个合理范围再通过压测收敛。第一层约束是 SQL 报文大小。MySQL 服务端的max_allowed_packet默认通常是 64MB单条 SQL 报文不能超过这个值。客户端驱动也有对应的maxAllowedPacket参数。假设每行数据约 300 字节2000 行就是 600KB完全在安全范围。但如果每行有大数据字段比如包含一个 10KB 的备注2000 行就直接到 20MB 了需要相应调小批量。可以用这个公式粗算推荐批量行数 单批次SQL报文上限建议 max_allowed_packet 的 1/10 1/5 / 单行平均字节数除以 1/10 到 1/5 是留出 SQL 头、参数绑定、网络封包的余量避免单条请求过大触发服务端拒绝或客户端内存紧张。第二层约束是事务和锁。批量插入不是单纯把数据“怼”进去每个事务持有锁的时间与事务内修改行数正相关。批次太小事务提交次数太多网络和日志开销没省够批次太大锁等待和 undo 膨胀风险上升。经验值一般在 1000 到 3000 行之间需要结合表结构、索引数量、数据库实例配置压测确定。第三层约束是主从同步延迟。如果 binlog 复制是异步的一个超大的事务在主库提交后从库需要一次性回放大量日志延迟会瞬间飙高。对线上高可用架构不友好的批量插入宁可多拆几批提交。4.2 动态确定批次的压测方法压测方法比公式更关键。我在这次优化里用了一个很简单的二分逼近法固定 60 万条数据不变分别测 500、1000、2000、5000、10000 行/批记录每档总耗时和数据库的关键指标锁等待、主从延迟、redo 日志速率。取耗时曲线开始变得平缓的那个点再取它的二分之一的档位做确认基本就是甜点区间。压测时要注意几个变量必须固定第一是表数据量空表和 500 万行的表批量插入表现完全不同第二是并发环境压测机最好独占数据库或者镜像一个同等配置的实例第三是连接池压测中用到的连接数要和线上一致因为连接太少会占不满 IO连接太多会出现锁竞争。如果你不想弄一堆脚本可以直接在 MySQL 里看两个指标SHOW ENGINE INNODB STATUS里的事务锁等待次数以及SHOW SLAVE STATUS或性能库里的复制延迟表。锁等待频繁说明批次偏大、并发偏高复制延迟大说明单事务过大或 binlog 写入压力过高。两个指标都不报警时批次大小基本是对的。4.3 从批量插入到批量更新一个容易忽略的边界有时候业务需要的不是“纯插入”而是“有则更新、无则插入”。这类需求很常见导入历史数据时同一订单号可能重复推送。MySQL 里一般用INSERT ... ON DUPLICATE KEY UPDATE实现。批量语义下就是把多值行和更新子句拼在一起遇到唯一键/主键冲突就更新指定列否则插入新行。这个语法在批量模式下非常高效但要注意批量更新的锁范围比纯插入大得多更容易死锁尤其是多个事务并发导入同一批数据时。MySQL 8.0.20 及以上版本开始废弃VALUES()函数读取当前插入行的旧值官方建议改用别名语法比如INSERT INTO t (id, v) VALUES (1, 2) AS new ON DUPLICATE KEY UPDATE v new.v。如果一张表同时有多个唯一键冲突判断按第一个触发冲突的唯一键处理语义不直观导入前要看清楚表结构。我处理过的一个线上问题是两个导入任务并发跑同一张表都用了批量 upsert结果每天固定时间出现死锁告警。排查下来就是两边插入的主键顺序不同导致 InnoDB 加锁顺序不一致互相等锁。解法是让所有导入任务先按主键排序再批量写死锁立刻消失。5. 常见问题与排查技巧实录5.1 死锁与锁等待批量插入也会打架批量插入最容易出现的锁问题有两种。第一种是批量大小过大单事务持锁时间过长导致其他事务的插入请求阻塞。表现形式是Lock wait timeout exceeded一般可以通过减小批次、缩短事务时间解决。第二种是并发批次之间加锁顺序不一致。比如任务 A 按主键 1、2、3 插入任务 B 同时按 3、2、1 插入两个事务互相持有对方要的锁最终死锁。这种问题从 SQL 层面看不出毛病必须在批量写入前做一次排序或者将同一批数据的主键区间隔离给单一任务处理。排查死锁最有效的手段是打开 MySQL 的死锁日志innodb_print_all_deadlocks1之后SHOW ENGINE INNODB STATUS能看到详细的锁等待关系。按照日志里显示的索引名和锁模式基本能反推出是哪两批数据在打架。5.2 主从延迟与 binlog 压力批量插入对主库的压力主要体现在 binlog 和 redo log对从库的压力主要体现在单事务过大导致的回放延迟。我见过一个事故某团队为了追求导入速度把 100 万条数据全放一个事务里提交主库自己的耗时只有几秒但从库回放这个超大事务花了整整 12 分钟期间线上读请求全部走了主库主库连接被打满。解决办法分两步。第一步限制单事务行数不让任何事务超过 10000 行第二步在主从延迟超过阈值时暂停批量写入等延迟回落后再继续。实现上可以在导入程序里定时查一下从库延迟指标超过设定值就 sleep 一段时间。这套机制加上后我再也没因为批量导入在大晚上被叫起来过。5.3 批量写一半失败了怎么办批量插入失败最怕的是“不知道哪些成功了哪些没成功”。如果所有数据在一个事务里失败就是全部回滚重来就是但很多时候为了性能已经把数据拆成多个批次多次提交那就会出现部分批次成功、后续批次失败的情况。我的做法是给导入任务加一张任务进度表字段包括批次号、起始主键、结束主键、状态。每提交一批就更新一次状态。任务中断后从状态表里找到最后一个成功批次从下一条记录继续。前提是数据本身要幂等或者目标表有唯一键可以防重。如果不想引入额外的表也可以在事务内设置SAVEPOINT只回滚失败的批try { ps.executeBatch(); conn.commit(); } catch (BatchUpdateException e) { conn.rollback(savepoint); // 记录失败批进入降级流程 }注意BatchUpdateException会携带getUpdateCounts()数组可以借此判断具体哪些行失败了对于数据校验类任务非常有用。5.4 内存溢出和导入超时问题批量插入的另一个高发问题是客户端内存溢出。你想着一次查 50 万条数据再批量插入但 ORM 一次性把 50 万条记录全部加载到 JVM 或者 Python 进程里堆直接被打爆。正确的姿势是用游标流式读取。MySQL JDBC 里可以设置useCursorFetchtrue配合fetchSize让结果集不一次性拉全而是按需从服务端分批拉取MyBatis 里对应配置fetchSize如果用的是 PythonSSCursor是同样的思路。读取一批写入一批内存占用始终可控。超时问题则相反批量插入的 SQL 执行时间比单条长如果数据库驱动或者中间件的写入超时设得太短大一点的批次会被误判为超时。我建议客户端超时设置比最慢批次预期耗时放宽 3 倍以上宁可让超时报错真的发生也不要被无谓的判断打断。6. 一点个人经验收尾批量插入这个题目技术点其实不难难的是把它放到整个系统里想清楚连接参数开没开、事务边界合不合理、主键是不是有序、索引能不能先建后删、写失败之后怎么补偿。每一步单看都很基础但组合在一起就是架构师和开发者的分水岭。我个人的体会是最忌讳两件事。一是把 batch 当万能药改完rewriteBatchedStatements就觉得完事了不去看事务和索引二是一味追大 batch忽略了主从延迟和锁冲突。如果只让我留一条建议那就是动手之前先看一眼连接串有没有开批量参数再看一眼表的主键生成方式这两点能帮你避开 80% 的写入性能问题。剩下的 20%就靠压测和日志排查看实效了。