mybatis中大批量數(shù)據(jù)插入解析

更新時(shí)間：2024年01月23日 10:48:45 作者：小星星*

這篇文章主要介紹了mybatis中大批量數(shù)據(jù)插入解析,使用Mybatis框架批量插入的3種方法,分別是多次調(diào)用insert方法、foreach標(biāo)簽、batch模式,本文來(lái)詳細(xì)說(shuō)明一下,需要的朋友可以參考下

mybatis批量數(shù)據(jù)插入

本文測(cè)試三種方式，插入大量數(shù)據(jù)（10萬(wàn)以上）到mysql數(shù)據(jù)庫(kù)中，有以下幾種方式

普通方式
foreach批量插入
ExecutorType.BATCH插入

1 user實(shí)體類

@Setter
@Getter
public class User {
    private Long id;
    private String phone;
    private String name;
}

2 IUserService

public interface IUserService {
    void insertUsers();
    void insertUser();
    void batchInsert();
    void insertUsersBatch();
}

3 userService類

package com.bear.dbinserttest;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
/**
 * @description:
 * @author: zhoushaoxiong
 * @date: 2023/6/8 11:08
 */
@Service
public class UserService implements IUserService {
    private static int count = 1000000;
    @Resource
    private IUserDao userDao;
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;
    @Override
    public void insertUser(){
        List<User> userList = new ArrayList<>();
        Long start = System.currentTimeMillis();
        for (int i=0; i<count; i++){
            User user = new User();
            user.setName("name"+i);
            user.setPhone("phonephone"+i);
            userList.add(user);
            userDao.insertUser(user);
        }
        Long usedTime = (System.currentTimeMillis()-start);
        System.out.println("插入"+count+"條數(shù)據(jù)耗時(shí)：:" + usedTime);
    }
    @Override
    //foreach插入
    public void insertUsers() {
        List<User> userList = new ArrayList<>();
        int batch = 20000;
        Long start = System.currentTimeMillis();
        for (int i=0; i<count; i++){
            User user = new User();
            user.setName("name"+i);
            user.setPhone("phonephone"+i);
            userList.add(user);
            if(userList.size()>=batch){  //mysql的sql語(yǔ)句有4M的大小限制，不能全部放在一個(gè)foreach
                userDao.insertUsers(userList);
                userList.clear();
            }
        }
        if(userList.size()>0){
            userDao.insertUsers(userList);
        }
        Long usedTime = (System.currentTimeMillis()-start);
        System.out.println("插入"+count+"條數(shù)據(jù)耗時(shí)：:" + usedTime);
    }
    @Override
    //ExecutorType.BATCH插入
    public void insertUsersBatch(){
        //手動(dòng)創(chuàng)建userDao
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false);
        IUserDao userDao2 = sqlSession.getMapper(IUserDao.class);
        Long start = System.currentTimeMillis();
        int batch = 1000;
        for (int i=0; i<count; i++){
            User user = new User();
            user.setName("name"+i);
            user.setPhone("phonephone"+i);
            userDao2.insertUser(user);
            if(i!=0 && i%batch==0){  //1000條提交一次
                sqlSession.commit();
            }
        }
        sqlSession.commit();
        Long usedTime = (System.currentTimeMillis()-start);
        System.out.println("插入"+count+"條數(shù)據(jù)耗時(shí)：:" + usedTime);
    }
}

4 IUserDao

package com.bear.dbinserttest;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.mybatis.spring.annotation.MapperScan;
import java.util.List;
/**
 * @description:
 * @author: zhoushaoxiong
 * @date: 2023/6/8 11:15
 */
public interface IUserDao {
    void insertUsers(List<User> userList);
    void insertUser(User user);
    void batchInsert(@Param("list") List<User> list);
    void insertUsersBatch(List<User> users);
}

5 UserMapper

