mysql insert max_Mybatis批量插入问题&MySQL参数max_allowed_packet

1、背景：

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

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

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` dateNULL 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 classUserTest {

@AutowiredprivateIUserOperateService userOperateService;

@AutowiredprivateSnowFlake snowFlake;

@Testpublic voidaddUser() {int start = 0;int len = 10000;

List list = new ArrayList<>(len);for (int j = 0; j < len; j++) {

User model= newUser();

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

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 variableslike 'max_allowed_packet'; //查看参数set global max_allowed_packet=536870912; // 重新打开数据库连接参数生效，数据库服务重启后参数恢复为默认，想持久化可以在my.cnf中设置该参数

4、回到刚才的报错

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