提升 DB2 查詢效能的開發者實戰技巧：索引、改寫與工具善用

核心工具介紹

Visual Explain / db2expln / db2exfmt

• Visual Explain：Control Center 或 Command Editor 圖形化顯示存取計畫，節點可查詳細統計（Timerons、CPU、I/O、謂詞選擇度、Buffer 用量）
• db2expln：命令列文字版存取計畫，資訊精簡易讀
• db2exfmt：將 Explain 表格式化輸出詳細文字報告

db2advis：索引建議專家

輸入 SQL 工作負載（含 --#SET FREQUENCY 指定頻率），輸出建議建立的索引、MQT、預估成本降低幅度。依工作負載整體權衡，而非單一查詢。

db2batch：實測基準工具

實際執行 SQL，回報準備/執行/擷取各階段耗時、CPU 時間、回傳筆數。驗證預估成本與實際表現關係。

索引設計實戰技巧

1. 多欄索引欄位順序：謂詞選擇度優先，SELECT 欄位殿後

B+ 樹結構決定：根/中間節點存多欄聯合值。Matching Index Scan（首欄有謂詞）可從根節點直達葉節點；Non-matching Index Scan（首欄無謂詞）需全索引掃描。範例：WHERE city='WASHINGTON' AND cntry_code='USA' → 索引 (city, cntry_code, add_date) 觸發 Matching Scan；若首欄放 add_date 則退化為 Non-matching。

2. 單欄索引 vs 多欄索引：檢視謂詞選擇度

執行計數查詢比較單一謂詞與組合謂詞選擇度。若單欄 city 選擇度接近組合條件，單欄索引 (city) 效能接近多欄索引但體積小得多（5.48MB vs 19.52MB），性價比更高。

3. 避免索引欄位上使用函數

函數單調性不確定，導致優化器無法 Matching Scan，甚至退化為 Table Scan。範例：WHERE UPPER(name)='SMITH' 破壞 name 索引效用。

4. 排序欄位建索引消除 SORT

ORDER BY、DISTINCT、GROUP BY 均觸發排序。索引本身有序，建立對應索引可替代 SORT 為 UNIQUE/INDEX SCAN。無 WHERE 條件查詢獲益最大。聯合索引首欄必須為排序欄位。

5. 善用 INCLUDE 建立唯一索引

主鍵 cust_num + 查詢需 cust_name：CREATE UNIQUE INDEX ... ON t(cust_num) INCLUDE (cust_name)。INCLUDE 欄位僅存在葉節點，非鍵值一部分，避免排序維護，優於聯合索引 (cust_num, cust_name)。僅限唯一索引。

6. 指定索引排序屬性（ASC/DESC/ALLOW REVERSE SCANS）

SELECT MAX(add_date) FROM employee：預設 ASC 索引需全掃描葉節點取最大；DESC 索引首節點即最大值，極快。ALLOW REVERSE SCANS 兼具雙向掃描，維護成本略高。若無法改索引，以範圍條件 WHERE add_date > CURRENT TIMESTAMP - 1 MONTH 限制掃描範圍。

索引與表維護

• REORG INDEXES ALL FOR TABLE t：重組索引消除碎片、恢復邏輯/物理順序一致
• REORG TABLE t INDEX idx：依指定索引順序重組表資料
• RUNSTATS ON TABLE t FOR INDEXES ALL：大量異動後重收統計資訊，避免優化器誤判存取計畫

查詢改寫技巧

1. NOT EXISTS vs NOT IN：視資料量與巢狀複雜度而定

• 子查詢大結果集（65 萬列）：NOT EXISTS (178K timerons) 遠優於 NOT IN (12.6T timerons)
• 子查詢小結果集（100 列）且有巢狀子查詢：NOT IN (917K) 反優於 NOT EXISTS (42K) → NOT EXISTS 受外層邏輯複雜度影響大
• 原則：評估子查詢結果集大小、巢狀深度、現有索引綜合決定

2. 子查詢預先限定掃描範圍 + 利用索引

原查詢：LEFT JOIN order ... WHERE cust.add_date>... OR ord.add_date>... 無索引使用。改寫：WITH tmp AS (SELECT sold_to_cust_num FROM order WHERE add_date>...) SELECT ... WHERE cust.add_date>... OR cust.cust_num IN (SELECT * FROM tmp)。預先縮小 order 掃描範圍並啟用 order_i2(add_date) 索引，成本大幅下降。即使無索引，縮小中間結果集也有效。

3. 調整 JOIN 順序縮小中間結果集

原：customer LEFT JOIN order JOIN contact WHERE contact.mod_date>... 先 LEFT JOIN 產生大中間集。改：customer JOIN contact WHERE ... LEFT JOIN order 先過濾 contact 再 JOIN，中間結果集大幅縮小。注意：若提前 JOIN 帶入過多欄位導致資料總量暴增，需權衡。

4. UDF 封裝複雜邏輯穩定存取計畫

複雜查詢（含視圖、UNION、子查詢）優化器易產生不穩定計畫。建立 Table Function UDF 分步執行：先取鍵值清單，再主查詢。範例：CREATE FUNCTION getCustNum(p_date) RETURNS TABLE(...) RETURN ...，主查詢 WHERE cust_num IN (SELECT * FROM TABLE(getCustNum(...)))。成本從 445K 降至 254K。應用層亦可採分步呼叫策略。

總結

善用 Visual Explain/db2advis/db2batch 觀察實況、理解 B+ 樹索引結構、掌握優化器決策邏輯，配合索引設計六大原則、查詢改寫四大技巧、定期 REORG/RUNSTATS，開發者即可在不調整系統參數前提下，大幅提升 DB2 查詢效能。