DB2 資料庫效能優化六大核心策略：從索引設計到鎖管理的實戰精要

策略一：索引建立時機與 RUNSTATS 搭配鐵律

兩種路徑，各有取捨

黃金法則：無論哪種路徑，RUNSTATS 都是讓優化器「看見」索引的前提條件。

策略二：大表必須置於多節點表空間並配合分區鍵

單節點表空間的資源天花板

• 現有架構：每節點 2 CPU + 4GB 記憶體，32 個節點。
• DIM 表空間為單節點 → 最大只能使用 2 CPU / 4GB。
• 大表若放在 DIM，受限於單節點資源，並行度為 1。

多節點表空間的爆發力

• 建立合適的分區鍵（如 PARTITION BY HASH(id)），將大表分散到 32 個節點。
• 查詢時 32 節點並行掃描/連接，結果匯總到協調節點（通常為節點 0）。
• 理論速度提升可達節點數倍數（受網路、資料傾斜影響）。

索引仍是加速器

即使 32 節點並行，良好的索引設計（叢集索引、覆蓋索引）仍能進一步縮減每節點掃描量，雙重加速。

策略三：大量寫入採用 NOT LOGGED INITIALLY / NONRECOVERABLE

鐵律：啟用 NLI/NONRECOVERABLE 的表，操作失敗 = 表報廢，只能重建。務必在載入成功後立即執行離線備份。

策略四：表級鎖定減少鎖記憶體壓力

• 鎖上限由 LOCKLIST（4KB 頁數）與 MAXLOCKS（單應用佔用上限%）共同控制。
• 行級鎖數量 = 修改列數 × 並發事務數，極易耗盡鎖清單記憶體 → 鎖升級 → 並發度驟降。
• LOCK TABLE tab IN EXCLUSIVE MODE 或 SHARE MODE 可將數萬行鎖縮減為 1 把表鎖。
• 適用：批次更新/刪除大量列、資料匯入、已知無並發衝突的維護窗口。

策略五：臨時表只裝載必要欄位與列，配合索引

• 常見錯誤：INSERT INTO temp SELECT * FROM big_table → 臨時表膨脹 10 倍，後續連接/排序極慢。
• 最佳實務：

DECLARE GLOBAL TEMPORARY TABLE session.temp_x AS
  (SELECT id, name, dept FROM big_table WHERE status='A')
  DEFINITION ONLY;

INSERT INTO session.temp_x
  SELECT id, name, dept FROM big_table WHERE status='A';

CREATE INDEX session.ix_temp_x_dept ON session.temp_x(dept);

• 僅選取必要欄位（投影下推）。
• 在臨時表上建立針對後續查詢謂詞的索引。
• 使用 DECLARED GLOBAL TEMPORARY TABLE（DGTT）隔離會話、自動清理。

策略六：LEFT JOIN 條件撰寫鐵律

錯誤寫法（主表限制寫在 ON 子句）

SELECT a.*, b.col
FROM main a
LEFT JOIN sub b
  ON a.id = b.id
  AND a.status = 'ACTIVE'  -- ❌ 主表過濾條件放在 ON 中
WHERE b.type = 'X';

後果：優化器可能將 LEFT JOIN 轉為 INNER JOIN，或產生錯誤結果集，且無法利用主表索引過濾。

正確寫法

SELECT a.*, b.col
FROM main a
LEFT JOIN sub b
  ON a.id = b.id          -- ✅ 僅連接條件
  AND b.type = 'X'        -- ✅ 右表過濾條件
WHERE a.status = 'ACTIVE'; -- ✅ 主表過濾條件放在 WHERE

• ON 子句僅包含：連接鍵 + 右表（被外連接側）的過濾條件。
• 主表（左表/保留側）的過濾條件**必須**放在 WHERE 子句。
• 這樣優化器能正確推導語意、選擇最佳存取路徑、避免語意錯誤。

總結檢查清單

1. ✅ 大表建索引 + RUNSTATS（插入前/後皆可，但統計必須新）。
2. ✅ 大表放多節點表空間 + 合理分區鍵 + 索引雙重加速。
3. ✅ 大量寫入用 NLI / NONRECOVERABLE，事後備份。
4. ✅ 批次大量修改用表級鎖，釋放鎖記憶體。
5. ✅ 臨時表只裝必要欄位/列 + 針對性索引。
6. ✅ LEFT JOIN：ON 只寫連接鍵 + 右表條件，主表條件進 WHERE。