我尝试通过批量插入来加快向mysql数据库中插入数据的速度。然而,当我运行以下代码时,两种方法得到相同的结果(时间为2分钟或173秒)。欢迎对此发表评论。
MySQLConn mysqlconn = new MySQLConn(db,username,pass);
Connection conn =mysqlconn.getConnection();
PreparedStatement ps = null;
String query = "insert into table (column) values (?)";
System.out.println("bulk insert started with "+10000 );
long startTime = System.currentTimeMillis();
try {
ps = conn.prepareStatement(query);
long start = System.currentTimeMillis();
for(int i =0; i<10000;i++){
ps.setString(1, "Name"+i);
ps.addBatch();
if(i%1000 == 0) ps.executeBatch();
}
ps.executeBatch();
System.out.println("Time Taken="+(System.currentTimeMillis()-start));
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("End inserting data...");
long stopTime = System.currentTimeMillis();
long elapsedTime = (stopTime - startTime);
long days = TimeUnit.MILLISECONDS.toDays(elapsedTime);
long hours = TimeUnit.MILLISECONDS.toHours(elapsedTime);
long minutes = TimeUnit.MILLISECONDS.toMinutes(elapsedTime);
long seconds = TimeUnit.MILLISECONDS.toSeconds(elapsedTime);
System.out.println("Time elapsed: "+minutes+"min, or "+seconds+"sec");
System.out.println("normal insert started with "+10000 );
long startTimeNormal = System.currentTimeMillis();
for(int i=0;i<10000;i++){
String ins="Data"+i;
Insert insert = new Insert(conn,ins,"db.table", "column" );
}
System.out.println("End inserting data...");
long stopTimeNormal = System.currentTimeMillis();
long elapsedTimeNormal = (stopTimeNormal - startTimeNormal);
long daysN = TimeUnit.MILLISECONDS.toDays(elapsedTime);
long hoursN = TimeUnit.MILLISECONDS.toHours(elapsedTime);
long minutesN = TimeUnit.MILLISECONDS.toMinutes(elapsedTime);
long secondsN = TimeUnit.MILLISECONDS.toSeconds(elapsedTime);
System.out.println("Time elapsed: "+minutesN+"min, or "+secondsN+"sec");