【Mybatis】大量插入/修改数据库方法实践对比

最新推荐文章于 2024-04-17 23:19:21 发布
最新推荐文章于 2024-04-17 23:19:21 发布
阅读量1k 收藏 22
点赞数 16
文章标签： mybatis 数据库 java spring
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42961251/article/details/135712605
版权

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

文章目录

在这里插入图片描述

问题

有时候我们做一些需求经常会需要大量往mysql中插入数据，或者修改以往mysql中的数据。
接下来举两个例子：
1.数据库中某些字段不符合当前产品需求需要回刷以前mysql中的数据
2.通过job定期去拉es/其他表数据组装大量写入新mysql表中
一般来说一个互联网公司每次对数据库批量操作的数据量可能高达百万千万级别，那么执行效率就很重要了。
如何提高插入/修改批量数据的效率就显得尤为重要。

结论

批处理和foreach效率都很不错，可以配合多线程以及在不同机器上分段执行插入/修改提高效率

准备

本次例子中我们以插入数据库为例。
1.mysql
mysql表用于演示表比较简单

表结构

CREATE TABLE `user_info_batch` (
  `id` bigint 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 '邮箱地址',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=603335 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC COMMENT='Mybatis Batch'

2.springboot-mybatis
我提供git仓库代码欢迎大家下载：https://gitee.com/llbnk/inserts-sql.git
其中如何搭建mybatis与springboot项目我就不在赘述（相信小伙伴们比我更牛逼）。

项目结构非常简单
在这里插入图片描述
准备数据方法

准备数据

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("llbnk")
                    .setNickName("llbnk")
                    .setMobile("139*******1")
                    .setPassWord("password")
            ;
            list.add(userInfoDO);
        }
        return list;
    }

    @PostConstruct
    public void assemblyData() {
        list = assemblyData(200000);
        lessList = assemblyData(2000);
        lageList = assemblyData(1000000);
        warmList = assemblyData(5);
    }

一、简单插入

我们在批量插入数据的时候能想到最常见的方法就是循环一条条插入数据。

简单插入代码

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());
    }

通过postman去调用

curl

curl --location --request GET 'localhost:8080/test/insert'

可以看到仅仅插入了2000条时间却很长。

结果
在这里插入图片描述

二、foreach插入

我们在进行插入数据的时候不可能仅满足2000条数据上w条在正常不过。
我们可以采用mapper的foreach插入.
这种方法需要注意
1.需要考虑分批数
2.MySQL对于单条SQL的字节大小有限制，默认为 1MB

后端代码

Userservice

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());
    }

后端代码

UserInfoBatchMapper.XML

<insert id="batchInsert">
        insert into user_info_batch (user_name, pass_word, nick_name, mobile, email)
        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})
        </foreach>
    </insert>

通过postman去调用

curl

curl --location --request GET 'localhost:8080/test/batch/insert'

结果
在这里插入图片描述
可看到foreach插入20w的数据和单条插入2k的数据效率竟然差不多

三、batch批处理

我们能看到在连接mysql的时候设置的url

jdbc:mysql://127.0.0.1:3306/insert-mysql-test?useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true

这个url携带rewriteBatchedStatements参数。
rewriteBatchedStatements参数介绍
MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。MySql JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySql数据库，批量插入实际上是单条插入，直接造成较低的性能。只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL。这个选项对INSERT/UPDATE/DELETE都有效。

这种方法需要做到两点
1.需要更改后端连接数据库的参数，“rewriteBatchedStatements”: “true”
2.需要手动管理 sqlSession 的commit

后端代码

UserService

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());
    }

通过postman去调用

curl

curl --location --request GET 'localhost:8080/test/process/insert'

结果
在这里插入图片描述

可看到批处理插入20w的数据的效率远远高于foreach更不用说单条插入了

探究批处理

为什么需要做到
1.需要更改后端连接数据库的参数，“rewriteBatchedStatements”: “true”
2.需要手动管理 sqlSession 的commit
这两点呢？

1.探究参数

我们点开commit代码

DefaultSqlSession.class

在这里插入图片描述
继续往下走，可以看到commit是Executor.class的接口，而实现这个接口的基类是BaseExecutor

BaseExecutor.class

在这里插入图片描述
在这里插入图片描述
继续往下走，可以看到doFlushStatements是BaseExecutor.class的接口，而实现这个接口的基类是BatchExecutor.class

