起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第28天，点击查看活动详情

前言

批量插入功能是我们日常工作中比较常见的业务功能之一，但是如果没有正确的使用批量插入则会导致速度非常的慢影响性能，本文将讲解业务系统我们如何实现批量插入。

实现方案

实现的方案大致有如下几种：

1. 循环单次插入
2. Mybatis-Plus批量插入
3. 原生批量插入

针对这几种方案，我们插入1000数据来进行性能对比。

循环单次插入

实例代码

   @RequestMapping("/insertUser")
    public String insert()
    {
       long startTime=System.currentTimeMillis();
       for(int i=0;i<1000;i++)
       {
           TUser tUser =new TUser();
           tUser.setName("my_"+i); 
           tUser.setAge(i);
           userService.save(tUser);
       }
       
       long costTime=System.currentTimeMillis()-startTime;
       logger.info("insertUser cost time :{}",costTime);
       return "成功";
    }

测试结果

 [http-nio-9090-exec-4] INFO  [] c.s.m.controller.UserController - insertUser cost time :34584ms

从测试结果可以看出插入1000条语句花了34秒左右，这性能是无法接受的。

Mybatis-Plus批量插入

Mybatis-Plus实现批量插入实现可以调用saveBatch的方法来进行批量插入

实例代码

 /**
     * mybatis自带批量插入
     * @return
     */
    @RequestMapping("/saveBatch")
    public String saveBatch()
    {
       long startTime=System.currentTimeMillis();
       List<TUser> userlist =new ArrayList<TUser>();
       for(int i=0;i<1000;i++)
       {
           TUser tUser =new TUser();
           tUser.setName("my_"+i); 
           tUser.setAge(i);
           userlist.add(tUser);
       }
       
       tUserService.saveBatch(userlist, 1000);
       long costTime=System.currentTimeMillis()-startTime;
       logger.info("saveBatch cost time :{}",costTime);
       return "成功";
    }

注意事项:

设置rewriteBatchedStatements值为true，否则会严重影响效率

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

测试结果

2022-04-29 17:57:09.793 [http-nio-9090-exec-7] INFO  [] c.s.m.controller.UserController - saveBatch cost time :435ms

从测试结果来看插入1000条语句花了435毫秒。

原生批量插入

原生批量插入实现比较简单，就是在xml文件中采用foreach循环插入

定义xml语句

 <insert id="addBatchUser" parameterType="com.skywares.mybatisplus.pojo.TUser">
     insert into t_user_1(name,age)
     values
      <foreach collection="list" item="item" index="index" separator=",">
            (
              #{item.name},
              #{item.age}
            )
     </foreach>
 </insert>
 

实例代码

   @RequestMapping("/addBatchUser")
    public String batchInsertUser()
    {
       long startTime=System.currentTimeMillis();
       List<TUser> userlist =new ArrayList<TUser>();
       for(int i=0;i<1000;i++)
       {
           TUser tUser =new TUser();
           tUser.setName("my"+i); 
           tUser.setAge(i);
           userlist.add(tUser);
       }
       userService.addBatchUser(userlist);
       long costTime=System.currentTimeMillis()-startTime;
       logger.info("addBatchUser cost time :{}",costTime);
       return "成功";
    }

注意事项:

查看Mysql的max_allowed_packet参数值，默认该插入值为1M.需要将之调整为4M.

当发送的批量的语句的长度大于1M是，批量插入会报错。

Mysql链接中需要添加allowMultiQueries=true参数

默认情况下Mysql不支持批量插入，所以我们需要开启允许批量插入。

事务需要控制大小，事务太大也会影响执行的效率，且大的事务也容易锁表

MySQL中的innodb_log_buffer_size配置项，默认大小为8M，数据默认存放在缓存区，但数据超过缓存区的大少后，才会把数据刷到磁盘中,所以我们需要控制数据提交的大少，尽量在达到缓冲区最大值之前进行事务提交，提交批量插入的效率。

执行结果

2022-04-29 18:10:53.323 [http-nio-9090-exec-5] INFO  [] c.s.m.controller.UserController - addBatchUser cost time :101

从测试结果可以看出，这种方法是将SQL拼接成一条SQl语句插入数据库，插入1000条语句花了101毫秒。

升级

将测试的数据有1000条提升到10000条进行测试，发现原生SQL插入执行的时间大概为10秒左右,这是因为我没有调整mysql的innodb_log_buffer_siz大少，而Mybatis-Plus批量插入的时间大概为2秒左右。

总结

从测试的结果可以看出采用第三种原生的批量插入的效率最高，但是存在的风险也是最大，单条SQL拼接优化方案并不是万能的，只能在特定数量区间之内才能安全的提升性能，所以需要结合实际的场景来选择合适的方案。