单条 INSERT 插个几百几千行，你根本感觉不到性能和速度有什么差别。但一旦进入大数据导入的场景，几十万、几百万甚至上千万行要往 MySQL 里塞，还是一条条地执行插入，那体验完全就是灾难。

我自己前前后后参与过不少数据迁移和离线清洗入库的活，最直观的感受是：同样一批数据，用错导入方式，可能跑几十分钟还没个尽头，而改成正确的批量写入套路，往往几分钟就能写完，数据量再大一点配合文件导入手段，几十秒也不是什么夸张的事。这篇文章我就围绕 MySQL 批量插入这件事，把批量的底层逻辑、主流实现方案、实测调优过程和常见坑位都梳理一遍，给正在做数据导入、大数据清洗落地或者单纯被慢速插入折磨的同学一个直接可以抄的参考。

1. 批量插入的整体收益解析：为什么单条插入会被大数据拖垮

先把一个最基础的问题说透：单条 INSERT 在数据量变大之后，到底慢在哪里。很多人误以为慢只是慢在网络或磁盘，其实远不止这么简单。

1.1 单条插入的隐藏开销拆解

单条 INSERT 的执行链路，表面上只是一条 SQL 提交到 MySQL，但内部至少要经历解析、权限检查、优化器生成执行计划、引擎层写入、事务提交、日志落盘这几个阶段。每一次独立提交，都意味着一次完整的同步或异步刷盘动作，InnoDB 的 redo log、binlog 都要跟着转一圈。

更麻烦的是网络往返。如果你的程序部署在应用服务器，数据库在另一台机器，每一条 INSERT 就是一次客户端到服务端的网络交互，哪怕毫秒级延迟，一万条就要十秒，一百万条就是几百上千秒，光是网络开销就能把你拖死。再加上事务机制，每条 INSERT 默认自动提交，事务的 begin 和 commit 看似轻量，积累到一定量级也是不小的开销。

所以单条插入真正的问题，不是 MySQL 写不进去，而是整个链路上有太多重复且不必要的步骤。大数据导入场景里，数据本身已经完整存在，逐条插入等于强迫数据库反复做热身运动。

1.2 批量插入适合的场景与不适合的场景

批量插入最适合这么几类场景。

• 日志类数据入库：大量只追加、基本不改写的记录，比如访问日志、操作日志、设备上报数据。
• 离线清洗后的结果落库：大数据任务跑完之后，把清洗结果从临时表或者文本文件里导回 MySQL，做后续查询分析。
• 数据迁移与备份恢复：把老库数据搬迁到新库，或者从导出文件重建数据。
• 初始化数据灌库：测试环境造数据、基准测试准备、新建业务表之后一次性铺底数据。

但批量插入也不是到处都能无脑用。比如线上高并发实时写入，每一笔请求都要立刻返回给用户，没法等你攒一批再写；再比如需要严格保持写入顺序并实时可见的场景，批量和事务合并反而会引入额外的复杂度和延迟。这种情况下，依然是单条写入但配合连接池和事务合理使用更实在。

1.3 三种主流批量写法的选型逻辑

MySQL 里常见的批量写入方案，归纳起来就是三条路。

• 多值 INSERT 语句，即一条 INSERT 带多个 VALUES。
• LOAD DATA INFILE 文件导入。
• 事务内批量提交，配合 INSERT ON DUPLICATE KEY UPDATE 做批量更新。

这三条路并不是互斥的，实际工程里经常组合使用。方案选型主要看数据来源：数据在程序内存里，用多值 INSERT 最方便；数据在文本文件里，LOAD DATA 基本是性能天花板；需要一边插入一边纠正重复数据，或者批量更新已有记录，那就在事务里处理 UPDATE 逻辑。接下来我逐个拆开讲。

2. 批量插入的三种底层实操方案：从语法到参数

方案选好之后，真正写起来并没有多难，但很多细节容易踩坑。这一部分我把三种方式的语法结构、底层原理和参数限制都讲透，方便你直接对照上手。

2.1 多值 INSERT：程序内批量插入的性价比之王

最基础的批量插入形式，就是把多条记录的 VALUES 拼在同一个 INSERT 语句里。比如一个用户表 user(id, name, age)，一次性插入三条：

