JDBC 批量插入性能

我需要向 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);
74295 次浏览
小开

你可以用一个 insert 语句插入多个行，一次插入几千行可以大大加快速度，也就是说，你不需要插入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 过去完全没有优化，只是发出单独的查询——我不知道最近的驱动程序是否还是这样)

如果你真的需要速度，加载你的数据从逗号分隔的文件与 加载数据文件，我们得到大约7-8倍的加速这样做比做千万插入。

小开

如果:

  1. 它是一个新表，或者要插入的数据量大于已经插入的数据
  2. 表上有索引
  3. 在插入期间不需要对表的其他访问

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

小开

您可以尝试使用 DDBulkLoad 对象。

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

我在 mysql 中遇到过类似的性能问题，通过在连接 url 中设置 UseServerPrepStmts和 重写批处理语句属性解决了这个问题。

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

我想详细介绍一下 Bertil 的答案，因为我一直在试验连接 URL 参数。

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

现在我认为是时候写 rewriteBatchedStatements=true如何如此显著地提高性能了。它通过 rewriting of prepared statements for INSERT into multi-value inserts when executeBatch()(来源)这样做。这意味着不需要在每次调用 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 服务器的语句都会被记录到一个文件中。

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

提交答案