利用物化查詢表 (MQT) 提升 WebSphere Information Integrator 聯邦查詢性能

MQT 概念與聯邦環境優勢

物化查詢表 (MQT) 預先計算並儲存涉及一或多個表/昵稱查詢的結果。後續查詢若匹配 MQT 全部或部分，優化器可改用 MQT 滿足查詢。聯邦環境下優勢更明顯：

• 避免跨網絡即時查詢遠程源，減少延遲。
• 遠程源不可用時，若優化器選用 MQT，查詢仍可執行。
• 為優化器提供精確統計信息，輔助過濾因子估算。

啟發性範例

總部紐約，數據倉庫聖何塞。銷售經理常查加州 2005 年按城市彙總銷售，及變體（特定城市、銷售額門檻）。數據可容忍數小時/天過期。定義 MQT：

CREATE TABLE AGGREGATE_SALES (CITY, TOTAL_SALES) AS
(SELECT CITY, SUM(PRICE)
 FROM DIVISIONAL_SALES
 WHERE STATE='CA' AND TRANSACTION_DATE BETWEEN '01/01/2005' AND '12/31/2005'
 GROUP BY CITY)
DATA INITIALLY DEFERRED REFRESH DEFERRED
ENABLE QUERY OPTIMIZATION
MAINTAINED BY SYSTEM;

建立並填充後，上述查詢及變體自動路由至 MQT。

優化器使用 MQT 的條件

• MQT 應用的謂詞集合包含用戶查詢謂詞的相同集合或子集。
• MQT 選擇的欄位包含用戶查詢選擇欄位的相同集合或超集。
• MQT 數據新鮮度 (REFRESH AGE) 與優化級別 (2 或 5+) 滿足要求。

創建與使用 MQT 完整步驟

1. Design Advisor 推薦 MQT：db2advis -d mydb -i my_queries.sql -m M -o my_rec_mqts.sql -u -k OFF。輸出含建立 MQT、刷新、建索引的 DDL。
2. 建立 MQT：使用 CREATE TABLE ... AS (SELECT ...) DATA INITIALLY DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION MAINTAINED BY SYSTEM/USER/FEDERATED_TOOL。子句含義：DATA INITIALLY DEFERRED 建表不填數據；REFRESH DEFERRED 手動刷新（昵稱必須用此）；ENABLE QUERY OPTIMIZATION 允許優化器使用；REPLICATED 於 DPF 環境全分區副本。
3. 用戶維護 MQT 脫離 check-pending：SET INTEGRITY FOR AGGREGATE_SALES ALL IMMEDIATE UNCHECKED。
4. 填充數據：
   • 系統維護：REFRESH TABLE AGGREGATE_SALES（全量刷新，可排程）。
   • 用戶維護：INSERT INTO ... SELECT ... 或 LOAD FROM CURSOR INSERT/REPLACE INTO ...（大數據量 LOAD 更快）。
   • Federated_Tool 維護：自動復制計劃同步數據。
5. 建立索引：CREATE UNIQUE INDEX ... ON mqt (key)（系統維護 MQT 不允許唯一索引）。
6. 定義昵稱信息約束：ALTER NICKNAME ... ADD CONSTRAINT ... NOT ENFORCED ENABLE QUERY OPTIMIZATION，輔助優化器消除連接、匹配 MQT。
7. 收集統計信息：RUNSTATS ON TABLE schema.mqt WITH DISTRIBUTION AND DETAILED INDEXES ALL。
8. 啟用優化器考慮 MQT：特殊暫存器 SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION ALL 或資料庫配置 DFT_MTTB_TYPES。
9. 設定 REFRESH AGE：SET CURRENT REFRESH AGE ANY 或 DFT_REFRESH_AGE，非零值才考慮 REFRESH DEFERRED MQT。
10. 驗證是否使用 MQT：db2exfmt 輸出中出現 MQT 物件、優化後 SQL 引用 MQT 而非昵稱、計劃顯示 MQT 掃描、物件列表中昵稱標註 (reference only)。

限制與故障診斷

主要限制

• 分區資料庫不支援引用昵稱的系統維護 MQT。
• 非關係昵稱 (Blast, HMMER, XML) 有特定謂詞/層級限制。
• Design Advisor 不推薦用戶維護 MQT；分區環境不可用；不考慮工作負載外的更新性能。

查詢未用 MQT 排查清單

• CURRENT REFRESH AGE = ANY？
• MQT 已 ENABLE QUERY OPTIMIZATION？
• CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION 包含 MQT 類型？
• MQT 參考表是否為查詢表子集？
• MQT 謂詞是否為查詢謂詞子集？
• 查詢是否動態編譯？靜態 SQL 不考慮 MQT。
• MQT 及索引已 RUNSTATS？
• MQT 過多導致編譯時間過長？
• 查詢隔離級別是否高於建立 MQT 時？

性能實測 (TPC-H 改造)

4GB TPC-H 數據分佈於兩物理機，第三機跑聯邦服務器。Design Advisor 推薦 8 個 MQT，經本地謂詞定制後，8 條查詢總耗時從 560 秒降至 76 秒，提升 83%。單條查詢最高提升 99%（Q5, Q18, Q19）。

結束語

MQT 是聯邦環境性能優化的強大工具，無需修改應用，優化器透明決策。識別合適工作負載、定義填充 MQT，可獲顯著性能提升。