JDBC批量处理效率实验

JDBC对数据库进行批量插入操作，使用不同的方式有很大差别，现将每种方式的测试做如下记录。
测试方式：

• 向数据库中的一张表中连续插入100000条记录
• 使用jdbc+mysql数据库进行测试，项目中仅加载mysql驱动，MySQL数据库采用5.7的版本
• 主测试类中仅实现业务逻辑，具体的时效监测采用JDK中InvocationHandler实例处理，再使用JDK的动态代理生成代理类
• mysql建表语句：

create table batch_test(
	id BIGINT not null primary key auto_increment,
	name varchar(100)
);

测试过程记录：

• 方式一：使用jdbc的statement进行处理

  • 接口代码：

  public interface BatchHandler {
      /**
       * 批量处理：方式一
       */
      public void batch_mode1() throws Exception;
  
      /**
       * 批量处理：方式二
       */
      public void batch_mode2() throws Exception;
  
      /**
       * 批量处理：方式三
       */
      public void batch_mode3() throws Exception;
  
      /**
       * 批量处理：方式四
       */
      public void batch_mode4() throws Exception;
      
  	/**
       * 批量处理：方式五
       */
      public void batch_mode5() throws Exception;
  }
  

  • 业务代码：

  @Override
  public void batch_mode1() throws Exception {
      Connection connection = JdbcUtils.getConnection();
      for(int i=0;i<count;i++){
          String sql = "insert into batch_test(name) values('name"+i+"')";
          Statement st = connection.createStatement();
          st.execute(sql);
          st.close();
      }
      connection.close();
  }
  

  • InvocationHandler代码：

  public class BatchTimeInvocationHandler implements InvocationHandler {
  
      private Object target ;
  
      public BatchTimeInvocationHandler(Object target){
          this.target = target ;
      }
  
      @Override
      public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
          System.out.println(method.getName()+" execute begin: " );
          long start = System.currentTimeMillis() ;
          Object ret = method.invoke(target, args);
          long end = System.currentTimeMillis();
          long spend_time = end - start ;
          System.out.println(method.getName()+" execute end: " );
          System.out.println(method.getName()+ " spend_time is : " + spend_time);
          return ret;
      }
  }
  

  • 结果：

  batch_mode1 execute begin: 
  batch_mode1 execute end: 
  batch_mode1 spend_time is : 144079
  

  • 总结
    此方式性能非常低下，花费时间较长，为144079毫秒

• 方式二：使用jdbc的preparedStatement进行处理

  • 业务代码

  @Override
  public void batch_mode2() throws Exception {
      Connection connection = JdbcUtils.getConnection();
      String sql = "insert into batch_test(name) values(?)";
      PreparedStatement ps = connection.prepareStatement(sql);
      for(int i=0;i<count;i++){
          ps.setString(1, "name" + i);
          ps.execute();
      }
      JdbcUtils.releaseResources(connection, ps, null);
  }
  

  • 结果

  batch_mode2 execute begin: 
  batch_mode2 execute end: 
  batch_mode2 spend_time is : 140029
  

  • 总结
    采用preparedStatement预编译的方式，在100000条数据的批量中，性能提升了25%左右，也就是说此过程中mysql在方式一的过程中，数据库硬解析sql语句的时间占据了25%左右。

• 方式三：使用jdbc的preparedStatement+addBatch的方式进行处理

  • 业务代码

  @Override
  public void batch_mode3() throws Exception {
      Connection connection = JdbcUtils.getConnection();
      String sql = "insert into batch_test(name) values(?)";
      PreparedStatement ps = connection.prepareStatement(sql);
      for(int i=0;i<count;i++){
          ps.setString(1, "name" + i);
          ps.addBatch();
          if((i + 1) % 1000 == 0){
              ps.executeBatch();
              ps.clearBatch();
          }
      }
      JdbcUtils.releaseResources(connection, ps, null);
  }
  

  • 结果

  batch_mode3 execute begin: 
  batch_mode3 execute end: 
  batch_mode3 spend_time is : 5017
  

  • 总结
    可以看到，采用preparedStatement+jdbc批量处理的方式，性能得到了很大的提升。

• 方式四：在方式三的基础加入事务一次性提交处理

  • 业务代码

  @Override
  public void batch_mode4() throws Exception {
      Connection connection = JdbcUtils.getConnection();
      connection.setAutoCommit(false);
      String sql = "insert into batch_test(name) values(?)";
      PreparedStatement ps = connection.prepareStatement(sql);
      for(int i=0;i<count;i++){
          ps.setString(1, "name" + i);
          ps.addBatch();
          if((i + 1) % 1000 == 0){
              ps.executeBatch();
              ps.clearBatch();
          }
      }
      connection.commit();
      JdbcUtils.releaseResources(connection, ps, null);
  }
  

  • 结果

  batch_mode4 execute begin: 
  batch_mode4 execute end: 
  batch_mode4 spend_time is : 4770
  

  • 总结
    在100000条记录的情况下，较方式三有了些许提升，如果批量数据加大的话，猜测提升会更加明显。

• 方式五：将方式四中的一次性提交修改为10000笔每次分批提交处理

  • 业务代码

  @Override
  public void batch_mode5() throws Exception {
      Connection connection = JdbcUtils.getConnection();
      connection.setAutoCommit(false);
      String sql = "insert into batch_test(name) values(?)";
      PreparedStatement ps = connection.prepareStatement(sql);
      for(int i=0;i<count;i++){
          ps.setString(1, "name" + i);
          ps.addBatch();
          if((i + 1) % 10000 == 0){
              ps.executeBatch();
              ps.clearBatch();
              connection.commit();
          }
      }
      JdbcUtils.releaseResources(connection, ps, null);
  }
  

  • 结果

  batch_mode5 execute begin: 
  batch_mode5 execute end: 
  batch_mode5 spend_time is : 4919
  

  • 总结
    修改按照批次提交的策略在100000笔数据的批量中，并没有产生优化效率，反而有所下降，猜想是因为批量数据量的原因，在1000000万级别的数据量中，理论上应该会有提升，感兴趣的同学可以自行测试一下。

• tips：
  在mysql5.7的版本中，默认是不支持executeBatch批量处理的，需要修改数据库的连接字符串，添加rewriteBatchedStatements=true 来修改会话中的策略。