MyBatis-plus 批量新增方法性能测试及优化学习

最近对myBatis-plus 中的两个批量新增方法进行了简单的性能测试，并尝试对其进行优化。

第一个批量新增方法是在Mapper.xml 文件中使用标签页<instert> 和 <foreach> 实现批量新增，

后文中我把这种方式简单称为ForEach新增。

第二个批量新增方法是使用 myBatis-plus 提供的 ServiceImpl类中的 saveBatch 方法实现批量新增，后文中我把这种方式简单称为 saveBatch 新增。

本次性能测试的数据只适用于个人电脑配置。

相关配置参数：

操作系统: windows 11, jdk : 1.8 ,处理器：AMD Ryzen 7 5800H（8核16线程)，内存 13.9GB，

MySQL版本： 8.0.23，MyBatis-plus 版本： 3.4.3 ，mysql-connector-java dependency依赖版本： 8.0.23，连接池 ：springboot 自带 HikariCP

测试及优化点一:mysql 配置参数对Mysql插入性能的影响

一般情况下，在MybatisPlus中使用saveBatch方法进行批量保存需要：在数据库连接串中添加&rewriteBatchedStatements=true，并保证MySQL驱动在5.0.18以上。

 首先测试下savebatch方法不带有rewriteBatchedStatements 的情况

savebatch方法带有rewriteBatchedStatements 的情况

（第一次新增时间明显长于其它次的原因是因为第一次程序需要建立mysql connection链接，所以我们每次把第一次的取值忽略掉）

我们可以看到增加 rewriteBatchedStatements =true 参数 对 saveBatch 方法的效率有着巨大的提升！阅读源码可得在 ClientPreparedStatement executeBatchInternal()方法中存在 rewriteBatchedStatements参数判断，如果没有rewriteBatchedStatements 参数，则PreparedStatement方法不执行批量操作的逻辑。

所以，第一个优化建议:

在使用mybatis-plus saveBatch 方法的时候需要添加jdbc参数  rewriteBatchedStatements =true，并保证MySQL驱动在5.0.18以上

参考文献:MybatisPlus批量保存原理及失效原因排查_八球的博客-CSDN博客_mybatisplus savebatch原理

