ResultSet Fetching#

在討論完 SQL statement 的最佳化（batching 與 caching）之後，本章將焦點轉向查詢處理的回應端。與 INSERT、UPDATE、DELETE 只回傳受影響的列數不同，JDBC SELECT 查詢會回傳一個 ResultSet。

資料庫 Executor 根據 execution plan 將資料擷取至 result set 中，資料列可以一次全部提取，也可以依客戶端的需求逐步提取。

SQL 標準透過 cursor descriptor 定義了以下 result set 屬性：

  • Scrollability — result set 的遍歷方向
  • Sensitivity — 資料何時被擷取
  • Updatability — 是否允許在遍歷時修改資料列
  • Holdability — result set 的範圍與 transaction 生命週期的關係

JDBC ResultSet 對上述所有屬性皆提供支援，並透過不同的常數值來設定。

ResultSet Scrollability#

JDBC ResultSet 透過 application-level cursor 來遍歷資料。fetching 機制隱藏在 iterator API 之後，將應用程式碼與實際的資料擷取策略解耦。

  • TYPE_FORWARD_ONLY（預設值）— 只能從第一筆遍歷到最後一筆，無法回頭
  • TYPE_SCROLL_INSENSITIVE — 取得載入時的靜態快照，可前後雙向遍歷
  • TYPE_SCROLL_SENSITIVE — 資料按需擷取，可動態反映其他 transaction 的變更

兩種 scrollable result set 的主要差異在於 selectivity：insensitive cursor 需要在遍歷前完整擷取資料（靜態視圖），而 sensitive cursor 允許動態擷取以反映並行變更。

各資料庫對 scrollable ResultSet 的支援程度不同。例如 Oracle 透過 client-side caching 模擬、PostgreSQL 僅支援 forward-only 與 insensitive scroll、MySQL 僅支援 insensitive scroll。

Figure 5.1: ResultSet cursor type

ResultSet Changeability#

預設情況下，result set 只是底層資料的唯讀投影。JDBC 標準也提供 updatable result set，讓應用程式在遍歷 cursor 時可以修改資料。

  • CONCUR_READ_ONLY（預設值）— 靜態資料投影，不允許列級操作
  • CONCUR_UPDATABLE — cursor 位置可用於更新、刪除甚至插入新紀錄

在 web 應用程式中，request 應盡可能短暫。將讀取與寫入邏輯混合在同一個 database transaction 中，並長時間持有 updatable result set 及其底層的 database connection，會嚴重影響應用程式的可擴展性。

效能測試顯示，所有資料庫在使用 forward-only 加 read-only 的 result set 時，效能都略優於 scroll sensitive 加 updatable 的組合。

如果目前的 transaction 不需要更新所選的記錄，forward-only 加 read-only（JDBC 1.0 的預設值）通常是最高效的選項，在大多數情境下仍然是正確的選擇。

ResultSet Holdability#

JDBC 3.0 新增了 result set holdability 的支援。與 scrollability 和 updatability 不同，其預設值取決於各資料庫的實作。

  • CLOSE_CURSORS_AT_COMMIT — 當前 transaction 結束時關閉 result set
  • HOLD_CURSORS_OVER_COMMIT — transaction commit 後 result set 仍然保持開啟

各資料庫的預設行為：

  • Oracle — 僅支援 HOLD_CURSORS_OVER_COMMIT，設定其他值會拋出例外
  • SQL Server — 預設在 transaction commit/rollback 後仍保持開啟，也支援 CLOSE_CURSORS_AT_COMMIT
  • PostgreSQL — 預設為 CLOSE_CURSORS_AT_COMMIT，亦支援 HOLD_CURSORS_OVER_COMMIT
  • MySQL — 僅支援 HOLD_CURSORS_OVER_COMMIT

即使 CLOSE_CURSORS_AT_COMMIT 並非所有資料庫都支援，也可以透過在 transaction 結束後主動關閉所有 ResultSet 與 Statement 物件來達到相同效果。為了可擴展性，result set 所佔用的系統資源應儘早釋放。

Fetching Size#

JDBC ResultSet 作為 application-level cursor，每次遍歷 statement 時，結果必須從資料庫傳輸到客戶端。傳輸速率由 Statement 的 fetch size 控制：

statement.setFetchSize(fetchSize);

自訂 fetch size 可提示 driver 每次資料庫 roundtrip 要擷取多少列。預設值 0 表示由各 driver 自行決定其擷取策略。

