商業智能環境下 DB2 查詢效能六大優化策略：從 MQT 到表分區與 MDC 組合拳

BI 查詢特徵與優化目標

• 大量掃描、多表連接、複雜聚合
• 資料分佈不均、資料傾斜
• 目標：減少 I/O、避免資料重分發、預計算聚合、並行化執行

測試環境基線

• 平台：Windows DB2 9 + DPF (Database Partitioning Feature)
• 星型綱要：SALES_FACT (2006 年銷售明細) + DATE_DIM/PRODUCT_DIM/STORE_DIM
• 分區策略：維度表單分區 (節點 0)；事實表 4 節點哈希分區
• 緩衝池：IBMDEFAULTBP 1000 頁 (4KB) 共享
• 核心查詢：二月 vs 十一月 10 家門市銷售比較（外連接）

策略一：建立參考完整性約束（RI Constraints）

• 優化器利用 FK/PK 關係推導連接基數、消除冗餘連接、啟用連接消除
• 語法：ALTER TABLE fact ADD CONSTRAINT fk_store FOREIGN KEY (store_id) REFERENCES store_dim (store_id) NOT ENFORCED ENABLE QUERY OPTIMIZATION
• NOT ENFORCED 避免載入時檢查開銷；ENABLE QUERY OPTIMIZATION 開放優化器使用

策略二：MQT 複製表跨分區實現並置連接

• 問題：維度表在節點 0，事實表分區鍵非維度鍵 → 連接需廣播/重分發
• 方案：在事實表分區組建立維度表 MQT 副本，分區鍵對齊事實表連接鍵
• 優化器自動重寫查詢走本地 MQT，消除網路傳輸
• 維護：REFRESH TABLE 或 SET INTEGRITY ... IMMEDIATE CHECKED

策略三：多維叢集 (MDC, Multi-Dimensional Clustering)

• 建表指定 ORGANIZE BY DIMENSIONS (month, store_id, product_id)
• 資料按維度組合塊 (Block) 儲存，區塊索引極小（僅記錄區塊 ID）
• 優勢：
  • 篩選維度查詢直接定位區塊，跳過大量掃描
  • 自動維護區塊索引，無需 REORG
  • 載入/刪除效能優於傳統索引
• 適用：低基數維度、已知篩選模式

策略四：表分區 (Table Partitioning, DB2 9 新功能)

• 範圍分區：PARTITION BY RANGE (sale_date) (START '2006-01-01' END '2006-12-31' EVERY 1 MONTH)
• 優勢：
  • 資料剪枝：查詢特定月份僅掃描對應分區
  • 滾動載入/歸檔：ALTER TABLE ... DETACH/ATTACH PARTITION 秒級元資料操作
  • 每分區獨立索引、獨立表空間、獨立壓縮
• 分區鍵選擇：時間、地區等自然範圍欄位

策略五：表分區 + MDC 雙重組織 (Range + MDC)

• 語法：PARTITION BY RANGE (sale_date) ... ORGANIZE BY DIMENSIONS (store_id, product_id)
• 效果疊加：
  • 粗粒度：分區剪枝時間範圍
  • 細粒度：區塊索引定位門市/產品組合
• 儲存節省：區塊索引比傳統複合索引小 1-2 個數量級

策略六：MQT 預計算聚合結果

• 建立彙總 MQT：CREATE TABLE sales_mth_store AS (SELECT month, store_id, SUM(qty), SUM(amt) FROM sales_fact GROUP BY month, store_id) DATA INITIALLY DEFERRED REFRESH DEFERRED
• 查詢月度門市報表直接命中 MQT，避免掃描億級事實表
• 定期 REFRESH TABLE sales_mth_store（可增量）

效能對比摘要（測試環境）

實施路線圖建議

1. 基線：收集統計、建立 RI Constraints、基準測試
2. 分區：核心事實表套用表分區（時間）
3. MDC：高頻篩選維度疊加 MDC
4. MQT：熱點報表建立聚合 MQT；跨分區連接建立複製 MQT
5. 持續：監控 db2pd -dyn、EXPLAIN 驗證剪枝/重寫生效

總結

BI 環境優化無銀彈，**組合拳才是王道**：表分區解決「掃描多少資料」，MDC 解決「如何快速定位區塊」，MQT 解決「跨分區連接」與「重複聚合」。在 DPF 架構下，分區鍵對齊與並置連接是前提；建立 RI Constraints 成本最低收益最高，必做動作。依查詢工作負載熱度圖，分層套用上述六策略，可將 TB 級星型查詢從小時級壓縮至秒級。