测试环境:mysql-connector-java-5.1.34.jar ;
jdbc:mysql://192.168.21.103:3306/yzhs?rewriteBatchedStatements=true
测试代码
1、不使用jdbc的addBatch\executeBatch
int size=100000;
private void noBatch(){
JDBCHelper jdbc=JDBCHelper.getInstance();
Connection conn=jdbc.getConnection();
try {
conn.setAutoCommit(false);
PreparedStatement prep=conn.prepareStatement("insert into temp_ban_list() values(?)");
long a=System.currentTimeMillis();
for (int i = 0; i < size; i++) {
prep.setInt(1, i);
prep.execute();
}
conn.commit();
long b=System.currentTimeMillis();
System.out.println("MySql非批量插入10万条记录用时"+ (b-a)+" ms");
} catch (SQLException e) {
e.printStackTrace();
}
}
/*
* 结果
* MySql非批量插入10万条记录用时51657 ms
* MySql非批量插入10万条记录用时49765 ms
*/
2、使用jdbc的addBatch\executeBatch
private void batch(){
JDBCHelper jdbc=JDBCHelper.getInstance();
Connection conn=jdbc.getConnection();
try {
conn.setAutoCommit(false);
PreparedStatement prep=conn.prepareStatement("insert into temp_ban_list() values(?)");
long a=System.currentTimeMillis();
for (int i = 0; i < size; i++) {
prep.setInt(1, i);
prep.addBatch();
}
prep.executeBatch();
conn.commit();
long b=System.currentTimeMillis();
System.out.println("MySql批量插入10万条记录用时"+ (b-a)+" ms");
} catch (SQLException e) {
e.printStackTrace();
}
}
/*
* 结果
* MySql批量插入10万条记录用时1040 ms
* MySql批量插入10万条记录用时1097 ms
*
* 去掉参数,结果
* MySql批量插入10万条记录用时91629 ms
*/
结论:使用jdbc的addBatch\executeBatch时url要加参数rewriteBatchedStatements=true