MyBatis實現(xiàn)插入大量數(shù)據(jù)方法詳解

更新時間：2022年11月21日 10:27:35 作者：小學(xué)生波波

最近在公司項目開發(fā)中遇到批量數(shù)據(jù)插入或者更新,下面這篇文章主要給大家介紹了關(guān)于MyBatis實現(xiàn)批量插入的相關(guān)資料,需要的朋友可以參考下

1、前言

在開發(fā)過程中，有時我們會碰到將大批量的數(shù)據(jù)入庫的場景，那么我們一般有下面三種方式入庫：

ExecutorType.BATCH批處理方式插入
foreach循環(huán)標簽插入
MyBatisPlus自帶的saveBatch批量新增方法

下面我們用一個案例來測試一下，看下三種方式哪種效率最好

2、案例說明

現(xiàn)在我數(shù)據(jù)庫新建一張表t_user，建表語句如下：

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵ID',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年齡',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手機號',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用戶表' ROW_FORMAT = DYNAMIC;

我這里MySQL數(shù)據(jù)庫版本是5.5.28

然后我用上面三種方式分別向t_user表中存5萬條數(shù)據(jù)，每次存數(shù)據(jù)前，先清空一下表數(shù)據(jù)，使用下面語句快速清除表數(shù)據(jù)：

truncate table t_user;

每種方式我都測試5次，然后每種方式我都計算一個耗時平均值，看哪種方式耗時最小

3、編碼

這是我的基礎(chǔ)項目：https://gitee.com/colinWu_java/spring-boot-base.git

我會在此基礎(chǔ)項目上做測試

3.1、ExecutorType.BATCH批處理方式插入

ExecutorType.BATCH介紹：

Mybatis內(nèi)置的ExecutorType有3種，SIMPLE、REUSE、BATCH，默認的是simple，該模式下它為每個語句的執(zhí)行創(chuàng)建一個新的預(yù)處理語句，單條提交sql
而batch模式重復(fù)使用已經(jīng)預(yù)處理的語句，并且批量執(zhí)行所有更新語句，顯然batch性能將更優(yōu)；但batch模式也有自己的問題，比如在Insert操作時，在事務(wù)沒有提交之前，是沒有辦法獲取到自增的id，這在某型情形下是不符合業(yè)務(wù)要求的
通過批處理的方式，我們就可以在 JDBC 客戶端緩存多條 SQL 語句，然后在 flush 或緩存滿的時候，將多條 SQL 語句打包發(fā)送到數(shù)據(jù)庫執(zhí)行，這樣就可以有效地降低上述兩方面的損耗，從而提高系統(tǒng)性能

下面開始編碼

在UserController新增下面接口

/**
     * 測試大批量數(shù)據(jù)插入數(shù)據(jù)庫
     * 方式1：用一個 for 循環(huán)，把數(shù)據(jù)一條一條的插入
     * @return
     */
@GetMapping("/insertUser1/{count}")
public JSONResult insertUser1(@PathVariable("count") Integer count){
    return userService.insertUser1(count);
}

userService代碼：

public JSONResult insertUser1(Integer count) {
    //如果自動提交設(shè)置為true，將無法控制提交的條數(shù)。所以我這里設(shè)置為false，，改為統(tǒng)一提交
    SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    UserMapper uMapper = session.getMapper(UserMapper.class);
    //獲取測試用戶集合數(shù)據(jù)
    List<User> userList = Tools.getUserList(count);
    int commitCount = 5000;//每次提交的數(shù)量條數(shù)
    long startTime = System.currentTimeMillis();
    for(int i=0; i<userList.size(); i++){
        uMapper.addUserOne(userList.get(i));
        if (i != 0 && i % commitCount == 0) {
            session.commit();
        }
    }
    session.commit();
    long endTime = System.currentTimeMillis();
    log.info("方式1耗時：{}", (endTime - startTime));
    return JSONResult.success();
}

在UserMapper中新增addUserOne方法：

Integer addUserOne(User user);

對應(yīng)xml代碼：

