JDBC批量插入性能

Question

我需要在mysql数据库中插入数亿条记录。我一次批量插入一百万。请看我下面的代码。它看起来很慢。有什么方法可以优化它吗？

try {
        // Disable auto-commit
        connection.setAutoCommit(false);

        // Create a prepared statement
        String sql = "INSERT INTO mytable (xxx), VALUES(?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);

        Object[] vals=set.toArray();
        for (int i=0; i<vals.length; i++) {
            pstmt.setString(1, vals[i].toString());
            pstmt.addBatch();
        }

        // Execute the batch
        int [] updateCounts = pstmt.executeBatch();
        System.out.append("inserted "+updateCounts.length);

Answer 1

我在mysql上遇到了类似的性能问题，通过在连接url中设置useServerPrepStmts和rewriteBatchedStatements属性解决了这个问题。

Connection c = DriverManager.getConnection("jdbc:mysql://host:3306/db?useServerPrepStmts=false&rewriteBatchedStatements=true", "username", "password");

Answer 2

我想扩展一下Bertil的答案，因为我一直在试验连接URL参数。

rewriteBatchedStatements=true是一个重要的参数。默认情况下，useServerPrepStmts已经为false，即使将其更改为true，在批插入性能方面也没有太大区别。

现在，我认为是时候写rewriteBatchedStatements=true是如何显著提高性能的了。它是通过rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() (Source)实现的。这意味着，不是每次调用executeBatch()时都向mysql服务器发送以下n INSERT语句：

INSERT INTO X VALUES (A1,B1,C1)
INSERT INTO X VALUES (A2,B2,C2)
...
INSERT INTO X VALUES (An,Bn,Cn)

它将发送一条INSERT语句：

INSERT INTO X VALUES (A1,B1,C1),(A2,B2,C2),...,(An,Bn,Cn)

您可以通过切换mysql日志(通过SET global general_log = 1)来观察它，它会将每个发送到mysql服务器的语句记录到一个文件中。

Answer 3

您可以使用一条insert语句插入多行，一次执行几千条语句可以极大地加快速度，也就是说，您可以使用INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(1,2,3),(1,2,3); (可能是JDBC .addBatch()现在执行类似的优化，而不是做3次INSERT INTO tbl_name (a,b,c) VALUES(1,2,3);形式的插入)-尽管mysql addBatch过去是完全未优化的，并且只是发出单独的查询-我不知道最近的驱动程序是否仍然是这样)

如果你真的需要速度，用LOAD DATA INFILE从逗号分隔的文件加载你的数据，我们这样做的速度比做数千万次插入的速度快7-8倍。