JDBC批量插入性能 [英] JDBC batch insert performance

问题描述

我需要插入一对夫妇数亿条记录到MySQL数据库。同时，我批其插入1个亿。请参阅我下面code。这似乎是缓慢的。有什么办法去优化它？

  {尝试
        //关闭自动提交
        connection.setAutoCommit（假）;        //创建一个prepared声明
        SQL字符串=INSERT INTO MYTABLE（XXX）VALUES（？）;
        preparedStatement数PreparedStatement pstmt =连接prepareStatement（SQL）。        [对象]丘壑= set.toArray（）;
        的for（int i = 0; I＆LT; vals.length;我++）{
            pstmt.setString（1，瓦尔斯[I]的ToString（））;
            pstmt.addBatch（）;
        }        //执行批处理
        INT [] updateCounts = pstmt.executeBatch（）;
        System.out.append（插入+ updateCounts.length）;
 

解决方案

我也有类似的性能问题与MySQL和通过设置的 useServer prepStmts 的和的 rewriteBatchedStatements <解决它/ em>的在连接URL属性。

 连接C = DriverManager.getConnection(\"jdbc:mysql://host:3306/db?useServer$p$ppStmts=false&rewriteBatchedStatements=true\", 用户名密码）;
 

I need to insert a couple hundreds of millions of records into the mysql db. I'm batch inserting it 1 million at a time. Please see my code below. It seems to be slow. Is there any way to optimize it?

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

解决方案

I had a similar performance issue with mysql and solved it by setting the useServerPrepStmts and the rewriteBatchedStatements properties in the connection url.

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

这篇关于JDBC批量插入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！