BatchExecutor.class

在这里插入图片描述
可以看到statementList去提供这个batch批处理的方法，而statementList就是一个statement的集合。现在我们看看mybtis中谁实现了statement，我们继续探究。

Statement.java

在这里插入图片描述

CallableStatement.class

在这里插入图片描述
可以看到在CallableStatement.class中继承了ClientPreparedStatement。现在我们再来看executeBatch()方法。

CallableStatement.class

在这里插入图片描述
在这里插入图片描述

StatementImpl.class

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

ClientPreparedStatement.class

在这里插入图片描述
也就是说插入会走上面的方法，而修改等会走下面的方法（还需要mysql版本>=4.1.0，并且batch的数量>3）。

2.探究手动管理 commit

mybatis是非spring自己管理的事务，会强制提交单条sql语句 ,换句话来说mybatis-spring模块强制把这个commit置为true。所以需要我们手动去commit

在这里插入图片描述

总结（其他提效方法）

批处理效率最高其次是foreach处理，按理来说应该都用批处理，但是往往我们也会采用foreach。
虽然批处理和foreach高效但是我们还有其他方法去提高大量数据库插入的方法。
插入：
如多开几个线程去执行批量sql操作（结合线程池）
修改：
查出主键id，将批量操作按id去分段，通过不同的服务机器去调用。

参考：
[1]https://blog.csdn.net/wangwei19871103/article/details/103686121
[2]https://mp.weixin.qq.com/s/TjzXuq1M_uzpSHZgzm2u3Q
[3]https://blog.csdn.net/weixin_45143924/article/details/122397913?utm_medium=distribute.pc_relevant.none-task-blog-2defaultbaidujs_baidulandingword~default-0-122397913-blog-103686121.235v40pc_relevant_anti_t3&spm=1001.2101.3001.4242.1&utm_relevant_index=3
[4]https://www.jb51.net/article/278324.htm

