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倍。