前阵子有个同事跑来找我说他有个脚本要往 MySQL 里导 30 万条数据跑了十分钟还没完问我能不能优化一下。我让他把脚本发过来看了一眼结论很直接这不是机器不行也不是数据库不行而是插入方式完全没利用上批量插入的能力。后来我把他的逻辑改了一版同样的数据量13 秒跑完。今天就把这个优化过程完整拆开讲一遍。不管是后端开发、数据分析师还是偶尔要处理数据导入的运维只要你的工作里出现过一执行脚本就卡半天的情况这篇文章都值得读完。我会先解释为什么慢再一步步演示怎么做快最后把批量插入实践里最容易踩的坑也一并列出来方便你直接照着抄。1. 先算一笔账30 万条逐条插入到底慢在哪里很多人第一次写插入逻辑时脑子里只有最朴素的想法一条数据执行一条 INSERT循环 30 万次完事。从功能上看这没什么问题但从性能上看这个方案把能踩的低效点全踩了一遍。1.1 网络往返才是第一大头假如你的应用和数据库不在同一台机器上哪怕它们在同一个机房一次 JDBC 请求从发出到收到结果至少要经过网络传输、数据库解析、执行、结果返回这几个阶段。在实际场景里一次 executeUpdate 的完整往返时间取 0.5ms 到 2ms 都是很正常的如果数据库是跨机房或者云上实例一次 10ms 也不奇怪。就拿本地环境、每次往返 1ms 来算30 万条就是 30 万次往返光是这些网络开销就要 300 秒。如果数据库在远程这个数字直接翻到几千秒。所以你会发现逐条插入时 CPU 和数据库可能都在摸鱼但网络链路已经被打满了。1.2 数据库每次都要走一遍全流程很多人忽略的一点是在执行每条 INSERT 时数据库端并不是哦又来一条插入完事这么简单。一条 INSERT 至少包含下面的动作SQL 文本解析生成执行计划权限校验事务相关处理写 undo log写入 redo log插入聚簇索引维护二级索引必要时触发 B 树页分裂对单条数据来说这些动作的耗时都是微秒或毫秒级小到可以忽略。但放大到 30 万倍再叠加网络耗时这就不再是量变而是质变了。可以这样理解批量插入和逐条插入的区别就像是同城快递一单单发和一个货车装满一整车再发。前者看似灵活但 30 万单要 30 万辆车跑后者只需要 300 辆车每辆车装 1000 件货成本结构完全不一样。1.3 快慢的分水岭就在于减少往返这里先抛出一个贯穿全文的核心观点批量插入的一切优化手段本质上都在做同一件事——减少客户端和数据库之间的往返次数同时减少事务提交次数。有了这个视角后文的所有操作你都可以对照着看如果是把 30 万条 SQL 合并成 300 条发过去那么往返次数从 30 万降到 300如果再把每 1000 条放一个事务里提交次数也从 30 万降到 300。两头一压速度自然就上来了。2. 四个台阶把插入从十几分钟压到十几秒明白了瓶颈在哪优化步骤就清晰了。下面按优化力度从低到高给出四个台阶每个台阶都会贴上能跑的代码或配置。2.1 台阶一关掉自动提交按批提交事务先看最常见的一段代码try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement( INSERT INTO t_order (id, uid, amount, status) VALUES (?, ?, ?, ?))) { for (Order order : orders) { ps.setLong(1, order.getId()); ps.setLong(2, order.getUid()); ps.setLong(3, order.getAmount()); ps.setInt(4, order.getStatus()); ps.executeUpdate(); } }这段代码默认情况下每一条数据都是一个独立事务。也就是说30 万条数据触发了 30 万次事务提交每次提交都可能涉及 redo log 刷盘。磁盘 fsync 一次哪怕只要 2ms乘以 30 万就是 600 秒。最简单的优化是关闭自动提交每攒够 1000 条再手动 committry (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement( INSERT INTO t_order (id, uid, amount, status) VALUES (?, ?, ?, ?))) { conn.setAutoCommit(false); int batchSize 1000; int count 0; for (Order order : orders) { ps.setLong(1, order.getId()); ps.setLong(2, order.getUid()); ps.setLong(3, order.getAmount()); ps.setInt(4, order.getStatus()); ps.executeUpdate(); count; if (count % batchSize 0) { conn.commit(); } } conn.commit(); }这一步改动极小但效果立竿见影事务提交次数从 30 万次变成 300 次。实测在本地 MySQL 上能明显感觉到速度提升但网络往返依然是 30 万次所以这还不够快。2.2 台阶二JDBC 批处理加 rewriteBatchedStatements接下来要用 JDBC 的 addBatch / executeBatch 接口。这里有一个非常容易踩的误区如果你只是把 executeUpdate 换成 addBatch然后把 executeBatch 放在循环外面默认情况下 MySQL 驱动并不会把多条语句合并发送它只是循环发送 SQL但保持在同一个事务里。性能提升非常有限。要让驱动真正把批里的多条 INSERT 重写成一条多值 INSERT必须在 JDBC 连接串上显式开启一个参数jdbc:mysql://127.0.0.1:3306/test_db?useSSLfalserewriteBatchedStatementstrue开启之后的代码长这样try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement( INSERT INTO t_order (id, uid, amount, status) VALUES (?, ?, ?, ?))) { conn.setAutoCommit(false); int batchSize 1000; int count 0; for (Order order : orders) { ps.setLong(1, order.getId()); ps.setLong(2, order.getUid()); ps.setLong(3, order.getAmount()); ps.setInt(4, order.getStatus()); ps.addBatch(); count; if (count % batchSize 0) { ps.executeBatch(); conn.commit(); } } ps.executeBatch(); conn.commit(); }开启 rewriteBatchedStatements 之后驱动会把同一批 1000 条 INSERT 合并成类似下面这一条 SQL 发出去INSERT INTO t_order (id, uid, amount, status) VALUES (1, 101, 100, 0), (2, 102, 200, 0), ...这一步生效之后往返次数从 30 万次降到 300 次性能会有一个质的飞跃。2.3 台阶三手动构造多值 INSERT把主动权握在自己手里如果不想依赖驱动重写或者你用的客户端工具不支持上面那种参数也可以直接手动拼接多值 INSERT。这种方式最直观也最容易理解int batchSize 1000; for (int start 0; start orders.size(); start batchSize) { int end Math.min(start batchSize, orders.size()); StringBuilder sql new StringBuilder(); sql.append(INSERT INTO t_order (id, uid, amount, status) VALUES ); for (int i start; i end; i) { Order o orders.get(i); if (i start) { sql.append(,); } sql.append(() .append(o.getId()).append(,) .append(o.getUid()).append(,) .append(o.getAmount()).append(,) .append(o.getStatus()) .append()); } try (Statement stmt conn.createStatement()) { stmt.executeUpdate(sql.toString()); } conn.commit(); }这里要提醒一句手动拼 SQL 只适合数据来源可控、字段类型简单的批量导入场景。如果值是用户输入或者包含字符串、日期等类型强烈建议还是用 PreparedStatement 配合 rewriteBatchedStatements避免 SQL 注入和格式转义问题。另外手动拼接时要注意 SQL 长度。因为每条语句都要被数据库完整接收并解析值越多语句越长对 max_allowed_packet 参数的挑战也越大。这一点后面单独讲。2.4 四条路线实测对比我用同一台本地 MySQL 8.0、同样的 30 万行数据做了个简单对比表结构是四个字段数据全部在内存里。结果如下方案事务提交次数网络往返次数30 万条实测耗时逐条插入 自动提交30 万30 万约 11 分钟逐条插入 手动提交30030 万约 90 秒JDBC 批处理 rewriteBatchedStatements300300约 28 秒手动多值 INSERT批大小 1000300300约 13 秒注意这个数字是在我没做任何数据库参数调优的情况下测的。JDBC 批处理比手动多值 INSERT 慢一点主要是因为驱动在重写和参数绑定上还有一些额外开销但对大多数场景来说28 秒和 13 秒都已经是可以接受的水平。真正拉开差距的是你要不要走到下一步继续压榨数据库端的参数潜力。3. 批大小和数据库参数怎么调到最优到这一步你的插入速度已经比最初的逐条方案快了一个数量级。但如果你追求极致或者数据量再翻几倍还需要继续调整两个变量一是批大小二是数据库自身的写入参数。3.1 批大小不是越大越好这是我实际测试时观察到的 U 型曲线。以手动多值 INSERT 为例不同批大小对应的耗时大致是这样的批大小批次数30 万条耗时2001500约 22 秒1000300约 13 秒2000150约 12 秒500060约 12 秒2000015约 16 秒批大小超过 5000 之后耗时不再下降反而可能上升。原因主要有三个单条 SQL 太长数据库解析成本变高网络数据包也可能被拆分。单事务涉及的行太多锁持有的时间变长提交时一次性刷盘的数据量变大。一旦中途出错整批回滚的成本和重试成本都成倍增加。我自己的经验值是普通行几个 int 加一个短 varchar每行 100 字节以内用 1000 到 5000 比较稳如果字段多、含大字段比如 JSON 或 TEXT建议批大小降到 200 到 500。拿不准时直接用小样本跑一遍看耗时曲线再定。3.2 MySQL 关键参数逐项说明这里说的都是 MySQL 场景。前端 JDBC 连接串要改数据库服务端参数也要改否则会出现客户端拼了一条超大 SQL服务端直接拒收的情况。第一个参数max_allowed_packet。服务端默认值通常是 4M也就是单条 SQL 最大 4MB。如果你一条多值 INSERT 拼了 5000 行每行 500 字节那这条 SQL 约 2.5MB勉强能过如果每行超过 800 字节5000 批直接超限。调法是在 my.cnf 里设置[mysqld] max_allowed_packet 64M同时也要看客户端因为连接这个参数在客户端也会校验。修改后需要重启 MySQL并重连应用。一个通用的估算方式是批大小上限 ≈ max_allowed_packet / 单行 SQL 字节数然后留 20% 余量。第二个参数innodb_flush_log_at_trx_commit。这个参数决定事务提交时 redo log 的刷盘策略参数值行为性能与安全1每次事务提交都刷盘最安全性能最慢2每次提交写入 OS 缓存每秒刷盘性能有提升崩溃时可能丢最近 1 秒数据0由后台线程刷盘性能最快但可靠性最低批量导入属于离线场景通常可以接受短期把参数临时改成 2导入完成后再调回 1。生产环境的线上写入不要乱改这个纪律要守住。第三个参数sync_binlog。如果开启了 binlog会影响性能。批量导入时可以将 sync_binlog 临时设置为 0 或较大的 N减少磁盘同步次数。同样导入完记得还原。第四个参数连接串参数。前面提到的 rewriteBatchedStatements 在批量导入时要保持开启。有些 JDBC 驱动版本里useServerPrepStmts 和 rewriteBatchedStatements 一起开会有问题表现是 SQL 报错或重写失效。如果你开了服务端预编译发现没效果可以先关掉 useServerPrepStmts 再试。3.3 索引策略插入时保留索引还是插入后重建这是很多人忽略的一个点。InnoDB 的表是按主键聚簇的插入顺序如果和主键顺序不一致会导致页分裂产生大量随机写二级索引越多每次 INSERT 需要维护的索引结构就越多。所以批量导入的常见策略是如果是空表导入先把非聚簇索引删掉或者建表时只保留主键。导入数据前如果数据源里已经有主键顺序尽量按主键排序后再插入。数据全部导入完成后再统一执行 CREATE INDEX 创建索引。这个策略对 30 万条数据可能只快几秒但对几百万条以上的数据差距会非常明显。索引全建好的状态下插入一条要更新 5 个索引和插入一条完全不更新索引完全是两个量级。4. 实测中最容易翻车的几个场景把速度提上来之后下一关是稳定性。批量插入比逐条插入更容易遇到一些不是马上报错但一报错就是整批报废的问题。这些都是我在实际项目里踩过的列出来帮你避坑。4.1 主键或唯一键冲突让整批直接回滚批量插入的原子性很强一条多值 INSERT 里如果有任何一行的主键冲突、唯一键冲突、字段超长整条语句就会失败回滚。这可能在很大程度上浪费前面的工作。比如 30 万条数据里只有一条重复逐条插入时最多就这一条报错批量插入时这一条会让整个批次全部失败。处理方式有三种第一种先在应用层对数据源做去重从源头消灭冲突。 第二种适当缩小批大小减小单批回滚的损失。 第三种使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE。但要注意这两种写法改变了 SQL 语义也需要注意和 rewriteBatchedStatements 的兼容性最好先做小规模验证再上。4.2 max_allowed_packet 爆掉被误判成网络问题有次我导入一批带备注字段的数据每条记录接近 2KB批大小设成 5000结果执行到中途直接报 MySQL server has gone away。一开始还以为是连接被 MySQL 主动断开排查了很久才发现是单条 SQL 超过 max_allowed_packet。解决思路并不复杂先量一下单行数据在 SQL 里大概占多少字节然后估算批大小上限再结合剩余内存和事务长度选一个合适值。比如 max_allowed_packet 是 64MB单行 SQL 约 2KB那么理论最大批大小约 32000 行但建议取四分之一到一半也就是 8000 到 16000给网络传输和驱动处理留出余量。4.3 多线程真的有用吗有时反而更慢有人觉得批量插入还不够快就开线程池多线程同时插入。这个思路没错但前提是瓶颈真的在网络或客户端侧。如果客户端和数据库都在同一台机器单连接已把磁盘写满这时候再开 20 个线程插入每条线程都疯狂生成 redo log锁竞争加剧整体吞吐可能不升反降。我实践中的参考做法是网络延迟高、单条往返成本大的场景并发收益明显本地低延迟场景先用 4 到 8 个并发做小规模压测观察耗时和数据库负载再决定要不要继续加。不要想当然地认为并发数等于连接池最大数。伪代码形态大致是这样ExecutorService pool Executors.newFixedThreadPool(8); for (int shard 0; shard SHARD_COUNT; shard) { final int s shard; pool.submit(() - importShard(s)); } pool.shutdown(); pool.awaitTermination(1, TimeUnit.HOURS);每个分片内部再按批量插入的方式处理同时要保持主键有序减少行锁和页分裂冲突。4.4 跨数据库的批量插入方案不能照抄最后提醒一句不同数据库的批量插入姿势差别很大。MySQL 有多值 INSERT 和 LOAD DATAPostgreSQL 更推荐 COPYSQLite 是事务包裹批量写入Oracle 有 FORALL 批量绑定。你在 MySQL 上调优出来的批大小 5000 rewriteBatchedStatements经验换到另一个数据库很可能不适用。每到一个新环境先把一个 5 万行的小数据集跑一遍用结果说话不要迷信经验。5. 数据量再涨十倍批量插入还够用吗如果你的数据量从 30 万变成 300 万、3000 万上面这套批量 INSERT 的思路还能续命但要开始考虑更专业的导入方式。5.1 走文件通道LOAD DATA INFILE如果数据本来就在文件里MySQL 的 LOAD DATA 会比任何形式的 INSERT 都快。它的工作方式是由数据库服务端直接读取文件绕过了 SQL 解析和大部分网络协议开销。LOAD DATA LOCAL INFILE /data/orders.csv INTO TABLE t_order FIELDS TERMINATED BY , LINES TERMINATED BY \n (id, uid, amount, status);使用前需要确认两个前提服务端开启 local_infile 参数客户端连接串里也要允许本地文件读取。这个方案对于千万级数据的导入通常能达到每秒几十万行的吞吐。PostgreSQL 对应的命令是 COPY思路类似。5.2 分片导入和断点续传300 万条数据如果一次性导入失败重新跑一遍的成本很高。更好的做法是分片。按文件行号或主键范围把数据切成若干个分片每个分片独立一个任务记录每个分片最后成功提交的主键或行号。断点续传时只需要重跑失败的分片而不是整个全量导入。分片大小与批大小不同这里的分片是任务粒度可以一个分片 5 万条内部再按 1000 条一个事务分批提交。这样的双层结构既控制了单事务大小又把失败重试的粒度控制在可控范围内。5.3 根据数据形态选方案而不是根据习惯做了那么多次批量导入之后我养成了一个习惯先看数据来源再选方案。如果数据在 CSV、日志文件里优先 LOAD DATA 或 COPY。 如果数据在另一个数据库里优先评估能否用 SELECT ... INTO OUTFILE 导出文件再走文件导入。 如果数据在 MQ 中或需要业务逻辑过滤后写入那就走批量 INSERT配合批大小压测和并发控制。这个顺序基本能覆盖绝大多数场景避免明明数据在文件里却非要拼 SQL 一条条插的低效操作。最后分享一点个人体会。曾经我也执着于把批大小调到最大后来发现快慢的分水岭从来不是简单地堆大值而是减少往返、合理调度、控制事务粒度三者平衡好才算真的把批量插入玩明白了。希望这篇文章能帮你少走几条弯路。
MySQL批量插入优化：30万条数据导入从10分钟降至13秒
前阵子有个同事跑来找我说他有个脚本要往 MySQL 里导 30 万条数据跑了十分钟还没完问我能不能优化一下。我让他把脚本发过来看了一眼结论很直接这不是机器不行也不是数据库不行而是插入方式完全没利用上批量插入的能力。后来我把他的逻辑改了一版同样的数据量13 秒跑完。今天就把这个优化过程完整拆开讲一遍。不管是后端开发、数据分析师还是偶尔要处理数据导入的运维只要你的工作里出现过一执行脚本就卡半天的情况这篇文章都值得读完。我会先解释为什么慢再一步步演示怎么做快最后把批量插入实践里最容易踩的坑也一并列出来方便你直接照着抄。1. 先算一笔账30 万条逐条插入到底慢在哪里很多人第一次写插入逻辑时脑子里只有最朴素的想法一条数据执行一条 INSERT循环 30 万次完事。从功能上看这没什么问题但从性能上看这个方案把能踩的低效点全踩了一遍。1.1 网络往返才是第一大头假如你的应用和数据库不在同一台机器上哪怕它们在同一个机房一次 JDBC 请求从发出到收到结果至少要经过网络传输、数据库解析、执行、结果返回这几个阶段。在实际场景里一次 executeUpdate 的完整往返时间取 0.5ms 到 2ms 都是很正常的如果数据库是跨机房或者云上实例一次 10ms 也不奇怪。就拿本地环境、每次往返 1ms 来算30 万条就是 30 万次往返光是这些网络开销就要 300 秒。如果数据库在远程这个数字直接翻到几千秒。所以你会发现逐条插入时 CPU 和数据库可能都在摸鱼但网络链路已经被打满了。1.2 数据库每次都要走一遍全流程很多人忽略的一点是在执行每条 INSERT 时数据库端并不是哦又来一条插入完事这么简单。一条 INSERT 至少包含下面的动作SQL 文本解析生成执行计划权限校验事务相关处理写 undo log写入 redo log插入聚簇索引维护二级索引必要时触发 B 树页分裂对单条数据来说这些动作的耗时都是微秒或毫秒级小到可以忽略。但放大到 30 万倍再叠加网络耗时这就不再是量变而是质变了。可以这样理解批量插入和逐条插入的区别就像是同城快递一单单发和一个货车装满一整车再发。前者看似灵活但 30 万单要 30 万辆车跑后者只需要 300 辆车每辆车装 1000 件货成本结构完全不一样。1.3 快慢的分水岭就在于减少往返这里先抛出一个贯穿全文的核心观点批量插入的一切优化手段本质上都在做同一件事——减少客户端和数据库之间的往返次数同时减少事务提交次数。有了这个视角后文的所有操作你都可以对照着看如果是把 30 万条 SQL 合并成 300 条发过去那么往返次数从 30 万降到 300如果再把每 1000 条放一个事务里提交次数也从 30 万降到 300。两头一压速度自然就上来了。2. 四个台阶把插入从十几分钟压到十几秒明白了瓶颈在哪优化步骤就清晰了。下面按优化力度从低到高给出四个台阶每个台阶都会贴上能跑的代码或配置。2.1 台阶一关掉自动提交按批提交事务先看最常见的一段代码try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement( INSERT INTO t_order (id, uid, amount, status) VALUES (?, ?, ?, ?))) { for (Order order : orders) { ps.setLong(1, order.getId()); ps.setLong(2, order.getUid()); ps.setLong(3, order.getAmount()); ps.setInt(4, order.getStatus()); ps.executeUpdate(); } }这段代码默认情况下每一条数据都是一个独立事务。也就是说30 万条数据触发了 30 万次事务提交每次提交都可能涉及 redo log 刷盘。磁盘 fsync 一次哪怕只要 2ms乘以 30 万就是 600 秒。最简单的优化是关闭自动提交每攒够 1000 条再手动 committry (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement( INSERT INTO t_order (id, uid, amount, status) VALUES (?, ?, ?, ?))) { conn.setAutoCommit(false); int batchSize 1000; int count 0; for (Order order : orders) { ps.setLong(1, order.getId()); ps.setLong(2, order.getUid()); ps.setLong(3, order.getAmount()); ps.setInt(4, order.getStatus()); ps.executeUpdate(); count; if (count % batchSize 0) { conn.commit(); } } conn.commit(); }这一步改动极小但效果立竿见影事务提交次数从 30 万次变成 300 次。实测在本地 MySQL 上能明显感觉到速度提升但网络往返依然是 30 万次所以这还不够快。2.2 台阶二JDBC 批处理加 rewriteBatchedStatements接下来要用 JDBC 的 addBatch / executeBatch 接口。这里有一个非常容易踩的误区如果你只是把 executeUpdate 换成 addBatch然后把 executeBatch 放在循环外面默认情况下 MySQL 驱动并不会把多条语句合并发送它只是循环发送 SQL但保持在同一个事务里。性能提升非常有限。要让驱动真正把批里的多条 INSERT 重写成一条多值 INSERT必须在 JDBC 连接串上显式开启一个参数jdbc:mysql://127.0.0.1:3306/test_db?useSSLfalserewriteBatchedStatementstrue开启之后的代码长这样try (Connection conn dataSource.getConnection(); PreparedStatement ps conn.prepareStatement( INSERT INTO t_order (id, uid, amount, status) VALUES (?, ?, ?, ?))) { conn.setAutoCommit(false); int batchSize 1000; int count 0; for (Order order : orders) { ps.setLong(1, order.getId()); ps.setLong(2, order.getUid()); ps.setLong(3, order.getAmount()); ps.setInt(4, order.getStatus()); ps.addBatch(); count; if (count % batchSize 0) { ps.executeBatch(); conn.commit(); } } ps.executeBatch(); conn.commit(); }开启 rewriteBatchedStatements 之后驱动会把同一批 1000 条 INSERT 合并成类似下面这一条 SQL 发出去INSERT INTO t_order (id, uid, amount, status) VALUES (1, 101, 100, 0), (2, 102, 200, 0), ...这一步生效之后往返次数从 30 万次降到 300 次性能会有一个质的飞跃。2.3 台阶三手动构造多值 INSERT把主动权握在自己手里如果不想依赖驱动重写或者你用的客户端工具不支持上面那种参数也可以直接手动拼接多值 INSERT。这种方式最直观也最容易理解int batchSize 1000; for (int start 0; start orders.size(); start batchSize) { int end Math.min(start batchSize, orders.size()); StringBuilder sql new StringBuilder(); sql.append(INSERT INTO t_order (id, uid, amount, status) VALUES ); for (int i start; i end; i) { Order o orders.get(i); if (i start) { sql.append(,); } sql.append(() .append(o.getId()).append(,) .append(o.getUid()).append(,) .append(o.getAmount()).append(,) .append(o.getStatus()) .append()); } try (Statement stmt conn.createStatement()) { stmt.executeUpdate(sql.toString()); } conn.commit(); }这里要提醒一句手动拼 SQL 只适合数据来源可控、字段类型简单的批量导入场景。如果值是用户输入或者包含字符串、日期等类型强烈建议还是用 PreparedStatement 配合 rewriteBatchedStatements避免 SQL 注入和格式转义问题。另外手动拼接时要注意 SQL 长度。因为每条语句都要被数据库完整接收并解析值越多语句越长对 max_allowed_packet 参数的挑战也越大。这一点后面单独讲。2.4 四条路线实测对比我用同一台本地 MySQL 8.0、同样的 30 万行数据做了个简单对比表结构是四个字段数据全部在内存里。结果如下方案事务提交次数网络往返次数30 万条实测耗时逐条插入 自动提交30 万30 万约 11 分钟逐条插入 手动提交30030 万约 90 秒JDBC 批处理 rewriteBatchedStatements300300约 28 秒手动多值 INSERT批大小 1000300300约 13 秒注意这个数字是在我没做任何数据库参数调优的情况下测的。JDBC 批处理比手动多值 INSERT 慢一点主要是因为驱动在重写和参数绑定上还有一些额外开销但对大多数场景来说28 秒和 13 秒都已经是可以接受的水平。真正拉开差距的是你要不要走到下一步继续压榨数据库端的参数潜力。3. 批大小和数据库参数怎么调到最优到这一步你的插入速度已经比最初的逐条方案快了一个数量级。但如果你追求极致或者数据量再翻几倍还需要继续调整两个变量一是批大小二是数据库自身的写入参数。3.1 批大小不是越大越好这是我实际测试时观察到的 U 型曲线。以手动多值 INSERT 为例不同批大小对应的耗时大致是这样的批大小批次数30 万条耗时2001500约 22 秒1000300约 13 秒2000150约 12 秒500060约 12 秒2000015约 16 秒批大小超过 5000 之后耗时不再下降反而可能上升。原因主要有三个单条 SQL 太长数据库解析成本变高网络数据包也可能被拆分。单事务涉及的行太多锁持有的时间变长提交时一次性刷盘的数据量变大。一旦中途出错整批回滚的成本和重试成本都成倍增加。我自己的经验值是普通行几个 int 加一个短 varchar每行 100 字节以内用 1000 到 5000 比较稳如果字段多、含大字段比如 JSON 或 TEXT建议批大小降到 200 到 500。拿不准时直接用小样本跑一遍看耗时曲线再定。3.2 MySQL 关键参数逐项说明这里说的都是 MySQL 场景。前端 JDBC 连接串要改数据库服务端参数也要改否则会出现客户端拼了一条超大 SQL服务端直接拒收的情况。第一个参数max_allowed_packet。服务端默认值通常是 4M也就是单条 SQL 最大 4MB。如果你一条多值 INSERT 拼了 5000 行每行 500 字节那这条 SQL 约 2.5MB勉强能过如果每行超过 800 字节5000 批直接超限。调法是在 my.cnf 里设置[mysqld] max_allowed_packet 64M同时也要看客户端因为连接这个参数在客户端也会校验。修改后需要重启 MySQL并重连应用。一个通用的估算方式是批大小上限 ≈ max_allowed_packet / 单行 SQL 字节数然后留 20% 余量。第二个参数innodb_flush_log_at_trx_commit。这个参数决定事务提交时 redo log 的刷盘策略参数值行为性能与安全1每次事务提交都刷盘最安全性能最慢2每次提交写入 OS 缓存每秒刷盘性能有提升崩溃时可能丢最近 1 秒数据0由后台线程刷盘性能最快但可靠性最低批量导入属于离线场景通常可以接受短期把参数临时改成 2导入完成后再调回 1。生产环境的线上写入不要乱改这个纪律要守住。第三个参数sync_binlog。如果开启了 binlog会影响性能。批量导入时可以将 sync_binlog 临时设置为 0 或较大的 N减少磁盘同步次数。同样导入完记得还原。第四个参数连接串参数。前面提到的 rewriteBatchedStatements 在批量导入时要保持开启。有些 JDBC 驱动版本里useServerPrepStmts 和 rewriteBatchedStatements 一起开会有问题表现是 SQL 报错或重写失效。如果你开了服务端预编译发现没效果可以先关掉 useServerPrepStmts 再试。3.3 索引策略插入时保留索引还是插入后重建这是很多人忽略的一个点。InnoDB 的表是按主键聚簇的插入顺序如果和主键顺序不一致会导致页分裂产生大量随机写二级索引越多每次 INSERT 需要维护的索引结构就越多。所以批量导入的常见策略是如果是空表导入先把非聚簇索引删掉或者建表时只保留主键。导入数据前如果数据源里已经有主键顺序尽量按主键排序后再插入。数据全部导入完成后再统一执行 CREATE INDEX 创建索引。这个策略对 30 万条数据可能只快几秒但对几百万条以上的数据差距会非常明显。索引全建好的状态下插入一条要更新 5 个索引和插入一条完全不更新索引完全是两个量级。4. 实测中最容易翻车的几个场景把速度提上来之后下一关是稳定性。批量插入比逐条插入更容易遇到一些不是马上报错但一报错就是整批报废的问题。这些都是我在实际项目里踩过的列出来帮你避坑。4.1 主键或唯一键冲突让整批直接回滚批量插入的原子性很强一条多值 INSERT 里如果有任何一行的主键冲突、唯一键冲突、字段超长整条语句就会失败回滚。这可能在很大程度上浪费前面的工作。比如 30 万条数据里只有一条重复逐条插入时最多就这一条报错批量插入时这一条会让整个批次全部失败。处理方式有三种第一种先在应用层对数据源做去重从源头消灭冲突。 第二种适当缩小批大小减小单批回滚的损失。 第三种使用 INSERT IGNORE 或 ON DUPLICATE KEY UPDATE。但要注意这两种写法改变了 SQL 语义也需要注意和 rewriteBatchedStatements 的兼容性最好先做小规模验证再上。4.2 max_allowed_packet 爆掉被误判成网络问题有次我导入一批带备注字段的数据每条记录接近 2KB批大小设成 5000结果执行到中途直接报 MySQL server has gone away。一开始还以为是连接被 MySQL 主动断开排查了很久才发现是单条 SQL 超过 max_allowed_packet。解决思路并不复杂先量一下单行数据在 SQL 里大概占多少字节然后估算批大小上限再结合剩余内存和事务长度选一个合适值。比如 max_allowed_packet 是 64MB单行 SQL 约 2KB那么理论最大批大小约 32000 行但建议取四分之一到一半也就是 8000 到 16000给网络传输和驱动处理留出余量。4.3 多线程真的有用吗有时反而更慢有人觉得批量插入还不够快就开线程池多线程同时插入。这个思路没错但前提是瓶颈真的在网络或客户端侧。如果客户端和数据库都在同一台机器单连接已把磁盘写满这时候再开 20 个线程插入每条线程都疯狂生成 redo log锁竞争加剧整体吞吐可能不升反降。我实践中的参考做法是网络延迟高、单条往返成本大的场景并发收益明显本地低延迟场景先用 4 到 8 个并发做小规模压测观察耗时和数据库负载再决定要不要继续加。不要想当然地认为并发数等于连接池最大数。伪代码形态大致是这样ExecutorService pool Executors.newFixedThreadPool(8); for (int shard 0; shard SHARD_COUNT; shard) { final int s shard; pool.submit(() - importShard(s)); } pool.shutdown(); pool.awaitTermination(1, TimeUnit.HOURS);每个分片内部再按批量插入的方式处理同时要保持主键有序减少行锁和页分裂冲突。4.4 跨数据库的批量插入方案不能照抄最后提醒一句不同数据库的批量插入姿势差别很大。MySQL 有多值 INSERT 和 LOAD DATAPostgreSQL 更推荐 COPYSQLite 是事务包裹批量写入Oracle 有 FORALL 批量绑定。你在 MySQL 上调优出来的批大小 5000 rewriteBatchedStatements经验换到另一个数据库很可能不适用。每到一个新环境先把一个 5 万行的小数据集跑一遍用结果说话不要迷信经验。5. 数据量再涨十倍批量插入还够用吗如果你的数据量从 30 万变成 300 万、3000 万上面这套批量 INSERT 的思路还能续命但要开始考虑更专业的导入方式。5.1 走文件通道LOAD DATA INFILE如果数据本来就在文件里MySQL 的 LOAD DATA 会比任何形式的 INSERT 都快。它的工作方式是由数据库服务端直接读取文件绕过了 SQL 解析和大部分网络协议开销。LOAD DATA LOCAL INFILE /data/orders.csv INTO TABLE t_order FIELDS TERMINATED BY , LINES TERMINATED BY \n (id, uid, amount, status);使用前需要确认两个前提服务端开启 local_infile 参数客户端连接串里也要允许本地文件读取。这个方案对于千万级数据的导入通常能达到每秒几十万行的吞吐。PostgreSQL 对应的命令是 COPY思路类似。5.2 分片导入和断点续传300 万条数据如果一次性导入失败重新跑一遍的成本很高。更好的做法是分片。按文件行号或主键范围把数据切成若干个分片每个分片独立一个任务记录每个分片最后成功提交的主键或行号。断点续传时只需要重跑失败的分片而不是整个全量导入。分片大小与批大小不同这里的分片是任务粒度可以一个分片 5 万条内部再按 1000 条一个事务分批提交。这样的双层结构既控制了单事务大小又把失败重试的粒度控制在可控范围内。5.3 根据数据形态选方案而不是根据习惯做了那么多次批量导入之后我养成了一个习惯先看数据来源再选方案。如果数据在 CSV、日志文件里优先 LOAD DATA 或 COPY。 如果数据在另一个数据库里优先评估能否用 SELECT ... INTO OUTFILE 导出文件再走文件导入。 如果数据在 MQ 中或需要业务逻辑过滤后写入那就走批量 INSERT配合批大小压测和并发控制。这个顺序基本能覆盖绝大多数场景避免明明数据在文件里却非要拼 SQL 一条条插的低效操作。最后分享一点个人体会。曾经我也执着于把批大小调到最大后来发现快慢的分水岭从来不是简单地堆大值而是减少往返、合理调度、控制事务粒度三者平衡好才算真的把批量插入玩明白了。希望这篇文章能帮你少走几条弯路。
-Value函数拟合算法01-StateValue估算：TD函数逼近算法06【线性逼近器/TD-Linear算法总结与分析】)
