mybatis批量插入資料三種方式性能對比

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

反複執行單條插入語句
foreach 拼接 sql
批處理

一、前期準備

基于Spring Boot + Mysql。

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://localhost:3306/mybatis?rewriteBatchedStatements=true
    username: mybatis
    password: password
    driver-class-name: com.mysql.jdbc.Driver
# mybatis
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.dcits.entity

1.3 實體類

package com.dcits.entity;

import java.io.Serializable;
import java.time.LocalDateTime;

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;

    public UserInfoBatchDO() {
    }

    public UserInfoBatchDO(Long id, String userName, String passWord, String nickName, String mobile, String email, LocalDateTime gmtCreate, LocalDateTime gmtUpdate) {
        this.id = id;
        this.userName = userName;
        this.passWord = passWord;
        this.nickName = nickName;
        this.mobile = mobile;
        this.email = email;
        this.gmtCreate = gmtCreate;
        this.gmtUpdate = gmtUpdate;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassWord() {
        return passWord;
    }

    public void setPassWord(String passWord) {
        this.passWord = passWord;
    }

    public String getNickName() {
        return nickName;
    }

    public void setNickName(String nickName) {
        this.nickName = nickName;
    }

    public String getMobile() {
        return mobile;
    }

    public void setMobile(String mobile) {
        this.mobile = mobile;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public LocalDateTime getGmtCreate() {
        return gmtCreate;
    }

    public void setGmtCreate(LocalDateTime gmtCreate) {
        this.gmtCreate = gmtCreate;
    }

    public LocalDateTime getGmtUpdate() {
        return gmtUpdate;
    }

    public void setGmtUpdate(LocalDateTime gmtUpdate) {
        this.gmtUpdate = gmtUpdate;
    }

    @Override
    public String toString() {
        return "UserInfoBatchDO{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", passWord='" + passWord + '\'' +
                ", nickName='" + nickName + '\'' +
                ", mobile='" + mobile + '\'' +
                ", email='" + email + '\'' +
                ", gmtCreate=" + gmtCreate +
                ", gmtUpdate=" + gmtUpdate +
                '}';
    }
}

1.4 UserInfoBatchMapper

package com.dcits.mapper;

import com.dcits.entity.UserInfoBatchDO;

import java.util.List;

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="com.dcits.mapper.UserInfoBatchMapper">


    <insert id="insert" parameterType="com.dcits.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();
            userInfoDO.setUserName("Van");
            userInfoDO.setNickName("風塵部落格");
            userInfoDO.setMobile("17098705205");
            userInfoDO.setEmail("[email protected]");
            userInfoDO.setPassWord("password");
            userInfoDO.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 接口

2.2 測試方法

因為這種方法太慢，是以資料降低到 2000 條。

@Test
    public void insert() {
        logger.info("【程式熱身】");
        for (UserInfoBatchDO userInfoBatchDO : warmList) {//5
            userInfoBatchMapper.insert(userInfoBatchDO);
        }
        logger.info("【熱身結束】");
        sw.start("反複執行單條插入語句");
        // 這裡插入 20w 條太慢了，是以我隻插入了 2000 條
        for (UserInfoBatchDO userInfoBatchDO : lessList) {//2000
            userInfoBatchMapper.insert(userInfoBatchDO);
        }
        sw.stop();
        logger.info("all cost info:{}",sw.prettyPrint());
    }

2.3 執行時間

第一次

mybatis批量插入資料三種方式性能對比
第二次

mybatis批量插入資料三種方式性能對比

第三次

mybatis批量插入資料三種方式性能對比

該方式插入2000 條資料，執行三次的平均時間：87021539698。

三、foreach 拼接SQL

3.1 對應mapper 接口

3.2 測試方法

@Test
    public void batchInsert() {
        logger.info("【程式熱身】 批量方式之一 foreach ");
        for (UserInfoBatchDO userInfoBatchDO : warmList) {//5
            userInfoBatchMapper.insert(userInfoBatchDO);
        }
        logger.info("【熱身結束】 批量方式之一 foreach ");
        sw.start("foreach 拼接 sql");
        userInfoBatchMapper.batchInsert(list);//200000
        sw.stop();
        logger.info("all cost info:{}",sw.prettyPrint());
    }

3.3 執行時間

第一次

mybatis批量插入資料三種方式性能對比

第二次

mybatis批量插入資料三種方式性能對比

第三次

mybatis批量插入資料三種方式性能對比

該方式插入20w 條資料，執行三次的平均時間：25154730120。

四、批處理

該方式 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() {
        logger.info("【程式熱身】 批量方式之二 foreach");
        for (UserInfoBatchDO userInfoBatchDO : warmList) {
            userInfoBatchMapper.insert(userInfoBatchDO);
        }
        logger.info("【熱身結束】 批量方式之二 foreach");
        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();
        logger.info("all cost info:{}",sw.prettyPrint());
    }

4.3 執行時間

第一次

mybatis批量插入資料三種方式性能對比
第二次

mybatis批量插入資料三種方式性能對比
第三次

mybatis批量插入資料三種方式性能對比

該方式插入20w 條資料，執行三次的平均時間：12656499721。

4.4 如果資料更大

當我把資料擴大到 100w 時，foreach 拼接 sql 的方式已經無法完成插入了，是以我隻能測試批處理的插入時間。

測試時，僅需将【4.2】測試代碼中的 list 切成 lageList 測試即可。

第一次

mybatis批量插入資料三種方式性能對比
第二次

mybatis批量插入資料三種方式性能對比

第三次

mybatis批量插入資料三種方式性能對比
該方式插入100w 條資料，執行三次的平均時間：43709821640。

五、總結

mybatis批量插入資料三種方式性能對比

循環插入單條資料雖然效率極低，但是代碼量極少，資料量較小時可以使用，但是資料量較大禁止使用，效率太低了；
foreach 拼接sql的方式，使用時有大段的xml和sql語句要寫，很容易出錯，雖然效率尚可，但是真正應對大量資料的時候，依舊無法使用，是以不推薦使用；
批處理執行是有大資料量插入時推薦的做法，使用起來也比較友善。

源代碼：

https://download.csdn.net/download/lwh_zfj/26362909

參考網址

https://www.toutiao.com/a7010611732557070861/?log_from=8699e9907a142_1632825538299

mybatis批量插入資料三種方式性能對比

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

一、前期準備

1.1 表結構

1.2 項目配置檔案

1.3 實體類

1.4 UserInfoBatchMapper

1.5 UserInfoBatchMapper.xml

1.6 預備資料

二、反複執行單條插入語句

2.1 對應 mapper 接口

2.2 測試方法

2.3 執行時間

三、foreach 拼接SQL

3.1 對應mapper 接口

3.2 測試方法

3.3 執行時間

四、批處理

4.1 rewriteBatchedStatements 參數

4.2 批處理準備

4.3 執行時間

4.4 如果資料更大

五、總結

繼續閱讀

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

用mybatis的generator插件在項目中自動生成dao及entity

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

從mybatis到mybatis plus(mp)

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method