【DB系列】Mybatis之批量插入的幾種姿勢

語言: CN / TW / HK

時間 2022-01-11 23:56:08 一灰灰Blog

在日常的業務需求開發過程中，批量插入屬於非常常見的case，在mybatis的寫法中，一般有下面三種使用姿勢

<foreach>

I. 環境配置

我們使用SpringBoot + Mybatis + MySql來搭建例項demo

springboot: 2.2.0.RELEASE
mysql: 5.7.22

1. 專案配置

<dependencies>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
</dependencies>

核心的依賴 mybatis-spring-boot-starter ，至於版本選擇，到mvn倉庫中，找最新的

另外一個不可獲取的就是db配置資訊， appliaction.yml

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
    username: root
    password:

2. 資料庫表

用於測試的資料庫

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '使用者名稱',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '錢',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;

II. 批量插入

1. 單個插入，批量呼叫方式

這種方式理解起來最簡單，一個單獨的插入介面，業務上迴圈呼叫即可

@Mapper
public interface MoneyInsertMapper {
    /**
     * 寫入
     * @param po
     * @return
     */
    int save(@Param("po") MoneyPo po);
}

對應的xml如下

<resultMap id="BaseResultMap" type="com.git.hui.boot.mybatis.entity.MoneyPo">
    <id column="id" property="id" jdbcType="INTEGER"/>
    <result column="name" property="name" jdbcType="VARCHAR"/>
    <result column="money" property="money" jdbcType="INTEGER"/>
    <result column="is_deleted" property="isDeleted" jdbcType="TINYINT"/>
    <result column="create_at" property="createAt" jdbcType="TIMESTAMP"/>
    <result column="update_at" property="updateAt" jdbcType="TIMESTAMP"/>
</resultMap>
<insert id="save" parameterType="com.git.hui.boot.mybatis.entity.MoneyPo" useGeneratedKeys="true" keyProperty="po.id">
  INSERT INTO `money` (`name`, `money`, `is_deleted`)
  VALUES
(#{po.name}, #{po.money}, #{po.isDeleted});
</insert>

使用姿勢如下

private MoneyPo buildPo() {
    MoneyPo po = new MoneyPo();
    po.setName("mybatis user");
    po.setMoney((long) random.nextInt(12343));
    po.setIsDeleted(0);
    return po;
}

public void testBatchInsert() {
    for (int i = 0; i < 10; i++) {
        moneyInsertMapper.save(buildPo());
    }
}

小結

上面這種方式的優點就是簡單直觀，缺點就是db互動次數多，開銷大

2. BATCH批處理模式

針對上面做一個簡單的優化，使用BATCH批處理模式，實現會話複用，避免每次請求都重新維護一個連結，導致額外開銷，可以如下操作

try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
    MoneyInsertMapper moneyInsertMapper = sqlSession.getMapper(MoneyInsertMapper.class);
    for (int i = 0; i < 10; i++) {
        moneyInsertMapper.save(buildPo());
    }
    sqlSession.commit();
}

說明

sqlSession.commit若放在for迴圈內，則每儲存一個就提交，db中就可以查詢到
若如上面放在for迴圈外，則所有的一起提交

3. foreach實現sql拼接

另外一種直觀的想法就是組裝批量插入sql，這裡主要是藉助foreach來處理

<insert id="batchSave" parameterType="com.git.hui.boot.mybatis.entity.MoneyPo"  useGeneratedKeys="true" keyProperty="id">
    insert ignore into `money` (`name`, `money`, `is_deleted`)
    values
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.name}, #{item.money}, #{item.isDeleted})
    </foreach>
</insert>

對應的mapper介面如下

/**
 * 批量寫入
 * @param list
 * @return
 */
int batchSave(@Param("list") List<MoneyPo> list);

實際使用case如下

List<MoneyPo> list = new ArrayList<>();
list.add(buildPo());
list.add(buildPo());
list.add(buildPo());
list.add(buildPo());
list.add(buildPo());
list.add(buildPo());
moneyInsertMapper.batchSave(list);

小結

使用sql批量插入的方式，優點是db互動次數少，在插入數量可控時，相比於前者開銷更小

缺點也很明顯，當一次插入的數量太多時，組裝的sql既有可能直接超過了db的限制，無法執行了

4. 分批BATCH模式

接下來的這種方式在上面的基礎上進行處理，區別在於對List進行拆分，避免一次插入太多資料，其次就是真個操作複用一個會話，避免每一次的互動都重開一個會話，導致額外的開銷

其使用姿勢如下

try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
    MoneyInsertMapper moneyInsertMapper = sqlSession.getMapper(MoneyInsertMapper.class);
    for (List<MoneyPo> subList : Lists.partition(list, 2)) {
        moneyInsertMapper.batchSave(subList);
    }
    sqlSession.commit();
}

與第二種使用姿勢差不多，區別在於結合了第三種批量的優勢，對大列表進行拆分，實現複用會話 + 批量插入

5. 如何選擇

上面介紹了幾種不同的批量插入方式，那我們應該選擇哪種呢？

就我個人的觀點來講，2，3，4這三個在一般的業務場景下並沒有太大的區別，如果已知每次批量寫入的資料不多（比如幾十條），那麼使用3就是最簡單的case了

如果批量插入的資料非常多，那麼方案4可能更加優雅

如果我們希望開發一個批量導資料的功能，那麼方案2無疑是更好的選擇

III. 不能錯過的原始碼和相關知識點

0. 專案

工程： http://github.com/liuyueyi/spring-boot-demo
原始碼： http://github.com/liuyueyi/spring-boot-demo/tree/master/spring-boot/103-mybatis-xml

系列博文：

1. 微信公眾號: 一灰灰Blog

盡信書則不如，以上內容，純屬一家之言，因個人能力有限，難免有疏漏和錯誤之處，如發現bug或者有更好的建議，歡迎批評指正，不吝感激

下面一灰灰的個人部落格，記錄所有學習和工作中的博文，歡迎大家前去逛逛

一灰灰Blog個人部落格 http://blog.hhui.top
一灰灰Blog-Spring專題部落格 http://spring.hhui.top

打賞如果覺得我的文章對您有幫助，請隨意打賞。

「其他文章」