DB2 索引建立核心原則與實戰技巧完整指南

索引是關聯式資料庫效能優化的關鍵機制。DB2 採用 B+ 樹結構實作索引，透過有序資料結構快速定位資料，避免全表掃描帶來的大量 I/O 操作。然而索引並非越多越好，需遵循核心原則才能發揮最大效益。

索引建立基本語法

CREATE INDEX <索引名稱> ON <表名稱> (<欄位1>, <欄位2>, ...)

七大核心建立原則

1. 針對「讀多寫少」的表建立索引

索引加速查詢，但每次 INSERT/UPDATE/DELETE 都需同步維護索引結構。頻繁寫入的表建立過多索引會嚴重拖慢寫入效能。建立前請評估讀寫比例。

2. 避免重複與冗餘索引

若已存在 INDEX_IDX1 (COL1, COL2, COL3)，再建立 INDEX_IDX2 (COL1, COL2) 通常無實質效益，反而浪費儲存空間與維護成本。DB2 優化器會自動利用前綴匹配特性。

3. 適用於大量資料與條件查詢的表

小型表（如系統設定表、編碼表）資料量少且常全表掃描，建立索引效益有限。索引最適合：資料量大、查詢常帶 WHERE 條件、需篩選少量資料的情境。

4. 善用唯一索引保證資料完整性

CREATE UNIQUE INDEX <索引名> ON <表名> (<欄位名>)

主鍵會隱式建立唯一索引，切勿在主鍵欄位重複建立索引浪費資源。

5. 控制索引總量，避免優化器負擔過重

DB2 路徑存取優化器會評估所有可用索引產生存取路徑並計算成本。索引過多會指數級增加編譯時間，延長 SQL 準備階段。

6. 利用索引有序特性避免排序操作

建立唯一索引後，DB2 掃描索引時會依索引順序輸出結果，可省略 ORDER BY 排序成本，提升查詢效能。

7. 高重複率欄位不宜建立索引

若欄位值分布極不均勻（如 90% 為 'Y'、10% 為 'N'），索引選擇率極低，優化器傾向全表掃描。實務經驗建議：欄位基數應達總筆數 0.5% 以上（選擇率 < 0.005）才具備建立價值。

實戰驗證範例

-- 建立測試表與索引
CREATE TABLE TEST1 (NO INT NOT NULL, NAME CHAR(5));
CREATE INDEX TEST1_IDX_1 ON TEST1 (NO);

-- 插入 100 筆測試資料
INSERT INTO TEST1 
SELECT ROW_NUMBER() OVER(), 
       CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48))||CHR(INT(RAND()*75+48))
FROM SYSCAT.COLUMNS T1 JOIN SYSCAT.COLUMNS T2 ON T1.COLNAME!=T2.COLNAME
FETCH FIRST 100 ROWS ONLY;

-- 驗證索引使用情況
SELECT * FROM TEST1 WHERE NO = 50;

執行 db2exfmt 分析存取計畫，確認是否使用 IXSCAN（索引掃描）而非 TBSCAN（表掃描）。

進階建議

• 定期執行 RUNSTATS 更新統計資訊，確保優化器決策準確
• 使用 db2pd -d -index 監控索引使用頻率，移除長期未使用的索引
• 考慮包含索引 避免回表查詢
• 分區表環境下評估局部與全域索引策略