MySQL批量插入技巧

关于MySQL批量插入的一些问题

MySQL一直是我们互联网行业比较常用的数据，当我们使用半ORM框架进行MySQL大批量插入操作时，你是否考虑过这些问题:

1. 进行大数据量插入时，是否需要进行分批次插入，一次插入多少合适？有什么判断依据？
2. 使用foreach进行大数据量的插入存在什么问题？
3. 如果插入批量插入过程中，因为服务器宕机等原因导致插入失败要怎么办？

基于此类问题，笔者以自己日常的开发手段作为依据演示一下MySQL批量插入的技巧。

常见的3种插入方式演示

实验样本数据

为了演示，这里给出一张示例表，除了id以外，有10个varchar字段，也就是说全字段写满的话一条数据差不多1k左右：

CREATE TABLE `batch_insert_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fileid_1` varchar(100) DEFAULT NULL,
  `fileid_2` varchar(100) DEFAULT NULL,
  `fileid_3` varchar(100) DEFAULT NULL,
  `fileid_4` varchar(100) DEFAULT NULL,
  `fileid_5` varchar(100) DEFAULT NULL,
  `fileid_6` varchar(100) DEFAULT NULL,
  `fileid_7` varchar(100) DEFAULT NULL,
  `fileid_8` varchar(100) DEFAULT NULL,
  `fileid_9` varchar(100) DEFAULT NULL,
  `fileid_10` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='测试批量插入，一行数据1k左右';

使用逐行插入

我们首先采用逐行插入方式分别插入3000、10w条的数据，这里为了保证实验的准确性，提前进行代码预热，先插入5条数据，然后在进行大批量的插入：

