java - 使用许多 VALUES (),(),(); 优化 MySQL INSERT；

Question

我正在努力提高我的 Java 应用程序的性能，此时我专注于一个必须将大量数据插入 mysql 的端点。

我将纯 JDBC 与 MariaDB Java 客户端驱动程序一起使用:

try (PreparedStatement stmt = connection.prepareStatement(
            "INSERT INTO data (" +
                    "fId, valueDate, value, modifiedDate" +
                    ") VALUES (?,?,?,?)") {
    for (DataPoint dp : datapoints) {
        stmt.setLong(1, fId);
        stmt.setDate(2, new java.sql.Date(dp.getDate().getTime()));
        stmt.setDouble(3, dp.getValue());
        stmt.setDate(4, new java.sql.Date(modifiedDate.getTime()));
        stmt.addBatch();
    }        
    int[] results = statement.executeBatch();
}

从转储文件填充新数据库，我知道 max_allowed_packet很重要，我已将其设置为 536,870,912 字节。

在https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html它指出:

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements. If you are adding data to a nonempty table, you can tune the bulk_insert_buffer_size variable to make data insertion even faster. See Section 5.1.7, “Server System Variables”.

在我的数据库中，这设置为 8MB

我还阅读了关于 key_buffer_size 的内容(当前设置为 16MB)。

我担心最后两个可能还不够。我可以对这个算法的 JSON 输入做一些粗略的计算，因为它看起来像这样:

[{"actualizationDate":null,"data":[{"date":"1999-12-31","value":0},
{"date":"2000-01-07","value":0},{"date":"2000-01-14","value":3144},
{"date":"2000-01-21","value":358},{"date":"2000-01-28","value":1049},
{"date":"2000-02-04","value":-231},{"date":"2000-02-11","value":-2367},
{"date":"2000-02-18","value":-2651},{"date":"2000-02-25","value":-
393},{"date":"2000-03-03","value":1725},{"date":"2000-03-10","value":-
896},{"date":"2000-03-17","value":2210},{"date":"2000-03-24","value":1782},

看起来为 bulk_insert_buffer_size 配置的 8MB 很容易被超过，如果不是 key_buffer_size 也是如此。

但是 MySQL 文档只提到了 MyISAM 引擎表，而我目前正在使用 InnoDB 表。

我可以设置一些测试，但最好知道这将如何破坏或降级(如果有的话)。

[编辑] 我有 --rewriteBatchedStatements=true。事实上，这是我的连接字符串:

jdbc:p6spy:mysql://myhost.com:3306/mydb\
    ?verifyServerCertificate=true\
    &useSSL=true\
    &requireSSL=true\
    &cachePrepStmts=true\
    &cacheResultSetMetadata=true\
    &cacheServerConfiguration=true\
    &elideSetAutoCommits=true\
    &maintainTimeStats=false\
    &prepStmtCacheSize=250\
    &prepStmtCacheSqlLimit=2048\
    &rewriteBatchedStatements=true\
    &useLocalSessionState=true\
    &useLocalTransactionState=true\
    &useServerPrepStmts=true

(来自 https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration)

Answer 1

另一种方法是不时执行批处理。这使您可以减少批处理的大小，让您专注于更重要的问题。

int batchSize = 0;

for (DataPoint dp : datapoints) {
    stmt.setLong(1, fId);
    stmt.setDate(2, new java.sql.Date(dp.getDate().getTime()));
    stmt.setDouble(3, dp.getValue());
    stmt.setDate(4, new java.sql.Date(modifiedDate.getTime()));
    stmt.addBatch();

    //When limit reach, execute and reset the counter
    if(batchSize++ >= BATCH_LIMIT){
        statement.executeBatch();

        batchSize = 0;
    }
}        

// To execute the remaining items
if(batchSize > 0){
    statement.executeBatch();
}

我通常使用基于 DAO 实现的常量或参数来提高动态性，但一批 10_000 行是一个好的开始。

private static final int BATCH_LIMIT = 10_000;

请注意，这不是执行后清除批处理所必需的。即使这未在 Statement.executeBatch 中指定文档，这是在 JDBC specification 4.3

14 Batch Updates
14.1 Description of Batch Updates
14.1.2 Successful Execution

Calling the method executeBatch closes the calling Statement object’s current result set if one is open.
The statement’s batch is reset to empty once executeBatch returns.

结果的管理有点复杂，但如果需要，您仍然可以连接结果。由于不再需要 ResultSet，因此可以随时对其进行分析。