INSERT INTO user(id, name, age) VALUES (1, '张三', 20), (2, '李四', 21), (3, '王五', 22);

这个写法的核心价值，是把原本 N 次网络交互和 N 次 SQL 解析压缩成 1 次。MySQL 拿到一条大 SQL，解析一次，执行器顺序遍历多行数据写入，事务也可以只提交一次。在数据总量不大、内存可以容纳的前提下，这是性价比最高的方式。

程序里用 JDBC 实现时，还有两个小技巧值得注意。第一个是开启 rewriteBatchedStatements 参数，PreparedStatement 的 addBatch 才会真正被 MySQL 重写成多值 INSERT，而不是 JDBC 驱动自己模拟循环发送单条语句。第二个是控制每批的条数，不要贪多，因为单条 SQL 的总长度受到 max_allowed_packet 限制，拼得太大容易直接报错。一般每批 500 到 2000 行是比较稳的区间，具体看单行字段长度，宁可多分几批也不要一次挑战上限。

多值 INSERT 还有一个容易被人忽略的点：如果有些字段需要调用函数处理，比如 NOW()、UUID()，拼接时要注意执行顺序。MySQL 对于同一语句内的多行记录，函数在每个上下文中是分别执行的，但你必须确保拼出来的 SQL 语义符合预期，别把函数执行结果当成固定值去理解。

2.2 LOAD DATA INFILE：大文件入库的性能天花板

如果数据已经形成了文件，比如 CSV、TSV 或者定长文本，LOAD DATA INFILE 就是近乎作弊的导入方式。它绕过了常规的 INSERT 执行链路，直接在存储引擎层面做批量加载，比拼 SQL 再快一个档次。

基本语法如下：

LOAD DATA INFILE '/data/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (id, user_id, amount, created_at);

需要注意，这里如果文件在 MySQL 服务器本地，直接写路径；如果文件在客户端机器上，要使用 LOAD DATA LOCAL INFILE，并且 MySQL 服务端和客户端都需要允许 local-infile 参数。生产环境里我建议优先用 LOCAL，因为可以直接用 CS 模式，不要求你拥有服务器文件系统的读写权限，尤其在云数据库场景非常管用。

LOAD DATA 之所以快，是因为它把数据解析和导入做成了流水线式操作，并且部分操作可以并行处理，减少了很多传统 SQL 执行层面的开销。默认情况下，它在事务方面的行为也和处理单条 INSERT 不同，如果遇到大量数据，还会涉及一系列日志机制。想让导入更快，可以在导入前临时把唯一索引和外键检查关掉：

SET unique_checks = 0; SET foreign_key_checks = 0;

导入完成之后再重新打开。这么做能明显减少索引维护和完整性检查的消耗，我在百万级数据导入时常用这个方法，收益很直接。

2.3 事务批量合并提交与批量更新

第三种方式是批量 INSERT 配合事务手动提交，比较适合既要插入又要更新的场景。举个实际例子：同步外部系统数据，目标表里可能已经有部分记录，重复的要更新，没有的要新增。

START TRANSACTION; INSERT INTO user(id, name, age) VALUES (1, '张三', 20), (2, '李四', 21) ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age); COMMIT;

放在事务里批量提交，可以减少每次 DML 的自动提交开销。更关键的是，如果导入过程中途出错，你可以统一 ROLLBACK，避免半截数据落在库里形成脏数据。

不过，事务也不是越大越好。一个事务塞几十万行数据，会导致 InnoDB 的 undo log 迅速膨胀，长时间占用大量内存，还可能拖垮 purge 线程。我在实践里常用的事务粒度是 5000 到 20000 行一批，既能保证提交频率不高，也不会让单个事务过于庞大。

这里还要提一个反直觉的点：批量更新比批量插入更容易引发锁竞争。因为 ON DUPLICATE KEY UPDATE 会对已存在的数据行加锁，如果目标表的数据量很大，几条几百行的批量更新语句在并发环境下也有可能造成锁等待。所以批量更新任务尽量安排在业务低峰期，或者控制并发线程数，不要一次性启动几十个线程同时对着同一张表猛写。

3. 100 万行数据实测：不同方案的真实耗时差距