/**
     * 逐行插入
     */
    @Test
    void rowByRowInsert() {
        //预热先插入5条数据
        performCodeWarmUp(5);
 
        //生成10w条数据
        List<BatchInsertTest> testList = generateBatchInsertTestData();
 
 
        long start = System.currentTimeMillis();
 
        for (BatchInsertTest test : testList) {
            batchInsertTestMapper.insert(test);
        }
 
        long end = System.currentTimeMillis();
        log.info("逐行插入{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start);
 
    }

输出结果如下，可以看到当进行3000条数据的逐条插入时耗时在3s左右：

逐行插入3000条数据耗时:3492

而逐行插入10w条的耗时将其2min，插入表现可以说是非常差劲：

05.988 INFO  c.s.w.WebTemplateApplicationTests:55   main                    逐行插入100000条数据耗时:119678

使用foreach语法实现批量插入

Mybatis为我们提供了foreach语法实现数据批量插入，从语法上不难看出，它会遍历我们传入的集合，生成一条批量插入语句，其语法格式大抵如下所示：

 insert into batch_insert_test (id, fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10) 
 values (1, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),
 (2, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10'),
 (3, '1', '2', '3', '4', '5', '6', '7', '8', '9', '10');

批量插入代码如下所示：

 /**
     * foreach插入
     */
    @Test
    void forEachInsert() {
        /**
         * 代码预热
         */
        performCodeWarmUp(5);
 
 
        List<BatchInsertTest> testList = generateBatchInsertTestData();
 
        long start = System.currentTimeMillis();
        batchInsertTestMapper.batchInsertTest(testList);
        long end = System.currentTimeMillis();
 
        log.info("foreach{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start);
 
    }

对应xml配置如下：

<!-- 插入数据 -->
  <insert id="batchInsertTest" parameterType="java.util.List">
    INSERT INTO batch_insert_test (fileid_1, fileid_2, fileid_3, fileid_4, fileid_5, fileid_6, fileid_7, fileid_8, fileid_9, fileid_10)
    VALUES
    <foreach collection="list" item="item" separator=",">
      (#{item.fileid1}, #{item.fileid2}, #{item.fileid3}, #{item.fileid4}, #{item.fileid5},
      #{item.fileid6}, #{item.fileid7}, #{item.fileid8}, #{item.fileid9}, #{item.fileid10})
    </foreach>
  </insert>

实验结果如下，使用foreach进行插入3000条的数据耗时不到1s：

10.496 INFO  c.s.w.WebTemplateApplicationTests:79   main                    foreach3000条数据耗时:403

当我们进行10w条的数据插入时，受限于max_allowed_packet配置的大小，max_allowed_packet定义了服务器和客户端之间传输的最大数据包大小。该参数用于限制单个查询或语句可以传输的最大数据量，默认情况下为4M左右，所以这也最终导致了这10w条数据的插入直接失败了。

Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (106,100,142 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.

使用批处理完成插入

再来看看笔者最推荐的一种插入方式——批处理插入，在正式介绍这种插入方式前，读者先确认自己的链接配置是否添加了这条配置语句，只有在MySQL连接参数后面增加这一项配置才会使得MySQL5.1.13以上版本的驱动批量提交你的插入语句。

rewriteBatchedStatements=true

完成连接配置后，我们还需要对于批量插入的编码进行一定调整，Mybatis默认情况下执行器为Simple，这种执行器每次执行创建的都是一个全新的语句，也就是创建一个全新的PreparedStatement对象，这也就意味着每次提交的SQL语句的插入请求都无法缓存，每次调用时都需要重新解析SQL语句。
而我们的批处理则是将ExecutorType改为BATCH，执行时Mybatis会先将插入语句进行一次预编译生成PreparedStatement对象，发送一个网络请求进行数据解析和优化，因为ExecutorType改为BATCH，所以这次预编译之后，后续的插入的SQL到DBMS时，就无需在进行预编译，可直接一次网络IO将批量插入的语句提交到MySQL上执行。

 
@Autowired
    private SqlSessionFactory sqlSessionFactory;
 
/**
     * session插入
     */
    @Test
    void batchInsert() {
        /**
         * 代码预热
         */
        performCodeWarmUp(5);
 
 
        List<BatchInsertTest> testList = generateBatchInsertTestData();
 
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        BatchInsertTestMapper sqlSessionMapper = sqlSession.getMapper(BatchInsertTestMapper.class);
 
        long start = System.currentTimeMillis();
 
        for (BatchInsertTest batchInsertTest : testList) {
            sqlSessionMapper.insert(batchInsertTest);
        }
        sqlSession.commit();
        long end = System.currentTimeMillis();
        log.info("批处理插入{}条数据耗时:{}", BATCH_INSERT_SIZE, end - start);
 
    }

可以看到进行3000条数据插入时，耗时也只需只需2ms左右：

05.226 INFO  c.s.w.WebTemplateApplicationTests:108  main                    批处理插入3000条数据耗时:179

而进行10w条数据批处理插入的时机只需4s左右，效率非常可观。

04.771 INFO  c.s.w.WebTemplateApplicationTests:108  main                    批处理插入100000条数据耗时:4635

原因分析

针对上述三种方式，笔者来解释一下为什么在能够确保不出错的情况下，批处理插入的效率最高，我们都知道MySQL进行插入操作时整体的耗时比例如下：

链接耗时 （30%）
发送query到服务器 （20%）
解析query （20%）
插入操作 （10% * 词条数目）
插入index （10% * Index的数目）
关闭链接 （10%）

由此可知，进行SQL插入操作时，最耗时的操作是链接，这也就是为什么在进行3000条数据插入时，foreach和批处理插入的性能的性能表现最出色。因为逐行插入提交时，每一条插入操作都会进行至少两次的网络返回(如果生成的是stament对象则是两次，PreparedStatement则还要加上预编译的网络往返)，在大量的插入情况下，所有的语句都需要经历一次最耗时的链接操作，性能自然是下降了不少。

我们再来说说为什么批处理比foreach高效的原因，明明同样是3000条语句的插入，foreach传输的数据包大小也小于批处理，为什么批处理的性能却要好于foreach插入操作呢？

我们在上文讲批处理的时候提到，Mybatis在默认情况下，执行器是为SIMPLE，这就意味每次提交的插入操作的SQL语句都是相当于全新的PreparedStatement，都是需要进行预编译的，所以一条插入的SQL则是需要经历预编译和执行两次的网络往返，对应的代码也相当于下面这段JDBC代码：

		  // 创建Statement对象
            PreparedStatement statement = connection.createStatement();
 
            // 批量插入的数据
            String[] names = {"John Doe", "Jane Smith", "Mike Johnson"};
            int[] ages = {30, 25, 35};
            String[] cities = {"New York", "London", "Paris"};
 
            // 构建批量插入的SQL语句
            StringBuilder insertQuery = new StringBuilder("INSERT INTO mytable (name, age, city) VALUES ");
            for (int i = 0; i < names.length; i++) {
                insertQuery.append("('").append(names[i]).append("', ").append(ages[i]).append(", '").append(cities[i]).append("')");
                if (i < names.length - 1) {
                    insertQuery.append(", ");
                }
            }
 
            // 执行批量插入操作
            statement.executeUpdate(insertQuery.toString());
 
            // 关闭连接和Statement
            statement.close();
            connection.close();

可以看到在每一次使用foreach进行插入操作时，都需要重新创建一个PreparedStatement构建出一个SQL语句，每次提交时MySQL都需要进行一次预编译，这意味着用户每次使用foreach插入时，都需要进行一次预编译的网络IO，也正是这个原因使得其性能相较于批处理会逊色一些。

而批处理则不同，在我们的代码中，我们手动将ExecutorType改为BATCH，这样一来，每次进行批量插入时，Mybatis会先拿着我们的SQL语句创建成一个PreparedStatement提交到MySQL上进行预编译，这样一来本次会话所有相同的SQL语句直接提交时，就无需经过编译检查的操作，后续批量插入效率显著提升。

更高效的插入方式

因为Mybatis对于原生批处理操作做了很多的封装，其中涉及很多校验检查和解析等繁琐的流程，所以通过使用原生JDBC Batch来避免这些繁琐的解析、动态拦截等操作，对于MySQL批量插入也会有显著的提升。

一次插入多少数据量合适

明确要使用批处理进行批量插入之后，我们再来了解下一个问题，一次性批量插入多少条SQL语句比较合适？

对此我们基于100w的数据，分别按照每次10、500、1000、20000、80000条压测，最终实验结果如下

80000的数据，每次插入10条，耗时:14555
80000的数据，每次插入500条，耗时:5001
80000的数据，每次插入1000条，耗时:3960
80000的数据，每次插入2000条，耗时:3788
80000的数据，每次插入3000条，耗时:3993
80000的数据，每次插入4000条，耗时:3847

在经过笔者的压测实验时发现，在2000条差不多2M大小的情况下插入时的性能最出色。这一点笔者也在网上看到一篇文章提到MySQL的全局变量max_allowed_packet，它限制了每条SQL语句的大小，默认情况下为4M，而这位作者的实验则是插入数据的大小在max_allowed_packet的一半情况下性能最佳。

show variables like 'max_allowed_packet%';  

当然并不一定只有上述条件影响批量插入的性能，影响批量插入的性能原因还有:

1.插入缓存：对于innodb存储引擎来说，插入是需要耗费缓冲池内存的，如果在写密集的情况下，插入缓存会占用过多的缓冲池内存，若插入操作占用大小超过缓冲池的一半，则会影响操其他的操作。

关于缓冲池的大小，可以通过下面这条SQL查看，默认情况下为134M:

show variables like 'innodb_buffer_pool_size';

2.索引的维护：这点相信读者比较熟悉，如果每次插入涉及大量无序且多个索引的维护，导致B+tree进行节点分裂合并等处理，则会消耗大量的计算资源，从而间接影响插入效率。

小结

针对上述三种方式，批处理插入的效率最高。