位置：首页-资讯-数据库

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

2023-08-30 12:43

短信预约 -IT技能 免费直播动态提醒

在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;    }