DB2 效能調優六大核心策略：從索引設計到鎖優化全維度指南

前言

DB2 效能調優是系統生命週期中的持續課題。本文提煉六大高頻實戰策略，涵蓋索引、分區、日誌、鎖、臨時表與連接寫法，助你精準定位瓶頸、實現量級提升。

策略一：索引建立時機與 RUNSTATS 協同

核心原則：「先建索引再寫入」與「寫入後建索引 + RUNSTATS」各有優劣，視業務容忍度選擇。

• 寫入前建索引：寫入過程同步維護索引，需大量日誌空間，導入速度較慢；優點是導入即可用，無額外維護窗口。
• 寫入後建索引：導入極快（可搭配 NOT LOGGED INITIALLY），但統計資訊過期導致優化器忽略索引；必須緊接 RUNSTATS ON TABLE schema.table AND INDEXES ALL 更新統計，才能讓優化器生成最佳執行計畫。

建議：大批量載入（ETL、初始化）採「後建索引 + RUNSTATS」；日常增量業務採「先建索引」。

策略二：大表部署於多節點表空間並配合索引

架構痛點：單節點表空間（如 DIM 維度表空間）僅能利用該節點 CPU/記憶體（例：2 CPU + 4GB），並發查詢時資源爭搶嚴重。

解決方案：

1. 為大表設計合理分區鍵（如日期、地區、業務代碼），建立於多節點表空間。
2. 查詢時 32 節點並行掃描，結果匯總至協調節點，線性縮短響應時間。
3. 並行 ≠ 免索引：即使並行度高，關鍵過濾/連接欄位仍需建立索引，可進一步將單節點掃描成本降維打擊。

策略三：大批量 DML 採用 NOT LOGGED 模式

ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE 或 LOAD ... NONRECOVERABLE。

• 收益：幾乎不產生重做日誌，消除日誌鎖爭用，寫入效能提升 3-10 倍。
• 風險：操作失敗無法回滾，表只能 DROP RECREATE 重來；嚴禁用於核心業務線上表，適用於可重跑的暫存表、階段表、數據倉庫載入窗口。

策略四：表級鎖升級減少鎖記憶體開銷

DB2 鎖上限受 LOCKLIST、MAXLOCKS 制約，海量行鎖會擠佔記憶體並觸發鎖升級（Lock Escalation）導致並發度驟降。

• 主動宣告：ALTER TABLE schema.table LOCKSIZE TABLE 強制使用表鎖，犧牲細粒度並發換取穩定的鎖記憶體佔用。
• 適用場景：獨佔式批次作業、資料倉庫載入窗口、唯讀報表表。
• 禁忌：高並發 OLTP 核心表絕對不可用。

策略五：臨時表「精準投料」與索引加持

常見反模式：為加速報表，將全量明細塞入臨時表，實際查詢僅用 10% 欄位/行。

• 只選需要的欄位：INSERT INTO temp SELECT col1, col2, col3 FROM ... 而非 SELECT *。
• 只載入需要的行：在來源端推下過濾條件（分區剪枝、分區鍵範圍）。
• 臨時表加索引：CREATE INDEX idx_temp_key ON temp(key_col)，後續連接/聚合可走索引掃描而非全表掃描。

策略六：LEFT JOIN 條件下推原則

錯誤寫法：

SELECT * 
FROM fact f 
LEFT JOIN dim d ON f.dim_key = d.dim_key 
  AND d.status = 'A'   -- 對右表過濾寫在 ON 中 ✅
  AND f.date = '2024-01-01'; -- 對主表過濾錯誤寫在 ON 中 ❌

正確寫法：

SELECT * 
FROM fact f 
LEFT JOIN dim d ON f.dim_key = d.dim_key 
  AND d.status = 'A'   -- 右表限制留在 ON
WHERE f.date = '2024-01-01'; -- 主表限制放在 WHERE

原理：主表過濾條件寫在 ON 中會導致優化器無法下推謂詞，產生龐大中間結果集；寫在 WHERE 可在掃描主表前就完成過濾，並保持 LEFT JOIN 語義不變。

總結速查卡

將這六大策略內化為開發規範與審核清單，能讓 DB2 系統在高併發、大數據量場景下持續保持優異效能。