mybatis批量插入数据三种方式性能对比

最新推荐文章于 2024-08-01 00:45:00 发布
最新推荐文章于 2024-08-01 00:45:00 发布
阅读量765 收藏 4
点赞数 2
分类专栏： mybatis 数据库_mysql 文章标签： mysql 数据库 java mybatis
原文链接：https://www.toutiao.com/a7010611732557070861/?log_from=8699e9907a142_1632825538299
版权

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

  1. 反复执行单条插入语句
  2. foreach 拼接 sql
  3. 批处理

一、前期准备

基于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("123456789@qq.com");
            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 接口

int insert(UserInfoBatchDO info);

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 执行时间

  • 第一次
    在这里插入图片描述

  • 第二次

在这里插入图片描述

  • 第三次

在这里插入图片描述
该方式插入2000 条数据，执行三次的平均时间：87021539698。

三、foreach 拼接SQL

3.1 对应mapper 接口

int batchInsert(List<UserInfoBatchDO> list);

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 执行时间

  • 第一次

在这里插入图片描述

  • 第二次

在这里插入图片描述

  • 第三次

在这里插入图片描述
该方式插入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 执行时间

  • 第一次
    在这里插入图片描述
  • 第二次
    在这里插入图片描述
  • 第三次在这里插入图片描述

该方式插入20w 条数据，执行三次的平均时间：12656499721。

4.4 如果数据更大

当我把数据扩大到 100w 时，foreach 拼接 sql 的方式已经无法完成插入了，所以我只能测试批处理的插入时间。

测试时，仅需将 【4.2】测试代码中的 list 切成 lageList 测试即可。

  • 第一次
    在这里插入图片描述

  • 第二次

在这里插入图片描述

  • 第三次
    在这里插入图片描述
    该方式插入100w 条数据，执行三次的平均时间：43709821640。

五、总结

在这里插入图片描述

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

源代码：

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

参考网址

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

