干过数据迁移或者往线上库灌过数据的人应该都体会过这种心情日志一行一行地刷INSERT一条一条地进数据库吞吐量却低得让人怀疑网络是不是被掐了。几百万条记录用单条插入硬跑几个小时甚至一整晚都不一定能跑完。日志归档、第三方系统数据同步、爬虫落库、报表初始化随便一个场景都能牵出大批量写入的需求。批量插入就是解决这个问题的第一把钥匙也是我从“一条条insert去怼”到“几十万行数据几分钟入库”转变中收获最大的一块。这篇内容适合所有需要把大量数据快速导进MySQL的人不管你是做数据同步、离线批处理还是日常给测试环境造数据都能找到可以直接抄的做法。我会从底层原理讲起逐步把单条INSERT改造成标准的批量插入再讲清楚JDBC、MyBatis、LOAD DATA这几条不同路线的取舍最后把这些年踩过的坑和排查方法一并列出来。1. 先搞清楚单条插入到底慢在哪里1.1 慢的不是SQL本身而是连接往返与事务提交很多人觉得单条插入慢是机器性能不行但大部分场景下真不是。一条INSERT语句的执行时间由三块组成客户端到服务端的网络传输、服务端执行写入操作、写入完成后的事务提交。绝大多数情况下真正拖后腿的是平时容易忽略掉的“连接往返”。拿业务系统最常见的配置来说每插入一条数据客户端要发一次请求MySQL执行完要回一个响应。如果代码里还开着autocommit那每条INSERT后面还会跟着一次隐式提交。一次提交背后涉及binlog写入、redo log刷盘、内存与磁盘同步等一系列动作这些动作的执行成本远比单纯往内存里写一行要高得多。一次往返加上一次提交也许只要几毫秒但乘以几十万、几百万之后差距就变成了分钟和小时。这里可以打个比方。单条插入就像你去快递站寄包裹每寄一个件都要重新排队、填单、付款一天寄100个件光排队就耗掉大半天。批量插入则是把100个包裹打包成一个托盘一次交接搞定搬运次数和人耗都降下来了。1.2 批量插入的底层原理批量插入能提速核心就是两件事减少网络往返次数减少事务提交次数。先看网络往返。最典型的写法是一条INSERT语句里带多组VALUES。原来是这样INSERT INTO t (a, b) VALUES (1, 2); INSERT INTO t (a, b) VALUES (3, 4); INSERT INTO t (a, b) VALUES (5, 6);改成这样INSERT INTO t (a, b) VALUES (1, 2), (3, 4), (5, 6);原本三次请求变成一次网络开销直接砍掉三分之二。这是所有批量插入方案最基础的原理JDBC的rewriteBatchedStatements、MyBatis拼SQL本质都是在朝这个方向靠。再看事务提交。单条插入且开着autocommit时每一行都有一次提交批量插入时你可以把几千行放进一个事务里统一提交。提交次数从N次变成N除以单批行数次binlog刷盘、redo log同步这些重量级操作也随之大幅减少。这一点在SSD和普通机械盘上的差异很明显磁盘IO本来就是批量导入的主要瓶颈之一把提交次数压下来等于直接把IO压力降了一个台阶。1.3 适用场景与边界批量插入不是万能的。它适合的场景是离线批量导入、数据迁移、日志归档这类写入密集、并发竞争少、不需要实时感知的任务。不适合的场景是用户下单、支付扣款这类在线交易操作——这种场景追求的是单条事务的快速响应你把不同用户的请求攒成一个大事务只会增加锁的持有时间还可能拖垮别人。另外批量插入的数据量也不是越大越好。批次太大时单条SQL的解析时间变长网络包变大事务提交时的索引维护和锁竞争也会加剧。后面我会专门讲批次大小的取值逻辑这里先记住一个原则批量插入优化的是整体吞吐不是单条延迟它的目标是规模化写入而不是把每一条都变得更快。2. 五条可行的批量插入路线2.1 原生多VALUES语句最简单也最直观如果不想改代码结构只是想快速验证批量插入的威力直接在命令行或者Navicat里拼一条多VALUES语句就行INSERT INTO order_item (order_id, goods_id, quantity, price, created_at) VALUES (1001, 2001, 2, 15.50, 2025-01-01 10:00:00), (1001, 2002, 1, 88.00, 2025-01-01 10:00:01), (1002, 2003, 3, 12.90, 2025-01-01 10:00:02);这条路线的优点是省掉了中间层SQL执行计划由MySQL直接优化解析成本低。缺点是手写维护不方便尤其是表字段多、数据量大时SQL会变得非常长读起来也费劲而且一不小心就容易漏逗号、错括号。这里有三个坑要记住。第一个是max_allowed_packet它限制单个SQL包的最大大小默认值是67108864字节也就是64MB。如果一次拼几十万行总包体超过了限制MySQL会直接报“Packet too large”。第二个是单批行数不要贪多500行一组是比较稳的起步值后续根据字段数量和单行大小再调整。第三个是字段里如果含有单引号或者特殊字符拼SQL时要做好转义否则很容易出现语法错误或者被注入。2.2 JDBC的addBatch代码批量写入的标准姿势如果你是Java开发直接操作JDBC那PreparedStatement的addBatch是标准做法。核心思路是先用预编译SQL固定语句结构再用addBatch()把参数攒起来最后由executeBatch()一次性发给服务端。这里要重点说一个非常容易被忽略的参数rewriteBatchedStatements。MySQL JDBC驱动默认情况下有个反直觉的行为executeBatch()并不会把多条语句自动合并成一条多VALUES语句而是逐条发送只是把网络往返做了部分优化。真正想让它合并成大VALUES语句必须在连接串上显式加上rewriteBatchedStatementstrue。不开启这个参数你写了addBatch性能提升也会远低于预期。我见过太多人在这上面栽跟头最后发现批量和非批量几乎没有区别。还有个细节是批次的flush时机。addBatch只是把参数缓存在客户端内存里并不是立刻发出去只有executeBatch调用之后才会真正传输。所以在循环里要“攒够一批就执行一次”不能疯狂addBatch不管内存否则参数列表越长PreparedStatement内部的开销越大这也是我后面要单独讲批次大小的原因。2.3 MyBatis的foreach批量企业项目中最常见的写法Java后端项目里直接写JDBC的不多大多数在用MyBatis。MyBatis批量插入最常见的做法是用动态SQL的foreach标签把多条VALUES拼起来insert idbatchInsert parameterTypelist INSERT INTO order_item (order_id, goods_id, quantity, price, created_at) VALUES foreach collectionlist itemitem separator, (#{item.orderId}, #{item.goodsId}, #{item.quantity}, #{item.price}, #{item.createdAt}) /foreach /insertforeach拼SQL这条路线的本质是把拼接工作交给MyBatis最终到达MySQL的仍然是一条多VALUES语句所以它的性能上限和原生SQL是一样的。需要注意的副作用是SQL解析开销当字段多、批量大时一条SQL里可能有几千个占位符MySQL解析SQL的时间会明显上升网络包也会变大。所以MyBatis这条路同样要控制批次大小我一般建议单批在几百行的量级不要盲目冲到上万行。另一种方式是把SqlSession的ExecutorType改成BATCH走的是JDBC的addBatch机制和foreach拼SQL是两条不同的技术路线。写法上是先开一个BATCH模式的SqlSession再连续执行单条insert最后统一flushSqlSession sqlSession sqlSessionFactory.openSession(ExecutorType.BATCH); try { OrderItemMapper mapper sqlSession.getMapper(OrderItemMapper.class); for (int i 0; i list.size(); i) { mapper.insert(list.get(i)); if ((i 1) % 500 0) { sqlSession.flushStatements(); } } sqlSession.commit(); } finally { sqlSession.close(); }ExecutorType.BATCH的好处是内存占用稳定不会因为拼接一个大SQL而导致GC压力缺点是如果混合了其他复杂的查询操作执行顺序和返回主键的行为都容易出问题用的时候要先把相关Mapper的insert逻辑单独抽出来。2.4 LOAD DATA INFILE服务端直读文件物理外挂如果你的数据已经存在文件里或者可以通过程序批量生成数据文件那LOAD DATA INFILE几乎是MySQL导入性能的天花板。它是MySQL服务端直接读取文件、解析并写入数据数据不经过应用层也没有逐条的网络与映射开销通常比多VALUES语句还快一个数量级。LOAD DATA LOCAL INFILE /data/order_item.csv INTO TABLE order_item CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (order_id, goods_id, quantity, price, created_at);这里要区分两个概念。带LOCAL的写法文件在客户端本地由客户端把文件内容传输到服务端不带LOCAL的写法文件必须放在MySQL服务器本机且要确保MySQL进程有权限读取该路径。两条路线本质都是让MySQL服务端做解析区别只在于文件内容的传输位置。使用LOCAL时也要注意安全边界它允许客户端上传文件给服务端读取如果是从不可信来源执行SQL要评估文件读取和内容解析的风险。生产环境里我一般优先把文件放到服务器固定目录用不带LOCAL的方式导入路径写死权限收紧。2.5 INSERT ... SELECT表间搬运的利器如果数据本来就在另一张表里不需要经过应用层计算直接用INSERT ... SELECT把数据从源表搬到目标表。整个过程发生在MySQL内部数据不经过客户端网络IO为零最适合分表归档、临时表加工、表结构迁移这类场景。INSERT INTO order_item_2025 (order_id, goods_id, quantity, price, created_at) SELECT order_id, goods_id, quantity, price, created_at FROM order_item WHERE created_at 2025-01-01 AND created_at 2025-02-01;这条路需要注意锁。INSERT ... SELECT在执行过程中会锁定源表与目标表的相关记录和区间如果目标表上有高并发的写入一定要评估锁影响尽量在业务低峰期执行或者退一步改成分批DELETE加INSERT的方式拆小事务。数据量特别大的时候也可以先用SELECT把结果集落成临时文件再走LOAD DATA两条路配合起来用。3. 实战改造从单条INSERT到批量INSERT的完整落地3.1 场景与原始方案我拿一个实际做过的需求举例。有一张订单明细表order_item字段包括order_id、goods_id、quantity、price、created_at一共十来列。数据来源是第三方系统的CSV导出文件一共50万行需要导入MySQL。最开始的做法是Java程序逐条解析CSV然后一条一条执行INSERT。在默认配置、机房内网网络正常的条件下50万行跑完大约需要25到35分钟。如果网络延迟稍微高一点或者目标表上已经存在二级索引和外键时间会成倍增加。改成多VALUES批量插入每批500行之后整个导入时间直接降到1到2分钟。这个数量级的差距就是批量插入最直观的收益。3.2 JDBC批量插入的完整代码先看Java JDBC的标准写法public void batchInsert(ListOrderItem list) throws SQLException { String url jdbc:mysql://127.0.0.1:3306/demo ?useUnicodetruecharacterEncodingutf8 rewriteBatchedStatementstrue; try (Connection conn DriverManager.getConnection(url, root, password)) { // 关闭自动提交把多次executeBatch合并成一个事务 conn.setAutoCommit(false); String sql INSERT INTO order_item (order_id, goods_id, quantity, price, created_at) VALUES (?, ?, ?, ?, ?); try (PreparedStatement ps conn.prepareStatement(sql)) { for (int i 0; i list.size(); i) { OrderItem item list.get(i); ps.setLong(1, item.getOrderId()); ps.setLong(2, item.getGoodsId()); ps.setInt(3, item.getQuantity()); ps.setBigDecimal(4, item.getPrice()); ps.setTimestamp(5, item.getCreatedAt()); ps.addBatch(); // 每500条执行一次清空缓冲区 if ((i 1) % 500 0) { ps.executeBatch(); ps.clearBatch(); } } // 处理最后不足500条的剩余数据 ps.executeBatch(); // 提交整个事务 conn.commit(); } catch (SQLException e) { conn.rollback(); throw e; } } }这段代码里有几个细节值得展开。第一个是url里必须带rewriteBatchedStatementstrue。前面已经提到MySQL JDBC驱动默认不会把executeBatch合并成多VALUES语句加了之后驱动才会在服务端可见的SQL层面把同一条语句的多次执行重写为VALUES (...), (...), (...)。这是Java批量插入能否真正提速的关键开关。第二个是setAutoCommit(false)。如果保持默认的自动提交每执行一次executeBatchMySQL都会做一次隐式提交批次越小提交越频繁性能损失越大。关闭自动提交后你可以控制多个批次在同一个事务里提交也可以一个批次一个事务灵活性高很多。第三个是每500条执行一次executeBatch。这个数字不是拍脑袋定的既是为了让单批SQL的总大小控制在合理区间也是为了避免PreparedStatement内部参数缓冲区占用过多内存。数据量很大的时候内存管理比SQL本身更值得关注。3.3 批次大小到底怎么定批次大小是最容易被忽略也最值得认真调的一个参数。如果你把批次设得太小比如10条一批网络往返依然密集优化效果不明显设得太大比如一次10万行内存占用飙升SQL解析时间长事务提交时索引维护和锁竞争都会加剧严重时还会触发死锁。我结合几个项目经验给一个参考值单行数据在1KB以内时500到2000行一批比较稳妥单行数据较大、带有大字段Text、Blob、JSON时200到500行一批更安全。最直接的判断依据是估算单批总包体大小让单批数据控制在1MB到4MB之间这个区间和max_allowed_packet的默认值也能很好地兼容。事务和批次的关系也在这里说清楚。如果你希望每批独立提交一批就是一个事务出错时只需要回滚一批影响范围小但整体提交次数依然偏多。如果你希望所有批次一次性提交整个导入就是一个大事务速度最快但任何一条数据出错都要全部回滚。我的建议是折中以批为单位作为一个事务或者每几批提交一次既能控制回滚范围又不会因为频繁提交损失太多性能。3.4 服务端参数什么样的配置值得调代码层面的优化做完之后如果想再压榨一轮性能就需要关注MySQL服务端的几个参数了。我把常用参数列成一张表方便对照。参数默认值作用什么时候调max_allowed_packet6710886464MB限制单次SQL包最大值多VALUES批次过大报Packet too large时调大innodb_buffer_pool_size128MB左右InnoDB缓存池大小目标表数据量远大于内存时调大给索引和写入留空间innodb_flush_log_at_trx_commit1控制redo log刷盘频率导入场景可临时改为2或0提升速度但要承担丢失最近日志的风险sync_binlog1控制binlog刷盘策略批量导入时可临时调整为较大值或0但要评估一致性需求innodb_autoinc_lock_mode2控制自增锁的加锁方式批量连续插入时模式2交错并发更好这几个参数需要特别提醒innodb_flush_log_at_trx_commit默认值是1含义是每次事务提交都要把redo log刷到磁盘这是保证崩溃后不丢最近数据的关键。做批量导入时把它临时改成2意思是每秒刷一次盘写入速度会明显提升但MySQL如果在这1秒内宕机最多可能丢失1秒的事务日志。生产环境要不要改取决于你对数据丢失风险的容忍度。线上核心库我很少动这个参数离线导入专用库或者临时搭建的数据同步环境才敢改。sync_binlog同理。默认1表示每次事务提交都同步binlog到磁盘改成较大的值可以降低刷盘频率、提升导入速度但主库宕机时binlog可能丢更多数据从库延迟也可能被拉大。这里的原则是测试和离线导入随便试线上谨慎再谨慎。4. 常见问题与坑点排查实录4.1 “Packet too large”报错这是我遇到最多的批量插入问题。现象是执行一条大SQL时MySQL直接报“Got a packet bigger than max_allowed_packet bytes”。排查思路第一步是确认当前值SHOW VARIABLES LIKE max_allowed_packet;如果当前值小于你要执行的SQL大小就需要调大。可以全局临时设置也可以写进my.cnf永久生效。不过我更建议换个思路与其一味调大max_allowed_packet不如把批次拆小。单批1MB到4MB的数据绝大多数默认配置都能扛住没必要把单条SQL拼到几十MB。毕竟max_allowed_packet调得再大网络传输和解析成本也不会消失拆小批次反而更可控。4.2 用了addBatch速度却没什么提升现象是代码里写了addBatch和executeBatch满怀期待地跑了一遍发现性能提升微乎其微。第一个要查的就是连接串里有没有rewriteBatchedStatementstrue。这是MySQL JDBC驱动合并SQL的前提很多教程没提很多人就是吃了这个亏。第二个要查的是PreparedStatement是不是真的复用了有些代码在循环里反复创建新的PreparedStatement批次根本没攒起来等于白写。第三个要查的是事务如果每次executeBatch都commit一次批量插入的效果会被频繁提交抵消掉一大半。这三个点按顺序排查下来基本能覆盖绝大多数“批量无效”的情况。4.3 大批量插入导致死锁如果目标表有自增主键大批量插入时死锁的概率会变高尤其是多线程并发导入的场景。这里涉及到InnoDB的自增锁机制。历史上InnoDB对自增列加锁的方式有三种模式innodb_autoinc_lock_mode取0、1、2。模式0和1在批量插入时会持有表级自增锁直到语句结束并发插入时容易互相等待模式2使用交错interleaved方式允许并发分配自增值大幅降低锁竞争也是MySQL 8.0的默认值。遇到死锁时除了确认自增锁模式还要看事务边界是否过大。单事务写入行数越多锁覆盖的间隙越大死锁概率越高。把单事务行数拆小是比调参数更常见、更安全的解法。另外多线程并行导入时尽量让每个线程写入不同的主键区间也可以有效降低互相等待的概率。4.4 唯一键或主键冲突整批全部回滚往一张已存在的表里批量导入数据时只要有一条记录撞上唯一键或主键整批事务就会回滚。50万行的导入跑到最后发现失败再从头再跑心态很容易崩。我的处理方案是导入前先按唯一键去重或者在SQL层面用INSERT ... ON DUPLICATE KEY UPDATE让冲突行走更新而不是报错。如果业务上允许丢弃重复数据也可以用INSERT IGNORE直接把冲突行忽略掉。这三种做法的语义不一样按业务需求选不要乱用。还有个细节是如果使用ON DUPLICATE KEY UPDATE批量插入的SQL长度会比普通INSERT更长要注意前面说的max_allowed_packet限制。4.5 导入速度越跑越慢大批量导入时越到后面越慢这种情况通常和索引维护有关。每次插入一行InnoDB不仅要写数据行还要同步更新所有二级索引数据量越大、索引越多维护成本越高。做法是如果导入的是一个全新的表可以先建表但先不加二级索引等数据全部导入完成后再统一ALTER TABLE创建索引。这样构建出来的索引结构是整体构建的效率远高于逐行插入时维护索引。导入中途如果发现实在慢也可以先把已有索引临时删掉导入完成后再补回来。这个操作要评估窗口期风险索引删除后查询会变慢不适合有在线查询的表。4.6 常见报错速查表报错或现象常见原因解决方向Packet too large单批SQL超过max_allowed_packet拆小批次或调大参数addBatch无明显提速未开启rewriteBatchedStatements连接串加入该参数Deadlock found自增锁竞争或事务过大检查innodb_autoinc_lock_mode、拆小事务Duplicate entry唯一键/主键冲突预去重、用ON DUPLICATE KEY UPDATE或INSERT IGNORE越导越慢二级索引维护成本高先导数据后建索引Lock wait timeout批量事务持有锁时间过长缩小批次、错峰执行5. 进阶超大批量场景的几条经验5.1 多线程并行导入当单线程批次优化已经到了瓶颈可以考虑多线程。思路是把数据按主键范围或文件行号分段每个线程处理一段各自建立数据库连接各自维护自己的事务边界。并行度越高总吞吐越高但要注意两点连接池大小要匹配不要一个连接池被几十个线程抢成排队目标表的锁竞争会加剧分段时尽量避免不同线程写入相同的主键区间否则死锁概率直线上升。并行导入的最优线程数没有固定值我在机器上一般从4个线程起步逐步加到8、16观察MySQL的TPS曲线找到拐点就停。5.2 临时关闭外键检查和唯一性检查如果导入涉及多张有关联的表外键检查会在每次写入时验证关联关系带来额外开销。在确认数据质量没问题的情况下可以在导入会话内临时关闭SET FOREIGN_KEY_CHECKS 0; SET UNIQUE_CHECKS 0;导入完成后记得重新打开。这里的“会话内”三个字很关键因为这两个变量是会话级的不会影响其他连接。如果中途发生错误需要重导记得先把检查打开否则脏数据可能悄悄混进去。5.3 终极提速mysqlimport与处理过的CSV如果连多VALUES都觉得不够快可以直接用mysqlimport它本质上是LOAD DATA命令的命令行封装mysqlimport --local --fields-terminated-by, \ --lines-terminated-by\n --ignore-lines1 \ -u root -ppassword demo /data/order_item.csv配合一个合理的CSV生成流程mysqlimport基本能跑满单机的写入能力。我遇到过50万行数据用mysqlimport在几十秒内完成导入的场景这是应用层代码很难企及的。用它的前提是CSV文件的字段顺序、分隔符、转义规则都要和目标表完全匹配所以生产上我更倾向于让上游程序直接生成规范化CSV把校验逻辑提前到文件生成阶段。5.4 警惕大事务的连带影响批量插入的最终形态是大事务而大事务对MySQL的影响不只是锁。事务长时间不提交undo日志会不断膨胀binlog里记录的大事务在主从复制时会让从库延迟飙升回滚时也可能耗时很久。在MySQL 8.0里可以借助performance_schema或者直接看information_schema的INNODB_TRX表来监控当前事务的运行时长SELECT trx_id, trx_state, trx_started, trx_rows_locked FROM information_schema.INNODB_TRX;这是我做导入任务时必看的一步尤其是长事务存在时一定要评估它会不会拖垮从库。做完一批导入后也建议顺手检查一下主从延迟时间把事务大小控制在从库能消化的范围内。最后再分享一个我自己的习惯代码里一定要加进度日志每一万行打印一次执行时间和当前TPS。数据量一大人脑是没法直观感知“跑到哪了”的有了进度日志导入一跑起来就知道瓶颈在哪、是不是卡住了。另外导入之前给目标表做一个备份或者至少记录一下当前数据量万一导错了还能有回旋余地。批量插入这件事技术本身不复杂复杂的是在各种边界条件和异常场景下仍然能安全、稳定、可预期地完成导入。