(标注: 接下来的saveBatch 方法测试都是带rewriteBatchedStatements =true参数的）

测试及优化点二:HikariCP数据库连接池的使用与参数优化

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mybatisPlusTest?useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
    hikari:
    ## 此属性控制从池返回的连接的默认自动提交行为,默认值：true
      auto-commit: true
    ## 数据库连接超时时间,默认30秒，即30000
      connection-timeout: 20000
    ## 最小空闲连接数量
      minimum-idle: 1
    ## 连接池最大连接数，默认是10
      maximum-pool-size: 1
    ## 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
      max-lifetime: 1200000
    ## 空闲连接存活最大时间，默认600000（10分钟）
      idle-timeout: 300000

这里我把线程池数量设为1 和把线程池数量设为10 ， 对比其数据库执行速度。 

maximum-pool-size 最大连接数是1时，我们看到mysql 也只创建了一个线程去执行sql语句。

maximum-pool-size  = 1，minimum-idle=1 savebatch 方法  六十字段 10万条数据量

maximum-pool-size  = 10，minimum-idle=10 时，mysql 则建立了10个线程执行该sql语句

  maximum-pool-size  = 10，minimum-idle=10 savebatch 方法  六十字段 10万条数据量

通过比对我们发现多线程执行sql语句与单线程执行sql语句的速度差别不大。接下来再测试下线程数是32时候的数据库执行速度

 ​​​​​​​maximum-pool-size  = 32，minimum-idle=32 savebatch 方法  六十字段 10万条数据量

 我们发现线程数量是32的时候，数据库执行速度反而变慢了。

所以，第二个优化建议：

合理配置数据库连接线程池大小，调优线程池参数，调优mysql 系统参数。

mysql调优参考文献:

Mysql查看连接数（连接总数、活跃数、最大并发数） - caoss - 博客园

MySQL 5.7调优参数详解【附源码】_恒星v_51CTO博客

测试及优化点三:数据量大小对Mysql插入性能的影响

新建一张二十个字段的表 test_model_order，我们来测试下 1万，10万，100万 条数据时，两个批量新增方法的速度。

 附上一个测试方法代码：

 @Test
    void testModeOrder(){
        // 总数据量
        int sumSize = 100000;
        // 每次插入数据量
        int insertSize = 10000;
        List<TestModelOrder> testModelOrders = new LinkedList<>();
        for (int i  = 0;i< sumSize;i++){
            TestModelOrder testModelOrder = genTestModelOrder();
            testModelOrders.add(testModelOrder);
        }
        int j = 1;
        for (int i = insertSize; i <= sumSize; i+=insertSize) {
            long startTime = System.currentTimeMillis();
            // saveBatch 方法
            testModelOrderService.saveBatch(testModelOrders.subList(i - insertSize, i));
            // forEach 方法
            // testModeOrderMapper.batchInsert(testModelOrders);
            long endTime = System.currentTimeMillis();
            System.out.println(insertSize +" 条数据量时,第"+j+"次批量新增所用时间:" +  
            (endTime - startTime));
            j++;
            // 清理数据库数据，保证数据量不变
            testModeOrderMapper.truncateTable();
        }
    }

<insert id="batchInsert" parameterType="map">
    <!--@mbg.generated-->
    insert into test_model_order
    (ID, orderNo, orderType, rideType, trafficOperatorId, merchantNo, companyNo, companyName, 
      routeId, routeName, driverName, cardNo, cardType, getOnTime, getOffTime, updateTime, 
      createTime, fileName, isTrans, isSend)
    values
    <foreach collection="list" item="item" separator=",">
      (#{item.id,jdbcType=VARCHAR}, #{item.orderno,jdbcType=VARCHAR}, #{item.ordertype,jdbcType=INTEGER}, 
        #{item.ridetype,jdbcType=INTEGER}, #{item.trafficoperatorid,jdbcType=VARCHAR}, 
        #{item.merchantno,jdbcType=VARCHAR}, #{item.companyno,jdbcType=INTEGER}, #{item.companyname,jdbcType=VARCHAR}, 
        #{item.routeid,jdbcType=INTEGER}, #{item.routename,jdbcType=VARCHAR}, #{item.drivername,jdbcType=VARCHAR}, 
        #{item.cardno,jdbcType=VARCHAR}, #{item.cardtype,jdbcType=INTEGER}, #{item.getontime,jdbcType=TIMESTAMP}, 
        #{item.getofftime,jdbcType=TIMESTAMP}, #{item.updatetime,jdbcType=TIMESTAMP}, #{item.createtime,jdbcType=TIMESTAMP}, 
        #{item.filename,jdbcType=VARCHAR}, #{item.istrans,jdbcType=INTEGER}, #{item.issend,jdbcType=INTEGER}
        )
    </foreach>
</insert>

savebatch 方法  20字段 1万条数据量

forEach 方法 20字段 1万条数据量

savebatch 方法 20字段  10万条数据量

forEach 方法 20字段 10万条数据量

savebatch 方法 20字段  100万条数据量

1000000 条数据量时,第1次批量新增所用时间:46031

1000000 条数据量时,第2次批量新增所用时间:44729

1000000 条数据量时,第3次批量新增所用时间:46203

forEach 方法 20字段 100万条数据量

抛出异常: Java heap space

        我们可以看出savebatch 方法比 forEach 方法的执行用时更少。从mysql的日志输出，以及查看其源码我们发现saveBatch 方法比 forEach 方法多了一次批量更新操作，saveBatch方法中有一个默认参数DEFAULT_BATCH_SIZE = 1000 ，也就是每一千次执行批量更新一次，而foreach 方法它的实现是直接生成一个大的sql语句，去执行全部数据的插入操作，很明显在数据量大的情况下拥有批量更新的方式比没有缓存的方式速度更快。而且数据量达到一百万的时候，直接使用 forEach 方法会报 java内存溢出的错误。

所以，第三个优化建议:

使用mybatis <forEach>标签的小伙伴，面对数据量大的情况,可以在程序中自行控制批量新增List对象的size()大小，实现分批次新增数据。（单条数据占用字节数越多（表字段越多)，每次更新条数越少)

100万条数据以下可使用List.subList()  方法实现数据批量新增

100万条数据以上，List对象过大，也会导致性能问题，建议从拆分数据源入手，比如分文件批量入库。

