上周，宋哥转发了一篇关于批量数据插入的文章，和大家聊聊批量数据插入的问题，以及如何快速做到。有朋友看了文章提出了不同意见：宋哥认真和BUG同学聊了聊，基本明白了这位朋友的意思，所以自己也写了一个测试用例，重新整理了今天的文章，希望和大家一起讨论这个问题小伙伴们，欢迎小伙伴们提出更好的解决方案。1、思路分析对于批量插入的问题，我们使用JDBC来操作，其实有两种思路：使用for循环，一条一条插入数据（这个需要开启批处理）。生成一个insertsql，类似于这个insertintouser(username,address)values('aa','bb'),('cc','dd')....哪个更快？我们从两个方面考虑这个问题：插入SQL本身的效率。网络输入输出。先说第一种方案，就是用for循环插入：这??种方案的好处是JDBC中的PreparedStatement有预编译功能，预编译后会缓存起来，后面的SQL执行速度会更快，而且JDBC可以开启批处理，这个批处理功能很强大。缺点是很多时候我们的SQL服务器和应用服务器可能不是同一个，所以必须考虑网络IO。如果网络IO比较耗时，可能会拖慢SQL的执行速度。再来说说第二种方案，就是生成一条SQL插入：这??种方案的优点是网络IO只有一次，即使分片处理也只是网络IO的几倍，所以这种方案不会花费太多网络IO时间。当然，这个解决方案有几个缺点。一是SQL太长，甚至可能需要分片后分批处理；二是无法充分发挥PreparedStatement预编译的优势，SQL需要重新解析，无法复用；三是最终生成的SQL太长，数据库管理器解析这么长的SQL需要时间。那么我们最后要考虑的是我们花在网络IO上的时间是否超过了SQL插入的时间？这是我们要考虑的核心问题。2、数据测试接下来我们做一个简单的测试，批量插入5万条数据。首先准备一个简单的测试表：CREATETABLE`user`(`id`int(11)unsignedNOTNULLAUTO_INCREMENT,`username`varchar(255)DEFAULTNULL,`address`varchar(255)DEFAULTNULL,`password`varchar(255)DEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;接下来创建一个SpringBoot工程，引入MyBatis依赖和MySQL驱动，然后在application.properties中配置数据库连接信息：spring.datasource.username=rootspring.datasource.password=123spring.datasource.url=jdbc:mysql:///batch_insert?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true大家需要注意，数据库连接URL地址中多了一个参数rewriteBatchedStatements，这是核心。默认情况下，MySQLJDBC驱动程序会忽略executeBatch()语句，将一组我们希望批量执行的SQL语句打散，一个一个地发送到MySQL数据库。批量插入实际上是单次插入，直接导致性能下降。将rewriteBatchedStatements参数设置为true，数据库驱动会帮我们批量执行SQL。OK，准备工作就完成了。2.1Plan1测试首先我们来看Plan1的测试，也就是一条一条插入（实际是批处理）。首先创建对应的mapper，如下：@MapperpublicinterfaceUserMapper{IntegeraddUserOneByOne(Useruser);}对应的XML文件如下：insertintouser(username,address,password)values(#{username},#{address},#{password})服务如下：@ServicepublicclassUserServiceextendsServiceImplimplementsIUserService{privatestaticfinalLoggerlogger=LoggerFactory.getLogger(UserService.class);@AutowiredUserMapperuserMapper;@AutowiredSqlSessionFactorysqlSessionFactory;@Transactional(rollbackForadd=ExceptionOneidOneList.class)publicusers){SqlSessionsession=sqlSessionFactory.openSession(ExecutorType.BATCH);UserMapperum=session.getMapper(UserMapper.class);longstartTime=System.currentTimeMillis();for(Useruser:users){um.addUserOneByOne(用户）;}session.commit();longendTime=System.currentTimeMillis();logger.info("一条条插入SQL需要时间{}",(endTime-startTime));}}这里我想说：虽然它就是一个一个Insert，但是我们要开启批处理模式（BATCH），这样前后都只用这个SqlSession，如果不使用批处理方式，反复获取Connection和释放Connection会耗费大量时间，效率极低。宋歌不会用这种效率极低的方式给大家测试。接下来写一个简单的测试接口看：@RestControllerpublicclassHelloController{privatestaticfinalLoggerlogger=getLogger(HelloController.class);@AutowiredUserServiceuserService;/***一个一个插入*/@GetMapping("/user2")publicvoiduser2(){Listusers=newArrayList<>();for(inti=0;i<50000;i++){Useru=newUser();u.setAddress("广州:"+i);u.setUsername("张三:"+i);u.setPassword("123:"+i);users.add(u);}userService.addUserOneByOne(users);}}写一个简单的单元测试：/****单元测试加的目的事务是插入后自动回滚，避免影响下一次测试结果*一个一个插入*/@Test@TransactionalvoidaddUserOneByOne(){Listusers=newArrayList<>();for(inti=0;i<50000;i++){Useru=newUser();u.setAddress("广州:"+i);u.setUsername("张三:"+i);u.setPassword("123:"+i);users.add(u);}userService.addUserOneByOne(users);}可以看出耗时901毫秒，不到1秒就插入了5w条数据。2.2方案二测试方案二是生成一条SQL，然后插入。mapper如下：@MapperpublicinterfaceUserMapper{voidaddByOneSQL(@Param("users")Listusers);}对应的SQL如下：insertintouser(username,address,password)values(#{user.username},#{user.address},#{user.password})服务如下:@ServicepublicclassUserServiceextendsServiceImplimplementsIUserService{privatestaticfinalLoggerlogger=LoggerFactory.getLogger(UserService.class);@AutowiredUserMapperuserMapper;@AutowiredSqlSessionFactorysqlSessionFactory;@Transactional(rollbackFor=Exception.class)publicvoidaddByOneSQL(Listusers){longstartTime=System.currentTimeByMillis();userMapper(用户);longendTime=System.currentTimeMillis();logger.info("合并成一条SQL需要时间insert{}",(endTime-startTime));}}然后在单元测试中调整这个方法：/***mergeinto一个SQL插入*/@Test@TransactionalvoidaddByOneSQL(){Listusers=newArrayList<>();for(inti=0;i<50000;i++){Useru=newUser();u.setAddress("广州:"+i);u.setUsername("张三："+i);u.setPassword("123:"+i);users.add(u);}userService.addByOneSQL(users);}可以看到插入5万条数据耗时1805毫秒。可以看到，生成SQL的执行效率还是很差的。另外需要注意的是第二种方案还有一个问题，就是当数据量很大的时候，生成的SQL会特别长，MySQL可能一次处理不了这么大的SQL时间。这时候需要修改mysql配置或者插入数据分片，这些操作都会导致插入时间变长。2.3对比分析显然，方案一更有优势。当批量插入10万或20万条数据时，方案一的优势会更加明显（方案二需要修改mysql配置或对插入数据进行分片）。3、MP是怎么做到的？小伙伴们知道MyBatisPlus中其实有一个批量插入方法saveBatch。我们来看看它的实现源码：@Transactional(rollbackFor=Exception.class)@OverridepublicbooleansaveBatch(CollectionentityList,intbatchSize){StringsqlStatement=getSqlStatement(SqlMethod.INSERT_ONE);returnexecuteBatch(entityList,batchSize,(sqlSession,entity))->sqlSession.insert(sqlStatement,entity));}可以看到这里得到的sqlStatement是一个INSERT_ONE，也就是一条一条插入。再看executeBatch方法，如下：publicstaticbooleanexecuteBatch(ClassentityClass,Loglog,Collectionlist,intbatchSize,BiConsumerconsumer){Assert.isFalse(batchSize<1,"batchSizemustnotbelessthanone");return!CollectionUtils.isEmpty(list)&&executeBatch(entityClass,log,sqlSession->{intsize=list.size();inti=1;for(Eelement:list){consumer.accept(sqlSession,element);if((i%batchSize==0)||i==size){sqlSession.flushStatements();}i++;}});}这里注意return中的第三个参数，是一个lambda表达式，这也是MP中batch插入的核心逻辑，可以看出MP先对数据进行分片（默认分片大小为1000），分片完成后，也是逐条插入。继续看executeBatch方法，你会发现这里的sqlSession其实是批处理sqlSession，不是普通的sqlSession。综上所述，MP中的批量插入方案其实和我们2.1节的批量插入思路是一样的。4.总结好了，经过上面的分析，现在小伙伴们知道批量插入怎么做了吗？本文转载自微信公众号“江南一点鱼”，可通过以下二维码关注。转载本文请联系江南一点鱼公众号。