<?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.bear.dbinserttest.IUserDao" >
    <insert id="insertUser" parameterType="com.bear.dbinserttest.User">
        insert into user(name, phone) values (#{name}, #{phone})
    </insert>
    <-- 普通插入 --/>
    <insert id="insertUsers" parameterType="com.bear.dbinserttest.User">
        insert into user(name, phone) values
        <foreach collection="userList" item="user" separator=",">
            (#{user.name}, #{user.phone})
        </foreach>
    </insert>
    <-- foreach插入 --/>
    <insert id="batchInsert" parameterType="com.bear.dbinserttest.User">
        <foreach collection="list" item="item" separator=";" >
            insert into user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="item.id != null">
                    id,
                </if>
                <if test="item.name != null">
                    `name`,
                </if>
                <if test="item.phone != null">
                    `phone`,
                </if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="item.id != null">
                    #{item.id,jdbcType=INTEGER}，
                </if>
                <if test="item.name != null">
                    #{item.name,jdbcType=VARCHAR},
                </if>
                <if test="item.phone != null">
                    #{item.phone,jdbcType=VARCHAR},
                </if>
            </trim>
        </foreach>
    </insert>
    <-- ExecutorType.BATCH插入 --/>
    <insert id="insertUsersBatch" parameterType="com.bear.dbinserttest.User">
        insert into user(name, phone) values (#{name}, #{phone})
    </insert>
</mapper>

6 application.yml 配置文件

url必須設(shè)置rewriteBatchedStatements=true,否則ExecutorType.BATCH插入的方式效率很低

server:
  port: 8080
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true
mybatis:
  mapper-locations: classpath:mapper/*.xml

7 DbinserttestApplicationTests 測(cè)試類

package com.bear.dbinserttest;
import org.junit.jupiter.api.Test;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
@SpringBootTest
class DbinserttestApplicationTests {
    @Autowired
    IUserService userService;
    @Test
    //測(cè)試普通插入
    void contextLoads() {
        userService.insertUser(); //插入100000條數(shù)據(jù)耗時(shí)：:74895ms
    }
    @Test
    //測(cè)試foreach插入
    void contextLoads2() {
        userService.insertUsers(); //插入100000條數(shù)據(jù)耗時(shí)：:1801ms
    }
    @Test
    //測(cè)試ExecutorType.BATCH插入
    void contextLoads3() { 
        userService.insertUsersBatch(); //插入100000條數(shù)據(jù)耗時(shí)：:1143ms
    }
}

8 小結(jié)

1.普通插入

默認(rèn)的插入方式是遍歷insert語(yǔ)句，單條執(zhí)行，效率肯定低下，如果成堆插入，更是性能有問(wèn)題。

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

可以看到每個(gè)語(yǔ)句的執(zhí)行創(chuàng)建一個(gè)新的預(yù)處理語(yǔ)句，單條提交sql,性能低下.

2.foreach 優(yōu)化插入

如果要優(yōu)化插入速度時(shí)，可以將許多小型操作組合到一個(gè)大型操作中。理想情況下，這樣可以在單個(gè)連接中一次性發(fā)送許多新行的數(shù)據(jù)，并將所有索引更新和一致性檢查延遲到最后才進(jìn)行。

INSERT INTO `table1` (`field1`, `field2`) 
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");

當(dāng)表的列數(shù)較多（20+），以及一次性插入的行數(shù)較多（5000+）時(shí)，整個(gè)插入的耗時(shí)十分漫長(zhǎng),注意耗時(shí)在PreparedStatement特別長(zhǎng)，包含了很多占位符，對(duì)于占位符和參數(shù)的映射尤其耗時(shí)

需要注意的點(diǎn)： Mysql 對(duì)語(yǔ)句的長(zhǎng)度有限制，默認(rèn)是 4M。

3.ExecutorType.BATCH插入

Mybatis內(nèi)置的ExecutorType有3種，SIMPLE、REUSE、BATCH; 默認(rèn)的是simple，該模式下它為每個(gè)語(yǔ)句的執(zhí)行創(chuàng)建一個(gè)新的預(yù)處理語(yǔ)句，單條提交sql；而batch模式重復(fù)使用已經(jīng)預(yù)處理的語(yǔ)句，并且批量執(zhí)行所有更新語(yǔ)句，顯然batch性能將更優(yōu)；但batch模式也有自己的問(wèn)題，比如在Insert操作時(shí)，在事務(wù)沒(méi)有提交之前，是沒(méi)有辦法獲取到自增的id，這在某型情形下是不符合業(yè)務(wù)要求的.

JDBC 在執(zhí)行 SQL 語(yǔ)句時(shí)，會(huì)將 SQL 語(yǔ)句以及實(shí)參通過(guò)網(wǎng)絡(luò)請(qǐng)求的方式發(fā)送到數(shù)據(jù)庫(kù)，一次執(zhí)行一條 SQL 語(yǔ)句，一方面會(huì)減小請(qǐng)求包的有效負(fù)載，另一個(gè)方面會(huì)增加耗費(fèi)在網(wǎng)絡(luò)通信上的時(shí)間。通過(guò)批處理的方式，我們就可以在 JDBC 客戶端緩存多條 SQL 語(yǔ)句，然后在 flush 或緩存滿的時(shí)候，將多條 SQL 語(yǔ)句打包發(fā)送到數(shù)據(jù)庫(kù)執(zhí)行，這樣就可以有效地降低上述兩方面的損耗，從而提高系統(tǒng)性能。

需要注意的點(diǎn)：進(jìn)行jdbc批處理時(shí)需在JDBC的url中加入rewriteBatchedStatements=true

ExecutorType.BATCH原理：把SQL語(yǔ)句發(fā)個(gè)數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)預(yù)編譯好，數(shù)據(jù)庫(kù)等待需要運(yùn)行的參數(shù)，接收到參數(shù)后一次運(yùn)行，ExecutorType.BATCH只打印一次SQL語(yǔ)句，預(yù)編譯一次sql,多次設(shè)置參數(shù)步驟.

經(jīng)過(guò)以上三種方式分析,在插入大數(shù)據(jù)量時(shí)優(yōu)先選擇第三種方式, ExecutorType.BATCH插入

到此這篇關(guān)于mybatis中大批量數(shù)據(jù)插入解析的文章就介紹到這了,更多相關(guān)mybatis批量數(shù)據(jù)插入內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: