MyBatis批量插入的幾種方式效率比較
前言
批處理數據主要有三種方式:
- 反復執行單條插入語句
- 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<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 預備數據
為瞭方便測試,抽離瞭幾個變量,並進行提前加載。
private List<UserInfoBatchDO> list = new ArrayList<>(); private List<UserInfoBatchDO> lessList = new ArrayList<>(); private List<UserInfoBatchDO> lageList = new ArrayList<>(); private List<UserInfoBatchDO> warmList = new ArrayList<>(); // 計數工具 private StopWatch sw = new StopWatch();
為瞭方便組裝數據,抽出瞭一個公共方法。
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; }
預熱數據
@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<UserInfoBatchDO> 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數據庫,批量插入實際上是單條插入,直接造成較低的性能。隻有把rewriteBatchedStatements參數置為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語句要寫,很容易出錯,雖然效率尚可,但是真正應對大量數據的時候,依舊無法使用,所以不推薦使用;
- 批處理執行是有大數據量插入時推薦的做法,使用起來也比較方便。
【本文示例代碼】
到此這篇關於MyBatis批量插入的幾種方式效率比較的文章就介紹到這瞭,更多相關MyBatis批量插入效率比較內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- 基於Java SSM實現Excel數據批量導入
- mybatis實現批量插入並返回主鍵(xml和註解兩種方法)
- mybatis update更新字段的使用操作
- mybatis條件語句中帶數組參數的處理
- MyBatis批量插入大量數據(1w以上)