Java 中 MySQL Insert 语句的性能:批处理模式准备好的语句与具有多个值的单个插入 [英] Performance of MySQL Insert statements in Java: Batch mode prepared statements vs single insert with multiple values
问题描述
我正在设计一个 MySQL 数据库，该数据库需要在各种 InnoDB 表中每秒处理大约 600 行插入.我当前的实现使用非批处理的准备语句.但是，写入 MySQL 数据库会遇到瓶颈，而且我的队列大小会随着时间的推移而增加.
I am designing a MySQL database which needs to handle about 600 row inserts per second across various InnoDB tables. My current implementation uses non-batched prepared statements. However, writing to the MySQL database bottlenecks and my queue size increases over time.
实现是用Java编写的，我不知道手头的版本.它使用 MySQL 的 Java 连接器.我需要考虑明天切换到 JDBC.我假设这是两个不同的连接器包.
The implementation is written in Java, I don't know the version off hand. It uses MySQL's Java connector. I need to look into switching to JDBC tomorrow. I am assuming these are two different connector packages.
我已阅读有关此问题的以下主题:
I have read the following threads on the issue:
来自 mysql 站点:
and from the mysql site:
我的问题是:
有没有人对在批处理模式下使用带有准备好的语句的 INSERT 与使用带有多个值的单个
INSERT语句的性能差异有建议或经验.
Does anyone have advice or experience on performance differences using INSERTs with prepared statements in batch mode vs. using a single
INSERTstatement with multiple VALUEs.
MySQL Java 连接器与 JDBC 之间的性能差异是什么.我应该使用一种还是另一种?
What are the performance differences between the MySQL Java connector vs. JDBC. Should I be using one or the other?
这些表用于存档目的，将看到 ~90% 的写入到 ~10% 的读取(甚至可能更少).我正在使用 InnoDB.这是对 MyISAM 的正确选择吗?
The tables are for archive purposes, and will see ~90% write to ~10% read (maybe even less). I am using InnoDB. Is this the right choice over MyISAM?
预先感谢您的帮助.
推荐答案
JDBC 只是一个 Java SE 数据库访问标准，提供了标准接口，因此您不会真正绑定到特定的 JDBC 实现.MySQL Java 连接器 (Connector/J) 是仅用于 MySQL 数据库的 JDBC 接口的实现.出于经验，我参与了一个使用 MySQL 使用大量数据的项目，对于可以生成的数据，我们最喜欢 MyISAM:它可以实现更高的性能丢失事务，但一般来说，MyISAM 更快，但 InnoDB 更可靠.
JDBC is simply a Java SE standard of database access offering the standard interfaces so you're not really bound to a specific JDBC implementation. MySQL Java connector (Connector/J) is an implementation of the JDBC interfaces for MySQL databases only. Out of experience, I'm involved to a project that uses huge amount of data using MySQL, and we mostly prefer MyISAM for data that can be generated: it allows to achieve much higher performance losing transactions, but generally speaking, MyISAM is faster, but InnoDB is more reliable.
大约一年前，我也想知道 INSERT 语句的性能，并在我的代码架中发现了以下旧测试代码(抱歉，它有点复杂，有点超出您的问题范围).下面的代码包含了 4 种插入测试数据方式的示例:
I wondered for the performance of the INSERT statements too about a year ago, and found the following old testing code in my code shelf (sorry, it's a bit complex and a bit out of your question scope). The code below contains examples of 4 ways of inserting the test data:
- 单个
INSERTs; - 批处理
INSERTs; - 手动批量
INSERT(永远不要使用它 - 这很危险)； - 最后准备批量
INSERT).
- single
INSERTs; - batched
INSERTs; - manual bulk
INSERT(never use it - it's dangerous); - and finally prepared bulk
INSERT).
它使用 TestNG 作为运行器，并使用一些自定义代码遗留，例如:
It uses TestNG as the runner, and uses some custom code legacy like:
runWithConnection()方法 - 确保在执行回调后关闭连接或放回连接池(但下面的代码使用了不可靠的语句关闭策略 - 即使没有try/finally减少代码);IUnsafeIn- 一个自定义回调接口，用于接受单个参数但可能抛出 E 类型异常的方法，例如:void handle(T argument)抛出 E;.
- the
runWithConnection()method - ensures that the connection is closed or put back to the connection pool after the callback is executed (but the code below uses not reliable strategy of the statement closing - even withouttry/finallyto reduce the code); IUnsafeIn<T, E extends Throwable>- a custom callback interface for the methods accepting a single parameter but potentially throwing an exception of type E, like: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) 的机器上产生以下结果:
Take a look at the methods annotated with the @Test annotation: they actually execute the INSERT statements. Also please take a look at the CREATE_TABLE_QUERY constant: in the source code it uses InnoDB producing the following results at my machine with MySQL 5.5 installed (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，您会看到显着的性能提升:
If you change the CREATE_TABLE_QUERY InnoDB to MyISAM, you'd see significant performance increase:
MyISAM
Single inserts: 604 ms
Batch insert: 447 ms
Dirty bulk insert: 63 ms
Safe bulk insert: 26 ms
希望这会有所帮助.
更新:
对于第 4 种方式，您必须正确自定义 mysql.ini([mysqld] 部分)中的 max_allowed_packet 以使其足够大支持非常大的数据包.
For the 4th way you must properly customize the max_allowed_packet in mysql.ini (the [mysqld] section) to be large enough to support really big packets.
这篇关于Java 中 MySQL Insert 语句的性能:批处理模式准备好的语句与具有多个值的单个插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
