【MyBatis】幾種批量插入效率的比較

風塵部落格發表於2021-09-14

批處理資料主要有三種方式：

反覆執行單條插入語句
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語句要寫，很容易出錯，雖然效率尚可，但是真正應對大量資料的時候，依舊無法使用，所以不推薦使用；
批處理執行是有大資料量插入時推薦的做法，使用起來也比較方便。

【本文示例程式碼】

幾種排序的比較
2018-09-12
排序
mybatis插入資料、批量插入資料
2021-12-08
MyBatis
MySQL：JDBC批量插入資料的效率
2021-08-16
MySqlJDBC
幾種中文字型的比較
2024-04-25
分割陣列的幾種方法比較
2019-03-12
陣列
ETL 幾種工具的比較(Kettle,Talend,Informatica )
2019-05-13
ORM
MyBatis Plus 批量資料插入功能，yyds！
2021-09-26
MyBatis
使用Mybatis批量插入大量資料的實踐
2022-01-03
MyBatis
Java幾種常用JSON庫效能比較
2019-04-19
JavaJSON
集中式Web的幾種替代方案比較
2022-11-08
Web
幾種分散式呼叫鏈監控元件的實踐與比較（二）比較
2019-03-04
分散式元件
分享幾個比較提高工作效率的軟體
2019-01-09
python 批量resize效能比較
2019-03-04
Python
幾種非易失性儲存器的比較
2020-10-23
Hibernate與mybatis比較
2018-12-21
MyBatis
Mybatis批量更新三種方式
2019-01-19
MyBatis
MyBatis中批量插入資料，多重forEach迴圈
2018-08-14
MyBatis
Java中的幾種Kafka客戶端比較介紹
2022-01-23
JavaKafka客戶端
批量插入 1 萬條資料，DB 查詢構造器和原生 SQL 效能比較
2020-03-17
SQL
訪問vector元素方法的效率比較
2019-03-09
Java通過Mybatis實現批量插入資料到Oracle中
2018-10-15
JavaMyBatisOracle
Cesium 比較常用的幾個方法
2024-09-15
2023版：深度比較幾種.NET Excel匯出庫的效能差異
2023-09-22
Excel
CArray CList CMap 插入與遍歷效率對比
2021-06-12
幾個比較火的BI分析工具
2020-09-23
HTTP協議幾個版本的比較
2018-05-28
HTTP協議
mybatis-plus批量插入saveBatch太慢？我願意稱rewriteBatchedStatements為神
2021-05-25
MyBatis
Java書籤 #MyBatis之批量插入並返回自增主鍵id
2020-10-16
JavaMyBatis
Springboot整合Mybatis-plus（比較詳細）
2020-10-24
Spring BootMyBatis
Java中陣列判斷元素存在幾種方式比較詳解
2018-07-10
Java陣列
LINUX 使用批量刪除檔案的幾種方法
2019-06-03
Linux
MySql避免重複插入記錄的幾種方法
2021-09-09
MySql
HashMap，LinkedHashMap，TreeMap讀取大量資料效率的比較
2019-03-15
HashMap
四種在Javascript比較物件的方法
2021-07-25
JavaScript物件
還用foreach在MyBatis批量插入資料，伺服器還沒崩？
2021-12-01
MyBatis伺服器
分散式中幾種服務註冊與發現元件的原理與比較
2018-10-05
分散式元件
通過append hint來插入資料，演示它和普通插入資料的效能比較。
2019-01-08
APP
mybatis批次插入
2024-04-19
MyBatis