光讲理论不跑数据，说服力始终差点意思。我在一台测试用的虚拟机里，用 MySQL 8.0 实测了一轮百万级数据导入，环境不算很强，但足够看出各方案之间的量级差距。下面把过程和结果完整记录下来。

3.1 测试环境与造数方法

测试环境大致是这样的：

• MySQL 版本：8.0.32
• 配置：4 核 CPU，8G 内存
• 存储：SSD
• 表结构：order_record(id BIGINT PRIMARY KEY, order_no VARCHAR(64), user_id INT, amount DECIMAL(10,2), status TINYINT, create_time DATETIME)

造数我用的是存储过程生成 100 万行，字段随机填充，保证数据足够分散。造数完成之后，分别用几种方案把这一百万行导入到另外一张结构完全相同的表里，统计总耗时。

3.2 实测耗时对比表

这个数据不是我为了写文章刻意挑出来的，而是同一个数据集、同一个环境下的真实观测。你可以看到，单条插入和最优方案之间差了差不多一百倍。所谓批量插入提升大数据导入效率，提升的核心就来自这里。

不同环境、不同表结构、不同磁盘性能会带来具体数字上的差异，但量级规律基本一致：LOAD DATA 最快，多值 INSERT 次之，单条插入垫底。如果你的表有大量二级索引，差距还会进一步拉大，因为逐条插入时每行都要反复更新所有索引。

3.3 影响导入速度的关键参数调优

实测之后，我把影响导入速度的关键参数整理成了几条经验。首先是 max_allowed_packet，它决定了一条 SQL 可以传递的最大包大小。如果你的多值 INSERT 拼接较大，必须把服务端和客户端的该参数都调大，否则会直接报错。

其次是 InnoDB 刷盘策略相关参数。在大批量导入场景中，如果允许一定程度的数据丢失风险，可以临时调整：

SET GLOBAL innodb_flush_log_at_trx_commit = 0; SET GLOBAL sync_binlog = 0;

默认情况下 InnoDB 每次事务提交都要把 redo log 刷到磁盘，binlog 也要同步，这是为了确保数据不丢失。但做离线导入时，数据本来就是从其他地方来的，就算中途崩溃大不了重新导入，没必要让每次提交都承受那么沉重的刷盘代价。导完数据后一定要记得把参数恢复原样，否则生产库会有数据丢失风险。

还有一个容易被忽视的参数是 innodb_buffer_pool_size。如果数据量远大于内存缓冲池，InnoDB 需要反复读写磁盘来维护索引页和缓存页。导入大批量数据之前，在机器内存允许的前提下把 buffer pool 调大一点，能让热点索引页留在内存里，减少 B+ 树频繁磁盘 IO。对百万级数据导入来说，这个调整的作用甚至比调整批量大小更直接。

关于批量大小，我也给一个相对通用的参考值。通过 JDBC 或 PyMySQL 执行多值 INSERT 时，建议从每批 500 行起步，逐步试到每批 2000 到 5000 行。超过一定程度，收益会边际递减，反而因为单条 SQL 过大、事务过长，引发内存和锁的问题。

4. 高频报错与问题排查实录：这些坑我基本都踩过

批量插入的原理和调优讲完，再来看看实际执行时最容易出现的报错和卡顿。以下这些问题，我在不同项目里基本都踩过，排查思路也都是验证过的。

4.1 max_allowed_packet 报错的正确处理方式

批量拼接 SQL 最容易撞上的错误就是 Packet for query is too large。这个报错的意思是提交的数据包超过了 MySQL 允许的最大大小。你可能会疑惑：我的单行数据明明很小，为什么会超？原因是多值 INSERT 会把所有行的数据都塞进一个包，10000 行哪怕每行只有 200 字节，整体也有 2MB 左右，而 MySQL 默认的 max_allowed_packet 往往只有 4MB 或更小，稍微多一点就触顶。

排查方法也很简单，先查看当前值：

SHOW VARIABLES LIKE 'max_allowed_packet';

然后根据数据量调大，比如改成 64M 或 128M，同时要同步修改客户端的 max_allowed_packet 配置。很多同学只改了 MySQL 服务端，客户端驱动仍然按默认值限制，结果依然报错。两边必须一致才行。

