Batch Updates#

JDBC 2.0 引入了 batch updates，允許將多個 DML 語句打包成單一資料庫請求。這樣做可以減少資料庫 roundtrip 次數，進而降低交易回應時間。即便規格中使用 updates 一詞，任何 insert、update 或 delete 語句都可以進行 batching。

JDBC 支援 java.sql.Statement、java.sql.PreparedStatement 和 java.sql.CallableStatement 的 batching。不同資料庫驅動程式的實作各有差異，甚至同一驅動程式的不同版本也可能需要不同的配置。

Figure 3.1: Statement batching

Batching Statements#

JDBC Statement 介面提供了 batching API，透過 addBatch() 加入語句，再以 executeBatch() 一次送出執行。executeBatch() 的回傳值 int[] 包含每個語句影響的資料列數。

除了測試用途外，不應使用 Statement 來執行 CRUD 操作，因為直接拼接 SQL 字串容易遭受 SQL injection 攻擊。

各資料庫驅動程式的差異#

  • Oracle – 對 Statement 和 CallableStatement，Oracle JDBC Driver 實際上不支援 batching，每個語句仍會被個別執行。只有 PreparedStatement 才能真正享受 batch 優勢。
  • MySQL – 預設情況下，MySQL JDBC driver 不會將 batched 語句合併成單一請求。必須設定 rewriteBatchedStatements 連線屬性，驅動程式才會將語句重寫到單一 String buffer 中。對 PreparedStatement，此屬性會將 batched insert 重寫為 multi-value insert，但代價是無法使用 server-side prepared statements。

Statement Batching 的效能觀察#

  • 測試以插入 1000 筆 post 及各 4 筆 comment 為基準，不同 batch size 下各資料庫表現差異顯著
  • 重新排序語句（例如先插入所有 post 再插入 comment）對整體效能改善有限，但部分驅動程式（如 MySQL）可能從中受益
  • 實務上建議使用較小的 batch size，以降低 client 端記憶體使用量並避免 server 端瞬間負載過大

Figure 3.2: Reordered statement batching

Figure 3.3: MySQL Statement batching

Batching PreparedStatements#

對於參數化語句，PreparedStatement 是比 Statement 更好的選擇。Statement 只能透過字串拼接來改變 SQL 語句，這不僅有 SQL injection 風險，效能也較差。

PreparedStatement 綁定單一 DML 語句，可以透過 addBatch() 將多組參數值加入同一個 prepared statement 的 batch 中，再一次執行：

PreparedStatement ps = connection.prepareStatement(
    "INSERT INTO post (title, version, id) VALUES (?, ?, ?)");
ps.setString(1, "Post no. 1");
ps.setInt(2, 0);
ps.setLong(3, 1);
ps.addBatch();
// ... 加入更多參數組 ...
int[] updateCounts = ps.executeBatch();

所有 DML 操作（insert、update、delete）都能從 PreparedStatement batching 中獲得顯著效能提升。相較於 Statement batching，同樣的資料量下 PreparedStatement 表現更佳。

Statement 不應用於 CRUD batching 操作，較適合用於 bulk processing（如一次性的批次更新或刪除）。

Choosing the right batch size#

選擇合適的 batch size 沒有通用的數學公式，必須透過實際測量來決定最佳值。以下是關鍵觀察：

  • 即使是較小的 batch size 也能顯著降低交易回應時間
  • 效能增益不會隨 batch size 線性成長——增大 batch size 能節省更多 roundtrip，但整體效能提升會趨緩
  • 過大的 batch size 反而可能損害效能，因為交易執行時間過長
  • 實務上，10 到 30 之間的 batch size 通常是不錯的起始值

Figure 3.4: Insert PreparedStatement batch size

Figure 3.5: Update PreparedStatement batch size

Figure 3.6: Delete PreparedStatement batch size

建議針對不同的 batch size 進行效能測量，找出應用程式的最佳值。不要盲目使用過大的 batch size。

