Mybatis批量插入问题&MySQL参数max_allowed_packet

1、背景:

  在做业务系统时,经常会碰到主子表模型,子表的数据量比较大,如果采用for循环进行insert操作,效率会很慢,MyBatis提供一个批量操作功能foreach,批量插入操作效率会大大提高。

<insert id="insertBatch" parameterType="java.util.List">
    <![CDATA[insert into bd_user (id, dept_id, user_code, user_name, birthday, usable) values ]]>
    <foreach collection="list" item="item" index="index" separator=",">
        <![CDATA[(#{item.id},#{item.deptId},#{item.userCode},#{item.userName},#{item.birthday},#{item.usable})]]>
    </foreach>
</insert>

  随之而来,我们会有一个疑问,这个数据量有没有极限呢,它会受哪些条件影响?带着这样的思考我们进行实验,看看结果如何。

2、测试过程

  1)数据库使用MySQL8.0.19,默认配置。数据使用虚拟机安装,虚拟机配置为2核4G内存。

  2)数据库表结构

CREATE TABLE `bd_user`  (
  `id` bigint(0) NOT NULL,
  `dept_id` bigint(0) NULL DEFAULT NULL COMMENT 部门ID,
  `user_code` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 用户编码,
  `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 用户名称,
  `birthday` date NULL DEFAULT NULL COMMENT 生日,
  `usable` tinyint(1) NULL DEFAULT NULL COMMENT 是否可用,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = 人员 ROW_FORMAT = DYNAMIC;

  3)测试代码

@SpringBootTest(classes = MybatisApplication.class)
public class UserTest {
    @Autowired
    private IUserOperateService userOperateService;
    @Autowired
    private SnowFlake snowFlake;

    @Test
    public void addUser() {
        int start = 0;
        int len = 10000;
        List<User> list = new ArrayList<>(len);
        for (int j = 0; j < len; j++) {
            User model = new User();
            model.setId(snowFlake.nextId());
            model.setDeptId((long) (1 + j % 4));
            model.setUserCode(StringUtils.leftPad((++start) + "", 10, "0"));
            model.setUserName("测试数据" + model.getUserCode());
            model.setUsable(Boolean.TRUE);
            model.setBirthday(new Date());
            list.add(model);
        }
        long startTime = System.currentTimeMillis();
        userOperateService.save(list);
//        for (User user : list) {
//            userOperateService.save(user);
//        }
        System.out.println("耗时:" + (System.currentTimeMillis() - startTime) + "毫秒");
    }
}

  4)实验结果如下(每次操作后数据都会被清空)

记录数 for方式耗时(毫秒) foreach方式耗时(毫秒)
第一次 第二次 第三次 第一次 第二次 第三次
1000 4909 4923 4327 890 860 879
5000 18196 18316 18633 1350 1200 1333
10000 -  - - 1782 1476 1398
100000 - - - 6567 4780 5288
500000       23691 22573 22128

  数据达到100W条记录时,出现如下错误:

com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (99,899,527 > 67,108,864). You can change this value on the server by setting the ‘max_allowed_packet‘ variable.

3、关于MySQL的max_allowed_packet参数

  1)参数说明

  • max_allowed_packet为数据包消息缓存区最大大小,单位字节,默认值67108864(64M),最大值1073741824(1G),最小值1024(1K),参数值须为1024的倍数,非倍数将四舍五入到最接近的倍数。
  • 数据包消息缓存区初始大小为net_buffer_length个字节
  • 每条SQL语句和它的参数都会产生一个数据包缓存区,跟事务无关。
  • 我尝试调整该参数的大小,它并不能提高性能,它的作用在于能够处理大参数,如大BLOB或长字符串就可能调整该参数,还有in后面的记录数也受制于该参数。

  2)查看和设置max_allowed_packet参数

show variables like net_buffer_length;
show variables like max_allowed_packet;  // 查看参数
set global max_allowed_packet=536870912;   // 重新打开数据库连接参数生效,数据库服务重启后参数恢复为默认,想持久化可以在my.cnf中设置该参数

  官网介绍:https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet

4、回到刚才的报错

  刚才我们测试100W条数据报错,如果我们把100W数据拆成2个50W条数据进行保存,则不会报错,耗时大约为插入50条数据的的2倍。

 

Mybatis批量插入问题&MySQL参数max_allowed_packet

上一篇:Zabbix 监控阿里云的redis数据库


下一篇:db2 内存研究