大批量数据分批批量插入或更新（Mybatis+MySQL）

Lady伯德

已于 2023-04-03 15:46:35 修改

阅读量2.8k

点赞数 1

文章标签： mybatis mysql 数据库 java spring boot

于 2023-04-03 15:42:21 首次发布

本文链接：https://blog.csdn.net/her_he/article/details/129929228

版权

大批量数据分批批量插入或更新

在MySQL数据库的前提下，插入或更新大批量数据。首先批量插入需要考虑到以下几个因素：

数据库一次可以承受多大或者多少条数据的插入
批量插入是否会占用Mysql资源太久，影响系统整体使用性能
代码中的集合是否会造成内存不足

一、MySql性能考虑

1.1计算插入一条数据需要的空间

以下是mysql各个数据类型的占用的字节，可在菜鸟教程-MySQL查看各个分类的具体字节。
数值类型

注意定长和变长数据类型，将所有数据加起来。假设一条数据需要的字节是100。

1.2查看MySQL参数

查看MySQL一次最大能插入多少

show variables like '%max_allowed_packet%';

1073741824/1024/1024=1024M，1M是10241024/100=10485.76，约等于10000条。那么1024M可插入102410000=1024W条。

1.3MySQL执行时间

链接耗时（30%）
发送query到服务器（20%）
解析query （20%）
插入操作（10% * 词条数目）
插入index （10% * Index的数目）
关闭链接（10%）
可以看出最耗时的是链接。其次是解析。所以插入或更新时尽量采用批量的方式

二、代码实现分批批量查询

2.1Mybatis的xml实现批量sql

直接贴代码，使用DUPLICATE KEY UPDATE是，在设置主键或者唯一键字段的前提下，存在则更新、不存在则插入。这是MySQL中的使用方法

<insert id="saveOrUpdateSlaStandardsRat">
        INSERT INTO sla_standards_rat(date_time,area_name,ec_code,ec_name,belong_trade,apn_name,standards_rat,order_type,create_time)
        VALUES
        <foreach collection="list" index="index" item="item" separator=",">
            (#{item.dateTime},#{item.city},#{item.ecCode},#{item.ecName},#{item.industry},#{item.apnName},#{item.rat},#{item.orderType},now())
        </foreach>
        ON DUPLICATE KEY UPDATE ec_name = values(ec_name), belong_trade=values(belong_trade),area_name=values(area_name),standards_rat=values(standards_rat),update_time=now()
</insert>

2.2分批插入

MAX_NUMBER为自行定义分批的大小。

    private static final Integer MAX_NUMBER = 2000;

    @Override
    public void test() {
        log.info("-----------------开始同步数据--------------------");
        List<SrsfulirmsResourceGprs> resourceGprs = srsfulirmsResourceOssMapper.getSrsfulirmsResourceGprss();
        //分割
        if (!CollectionUtils.isEmpty(resourceGprs)){
            int limit = countStep(resourceGprs.size());
            List<List<SrsfulirmsResourceGprs>> mglist1 = new ArrayList<>();
            Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {
                mglist1.add(resourceGprs.stream().skip(i * MAX_NUMBER).limit(MAX_NUMBER).collect(Collectors.toList()));
            });
            for (List<SrsfulirmsResourceGprs> myList:mglist1){
                int row1 = srsfulirmsResourceMysqlMapper.saveOrUpdateSrsfulirmsResourceGprss(myList);
                log.info("---------------------同步数据row1:"+row1+"------------------------");
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        }
    }

   
    private static Integer countStep(Integer size) {
        return (size + MAX_NUMBER - 1) / MAX_NUMBER;
    }