MyBatis批量插入的几种方式效率比较

MyBatis批量插入的几种方式效率比较

目录前言一、前期准备1.1 表结构1.2 项目配置文件1.3 实体类二、反复执行单条插入语句2.1 对应 mapper 接口2.2 测试方法三、foreach 拼接SQL3.1 对应mapper 接口3.2 测试方法3.3 执行时间四、批处理4.1 rewriteBatchedStatements 参数4.2 批处理准备4.3 执行时间4.4 如果数据更大五、总结

前言

批处理数据主要有三种方式：

反复执行单条插入语句

foreach 拼接 sql

批处理

一、前期准备

基于Spring Boot + mysql，同时为了省略get/set，使用了lombok，详见pom.xml。

1.1 表结构

id 使用数据库自增。

DROP TABLE IF EXISTS `user_info_batch`;

CREATE TABLE `user_info_batch` (

`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',

`user_name` varchar(100) NOT NULL COMMENT '账户名称',

`pass_word` varchar(100) NOT NULL COMMENT '登录密码',

`nick_name` varchar(30) NOT NULL COMMENT '昵称',

`mobile` varchar(30) NOT NULL COMMENT '手机号',

`email` varchar(100) DEFAULT NULL COMMENT '邮箱地址',

`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`gmt_update` timestamp NULL DEFAULT NULL COMMENT '更新时间',

PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT 'Mybatis Batch';

1.2 项目配置文件

细心的你可能已经发现，数据库url 后面跟了一段 rewriteBatchedStatements=true，有什么用呢？先不急，后面会介绍。

# 数据库配置

spring:

datasource:

url: jdbc:mysql://47.111.118.152:3306/mybatis?rewriteBatchedStatements=true

username: mybatis

password: password

driver-class-name: com.mysql.cj.jdbc.Driver

# mybatis

mybatis:

mapper-locations: classpath:mapper/*.xml

type-aliases-package: cn.van.mybatis.batch.entity

1.3 实体类

@Data

@Accessors(chain = true)

public class UserInfoBatchDO implements Serializable {

private Long id;

private String userName;

private String passWord;

private String nickName;

private String mobile;

private String email;

private LocalDateTime gmtCreate;

private LocalDateTime gmtUpdate;

}

1.4 UserInfoBatchMapper

public interface UserInfoBatchMapper {

/** 单条插入

* @param info

* @return

*/

int insert(UserInfoBatchDO info);

/**

* foreach 插入

* @param list

* @return

*/

int batchInsert(List list);

}

1.5 UserInfoBatchMapper.xml

insert into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update)

values (#{userName,jdbcType=VARCHAR}, #{passWord,jdbcType=VARCHAR},#{nickName,jdbcType=VARCHAR}, #{mobile,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{gmtCreate,jdbcType=TIMESTAMP}, #{gmtUpdate,jdbcType=TIMESTAMP})

insert into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update)

values