<insert id="addUserOne">
    insert into t_user (name, age, phone) values (#{name}, #{age}, #{phone})
</insert>

getUserList方法代碼如下，就是獲取指定數(shù)量的測試用戶數(shù)據(jù)而已：

/**
     * 獲取指定數(shù)量的用戶測試對象
     * @param count 數(shù)量
     * @return
     */
public static List<User> getUserList(int count){
    List<User> userList = new ArrayList<>();
    User user = null;
    for(int i=1; i<=count; i++){
        user = new User();
        user.setName("王天霸" + i + "號");
        user.setAge(i);
        user.setAge(i);
        user.setPhone("18022222222");
        userList.add(user);
    }
    return userList;
}

注意事項：

如果單次提交給MySQL數(shù)據(jù)庫的數(shù)據(jù)量太大的話，可能會報如下錯誤：

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (204444558 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.

出現(xiàn)這個錯誤的原因是：

大數(shù)據(jù)量的插入或更新會被 max_allowed_packet 參數(shù)限制，導(dǎo)致失敗，因為MySQL根據(jù)配置文件會限制server允許接收的數(shù)據(jù)包大小

解決方案：

修改MySQL的配置參數(shù)

打開Mysql命令客戶端：

然后輸入密碼之后輸入下面命令：

show VARIABLES like '%max_allowed_packet%';

結(jié)果：

用這個值除以1024*1024等于200M，那我這里就是設(shè)置的200M，根據(jù)實際情況設(shè)置該值，執(zhí)行如下命令即可：

set global max_allowed_packet = 200*1024*1024;

設(shè)置完成之后，需要關(guān)閉MySQL服務(wù)，然后再重啟（注意，這里最大設(shè)置為：1G）

重啟成功后，需要關(guān)掉上面的MySQL命令客戶端，再重新打開一次，查看是否設(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都有效

url: jdbc:mysql://127.0.0.1:3306/test1?allowMultiQueries=true&rewriteBatchedStatements=true
//allowMultiQueries=true，允許一次性執(zhí)行多條SQL,批量插入時必須在連接地址后面加allowMultiQueries=true這個參數(shù)
//rewriteBatchedStatements=true，批量將數(shù)據(jù)傳給MySQL，數(shù)據(jù)庫會更高性能的執(zhí)行批量處理，MySQL數(shù)據(jù)庫版本在5.1.13以上，才能實現(xiàn)高性能的批量插入

3.2、foreach循環(huán)標簽插入

UserController新增方法：

/**
     * 測試大批量數(shù)據(jù)插入數(shù)據(jù)庫
     * 方式2：foreach標簽批量插入
     * @return
     */
@GetMapping("/insertUser2/{count}")
public JSONResult insertUser2(@PathVariable("count") Integer count){
    return userService.insertUser2(count);
}

insertUser2代碼：

public JSONResult insertUser2(Integer count) {
    //獲取測試用戶集合數(shù)據(jù)
    List<User> userList = Tools.getUserList(count);
    int countItem = 5000;//每次提交的記錄條數(shù)
    int userSize = userList.size();
    List<User> userListTemp = new ArrayList<>();
    long startTime = System.currentTimeMillis();
    for (int i = 0, n=userSize; i < n; i++) {
        User user= userList.get(i);
        userListTemp.add(user);
        if ((i>0 && i % countItem == 0) || i == userSize - 1) {
            //每5000條記錄提交一次
            userMapper.insertUserBatch(userListTemp);
            userListTemp.clear();
        }
    }
    long endTime = System.currentTimeMillis();
    log.info("方式2耗時：{}", (endTime - startTime));
    return JSONResult.success();
}

UserMapper的insertUserBatch方法：

void insertUserBatch(@Param("userList") List<User> userList);

對應(yīng)xml代碼：

<insert id="insertUserBatch">
    insert into t_user (name, age, phone) values
    <foreach collection="userList" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.phone})
    </foreach>
</insert>

3.3、MyBatisPlus自帶的saveBatch批量新增方法

UserController新增方法：

@Autowired
public UserService userService;
/**
     * 測試大批量數(shù)據(jù)插入數(shù)據(jù)庫
     * 方式3：mybatisplus自帶的saveBatch批量新增方法
     * @return
     */
@GetMapping("/insertUser3/{count}")
public JSONResult insertUser3(@PathVariable("count") Integer count){
    //獲取測試用戶集合數(shù)據(jù)
    List<User> userList = Tools.getUserList(count);
    long startTime = System.currentTimeMillis();
    userService.saveBatch(userList, 5000);//每次往數(shù)據(jù)庫提交5000條數(shù)據(jù)
    long endTime = System.currentTimeMillis();
    log.info("方式3耗時：{}", (endTime - startTime));
    return JSONResult.success();
}