如何在 MySQL 中高效插入 1w 条数据？6 个案例帮你找出最优方案！

前言

电商平台的订单、金融交易流水、用户行为的记录，使用传统的单条 INSERT 语句逐行插入方式，在处理 1 万条数据时往往需要数秒，这不仅会导致事务锁竞争加剧，更可能引发连接超时等系统性风险

那么如何优化这种批量插入的场景呢？让我们一起看看吧！

传统插入的性能瓶颈

• 网络延迟：每次插入都独立发往数据库执行，那么每个 SQL 语句的网络通信都可能造成延迟
• 事务控制：每次插入通常会涉及一次事务的开启和提交（如果没有显式地控制事务）
• 锁争用和并发性：如果大量插入数据时，逐个插入的方式会增加锁的争用，尤其是当数据表上有多个事务并发访问时，插入的性能会进一步下降

优化批量插入

扩大事务粒度

在进行批量插入时，大事务（即将多条 INSERT 语句放在一个事务中）比独立事务（即每一条 INSERT 语句都单独使用一个事务）通常性能更好，原因是：

• 事务提交开销

  • 每个独立事务需要执行 COMMIT 操作，这会触发 MySQL 的日志持久化（如 redo log 的 fsync 操作），导致磁盘 I/O 开销。同时，多次发送开启事务和提交事务的操作，带来了额外的网络开销
  • 大事务只需一次 COMMIT，减少了日志刷盘的次数，从而显著降低 I/O 等待时间

• 锁竞争与锁释放

  • 独立事务每次提交会释放行锁，下一次插入时需重新获取锁，增加了锁竞争的开销
  • 大事务在整个过程中持有锁（如行锁），减少了锁的重复获取和释放，提高并发效率

• 日志写入优化

  • 对于 InnoDB 引擎，事务日志（redo log）以顺序追加方式写入
  • 大事务的日志批量写入比多次小事务的分散写入更高效，减少了日志缓冲区切换和磁盘寻址开销

合并多条 INSERT

将多条 INSERT 语句合并成一条 INSERT 语句（例如 INSERT INTO table (col1, col2) VALUES (val1, val2), (val3, val4), ...）也能提高性能，原因包括：

• 减少网络开销：合并为单条 INSERT 语句后，客户端与数据库服务器的交互次数减少，降低了网络延迟的影响
• 降低 SQL 解析成本：每条 SQL 都需要经历语法解析、优化器处理等步骤。合并后只需一次解析，节省 CPU 资源
• 减少锁开销： 每条独立的 INSERT 都需要进行锁定和解锁操作，而合并为一条 INSERT 操作时，从而减少了锁相关的性能损失

实现 INSERT 合并

• 手动拼接 SQL。比如通过 MyBatis foreach 标签
• JDBC BatchInsert。在 Connection 中，添加参数 rewriteBatchedStatements=true 如 jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true。有 JDBC 帮我们完成 SQL 的合并

预生成 ID

InnoDB 为保证自增 ID 的全局唯一性，在分配自增值时会持有自增锁（AUTO-INC Lock）。在高并发情况下，多个插入操作会频繁争抢数据库的自增 ID，这可能导致锁的竞争和性能瓶颈

使用预生成 ID 会有更好的性能表现，比如预先生成雪花 ID。避免在数据库层面加锁解锁影响性能

批量插入方案性能测试

环境准备

applicatoin.yml 配置（关键在于rewriteBatchedStatements=true）：

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true&useServerPrepStmts=false
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

为了方便测试，使用数据库的自增 ID，生成数据库表 user：

