百度360必应搜狗淘宝本站头条
mysql distinctjenkins官网mysql 子查询vba splitpython list sort
当前位置：网站首页 > IT技术 > 正文

坑已踩好，MyBatis 几种批量插入性能比较

wptr33 2025-02-07 19:21 17 浏览

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

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

一、前期准备

基于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 list);
}

1.5 UserInfoBatchMapper.xml






  
    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 into user_info_batch (user_name, pass_word, nick_name, mobile, email, gmt_create, gmt_update)
    values
    
      (#{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})

1.6 预备数据

为了方便测试，抽离了几个变量，并进行提前加载。

   
private List list = new ArrayList<>();
    private List lessList = new ArrayList<>();
    private List lageList = new ArrayList<>();
    private List warmList = new ArrayList<>();
    // 计数工具
    private StopWatch sw = new StopWatch();

为了方便组装数据，抽出了一个公共方法。

private List assemblyData(int count){
        List 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 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. 批处理执行是有大数据量插入时推荐的做法，使用起来也比较方便。

相关推荐

redis的八种使用场景

前言:redis是我们工作开发中，经常要打交道的，下面对redis的使用场景做总结介绍也是对redis举报的功能做梳理。缓存Redis最常见的用途是作为缓存，用于加速应用程序的响应速度。...

基于Redis的3种分布式ID生成策略

在分布式系统设计中，全局唯一ID是一个基础而关键的组件。随着业务规模扩大和系统架构向微服务演进，传统的单机自增ID已无法满足需求。高并发、高可用的分布式ID生成方案成为构建可靠分布式系统的必要条件。R...

基于OpenWrt系统路由器的模式切换与网页设计

摘要：目前商用WiFi路由器已应用到多个领域，商家通过给用户提供一个稳定免费WiFi热点达到吸引客户、提升服务的目标。传统路由器自带的Luci界面提供了工厂模式的Web界面，用户可通过该界面配置路...

这篇文章教你看明白 nginx-ingress 控制器

主机nginx一般nginx做主机反向代理（网关）有以下配置...

如何用redis实现注册中心

一句话总结使用Redis实现注册中心：服务注册...

爱可可老师24小时热门分享(2020.5.10)

No1.看自己以前写的代码是种什么体验？No2.DooM-chip!国外网友SylvainLefebvre自制的无CPU、无操作码、无指令计数器...No3.我认为CS学位可以更好，如...

Apportable:拯救程序员，IOS一秒变安卓

摘要：还在为了跨平台使用cocos2d-x吗,拯救objc程序员的奇葩来了,ApportableSDK:FreeAndroidsupportforcocos2d-iPhone。App...

JAVA实现超买超卖方案汇总，那个最适合你，一篇文章彻底讲透

以下是几种Java实现超买超卖问题的核心解决方案及代码示例，针对高并发场景下的库存扣减问题：方案一：Redis原子操作+Lua脚本（推荐）//使用Redis+Lua保证原子性publicbo...

3月26日更新 快速施法自动施法可独立设置

2016年3月26日DOTA2有一个79.6MB的更新主要是针对自动施法和快速施法的调整本来内容不多不少朋友都有自动施法和快速施法的困扰英文更新日志一些视觉BUG修复就不翻译了主要翻译自动施...

Redis 是如何提供服务的

在刚刚接触Redis的时候，最想要知道的是一个’setnameJhon’命令到达Redis服务器的时候，它是如何返回’OK’的？里面命令处理的流程如何，具体细节怎么样？你一定有问过自己...

lua _G、_VERSION使用

到这里我们已经把lua基础库中的函数介绍完了，除了函数外基础库中还有两个常量，一个是_G，另一个是_VERSION。_G是基础库本身，指向自己，这个变量很有意思，可以无限引用自己，最后得到的还是自己，...

China&#39;s top diplomat to chair third China-Pacific Island countries foreign ministers&#39; meeting

BEIJING,May21(Xinhua)--ChineseForeignMinisterWangYi,alsoamemberofthePoliticalBureau...

移动工作交流工具Lua推出Insights数据分析产品

Lua是一个适用于各种职业人士的移动交流平台，它在今天推出了一项叫做Insights的全新功能。Insights是一个数据平台，客户可以在上面实时看到员工之间的交流情况，并分析这些情况对公司发展的影响...

Redis 7新武器:用Redis Stack实现向量搜索的极限压测

当传统关系型数据库还在为向量相似度搜索的性能挣扎时，Redis7的RedisStack...

Nginx/OpenResty详解，Nginx Lua编程，重定向与内部子请求

重定向与内部子请求Nginx的rewrite指令不仅可以在Nginx内部的server、location之间进行跳转，还可以进行外部链接的重定向。通过ngx_lua模块的Lua函数除了能实现Nginx...

一周热门
最近发表
标签列表
赣ICP备2024040476号-12