MyBatis大数据量插入方案

1、前言

在开发过程中，有时我们会碰到将大批量的数据入库的场景，那么我们一般有下面三种方式入库：

1. ExecutorType.BATCH批处理方式插入
2. foreach循环标签插入
3. MyBatisPlus自带的saveBatch批量新增方法

下面我们用一个案例来测试一下，看下三种方式哪种效率最好

2、案例说明

现在我数据库新建一张表t_user，建表语句如下：

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = DYNAMIC;
1
2
3
4
5
6
7
8

我这里MySQL数据库版本是5.5.28

然后我用上面三种方式分别向t_user表中存5万条数据，每次存数据前，先清空一下表数据，使用下面语句快速清除表数据：

truncate table t_user;
1

每种方式我都测试5次，然后每种方式我都计算一个耗时平均值，看哪种方式耗时最小

3、编码

这是我的基础项目：https://gitee.com/colinWu_java/spring-boot-base.git

我会在此基础项目上做测试

3.1、ExecutorType.BATCH批处理方式插入

ExecutorType.BATCH介绍：

1. Mybatis内置的ExecutorType有3种，SIMPLE、REUSE、BATCH，默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql
2. 而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优；但batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的
3. 通过批处理的方式，我们就可以在 JDBC 客户端缓存多条 SQL 语句，然后在 flush 或缓存满的时候，将多条 SQL 语句打包发送到数据库执行，这样就可以有效地降低上述两方面的损耗，从而提高系统性能

下面开始编码

在UserController新增下面接口

/**
     * 测试大批量数据插入数据库
     * 方式1：用一个 for 循环，把数据一条一条的插入
     * @return
     */
@GetMapping("/insertUser1/{count}")
public JSONResult insertUser1(@PathVariable("count") Integer count){
    return userService.insertUser1(count);
}
1
2
3
4
5
6
7
8
9

userService代码：

