Mysql insert speed test

[darydevel]

Понадобилось мне недавно ускорять загрузку данных в mysql из java. Сначала она была сделана самым стандартным способом, через отправку INSERT команд пакетами (batch).
Когда замеры производительности показали, что поставленным критериям такой способ совершенно не удовлетворяет, решено было посмотреть на альтернативы загрузки, предлагаемые mysql. Всего их две:

• Bulk (загрузка скопом) Вставка множества записей одним INSERT выражением.
  
• File (загрузка файлом) Вставка записей из текстового файла.
  

В документации написано, что File upload по скорости превосходит Insert (правда не сказано какой) в 20 раз, но в то же время сразу переписывать код основного приложения под File upload не хотелось, поэтому я решила написать сравнительный тест. Так же меня давно интересовало, как зависит скорость добавления новых значений в индексы в случаях добавления в середину либо конец индекса.
Результат тестирования показал однозначную победу Bulk upload метода. Не знаю как во всех случаях, но в случае который был описан тестом, File upload показал заметное отставание от Bulk. Что касается скорости записи в середину/конец индекса, то во всех случаях кроме того же File upload скорость записи в середину оказалась чуть выше.

Сводная таблица времени загрузки данных (в миллисекундах):

Record Count
Standard  Upload
Bulk Upload
File Upload
Sequential
Periodical
Sequential
Periodical
Sequential
Periodical
10000
1555
938
354
204
1768
1363
100000
9285
8423
2149
1998
4282
4762
1000000
86031
85398
21043
20772
31295
31615
10000000
878673
877662
236743
232451
271003
281121
50000000
4356437
4351976
1209249
1193325
1552083
1520502

Sequential distribution - вносимые значения индексированных полей постоянно возрастают.
Periodical distribution - вносимые значения индексированных полей возрастают до некоторой величины затем сбрасываются до нуля и возрастают снова.

Количество записей отправляемое за один проход (для Standard и Bulk): 5000

Используемая таблица:
CREATE TABLE test(
        id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        field1 VARCHAR(512) NOT NULL,
        field2 BIGINT NOT NULL,
        field3 INT UNSIGNED NOT NULL,
        field4 INT UNSIGNED NOT NULL,
        field5 INT UNSIGNED NOT NULL,
        field6 INT UNSIGNED NOT NULL,
        INDEX index1(field2, field3)
)


SQL выражения и фрагмент исходного кода, отвечающего за загрузку данных в mysql.
Standard Upload Code
        INSERT INTO test(field1, field2, field3, field4, field5, field6) values(?,?,?,?,?,?)

pstmt = mConnection.prepareStatement(buildInsertSql(1)); for (int i = 1; i <= RECORD_COUNT; i++) { pstmt.setString(1, gen.getField1()); pstmt.setLong(2, gen.getField2()); pstmt.setInt(3, gen.getField3()); pstmt.setInt(4, gen.getField4()); pstmt.setInt(5, gen.getField5()); pstmt.setInt(6, gen.getField6()); pstmt.addBatch(); // bulk or records end
if (i % BULK_SIZE == 0 || i == RECORD_COUNT) { pstmt.executeBatch(); } }
Bulk Upload Code
        INSERT INTO test(field1, field2, field3, field4, field5, field6)
        values(?,?,?,?,?,?),
        (?,?,?,?,?,?)
        ...

int bulkSize = 0; for (long i = 0; i < RECORD_COUNT; i++) { int idx = (int) (i % BULK_SIZE); // start of new bulk
if (idx == 0) { bulkSize = (int) Math.min(RECORD_COUNT-i, BULK_SIZE); if (pstmt != null) { pstmt.close(); } pstmt = mConnection.prepareStatement(buildInsertSql(bulkSize)); } pstmt.setString(idx*6 + 1, gen.getField1()); pstmt.setLong(idx*6 + 2, gen.getField2()); pstmt.setInt(idx*6 + 3, gen.getField3()); pstmt.setInt(idx*6 + 4, gen.getField4()); pstmt.setInt(idx*6 + 5, gen.getField5()); pstmt.setInt(idx*6 + 6, gen.getField6()); // end of bulk
if (idx == bulkSize - 1) { pstmt.executeUpdate(); } }

File Upload Code
        LOAD DATA LOCAL INFILE 'data.csv'
        INTO TABLE test
        FIELDS TERMINATED BY ' ' ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        (field1, field2, field3, field4, field5, field6)

com.mysql.jdbc.PreparedStatement pStmt = null; final PipedInputStream is = new PipedInputStream(); Runnable rn = new Runnable() { public void run() { try { PrintWriter wrtr = new PrintWriter(new OutputStreamWriter( new PipedOutputStream(is), "US-ASCII")); for (long i = 0; i < RECORD_COUNT; i++) { wrtr.printf("\"%s\"", gen.getField1()); wrtr.print(" "); wrtr.printf("\"%d\"", gen.getField2()); wrtr.print(" "); wrtr.printf("\"%d\"", gen.getField3()); wrtr.print(" "); wrtr.printf("\"%d\"", gen.getField4()); wrtr.print(" "); wrtr.printf("\"%d\"", gen.getField5()); wrtr.print(" "); wrtr.printf("\"%d\"", gen.getField6()); wrtr.println(); } wrtr.flush(); wrtr.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (IOException e) { throw new RuntimeException(e); } } }; Thread dataThread = new Thread(rn); dataThread.start(); // give data thread enough time to start writing data
try { Thread.sleep(1000); } catch (InterruptedException e) { e.printStackTrace(); } try { pStmt = (com.mysql.jdbc.PreparedStatement) mConnection
.prepareStatement(SQL_INSERT_FILE); pStmt.setLocalInfileInputStream(is); pStmt.executeUpdate(); } catch (SQLException e) {