你确定你的批量方法插入是正确的吗？

麦洛

发布于 2021-08-23 13:57:33

9190

发布于 2021-08-23 13:57:33

文章被收录于专栏：麦洛的历劫之路麦洛的历劫之路

前言

写在前面

各位小伙伴好久不见，时光荏苒，不知不觉已经来到了寒风刺骨的冬天，今天出门差点把自己冻废在路上。在这寒冷的冬天，我带着我对程序探究的热情，来温暖这个寒冷的冬天。虽然好久不见，迫于生计，还是要一有时间就要总结的。

开始今天的分享，初级的小伙伴在面试的过程中，肯定会问到目前主流的持久层框架使用的相关技术问题，当然作为“IT小白”的我，在面试求职者的时候同样也会问关于Mybtatis使用、二级缓存等等相关的问题。比较主流的一个问题就是：如果在数据量大的情况下，你如何进行数据的批量插入，回答我问题的答案，一般就是两个。

通过程序循环多次调用Mybatis单个插入
通过程序调用一次将数组传递至Mybatis单次批量插入

那我们今天就亲身尝试下这两种方式所耗时间的区别。

程序验证

程序批量插入

搭建SpringBoot+Mybatis基础的整合框架，创建表

CREATE TABLE t_user(
  user_id BIGINT PRIMARY KEY COMMENT '用户编号',
  user_name VARCHAR(20) COMMENT '租户名称',
  user_pass VARCHAR(50) COMMENT '用户密码'
)COMMENT '用户信息表';

编写实体类

package com.yang.bean;

/**
 * 用户实体类
 * @author Yang
 *
 */
public class User {
  /*用户名称*/
  private int userId;
  /*用户登录名称*/
  private String userName;
  /* 用户登录密码 */
  private String userPass;

  public int getUserId() {
    return userId;
  }

  public void setUserId(int userId) {
    this.userId = userId;
  }

  public String getUserName() {
    return userName;
  }

  public void setUserName(String userName) {
    this.userName = userName;
  }

  public String getUserPass() {
    return userPass;
  }

  public void setUserPass(String userPass) {
    this.userPass = userPass;
  }
}

编写插入与查询的UserMapper文件

package com.yang.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import com.yang.bean.User;

/**
 * 用户登录数据层
 * @author Yang
 *
 */
@Mapper //标识为mybatis数据层接口
public interface UserDao {
  /**
   *模糊查询用户
   * @return
   */
  List<User> getUser();