Bulk processing#

除了 batching 之外，SQL 本身提供了 bulk operations 來一次修改所有符合條件的資料列。Bulk update 或 delete 語句同樣能受益於索引：

UPDATE post SET version = version + 1;
DELETE FROM post WHERE spam = true AND created_on < current_timestamp - INTERVAL '30' day;
  • Bulk 操作比 batch updates 快一個數量級——例如 bulk update 只需 9-58 ms，而 batch update 則需數百毫秒
  • 然而 batch updates 可搭配應用層的 optimistic locking，適合需要防止 lost updates 的場景（資料在 read-only transaction 中載入，再於後續 transaction 中寫回）
  • Bulk delete 同樣遠快於 batch delete

Long-running transaction 的風險：在高併發環境中，無論使用 2PL (Two-Phase Locking) 或 MVCC (Multiversion Concurrency Control)，writers 總是會阻塞其他 writers。若 batch 或 bulk 操作修改了大量記錄，建議將大型批次任務拆分為較小的可管理單位，以便及時釋放鎖。

Retrieving auto-generated keys#

許多開發者習慣將 primary key 的生成委託給資料庫（如 identity column），但 auto-generated identifiers 可能與 batch insert 流程產生衝突。

設定自動遞增識別碼的策略因資料庫而異，主要分為：

  • Identity column – Oracle 12c (GENERATED ALWAYS AS IDENTITY)、SQL Server (IDENTITY)、PostgreSQL (SERIAL / BIGSERIAL)、MySQL (AUTO_INCREMENT)
  • Database sequence – Oracle、SQL Server 2012+、PostgreSQL 都支援

要取回自動生成的 key，JDBC PreparedStatement 提供三種方式：

  1. 使用 Statement.RETURN_GENERATED_KEYS 旗標
  2. 指定 column index（如 new int[] {1}）
  3. 指定 column name（如 new String[] {"id"}）

這三種方式在不同資料庫上不能互換使用。Oracle 使用 RETURN_GENERATED_KEYS 時會回傳 ROWID 而非實際生成的值，必須改用 column index 或 column name。

各驅動程式的 batch key retrieval 支援#

並非所有驅動程式都支援從 batch 語句中取回 auto-generated keys：

驅動程式executeUpdate()executeBatch()
Oracle JDBC 11.2.0.4YesNo
Oracle JDBC 12.1.0.1YesYes
SQL Server JDBC 4.2YesNo
PostgreSQL JDBCYesYes
MySQL JDBC 5.1.36YesYes

Sequences to the rescue#

相較於 identity column，database sequences 可以將識別碼生成與實際的 row insert 解耦。在 insert 前先取得 sequence 值，就不需要依賴 getGeneratedKeys() 方法，batch insert 也不再受限於驅動程式的支援程度。

try (PreparedStatement ps = connection.prepareStatement(
        "INSERT INTO post (id, title, version) VALUES (?, ?, ?)")) {
    for (int i = 0; i < postCount; i++) {
        if (i > 0 && i % batchSize == 0) {
            ps.executeBatch();
        }
        ps.setLong(1, getNextSequenceValue(connection));
        ps.setString(2, String.format("Post no. %1$d", i));
        ps.setInt(3, 0);
        ps.addBatch();
    }
    ps.executeBatch();
}
  • 對於少量插入，額外的 sequence 呼叫開銷可忽略不計
  • 對於大量資料的批次處理器，額外的 sequence roundtrip 可能累積成顯著開銷

Sequence 呼叫最佳化：如果 sequence 的遞增步長（increment step）大於 1（例如 N），則 data access layer 可以在兩次資料庫 sequence 呼叫之間自行分配 N 個連續識別碼（如 2, 3, …, N-1, N），大幅減少網路 roundtrip。此策略在 Hibernate types and identifiers 章節有更詳細的討論。

Backward JDBC Connection Management Statement Caching Forward