提升 MySQL 批量更新效率的底层原理与优化策略-51CTO.COM

近期进行项目优化梳理工作时，发现某些功能模块进行MySQL数据库批量更新操作比较耗时，对此笔者查阅相关资料比进行压测后，得出最优解，遂以此文章记录一下笔者的解决方案。

一、前置准备

为方便演示，笔者先说明一下本文进行实验的数据表，对应的DDL语句如下，可以看到该表有一个自增的主键ID和9个字段以及一个日期字段：

CREATE TABLE `batch_insert_test` (
  `id` int(11) 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,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `batch_insert_test_create_date_IDX` (`create_date`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=19091237 DEFAULT CHARSET=utf8 COMMENT='测试批量插入，一行数据1k左右';

特别注意，读者在根据本文进行操作时需要对数据库连接配置上追加如下两个参数，否则优化方案不会生效：

&rewriteBatchedStatements=true&allowMultiQueries=true

二、三种方案压测实验

1. 逐条更新

首先查看逐条更新的解决方案，笔者通过分页查询查询大约3000条数据，然后逐条进行遍历更新：

 /**
     * 使用foreach进行逐条插入
     */
    @Test
    public void foreachUpdate() {
        //分页查询3k的数据
        PageHelper.startPage(PAGE, SIZE);
        List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);

        //逐条更新
        StopWatch stopWatch = new StopWatch("foreachUpdate");
        stopWatch.start();
        for (BatchInsertTest insertTest : insertTestList) {
            batchInsertTestMapper.updateByPrimaryKey(insertTest);
        }
        stopWatch.stop();

        log.info("逐条更新完成,size:{},耗时:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());


    }

对应耗时结果如下，可以看到耗时花费了1592ms，表现比较逊色，原因很简单，每条数据操作时都涉及网络IO，3000次串行的网络IO+DB更新，执行效率自然上不去：

2025-01-10 09:07:02.920  INFO 19328 --- [           main] c.s.mapper.BatchInsertTestMapperTest     : 逐条更新完成,size:3000,耗时:1592ms

2. 并行运算

不知道读者是否留意笔者上文所说的串行DB更新，既然串行的网络IO会降低执行效率，那么我们并行更新呢？

所以笔者将代码进行进一步的优化：

/**
     * 使用并行流foreach进行逐条插入
     */
    @Test
    public void foreachParallelStreamUpdate() {
        PageHelper.startPage(PAGE, SIZE);

        List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);

        //采用并行流的方式进行并行更新
        StopWatch stopWatch = new StopWatch("foreachUpdate");
        stopWatch.start();
        insertTestList.parallelStream()
                .forEach(i -> {
                    batchInsertTestMapper.updateByPrimaryKey(i);
                });


        stopWatch.stop();

        log.info("逐条更新完成,size:{},耗时:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());


    }

可以看到3000条数据花费了357ms，执行效率还是很客观的，但笔者认为这还不是最优解，原因很简单，每次进行批量更新操作都需要进行多次网络IO，如果在并发量非常大的场景，比较吃MySQL的连接池资源：

2025-01-10 09:07:00.789  INFO 19328 --- [           main] c.s.mapper.BatchInsertTestMapperTest     : 逐条更新完成,size:3000,耗时:357ms

3. foreach更新

我们再来看看mybatis内置的foreach语法的批量更新：

<update id="updateByPrimaryKeyForeach" parameterType="com.sharkChili.domain.BatchInsertTest">
        <foreach collection="list" item="item" separator=";">
            update batch_insert_test
            set fileid_1 = #{item.fileid1,jdbcType=VARCHAR},
            fileid_2 = #{item.fileid2,jdbcType=VARCHAR},
            fileid_3 = #{item.fileid3,jdbcType=VARCHAR},
            fileid_4 = #{item.fileid4,jdbcType=VARCHAR},
            fileid_5 = #{item.fileid5,jdbcType=VARCHAR},
            fileid_6 = #{item.fileid6,jdbcType=VARCHAR},
            fileid_7 = #{item.fileid7,jdbcType=VARCHAR},
            fileid_8 = #{item.fileid8,jdbcType=VARCHAR},
            fileid_9 = #{item.fileid9,jdbcType=VARCHAR},
            create_date = #{item.createDate,jdbcType=TIMESTAMP}
            where id = #{item.id,jdbcType=INTEGER}
        </foreach>
    </update>

对应测试代码如下：

@Test
    public void updateByPrimaryKeyForeach() {
        PageHelper.startPage(PAGE, SIZE);

        List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
        StopWatch stopWatch = new StopWatch("updateByPrimaryKeyForeach");
        stopWatch.start();

        batchInsertTestMapper.updateByPrimaryKeyForeach(insertTestList);

        stopWatch.stop();

        log.info("使用updateByPrimaryKeyForeach更新完成,size:{},耗时:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());
    }

耗时563ms左右，性能表现也还行，并且foreach操作会因为字符串拼接操导致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.即提交的SQL执行数据包过大被拒绝的风险:

2025-01-10 09:10:57.592  INFO 18332 --- [           main] c.s.mapper.BatchInsertTestMapperTest     : 使用updateByPrimaryKeyForeach更新完成,size:3000,耗时:563ms

4. 批处理更新

笔者希望可以一批更新操作可以一个批次的进行提交，所以接下来介绍这种方案就是一次性组装一批量的更新语句，然后一次性提交。

 /**
     * 使用批处理进行更新
     */
    @Test
    public void updateBatch() {
        PageHelper.startPage(PAGE, SIZE);

        List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
        StopWatch stopWatch = new StopWatch("updateBatch");
        stopWatch.start();

        //创建一个进行批处理操作的sqlsession组装一批更新语句
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            BatchInsertTestMapper batchInsertTestMapper = sqlSession.getMapper(BatchInsertTestMapper.class);

            insertTestList.parallelStream()
                    .forEach(i -> {
                        batchInsertTestMapper.updateByPrimaryKey(i);
                    });
            //手动提交
            sqlSession.commit();
            stopWatch.stop();
        } catch (Exception e) {

        }

        log.info("批处理更新完成,size:{},耗时:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());

    }

最终更新耗时为1s左右，相较于上述方案相对逊色一些，但是网络IO的开销以及MySQL的连接池使用都减小了，综合起来性价比还是蛮高的：

2024-02-22 23:25:05.265  INFO 18844 --- [           main] c.s.mapper.BatchInsertTestMapperTest     : 批处理更新完成,size:3000,耗时:1566ms

5. case-when更新

最后一种case-when更新，语法如下，猛的一看比较复杂，实际理解起来还是蛮简单的，对每个字段进行set操作，例如:当id等于1时，fileid_1则取id为1的那条数据的值，通过case分支实现一条SQL批量更新多条数据：

update batch_insert_test
  -- 当id=1 则设置fileid_1为aaa
        set fileid_1=
            when 1 then aaa
  ....其余同理
   where id in (本次批处理的id列表)

所以结合mybatis框架的语法，我们得出下面这样一个SQL语句：

<update id="updateBatch" parameterType="java.util.List">
        update batch_insert_test
        set fileid_1=
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid1,jdbcType=VARCHAR}
        </foreach>,
        fileid_2 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid2,jdbcType=VARCHAR}
        </foreach>,
        fileid_3 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid3,jdbcType=VARCHAR}
        </foreach>,
        fileid_4 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid4,jdbcType=VARCHAR}
        </foreach>,
        fileid_5 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid5,jdbcType=VARCHAR}
        </foreach>,
        fileid_6 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid6,jdbcType=VARCHAR}
        </foreach>,
        fileid_7 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid7,jdbcType=VARCHAR}
        </foreach>,
        fileid_8 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid8,jdbcType=VARCHAR}
        </foreach>,
        fileid_9 =
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.fileid9,jdbcType=VARCHAR}
        </foreach>,
        create_date=
        <foreach collection="list" item="item" index="index"
                 separator=" " open="case ID" close="end">
            when #{item.id} then #{item.createDate,jdbcType=TIMESTAMP}
        </foreach>
        where id in
        <foreach collection="list" index="index" item="item"
                 separator="," open="(" close=")">
            #{item.id,jdbcType=INTEGER}
        </foreach>
    </update>

