Java의 MySQL Insert 문 성능: Batch mode 준비문 vs 여러 값을 가진 단일 insert
저는 설계를 하고 있습니다.MySQL다양한 InnoDB 테이블에 걸쳐 초당 약 600개의 행 삽입을 처리해야 하는 데이터베이스.현재 구현에서는 일괄 처리되지 않은 준비된 문을 사용합니다.,를 쓰는 것,은.MySQL데이터베이스 병목 현상과 대기열 크기가 시간이 지남에 따라 증가합니다.
구현은 자바로 작성되어 있는데, 지금 당장은 버전을 모릅니다.사용합니다.MySQL'자바 커넥터.제가 좀 알아봐야 할 것 같아요.JDBC내일입니다. 이두의 다른 저는 이것들이 서로 다른 두 개의 커넥터 패키지라고 가정합니다.
이 문제에 대해 다음 스레드를 읽었습니다.
그리고 mysql 사이트에서:
제 질문은 다음과 같습니다.
INSERT 를를 것에 대해 ?
INSERT여러 개의 값을 가지는 문장.입니까의 입니까?
MySQLJava 커넥터 vs.JDBC하나를 사용해야 하나요 하나를 사용해야 합니까 아니면 다른 하나를 사용해야 하나요?테이블은 아카이브 목적으로 작성되며, 최대 90%에서 최대 10%까지 읽기가 가능합니다(심지어 더 적을 수도 있음).저는 InnoDB를 사용하고 있습니다.이것이 My ISAM에 대한 올바른 선택입니까?
당신의 도움에 미리 감사드립니다.
JDBC는 단지 표준 인터페이스를 제공하는 데이터베이스 액세스의 Java SE 표준이므로 특정 JDBC 구현에 구속되지 않습니다.MySQL Java 커넥터(Connector/J)는 MySQL 데이터베이스만을 위한 JDBC 인터페이스 구현입니다.저는 경험상 MySQL을 사용하여 막대한 양의 데이터를 사용하는 프로젝트에 참여하고 있으며, 생성할 수 있는 데이터는 MyISAM을 주로 선호합니다. 트랜잭션을 손실하는 성능을 훨씬 높일 수 있지만 일반적으로 MyISAM이 더 빠르지만 InnoDB가 더 안정적입니다.
INSERT 문의 성능도 1년 전쯤에 궁금했는데, 제 코드 선반에서 다음과 같은 오래된 테스트 코드를 발견했습니다(죄송합니다, 좀 복잡하고 질문 범위를 벗어났습니다).아래 코드에는 테스트 데이터를 삽입하는 4가지 방법의 예가 포함되어 있습니다.
-
INSERTs;s; -
INSERTs;s; -
INSERT하지 마십시오 음 -합니다); - 그리고 마침내 준비된 벌크.
INSERT).
TestNG를 런너로 사용하며 다음과 같은 사용자 정의 코드 레거시를 사용합니다.
runWithConnection()-된 후 , 는 method가 없어도 할 수 없는 -다합니다)).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된 내 constant:에서 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_QUERYInnoDB to MyISAM을 사용하면 성능이 크게 향상됩니다.
MyISAM
Single inserts: 604 ms
Batch insert: 447 ms
Dirty bulk insert: 63 ms
Safe bulk insert: 26 ms
도움이 되길 바랍니다.
업데이트:
네번째 방법을 위해서 당신은 적절하게 커스터마이징 해야 합니다.max_allowed_packet인에mysql.ini)[mysqld]section)은 매우 큰 패킷을 지원할 수 있을 정도로 큽니다.
이 스레드가 꽤 오래된 것은 알지만, mysql을 사용할 때 jdbc url에 "rewriteBatchedStatements=true"를 추가하면 배치된 문을 사용할 때 성능이 크게 향상될 수 있다는 점을 언급하고 싶습니다.
영향을 받는 테이블에 트리거가 있습니까?그렇지 않다면 초당 600개의 삽입물이 많아 보이지 않습니다.
JDBC의 배치 삽입 기능은 동일한 트랜잭션에서 동일한 문을 여러 번 실행하는 반면 다중 값 SQL은 단일 문의 모든 값을 압축합니다.다중 값 문의 경우 삽입 SQL을 동적으로 구성해야 하며 이는 더 많은 코드, 더 많은 메모리, SQL 주입 보호 메커니즘 등의 측면에서 오버헤드가 될 수 있습니다.먼저 일반 배치 기능을 사용해 보십시오. 워크로드의 경우에는 문제가 없을 것입니다.
데이터를 일괄적으로 받지 못할 경우 삽입하기 전에 일괄적으로 배치하는 것을 고려합니다.별도의 스레드에서 대기열을 사용하여 생산자-소비자 간 합의를 구현합니다.이 경우 일정 시간이 경과하거나 대기열 크기가 임계값을 넘을 때까지 삽입을 보류합니다.
성공적으로 삽입된 것에 대해 생산자에게 알려주기를 원하는 경우에는 배관 작업이 더 필요합니다.
때때로 실 위에서 차단하는 것이 더 간단하고 실용적일 수 있습니다.
if(System.currentTimeMills()-lastInsertTime>TIME_THRESHOLD || queue.size()>SIZE_THRESHOLD) {
lastInsertTime=System.currentTimeMills();
// Insert logic
} else {
// Do nothing OR sleep for some time OR retry after some time.
}
Jordan L은 나의 몇가지 테스트 후에 가장 좋은 팁을 주었습니다.Yubomyr가 InnoDB 비 dirty 배치 삽입에 대해 부여한 실행 시간이 잘못된 것은 JDBC 연결 문자열에 "rewriteBatchedStatements=true"를 사용하지 않았을 가능성이 높기 때문입니다.그것이 없으면 배치는 가치가 없습니다.제가 직접 테스트한 결과, 준비된 문을 사용하는 더러운 일괄 삽입은 준비된 문을 사용하는 더러운 방식보다 훨씬 빨랐습니다.
언급URL : https://stackoverflow.com/questions/11389449/performance-of-mysql-insert-statements-in-java-batch-mode-prepared-statements-v
'programing' 카테고리의 다른 글
| calloc()는 SIZE_MAX보다 많이 할당할 수 있습니까? (0) | 2023.10.12 |
|---|---|
| CSS를 사용하여 디브 3개를 나란히 띄우는 방법? (0) | 2023.10.12 |
| 다음 형제/X를 선택하는 방법XPath를 사용한 ML 태그 (0) | 2023.10.12 |
| Amazon RDS MySQL 인스턴스의 성능이 매우 느림 (0) | 2023.10.12 |
| 스위프트의 블록(애니메이션 위드 듀레이션:애니메이션:완료:) (0) | 2023.10.12 |