DB2 SQL語句優化指南：提升應用程式效能的七大核心準則

前言：應用程式設計決定資料庫效能

在設計新系統或分析現有系統時，應用程式的設計品質是影響效能的關鍵因素。即便資料庫本身設計優良且經過調校，不當的應用程式設計仍會導致嚴重的效能瓶頸。實務經驗證明，修正應用程式層面的設計缺失，往往比調整資料庫配置參數更能顯著改善效能。

DB2 SQL 編譯器的雙階段優化機制

DB2 通用資料庫內建 SQL 編譯器，執行過程包含兩個關鍵階段：

• 查詢重寫：編譯器將 SQL 語句轉換為更易優化的等價形式。
• 查詢優化：依據表、索引、欄位及函數的統計資訊，評估多種執行方案的成本，選擇成本最低的存取路徑。

值得注意的是，無論存取方案優劣，編譯器必須選擇一個方案產生結果集。因此，撰寫查詢時應僅檢索必要的資料，避免多餘資料干擾優化器判斷，確保其能選出最佳存取方案。

撰寫高效 SELECT 語句的七大黃金準則

1. SELECT 清單僅指定必要欄位

避免使用 SELECT *。雖然編寫方便，但會導致系統傳回不需要的欄位，造成不必要的 I/O、網路傳輸與記憶體消耗，尤其在寬表中浪費更明顯。這也是不良的程式設計習慣。

2. 善用謂詞限制回傳列數，理解四類謂詞效能階層

DB2 依評估方式與時機將謂詞分為四類，效能由高至低排序：

1. 範圍界定謂詞：限定索引掃描範圍，提供起始/終止鍵值（如 name = 'John'）。
2. 索引參數謂詞：不界定範圍，但可透過索引評估（如複合索引 (name, dept, years) 中的 years > 5）。
3. 資料參數謂詞：需由資料管理服務（DMS）評估，通常需存取基礎表資料列。
4. 剩餘謂詞：無法透過索引或 DMS 早期評估，處理成本最高。

範圍界定與索引參數謂詞能減少需讀取的資料頁數；索引參數謂詞不影響索引頁存取數。撰寫 WHERE 子句時，應盡量使用前兩類謂詞。

3. 明確指定 FOR UPDATE 子句減少死結機會

在游標宣告中加入 FOR UPDATE OF <欄位>，提取資料列時會直接施加 U（更新）鎖，而非 S（共享）鎖。這能有效避免兩個應用程式同時持有 S 鎖並嘗試升級為 U 鎖而導致的死結情境。CLI 程式可透過 SQLSetConnectAttr() 設定 SQL_ATTR_ACCESS_MODE = SQL_MODE_READ_WRITE 達到同樣效果。

4. 使用 OPTIMIZE FOR n ROWS 子句引導優化器

當預期提取行數遠小於潛在結果集時，加入 OPTIMIZE FOR n ROWS。此子句會影響查詢優化策略與通訊緩衝區大小（以 n 列為一組傳輸）。注意：它不限制實際提取行數，若最終行數 ≤ n 則提升效能，否則可能降低效能。

5. 使用 FETCH FIRST n ROWS ONLY 限制結果集上限

若不想提取超過 n 列，請指定 FETCH FIRST n ROWS ONLY。此子句同樣決定通訊緩衝區大小，且不可與 FOR UPDATE 同時使用。若同時指定 OPTIMIZE FOR 與 FETCH FIRST，緩衝區大小取兩者較小值。

6. 指定 FOR FETCH ONLY 提升唯讀查詢效能

對於純查詢操作，加入 FOR FETCH ONLY（或同義詞 FOR READ ONLY）可啟用區塊提取技術，減少管理器負擔，並移除資料列上的獨佔鎖，改善並發性能。

7. 避免資料類型隱含轉換

比較或聯結時，確保參與運算的欄位資料類型一致。例如 DECIMAL 與 INTEGER 聯結會觸發執行期轉換（十六進位 123C vs 7B），增加 CPU 開銷並可能因精度問題導致錯誤結果。

資料類型選擇的實務建議

• 短定長字串：優先用 CHAR 而非 VARCHAR，避免長度檢查開銷。
• 可變長字串：用 VARCHAR/VARGRAPHIC 取代 LONG VARCHAR/LONG VARGRAPHIC，後者有緩衝池限制且最大長度差異極小。
• 數值型別：無小數部分需求時，用整數（SMALLINT/INTEGER/BIGINT）取代浮點數或十進位，處理成本大幅降低。
• 日期時間：優先用 DATE/TIME/TIMESTAMP 而非字串，佔用空間少且支援內建函數（YEAR、MONTH 等）運算效率更高。
• 數字優於字串：同義資料盡量用數值型別儲存。

資料庫設計需在儲存空間與處理成本間取捨，無法兼得。開發時應綜合評估整體應用架構，權衡利弊，追求整體效能最優。

結語

效能優化非屬事後維護專利，編碼階段即埋下優化種子。本文闡述撰寫 SQL 時的關鍵注意事項，拋磚引玉，期與同好共勉精進。