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+的版本。