另一个思路是降低每批的条数。如果你的表字段很多，单行可能达到几 KB，那每批 200 行可能已经很危险了，这种情况下别硬撑，把批量大小往下调是更稳的办法。

4.2 Lock wait timeout 和死锁问题

批量导入并发执行时，最烦人的报错就是 Lock wait timeout exceeded。这通常是因为多个写入事务同时修改了同一行，或者在执行 INSERT ... ON DUPLICATE KEY UPDATE 时对重复键加锁产生了竞争。排查的时候先看当前有没有长时间未提交的事务：

SELECT * FROM information_schema.innodb_trx;

如果发现有事务长时间处于 RUNNING 状态，就看它的 trx_query 和 trx_started，定位到对应连接之后，确认是否需要 COMMIT 或 ROLLBACK。

从预防角度来说，批量导入并发任务一定要做好分片。比如按 user_id 的区间段切分数据，确保不同线程写不同范围的数据，尽量避免互相锁竞争。同时，单个事务不要开太大，前面提到的 5000 到 20000 行一批在实践中是比较平衡的粒度。如果表上有明确的主键或唯一键，批量 UPDATE 尽量把数据排序后再执行，降低死锁概率。

4.3 数据乱码与 SSL 连接报错

批量导入时如果客户端字符集和服务端不一致，最容易出现中文字符乱码。我先说结论：导入前统一执行一句：

SET NAMES utf8mb4;

这会把客户端、连接、结果三者的字符集都设为 utf8mb4，基本能避免绝大多数乱码问题。如果是 LOAD DATA 导入文本文件，顺便要注意文件本身的编码，最好先用 file 命令或编辑器确认文件是 UTF-8，而不是 GBK 或其他编码，否则到了库里照样是乱码。

SSL 连接报错也是高频问题，常见的是 Public Key Retrieval is not allowed，尤其在使用 JDBC 连接 MySQL 8 时经常遇到。这个报错主要出现在客户端用 caching_sha2_password 认证连接时，默认要求从服务端获取公钥，如果 JDBC 参数没设置好就会失败。常用的处理方式是在连接 URL 里加上 allowPublicKeyRetrieval=true，同时 useSSL=true。但要注意，这个参数在安全性要求高的内网环境没问题，如果走公网连接，还是建议配合证书链做完整校验，不要盲目关掉 SSL。

4.4 批量插入过程中连接被切断了

批量导入时还有一个著名问题叫 MySQL has gone away。这不一定真的是 MySQL 挂了，更常见的情况是：SQL 太复杂，执行时间太长，或者提交的数据包太大，超过了 MySQL 服务端的 wait_timeout 和 max_allowed_packet 限制，连接被服务端主动断开。

遇到这类问题，从两个地方排查。一是看服务端的 wait_timeout、interactive_timeout 和 max_allowed_packet 设置，确认你的批量操作不会接近甚至超过这些阈值。二是看数据库连接池的配置，尤其是连接空闲检查时间。如果连接池里某个连接长时间没有活动，服务端已经把它断开了，而池子里的对象没有及时失效，下一次拿这个连接去执行批量插入就会报 gone away。

连接池的正确配置方式，是让空闲连接的最大生存时间小于 MySQL 的 wait_timeout，同时配置合理的 testWhileIdle 和 validationQuery。你不一定要在每次取连接时都做验证，但至少要保持连接池里的连接和服务端状态是一致的。数据量特别大的批量导入任务，还可以考虑直接用短连接的方式，在一批任务开始前建立连接，任务完成后主动关闭，避免连接池带来的隐性问题。

5. 大数据落地场景的配合打法：清洗、分片与并发控制

批量插入很少是孤立存在的动作，尤其在大数据项目里，前面连着一堆清洗和计算逻辑，后面还要考虑目标表的结构和索引问题。最后这部分我讲讲真实项目里批量插入前后该怎么配合。

5.1 数据清洗先于导入，别把数据库当计算引擎

很多同学图省事，数据文件拿到手直接 LOAD DATA 进 MySQL，再用 SQL 慢慢 UPDATE 清洗。这个思路在数据量小的时候可以接受，一旦上了百万千万级，就会非常被动。用 MySQL 做大批量 UPDATE 清洗时，每一行都要走一遍事务和索引更新，性能和灵活性都很差。

