Statement Caching#

SQL 是一種宣告式語言，描述的是「要什麼」而非「怎麼做」。資料庫會自行選擇最有效率的 execution strategy 來產生結果集。為了提升效能，資料庫可以透過 Statement Caching 來避免重複的解析與最佳化工作。

Statement Lifecycle#

資料庫處理 SQL statement 的三大核心模組為 Parser、Optimizer 與 Executor。

Parser#

• Parser 負責檢查 SQL statement 的語法正確性（syntactic validation）與語意正確性（semantic validation，確認參照的 table 與 column 確實存在）
• 解析完成後，SQL statement 會被轉換為資料庫內部表示，稱為 syntax tree（又稱 parse tree 或 query tree）
• Syntax tree 是 SQL statement 的邏輯表示，描述完成查詢所需的資料庫物件

Optimizer#

• Optimizer 針對給定的 syntax tree，決定最有效率的 data fetching 演算法
• 需要評估的項目包括：
  • 每個 table 的 access method（table scan 或 index scan）
  • 對於 index scan，選擇最合適的 index
  • 對於每個 join relation，選擇最佳的 join type（Nested Loops Joins、Hash Joins、Sort Merge Joins）
  • Joining order，對 Nested Loops Joins 特別重要
• 最終產出一份 execution plan
• 最常見的決策演算法是 CBO（Cost-Based Optimizer），根據 table size、data cardinality 等統計資訊，計算 CPU cycles 與 I/O 操作數來估算每個 plan 的成本
• Optimizer 有固定的時間預算，因此它在有限時間內找出合理的 plan，而非保證找到全域最佳解

Execution plan cache 使用固定大小的記憶體空間，以 LRU 策略淘汰較少使用的 plan。DDL statement 可能使現有 execution plan 失效，資料庫必須有機制驗證 cached plan 的有效性。

Execution Plan Visualization#

各資料庫提供不同的方式來檢視 execution plan：

• Oracle：使用 EXPLAIN PLAN FOR 語法，輸出存放在 dbms_xplan package
• PostgreSQL：使用 EXPLAIN 關鍵字
• SQL Server：透過 Management Studio 的圖形化 viewer，或啟用 SHOWPLAN_ALL 設定
• MySQL：使用 EXPLAIN 或 EXPLAIN EXTENDED；MySQL 5.6.5 以上可使用 EXPLAIN FORMAT=JSON 取得更詳細的成本資訊

Executor#

• Executor 接收 execution plan，從 Storage Engine 載入資料並建構 result set
• 同時利用 Transaction Engine 確保交易的資料完整性保證
• 足夠大的 in-memory buffer 可以減少 I/O contention，降低 transaction response time
• Consistency model 也會影響整體效能：需要取得的 lock 越多，parallel execution 的機會就越少

Caching Performance Gain#

在深入 server-side 與 client-side caching 之前，先量化 statement reuse 對效能的影響。測試使用包含 table joining 與 query nesting 的非簡單 execution plan，計算一分鐘內可執行的 query 數量：

Statement caching 對於高效能 OLTP（Online Transaction Processing）系統的最佳化扮演非常重要的角色。大多數資料庫系統都能從 statement reuse 中獲益，某些情況下效能提升相當顯著。

Server-side Statement Caching#

由於 statement parsing 與 execution plan generation 是資源密集的操作，部分資料庫提供 execution plan cache。其運作方式為：

• 以 SQL statement 字串為輸入，經過 hashing function 產生 cache entry key
• 若 statement 字串在不同次執行之間有所變動，就無法重用已有的 execution plan
• 因此，動態產生的 JDBC Statement(s) 不適合用來重用 execution plan

PreparedStatement 始終關聯到單一 SQL statement，使用 bind parameters 來替換不同的執行時期參數值。資料庫可以在建立時期就 precompile SQL statement：

1. PREPARE 階段：資料庫驗證 SQL statement 並解析為 syntax tree
2. EXECUTE 階段：driver 送出實際參數值，資料庫直接編譯並執行 execution plan

部分資料庫支援 Forced Parameterization，可以在 runtime 攔截 SQL statement，自動將所有 value literals 替換為 bind variables，使其能重用已 cached 的 execution plan。Oracle 使用 ALTER SESSION SET cursor_sharing=force，SQL Server 使用 ALTER DATABASE ... SET PARAMETERIZATION FORCED。

Bind-sensitive Execution Plans#

Execution plan 的選擇取決於 bind parameter 的 selectivity（選擇性）：

• Cardinality：給定 predicate 回傳的列數
• Selectivity：cardinality 除以總列數的百分比
• Selectivity 越低（匹配的列數越少），predicate 越具選擇性，Optimizer 傾向使用 index lookup
• Selectivity 較高時（如 95%），Optimizer 傾向使用 sequential scan，以減少隨機 disk access 的 roundtrip

