MySQL批量写入

MySQL

MySQL批量写入，通常可以使用JDBCTemplate的batchUpdate

1
2
3
       public int [] batchUpdate (String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
     
}

使用后，针对批量操作，jdbc driver会render成批量语句发送给MySQL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
       @Override
       public void setValues(PreparedStatement ps, int i) throws SQLException {
            Log log = logs.get(i);
            ps.setLong(1,log.getKeyid());
            ps.setLong(2,log.getUserid());
            ps.setLong(3,log.getPlanid());
            ps.setLong(4,log.getUnitid());
            ps.setLong(5,log.getLevel());
            ps.setInt(6,log.getType());
      }
       @Override
       public int getBatchSize() {
             return logs.size();
      }
});

今天一个线上case排查中发现，最终没有生效，SQL仍然是一条一条的发送出去，整体的性能下降明显。

查阅资料发现，原来MySQL默认是不支持batch的，jdbc driver虽然提供了batch接口，但是默认并没有开启，需要给JDBC Connection增加配置参数rewriteBatchedStatements=true，示例配置：

jdbc:mysql://10.10.10.38:5858?characterEncoding=gbk&rewriteBatchedStatements=true

PrepareStatement在执行executeBatch的时候，会对该参数进行判断，来进行批量操作。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
public int[] executeBatch() throws SQLException {
     synchronized (checkClosed().getConnectionMutex()) {
    
          if (this.connection.isReadOnly()) {
               throw new SQLException(Messages.getString("PreparedStatement.25") //$NON-NLS-1$
                         + Messages.getString("PreparedStatement.26"), //$NON-NLS-1$
                         SQLError.SQL_STATE_ILLEGAL_ARGUMENT);
          }

          if (this.batchedArgs == null || this.batchedArgs.size() == 0) {
           return new int[0];
       }

          // we timeout the entire batch, not individual statements
          int batchTimeout = this.timeoutInMillis;
          this.timeoutInMillis = 0;
    
          resetCancelledState();
         
          try {
               statementBegins();
              
               clearWarnings();

               if (!this.batchHasPlainStatements
                         && this.connection.getRewriteBatchedStatements()) {
                   
                   
                    if (canRewriteAsMultiValueInsertAtSqlLevel()) {
                         return executeBatchedInserts(batchTimeout);
                    }
                   
                    if (this.connection.versionMeetsMinimum(4, 1, 0)
                              && !this.batchHasPlainStatements
                              && this.batchedArgs != null
                              && this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
                         return executePreparedBatchAsMultiStatement(batchTimeout);
                    }
               }

               return executeBatchSerially(batchTimeout);
          } finally {
               this.statementExecuting.set(false);
              
               clearBatch();
          }
     }
}

需要指出的时，该参数在JDBC 5.1.8开始才开始支持，5.1.17进行了优化，如果采用该机制，期望采用5.1.17+的版本。