  /**
   * 添加用户信息
   * @param user
   * @return
   */
  int addUser(User user);
}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yang.dao.UserDao">
  <!-- 获得用户列表 -->
  <select id="getUser"  resultType="com.yang.bean.User">
    select  user_id userId,user_name userName,user_pass userPass from t_user
  </select>
  <insert id="addUser" parameterType="com.yang.bean.User">
    INSERT INTO  t_user VALUES  (#{userId},#{userName},#{userPass})
  </insert>
</mapper>

开始验证,编写单个插入方法

/**
   * 添加用户信息
   * @return
   */
  public Long addUser(){
    log.info("开始插入数据");
    //记录影响的行数
    int i = 0;
    //模拟生成数组
    List<User> userList = new ArrayList<User>();
    //生成数据
    for (int j=0;j<5000;j++){
      User users = new User();
      users.setUserId(j);
      users.setUserName("test" + j);
      users.setUserPass("abcdef" + j);
      userList.add(users);
    }
    long startNow=System.currentTimeMillis();
    //去单个插入数据
    for (User temp : userList){
      i += userDao.addUser(temp);
    }
    log.info("影响行数"+i);
    long endTime=System.currentTimeMillis();
    long time = endTime - startNow;
    log.info("所耗费时间"+time);
    return time;
  }

插入五万行数据，结果如下，共费时长12263ms

2020-11-20 15:44:25.340  INFO 3182 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 开始插入数据
2020-11-20 15:44:25.390  INFO 3182 --- [nio-8088-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-11-20 15:44:37.622  INFO 3182 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 影响行数50000
2020-11-20 15:44:37.622  INFO 3182 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 所耗费时间12263

程序验证

Mybatis批量插入

编写批量插入SQL

  <!-- 批量插入 -->
  <insert id="bathAddUser" parameterType="java.util.List">
    INSERT INTO  t_user VALUES
    <foreach collection="list" separator=","  item="item">
      (#{item.userId},#{item.userName},#{item.userPass})
    </foreach>
  </insert>

批量插入的业务代码

  /**
   * 批量添加用户信息
   * @return
   */
  public Long bathAddUser(){
    log.info("开始批量插入数据");
    //记录影响的行数
    int i = 0;
    //模拟生成数组
    List<User> userList = new ArrayList<User>();
    //生成数据
    for (int j=0;j<50000;j++){
      User users = new User();
      int index = 50000 +j;
      users.setUserId(index);
      users.setUserName("testBath" + index);
      users.setUserPass("testBath" + index);
      userList.add(users);
    }
    long startNow=System.currentTimeMillis();
    //去批量插入数据
    i += userDao.bathAddUser(userList);
    log.info("影响行数"+i);
    long endTime=System.currentTimeMillis();
    long time = endTime - startNow;
    log.info("所耗费时间"+time);
    return time;
  }

结果验证如下，批量插入五万行数据，结果如下，共费时长1165ms

2020-11-20 15:56:50.972  INFO 3404 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 开始批量插入数据
2020-11-20 15:56:51.598  INFO 3404 --- [nio-8088-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-11-20 15:56:52.156  INFO 3404 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 影响行数50000
2020-11-20 15:56:52.156  INFO 3404 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 所耗费时间1165

结论

结果对比

单个插入	批量插入
耗时12263ms	耗时1165ms

经过程序的验证，五万条数据使用程序一个个插入，和使用Mybatis将SQL进行拼接，使用批量插入SQL，只有三个字段的实体，在耗时层面效率差距≈10.5倍，如果当实体类数据较为复杂，数据量更大的情况下，这个差距会拉取的更大，单个插入，每次插入需要程序将SQL给到MySQL执行，期间交互5万次，而批量插入只需要交互一次，且使用程序循环的过程中也会造成对内存的浪费，所以当面试官再问到此次问题的时候，请毫不犹豫的选择使用Mybatis的foreach标签进行批量插入。

反问

插入是否有限制

反问：Mybatis的批量插入有限制吗？可以随便插入任意条数据吗？来验证一下当Mybatis什么时候会承受不住插入的数据量，会报什么异常？我来用20万条数据做个试验，程序抛出了异常。

### Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
; SQL []; Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.] with root cause

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (10400030 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
  at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3540) ~[mysql-connector-java-5.1.35.jar:5.1.35]
  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2417) ~[mysql-connector-java-5.1.35.jar:5.1.35]

此时程序抛出异常：com.mysql.jdbc.PacketTooBigException，查看资料后发现，MYSQL会根据配置文件会限制server接受的数据包大小，有时候在大的插入和更新会被max_allowed_packet 参数限制掉，导致失败。我们查看MySQL本机的配置为多少。

yangzhenxin@MacBook-Pro bin % ./mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1106
Server version: 5.7.30 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show VARIABLES like '%max_allowed_packet%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 4194304    |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+
2 rows in set (0.01 sec)

mysql>

经过查询，本机Mysql的插入的包的最大限制为4194304（B）,错误提示中的“10400030 > 4194304”，正好对应了本机的MySQL服务的限制，插入的SQL达到了10400030B，故程序超出错误，MySQL对插入的数量并未进行限制，只是限制了包的大小，所以在项目中遇到批量迁移数据的时候，也并不是一股脑的去把所有的程序一次插入，可采用分批+批量插入的方式完成。可参考如下代码：

/**
   * 批量添加用户信息
   * @return
   */
  public Long bathAddUser(){
    log.info("开始批量插入数据");
    //记录影响的行数
    int i = 0;
    //模拟生成数组
    List<User> userList = new ArrayList<User>();
    //生成数据
    for (int j=0;j<200000;j++){
      User users = new User();
      int index = 200000 +j;
      users.setUserId(index);
      users.setUserName("testBath" + index);
      users.setUserPass("testBath" + index);
      userList.add(users);
    }
    long startNow=System.currentTimeMillis();
    //去批量插入数据
    List userListTemp = new ArrayList();
    for(int j=0;j<200000;j++){
      int index = j+1 ;
      //分页插入 一次1000条
      if(index%1000==0){
        userListTemp.add(userList.get(j));
        i += userDao.bathAddUser(userListTemp);
        userListTemp.clear();
      }else{
        userListTemp.add(userList.get(j));
      }
    }
    log.info("影响行数"+i);
    long endTime=System.currentTimeMillis();
    long time = endTime - startNow;
    log.info("所耗费时间"+time);
    return time;
  }

2020-11-20 16:31:02.365  INFO 4101 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 开始批量插入数据
2020-11-20 16:31:02.496  INFO 4101 --- [nio-8088-exec-1] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-11-20 16:31:06.122  INFO 4101 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 影响行数200000
2020-11-20 16:31:06.122  INFO 4101 --- [nio-8088-exec-1] com.yang.service.impl.UserServiceImpl    : 所耗费时间3706

最终插入20万条数据使用了3706ms，比之前单个插入的5万条数据还要快。

实践可以告诉我们真相，也能验证认知中的场景，去深刻的记得自己理论在实践中的具体效果。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2021-08-16，如有侵权请联系 cloudcommunity@tencent.com 删除

mybatis