llbnk
关注
  • 16
    点赞
  • 踩
  • 22
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mybatis 批量将list数据插入到数据库的实现
08-18
在实际开发中，我们经常需要将大量数据批量插入到数据库中，MyBatis提供了多种方式来实现这一功能。本文将主要介绍MyBatis批量将List数据插入到数据库的实现。 在MyBatis中，我们可以使用foreach语句来批量插入数据...
MyBatis批量插入数据到Oracle数据库中的两种方式(实例代码)
08-29
MyBatis批量插入数据到Oracle数据库中的两种方式 MyBatis是一款流行的持久层框架，提供了批量插入数据到数据库的功能。本文将通过实例代码，分享MyBatis批量插入数据到Oracle数据库中的两种方式。 第一种方式：...
如何快速更新数据库中的百万条数据
abcd1f2的专栏
04-26 2万+
环境：ubuntu 14.04，Mysql 5.6，python 2.7。 本文使用python的MySQLdb这个库，MySQL Python tutorial这个教程很赞。 MySQLDBdb处理INSERT、UPDATE、DELETE时需要显式得commit()才能使更新有效，这也意味着commit之前的更新语句是放在一个事务中的。 For databases th
Mybatis批量插入的四种方式
热门推荐
一零贰肆的博客
06-13 3万+
测试结果还是显而易见的，循环插入>批量插入>BatchType插入>JDBC批量插入
批量插入10w数据方法对比
最新发布
qq_43586337的博客
04-17 929
批量插入10w数据方法效率对比
Mybatis使用foreach标签实现批量插入
rushbubu的博客
05-29 3371
需要注意的是，为了避免 SQL 注入攻击，在将 List 集合作为参数传入 Mapper 方法时，建议使用 Java 的 Collection 接口或者数组类型，而不是采用字符串拼接的方式动态生成 SQL 语句。方法可以直接将 List 集合作为参数传入到 Mapper 中，然后在 Mapper 的 XML 中使用。标签实现单条数据的插入，但如果需要插入多条数据，则可以使用 Mybatis 提供的。标签遍历了 List 中的每一个 User 对象，并将其属性插入到表中。在 Mybatis 中，可以使用。
Mysql 批量修改四种方式效率对比
y19910825的博客
10-25 2843
这句sql的意思是，更新display_order 字段，如果id=1 则display_order 的值为3，如果id=2 则 display_order 的值为4，如果id=3 则 display_order 的值为5。使用的时候建议分批插入。replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值,而且要保证传的参数id和之前的id是一样的,避免出现改变id的问题.-- 批量更新第三种方法，通过 replace into -->
MyBatis批量插入之forEach与Batch的抉择
qq_50661854的博客
03-20 1727
使用`MyBatis`框架时，让你写一个批量插入，是不是只会在mapper.xml文件中使用`forEach`标签循环呢？那你知道使用`forEach`标签存在的问题吗？
springboot+mybatis通过实体类自动生成数据库表的方法
08-18
Spring Boot + MyBatis 通过实体类自动生成数据库表的方法 本文主要介绍了使用 Spring Boot 2.1.8.RELEASE 和 MyBatis 生成数据库表的方法。该方法通过实体类自动创建数据库表，并提供了详细的示例代码，对学习和...
Spring+Mybatis+Mysql搭建分布式数据库访问框架的方法
08-27
Spring+Mybatis+Mysql搭建分布式数据库访问框架的方法主要介绍了如何使用Spring、Mybatis和Mysql搭建分布式数据库访问框架，解决大规模数据存储和访问效率低下的问题。该方法适用于数据库数量和名称固定、且不是特别...
SpringBoot集成MyBatis-Plus实现国产数据库适配.docx
05-20
SpringBoot 集成 MyBatis-Plus 实现国产数据库适配 MyBatis-Plus 是一款在 MyBatis 的基础上进行扩展的开源工具包，只做增强不做改变，引入 MyBatis-Plus 不会对您现有的 Mybatis 构架产生任何影响。MyBatis-Plus ...
使用事务批量修改数据库
10-10
使用事务批量修改数据库，大师大师答道大大大声大声大大大大大大哒哒哒发放
Mybatis foreach批量插入语法的使用
weixin_43432438的博客
04-17 4065
Mybatis foreach批量插入语法的使用
MyBatis 使用 foreach 批量插入
lizhengyu891231的博客
06-30 5073
MyBatis 使用 foreach 批量插入
Mybatis批量插入的几种方式
qq_58557109的博客
01-22 2770
这种方式是我们比较常用的方式，比传统的循环调用xml插入语句要好得多，在MySql的文档中也提到过这个，如果要优化插入速度时，可以将许多小型操作组合到一个大型操作中。在我们的项目中，会不停地使用批量插入这个方法，而因为MyBatis对于含有的语句（即包含元素），无法采用缓存，所以在每次调用方法时，都会重新解析sql语句。在相关的情况下：将有大量的记录要插入，并且将达到数据库配置的限制（默认情况下，每条语句大约有2000个参数），如果语句本身变得太大，最终可能会出现DB堆栈错误。
MyBatis 批量插入-优化&效率对比
常有理爱摸鱼
10-08 4174
今天我们围绕Mybatis批量插入的for循环插入、batch()伪批量插入、真实批量插入-方法、真实批量插入-配置的效率&优劣进行详解。
MyBatis实现MySQL的批量插入
司马懿的西山居
05-29 2071
在本文中，我们学习了如何使用MyBatis实现MySQL的批量插入操作。我们首先准备了数据库表和MyBatis的映射文件，然后编写了Java代码来执行批量插入操作。通过使用MyBatis的批量插入功能，我们可以显著提高插入大量数据的性能和效率。如果你有任何问题或疑问，欢迎提出。
mysql--批量修改
weixin_70280523的博客
02-10 2642
sql
Mybatis如何实现一个高效的批量插入操作呢？
qq_25073223的博客
11-28 3934
Mybatis实现一个高效的批量插入操作的方法分享
mybatis plus批量插入达梦数据库性能优化
10-27
针对Mybatis plus批量插入达梦数据库的性能优化，可以考虑以下几点： 1.使用JDBC批量插入：Mybatis plus的批量插入是通过循环单条插入实现的，而JDBC批量插入可以一次性插入多条数据，效率更高。 2.调整批量插入的大小：批量插入的大小需要根据具体情况进行调整，过大会导致内存溢出，过小则会影响插入效率。 3.关闭自动提交：关闭自动提交可以减少数据库的IO操作，提高插入效率。 4.使用多线程：可以将数据分成多个批次，使用多线程同时插入，提高插入效率。 5.优化SQL语句：可以通过优化SQL语句、建立索引等方式提高插入效率。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值