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