小弟在使用MYSQL数据库的时候,遇到一个奇怪的问题:为什么我使用批量插入和逐条插入的性能是差不多的?package com.ray.test.db;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;public class App { public static void main(String[] argv) {
if (!testClass())
return; try {
String url = "jdbc:mysql://localhost:3306/test?user=root&password=220315";
Connection conn = DriverManager.getConnection(url);
if (!conn.isClosed())
System.out.println("資料庫連線成功"); // java.sql.PreparedStatement caller =
// conn.prepareStatement("insert into test values (?)");
// System.out.println("start excute : " +
// System.currentTimeMillis());
// for (int i = 0; i < 10000; i++) {
// caller.setInt(1, i);
// caller.executeUpdate();
// }
// System.out.println("finish excute : " +
// System.currentTimeMillis()); java.sql.PreparedStatement caller = conn.prepareStatement("insert into test values (?)");
System.out.println("start excute : " + System.currentTimeMillis());
for (int i = 0; i < 10000; i++) {
caller.setInt(1, i);
caller.addBatch();
}
caller.executeBatch();
System.out.println("finish excute : " + System.currentTimeMillis());
conn.close();
} catch (SQLException e) {
System.out.println("Exception occur, e.getMessage()=" + e.getMessage());
} } private static boolean testClass() {
try {
Class.forName("com.mysql.jdbc.Driver");
return true;
} catch (ClassNotFoundException e) {
System.out.println("找不到驅動程式類別, e.getMessage()=" + e.getMessage());
}
return false;
}} 被注释掉的代码是逐行插入的,用时1090毫秒,而未注释的代码是使用了批量插入的,用时1073毫秒秒。怎么只差了17毫秒而已,没有体现出批量插入的优势啊!?
import java.sql.DriverManager;
import java.sql.SQLException;public class App { public static void main(String[] argv) {
if (!testClass())
return; try {
String url = "jdbc:mysql://localhost:3306/test?user=root&password=220315";
Connection conn = DriverManager.getConnection(url);
if (!conn.isClosed())
System.out.println("資料庫連線成功"); // java.sql.PreparedStatement caller =
// conn.prepareStatement("insert into test values (?)");
// System.out.println("start excute : " +
// System.currentTimeMillis());
// for (int i = 0; i < 10000; i++) {
// caller.setInt(1, i);
// caller.executeUpdate();
// }
// System.out.println("finish excute : " +
// System.currentTimeMillis()); java.sql.PreparedStatement caller = conn.prepareStatement("insert into test values (?)");
System.out.println("start excute : " + System.currentTimeMillis());
for (int i = 0; i < 10000; i++) {
caller.setInt(1, i);
caller.addBatch();
}
caller.executeBatch();
System.out.println("finish excute : " + System.currentTimeMillis());
conn.close();
} catch (SQLException e) {
System.out.println("Exception occur, e.getMessage()=" + e.getMessage());
} } private static boolean testClass() {
try {
Class.forName("com.mysql.jdbc.Driver");
return true;
} catch (ClassNotFoundException e) {
System.out.println("找不到驅動程式類別, e.getMessage()=" + e.getMessage());
}
return false;
}} 被注释掉的代码是逐行插入的,用时1090毫秒,而未注释的代码是使用了批量插入的,用时1073毫秒秒。怎么只差了17毫秒而已,没有体现出批量插入的优势啊!?
注意事务的处理能说具体点吗?
System.out.println("start excute: \t" + System.currentTimeMillis());
StringBuilder builder = new StringBuilder("INSERT INTO test VALUES ");
for (int i = 0; i < 10000; i++) {
if (i == 9999)
builder.append("(9999)");
else
builder.append("( " + i + " ), ");
}
Statement sm = conn.createStatement();
sm.execute(builder.toString());
System.out.println("finish excute: \t" + System.currentTimeMillis());
用时36毫秒,目前最快的方式了。
性能还不错,能够达到1W行每秒的插入速度。结贴~