Mybatis批量插入数据对比

前言

业务场景中有很多批量插入数据的场景：
比如：数据库需要造几百万条数据，又或者excel批量导入数据…
这里主要讲使用MyBatis批量导入数据的两种方式，及对比。

环境与数据准备

数据库统一使用了MySQL 5.7.31 ，JDK 1.8，关键pom依赖版本：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.2</version>
</dependency>

建表sql:

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(16) DEFAULT NULL COMMENT '姓名',
  `phone_no` varchar(11) DEFAULT NULL COMMENT '手机号',
  `sex` char(1) DEFAULT NULL COMMENT '性别 0-女 1-男',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

循环单条插入

首先整了个“妈见打”的循环单条insert，大致代码如下

public void insert() {
    long start = System.currentTimeMillis();
    User user;
    for (int i = 0; i < 100000; i++) {
        user = new User();
        user.setName(RandomUtil.getRandomName());
        user.setPhoneNo(RandomUtil.getRandomPhone());
        user.setSex(RandomUtil.getRandomSex());
        user.setCreateTime(new Date());
        userDao.insert(user);
    }
    long end = System.currentTimeMillis();
    double second = (end - start) / 1000;
    logger.info("mybatis插入完成，耗时:{}秒", second);
}

测试结果如下：

数据	10000条耗时（秒）	50000条耗时（秒）	100000条耗时（秒）
1	15.473	69.741	230.596
2	15.473	67.203	225.259
3	14.676	70.805	237.651
4	14.446	72.579	240.32
5	14.243	68.3	235.089
平均	14.703	69.726	233.783

可以看到速度很慢了，不要这样写代码

使用foreach标签进行批量插入

标准的MyBatis批量插入，代码如下：

public void testMybatisInsertList() {
    long start = System.currentTimeMillis();
    User user;
    List<User> list = new ArrayList<>(500000);
    for (int i = 0; i < 100000; i++) {
        user = new User();
        user.setName(RandomUtil.getRandomName());
        user.setPhoneNo(RandomUtil.getRandomPhone());
        user.setSex(RandomUtil.getRandomSex());
        user.setCreateTime(new Date());
        list.add(user);
    }
    userDao.insertList(list);;
    long end = System.currentTimeMillis();
    double second = (end - start) / 1000;
    logger.info("mybatis插入完成，耗时:{}秒", second);
}

mapper.xml:

<insert id="insertList" useGeneratedKeys="true" keyProperty="id" parameterType="com.cybergeralt.model.User">
        INSERT INTO `user`(`name`, `phone_no`, `sex`, `create_time`) VALUES
        <foreach collection="list" item="list" index="index" separator=",">
            (#{list.name,jdbcType=VARCHAR}, #{list.phoneNo,jdbcType=VARCHAR},#{list.sex,jdbcType=VARCHAR},#{list.createTime,jdbcType=TIMESTAMP})
        </foreach>
</insert>

测试结果如下：

数据	10000条耗时（秒）	50000条耗时（秒）	100000条耗时（秒）	500000条耗时（秒）
1	1.017	1.912	2.971	12.536
2	1.065	1.92	3.036	12.454
3	0.999	1.938	3.024	11.598
4	0.985	1.882	2.963	11.917
5	1.018	1.889	2.957	11.868
平均	1.017	1.908	2.99	12.075

速度比想象中的快很多
ps：MySQL对语句大小有限制，我测试批量插入50000条数据的时候，就会报错：

com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (4,736,064 > 4,194,304). You can change this value on the server by setting the 'max_allowed_packet' variable.

解决：需要增大数据库配置max_allowed_packet，默认为4M。

SET GLOBAL max_allowed_packet=268435456; ##改为256M

结

通过对比可以看出，循环单条插入效率远低于使用foreach标签进行批量插入。
所以建议MyBatis使用foreach标签进行批量插入

转自一位大佬：https://cybergeralt.com/archives/99