MyBatis批量插入的幾種方式效率比較
更新時間：2021年09月15日 15:20:05 作者：VanFan
最近工作中遇到了解析excel,然后批量插入,發(fā)現(xiàn)這個插入時間比較長,所以想要進行一些優(yōu)化,下面這篇文章主要給大家介紹了關(guān)于MyBatis批量插入的幾種方式效率比較的相關(guān)資料,需要的朋友可以參考下
前言
批處理數(shù)據(jù)主要有三種方式：
- 反復執(zhí)行單條插入語句
- foreach 拼接 sql
- 批處理
一、前期準備
基于Spring Boot + Mysql，同時為了省略get/set，使用了lombok，詳見pom.xml。
1.1 表結(jié)構(gòu)
id 使用數(shù)據(jù)庫自增。
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 '創(chuàng)建時間',
`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 項目配置文件
細心的你可能已經(jīng)發(fā)現(xiàn)，數(shù)據(jù)庫url 后面跟了一段 rewriteBatchedStatements=true，有什么用呢？先不急，后面會介紹。
# 數(shù)據(jù)庫配置
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<UserInfoBatchDO> list);
}
1.5 UserInfoBatchMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.van.mybatis.batch.mapper.UserInfoBatchMapper">
<insert id="insert" parameterType="cn.van.mybatis.batch.entity.UserInfoBatchDO">
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>
<insert id="batchInsert">
insert into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update)
values
<foreach collection="list" item="item" separator=",">
(#{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})
</foreach>
</insert>
</mapper>
1.6 預備數(shù)據(jù)
為了方便測試，抽離了幾個變量，并進行提前加載。
private List<UserInfoBatchDO> list = new ArrayList<>();
private List<UserInfoBatchDO> lessList = new ArrayList<>();
private List<UserInfoBatchDO> lageList = new ArrayList<>();
private List<UserInfoBatchDO> warmList = new ArrayList<>();
// 計數(shù)工具
private StopWatch sw = new StopWatch();
為了方便組裝數(shù)據(jù)，抽出了一個公共方法。
private List<UserInfoBatchDO> assemblyData(int count){
List<UserInfoBatchDO> 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());
list.add(userInfoDO);
}
return list;
}
預熱數(shù)據(jù)
@Before
public void assemblyData() {
list = assemblyData(200000);
lessList = assemblyData(2000);
lageList = assemblyData(1000000);
warmList = assemblyData(5);
}
二、反復執(zhí)行單條插入語句
可能‘懶'的程序員會這么做，很簡單，直接在原先單條insert語句上嵌套一個for循環(huán)。
2.1 對應 mapper 接口
int insert(UserInfoBatchDO info);
2.2 測試方法
因為這種方法太慢，所以數(shù)據(jù)降低到 2000 條
@Test
public void insert() {
log.info("【程序熱身】");
for (UserInfoBatchDO userInfoBatchDO : warmList) {
userInfoBatchMapper.insert(userInfoBatchDO);
}
log.info("【熱身結(jié)束】");
sw.start("反復執(zhí)行單條插入語句");
// 這里插入 20w 條太慢了，所以我只插入了 2000 條
for (UserInfoBatchDO userInfoBatchDO : lessList) {
userInfoBatchMapper.insert(userInfoBatchDO);
}
sw.stop();
log.info("all cost info:{}",sw.prettyPrint());
}
2.3 執(zhí)行時間
第一次
----------------------------------------- ms % Task name ----------------------------------------- 59887 100% 反復執(zhí)行單條插入語句
第二次
----------------------------------------- ms % Task name ----------------------------------------- 64853 100% 反復執(zhí)行單條插入語句
第三次
----------------------------------------- ms % Task name ----------------------------------------- 58235 100% 反復執(zhí)行單條插入語句
該方式插入2000 條數(shù)據(jù)，執(zhí)行三次的平均時間：60991 ms。
三、foreach 拼接SQL
3.1 對應mapper 接口
int batchInsert(List<UserInfoBatchDO> list);
3.2 測試方法
該方式和下一種方式都采用20w條數(shù)據(jù)測試。
@Test
public void batchInsert() {
log.info("【程序熱身】");
for (UserInfoBatchDO userInfoBatchDO : warmList) {
userInfoBatchMapper.insert(userInfoBatchDO);
}
log.info("【熱身結(jié)束】");
sw.start("foreach 拼接 sql");
userInfoBatchMapper.batchInsert(list);
sw.stop();
log.info("all cost info:{}",sw.prettyPrint());
}
3.3 執(zhí)行時間
第一次
----------------------------------------- ms % Task name ----------------------------------------- 18835 100% foreach 拼接 sql
第二次
----------------------------------------- ms % Task name ----------------------------------------- 17895 100% foreach 拼接 sql
第三次
----------------------------------------- ms % Task name ----------------------------------------- 19827 100% foreach 拼接 sql
該方式插入20w 條數(shù)據(jù)，執(zhí)行三次的平均時間：18852 ms。
四、批處理
該方式 mapper 和xml 復用了 2.1。
4.1 rewriteBatchedStatements 參數(shù)
我在測試一開始，發(fā)現(xiàn)改成 Mybatis Batch提交的方法都不起作用，實際上在插入的時候仍然是一條條記錄的插，而且速度遠不如原來 foreach 拼接SQL的方法，這是非常不科學的。
后來才發(fā)現(xiàn)要批量執(zhí)行的話，連接URL字符串中需要新增一個參數(shù)：rewriteBatchedStatements=true
- rewriteBatchedStatements參數(shù)介紹
MySql的JDBC連接的url中要加rewriteBatchedStatements參數(shù)，并保證5.1.13以上版本的驅(qū)動，才能實現(xiàn)高性能的批量插入。MySql JDBC驅(qū)動在默認情況下會無視executeBatch()語句，把我們期望批量執(zhí)行的一組sql語句拆散，一條一條地發(fā)給MySql數(shù)據(jù)庫，批量插入實際上是單條插入，直接造成較低的性能。只有把rewriteBatchedStatements參數(shù)置為true, 驅(qū)動才會幫你批量執(zhí)行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("【熱身結(jié)束】");
sw.start("批處理執(zhí)行 插入");
// 打開批處理
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條提交一次防止內(nèi)存溢出
if(i%20000==19999){
session.commit();
session.clearCache();
}
}
session.commit();
session.clearCache();
sw.stop();
log.info("all cost info:{}",sw.prettyPrint());
}
4.3 執(zhí)行時間
第一次
----------------------------------------- ms % Task name ----------------------------------------- 09346 100% 批處理執(zhí)行 插入
第二次
----------------------------------------- ms % Task name ----------------------------------------- 08890 100% 批處理執(zhí)行 插入
第三次
----------------------------------------- ms % Task name ----------------------------------------- 09042 100% 批處理執(zhí)行 插入
該方式插入20w 條數(shù)據(jù)，執(zhí)行三次的平均時間：9092 ms。
4.4 如果數(shù)據(jù)更大
當我把數(shù)據(jù)擴大到 100w 時，foreach 拼接 sql 的方式已經(jīng)無法完成插入了，所以我只能測試批處理的插入時間。
測試時，僅需將 【4.2】測試代碼中的 list 切成 lageList 測試即可。
第一次
----------------------------------------- ms % Task name ----------------------------------------- 32419 100% 批處理執(zhí)行 插入
第二次
----------------------------------------- ms % Task name ----------------------------------------- 31935 100% 批處理執(zhí)行 插入
第三次
----------------------------------------- ms % Task name ----------------------------------------- 33048 100% 批處理執(zhí)行 插入
該方式插入100w 條數(shù)據(jù)，執(zhí)行三次的平均時間：32467 ms。
五、總結(jié)
| 批量插入方式 | 數(shù)據(jù)量 | 執(zhí)行三次的平均時間 |
|---|---|---|
| 循環(huán)插入單條數(shù)據(jù) | 2000 | 60991 ms |
| foreach 拼接sql | 20w | 18852 ms |
| 批處理 | 20w | 9092 ms |
| 批處理 | 100w | 32467 ms |
- 循環(huán)插入單條數(shù)據(jù)雖然效率極低，但是代碼量極少，數(shù)據(jù)量較小時可以使用，但是數(shù)據(jù)量較大禁止使用，效率太低了；
- foreach 拼接sql的方式，使用時有大段的xml和sql語句要寫，很容易出錯，雖然效率尚可，但是真正應對大量數(shù)據(jù)的時候，依舊無法使用，所以不推薦使用；
- 批處理執(zhí)行是有大數(shù)據(jù)量插入時推薦的做法，使用起來也比較方便。
【本文示例代碼】
到此這篇關(guān)于MyBatis批量插入的幾種方式效率比較的文章就介紹到這了,更多相關(guān)MyBatis批量插入效率比較內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
Spring Boot與Kotlin處理Web表單提交的方法
本篇文章主要介紹了Spring Boot 與 Kotlin 處理Web表單提交的方法，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-01-01
這篇文章主要介紹了SpringCloud鏈路追蹤組件Sleuth配置方法解析,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-03-03
Java實現(xiàn)字符串的分割(基于String.split()方法)
Java中的我們可以利用split把字符串按照指定的分割符進行分割,然后返回字符串數(shù)組,下面這篇文章主要給大家介紹了關(guān)于Java實現(xiàn)字符串的分割的相關(guān)資料,是基于jDK1.8版本中的String.split()方法,需要的朋友可以參考下2022-09-09
Java如何解決發(fā)送Post請求報Stream?closed問題
這篇文章主要介紹了Java如何解決發(fā)送Post請求報Stream?closed問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-06-06
這篇文章主要為大家介紹了SpringBoot注入Bean的幾種方式示例詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步早日升職加薪2022-03-03
這篇文章主要介紹了Java使用原型模式展現(xiàn)每日生活應用案例,較為詳細的分析了原型模式的概念、原理及Java使用原型模式展現(xiàn)每日生活案例的相關(guān)操作步驟與注意事項,需要的朋友可以參考下2018-05-05
BigDecimal divide除法除不盡報錯的問題及解決
這篇文章主要介紹了BigDecimal divide除法除不盡報錯的問題及解決方案，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-06-06
SpringBoot多數(shù)據(jù)源配置方式以及報錯問題的解決
這篇文章主要介紹了SpringBoot多數(shù)據(jù)源配置方式以及報錯問題的解決方案,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-07-07
這篇文章主要介紹了IDEA之web項目導入jar包方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05
這篇文章主要給大家總結(jié)SpringBoot注入Bean的四種方式,啟動類注入Bean,啟動類掃描@ComponentScan,啟動類@EnableConfigurationProperties以及啟動類@Import這四種方式,文章通過代碼示例講解非常詳細,需要的朋友可以參考下2023-11-11