MyBatis 批量插入的几种方式与性能对比

3 万条数据导了 20 分钟

上周五下午接到个活儿：从老系统导 3 万条历史订单到新库。我写了个循环，单次 insert，跑起来就去接水了。回来一看还在跑，最后总共花了 18 分 42 秒。DBA 在钉钉上问我是不是在攻击数据库。

痛定思痛，我把 MyBatis 批量插入的几种写法全试了一遍，顺便记个对比。

基线：for 循环单条插入

// Mapper
int insertOne(Order order);

// Service
for (Order o : list) {
    orderMapper.insertOne(o);
}

3 万条跑了 18 分 42 秒。慢得理所当然：每条都是一次独立的网络往返 + 一次事务提交（autocommit 开着的话）。按 37ms 一条算，光网络 RTT 就占了大头。

方案一：foreach 拼成大 SQL

这是网上最常见的写法：

<insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO t_order (order_no, user_id, amount, status, create_time)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.orderNo}, #{item.userId}, #{item.amount}, #{item.status}, #{item.createTime})
    </foreach>
</insert>

3 万条一次性塞进去，直接给我报了这个：

com.mysql.jdbc.PacketTooBigException: Packet for query is too large
(4521873 > 4194304). You can change this value on the server
by setting the max_allowed_packet' variable.

MySQL 5.7 默认 max_allowed_packet 是 4MB，一条 SQL 塞不下 3 万条记录。而且就算调大到 64MB，SQL 太长也会把 SQL 解析、预编译的开销全堆在数据库端，还容易撑爆 PreparedStatement 缓存。

所以必须分批。我按每批 1000 条切：

int batchSize = 1000;
for (int i = 0; i < list.size(); i += batchSize) {
    int end = Math.min(i + batchSize, list.size());
    orderMapper.batchInsert(list.subList(i, end));
}

结果：耗时 24.3 秒。从 18 分钟到 24 秒，提升约 46 倍。

方案二：ExecutorType.BATCH

MyBatis 还有个正经的批处理模式，原理是复用同一个 PreparedStatement，把参数攒起来一次性发给 MySQL。

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
    OrderMapper mapper = session.getMapper(OrderMapper.class);
    for (int i = 0; i < list.size(); i++) {
        mapper.insertOne(list.get(i));
        if (i % 1000 == 999) {
            session.flushStatements();   // 攒够一批就刷
            session.commit();
            session.clearCache();
        }
    }
    session.flushStatements();
    session.commit();
} finally {
    session.close();
}

第一次跑完我傻眼了：耗时 15 分 08 秒，只比 for 循环快了一点点。

问题出在 JDBC 驱动上。MySQL 的 JDBC 驱动（Connector/J 5.1.x）默认会把 batch 拆成一条一条单独发，addBatch 等于白攒。必须加一个 URL 参数：

jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8
  &rewriteBatchedStatements=true

加上 rewriteBatchedStatements=true 之后，驱动才会真正把多条 INSERT 重写成一条 INSERT INTO ... VALUES (...),(...),(...) 发出去。重跑：耗时 19.1 秒，比 foreach 还快一点。

三种方案实测对比

环境：MySQL 5.7.21，Connector/J 5.1.46，MyBatis 3.4.6，JDK 8，本地局域网，3 万条记录。

几个我踩到的细节

• 一定要手动 commit。 openSession(ExecutorType.BATCH, false) 第二个参数是 autoCommit，关掉之后忘了 commit，数据一条都没进去，我还以为代码写错了。
• 定期 flushStatements。 攒 3 万条不刷，客户端内存会彪，而且违反 MySQL 的 packet 大小限制。我一般 500~1000 刷一次。
• clearCache 别漏。 BATCH 模式下不 clearCache，一级缓存会把所有 statement 攒在内存里，3 万条跑下来老年代占用涨了 200 多 MB。
• insertOne 的 id 回写会失效。 BATCH 模式下 useGeneratedKeys 拿不到自增 id，需要 id 的话老实用 foreach。

我的选择

最后线上我选了 foreach 分批。原因很实际：BATCH 模式要手动管 SqlSession，代码侵入大，而且 Spring 托管的 Mapper 默认不是 BATCH 的，混用容易出事。多出那 5 秒换来的可维护性我觉得值。

另外，3 万这个量级用 MyBatis 还行。如果哪天要导 300 万，我会直接用 LOAD DATA INFILE——那玩意儿 300 万条大概 40 秒，不是 MyBatis 能比的量级。

两个补充的踩坑记录

事务一定要手动开

最早我没有加 @Transactional，autocommit 模式下每批 1000 条各自提交一次，30 批就是 30 次 fsync，耗时 41 秒。加上事务之后，30 批共用一个事务，只在最后提交一次，降到 24 秒。如果数据量特别大，也可以分批次提交（每 10 批提交一次），避免单个事务的 undo log 膨胀——我们生产环境就是这么配的。

Mapper 方法别传太多参数

foreach 生成的 SQL 参数个数是 记录数 × 字段数。1000 条记录、每条 5 个字段就是 5000 个占位符。MySQL 的预编译语句在解析这 5000 个参数时也要时间，我试过把批次改成 500 条，总耗时反而从 24.3 秒降到 22.8 秒。所以批次大小不是越大越好，我们最后定的是 500，这个值是我们那张表实测出来的最优解，换张表可能就不一样了。

导入完记得 ANALYZE

这个不是 MyBatis 的问题，但顺带记一下。3 万条数据灌进去之后，第二天有同学反馈列表查询变慢了。原因是 InnoDB 的索引统计信息没更新，优化器选错了索引。执行一次就好了：

ANALYZE TABLE t_order;

我们把这行加到了导入脚本的末尾。

最后一个：导入之前先把 binlog 格式确认一下。如果用的是 ROW 格式，一次批量插入会产生巨大的 binlog，从库同步会明显延迟。我们测试环境就是因为导 3 万条把从库拖慢了十几分钟，监控上看到主从延迟告警还以为是数据库出故障了。

参考

• 官方文档：database-sql
• Baeldung 上的相关实战
• StackOverflow 上的相关讨论