对应的Java代码如下，比较简单，笔者这里就不多做赘述了：

@Test
    public void updateDateByWhenCase() {
        PageHelper.startPage(PAGE, SIZE);

        List<BatchInsertTest> insertTestList = batchInsertTestMapper.selectByExample(null);
        StopWatch stopWatch = new StopWatch("updateBatch");
        stopWatch.start();

        batchInsertTestMapper.updateBatch(insertTestList);

        stopWatch.stop();

        log.info("使用when case更新完成,size:{},耗时:{}ms", insertTestList.size(), stopWatch.getLastTaskTimeMillis());
    }

最终可以看到耗时800毫秒左右，相较于批处理更加出色一些，而且网络和连接池的开销都是差不多的，但和foreach意义也可能存在数据包过大导致报错的风险：

2025-01-10 09:17:06.878  INFO 16788 --- [           main] c.s.mapper.BatchInsertTestMapperTest     : 使用when case更新完成,size:3000,耗时:738ms

小结

以上便是笔者本次大量压测后得出的解决方案，总结如下：

如果网络情况良好且MySQL连接池资源充分的情况下，笔者更推荐使用并行进行逐条更新。
如果网络情况不好或者MySQL资源紧张，笔者更推荐使用foreach更新，相较于同等一次性更新多条语句的when-case语法,它语法更简单且执行性能更好一些。
当然如果一次性要更新比较大基数的数据，考虑到MySQL的传输packet size我们还是优先考虑批处理这个性能和稳定性处于折中的方案。