批量处理数据对比（＜foreach＞标签和sqlsession）

最新推荐文章于 2024-04-15 09:53:50 发布

凯先生boy

最新推荐文章于 2024-04-15 09:53:50 发布

阅读量1.5k

点赞数 3

分类专栏： springboot mysql 文章标签： sql mysql spring boot java

本文链接：https://blog.csdn.net/weixin_44457062/article/details/124238654

版权

springboot 同时被 2 个专栏收录

9 篇文章 0 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

批量处理数据对比（标签和sqlsession）

本文主要对一下情况进行对比

Java代码中使用for循环，mysql中一条一条的插入
mysql的批量插入，借用mybatis的标签
使用sqlsession进行批量处理，到达一定的条数再进行提交

使用环境

Java版本：Java8
Mysql:8.0.27
idea版本：Idea2021
操作系统：MacOS（12.1）
处理器：M1Pro（8核）
内存：16G
框架：Spring Boot（2.6.2）

表结构

id表示自增主键，剩下的20列采用的默认长度255，varchar类型，引擎：InnoDB

CREATE TABLE `cay`.`Untitled`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name3` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name4` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name5` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name6` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name7` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name8` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name9` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name10` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name11` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name12` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name13` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name14` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name15` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name16` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name17` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name18` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name19` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name20` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

实体类

@Data
public class BatchEntity {
    private Integer id;
    private String name1;
    private String name2;
    private String name3;
    private String name4;
    private String name5;
    private String name6;
    private String name7;
    private String name8;
    private String name9;
    private String name10;
    private String name11;
    private String name12;
    private String name13;
    private String name14;
    private String name15;
    private String name16;
    private String name17;
    private String name18;
    private String name19;
    private String name20;
}

for循环批量插入数据

在java代码中通过for循环来实现批量插入。其中构建对象的时间没有计算。

代码实现

Mapper

@Mapper
public interface BatchMapper {
    int insertByOne(BatchEntity batchEntity);
}

Mapper.xml

