JDBC只是数据库访问的Java SE标准，提供了标准接口，因此您实际上并不局限于特定的JDBC实现。MySQL Java连接器（Connector /
J）仅是MySQL数据库的JDBC接口的实现。根据经验，我参与了一个使用MySQL使用大量数据的项目，对于可生成的数据，我们最喜欢使用MyISAM：它可以实现更高的性能损失交易，但总的来说，MyISAM更快，但是InnoDB更可靠。
我也想知道大约一年前INSERT语句的性能，并且在我的代码架中找到了以下旧测试代码（对不起，它有点复杂，而且超出了您的问题范围）。以下代码包含4种插入测试数据的方式的示例：
- 单
INSERT
s; - 分批
INSERT
的 - 手动散装
INSERT
（切勿使用-危险）； - 最后 准备好批量
INSERT
）。
它使用TestNG作为运行程序，并使用一些自定义代码，例如：
- 该
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);}@BeforeSuitepublic 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(); } });}@AfterSuitepublic 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(); } });}@BeforeTestpublic 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(); } });}@Testpublic 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(); } } }); } });}@Testpublic 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(); } }); } });}@Testpublic 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(); } }); } });}@Testpublic 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）的计算机上产生以下结果：
InnoDBSingle inserts: 74148 msBatch insert: 84370 msDirty bulk insert: 178 msSafe bulk insert: 118 ms
如果将
CREATE_TABLE_QUERYInnoDB 更改为MyISAM，则会看到显着的性能提升：
MyISAMSingle inserts: 604 msBatch insert: 447 msDirty bulk insert: 63 msSafe bulk insert: 26 ms
希望这可以帮助。
UPD：
对于第四种方法，您必须适当地自定义
max_allowed_packetin
mysql.ini（本
[mysqld]节），使其足够大以支持真正的大数据包。
