【总结】Mybatis-常用的三种批量插入操作

最近在项目当中，有用到批量新增的操作。总结一下，大概有三种方式来完成这个操作，（1）在业务代码中循环逐条新增（2）在业务代码中循环逐渐新增-开启batch模式（3）使用Mybatis-foreach标签拼接sql执行，逐条更新操作是在数据库中执行的，在业务代码中体现的是一次性更新。下面将通过本地连接MySQL数据库的方式，测试三种方式之间的差异。

 表结构

CREATE TABLE IF NOT EXISTS `role` (
  `id` bigint(20) NOT NULL COMMENT 'id',
  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

 一、在业务代码中循环插入

public void inert() {
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.SIMPLE,false);
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        Long start = System.currentTimeMillis();
        Role role;
        for (int i = 0; i < 10000; i++) {
            role = new Role();
            role.setId(i);
            role.setName("name" + i);
            roleMapper.insert(role);
        }
        log.info("业务代码循环插入10000条数据耗时={}" + (System.currentTimeMillis() - start));
    }

 对应Mybatis-Mapper配置

<insert id="insert" parameterType="com.example.transaction.dto.Role" >
    insert into role (id, name)
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
  </insert>

二、在业务代码中循环新增-开启批处理

public void batchInsert() {
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class);
        Long start = System.currentTimeMillis();
        Role role;
        for (int i = 0; i < 10000; i++) {
            role = new Role();
            role.setId(i);
            role.setName("name" + i);
            roleMapper.insert(role);
        }
        sqlSession.commit();
        log.info("(使用批处理）业务代码循环插入1000000条数据耗时={}" + (System.currentTimeMillis() - start));
    }

对应的mapper配置一样

<insert id="insert" parameterType="com.example.transaction.dto.Role" >
    insert into role (id, name)
    values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
  </insert>

另外，jdbc连接时是加上rewriteBatchedStatements=true才是真正开启批处理。

spring:
  datasource:
    name: test
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&rewriteBatchedStatements=true
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver

总结：Mybatis的ExecutorType有三种（REUSE、SIMPLE、BATCH），默认是SIMPLE，这种模式会为每个语句的执行创建新的预处理，单条提交。而BATCH模式把SQL语句发送到数据库后，数据库预处理SQL，只打印一次SQL语句，多次设置参数。

三、Mybatis-foreach-sql拼接方式

@GetMapping("insert")
    public void inert(@RequestParam("count") Integer count) {
        Long start = System.currentTimeMillis();
        List<Role> list = new ArrayList<>();
        Role role;
        for (int i = 0; i < count; i++) {
            role = new Role();
            role.setId(i);
            role.setName("name" + i);
            list.add(role);
        }
        roleMapper.insertBatch(list);
        log.info("sql使用foreach方式循环插入{}条数据耗时={}", count, (System.currentTimeMillis() - start));
    }

SQL语句如下：

<insert id="insertBatch" parameterType="java.util.List" >
    insert into role (id, name)
    values
    <foreach collection="list" item="role" separator=",">
      (#{role.id,jdbcType=INTEGER}, #{role.name,jdbcType=VARCHAR})
    </foreach>
  </insert>

 关于foreach标签的使用

item：将当前遍历出的元素赋值给指定的变量
index:索引。遍历list的时候是index就是索引，item就是当前值
遍历map的时候index表示的就是map的key，item就是map的值
#{变量名}就能取出变量的值也就是当前遍历出的元素

在本次测试过程中，使用的数据库版本是5.7.19，单条sql的最大数据量是4194304，故当数据量太大时，需调整MySQL的配置。（1）可通过MySQL安装目录下的my.ini文件修改单条sql的数据量，在‘mysqld’段上添加max_allowed_packet = 20M，（2）或者通过命令行set global max_allowed_packet = 20*1024*1024，然后退出命令行，重启MySQL服务即可。

在这次测试过程中，当插入100万条数据时，就会有问题，需调整配置。

四、结果比较 

毫秒	业务代码循环新增	batch批处理	foreach-sql拼接
10000条	17057	495	576
100000条	159297	1095	3265
1000000条	1912256	17666	36304

总结：当数据量比较大时，使用BATCH批处理方式执行批量操作的效率会高很多。