MySQL大批量数据插入方案探讨

因为公司有需求要进行一个大批量数据的插入，所以特地花时间去研究了一下，目前总结出5种方法：

1. 单条数据循环插入
2. 进行sql拼接插入
3. 利用mybatis plus（mp）的批量插入
4. 结合sql拼接+循环插入
5. 4的升级版，放入同一个session种插入
   以上即为我总结的5种方式，下面会有详细的验证流程，如有错误欢迎指正。

一、准备工作

1.创建表

因为是测试，所以表就只建了几个字段

CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `dept_id` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;

2.基本代码

接下来就是mapper，service和controller层的创建，因为比较简单，所以就不贴代码了。

3.配置文件

需要在数据库开启批处理：rewriteBatchedStatements = true

datasource:
  type: com.zaxxer.hikari.HikariDataSource
  driver-class-name: com.mysql.cj.jdbc.Driver
  url: jdbc:mysql://localhost:3306/demo?serverTimezone=Asia/Shanghai&rewriteBatchedStatements = true

4.声明

本次测试中插入数据完成之后都会将表清空，防止老数据产生的影响。
插入的速度也会因为cpu等硬件的影响，因为同一台机器，可以忽略不计
每次测试都会进行3次并且重启服务

二、开始验证

1、单条数据循环插入

因为这种方式插入太慢了所以我只设置了1万条。

@Override
public int batchInsert() {
    Integer max = 10000;
    List<User> list = new ArrayList<>();
    for (Integer i = 0; i < max; i++) {
        User user = new User();
        user.setName("name:" + i);
        user.setTel("tel:" + 1);
        list.add(user);
    }
    long l = System.currentTimeMillis();
    for (User user : list) {
        mapper.insert(user);
    }
    Long time = System.currentTimeMillis() - l;
    System.out.println("总耗时：" + time);
    return 1;
}

3次测试结果如下：

总耗时：66188
总耗时：65873
总耗时：61469

可以看到，单单1万条数据就需要一分多钟，这肯定是不行的，究其原因，主要是时间都花在了获取、释放连接和关闭资源等操作上。

2、sql拼接插入

这种方式呢主要是用到了MySQL自带的批量插入语句，即：insert into table(…) values(…),(…),(…)…
代码如下：

@Override
public int batchInsert() {
    Integer max = 10000;
    List<User> list = new ArrayList<>();
    for (Integer i = 0; i < max; i++) {
        User user = new User();
        user.setName("name:" + i);
        user.setTel("tel:" + 1);
        list.add(user);
    }
    long l = System.currentTimeMillis();
    mapper.batchInsert(list);
    Long time = System.currentTimeMillis() - l;
    System.out.println("总耗时：" + time);
    return 1;
}

batchInsert方法的xml文件如下：

<insert id="batchInsert" parameterType="club.gggd.demo.domain.entity.User">
    INSERT INTO user(`name`, tel) VALUES
    <foreach collection="list" separator="," item="item">
        (#{item.name},#{item.tel})
    </foreach>
</insert>

3次测试结果如下：

总耗时：986
总耗时：1116
总耗时：1182

可以看到，将所有数据的插入拼接成一条SQL后，效率极大的提高了，毕竟只有一条SQL，不需要花什么时间去建立连接之类的。
但是当数据量变得很大时会导致SQL非常长，而且我这里只有3个字段，当字段过多时也会导致SQL变长，所以尽量不要使SQL太长。

3、mp的批量插入

mp自带了批量插入的功能，我们可以利用这个来进行批量插入：
到了这里以及后面的方法效率都比较高，所以我把数据量提升到了10万，这样看的会比较明显。

@Override
public int batchInsert() {
    Integer max = 100000;
    List<User> list = new ArrayList<>();
    for (Integer i = 0; i < max; i++) {
        User user = new User();
        user.setName("name:" + i);
        user.setTel("tel:" + 1);
        list.add(user);
    }
    long l = System.currentTimeMillis();
    this.saveBatch(list);
    Long time = System.currentTimeMillis() - l;
    System.out.println("总耗时：" + time);
    return 1;
}

3次测试结果如下：

总耗时：5392
总耗时：5220
总耗时：5330

可以看到，mp自带的批量插入方法插入10万条只需要5秒多就完成了，效率还是很不错的。
通过查看源码，可以发现mp的批量插入对要插入的数据进行了分批操作，每1000条作为一批次，然后循环将数据插入，所以效率也是很高的。

4、结合sql拼接+循环插入

依据第二个方法，通过SQL拼接插入也能有很好的效率，但是如果数据量不可控就会导致SQL过长从而报错，所以我们可以结合第一与第二个方法，即既拼接又循环的方式，如下所示：

@Override
public int batchInsert() {
    Integer max = 100000;
    List<User> list = new ArrayList<>();
    for (Integer i = 0; i < max; i++) {
        User user = new User();
        user.setName("name:" + i);
        user.setTel("tel:" + 1);
        list.add(user);
    }
    // 对数据按每1000条分割
    List<List<User>> split = ListUtil.split(list, 1000);
    long l = System.currentTimeMillis();
    for (List<User> users : split) {
        mapper.batchInsert(users);
    }
    Long time = System.currentTimeMillis() - l;
    System.out.println("总耗时：" + time);
    return 1;
}

3次测试结果如下：

总耗时：4169
总耗时：3848
总耗时：3908

可以看到，这种方式的效率比mp的批插入还要高，具体原因我认为是mp的批插入是每1000条做一次提交，但是它这1000条都是一条条插入的，而这种方法是将1000条合并为1条SQL插入的，所以效率会更高一些。

5、放入同一个session种插入

这种方式也就是方法3和方法4的结合版，方法4中每一次循环都是一次commit提交，所以也需要一定的时间去开启关闭连接等操作，那结合mp里面将1000条插入作为一次提交呢？

@Override
public int batchInsert() {
    Integer max = 100000;
    List<User> list = new ArrayList<>();
    for (Integer i = 0; i < max; i++) {
        User user = new User();
        user.setName("name:" + i);
        user.setTel("tel:" + 1);
        list.add(user);
    }
    SqlSession sqlSession = null;
    long l = 0L;
    try {
        //  开启批量处理模式 BATCH 、关闭自动提交事务
        sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
        //  反射获取Mapper
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // 对数据按每1000条分割
        List<List<User>> split = ListUtil.split(list, 1000);
        l = System.currentTimeMillis();
        for (List<User> users : split) {
            userMapper.batchInsert(users);
        }
        // 所有数据都插入完成后再提交事务
        sqlSession.commit();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        // 最后记得关闭资源
        if (sqlSession != null) {
            sqlSession.close();
        }
    }
    Long time = System.currentTimeMillis() - l;
    System.out.println("总耗时：" + time);
    return 1;
}

3次测试结果如下：

总耗时：2699
总耗时：2761
总耗时：2690

可以看到效率又进一步提升了，因为所有的插入都放在同一个commit里了，只需要一次连接即可，这个方案也是目前我认为最好的一个方案。
以上就是我对批量数据插入的一些浅薄理解，如果有什么错误的地方，欢迎大佬指正。