10万条数据批量插入，到底怎么做才快？

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《10万条数据批量插入，到底怎么做才快？》，文章讲解的知识点主要包括MySQL、Java、jdbc、mybatis-plus，如果你对数据库方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

@[toc]
上周松哥转载了一个数据批量插入的文章，里边和大家聊了一下数据批量插入的问题，批量插入到底怎么做才快。

有个小伙伴看了文章后提出了不同的意见：

松哥认真和 BUG 同学聊了下，基本上明白了这个小伙伴的意思，于是我自己也写了个测试案例，重新整理了今天这篇文章，希望和小伙伴们一起探讨这个问题，也欢迎小伙伴们提出更好的方案。

1. 思路分析

批量插入这个问题，我们用 JDBC 操作，其实就是两种思路吧：

1. 用一个 for 循环，把数据一条一条的插入（这种需要开启批处理）。
2. 生成一条插入 sql，类似这种

   CREATE TABLE `user` (
     `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
     `username` varchar(255) DEFAULT NULL,
     `address` varchar(255) DEFAULT NULL,
     `password` varchar(255) DEFAULT NULL,
     PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

   接下来创建一个 Spring Boot 工程，引入 MyBatis 依赖和 MySQL 驱动，然后 application.properties 中配置一下数据库连接信息：

   spring.datasource.username=root
   spring.datasource.password=123
   spring.datasource.url=jdbc:mysql:///batch_insert?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true

   大家需要注意，这个数据库连接 URL 地址中多了一个参数 rewriteBatchedStatements，这是核心。

   MySQL JDBC 驱动在默认情况下会无视

   @Mapper
   public interface UserMapper {
       Integer addUserOneByOne(User user);
   }

   对应的 XML 文件如下：

   
       insert into user (username,address,password) values (#{username},#{address},#{password})
   

   service 如下：

   @Service
   public class UserService extends ServiceImpl implements IUserService {
       private static final Logger logger = LoggerFactory.getLogger(UserService.class);
       @Autowired
       UserMapper userMapper;
       @Autowired
       SqlSessionFactory sqlSessionFactory;
   
       @Transactional(rollbackFor = Exception.class)
       public void addUserOneByOne(List users) {
           SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
           UserMapper um = session.getMapper(UserMapper.class);
           long startTime = System.currentTimeMillis();
           for (User user : users) {
               um.addUserOneByOne(user);
           }
           session.commit();
           long endTime = System.currentTimeMillis();
           logger.info("一条条插入 SQL 耗费时间 {}", (endTime - startTime));
       }
   }
   

   这里我要说一下：

   虽然是一条一条的插入，但是我们要开启批处理模式（BATCH），这样前前后后就只用这一个 SqlSession，如果不采用批处理模式，反反复复的获取 Connection 以及释放 Connection 会耗费大量时间，效率奇低，这种效率奇低的方式松哥就不给大家测试了。

   接下来写一个简单的测试接口看下：

   @RestController
   public class HelloController {
       private static final Logger logger = getLogger(HelloController.class);
       @Autowired
       UserService userService;
       /**
        * 一条一条插入
        */
       @GetMapping("/user2")
       public void user2() {
           List users = new ArrayList();
           for (int i = 0; i 

   写个简单的单元测试：

   /**
    * 
    * 单元测试加事务的目的是为了插入之后自动回滚，避免影响下一次测试结果
    * 一条一条插入
    */
   @Test
   @Transactional
   void addUserOneByOne() {
       List users = new ArrayList();
       for (int i = 0; i 

   

   可以看到，耗时 901 毫秒，5w 条数据插入不到 1 秒。

   2.2 方案二测试

   方案二是生成一条 SQL 然后插入。

   mapper 如下：

   @Mapper
   public interface UserMapper {
       void addByOneSQL(@Param("users") List users);
   }

   对应的 SQL 如下：

   
       insert into user (username,address,password) values
       
           (#{user.username},#{user.address},#{user.password})
       

   service 如下：

   @Service
   public class UserService extends ServiceImpl implements IUserService {
       private static final Logger logger = LoggerFactory.getLogger(UserService.class);
       @Autowired
       UserMapper userMapper;
       @Autowired
       SqlSessionFactory sqlSessionFactory;
       @Transactional(rollbackFor = Exception.class)
       public void addByOneSQL(List users) {
           long startTime = System.currentTimeMillis();
           userMapper.addByOneSQL(users);
           long endTime = System.currentTimeMillis();
           logger.info("合并成一条 SQL 插入耗费时间 {}", (endTime - startTime));
       }
   }

   然后在单元测试中调一下这个方法：

   /**
    * 合并成一条 SQL 插入
    */
   @Test
   @Transactional
   void addByOneSQL() {
       List users = new ArrayList();
       for (int i = 0; i 

   

   可以看到插入 5 万条数据耗时 1805 毫秒。

   可以看到，生成一条 SQL 的执行效率还是要差一点。

   另外还需要注意，第二种方案还有一个问题，就是当数据量大的时候，生成的 SQL 将特别的长，MySQL 可能一次性处理不了这么大的 SQL，这个时候就需要修改 MySQL 的配置或者对待插入的数据进行分片处理了，这些操作又会导致插入时间更长。

   2.3 对比分析

   很明显，方案一更具优势。当批量插入十万、二十万数据的时候，方案一的优势会更加明显（方案二则需要修改 MySQL 配置或者对待插入数据进行分片）。

   3. MP 怎么做的？

   小伙伴们知道，其实 MyBatis Plus 里边也有一个批量插入的方法 saveBatch，我们来看看它的实现源码：

   @Transactional(rollbackFor = Exception.class)
   @Override
   public boolean saveBatch(Collection entityList, int batchSize) {
       String sqlStatement = getSqlStatement(SqlMethod.INSERT_ONE);
       return executeBatch(entityList, batchSize, (sqlSession, entity) -> sqlSession.insert(sqlStatement, entity));
   }

   可以看到，这里拿到的 sqlStatement 就是一个

   public static  boolean executeBatch(Class> entityClass, Log log, Collection list, int batchSize, BiConsumer consumer) {
       Assert.isFalse(batchSize  {
           int size = list.size();
           int i = 1;
           for (E element : list) {
               consumer.accept(sqlSession, element);
               if ((i % batchSize == 0) || i == size) {
                   sqlSession.flushStatements();
               }
               i++;
           }
       });
   }

   这里注意 return 中的第三个参数，是一个 lambda 表达式，这也是 MP 中批量插入的核心逻辑，可以看到，MP 先对数据进行分片（默认分片大小是 1000），分片完成之后，也是一条一条的插入。继续查看 executeBatch 方法，就会发现这里的 sqlSession 其实也是一个批处理的 sqlSession，并非普通的 sqlSession。

   综上，MP 中的批量插入方案跟我们 2.1 小节的批量插入思路其实是一样的。

   4. 小结

   好啦，经过上面的分析，现在小伙伴们知道了批量插入该怎么做了吧？

   松哥提供了一个测试案例，公众号后台回复批量插入测试获取案例地址，案例中有三个单元测试方法，直接运行，就可以看到批量插入的时间差异（数据库脚本在 resources 目录下）。

   感兴趣的小伙伴不妨试试~

   最后再次感谢 BUG 童鞋提出的意见~

   文中关于mysql的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《10万条数据批量插入，到底怎么做才快？》文章吧，也可关注golang学习网公众号了解相关技术文章。