最近在项目当中,有用到批量新增的操作。总结一下,大概有三种方式来完成这个操作,(1)在业务代码中循环逐条新增(2)在业务代码中循环逐渐新增-开启batch模式(3)使用Mybatis-foreach标签拼接sql执行,逐条更新操作是在数据库中执行的,在业务代码中体现的是一次性更新。下面将通过本地连接MySQL数据库的方式,测试三种方式之间的差异。
表结构
CREATE TABLE IF NOT EXISTS `role` (
`id` bigint(20) NOT NULL COMMENT 'id',
`name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
一、在业务代码中循环插入
public void inert() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.SIMPLE,false);
RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
Long start = System.currentTimeMillis();
Role role;
for (int i = 0; i < 10000; i++) {
role = new Role();
role.setId(i);
role.setName("name" + i);
roleMapper.insert(role);
}
log.info("业务代码循环插入10000条数据耗时={}" + (System.currentTimeMillis() - start));
}
对应Mybatis-Mapper配置
<insert id="insert" parameterType="com.example.transaction.dto.Role" >
insert into role (id, name)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
</insert>
二、在业务代码中循环新增-开启批处理
public void batchInsert() {
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
Long start = System.currentTimeMillis();
Role role;
for (int i = 0; i < 10000; i++) {
role = new Role();
role.setId(i);
role.setName("name" + i);
roleMapper.insert(role);
}
sqlSession.commit();
log.info("(使用批处理)业务代码循环插入1000000条数据耗时={}" + (System.currentTimeMillis() - start));
}
对应的mapper配置一样
<insert id="insert" parameterType="com.example.transaction.dto.Role" >
insert into role (id, name)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
</insert>
另外,jdbc连接时是加上rewriteBatchedStatements=true才是真正开启批处理。
spring:
datasource:
name: test
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
总结:Mybatis的ExecutorType有三种(REUSE、SIMPLE、BATCH),默认是SIMPLE,这种模式会为每个语句的执行创建新的预处理,单条提交。而BATCH模式把SQL语句发送到数据库后,数据库预处理SQL,只打印一次SQL语句,多次设置参数。
三、Mybatis-foreach-sql拼接方式
@GetMapping("insert")
public void inert(@RequestParam("count") Integer count) {
Long start = System.currentTimeMillis();
List<Role> list = new ArrayList<>();
Role role;
for (int i = 0; i < count; i++) {
role = new Role();
role.setId(i);
role.setName("name" + i);
list.add(role);
}
roleMapper.insertBatch(list);
log.info("sql使用foreach方式循环插入{}条数据耗时={}", count, (System.currentTimeMillis() - start));
}
SQL语句如下:
<insert id="insertBatch" parameterType="java.util.List" >
insert into role (id, name)
values
<foreach collection="list" item="role" separator=",">
(#{role.id,jdbcType=INTEGER}, #{role.name,jdbcType=VARCHAR})
</foreach>
</insert>
关于foreach标签的使用
item:将当前遍历出的元素赋值给指定的变量
index:索引。遍历list的时候是index就是索引,item就是当前值
遍历map的时候index表示的就是map的key,item就是map的值
#{变量名}就能取出变量的值也就是当前遍历出的元素
在本次测试过程中,使用的数据库版本是5.7.19,单条sql的最大数据量是4194304,故当数据量太大时,需调整MySQL的配置。(1)可通过MySQL安装目录下的my.ini文件修改单条sql的数据量,在‘mysqld’段上添加max_allowed_packet = 20M,(2)或者通过命令行set global max_allowed_packet = 20*1024*1024,然后退出命令行,重启MySQL服务即可。
在这次测试过程中,当插入100万条数据时,就会有问题,需调整配置。
四、结果比较
毫秒 | 业务代码循环新增 | batch批处理 | foreach-sql拼接 |
10000条 | 17057 | 495 | 576 |
100000条 | 159297 | 1095 | 3265 |
1000000条 | 1912256 | 17666 | 36304 |
总结:当数据量比较大时,使用BATCH批处理方式执行批量操作的效率会高很多。