若 selectivity 在整個 bind value domain 中保持一致，execution plan 就不受參數值影響，稱為 generic execution plan，較容易重用。反之，bind-sensitive execution plan 需要更精細的處理。

各資料庫的實作#

Oracle：

• 重用現有 execution plan 稱為 soft parse，需要 SQL statement 完全一致（含大小寫與空白）
• 找不到現有 plan 時進行 hard parse，Optimizer 評估多個 plan 並選擇成本最低的
• 使用 bind peeking：在 hard parse 時檢查第一組 bind parameter 來決定 cached plan 的 selectivity
• Oracle 11g 引入 adaptive cursor sharing，允許一個 statement 使用多個 execution plan，根據 selectivity metadata 決定重用哪個

SQL Server：

• 對 Statement(s) 和 PreparedStatement(s) 都會 cache execution plan，存放在 procedure cache
• 使用 parameter sniffing：在首次執行時檢查實際參數值
• 若 cached plan 效能不佳或資料分布統計有衝突性變化，資料庫會自動重新編譯
• 可使用 OPTION(RECOMPILE) query hint 強制每次重新產生 plan
• 建議 prepared statement 使用 qualified object name（含 schema），以利 cache 正確運作

PostgreSQL：

• 自 PostgreSQL 9.2 起，prepare 階段僅解析與重寫 statement，optimization 與 planning 延遲到 execution time
• 預設情況下，JDBC PreparedStatement(s) 會將 prepare 與 execute 合併在單一 database roundtrip 中
• Client-side prepared statement 需執行至少 5 次才會轉換為 server-side statement（由 prepareThreshold 參數控制）
• 若效能不受 bind parameter 值影響，Optimizer 可能將 plan 轉為 generic plan 並 cache 重用

MySQL：

• Parser 產生 syntax tree 後，經 resolution mechanism 驗證與預先最佳化，產出 permanent tree
• 自 MySQL 5.7.4 起，所有 permanent transformations 在 prepare 階段完成，execution 階段僅處理 data-sensitive transformations
• MySQL 不 cache execution plan，每次執行都針對當前 bind parameter 值最佳化，避免 data skew 問題
• MySQL JDBC driver 預設僅 emulate server-side prepared statement；需同時設定 useServerPrepStmts 和 cachePrepStmts 為 true 才能啟用真正的 server-side prepared statement

Client-side Statement Caching#

除了資料庫端的 caching，JDBC driver 也可以重用已建構好的 statement 物件。Client-side statement caching 的主要目標：

• 降低 client-side statement processing 時間，進而降低 transaction response time
• 節省應用程式資源，回收 statement 物件及其相關的 database-specific metadata

在高效能 OLTP 應用中，transaction 往往非常短暫，即使是微小的 response time 降低，都能對整體 transaction throughput 產生實質影響。

Oracle#

Oracle JDBC driver 僅支援 PreparedStatement(s) 和 CallableStatement(s) 的 caching（預設關閉）。提供兩種機制：

• Implicit caching：僅儲存 statement metadata（不隨執行改變的資訊）。設定 implicitStatementCacheSize 即可啟用。預設所有 statement 都會被 cache，可透過 setPoolable(false) 排除特定 statement
• Explicit caching：透過 Oracle-specific API 管理，使用 getStatementWithKey() 取得、closeWithKey() 歸還。除了 metadata，還能儲存 execution state 與 data

Explicit caching 使用 vendor-specific API，會耦合到 Oracle 特定的程式碼，影響可攜性。在需要支援多種資料庫系統時，需要更複雜的 data access logic。

SQL Server#

• Microsoft SQL Server JDBC driver 定義了 disableStatementPooling 屬性，但截至 4.2 版，statement cache 尚無法啟用
• 替代方案 jTDS（開源 JDBC 3.0 實作）支援 per-connection 的 statement caching，預設 cache 500 個 entries，可透過 setMaxStatements() 調整

PostgreSQL#

• 自 PostgreSQL JDBC driver 9.4-1202 版起，client-side statement 會被 cached，且 server-side statement key 在 PreparedStatement(s) 關閉後仍會保留
• 透過以下 connection properties 控制：
  • preparedStatementCacheQueries：每個 connection cache 的 statement 數量（預設 256）
  • preparedStatementCacheSizeMiB：cache 的記憶體上限（預設 5 MB）
• setPoolable(boolean poolable) 方法無效，無法按個別 statement 停用 caching

MySQL#

• Statement caching 關聯到 database connection，套用於所有執行中的 statement
• 在 Connector/J 5.1.36 版中，setPoolable(false) 僅停用 server-side statement 的 caching，client-side 不受影響
• 透過以下 connection properties 設定：
  • cachePrepStmts：啟用 client-side statement cache 與 server-side statement validity checking（預設關閉）
  • prepStmtCacheSize：每個 connection cache 的 statement 數量（預設 25）
  • prepStmtCacheSqlLimit：允許被 cache 的 SQL statement 最大長度（預設 256）