Mybatis 批量插入的相关问题实践

爱学习的某人
1,448 阅读5分钟

Mybatis批量插入的实战测试

背景

在我们实际业务开发过程中经常会有需求需要使用到批量插入的场景。在我的工作过程中接触的到的批量插入方式有几种：

一、代码中for 循环insert

public Boolean insertByFor(List<City> cities) {
  for (City city : cities) {
    cityDao.insert(city);
  }

   return Boolean.TRUE;
}

优点：代码简单。

缺点：慢

二、批量foreach插入

<insert id="insertBatch" parameterType="org.spring.springboot.domain.City">
  insert into city
  (province_id, city_name, description)
  VALUES
  <foreach collection="cities" item="item" index="index" separator=",">
    (#{item.provinceId}, #{item.cityName}, #{item.description})
  </foreach>
</insert>

优点：数据量不大的情况下，插入速度较快

缺点：mysql默认的max_allow_packet大小为4194304，当插入数量太大时会超过4194304，导致sql抛异常。

（PS：可以修改max_allow_packet的值）

三、使用Mybatis的BATCH模式

# 设置batch模式
mybatis.executor-type=batch

（PS：代码和for循环插入一样）

ExecutorType.SIMPLE

这个执行器类型不做特殊的事情。它为每个语句的执行创建一个新的预处理语句。（默认的）

ExecutorType.REUSE

官方文档中的解释是“执行器会重用预处理语句（prepared statements）” ， 这次倒是解释的很详细。也就是说不会每一次调用都去创建一个 Statement 对象 ， 而是会重复利用以前创建好的（如果SQL相同的话），这也就是在很多数据连接池库中常见的 PSCache 概念 。但是ReuseExecutor的PSCache 范围只能存在于一次回话中 ， 因为每一次回话内部都会使用一个新的 ReuseExecutor 对象 ， 所以 mybatis 的 PSCache 作用十分有限。

ExecutorType.BATCH

支持批量操作、BatchExecutor 的事务是没法自动提交的。因为 BatchExecutor 只有在调用了 SqlSession 的 commit 方法的时候 ， 它才会去执行 executeBatch 方法。

有了以上简单的背景后，针对这三种批量插入的方式，网络有多种解释。经过自己简单的实际实践后结果数据：

三种方式性能对比

环境：

MySQL：5.7

Driver：Hikai

表结构：

表1：

-- springbootdb.city30 definition

CREATE TABLE `city30` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `province_id` bigint(20) DEFAULT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL，
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1893641 DEFAULT CHARSET=utf8mb4;

表2：

-- springbootdb.city10 definition

CREATE TABLE `city10` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `province_id` bigint(20) DEFAULT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `test1` varchar(64) DEFAULT NULL,
  `test2` varchar(64) DEFAULT NULL,
  `test3` varchar(64) DEFAULT NULL,
  `test4` varchar(64) DEFAULT NULL,
  `test5` varchar(64) DEFAULT NULL,
  `test6` varchar(64) DEFAULT NULL,
  `test7` varchar(64) DEFAULT NULL,
  `test8` varchar(64) DEFAULT NULL,
  `test9` varchar(64) DEFAULT NULL,
  `test10` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city_test1_IDX` (`test1`,`test2`) USING BTREE,
  KEY `city_test3_IDX` (`test3`) USING BTREE,
  KEY `city_test7_IDX` (`test7`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=6608751 DEFAULT CHARSET=utf8mb4;

表3：

-- springbootdb.city20 definition

CREATE TABLE `city20` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `province_id` bigint(20) DEFAULT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `test1` varchar(64) DEFAULT NULL,
  `test2` varchar(64) DEFAULT NULL,
  `test3` varchar(64) DEFAULT NULL,
  `test4` varchar(64) DEFAULT NULL,
  `test5` varchar(64) DEFAULT NULL,
  `test6` varchar(64) DEFAULT NULL,
  `test7` varchar(64) DEFAULT NULL,
  `test8` varchar(64) DEFAULT NULL,
  `test9` varchar(64) DEFAULT NULL,
  `test10` varchar(64) DEFAULT NULL,
  `test11` varchar(64) DEFAULT NULL,
  `test12` varchar(64) DEFAULT NULL,
  `test13` varchar(64) DEFAULT NULL,
  `test14` varchar(64) DEFAULT NULL,
  `test15` varchar(64) DEFAULT NULL,
  `test16` varchar(64) DEFAULT NULL,
  `test17` varchar(64) DEFAULT NULL,
  `test18` varchar(64) DEFAULT NULL,
  `test19` varchar(64) DEFAULT NULL,
  `test20` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city_test17_IDX` (`test17`) USING BTREE,
  KEY `city_test1_IDX` (`test1`,`test2`) USING BTREE,
  KEY `city_test3_IDX` (`test3`) USING BTREE,
  KEY `city_test7_IDX` (`test7`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表4：

-- springbootdb.city30 definition

CREATE TABLE `city30` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `province_id` bigint(20) DEFAULT NULL,
  `city_name` varchar(100) DEFAULT NULL,
  `description` varchar(100) DEFAULT NULL,
  `test1` varchar(64) DEFAULT NULL,
  `test2` varchar(64) DEFAULT NULL,
  `test3` varchar(64) DEFAULT NULL,
  `test4` varchar(64) DEFAULT NULL,
  `test5` varchar(64) DEFAULT NULL,
  `test6` varchar(64) DEFAULT NULL,
  `test7` varchar(64) DEFAULT NULL,
  `test8` varchar(64) DEFAULT NULL,
  `test9` varchar(64) DEFAULT NULL,
  `test10` varchar(64) DEFAULT NULL,
  `test11` varchar(64) DEFAULT NULL,
  `test12` varchar(64) DEFAULT NULL,
  `test13` varchar(64) DEFAULT NULL,
  `test14` varchar(64) DEFAULT NULL,
  `test15` varchar(64) DEFAULT NULL,
  `test16` varchar(64) DEFAULT NULL,
  `test17` varchar(64) DEFAULT NULL,
  `test18` varchar(64) DEFAULT NULL,
  `test19` varchar(64) DEFAULT NULL,
  `test20` varchar(64) DEFAULT NULL,
  `test21` varchar(100) DEFAULT NULL,
  `test22` varchar(100) DEFAULT NULL,
  `test23` varchar(100) DEFAULT NULL,
  `test24` varchar(100) DEFAULT NULL,
  `test25` varchar(100) DEFAULT NULL,
  `test26` varchar(100) DEFAULT NULL,
  `test27` varchar(100) DEFAULT NULL,
  `test28` varchar(100) DEFAULT NULL,
  `test29` varchar(100) DEFAULT NULL,
  `test30` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city_test17_IDX` (`test17`) USING BTREE,
  KEY `city_test1_IDX` (`test1`,`test2`) USING BTREE,
  KEY `city_test3_IDX` (`test3`) USING BTREE,
  KEY `city_test7_IDX` (`test7`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1893641 DEFAULT CHARSET=utf8mb4;

以下为多次测试的结果数据：

4个字段的表 使用foreach插入数据情况

image-20211228154111578.png

横坐标：插入的数量（单位：千条）

纵坐标：插入需要的时间（单位：毫秒）

14个字段的表 使用foreach插入数据情况

image-20211228153557916.png

横坐标：插入的数量（单位：千条）

纵坐标：插入需要的时间（单位：毫秒）

24个字段的表 使用foreach插入数据情况

image-20211228145717119.png

横坐标：插入的数量（单位：千条）

纵坐标：插入需要的时间（单位：毫秒）

34个字段的表 使用foreach插入数据情况

image-20211228151914822.png 横坐标：插入的数量（单位：千条）

纵坐标：插入需要的时间（单位：毫秒）

从上述结果可以看出得出以下结论：

1、字段数少的表 批量插入时，使用foreach批量插入 性能会比较好；

2、字段数稍微多的表 批量插入时，BATCH性能最好，foreach在测试中不存在性能拐点。（因为4张图对应的折线，除了几个点以外，其他整体的上升速度比较平均）

3、Mybatis 的executor 为BATCH时，在表字段数量较少的情况下，没有优势，但是当表字段数量增加时整体的性能远远领先foreach的方式（预估耗时 在foreach的1/2左右）。

avatar
文章
阅读
粉丝