使用预编译语句批量更新，在Java中进行批量插入

Question

我正在尝试使用Java填充一个resultSet，包含大约50,000行10列的数据，并使用PreparedStatement的batchExecute方法将它们插入到另一个表中。

为了加快这个过程，我进行了一些研究，发现在读取数据到resultSet时，fetchSize起着重要作用。

如果fetchSize非常低，可能导致向服务器发送太多请求，而如果fetchSize非常高，则可能会阻塞网络资源，因此我进行了一些实验，并设置了适合我的基础设施的最优大小。

我正在读取这个resultSet并创建插入语句以插入到另一个不同数据库的表中。

类似于这样（只是示例，不是真正的代码）：

for (i=0 ; i<=50000 ; i++) {
    statement.setString(1, "[e-mail]");
    statement.setLong(2, 1);
    statement.addBatch();
}
statement.executeBatch();

• executeBatch方法会尝试一次性发送所有数据吗？
• 是否有一种方法来定义批处理大小？
• 有没有更好的方法来加快大量插入的过程？

在批量更新（50,000行10列）时，使用可更新的ResultSet还是带有批处理执行的PreparedStatement更好？

- Mrinmoy

Answer 1

我将逐一回答您的问题。

• executeBatch方法会一次性尝试发送所有数据吗？

这取决于每个JDBC驱动程序，但我研究过的几个会迭代每个批处理条目，并将参数与预处理语句句柄一起发送到数据库以执行。也就是说，在上面的示例中，将执行50,000次准备好的语句，每次都有50,000对参数，但是这50,000步可以在较低级别的“内部循环”中完成，这就是时间节省的地方。一个相当牵强的比喻是，从“用户模式”退到“内核模式”，并在那里运行整个执行循环。您可以节省每个批处理条目中跳入和跳出更低级别模式的成本。

• 有没有办法定义批量大小？

通过在使用Statement#executeBatch()执行批处理之前推送50,000个参数集，您已经隐含地定义了它。批量大小为1同样有效。

• 是否有任何更好的方法来加快批量插入的进程？

考虑在批量插入之前明确打开事务，并在之后提交它。不要让数据库或JDBC驱动程序在批处理的每个插入步骤周围强制执行事务边界。您可以使用Connection#setAutoCommit(boolean)方法控制JDBC层。首先将连接从自动提交模式中取出，然后填充批次，启动事务，执行批处理，然后通过Connection#commit()提交事务。

假设您的插入操作不会与并发写操作竞争，并且假设这些事务边界将为您提供足够一致的值，供插入操作中使用。如果不是这种情况，请优先考虑正确性而非速度。

• 使用可更新的ResultSet还是使用批量执行的PreparedStatement更好？

最好的方法是使用您选择的JDBC驱动程序进行测试，但我预计后者-PreparedStatement和Statement#executeBatch()会在此处获胜。语句句柄可能具有关联的“批处理参数”列表或数组，每个条目都是在调用Statement#executeBatch()和Statement#addBatch()（或Statement#clearBatch()）之间提供的参数集。列表将随每次调用addBatch()而增长，并且直到调用executeBatch()之前不会刷新。因此，Statement实例实际上是作为参数缓冲区；您正在使用Statement实例来交换内存和便利性（而不是自己的外部参数设置缓冲区）。

同样地，只要我们不讨论特定的 JDBC驱动程序，您应该将这些答案视为一般性和推测性的。每个驱动程序的复杂程度都不同，每个驱动程序追求的优化也不同。

- seh

---

谢谢，非常有趣的指针。这确实提高了我对JDBC的理解。我会从这里开始尝试一下。 - Mrinmoy

Answer 2

批处理将会一次性完成 - 这是您要求的。

一次调用尝试处理 50,000 似乎有点过大。我建议将其拆分为较小的 1,000 的块，如下所示:

final int BATCH_SIZE = 1000;
for (int i = 0; i < DATA_SIZE; i++) {
  statement.setString(1, "[e-mail]");
  statement.setLong(2, 1);
  statement.addBatch();
  if (i % BATCH_SIZE == BATCH_SIZE - 1)
    statement.executeBatch();
}
if (DATA_SIZE % BATCH_SIZE != 0)
  statement.executeBatch();

5万行数据不应该花费超过几秒钟的时间。

- Bohemian

---

谢谢，我会按照您建议的去做，但这将是我的最后选择。我正在寻找JDBC API中的一些内置功能，例如在批量读取时设置Fetch-Size一样的批处理大小。如果没有提供批处理执行的批处理大小，那么为什么允许FetchSize进行批量读取呢？ - Mrinmoy

@Bohemian，DATA_SIZE 的理想值是多少？ - Bindumalini KK

在我的经验中，10K是一个不错的起点。为了调整它，不断提高它的值，直到它不能可靠地工作为止，然后使用一半的值。 - Bohemian

Answer 3

批量未记录更新不会按照您的方式提供所需的改进性能。请参见this。

- Lekkie

Answer 4

如果只是将一个或多个表中的数据插入到此表中，而没有干预（更改结果集），则调用statement.executeUpdate(SQL)执行INSERT-SELECT语句，这样更快，因为没有开销。没有数据流出数据库，整个操作都在数据库中进行，而不是在应用程序中进行。

- LINQ Newbee

---

正如我在问题中提到的，我正在将数据插入到不同数据库的不同表中，因此由于没有任何指向源的DBlink，因此无法使用插入选择语句。 - Mrinmoy