今天在做某項目的POC測試，甲方提供了一個三十萬記錄的TXT數據文件，需要把該文件的記錄插入到數據庫中，由于項目部的同事在搭建測試環境中用的是Mysql數據庫，在把數據導入到數據庫中用的是JDBC的批處理。代碼如下
private void batchParseGroup()
{
Connection con= null;
PreparedStatement ps =null;
try {
con= DbConnectionManager.getConnection();
con.setAutoCommit(false);
String sql = " insert into jivegroup(uri,groupname,pgroupid,description,creationdate,modificationdate,priority,selfpriority) values(?,?,?,?,?,?,?,?)";
ps= con.prepareStatement(sql);
for(int i=0;i<groupList.size();i++){
Group group = groupList.get(i);
ps.setString(1, group.getUri());
ps.setString(2, group.getName());
ps.setString(3, group.getPgroupId());
ps.setString(4, group.getName());
ps.setString(5, ""+System.currentTimeMillis());
ps.setString(6, ""+System.currentTimeMillis());
ps.setInt(7, group.getPriority());
ps.setInt(8, group.getPriority());
ps.addBatch();
if(i%100==0){
ps.executeBatch();
}
}
con.commit();
ps.executeBatch();
} catch (SQLException e) {
e.printStackTrace();
}finally{
DbConnectionManager.closeConnection(ps, con);
}
}在測試時，發現三十萬的數據居然需要十分鐘左右的時間。首先想到的就是Mysql的相關配置是不是有問題，反復修改了Mysql的相應配置參數，收效甚微。
在Mysql的官網上查到如下：
http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html
關于rewriteBatchedStatements參數，Mysql官方的說明：
Should the driver use multiqueries (irregardless of the setting of "allowMultiQueries") as well as rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() is called? Notice that this has the potential for SQL injection if using plain java.sql.Statements and your code doesn't sanitize input correctly. Notice that for prepared statements, server-side prepared statements can not currently take advantage of this rewrite option, and that if you don't specify stream lengths when using PreparedStatement.set*Stream(), the driver won't be able to determine the optimum number of parameters per batch and you might receive an error from the driver that the resultant packet is too large. Statement.getGeneratedKeys() for these rewritten statements only works when the entire batch includes INSERT statements.
解決辦法：
下載最新的JDBC的驅動程序。
MYSQL URL的配置參數如下：
jdbc:mysql://54.200.190.80:3306/ccb_ucstar?rewriteBatchedStatements=true
經過測試。三十多萬的數據。70秒內搞定！