测试及优化点四: mysql表总字段长度（表字段多少)对Mysql插入性能的影响

新建三张表 ：testuser（六字段)  ,test_model_order(二十字段), test_big_order(六十字段)

测试每次插入10万数据量时的savebatch(）方法性能

六字段:

二十字段: 

六十字段:

很明显，单表字段越多，mysql 执行插入的速率越慢，那么mysql一张表建多个字段比较合适呢？

mysql物理存储的结构，由段-区-页-行组成

每个区是1M大小，由连续的64个16k的页组成，每个页又由N行组成

每个页16k，在mysql内存加载过程中，数据加载的最小单位是页。所以每个页中存储的行越多，则数据加载的页会越少，查找性能越高。

假设一页16k=160行，则一行=100字节，100字节=10个字段=>1个字段=10字节，所以这里看下1行存储10个10字节的字段，这样一页能存储160行数据。

第四个优化建议:

建议规范: mysql的一个表总共字段长度不超过65535。

所以在开发过程中，我们可以根据每条数据的实际字节长度去判断我们是否需要进行分表操作。

参考文献:

mysql一张表建多个字段比较合适呢，答案来嘞_一一空的专栏-CSDN博客_一张表多少个字段合适

<MYSQL 数据库设计规范.docx>

测试及优化点五: mysql表中数据量大小对Mysql插入性能的影响

新建表: test_model_order(二十字段)

测试数据库包含0条数据- 200万条数据，数据入库的执行时间。

从0-100 万（第一条数据包含mysql建立链接时长）

从100-200 万（第一条数据包含mysql建立链接时长）

 可以看到，每次插入数据库的时间都在逐渐递增。可见数据量大小对插入性能的影响也是巨大的。

所以第五个优化建议:

我们在设计数据库的时候一定要对大数据量的表进行分库，分表，分区设计。

 参考文献:

<MYSQL 数据库设计规范.docx>

测试及优化点六: mybatis-plus savebatch 方法 DEFAULT_BATCH_SIZE 参数设置

通过mybatis-plus 源码 ，我们可以查到

 saveBatch 方法可以手动设置batchsize的大小，那么batchsize 设置多少是最佳选择呢？

我们可以手动写一个测试方法进行测试，从而得到想要的batchsize的大小。

 /**
     * 关于表中有多少条数据对插入执行效率的影响
     */
    @Test
    void tableBatchSizeTest(){
        // 总数据量
        int sumIndex = 5000000;
        // 每次更新数据量
        int keyIndex = 1000000;
        // 缓存数据量大小
        int batchSize = 1000;
        List<TestBigOrder> testBigOrders = new LinkedList<>();
        for (int i  = 0;i< sumIndex;i++){
            TestBigOrder testBigOrder = genTestBigOrder();
            testBigOrders.add(testBigOrder);
        }
        // 去除mysql连接的影响
        testBigOrderService.save(genTestBigOrder());
        System.out.println("testUsersList.size:" + testBigOrders.size());

        int j = 1;
        for (int i = keyIndex; i <= sumIndex; i+=keyIndex) {
            long startTime = System.currentTimeMillis();
            System.out.println("batchSize:" + batchSize);
            testBigOrderService.saveBatch(testBigOrders.subList(i - keyIndex, i),batchSize+=1000);
            long endTime = System.currentTimeMillis();
            System.out.println("第"+j+"次批量新增所用时间:" +  (endTime - startTime));
            j++;
            testBigOrderMapper.truncateTable();
        }
    }

测试test_big_order（六十字段）每次插入100万条数据 batchsize  1000 -10000

本次测试并没有发现batchsize调整对mysql插入执行速度有多大的影响哈，但是想提高程序入库效率的小伙伴，从batchsize参数入手也是一个很好的测试点。

最后小编在工作中发现一个mybatis-plus 的相关问题：在执行saveBatch()方法的时候，控制台报ArryIndexOutOfBoundException错误，于是网上搜索了一下解决方案

一.  驱动版本过低导致

参考文献：

mybatis连接oracle数据库，新增记录时java.lang.ArrayIndexOutOfBoundsException错误_crj的博客-CSDN博客

二.表字段过多，数据量大， 可以尝试修改batchsize 减少每次批量更新数据的数量，比如每次批量更新500条数据。