Springboot 批量插入优化

• 优化背景
• 在一次批量导入数据过程中，3000条数据，postman请求耗时：5.65s
  
• 方案说明
• 代码

      public void importSupplyOrder(MultipartFile file, String recycleId) {
          long methodStart = System.currentTimeMillis();
           //从数据库查询相关信息
          .................
          ArrayList<SupplyOrder> supplyOrders = new ArrayList<>();
          long forStart = System.currentTimeMillis();
          for (int i = 0; i <importSupplyOrderVos.size(); i++) {
             //参数判断，写入数据List 封装
             .................
          }
          long forEnd = System.currentTimeMillis();
          saveBatch(supplyOrders);
  //        supplyOrderMapper.importSupplyOrder(supplyOrders);
          long insertEnd = System.currentTimeMillis();
          log.info("循环前消耗时间[{}]",forStart-methodStart);
          log.info("循环消耗时间[{}]",forEnd-forStart);
          log.info("insert消耗时间[{}]",insertEnd-forEnd);
      }

• 优化前方案：使用mybatisplus 定义的 saveBatch(....) 方法
• 优化后方案：在 xml 文件中使用sql 语句处理

      <insert id="importSupplyOrder">
          insert into supply_order (
          	<!--表字段-->
             .......
          )values
          <foreach collection="supplyOrders"  open="(" separator="),(" close=")" item="orderVo">
             <!--字段值-->
             .......               
          </foreach>
      </insert>

• 优化前后耗时对比
• 3000条数据，日志打印

  数据量		优化前	优化后	差值
  3000	循环前耗时	846	776	正常波动，忽略
  	循环耗时	30	21	正常波动，忽略
  	写入耗时	4528	1121	3407
  10000	循环前耗时	1405	1344	正常波动，忽略
  	循环耗时	51	54	正常波动，忽略
  	写入耗时	12074	3080	8994

  
  
• post请求对比
  
•  原因分析
• 优化前sql 执行log

  INSERT INTO supply_order ( supply_order, supply_user_id, supply_user_name, goods_id, goods_name, tax_code, car_num, gross_weight, tare_weight, deduct_weight, net_weight, unit, price, total_price, status, create_time, supply_order_time, operation_time, info_id, recycle_id, recycle_name, supply_credential_num ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

• 优化后sql 执行log

  insert into supply_order ( supply_order, supply_user_id, supply_user_name, goods_id, goods_name, tax_code, size, car_num, gross_weight, tare_weight, deduct_weight, net_weight, unit, price, total_price, status, create_time, supply_order_time, operation_time, info_id, description, recycle_id, recycle_name, supply_credential_num, unit_id )values 
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ),
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ),
  ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ),

  
  
• 源码分析

•  //com.baomidou.mybatisplus.extension.service.impl    ServiceImpl
      /**
       * 批量插入
       *
       * @param entityList
       * @param batchSize
       * @return
       */
      @Transactional(rollbackFor = Exception.class)
      @Override
      public boolean saveBatch(Collection<T> entityList, int batchSize) {
          int i = 0;
          String sqlStatement = sqlStatement(SqlMethod.INSERT_ONE);
          try (SqlSession batchSqlSession = sqlSessionBatch()) {
              for (T anEntityList : entityList) {
                  batchSqlSession.insert(sqlStatement, anEntityList);      //每条数据都会执行，
                  if (i >= 1 && i % batchSize == 0) {
                      batchSqlSession.flushStatements();
                  }
                  i++;
              }
              batchSqlSession.flushStatements();       //预写入
          }
          return true;
      }

  //org.apache.ibatis.session.defaults ;    DefaultSqlSession
    @Override
    public int insert(String statement, Object parameter) {
      return update(statement, parameter);
    }
    @Override
    public int update(String statement, Object parameter) {
      try {
        dirty = true;
        MappedStatement ms = configuration.getMappedStatement(statement);
        return executor.update(ms, wrapCollection(parameter));
      } catch (Exception e) {
        throw ExceptionFactory.wrapException("Error updating database.  Cause: " + e, e);
      } finally {
        ErrorContext.instance().reset();
      }
    }

  //优化前   mybatisplus saveBatch(...) 方法
  //org.apache.ibatis.executor;  BatchExecutor
    @Override
    public int doUpdate(MappedStatement ms, Object parameterObject) throws SQLException {
      final Configuration configuration = ms.getConfiguration();
      final StatementHandler handler = configuration.newStatementHandler(this, ms, parameterObject, RowBounds.DEFAULT, null, null);
      final BoundSql boundSql = handler.getBoundSql();
      final String sql = boundSql.getSql();
      final Statement stmt;
      if (sql.equals(currentSql) && ms.equals(currentStatement)) {
        int last = statementList.size() - 1;
        stmt = statementList.get(last);
        applyTransactionTimeout(stmt);
       handler.parameterize(stmt);//fix Issues 322
        BatchResult batchResult = batchResultList.get(last);
        batchResult.addParameterObject(parameterObject);
      } else {
        Connection connection = getConnection(ms.getStatementLog());
        stmt = handler.prepare(connection, transaction.getTimeout());
        handler.parameterize(stmt);    //fix Issues 322
        currentSql = sql;
        currentStatement = ms;
        statementList.add(stmt);
        batchResultList.add(new BatchResult(ms, sql, parameterObject));
      }
    // handler.parameterize(stmt);
      handler.batch(stmt);
      return BATCH_UPDATE_RETURN_VALUE;
    }
  //优化后   
  // org.apache.ibatis.executor;    SimpleExecutor
    @Override
    public int doUpdate(MappedStatement ms, Object parameter) throws SQLException {
      Statement stmt = null;
      try {
        Configuration configuration = ms.getConfiguration();
        StatementHandler handler = configuration.newStatementHandler(this, ms, parameter, RowBounds.DEFAULT, null, null);
        stmt = prepareStatement(handler, ms.getStatementLog());
        return handler.update(stmt);
      } finally {
        closeStatement(stmt);
      }
    }

  
  
• 优化后需要考虑的问题
• mysql  有包最大允许限制   4,194,304字节，因此在处理过程中要手动对数据进行分割

  SHOW VARIABLES LIKE  'max_allowed_packet'

  
  
• 数据分割示例：

      batchSize = 1000    
          
  for(int i = 0;true;i++){
  
              if(((i+1)*batchSize)<supplyOrders.size()){
                  supplyOrderMapper.importSupplyOrder(supplyOrders.subList(i*batchSize,(i+1)*batchSize ));
              }else{
                  supplyOrderMapper.importSupplyOrder(supplyOrders.subList(i*batchSize,supplyOrders.size() ));
                  break;
              }
          }