坑已踩好，MyBatis 幾種批量插入性能比較
點(diǎn)擊上方藍(lán)色字體，選擇“設(shè)為星標(biāo)”
批處理數(shù)據(jù)主要有三種方式：
反復(fù)執(zhí)行單條插入語(yǔ)句
foreach 拼接 sql
批處理
一、前期準(zhǔn)備
基于Spring Boot + Mysql，同時(shí)為了省略get/set，使用了lombok，詳見pom.xml。
1.1 表結(jié)構(gòu)
id使用數(shù)據(jù)庫(kù)自增。
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 '手機(jī)號(hào)',`email` varchar(100) DEFAULT NULL COMMENT '郵箱地址',`gmt_create` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',`gmt_update` timestamp NULL DEFAULT NULL COMMENT '更新時(shí)間',PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT 'Mybatis Batch';
1.2 項(xiàng)目配置文件
細(xì)心的你可能已經(jīng)發(fā)現(xiàn)，數(shù)據(jù)庫(kù)url 后面跟了一段 rewriteBatchedStatements=true，有什么用呢？先不急，后面會(huì)介紹。
# 數(shù)據(jù)庫(kù)配置spring:datasource:url: jdbc:mysql://47.111.118.152:3306/mybatis?rewriteBatchedStatements=trueusername: mybatispassword: password: com.mysql.cj.jdbc.Driver# mybatismybatis:: classpath:mapper/*.xml: cn.van.mybatis.batch.entity
1.3 實(shí)體類
(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
<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 預(yù)備數(shù)據(jù)
為了方便測(cè)試，抽離了幾個(gè)變量，并進(jìn)行提前加載。
private List<UserInfoBatchDO> list = new ArrayList<>();private List<UserInfoBatchDO> lessList = new ArrayList<>();private List<UserInfoBatchDO> lageList = new ArrayList<>();private List<UserInfoBatchDO> warmList = new ArrayList<>();// 計(jì)數(shù)工具private StopWatch sw = new StopWatch();
為了方便組裝數(shù)據(jù)，抽出了一個(gè)公共方法。
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("風(fēng)塵博客").setMobile("17098705205").setPassWord("password").setGmtUpdate(LocalDateTime.now());list.add(userInfoDO);}return list;}
預(yù)熱數(shù)據(jù)
@Beforepublic void assemblyData() {list = assemblyData(200000);lessList = assemblyData(2000);lageList = assemblyData(1000000);warmList = assemblyData(5);}
二、反復(fù)執(zhí)行單條插入語(yǔ)句
可能‘懶’的程序員會(huì)這么做，很簡(jiǎn)單，直接在原先單條
insert語(yǔ)句上嵌套一個(gè)for循環(huán)。
2.1 對(duì)應(yīng) mapper 接口
int insert(UserInfoBatchDO info);2.2 測(cè)試方法
因?yàn)檫@種方法太慢，所以數(shù)據(jù)降低到
2000條
@Testpublic void insert() {log.info("【程序熱身】");for (UserInfoBatchDO userInfoBatchDO : warmList) {userInfoBatchMapper.insert(userInfoBatchDO);}log.info("【熱身結(jié)束】");sw.start("反復(fù)執(zhí)行單條插入語(yǔ)句");// 這里插入 20w 條太慢了，所以我只插入了 2000 條for (UserInfoBatchDO userInfoBatchDO : lessList) {userInfoBatchMapper.insert(userInfoBatchDO);}sw.stop();log.info("all cost info:{}",sw.prettyPrint());}
2.3 執(zhí)行時(shí)間
第一次
-----------------------------------------ms % Task name-----------------------------------------59887 100% 反復(fù)執(zhí)行單條插入語(yǔ)句
第二次
-----------------------------------------ms % Task name-----------------------------------------64853 100% 反復(fù)執(zhí)行單條插入語(yǔ)句
第三次
-----------------------------------------ms % Task name-----------------------------------------58235 100% 反復(fù)執(zhí)行單條插入語(yǔ)句
該方式插入2000 條數(shù)據(jù)，執(zhí)行三次的平均時(shí)間：60991 ms。
三、foreach 拼接SQL
3.1 對(duì)應(yīng)mapper 接口
int batchInsert(List<UserInfoBatchDO> list);3.2 測(cè)試方法
該方式和下一種方式都采用
20w條數(shù)據(jù)測(cè)試。
@Testpublic 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í)行時(shí)間
第一次
-----------------------------------------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í)行三次的平均時(shí)間：18852 ms。
四、批處理
該方式
mapper和xml復(fù)用了2.1。
4.1 rewriteBatchedStatements 參數(shù)
我在測(cè)試一開始，發(fā)現(xiàn)改成
Mybatis Batch提交的方法都不起作用，實(shí)際上在插入的時(shí)候仍然是一條條記錄的插，而且速度遠(yuǎn)不如原來(lái)foreach拼接SQL的方法，這是非常不科學(xué)的。
后來(lái)才發(fā)現(xiàn)要批量執(zhí)行的話，連接URL字符串中需要新增一個(gè)參數(shù)：rewriteBatchedStatements=true
rewriteBatchedStatements參數(shù)介紹
MySql的JDBC連接的url中要加rewriteBatchedStatements參數(shù)，并保證5.1.13以上版本的驅(qū)動(dòng)，才能實(shí)現(xiàn)高性能的批量插入。MySql JDBC驅(qū)動(dòng)在默認(rèn)情況下會(huì)無(wú)視executeBatch()語(yǔ)句，把我們期望批量執(zhí)行的一組sql語(yǔ)句拆散，一條一條地發(fā)給MySql數(shù)據(jù)庫(kù)，批量插入實(shí)際上是單條插入，直接造成較低的性能。只有把rewriteBatchedStatements參數(shù)置為true, 驅(qū)動(dòng)才會(huì)幫你批量執(zhí)行SQL。這個(gè)選項(xiàng)對(duì)INSERT/UPDATE/DELETE都有效。
4.2 批處理準(zhǔn)備
手動(dòng)注入 SqlSessionFactory
@Resourceprivate SqlSessionFactory sqlSessionFactory;
測(cè)試代碼
@Testpublic 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í)行時(shí)間
第一次
-----------------------------------------ms % Task name-----------------------------------------09346 100% 批處理執(zhí)行 插入
第二次
-----------------------------------------ms % Task name-----------------------------------------08890 100% 批處理執(zhí)行 插入
第三次
-----------------------------------------ms % Task name-----------------------------------------09042 100% 批處理執(zhí)行 插入
該方式插入20w 條數(shù)據(jù)，執(zhí)行三次的平均時(shí)間：9092 ms。
4.4 如果數(shù)據(jù)更大
當(dāng)我把數(shù)據(jù)擴(kuò)大到 100w 時(shí)，foreach 拼接 sql 的方式已經(jīng)無(wú)法完成插入了，所以我只能測(cè)試批處理的插入時(shí)間。
測(cè)試時(shí)，僅需將 【4.2】測(cè)試代碼中的
list切成lageList測(cè)試即可。
第一次
-----------------------------------------ms % Task name-----------------------------------------32419 100% 批處理執(zhí)行 插入
第二次
-----------------------------------------ms % Task name-----------------------------------------31935 100% 批處理執(zhí)行 插入
第三次
-----------------------------------------ms % Task name-----------------------------------------33048 100% 批處理執(zhí)行 插入
該方式插入100w 條數(shù)據(jù)，執(zhí)行三次的平均時(shí)間：32467 ms。
五、總結(jié)
| 批量插入方式 | 數(shù)據(jù)量 | 執(zhí)行三次的平均時(shí)間 |
|---|---|---|
| 循環(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í)可以使用，但是數(shù)據(jù)量較大禁止使用，效率太低了；
foreach 拼接sql的方式，使用時(shí)有大段的xml和sql語(yǔ)句要寫，很容易出錯(cuò)，雖然效率尚可，但是真正應(yīng)對(duì)大量數(shù)據(jù)的時(shí)候，依舊無(wú)法使用，所以不推薦使用；
批處理執(zhí)行是有大數(shù)據(jù)量插入時(shí)推薦的做法，使用起來(lái)也比較方便。
作者：VanFan
鏈接：https://juejin.cn/post/7007608714093920286
來(lái)源：掘金
后臺(tái)回復(fù) 學(xué)習(xí)資料 領(lǐng)取學(xué)習(xí)視頻
如有收獲，點(diǎn)個(gè)在看，誠(chéng)摯感謝