CREATE TABLE `user` (
                        `id` bigint(20) NOT NULL AUTO_INCREMENT,
                        `name` varchar(100) DEFAULT NULL,
                        `age` int(11) DEFAULT NULL,
                        `email` varchar(100) DEFAULT NULL,
                        PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

java User 实体类：

@Data
@TableName("user")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

生成 1w 条测试数据：

private List<User> prepareTestData(int count) {
    List<User> users = new ArrayList<>(count);
    for (int i = 0; i < count; i++) {
        User user = new User();
        user.setId(null);  // 自增ID
        user.setName("test" + i);
        user.setAge(20 + i % 50);
        user.setEmail("test" + i + "@test.com");
        users.add(user);
    }
    return users;
}

每次测试后，清空数据，避免对下次测试的影响：

TRUNCATE TABLE user;
ALTER TABLE user AUTO_INCREMENT = 1;

测试

独立事务循环插入

使用 for 循环，逐个 INSERT。每个 INSERT 都会隐式独立地开启并提交事务，一条 INSERT 语句都单独使用一个事务。如图：

@Test
public void testSingleTransactionInsert() {
    List<User> users = prepareTestData(10000);
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();

    for (User user : users) {
        userMapper.insert(user);
    }

    stopWatch.stop();
    System.out.println("独立事务循环插入耗时: " + stopWatch.getTotalTimeMillis() + "ms");
}

测试结果：

特点

• N 次 INSERT + N 次 COMMIT → 高 I/O、高锁竞争
• 当某条 INSERT 失败，只回滚它自身，降低执行失败造成的影响

大事务循环插入

显式使用@Transactional开启事务，让执行的 SQL 都在同一个大事务中，这样只需要开启和提交一次事务，如图：

@Test
@Transactional // 开始事务
public void testBigTransactionInsert() {
    List<User> users = prepareTestData(10000);
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();

    for (User user : users) {
        userMapper.insert(user);
    }

    stopWatch.stop();
    System.out.println("大事务循环插入耗时: " + stopWatch.getTotalTimeMillis() + "ms");
}

测试结果：

特点

• N 次 INSERT + 1 次 COMMIT → 锁竞争可控
• 过大的事务可能导致 Undo Log 膨胀或长事务阻塞其他操作
• 假设某个数据插入失败，所有数据都要回滚

MyBatis foreach

通过 MyBatis foreach 标签，将多条 INSERT 语句合并为一条 INSERT 语句，如图：

@Mapper
public interface UserMapper extends BaseMapper<User> {
    // 方便测试，直接将 SQL 写到注解
    @Insert("<script>" +
            "INSERT INTO user (name, age, email) VALUES " +
            "<foreach collection='users' item='user' separator=','>" +
            "(#{user.name}, #{user.age}, #{user.email})" +
            "</foreach>" +
            "</script>")
    void batchInsert(@Param("users") List<User> users);
}

@Test
public void testMybatisForeachInsert() {
    List<User> users = prepareTestData(10000);
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();

    userMapper.batchInsert(users);

    stopWatch.stop();
    System.out.println("MyBatis foreach批量插入耗时: " + stopWatch.getTotalTimeMillis() + "ms");
}

测试结果：

特点

• 1 次多值 INSERT + 1 次 COMMIT → 最小化网络、解析、I/O 开销
• 需要对 foreach 标签进行解析，可能带来额外的开销
• 自己拼接 SQL，不依赖参数rewriteBatchedStatements=true和 JDBC 批量插入机制

JDBC BatchInsert

需要开启 rewriteBatchedStatements=true

JDBC BatchInsert 的 SQL 和 INSERT 单条数据的语法一样，JDBC 会帮我们像上面 MyBatis foreach 一样，将多个 INSERT 聚合为一条 INSERT 语句

@Test
public void testJdbcBatchInsert() {
    List<User> users = prepareTestData(10000);
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();

    jdbcTemplate.batchUpdate("INSERT INTO user (name, age, email) VALUES (?, ?, ?)",
            new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    User user = users.get(i);
                    ps.setString(1, user.getName());
                    ps.setInt(2, user.getAge());
                    ps.setString(3, user.getEmail());
                }

                @Override
                public int getBatchSize() {
                    return users.size();
                }
            });

    stopWatch.stop();
    System.out.println("JDBC batch插入耗时: " + stopWatch.getTotalTimeMillis() + "ms");
}

测试结果：

特点

