我需要将几亿条记录插入mysql db。我要一次插入一百万个。请在下面查看我的代码。它似乎很慢。有什么方法可以优化它吗?
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);
我在mysql中遇到类似的性能问题,并通过在连接URL中设置useServerPrepStmts和rewriteBatchedStatements属性来解决。
Connection c = DriverManager.getConnection("jdbc:mysql://host:3306/db?useServerPrepStmts=false&rewriteBatchedStatements=true", "username", "password");