企业官网建设流程全解析

上周接手了一个内部数据平台的批量写入优化任务，业务方很着急，说每天要往 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()，配合连接串参数rewriteBatchedStatements=true，驱动会把多条 insert 自动重写成多值 INSERT 的形式发给服务端。这里有个非常关键的细节：如果不开rewriteBatchedStatements，JDBC 驱动表面上是在“批量提交”，实际还是一行一行把 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的。

异步攒批则是把上面的方案继续抽象一层。用一个队列接收插入请求，后台线程按固定大小或固定时间窗口封装成批写入。这个方案适合削峰填谷，比如请求洪峰来了不必每笔都立刻落库，先攒着写。代价是数据不是强实时，以及进程崩溃时会有丢失风险，需要结合业务场景权衡。

下面我把几个方案的对比列个表，方便直接参考：

3. 实操：从 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?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&useSSL=false&serverTimezone=Asia/Shanghai

关键就是rewriteBatchedStatements=true。然后把代码从“循环单条 insert”改成 JDBC 原生 Batch：

try (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 行/批，结果如下：

结论是 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_deadlocks=1之后，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 里可以设置useCursorFetch=true配合fetchSize，让结果集不一次性拉全，而是按需从服务端分批拉取；MyBatis 里对应配置fetchSize；如果用的是 Python，SSCursor是同样的思路。读取一批，写入一批，内存占用始终可控。

超时问题则相反：批量插入的 SQL 执行时间比单条长，如果数据库驱动或者中间件的写入超时设得太短，大一点的批次会被误判为超时。我建议客户端超时设置比最慢批次预期耗时放宽 3 倍以上，宁可让超时报错真的发生，也不要被无谓的判断打断。

6. 一点个人经验收尾

批量插入这个题目，技术点其实不难，难的是把它放到整个系统里想清楚：连接参数开没开、事务边界合不合理、主键是不是有序、索引能不能先建后删、写失败之后怎么补偿。每一步单看都很基础，但组合在一起就是架构师和开发者的分水岭。

我个人的体会是，最忌讳两件事。一是把 batch 当万能药，改完rewriteBatchedStatements就觉得完事了，不去看事务和索引；二是一味追大 batch，忽略了主从延迟和锁冲突。如果只让我留一条建议，那就是动手之前先看一眼连接串有没有开批量参数，再看一眼表的主键生成方式，这两点能帮你避开 80% 的写入性能问题。剩下的 20%，就靠压测和日志排查看实效了。