• 需要开启 rewriteBatchedStatements=true。由 JDBC 帮我们进行批处理，不用我们手动合并 INSERT
• 单条 SQL 长度受 max_allowed_packet 参数限制，需避免超出阈值
• JDBC 硬编码，使用字符串拼接 SQL，不方便维护

MyBatis SqlSession 批量插入

需要开启 rewriteBatchedStatements=true

获取 MyBatis 的 ExecutorType.BATCH 的 SqlSession，执行批量插入，底层依赖 JDBC BatchInsert，将多条 SQL 合并成一条

@Test
public void testMybatisBatchInsert() {
    List<User> users = prepareTestData(10000);
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();

    try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        for (User user : users) {
            mapper.insert(user);
        }
        sqlSession.commit();
    }

    stopWatch.stop();
    System.out.println("MyBatis SqlSession批量插入耗时: " + stopWatch.getTotalTimeMillis() + "ms");
}

测试结果：

特点

• 底层依赖 JDBC BatchInsert 实现，需要开启 rewriteBatchedStatements=true

MyBatis-Plus saveBatch

需要开启 rewriteBatchedStatements=true

使用 MyBatis-Plus saveBatch 将多个 SQL 合并成一条

@Test
public void testMybatisPlusBatchInsert() {
    List<User> users = prepareTestData(10000);
    StopWatch stopWatch = new StopWatch();
    stopWatch.start();

    userService.saveBatch(users, 1000);

    stopWatch.stop();
    System.out.println("MyBatis-Plus批量插入耗时: " + stopWatch.getTotalTimeMillis() + "ms");
}

测试结果：

特点

• 底层依赖 JDBC BatchInsert 实现，需要开启 rewriteBatchedStatements=true
• saveBatch 内部封装了对分片处理（默认 1000） 和分批提交事务的操作

结果分析

方法	耗时(ms)
独立事务循环插入	12485
大事务循环插入	9565
MyBatis foreach	891
JDBC BatchInsert	587
MyBatis SqlSession 批量插入	825
MyBatis-Plus saveBatch	860

性能排名：

1. JDBC BatchInsert
2. MyBatis SqlSession 批量插入
3. MyBatis-Plus saveBatch
4. MyBatis foreach
5. 大事务循环插入
6. 独立事务循环插入

如何选择？

都需要开启rewriteBatchedStatements=true

• 极致性能：JDBC BatchInsert。不过需要手写 SQL，导致硬编码
• 通用方案：优先选择 MyBatis-Plus saveBatch，可以很方便地分片处理和分批提交事务。其次选择 MyBatis SqlSession 批量插入

测试的局限性

1. 需要记录每种方案在不同数据量、不同字段数和不同字段大小对批量插入的影响
2. 考虑并发场景下的性能，是否会涉及大量锁竞争
3. 测试是在本地运行，没有考虑网络带宽的影响

如何将批量插入应用到业务中？

问题的核心在于：如何“攒一波”数据来实现批量插入？

推荐批量插入与 MQ 配合使用。将需要插入的数据的消息发送给 MQ，生产者需要保证 MQ 的消息发送和本地事务的原子性。对于消费者，一次性拉取多个消息进行批量插入。当消息消费失败时，可以让 MQ 重新投递消息并重新消费

不过 MQ 的引入和积攒数据，带来的数据插入的延迟是不可避免的，同时只能保证最终一致性而不是强一致

结语

只需要开启rewriteBatchedStatements=true并使用 MyBatis-Plus saveBatch，就可以大幅提高批量插入的性能。如果可以离线导入数据，使用LOAD DATA也是一种选择

不过一致性与性能难以兼得，一旦某个 INSERT 执行失败，整个事务的插入操作都要回滚。如果是在不同分片的事务，分片与分片之间的一致性也无法保证。同时，数据的插入可能会延后一段时间。所以不建议强一致的场景使用批量插入

---

如果文章对你有帮助，欢迎点赞+收藏+关注，有问题可以在评论区评论哦！

公众号【牛肉烧烤屋】

B 站【爱烤猪蹄的乔治】

参考资料

https://juejin.cn/post/7442351780288380969

封面：PTITSA