MyBatis批量插入的几种方式效率比较

目录
  • 前言
  • 一、前期准备
    • 1.1 表结构
    • 1.2 项目配置文件
    • 1.3 实体类
  • 二、反复执行单条插入语句
    • 2.1 对应 mapper 接口
    • 2.2 测试方法
  • 三、foreach 拼接SQL
    • 3.1 对应mapper 接口
    • 3.2 测试方法
    • 3.3 执行时间
  • 四、批处理
    • 4.1 rewriteBatchedStatements 参数
    • 4.2 批处理准备
    • 4.3 执行时间
    • 4.4 如果数据更大
  • 五、总结

前言

批处理数据主要有三种方式:

  • 反复执行单条插入语句
  • 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
  1. 循环插入单条数据虽然效率极低,但是代码量极少,数据量较小时可以使用,但是数据量较大禁止使用,效率太低了;
  2. foreach 拼接sql的方式,使用时有大段的xml和sql语句要写,很容易出错,虽然效率尚可,但是真正应对大量数据的时候,依旧无法使用,所以不推荐使用;
  3. 批处理执行是有大数据量插入时推荐的做法,使用起来也比较方便。

本文示例代码

到此这篇关于MyBatis批量插入的几种方式效率比较的文章就介绍到这了,更多相关MyBatis批量插入效率比较内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 详解MyBatis批量插入数据Mapper配置文件的写法

    对于MyBatis配置文件的用法一直不是很熟悉,之前一直是使用注解来开发的,但是注解也有不好的地方就是如果数据库的表结构发生变化在代码中修改起来很麻烦. 其实批量插入很简单,这里做些简要的说明.请看配置文件的写法: <insert id="insertAll" parameterType="java.util.List" useGeneratedKeys="true"> <selectKey resultType="l

  • MyBatis批量插入数据到Oracle数据库中的两种方式(实例代码)

    一.mybatis批量插入数据到Oracle中的两种方式: 第一种: <insert id="addList" parameterType="java.util.List" useGeneratedKeys="false"> INSERT ALL <foreach item="item" index="index" collection="list"> INTO

  • 详解mybatis批量插入10万条数据的优化过程

    数据库 在使用mybatis插入大量数据的时候,为了提高效率,放弃循环插入,改为批量插入,mapper如下: package com.lcy.service.mapper; import com.lcy.service.pojo.TestVO; import org.apache.ibatis.annotations.Insert; import java.util.List; public interface TestMapper { @Insert("") Integer test

  • mybatis插入与批量插入返回ID的原理详解

    背景 最近正在整理之前基于mybatis的半ORM框架.原本的框架底层类ORM操作是通过StringBuilder的append拼接的,这次打算用JsqlParser重写一遍,一来底层不会存在太多的文本拼接,二来基于其他开源包维护难度会小一些,最后还可以整理一下原有的冗余方法. 这两天整理insert相关的方法,在将对象插入数据库后,期望是要返回完整对象,并且包含实际的数据库id. 基础相关框架为:spring.mybatis.hikari. 底层调用方法 最底层的做法实际上很直白,就是利用my

  • Java实现mybatis批量插入数据到Oracle

    最近项目中遇到一个问题:导入数据到后台并将数据插入到数据库中,导入的数据量有上万条数据,考虑采用批量插入数据的方式:  结合网上资料,写了个小demo,文章末尾附上demo下载地址 1.新建项目:项目目录结构如下图所示,添加相应的jar包 2.新建数据库表:ACCOUNT_INFO CREATE TABLE ACCOUNT_INFO ( "ID" NUMBER(12) NOT NULL , "USERNAME" VARCHAR2(64 BYTE) NULL , &q

  • MyBatis批量插入(insert)数据操作

    在程序中封装了一个List集合对象,然后需要把该集合中的实体插入到数据库中,由于项目使用了Spring+MyBatis的配置,所以打算使用MyBatis批量插入,由于之前没用过批量插入,在网上找了一些资料后最终实现了,把详细过程贴出来. 实体类TrainRecord结构如下: public class TrainRecord implements Serializable { private static final long serialVersionUID = -12069604621179

  • mybatis中批量插入的两种方式(高效插入)

    MyBatis简介 MyBatis是一个支持普通SQL查询,存储过程和高级映射的优秀持久层框架.MyBatis消除了几乎所有的JDBC代码和参数的手工设置以及对结果集的检索封装.MyBatis可以使用简单的XML或注解用于配置和原始映射,将接口和Java的POJO(Plain Old Java Objects,普通的Java对象)映射成数据库中的记录. 一.mybiats foreach标签 foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合.foreach元素的属性主

  • 详解MyBatis直接执行SQL查询及数据批量插入

    一.直接执行SQL查询: 1.mappers文件节选 <resultMap id="AcModelResultMap" type="com.izumi.InstanceModel"> <result column="instanceid" property="instanceID" jdbcType="VARCHAR" /> <result column="insta

  • Mybatis批量插入数据返回主键的实现

    响应效果(id为主键): { "data": [ {"studentName": "张三","classNo": "一班","id": 111}, {"studentName": "李四","classNo": "二班","id": 112}, {"studentName&quo

  • Mybatis 插入一条或批量插入 返回带有自增长主键记录的实例

    首先讲一下, 插入一条记录返回主键的 Mybatis 版本要求低点,而批量插入返回带主键的 需要升级到3.3.1版本,3.3.0之前的都不行. <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>3.3.1</version> </dependency> 1.MySQL <

随机推荐