lwh_zfj
关注
专栏目录
MyBatis批量插入，10秒内十万条数据插入，及性能测试
user__kk的博客
09-05 2125
本文介绍了 MyBatis 批量插入的 3 种方法，其中循环单次插入的性能最低，也是最不可取的；使用 MyBatis 拼接原生 SQL 一次性插入的方法性能最高，但此方法可能会导致程序执行报错（触发了数据库最大执行 SQL 大小的限制），所以综合以上情况，可以考虑使用 Mybatis-Plus 的批量插入功能。
浅谈一下 MyBatis 批量插入的 3 种方法！
m0_71777195的博客
08-24 492
本文我们介绍了 MyBatis 批量插入的 3 种方法，其中循环单次插入的性能最低，也是最不可取的；使用 MyBatis 拼接原生 SQL 一次性插入的方法性能最高，但此方法可能会导致程序执行报错（触发了数据库最大执行 SQL 大小的限制），所以综合以上情况，可以考虑使用 MP 的批量插入功能。
Mybatis与JDBC批量插入MySQL数据库性能测试
04-13
Mybatis与JDBC批量插入MySQL数据库性能测试，资源包含文档、代码和数据库。
MyBatis 批量插入-优化&效率对比
常有理爱摸鱼
10-08 4834
今天我们围绕Mybatis批量插入的for循环插入、batch()伪批量插入、真实批量插入-方法、真实批量插入-配置的效率&优劣进行详解。
【MyBatis】mybatis-plus 批量插入 性能优化
最新发布
九师兄
08-01 1641
稍微总结下我粗略的对比（虽然粗略，但实验结果符合原理层面的理解），如果你想更准确地实验，可以使用JMH，并且测试更多组数（如 5000，10000等）的情况。批量保存方式数据量（条）耗时（ms）单条循环插入1000121011100059927mybatis-plus saveBatch(添加rewtire参数)10002589手动拼接sql10002275100055663jdbc executeBatch(添加rewtire参数)1000324。
mybatis批量插入方式对比
Donny的博客
05-17 338
来自https://blog.csdn.net/m0_37981235/article/details/79131493 第一种：普通for循环插入 ①junit类 @Test public void testInsertBatch2() throws Exception { long start = System.currentTimeMillis(); User u...
批量插入数据的三种方法性能对比
zouyang920的博客
12-09 396
MyBatis 批量插入foreach性能问题
qq_36042938的博客
08-26 1408
批量插入
浅谈MyBatis批量插入方法，10w条数据处理仅需2秒
热门推荐
退役复学在校大学生，目光所至，皆为华夏
09-05 2万+
本文我们介绍了 MyBatis 批量插入的 3 种方法，其中循环单次插入的性能最低，也是最不可取的；使用 MyBatis 拼接原生 SQL 一次性插入的方法性能最高，但此方法可能会导致程序执行报错（触发了数据库最大执行 SQL 大小的限制），所以综合以上情况，可以考虑使用 MP 的批量插入功能。最后学长相信大家一定学费了MyBatis批量插入的3种方法和10w条数据仅需2秒的方法，关注学长不迷路，下期带领大家学习更多的实用技巧哦~~
MyBatis 批量插入数据的 3 种方法
uuuyy_的博客
12-06 5751
批量插入功能是我们日常工作中比较常见的业务功能之一，之前我也写过一篇关于《MyBatis Plus 批量数据插入功能，yyds！》的文章，但评论区的反馈不是很好，主要有两个问题：第一，对 MyBatis Plus（下文简称 MP）的批量插入功能很多人都有误解，认为 MP 也是使用循环单次插入数据的，所以性能并没有提升；第二，对于原生批量插入的方法其实也是有坑的，但鲜有人知。 所以综合以上情况，磊哥决定再来一个 MyBatis 批量插入的汇总篇，同时对 3 种实现方法做一个性能测试，以及相应的原理分析。
调优 mybatis saveBatch 25倍性能
yes
04-04 1710
稍微总结下我粗略的对比（虽然粗略，但实验结果符合原理层面的理解），如果你想更准确地实验，可以使用JMH，并且测试更多组数（如 5000，10000等）的情况。批量保存方式数据量（条）耗时（ms）单条循环插入1000121011100059927mybatis-plus saveBatch(添加rewtire参数)10002589手动拼接sql10002275100055663jdbc executeBatch(添加rewtire参数)1000324。
MyBatis批量插入性能及问题
weixin_38168547的博客
09-16 450
1、mybatis三种批量插入方式对比 2、Mybatis与JDBC批量插入MySQL数据库性能测试及解决方案 3、Mybatis批量插入引发的血案 4、Oracle批量插入数据SQL语句太长出错 转载于:https://www.cnblogs.com/yixiu868/p/11527035.html...
MyBatis批量插入数据(MySql)
aa1358075776的博客
09-14 980
MyBatis批量插入数据(MySql)   由于项目需要生成多条数据，并保存到数据库当中，在程序中封装了一个List集合对象，然后需要把该集合中的实体插入到数据库中,项目使用了Spring+MyBatis，所以打算使用MyBatis批量插入，应该要比循环插入的效果更好,由于之前没用过批量插入，在网上找了一些资料后最终实现了，把详细过程贴出来。供以后查阅和学习。
Fork/Join优化mybatis-plus批量插入性能
jin2111843364的博客
04-19 831
Fork/Join多线程优化Mybatis-Plus批量插入
【Mybatis扩展】MybatisPlus批量插入性能比较
秋装什么的博客
06-07 234
【Mybatis扩展】MybatisPlus批量插入性能比较
Mybatis插入大量数据效率对比：foreach插入、SqlSession批量插入、sql插入
2401_84183669的博客
04-27 853
System.out.println(“sql 插入耗时：”+(System.currentTimeMillis()-start));System.out.println(“forEachInsert 插入开始========”);System.out.println(“batchInsert 插入开始========”);System.out.println(“xmlInsert 批量插入开始========”);System.out.println(“sql 插入开始========”);
Mybatis高效批量插入实战：五种策略全面解析
wenxuankeji的博客
05-10 646
Mybatis高效批量插入实战：五种策略全面解析
mybatis批量插入mysql性能优化_mybatis三种批量插入方式性能对比
weixin_29147589的博客
02-26 521
mybatis三种批量插入方式性能对比发布时间：2018-08-03 13:58,浏览次数：1523, 标签：mybatis准备：1.表结构CREATE TABLE `t_user` (`id` varchar(32) CHARACTER SET utf8 NOT NULL COMMENT '主键',`name` varchar(50) CHARACTER SET utf8 DEFAULT NUL...
Mybatis批量插入四种实现方式的比较与测试
"Mybatis批量插入的四种方式" Mybatis是一款流行的Java持久层框架，它提供了多种方式来实现批量插入操作。下面我们将详细介绍Mybatis批量插入的四种方式。 循环插入 循环插入是最简单的批量插入方式，即使用循环...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值