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

焦虑的某人 07-21 222

前言：

如今看官们对“spring事务分批提交”大致比较珍视，你们都想要了解一些“spring事务分批提交”的相关知识。那么小编也在网络上网罗了一些有关“spring事务分批提交””的相关文章，希望小伙伴们能喜欢，小伙伴们一起来学习一下吧！

背景

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

一、代码中for 循环insert

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

优点：代码简单。

缺点：慢

二、批量foreach插入

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

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

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

（PS：可以修改max_allow_packet的值）

三、使用Mybatis的BATCH模式

# 设置batch模式brmybatis.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：

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：

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：

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：

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插入数据情况

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

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

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

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

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

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

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

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

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

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