(#{item.userName,jdbcType=VARCHAR}, #{item.passWord,jdbcType=VARCHAR}, #{item.nickName,jdbcType=VARCHAR}, #{item.mobile,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR}, #{item.gmtCreate,jdbcType=TIMESTAMP}, #{item.gmtUpdate,jdbcType=TIMESTAMP})

1.6 预备数据

为了方便测试，抽离了几个变量，并进行提前加载。

private List list = new ArrayList<>();

private List lessList = new ArrayList<>();

private List lageList = new ArrayList<>();

private List warmList = new ArrayList<>();

// 计数工具

private StopWatch sw = new StopWatch();

为了方便组装数据，抽出了一个公共方法。

private List assemblyData(int count){

List list = new ArrayList<>();

UserInfoBatchDO userInfoDO;

for (int i = 0;i < count;i++){

userInfoDO = new UserInfoBatchDO()

.setUserName("Van")

.setNickName("风尘博客")

.setMobile("17098705205")

.setPassWord("password")

.setGmtUpdate(LocalDateTime.now());

http:// list.add(userInfoDO);

}

return list;

}

预热数据

@Before

public void assemblyData() {

list = assemblyData(200000);

lessList = assemblyData(2000);

lageList = assemblyData(1000000);

warmList = assemblyData(5);

}

二、反复执行单条插入语句

可能‘懒'的程序员会这么做，很简单，直接在原先单条insert语句上嵌套一个for循环。

2.1 对应 mapper 接口

int insert(UserInfoBatchDO info);

2.2 测试方法

因为这种方法太慢，所以数据降低到 2000 条

@Test

public void insert() {

log.info("【程序热身】");

for (UserInfoBatchDO userInfoBatchDO : warmList) {

userInfoBatchMapper.insert(userInfoBatchDO);

}

log.info("【热身结束】");

sw.start("反复执行单条插入语句");

// 这里插入 20w 条太慢了，所以我只插入了 2000 条

for (UserInfoBatchDO userInfoBatchDO : lessList) {

userInfoBatchMapper.insert(userInfoBatchDO);

}

sw.stop();

log.info("all cost info:{}",sw.prettyPrint());

}

2.3 执行时间

第一次

-----------------------------------------

ms % Task name

-----------------------------------------

59887 100% 反复执行单条插入语句

第二次

-----------------------------------------

ms % Task name

-----------------------------------------

64853 100% 反复执行单条插入语句

第三次

-----------------------------------------

ms % Task name

-----------------------------------------

58235 100% 反复执行单条插入语句

该方式插入2000 条数据，执行三次的平均时间：60991 ms。

三、foreach 拼接SQL

3.1 对应mapper 接口

int batchInsert(List list);

3.2 测试方法

该方式和下一种方式都采用20w条数据测试。

@Test

public void batchInsert() {

log.info("【程序热身】");

for (UserInfoBatchDO userInfoBatchDO : warmList) {

userInfoBatchMapper.insert(userInfoBatchDO);

}

log.info("【热身结束】");

sw.start("foreach 拼接 sql");

userInfoBatchMapper.batchInsert(list);

sw.stop();

log.info("all cost info:{}",sw.prettyPrint());

}

3.3 执行时间

第一次

-----------------------------------------

ms % Task name

-----------------------------------------

18835 100% foreach 拼接 sql

第二次

-----------------------------------------

ms % Task name

-----------------------------------------

17895 100% foreach 拼接 sql

第三次

-----------------------------------------

ms % Task name

-----------------------------------------

19827 100% foreach 拼接 sql

该方式插入20w 条数据，执行三次的平均时间：18852 ms。

四、批处理

该方式 mapper 和xml 复用了 2.1。

4.1 rewriteBatchedStatements 参数

我在测试一开始，发现改成 Mybatis Batch提交的方法都不起作用，实际上在插入的时候仍然是一条条记录的插，而且速度远不如原来 foreach 拼接SQL的方法，这是非常不科学的。

后来才发现要批量执行的话，连接URL字符串中需要新增一个参数：rewriteBatchedStatements=true

rewriteBatchedStatements参数介绍

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

4.2 批处理准备

手动注入 SqlSessionFactory

@Resource

private SqlSessionFactory sqlSessionFactory;

测试代码

@Test

public void processInsert() {

log.info("【程序热身】");

for (UserInfoBatchDO userInfoBatchDO : warmList) {

userInfoBatchMapper.insert(userInfoBatchDO);

}

log.info("【热身结束】");

sw.start("批处理执行 插入");

// 打开批处理

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);

UserInfoBatchMapper mapper = session.getMapper(UserInfoBatchMapper.class);

for (int i = 0,length = list.size(); i < length; i++) {

mapper.insert(list.get(i));

//每20000条提交一次防止内存溢出

if(i%20000==19999){

session.commit();

session.clearCache();

}

}

session.commit();

session.clearCache();

sw.stop();

log.info("all cost info:{}",sw.prettyPrint());

}

4.3 执行时间

第一次

-----------------------------------------

ms % Task name

-----------------------------------------

09346 100% 批处理执行 插入

第二次

-----------------------------------------

ms % Task name

-----------------------------------------

08890 100% 批处理执行 插入

第三次

-----------------------------------------

ms % Task name

-----------------------------------------

09042 100% 批处理执行 插入

该方式插入20w 条数据，执行三次的平均时间：9092 ms。

4.4 如果数据更大

当我把数据扩大到 100w 时，foreach 拼接 sql 的方式已经无法完成插入了，所以我只能测试批处理的插入时间。

测试时，仅需将 【4.2】测试代码中的 list 切成 lageList 测试即可。

第一次

-----------------------------------------

ms % Task name

-----------------------------------------

32419 100% 批处理执行 插入

第二次

-----------------------------------------

ms % Task name

-----------------------------------------

31935 100% 批处理执行 插入

第三次

-----------------------------------------

ms % Task name

-----------------------------------------

33048 100% 批处理执行 插入

该方式插入100w 条数据，执行三次的平均时间：32467 ms。

五、总结

批量插入方式

数据量

执行三次的平均时间

循环插入单条数据

2000

60991 ms

foreach 拼接sql

20w

18852 ms

批处理

20w

9092 ms

批处理

100w

32467 ms

循环插入单条数据虽然效率极低，但是代码量极少，数据量较小时可以使用，但是数据量较大禁止使用，效率太低了；

foreach 拼接sql的方式，使用时有大段的xml和sql语句要写，很容易出错，虽然效率尚可，但是真正应对大量数据的时候，依旧无法使用，所以不推荐使用；

批处理执行是有大数据量插入时推荐的做法，使用起来也比较方便。

【本文示例代码】

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。