public JSONResult insertUser1(Integer count) {
    //如果自动提交设置为true，将无法控制提交的条数。所以我这里设置为false，，改为统一提交
    SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
    UserMapper uMapper = session.getMapper(UserMapper.class);
    //获取测试用户集合数据
    List<User> userList = Tools.getUserList(count);
    int commitCount = 5000;//每次提交的数量条数
    long startTime = System.currentTimeMillis();
    for(int i=0; i<userList.size(); i++){
        uMapper.addUserOne(userList.get(i));
        if (i != 0 && i % commitCount == 0) {
            session.commit();
        }
    }
    session.commit();
    long endTime = System.currentTimeMillis();
    log.info("方式1耗时：{}", (endTime - startTime));
    return JSONResult.success();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

在UserMapper中新增addUserOne方法：

Integer addUserOne(User user);
1

对应xml代码：

<insert id="addUserOne">
    insert into t_user (name, age, phone) values (#{name}, #{age}, #{phone})
insert>
1
2
3

getUserList方法代码如下，就是获取指定数量的测试用户数据而已：

/**
     * 获取指定数量的用户测试对象
     * @param count 数量
     * @return
     */
public static List<User> getUserList(int count){
    List<User> userList = new ArrayList<>();
    User user = null;
    for(int i=1; i<=count; i++){
        user = new User();
        user.setName("王天霸" + i + "号");
        user.setAge(i);
        user.setAge(i);
        user.setPhone("18022222222");
        userList.add(user);
    }
    return userList;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

注意事项：

如果单次提交给MySQL数据库的数据量太大的话，可能会报如下错误：

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (204444558 > 1048576). You can change this value on the server by setting the max_allowed_packet' variable.
1

• 出现这个错误的原因是：

大数据量的插入或更新会被 max_allowed_packet 参数限制，导致失败，因为MySQL根据配置文件会限制server允许接收的数据包大小

• 解决方案：

修改MySQL的配置参数

打开Mysql命令客户端：

然后输入密码之后输入下面命令：

show VARIABLES like '%max_allowed_packet%';
1

结果：

用这个值除以1024*1024等于200M，那我这里就是设置的200M，根据实际情况设置该值，执行如下命令即可：

set global max_allowed_packet = 200*1024*1024;
1

设置完成之后，需要关闭MySQL服务，然后再重启（注意，这里最大设置为：1G）

重启成功后，需要关掉上面的MySQL命令客户端，再重新打开一次，查看是否设置成功

注意：

• MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入
• MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组SQL语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能
• 只有把rewriteBatchedStatements参数置为true，驱动才会帮你批量执行SQL，另外这个选项对INSERT/UPDATE/DELETE都有效

url: jdbc:mysql://127.0.0.1:3306/test1?allowMultiQueries=true&rewriteBatchedStatements=true
//allowMultiQueries=true，允许一次性执行多条SQL,批量插入时必须在连接地址后面加allowMultiQueries=true这个参数
//rewriteBatchedStatements=true，批量将数据传给MySQL，数据库会更高性能的执行批量处理，MySQL数据库版本在5.1.13以上，才能实现高性能的批量插入
1
2
3

3.2、foreach循环标签插入

UserController新增方法：

/**
     * 测试大批量数据插入数据库
     * 方式2：foreach标签批量插入
     * @return
     */
@GetMapping("/insertUser2/{count}")
public JSONResult insertUser2(@PathVariable("count") Integer count){
    return userService.insertUser2(count);
}
1
2
3
4
5
6
7
8
9

insertUser2代码：

public JSONResult insertUser2(Integer count) {
    //获取测试用户集合数据
    List<User> userList = Tools.getUserList(count);
    int countItem = 5000;//每次提交的记录条数
    int userSize = userList.size();
    List<User> userListTemp = new ArrayList<>();
    long startTime = System.currentTimeMillis();
    for (int i = 0, n=userSize; i < n; i++) {
        User user= userList.get(i);
        userListTemp.add(user);
        if ((i>0 && i % countItem == 0) || i == userSize - 1) {
            //每5000条记录提交一次
            userMapper.insertUserBatch(userListTemp);
            userListTemp.clear();
        }
    }
    long endTime = System.currentTimeMillis();
    log.info("方式2耗时：{}", (endTime - startTime));
    return JSONResult.success();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

UserMapper的insertUserBatch方法：

void insertUserBatch(@Param("userList") List<User> userList);
1

对应xml代码：

<insert id="insertUserBatch">
    insert into t_user (name, age, phone) values
    <foreach collection="userList" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.phone})
    foreach>
insert>
1
2
3
4
5
6

3.3、MyBatisPlus自带的saveBatch批量新增方法

UserController新增方法：

@Autowired
public UserService userService;
/**
     * 测试大批量数据插入数据库
     * 方式3：mybatisplus自带的saveBatch批量新增方法
     * @return
     */
@GetMapping("/insertUser3/{count}")
public JSONResult insertUser3(@PathVariable("count") Integer count){
    //获取测试用户集合数据
    List<User> userList = Tools.getUserList(count);
    long startTime = System.currentTimeMillis();
    userService.saveBatch(userList, 5000);//每次往数据库提交5000条数据
    long endTime = System.currentTimeMillis();
    log.info("方式3耗时：{}", (endTime - startTime));
    return JSONResult.success();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

4、测试

在浏览器中访问地址：http://localhost:8001/user/insertUser1/50000

这里的50000是表示插入5万条数据到t_user表

每次访问后，都记录一下耗时，然后清掉t_user表数据，再访问，反复测试5次

然后再访问http://localhost:8001/user/insertUser2/50000和http://localhost:8001/user/insertUser2/50000

步骤和上面一致，最终我统计出来的结果如下：

很明显，方式一效率最高
注意：以上结果仅是我本地测试情况，大家机器上可能会不同

5、总结

1. 经过今天的测试，我们知道批量插入数据最快的方式就是ExecutorType.BATCH批处理方式插入
2. 有些小的注意点记得在实际开发中谨慎处理，比如url参数配置，还有提交数量不要太大

如果本文对你有帮助的话，记得帮忙点个赞哦