IBM DB2 SQL 編碼優化核心指南：從優化器原理到實戰最佳實踐

IBM DB2 SQL 編碼優化核心指南：從優化器原理到實戰最佳實踐

DB2 優化器是基於成本的專家系統，依據系統目錄統計信息、SQL 文本、CPU/I/O 成本模型生成最優存取路徑。掌握其工作機制並遵循編碼鐵律，能避開 80% 的性能坑。本文覆蓋優化器四步流程、統計信息維護、索引設計原則、三大 SQL 編碼鐵律、應用開發進階技巧。

優化器如何工作

1. 接收並驗證 SQL 語法。
2. 分析環境（統計信息、索引、約束等），枚舉候選存取路徑，計算成本，選擇最優。
3. 生成機器可讀指令（訪問計劃）。
4. 執行或緩存以備後續複用（動態/靜態 SQL）。

核心公式：總成本 = CPU 成本 + I/O 成本，權重由統計信息（行數、頁數、離散值分布、索引聚簇度等）決定。

統計信息：優化器的「眼睛」

必須保持最新，來源：

• 表：總行數、頁數、壓縮信息。
• 列：離散值數、值分布區間。
• 表空間：活動頁數。
• 索引：存在性、層級、葉頁數、離散鍵值、是否聚簇。
• 分區/節點組信息。

收集命令：RUNSTATS（Control Center、批處理、CLP）。生產環境協同 DBA 制定收集策略（負載低峰、大表抽樣等）。

索引設計：性能倍增器

基本判斷邏輯

SELECT LASTNAME, SALARY FROM EMP
WHERE EMPNO = '000010' AND DEPTNO = 'D01';

候選索引：EMPNO、DEPTNO、(EMPNO, DEPTNO)。組合索引通常最優——單次索引查找滿足兩個等值謂詞。

權衡因素

• 修改代價：每個索引增加 INSERT/DELETE/UPDATE（含索引列）開銷。
• 現有索引覆蓋：若已有單列索引，優化器可用索引合併（Index ORing/ANDing）滿足查詢，新增組合索引未必必要。
• 列順序：等值謂詞列前置，範圍謂詞列後置。例：WHERE EMPNO=? AND DEPTNO>>? → 索引 (EMPNO, DEPTNO)。
• 業務重要性：核心高頻查詢值得專用索引；邊緣查詢複用現有索引即可。
• 僅索引訪問：索引包含 SELECT 所有列（如 (EMPNO, DEPTNO) INCLUDE (LASTNAME, SALARY)），避免回表，大幅降低 I/O。謹慎使用，避免索引膨脹。

三大 SQL 編碼鐵律

1. 顯式列清單，拒絕 SELECT *
   SELECT LASTNAME FROM EMP WHERE EMPNO=? ✓
   SELECT * FROM EMP WHERE EMPNO=? ✗
   理由：表結構變更導致程序報錯；傳輸/處理無用列浪費 CPU/I/O/網絡；降低代碼可讀性。
2. 不檢索已知值
   SELECT LASTNAME, SALARY FROM EMP WHERE EMPNO='000010'; ✓
   SELECT EMPNO, LASTNAME, SALARY FROM EMP WHERE EMPNO='000010'; ✗
   WHERE 條件已鎖定 EMPNO，再次獲取純屬開銷。
3. 過濾下推：WHERE 子句在引擎端完成
   SELECT EMPNO, LASTNAME FROM EMP WHERE SALARY > 50000; ✓
   應用層全撈再過濾 ✗
   理由：DB2 利用索引/統計信息高效過濾，減少網絡傳輸與應用層 CPU。

進階技巧：參數化與游標優化

• 參數化查詢：WHERE EMPNO = ?。優化器可複用存取計劃，避免重複編譯，顯著提升高頻執行性能。
• FETCH FIRST n ROWS ONLY：限制返回行數，適用於分頁/前 N 需求。
  SELECT ... FROM EMP WHERE SALARY > 10000 FETCH FIRST 200 ROWS ONLY;
• OPTIMIZE FOR n ROWS：不限制結果集，但引導優化器優先優化前 N 行訪問路徑。適配「分頁顯示 20 行」場景。
  SELECT ... FROM EMP WHERE SALARY > 10000 OPTIMIZE FOR 20 ROWS;
• FOR READ ONLY：聲明只讀游標，消除歧義，允許優化器選擇更高效的只讀存取路徑（如阻塞讀取）。
  SELECT ... FROM EMP WHERE SALARY > 10000 FOR READ ONLY;

應用開發層面的事務與並發控制

• 頻繁 COMMIT：釋放鎖，避免長事務導致並發超時。修改操作（INSERT/UPDATE/DELETE）後盡快提交。
• 避免長事務中穿插用戶交互（思考時間導致鎖持有過久）。
• 只讀業務显式聲明 FOR READ ONLY，減少鎖開銷。

總結

優化器很強，但不是萬能。統計信息是基石，索引是杠桿，編碼規範是保障。遵循「顯式列、不取已知、下推過濾、參數化、合理分頁、只讀聲明」，配合 COMMIT 節奏控制，即可構建高性能、可維護的 DB2 應用。深入調優還需學習連接策略、子查詢轉換、EXPLAIN 計劃分析等進階技能——從基礎做起，漸進精通。