我的习惯是先把清洗逻辑放在大数据处理链路里完成，比如用 Spark、Hive 或者简单的 Python 脚本做去重、格式转换、字段补全，最终生成已经规整好的目标文件，再批量导入 MySQL。MySQL 在这个过程里只负责存储和查询，不参与复杂计算。

这样做的另一个好处是，导入过程本身变成了一个确定性动作：文件里的格式和库里表结构强对齐，LOAD DATA 直接一把梭，导入前后不需要再处理数据修正。如果清洗不得不放在入库存之后做，比如要依赖 MySQL 里的历史数据做关联，那就尽量用批量 UPDATE 加事务分块提交，并且利用临时表做中间态，不要直接在业务主表上反复横跳。

5.2 分片并行写入与索引取舍

百万行级别的数据，单线程导入已经可以跑得不错，但到了千万行，单线程无论如何都有点力不从心。一个常见的优化策略是把数据按照某个业务键切分，用多个线程或多个进程各自维护一批数据，并行导入。切分方式通常有两种。

• 如果是文件导入，直接把源文件按行数切成多个子文件，每个线程各自 LOAD DATA 一个子文件。
• 如果数据在程序内存或外部系统里，就按 ID 范围或取模分片，比如 user_id % 8 分成 8 个分组，每个分组单独跑一个导入任务。

分片并行写入时，最怕的是多个线程同时操作同一张表且数据范围重叠。前面提到，这会产生严重的锁等待和死锁。所以分片设计目标就是让各个线程尽量写不同的数据页，减少锁竞争。

索引的取舍同样很关键。对于空表导入大量数据，正确操作是先移除或者禁用二级索引，等数据导入完成后再统一重建索引。这样能避免在逐行插入时频繁更新 B+ 树索引，整体的耗时往往能减少一半以上。如果表本身有业务在用，不能随便删索引，那就尽量选在低峰期做导入，或者改用 pt-osc 这类工具控制索引重建过程。

5.3 导入时的监控手段和容量评估

批量导入过程中，我一般会同时开三个观察窗口。

第一个是 MySQL 的慢查询和当前执行的线程状态，用来确认没有出现长时间卡住的写入操作。第二个是系统的磁盘 IO 和 CPU 使用率，因为 LOAD DATA 是很典型的 IO 密集操作，如果磁盘先到瓶颈，再怎么调批量参数也没用。第三个是 InnoDB 的锁和事务状态，数据量大时最容易在这里出问题。

容量评估方面，导入前最好先估算目标表的数据量和索引大小。注意 InnoDB 表实际占用的空间往往比逻辑数据尺寸大不少，因为主键索引本身就是聚簇索引，每行数据里都带着主键和所有字段，加上二级索引的空间，膨胀系数经常超过一倍。如果磁盘剩余空间不足，导入到一半报“table is full”而无法回滚，那才是真正的灾难。

5.4 我的一点实战心得

批量插入这门技术在文档里看很简单，但实际用起来到处是细节。我这里补几个自己总结的小原则。

第一个原则：先想清楚失败重试方案，再动手导入。批量导入的数据量越大，越不能指望一次成功。我一般会给每个导入批次设计唯一批次号，或者先用临时表做过渡，导入验证无误后再把它切换成正式表。这样即使中途出错，清理和重跑的成本都很低。

第二个原则：在大数据导入环节，别把一切资源都打满。有人喜欢一次开几十个线程满负荷导入，觉得越快越好。实际上，当数据库机器 CPU 和 IO 被打满时，不仅仅是 MySQL 响应变慢，同一个机器上的其他服务也会跟着遭殃。如果你的数据库实例还承担在线业务，更要控制导入并发和 IO 负载。

第三个原则：把导入任务的结果完整记录下来。不同批次的数据量、耗时、失败行数这些信息，看似不起眼，但在排查问题和后续优化时非常有用。我在很多项目里养成了习惯，每次批量导入跑完自动写一条日志，久而久之就能摸清自己系统在不同数据量级的真实上限。

批量插入本身不复杂，真正的门槛在于理解数据量大了之后数据库内部发生了什么。把网络往返、事务提交、索引维护、日志刷盘这些开销想清楚，再对照着选择合适的方案和参数，大数据导入的效率就掌握在自己手里了。