各資料庫的 fetch size 行為：

  • Oracle — 預設 fetch size 為 10。10i/11g driver 在 statement 建立時即預先配置記憶體 buffer，12c 則延遲配置以降低記憶體消耗。建議上限為 100 筆
  • SQL Server — 使用 adaptive buffering 自動分批擷取，batch 大小由 driver 自動控制
  • PostgreSQL — 預設一次擷取全部結果至客戶端記憶體。設定 fetch size 後改用 database cursor 按需擷取
  • MySQL — 預設擷取全部結果。可透過設定 Integer.MIN_VALUE 啟用 streaming（逐列擷取），或設定 useCursorFetch=true 配合正整數 fetch size 分批擷取

效能測試顯示，fetch size 為 1 時需要 10,000 次 roundtrip，網路開銷極大。提升至 100 時效能顯著改善，但超過 100 後收益遞減。最佳 fetch size 取決於應用程式的資料量和並行拓撲，需透過實際量測來決定。

Figure 5.2: ResultSet fetch size

ResultSet Size#

設定適當的 fetch size 可以加速 result set 的擷取，但前提是 statement 只擷取目前業務邏輯所需的資料。過多的 ORM 工具使用往往導致 SELECT 的資料列或欄位超出實際需要。

Too Many Rows#

隨著資料表的增長，原本適度的 result set 可能變成效能瓶頸。UI 只能顯示有限資料，因此擷取整個 result set 是不必要的——分頁或動態捲動是解決方案。在批次處理中，限制 result set 也能避免長時間 transaction 對資料庫 undo/redo log 的壓力。

當 result set 大小受外部因素限制時，選取超出需要的資料毫無意義。限制查詢可確保可預測的回應時間與資料庫資源使用率，列級鎖越快釋放，data access layer 的可擴展性越高。

Figure 5.3: ResultSet size

限制 result set 有兩種策略：

SQL Limit Clause#

在 SQL statement 中直接加入列數限制，是最高效的策略。Optimizer 可據此產生最佳的 execution plan（例如選擇 index scan 而非 table scan）。

  • SQL:2008 標準 — OFFSET ? ROWS FETCH FIRST (?) ROWS ONLY（Oracle 12c、SQL Server 2012、PostgreSQL 8.4 起支援）
  • Oracle（舊版）— 使用 ROWNUM 虛擬欄位
  • SQL Server（舊版）— 使用 TOP 關鍵字
  • PostgreSQL / MySQL — 使用 LIMIT 關鍵字

JDBC Max Rows#

JDBC 規範定義了 maxRows 屬性，可限制當前 statement 的所有 ResultSet：

statement.setMaxRows(maxRows);

此方法具有跨所有 driver 實作的可移植性，但效果因資料庫而異：

  • Oracle — 在客戶端 cursor 以 fetch size 為單位擷取時檢查 maxRows 上界，可避免擷取不需要的紀錄。但若 maxRows 值較小，Optimizer 可能不會使用 index
  • SQL Server — driver 透過 SET ROWCOUNT N 實作，僅在 execution 階段生效，不影響 plan 生成，因此可能選擇 table scan 而非 index scan
  • PostgreSQL — driver 將 maxRows 連同查詢一起發送，Optimizer 可據此量身打造 execution plan
  • MySQL — maxRows 不會傳送至資料庫端，Optimizer 無法利用此提示

效能測試證實，SQL 層級的限制（如 LIMIT、FETCH FIRST）是限制 result set 的最佳策略。maxRows 的 driver 實作在部分資料庫上也能產生不錯的效果，但不如 SQL 層級直接。擷取過大的 result set 會對資料庫資源造成壓力，也會影響其他並行的 transaction。

Too Many Columns#

不僅過多的資料列會造成效能問題，擷取過多的欄位同樣會增加 result set 的處理時間。

測試案例對比了 SELECT *（擷取所有欄位）與僅 SELECT 所需欄位的效能差異，結果顯示自訂投影的執行時間顯著降低。

這個問題在 ORM 工具中尤為常見——為了完整填充 entity，所有欄位都會被 SELECT。對於少量 entity 可能不明顯，但對大型 result set 而言，這會成為顯著的效能問題。如果業務需求只需要 entity 屬性的子集，擷取多餘的欄位就是在浪費資料庫和應用程式資源（CPU、Memory、I/O、Networking）。

Figure 5.4: ResultSet projection size

Backward Statement Caching Transactions Forward