Question

java mysql批量insert多少条效率最高

Answer 1

conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
pstmt = conn
.prepareStatement("insert into loadtest (id,
data) values (?, ?)");
for (int i = 1; i <= COUNT; i++)
{
pstmt.clearParameters();
pstmt.setInt(1,
i);
pstmt.setString(2, DATA);
pstmt.execute();
}

MyISAM：246.6秒、InnoDB：360.2秒

方法2： 使用事务，不自动commit

Java code

复制代码
代码如下:

conn =
DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
conn.setAutoCommit(false);
pstmt = conn
.prepareStatement("insert into loadtest (id, data) values (?, ?)");
for (int i = 1; i <= COUNT; i++) {
pstmt.clearParameters();
pstmt.setInt(1, i);
pstmt.setString(2, DATA);
pstmt.execute();
if (i %
COMMIT_SIZE == 0) {
conn.commit();
}
}
conn.commit();

InnoDB：31.5秒