两个优化点
mysql 默认关闭批处理
开启方法:url 后添加?rewriteBatchedStatements=true
url=jdbc:mysql://localhost:3306/girls?rewriteBatchedStatements=true
每次执行都要将java中的sql传入数据库中,可以先存一批,然后一次性传入数据库
// 向batch中添加sql语句
preparedStatement.addBatch();
// 执行batch
preparedStatement.executeBatch();
// 清空batch
preparedStatement.clearBatch();
数据库每收到一条语句,就会自动提交,可以关闭自动提交,等到语句到一定数量,一次性提交
// 关闭自动提交
connection.setAutoCommit(false);
// 手动提交
connection.commit();
public static void main(String[] args) throws Exception{
String sql = "insert into test value(null,?)";
Connection connection = JDBCUtil.getConnection();
PreparedStatement preparedStatement = Objects.requireNonNull(connection).prepareStatement(sql);
long start = System.currentTimeMillis();
// 关闭自动提交
connection.setAutoCommit(false);
for (int i = 0; i < 1000000; i++) {
preparedStatement.setObject(1,"num_"+i);
preparedStatement.addBatch();
if(i%1000==0){
// 执行batch
preparedStatement.executeBatch();
// 清空batch
preparedStatement.clearBatch();
}
}
// 手动提交
connection.commit();
long end = System.currentTimeMillis();
System.out.println(end-start);
}