提升 MySQL 批量更新效率的底层原理与优化策略

写代码的SharkChili 2025-01-10

写在文章开头

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

Hi，我是 sharkChili ，是个不断在硬核技术上作死的技术人，是 CSDN的博客专家 ，也是开源项目 Java Guide 的维护者之一，熟悉 Java 也会一点 Go ，偶尔也会在 C源码 边缘徘徊。写过很多有意思的技术博客，也还在研究并输出技术的路上，希望我的文章对你有帮助，非常欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

因为近期收到很多读者的私信，所以也专门创建了一个交流群，感兴趣的读者可以通过上方的公众号获取笔者的联系方式完成好友添加，点击备注 “加群” 即可和笔者和笔者的朋友们进行深入交流。

前置准备

为方便演示，笔者先说明一下本文进行实验的数据表，对应的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

三种方案压测实验

逐条更新

首先查看逐条更新的解决方案，笔者通过分页查询查询大约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

并行运算

不知道读者是否留意笔者上文所说的串行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

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

批处理更新

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

 /**
     * 使用批处理进行更新
     */
    @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

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我们还是优先考虑批处理这个性能和稳定性处于折中的方案。

我是 sharkchili ，CSDN Java 领域博客专家，mini-redis的作者，我想写一些有意思的东西，希望对你有帮助，如果你想实时收到我写的硬核的文章也欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

参考

mybatis + mysql 高性能批量插入和批量更新:https://blog.csdn.net/w_g_b/article/details/118388059

Mybatis中进行批量更新（updateBatch）:https://blog.csdn.net/xyjawq1/article/details/74129316

MySQL通过 case when 批量更新数据:https://blog.csdn.net/network_dream/article/details/89339185

文章转载自写代码的SharkChili，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。