MyBatis实现插入大量数据方法详解

目录

• 1、前言
• 2、案例说明
• 3、编码
• 3.1、ExecutorType.BATCH批处理方式插入
• 3.2、foreach循环标签插入
• 3.3、MyBatisPlus自带的saveBatch批量新增方法
• 4、测试
• 5、总结

1、前言

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

• ExecutorType.BATCH批处理方式插入
• foreach循环标签插入
• 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 NU开发者_PythonLL DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = DYNAMIC;

我这里mysql数据库版本是5.5.28

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

truncate table t_user;

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

3、编码

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

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

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

ExecutorType.BATCH介绍：

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

下面开始编码

在UserController新增下面接口

/**
     * 测试大批量数据插入数据库
     * 方式1：用一个 for 循环，把数据一条一条的插入
     * @return
     */
@GetMapping("/insertUser1/{count}")
public jsONResult insertUser1(@PathVariable("count") javascriptInteger count){
    return userService.insertUser1(count);
}

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

在UserMapper中新增addUserOne方法：

Integer addUserOne(User user);

对应XML代码：

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

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;
}

注意事项：

如果单次提交给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.

出现这个错误的原因是：

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

解决方案：

修改eEcSmMySQL的配置参数

打开Mysql命令客户端：

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

show VARIABLES like '%max_allowed_packet%';

结果：

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

set global max_allowed_packet = 200*1024*1024;

设置完成之后，需要关闭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以上，才能实现高性能的批量插入

3.2、foreach循环标签插入

UserController新增方法：

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

insertUser2代码：

public JSONResult insertUser2(Integer count) {
    //获取测试用户集合数据
    List<User> userList = Tools.getUserList(count);
    int countItem = 5000;//每次提交的记录条数
    int userSize = userList.size();
    List<User> userListTemp = new ArrayList<>android();
    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();
}

UserMapper的insertUserBatch方法：

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

对应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>

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)php);
    return JSONResult.success();
}

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、总结

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

到此这篇关于MyBatis实现插入大量数据方法详解的文章就介绍到这了,更多相关MyBatis插入大量数据内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家http://www.devze.com以后多多支持我们！