JDBC批量插入性能优化

Question

我需要将几亿条记录插入到mysql数据库中。我每次批量插入100万条记录。请看下面的代码。它似乎很慢。有没有什么方法可以优化它？

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);

- user157195

---

你的代码有点损坏了（并且提前截断了）。 - Uri

顺便问一下，你正在使用哪个驱动程序？是通用的JDBC，还是JDBC-MySQL连接器？ - Uri

我正在使用com.mysql.jdbc.Driver。 - user157195

需要多长时间？您是基于哪些比较材料得出它运行缓慢的结论的？ - BalusC

我只是在本地电脑（4GB RAM）上进行了一次100万条数据的测试插入，大约花了10分钟，想知道是否有改进的空间。 - user157195

Answer 1

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

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

- Bertil Chapuis

---

@Kimble - 那为什么不接受这个答案呢？ 谢谢，伙计！这就像魔法一样有用！ - Peter Perháč

10秒钟代替一小时。 好评加一！ - 0x6B6F77616C74

MySQL 驱动程序属性的参考文档已经移动。 - ben3000

我注意到在使用Java驱动程序（至少在驱动程序的5.1.10版本中）进行插入批处理时，还有另一个要求：在SQL语句中，“VALUES”部分必须后跟一个空格，而不是直接跟随开括号，否则驱动程序将退回到顺序插入。 - Christian Semrau

你能解释一下为什么添加useServerPrepStmts=false可以起作用吗？在阅读完https://dev59.com/qlwY5IYBdhLWcg3wuZym之后，我本以为它会产生相反的效果。我相信你是正确的，但我不完全理解这个设置如何提高性能？谢谢。 - Stephane Grenier

Answer 2

我希望扩展一下Bertil的答案，因为我一直在尝试连接URL参数。 rewriteBatchedStatements=true是一个重要的参数。useServerPrepStmts默认就已经是false，即使将其改为true，在批量插入性能方面也没有太大的区别。

现在我想写一下rewriteBatchedStatements=true如何如此显著地提高性能。它通过重写INSERT语句的准备语句为多值插入(来源)来实现。这意味着，每次调用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服务器的每个语句记录到一个文件中。

- Eran

---

Eran - 关于更新，rewriteBatchedStatements=true 是否像插入语句一样提高了更新的性能？因为更新语法与插入不同，不能像插入一样整体执行（据我所知）。我之所以这么说是因为我在插入10k+行时只需要不到一秒钟，而在更新100行时需要1秒钟。 - rpajaziti

感谢您的解释。我有点惊讶JDBC驱动程序发送了一个长的SQL字符串，而不是使用二进制API。 - Sam Goldberg

@SamGoldberg 嗯，这个答案已经8.5年了，所以我不确定JDBC驱动程序是否仍然会以这种方式运行。 - Eran

Answer 3

您可以使用一条插入语句插入多行，每次执行几千次可以大大加速处理速度，即不是执行如下 3 条形如 INSERT INTO tbl_name (a,b,c) VALUES(1,2,3); 的插入语句，而是执行 INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(1,2,3),(1,2,3);（现在可能 JDBC 的 .addBatch() 做了类似的优化 - 尽管 MySQL 的 addBatch 曾经完全未优化，只发出单个查询。无论如何，我不知道最近的驱动程序是否仍然是这种情况）。

如果您真的需要速度，请使用带有LOAD DATA INFILE的逗号分隔文件加载数据，我们通过这种方式可以获得大约7-8倍的速度提升，相对于执行数千万次的插入操作。

- nos

---

使用load data infile可能是一个不错的选择，但我的输入文件需要清理，我只对插入某些行感兴趣，其中第二个标记与一个字符串匹配（空格分隔的标记），load data infile是否足够灵活以过滤行？ - user157195

我认为它不能进行过滤，但你可以自己清理数据，写入一个新文件并加载该文件。 - nos

我的插入现在快了10倍！ - Matt Sgarlata

Answer 4

如果：

1. 这是一张新表，或要插入的数据量大于已经插入的数据量； 2. 表上有索引； 3. 在插入时不需要对表进行其他访问；

那么，ALTER TABLE tbl_name DISABLE KEYS 可以大大提高插入操作的速度。完成后，运行 ALTER TABLE tbl_name ENABLE KEYS 来开始构建索引。这可能需要一段时间，但不会像为每个插入操作构建索引那样耗费长时间。

- Wrikken

Answer 5

try {
        // Disable auto-commit
        connection.setAutoCommit(false);
        int maxInsertBatch = 10000;     
        // Create a prepared statement
        String sql = "INSERT INTO mytable (xxx), VALUES(?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);

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

        // Execute the batch
        pstmt.executeBatch();
        System.out.append("inserted "+count);

- Hieu HoangChi

---

不要直接点踩，可以在评论中解释为什么在执行多个批处理之间而不是一次性执行所有批处理时，它能否提高性能。 - benez

看起来上面的答案有一个几乎相同的代码片段，这是一个问题。 - suhas0sn07

Answer 6

您可以尝试使用DDBulkLoad对象。

// Get a DDBulkLoad object
DDBulkLoad bulkLoad = DDBulkLoadFactory.getInstance(connection);
bulkLoad.setTableName(“mytable”);
bulkLoad.load(“data.csv”);

- Lalith