<insert id="insertByOne" parameterType="BatchEntity">
  insert into t_batch(name1,name2,name3,name4,name5,name6,name7,name8,name9,name10
  ,name11,name12,name13,name14,name15,name16,name17,name18,name19,name20)
  values (#{name1}, #{name2},#{name3}, #{name4},#{name5}, #{name6},#{name7}, #{name8},#{name9}, #{name10}
  ,#{name11}, #{name12},#{name13}, #{name14},#{name15}, #{name16},#{name17}, #{name18},#{name19}, #{name20})
</insert>

测试类方法

@Autowired
private BatchMapper batchMapper;
private final static String CAY_STR = "cay";//公用的字符串
/**
* 测试for循环
*/
@Test
public void testInsertByOne(){
  int j = 10;
  for (int i = 1; i <= 5; i++, j = j * 10){//以10为基数，每次扩大10被直至十万数据，几十万数据用for循环不太现实。
    log.info("第  {}  次循环，条数： {}", i, j);
    List<BatchEntity> batchList = getList(j);
    long startTime = System.currentTimeMillis();
    batchList.forEach((batchEntity) ->{
      batchMapper.insertByOne(batchEntity);
    });
    long endTime = System.currentTimeMillis();
    log.info("执行时间：{}", endTime - startTime);
  }
}

//公用的创建对象的方法
/**
* 构造实体类
* @param n 实体类的数量
* @return
*/
private List<BatchEntity> getList(int n){
  List<BatchEntity> batchList = new ArrayList<>();
  for (int i = 0; i < n; i++){
    BatchEntity batchEntity = new BatchEntity();
    batchEntity.setName1(CAY_STR);
    batchEntity.setName2(CAY_STR);
    batchEntity.setName3(CAY_STR);
    batchEntity.setName4(CAY_STR);
    batchEntity.setName5(CAY_STR);
    batchEntity.setName6(CAY_STR);
    batchEntity.setName7(CAY_STR);
    batchEntity.setName8(CAY_STR);
    batchEntity.setName9(CAY_STR);
    batchEntity.setName10(CAY_STR);
    batchEntity.setName11(CAY_STR);
    batchEntity.setName12(CAY_STR);
    batchEntity.setName13(CAY_STR);
    batchEntity.setName14(CAY_STR);
    batchEntity.setName15(CAY_STR);
    batchEntity.setName16(CAY_STR);
    batchEntity.setName17(CAY_STR);
    batchEntity.setName18(CAY_STR);
    batchEntity.setName19(CAY_STR);
    batchEntity.setName20(CAY_STR);
    batchList.add(batchEntity);
  }
  return batchList;
}

执行结果：

 第  1  次循环，条数： 10
 HikariPool-1 - Starting...
 HikariPool-1 - Start completed.
 执行时间：715
 第  2  次循环，条数： 100
 执行时间：268
 第  3  次循环，条数： 1000
 执行时间：790
 第  4  次循环，条数： 10000
 执行时间：3424
 第  5  次循环，条数： 100000
 执行时间：27971

可以看出来，当没有开启连接池的时候10条数据使用了715ms，当连接池开启之后，插入的条数增减，但是时间减少了。随着条数不断的增加，时间也越来越长，仅仅10万数据就需要27秒多。很明显当数据到达10万这个量级的时候。使用for循环已经很不明智了。

mysql 的 values()批量插入

通过mybatis的foreach标签来实现批量插入。

Mapper

@Mapper
public interface BatchMapper {
    int insertByForEach(@Param("entityList") List<BatchEntity> batchEntity);
}

Mapper .xml

<insert id="insertByForEach" parameterType="BatchEntity">
  insert into t_batch(name1,name2,name3,name4,name5
  ,name6,name7,name8,name9,name10,name11,name12,name13,name14,name15,name16,name17,name18,name19,name20)
  values
  <foreach collection="entityList" open="(" close=")" item="entity" separator="),(">
    #{entity.name1},#{entity.name2},#{entity.name3},#{entity.name4},#{entity.name5},#{entity.name6}
    ,#{entity.name7},#{entity.name8},#{entity.name9},#{entity.name10},#{entity.name11},#{entity.name12}
    ,#{entity.name13},#{entity.name14},#{entity.name15},#{entity.name16},#{entity.name17},#{entity.name18}
    ,#{entity.name19},#{entity.name20}
  </foreach>
</insert>

测试方法：

/**
* 测试foreach
*/
@Test
public void testInsertByForEach(){
  int j = 10;
  for (int i = 1; i <= 5; i++, j = j * 10){//以10为基数，每次扩大10被直至百万数据
    log.info("第  {}  次循环，条数： {}", i, j);
    List<BatchEntity> batchList = getList(j);//获取实体类
    long startTime = System.currentTimeMillis();
    batchMapper.insertByForEach(batchList);
    long endTime = System.currentTimeMillis();
    log.info("执行时间：{}", endTime - startTime);
  }
}

执行结果：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：346
第  2  次循环，条数： 100
执行时间：116
第  3  次循环，条数： 1000
执行时间：212
第  4  次循环，条数： 10000
执行时间：1088
第  5  次循环，条数： 100000
执行时间：11510

可以看出使用mysql自带的批量处理也要比for循环插入要好的多。10万条数据，从25秒多变成11秒多。

当我尝试增大到30万数据的时候,会报如下异常。mysql自带的批量处理，不能一次处理太多数据。

org.springframework.dao.TransientDataAccessResourceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (89,277,988 > 67,108,864). You can change this value on the server by setting the 'max_allowed_packet' variable.

错误主要关注点：

Packet for query is too large (89,277,988 > 67,108,864)
setting the ‘max_allowed_packet’ variable.

原因：

mysql对sql语句默认有闲置大小，具体可以通过下面的语句在mysql中新建查询进行查询。

show variables like 'max_allowed_packet'

本人使用mysql版本的默认大小：

Variable_name	Value
max_allowed_packet	67108864

从上面报错中可以看出，一次传输三十万条的sql语句太大了，以至于超过了mysql默认的sql语句的大小（(89,277,988 > 67,108,864)），数据库无法进行处理，所以报错了。

SqlSession批量插入

Maper 和Mapper.xml的代码相同，不再展示，直接展示测试方法

测试方法：

/**
* 测试sqlsession
*/
@Test
public void testInsertBySqlSession(){
  int j = 10;
  for (int i = 1; i <= 5; i++, j = j * 10){//以10为基数，每次扩大10被直至百万数据
    log.info("第  {}  次循环，条数： {}", i, j);
    List<BatchEntity> batchList = getList(j);//获取实体类
    long startTime = System.currentTimeMillis();
    //建议自己尝试去掉这两个参数和去掉false
    SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    BatchMapper mapper = sqlSession.getMapper(BatchMapper.class);
    int[] k = {1};
    batchList.forEach((entity -> {
      mapper.insertByOne(entity);
      if (k[0] % 1000 == 999){//1000条提交一次
        sqlSession.commit();
        sqlSession.clearCache();
      }
      k[0]++;
    }));
   	//未满足的提交一次
    sqlSession.commit();
    sqlSession.clearCache();
    long endTime = System.currentTimeMillis();
    log.info("执行时间：{}", endTime - startTime);
  }
}

执行结果：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：233
第  2  次循环，条数： 100
执行时间：68
第  3  次循环，条数： 1000
执行时间：379
第  4  次循环，条数： 10000
执行时间：1831
第  5  次循环，条数： 100000
执行时间：15967
HikariPool-1 - Shutdown initiated...
HikariPool-1 - Shutdown completed.

经过上面对使用foreach标签和sqlsession差多的测试发现，两者性能差不多，但是本人经常在网上看到说sqlsession比foreach标签，于是本人带着疑问，是否自己忘记配置某些东西，于是在网上搜到需要在datasource的url上配置rewriteBatchedStatements=true。

spring:
  datasource:
    url: jdbc:mysql:///cay?characterEncoding=utf-8&rewriteBatchedStatements=true

添加配置之后，得到结果如下

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：690
第  2  次循环，条数： 100
执行时间：128
第  3  次循环，条数： 1000
执行时间：239
第  4  次循环，条数： 10000
执行时间：1176
第  5  次循环，条数： 100000
执行时间：10105

可见添加rewriteBatchedStatements=true配置之后效果明显提升。但是标签的批量插入时间有所加长。测试结果如下:

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：775
第  2  次循环，条数： 100
执行时间：153
第  3  次循环，条数： 1000
执行时间：372
第  4  次循环，条数： 10000
执行时间：1289
第  5  次循环，条数： 100000
执行时间：13689

从上文测试结果，可以看出在数据量10万时，标签模式未添加rewriteBatchedStatements=true时，时间会在11秒左右，添加之后，时间会在13秒多。可见添加rewriteBatchedStatements=true配置，一定情况会影响单条（标签其实是一条sql）的数据插入。

**添加rewriteBatchedStatements=true，类似的for循环插入的时间也变长了。**for循环插入测试结果：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：676
第  2  次循环，条数： 100
执行时间：346
第  3  次循环，条数： 1000
执行时间：875
第  4  次循环，条数： 10000
执行时间：4010
第  5  次循环，条数： 100000
执行时间：32655

从上文进行对比发现，当10万条数据时，没有配置rewriteBatchedStatements=true时，使用时间在27秒左右，添加配置rewriteBatchedStatements=true时，时间在32秒左右。

结论：

使用ssqlsession时，再添加rewriteBatchedStatements=true，反之，则不要添加rewriteBatchedStatements=true。

rewriteBatchedStatements=true的作用：

默认情况下MySQL 的JDBC驱动是不支持批量插入的，会自动将批量的一组数据拆分，然后再一条一条的发送给数据库，这样就和for循环插入差不多了。但是添加rewriteBatchedStatements=true之后，就可以让驱动知道这是需要批量执行的，提高效率。

前提条件：保证jdbc的驱动在5.1.13以上。

下面是参考博客的描述：

MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。
只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL
另外这个选项对INSERT/UPDATE/DELETE都有效

博客链接：https://blog.csdn.net/qq_38361800/article/details/102697824

当我把代码中的forEach改成增强for循环之后效果进一步得到的提升。

改成增强for循环之后：

代码如下：

/**
     * 测试sqlsession
     */
    @Test
    public void testInsertBySqlSession(){
        int j = 10;
        for (int i = 1; i <= 5; i++, j = j * 10){//以10为基数，每次扩大10被直至百万数据
            log.info("第  {}  次循环，条数： {}", i, j);
            List<BatchEntity> batchList = getList(j);//获取实体类
            long startTime = System.currentTimeMillis();
            //ExecutorType.BATCH, false
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
            BatchMapper mapper = sqlSession.getMapper(BatchMapper.class);
          	//增强for循环
            for(int k = 0; k < batchList.size(); k++ ){
                mapper.insertByOne(batchList.get(k));
                if (k % 1000 == 999){
                    sqlSession.commit();
                    sqlSession.clearCache();
                }
            }
            /*int[] k = {1};
            batchList.forEach((entity -> {
                mapper.insertByOne(entity);
                if (k[0] % 100 == 99){
                    sqlSession.commit();
                    sqlSession.clearCache();
                }
                k[0]++;
            }));*/
            sqlSession.commit();
            sqlSession.clearCache();
            long endTime = System.currentTimeMillis();
            log.info("执行时间：{}", endTime - startTime);
        }
    }

得到结果

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：647
第  2  次循环，条数： 100
执行时间：122
第  3  次循环，条数： 1000
执行时间：212
第  4  次循环，条数： 10000
执行时间：886
第  5  次循环，条数： 100000
执行时间：6793

可见在10万条数据的时候增强for循环比forEach效果更加好，快了两秒多，在未添加rewriteBatchedStatements=true之前，两者时间差不多，有时候会有较大差异，有时候差异不大。之前也做过不同for循环的测试，但是得到结果有些和网上不太一样，有时间再重新测试一下。本人还是建议在实际生产环境，进行测试for循环，这样才能得到更好的答案。本文就不深究for循环了。

在未添加rewriteBatchedStatements=true之前，两者时间差不多，有时候会有较大差异，有时候差异不大的情况展示：

增强for循环：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：679
第  2  次循环，条数： 100
执行时间：202
第  3  次循环，条数： 1000
执行时间：450
第  4  次循环，条数： 10000
执行时间：2205
第  5  次循环，条数： 100000
执行时间：18597

forEach方法：

 第  1  次循环，条数： 10
 HikariPool-1 - Starting...
 HikariPool-1 - Start completed.
 执行时间：642
 第  2  次循环，条数： 100
 执行时间：197
 第  3  次循环，条数： 1000
 执行时间：469
 第  4  次循环，条数： 10000
 执行时间：2333
 第  5  次循环，条数： 100000
 执行时间：18910

两者并没有太大差异，所以还是希望读者在实际环境，进行测试，然后再选择是使用哪一种for循环。

再次思考：本来我以为这句代码

sqlSessionFactory.openSession(ExecutorType.BATCH, false);

和rewriteBatchedStatements=true效果是一样，所以之前测试的时候没有加上。现在才发现不一样。

通过控制变量法，配置rewriteBatchedStatements=true，将上面的这句代码变为如下：

sqlSessionFactory.openSession();

得到结果如下：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：618
第  2  次循环，条数： 100
执行时间：342
第  3  次循环，条数： 1000
执行时间：845
第  4  次循环，条数： 10000
执行时间：3786
第  5  次循环，条数： 100000
执行时间：32881

通过上面的测试，发现如果在openSession不使用ExecutorType.BATCH性能比不配置rewriteBatchedStatements=true性能更差。至于后面的布尔值则没有太大的影响。即

//这三条语句并不会有太大差距
sqlSessionFactory.openSession(ExecutorType.BATCH, false);
sqlSessionFactory.openSession(ExecutorType.BATCH, true);
sqlSessionFactory.openSession(ExecutorType.BATCH);

读者可以使用上面语句测试，本人已经测试，具体结果在此不展示了，希望读者自己亲自试试。后面本人会继续查看布尔值的用处，后续会进行补充。

最后一种情况：

使用sqlSessionFactory.openSession()并且不配置rewriteBatchedStatements=true。

得到的结果：

第  1  次循环，条数： 10
HikariPool-1 - Starting...
HikariPool-1 - Start completed.
执行时间：689
第  2  次循环，条数： 100
执行时间：301
第  3  次循环，条数： 1000
执行时间：742
第  4  次循环，条数： 10000
执行时间：3377
第  5  次循环，条数： 100000
执行时间：27784

发现两个都不使用，比单独配置rewriteBatchedStatements=true效果还要好一点点，让人难以相信。

**总结：**希望大家在使用sqlsession的时候要两个都配置：

//带不带后面的布尔值，大家可以自己尝试。本人之后了解了，也会进行补充
sqlSessionFactory.openSession(ExecutorType.BATCH, false);

spring:
  datasource:
    url: jdbc:mysql:///cay?characterEncoding=utf-8&rewriteBatchedStatements=true

注意：

本人使用sqlsession的时候是以1000进行分组，大家也可以尝试其他的数量进行分组。本人尝试的有100，500，1500，2000，2500。发现和使用1000分组差不多，就没有往后面尝试。并且，**本人使用的forEach进行的循环，尝试使用普通for循环和增强for循环，在某些情况下会差距有点大，具体情况大家可以在实际使用场景中进行测试，本次主要是批处理，对for循环的问题没有深究，所以测了部分情况，但是没有展示。大家还是在实际情况进行测试一下，看具体哪一种for循环的效果更好，有时候会有较大差别，有时候没有太大差别。**有兴趣的读者可以自己亲自动手尝试，自己动手才能真正的观察到。

另外，本文如果有什么忽略的地方，也希望大神指出。本人在Java方面也在努力学习，不断查漏补缺，请多指教。

个人意见：

只有自己自己尝试才能知道那个效果能更加好，本人得到测试结果也可能错误，希望读者自己测试一下，有不同的结果，可以反馈给我，共同探讨一下。还是要多读一些资料，亲自动手，才能让自己得到正确的答案。总的来说sqlsession的批量处理要比标签好，但是要在sqlSessionFactory.openSession(ExecutorType.BATCH, false)（布尔值没什么作用，后续会补充，读者也可以自己尝试）中使用并且在url中配置rewriteBatchedStatements=true。只配值其中一个效果没有那么明显。

凯先生boy

关注

3
点赞
踩
4

收藏

觉得还不错? 一键收藏
1
评论
批量处理数据对比（＜foreach＞标签和sqlsession）

批量处理数据对比（标签和sqlsession）本文主要对一下情况进行对比Java代码中使用for循环，mysql中一条一条的插入mysql的批量插入，借用mybatis的标签使用sqlsession进行批量处理，到达一定的条数再进行提交使用环境Java版本：Java8Mysql:8.0.27idea版本：Idea2021操作系统：MacOS（12.1）处理器：M1Pro（8核）内存：16G框架：Spring Boot（2.6.2）表结构id表示自增主键，剩下的20列采用的
复制链接

扫一扫