JDBC 只是提供标准接口的数据库访问的 Java SE 标准,因此您并不真正受限于特定的 JDBC 实现。 MySQL Java 连接器 (Connector/J) 是仅用于 MySQL 数据库的 JDBC 接口的实现。出于经验,我参与了一个使用MySQL使用大量数据的项目,对于可以生成的数据,我们最喜欢MyISAM:它允许在丢失事务时实现更高的性能,但总的来说,MyISAM更快,但InnoDB更可靠。
大约一年前,我也想知道 INSERT 语句的性能,并在我的代码架中找到了以下旧的测试代码(抱歉,它有点复杂,有点超出了您的问题范围)。下面的代码包含 4 种插入测试数据的方法的示例:
-
single
INSERT
s;
-
batched
INSERT
s;
-
手动批量
INSERT
(永远不要使用它 - 它很危险);
- 最后准备好的散装
INSERT
).
It uses TestNG http://testng.org/doc/index.html作为跑步者,并使用一些自定义代码遗留,例如:
- the
runWithConnection()
方法 - 确保在执行回调后连接被关闭或放回连接池(但下面的代码使用了不可靠的语句关闭策略 - 即使没有try
/finally
减少代码);
-
IUnsafeIn<T, E extends Throwable>
- 接受单个参数但可能抛出 E 类型异常的方法的自定义回调接口,例如:void handle(T argument) throws E;
.
package test;
import test.IUnsafeIn;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import static java.lang.String.format;
import static java.lang.String.valueOf;
import static java.lang.System.currentTimeMillis;
import core.SqlBaseTest;
import org.testng.annotations.AfterSuite;
import org.testng.annotations.BeforeSuite;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
public final class InsertVsBatchInsertTest extends SqlBaseTest {
private static final int ITERATION_COUNT = 3000;
private static final String CREATE_TABLE_QUERY = "CREATE TABLE IF NOT EXISTS ttt1 (c1 INTEGER, c2 FLOAT, c3 VARCHAR(5)) ENGINE = InnoDB";
private static final String DROP_TABLE_QUERY = "DROP TABLE ttt1";
private static final String CLEAR_TABLE_QUERY = "DELETE FROM ttt1";
private static void withinTimer(String name, Runnable runnable) {
final long start = currentTimeMillis();
runnable.run();
logStdOutF("%20s: %d ms", name, currentTimeMillis() - start);
}
@BeforeSuite
public void createTable() {
runWithConnection(new IUnsafeIn<Connection, SQLException>() {
@Override
public void handle(Connection connection) throws SQLException {
final PreparedStatement statement = connection.prepareStatement(CREATE_TABLE_QUERY);
statement.execute();
statement.close();
}
});
}
@AfterSuite
public void dropTable() {
runWithConnection(new IUnsafeIn<Connection, SQLException>() {
@Override
public void handle(Connection connection) throws SQLException {
final PreparedStatement statement = connection.prepareStatement(DROP_TABLE_QUERY);
statement.execute();
statement.close();
}
});
}
@BeforeTest
public void clearTestTable() {
runWithConnection(new IUnsafeIn<Connection, SQLException>() {
@Override
public void handle(Connection connection) throws SQLException {
final PreparedStatement statement = connection.prepareStatement(CLEAR_TABLE_QUERY);
statement.execute();
statement.close();
}
});
}
@Test
public void run1SingleInserts() {
withinTimer("Single inserts", new Runnable() {
@Override
public void run() {
runWithConnection(new IUnsafeIn<Connection, SQLException>() {
@Override
public void handle(Connection connection) throws SQLException {
for ( int i = 0; i < ITERATION_COUNT; i++ ) {
final PreparedStatement statement = connection.prepareStatement("INSERT INTO ttt1 (c1, c2, c3) VALUES (?, ?, ?)");
statement.setInt(1, i);
statement.setFloat(2, i);
statement.setString(3, valueOf(i));
statement.execute();
statement.close();
}
}
});
}
});
}
@Test
public void run2BatchInsert() {
withinTimer("Batch insert", new Runnable() {
@Override
public void run() {
runWithConnection(new IUnsafeIn<Connection, SQLException>() {
@Override
public void handle(Connection connection) throws SQLException {
final PreparedStatement statement = connection.prepareStatement("INSERT INTO ttt1 (c1, c2, c3) VALUES (?, ?, ?)");
for ( int i = 0; i < ITERATION_COUNT; i++ ) {
statement.setInt(1, i);
statement.setFloat(2, i);
statement.setString(3, valueOf(i));
statement.addBatch();
}
statement.executeBatch();
statement.close();
}
});
}
});
}
@Test
public void run3DirtyBulkInsert() {
withinTimer("Dirty bulk insert", new Runnable() {
@Override
public void run() {
runWithConnection(new IUnsafeIn<Connection, SQLException>() {
@Override
public void handle(Connection connection) throws SQLException {
final StringBuilder builder = new StringBuilder("INSERT INTO ttt1 (c1, c2, c3) VALUES ");
for ( int i = 0; i < ITERATION_COUNT; i++ ) {
if ( i != 0 ) {
builder.append(",");
}
builder.append(format("(%s, %s, '%s')", i, i, i));
}
final String query = builder.toString();
final PreparedStatement statement = connection.prepareStatement(query);
statement.execute();
statement.close();
}
});
}
});
}
@Test
public void run4SafeBulkInsert() {
withinTimer("Safe bulk insert", new Runnable() {
@Override
public void run() {
runWithConnection(new IUnsafeIn<Connection, SQLException>() {
private String getInsertPlaceholders(int placeholderCount) {
final StringBuilder builder = new StringBuilder("(");
for ( int i = 0; i < placeholderCount; i++ ) {
if ( i != 0 ) {
builder.append(",");
}
builder.append("?");
}
return builder.append(")").toString();
}
@SuppressWarnings("AssignmentToForLoopParameter")
@Override
public void handle(Connection connection) throws SQLException {
final int columnCount = 3;
final StringBuilder builder = new StringBuilder("INSERT INTO ttt1 (c1, c2, c3) VALUES ");
final String placeholders = getInsertPlaceholders(columnCount);
for ( int i = 0; i < ITERATION_COUNT; i++ ) {
if ( i != 0 ) {
builder.append(",");
}
builder.append(placeholders);
}
final int maxParameterIndex = ITERATION_COUNT * columnCount;
final String query = builder.toString();
final PreparedStatement statement = connection.prepareStatement(query);
int valueIndex = 0;
for ( int parameterIndex = 1; parameterIndex <= maxParameterIndex; valueIndex++ ) {
statement.setObject(parameterIndex++, valueIndex);
statement.setObject(parameterIndex++, valueIndex);
statement.setObject(parameterIndex++, valueIndex);
}
statement.execute();
statement.close();
}
});
}
});
}
}
看看用@Test注解注释的方法:它们实际上执行的是INSERT
声明。另请查看CREATE_TABLE_QUERY
常量:在源代码中,它使用 InnoDB 在安装了 MySQL 5.5 (MySQL Connector/J 5.1.12) 的机器上产生以下结果:
InnoDB
Single inserts: 74148 ms
Batch insert: 84370 ms
Dirty bulk insert: 178 ms
Safe bulk insert: 118 ms
如果您更改CREATE_TABLE_QUERY
InnoDB 到 MyISAM,您会看到性能显着提升:
MyISAM
Single inserts: 604 ms
Batch insert: 447 ms
Dirty bulk insert: 63 ms
Safe bulk insert: 26 ms
希望这可以帮助。
UPD:
对于第四种方式,您必须正确自定义max_allowed_packet
in mysql.ini
(the [mysqld]
部分)足够大以支持真正大的数据包。