為了測(cè)試分布式定時(shí)任務(wù)的性能篮赢，以處理數(shù)據(jù)庫(kù)數(shù)據(jù)為基礎(chǔ)測(cè)試币砂，所以需要在數(shù)據(jù)庫(kù)里初始化一個(gè)表建峭，大概是200w條數(shù)據(jù)。
1决摧、因?yàn)槭且粋€(gè)SpringBoot服務(wù)亿蒸，spring提供的JdbcTemplate有批量插入的功能，所以自然想到直接用它蜜徽，代碼是這個(gè)樣子：
private void initialDatabase() {
long beginTime = new Date().getTime();
final int total = 2*1000000;
jdbcTemplate.batchUpdate(initialDatabaseSql, new BatchPreparedStatementSetter() {
public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
preparedStatement.setString(1,String.valueOf(i));
preparedStatement.setString(2,"data for test timerTask");
}
public int getBatchSize() {
return total;
}
});
long endTime = new Date().getTime();
System.out.println("---------初始化數(shù)據(jù)完畢祝懂，耗時(shí)："+(endTime-beginTime)+"ms----------");
// System.exit(0);
}
但是結(jié)果確出人意料，平均耗時(shí)大概是372817ms/1w條拘鞋，簡(jiǎn)直可怕砚蓬，想到時(shí)間可能會(huì)長(zhǎng)，但是沒想到這么長(zhǎng)盆色，查了資料說(shuō)要開啟批量插入支持灰蛙，debug了下確實(shí)走的批量插入祟剔，不明白，應(yīng)該是哪里出了問(wèn)題摩梧，畢竟這么成熟的框架物延，性能不至于這么差，之后會(huì)分析原因仅父。
2叛薯、用框架不行，那就用最原始的JDBC操作了笙纤，直接用java.sql中的批量操作耗溜，代碼是這個(gè)樣子的：
private void initialDatabaseNewest() {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/timer?useUnicode=true&characterEncoding=utf-8","root","123456");
connection.setAutoCommit(false);
String sql = "insert into message(id,message,createTime) values(?,?,SYSDATE()) ";
statement = connection.prepareStatement(sql);
long begin = System.currentTimeMillis();
int i =0,j=1;
for (;j<100;j++) {
for (; i < 10000*j; i++) {
statement.setString(1,String.valueOf(i));
statement.setString(2,"data for test timerTask");
statement.addBatch();
}
statement.executeBatch();
}
connection.commit();
long end = System.currentTimeMillis();
System.out.println("---------初始化數(shù)據(jù)完畢，耗時(shí)："+(end-begin)+"ms----------");
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
statement.close();
connection.close();
} catch (SQLException e) {
}
}
耗時(shí)2643ms/1w條省容，看著比之前快很多抖拴，但還是不夠快，進(jìn)入源碼看看腥椒，雖然用的是Java自帶的阿宅，算是往下了一層，但是這樣的批量底層實(shí)現(xiàn)還是一條一條執(zhí)行：
for(this.batchCommandIndex = 0; this.batchCommandIndex < nbrCommands; ++this.batchCommandIndex) {
Object arg = this.batchedArgs.get(this.batchCommandIndex);
try {
if (!(arg instanceof String)) {
PreparedStatement.BatchParams paramArg = (PreparedStatement.BatchParams)arg;
updateCounts[this.batchCommandIndex] = this.executeUpdateInternal(paramArg.parameterStrings, paramArg.parameterStreams, paramArg.isStream, paramArg.streamLengths, paramArg.isNull, true);
this.getBatchedGeneratedKeys(this.containsOnDuplicateKeyUpdateInSQL() ? 1 : 0);
} else {
updateCounts[this.batchCommandIndex] = this.executeUpdateInternal((String)arg, true, this.retrieveGeneratedKeys);
this.getBatchedGeneratedKeys(this.results.getFirstCharOfQuery() == 'I' && this.containsOnDuplicateKeyInString((String)arg) ? 1 : 0);
}
}
}
debug這里的時(shí)候發(fā)現(xiàn)了極其詭異的事笼蛛，1和2都是走的上面這段代碼洒放，但是1慢了太多，不明白為啥伐弹。
debug時(shí)發(fā)現(xiàn)這么一段：
try {
this.statementBegins();
this.clearWarnings();
long[] var3;
if (!this.batchHasPlainStatements && this.connection.getRewriteBatchedStatements()) {
if (this.canRewriteAsMultiValueInsertAtSqlLevel()) {
var3 = this.executeBatchedInserts(batchTimeout); //***************************
return var3;
}
if (this.connection.versionMeetsMinimum(4, 1, 0) && !this.batchHasPlainStatements && this.batchedArgs != null && this.batchedArgs.size() > 3) {
var3 = this.executePreparedBatchAsMultiStatement(batchTimeout); //***************************
return var3;
}
}
var3 = this.executeBatchSerially(batchTimeout); //默認(rèn)執(zhí)行的是這一步 //***************************
return var3;
}
1和2默認(rèn)都是走的executeBatchSerially這個(gè)方法拉馋，里面就是上一段代碼那樣一條一條遍歷執(zhí)行sql(不明白為啥時(shí)間差那么多)。那executeBatchedInserts呢惨好？進(jìn)入看一下：
for(int i = 0; i < ex; ++i) {
if (i != 0 && i % numValuesPerBatch == 0) {
try {
updateCountRunningTotal += batchedStatement.executeLargeUpdate();
} catch (SQLException var49) {
sqlEx = this.handleExceptionForBatch(batchCounter - 1, numValuesPerBatch, updateCounts, var49);
}
this.getBatchedGeneratedKeys(batchedStatement);
batchedStatement.clearParameters();
batchedParamIndex = 1;
}
//會(huì)循環(huán)執(zhí)行這一步將參數(shù)值拼到一起
batchedParamIndex = this.setOneBatchedParameterSet(batchedStatement, batchedParamIndex, this.batchedArgs.get(batchCounter++));
}
try {
updateCountRunningTotal += batchedStatement.executeLargeUpdate();
}
在數(shù)據(jù)庫(kù)連接串中加入rewriteBatchedStatements=true(感覺這樣才是真正的batch處理)
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/timer?useUnicode=true&rewriteBatchedStatements=true"
debug看executeBatchedInserts方法中上面一段執(zhí)行后PreparedStatement中的sql是這樣的：
com.mysql.jdbc.PreparedStatement@3098eb37: insert into message(id,message,createTime)
values('0','data for test timerTask',SYSDATE())
,('1','data for test timerTask',SYSDATE())
,('2','data for test timerTask',SYSDATE())
,('3','data for test timerTask',SYSDATE())
,('4','data for test timerTask',SYSDATE())
,('5','data for test timerTask',SYSDATE())
... ...
果然這樣確實(shí)批量處理了煌茴，耗時(shí)1405ms/1w條(開啟了rewriteBatchedStatements后1和2時(shí)間差不多都是這么多)。
3日川、2中開啟批量處理后可以看到就是相當(dāng)于拼成了一條sql蔓腐，那直接自己拼就可以省去框架的其他處理操作，是不是會(huì)快點(diǎn)龄句？再試試最原始的一條sql回论，直接拼接成一條sql，代碼是這個(gè)樣子的：
private void initialDatabaseNew() {
Connection connection = null;
Statement statement = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/timer?useUnicode=true&characterEncoding=utf-8","root","123456");
connection.setAutoCommit(false);
statement = connection.createStatement();
long begin = System.currentTimeMillis();
int i =0,j=1;
for (;j<101;j++) { //這里分批是因?yàn)镸ysql有大小限制分歇，sql不能拼太長(zhǎng)
StringBuffer sql = new StringBuffer("insert into message(id,message,createTime) values");
for (; i < 20000*j; i++) {
sql.append("('" + i + "','data for test timerTask',SYSDATE()),");
}
sql.append("('" + i++ + "','data for test timerTask',SYSDATE())");
statement.execute(sql.toString());
}
connection.commit();
long end = System.currentTimeMillis();
System.out.println("---------初始化數(shù)據(jù)完畢傀蓉，耗時(shí)："+(end-begin)+"ms----------");
}
catch (Exception e) {
e.printStackTrace();
}
finally {
try {
statement.close();
connection.close();
} catch (SQLException e) {
}
}
耗時(shí)266ms/1w條，這個(gè)時(shí)間似乎可以接受职抡。但是有沒有更快的呢葬燎？畢竟現(xiàn)在還是停留在調(diào)用API，存儲(chǔ)過(guò)程？還有其他谱净？后續(xù)....
