DB2 中 MQT 的匹配原理及使用技巧

簡介

MQT（Materialized Query Table，物化查詢表） 物化了涉及一個或多個表或昵稱的查詢的預先計算結果。而後續的查詢可以通過全部或部分匹配 MQT，並由 DB2 來補償剩余的查詢功能，從而達到提高查詢性能的目的。本文將會介紹 DB2 中 MQT 匹配的基本原理，並基於此指出如何設計 MQT 從而能使得查詢獲得更高的匹配率從而提高查詢效率。

MQT 匹配原理

MQT 在 OLAP 場景下能夠有效提高復雜查詢響應時間，尤其是有下面幾類數據操作需求的查詢：

在一個或多個維度上聚合數據。

在多個表之間連接數據。

數據來自於一個常見的數據訪問子集—也就是該子集會被頻繁訪問，MQT 能夠避免重復計算。

MQT 對應用程序是完全透明的。MQT 的相關信息已經被整合進 DB2 SQL 編譯器中，它們會判斷是否 MQT 應該被用來響應一個完整查詢或者查詢的一部分。因此，用戶可以在不改變應用程序代碼的情況下，創建和刪除 MQTs，就和創建和刪除索引而不需要更改應用程序一樣。

而如何做到上面的透明性，這是由 DB2 SQL 編譯器的 MQT 匹配算法來完成。如果我們把自己作為 MQT 匹配算法的作者，最容易想到的就是 MQT 需要滿足以下條件才能夠被匹配：MQT 中包含查詢需要的所有行 (Record)；MQT 中包含查詢需要的所有列 (Column)；MQT 中行的冗余度與查詢結果一致。或者通過某種程度的補償能夠達到上述 3 個條件，那麼 MQT 才有可能匹配對應查詢。在 DB2 中也是遵循上述基本原理來進行匹配。其大致步驟如下：1) 在查詢重寫 (Rewrite) 階段，DB2 編譯器會針對目前所有可能被匹配的 MQT 進行分析，並選擇一個最優的 MQT 匹配執行方案和不用 MQT 的執行方案。2) 在查詢優化 (Optimizer) 階段，會計算上述兩種方案的成本，並選擇成本最優的方案作為最終執行方案。需要注意的一點是在第一步中選擇最優 MQT 匹配方案是一種啟發式的選擇 (rule/heuristic based)，並沒有真正計算成本。而且在這個過程中，可能匹配的 MQT 數目越多，需要的匹配過程越復雜，對應的編譯時間越長。所以說並不是 MQT 越多越好，一方面 MQT 會占用存儲空間，同時會增加編譯時間。用戶需要針對性地創建 MQT，保證其能夠真正帶來性能上的提升。而匹配的具體算法就不在這裡詳細闡述。如果讀者有興趣，可以在參考資源 2 中找到具體細節。

根據上面介紹的原理，以下 6 種查詢可以利用 MQT 來提高性能。本文將針對每種查詢舉例加以介紹：

MQT 能夠精確匹配查詢；

查詢結果集是 MQT 的子集；

查詢中連接的表數目多於 MQT；

查詢中連接的表是 MQT 中表的子集，需滿足引用完整性 (Reference Integrity, RI)；

查詢中包含 MQT 中不存在的列，需滿足功能依賴；

查詢對應的聚集級別 (aggregation level) 高於 MQT。

為了對上面的 6 種情況進行詳細介紹，先創建一些示例表以方便通過實例來闡述這些原理。如清單 1 所示，表 Product 和 Customer 是維表 (dimension table)，且分別定義了唯一鍵；表 Sales 是事實表 (fact table)，它通過 PROD_ID 和 CUST_ID 的外鍵約束來保證引用完整性。至於表 Product 上定義的函數依賴 (Functional Dependency)，我們將在後面詳細討論。另外，在實驗的過程中人為的設置了基本表和 MQT 表的統計信息，使得編譯器在選擇查詢計劃總認為使用 MQT 的代價低。這並不影響 MQT 匹配的過程，而且簡化了討論。

清單 1. 創建示例中的表

　CREATE　TABLE　MQTSCH.PRODUCT(PROD_ID　INT　NOT　NULL　UNIQUE,　
　PROD_DESC　VARCHAR(64),　
　CAT_ID　INT　NOT　NULL,　CAT_DESC　VARCHAR(64),　
　GROUP_ID　INT　NOT　NULL,　GROUP_DESC　VARCHAR(64),　
　CONSTRAINT　FD1　CHECK　
　(CAT_DESC　DETERMINED　BY　CAT_ID)　
　NOT　ENFORCED　ENABLE　QUERY　OPTIMIZATION,　
　CONSTRAINT　FD2　CHECK　
　(GROUP_ID　DETERMINED　BY　CAT_ID)　
　NOT　ENFORCED　ENABLE　QUERY　OPTIMIZATION　
　);　
　
　CREATE　TABLE　MQTSCH.CUSTOMER(CUST_ID　INT　NOT　NULL　UNIQUE,　
　CUST_NAME　VARCHAR(50),　
　CUST_ADDRESS　VARCHAR(100));　
　
　CREATE　TABLE　MQTSCH.SALES(PROD_ID　INT　NOT　NULL　REFERENCES　MQTSCH.PRODUCT(PROD_ID),　
　CUST_ID　INT　NOT　NULL　REFERENCES　MQTSCH.CUSTOMER(CUST_ID),　
　SALE_DATE　DATE　NOT　NULL,　
　AMOUNT　DECIMAL(9,2)　NOT　NULL);　
　
　runstats　on　table　MQTSCH.CUSTOMER;　
　runstats　on　table　MQTSCH.PRODUCT;　
　runstats　on　table　MQTSCH.SALES;　
　update　syscat.tables　set　card=20　where　tabname='CUSTOMER';　
　update　syscat.tables　set　card=200　where　tabname='PRODUCT';　
　update　syscat.tables　set　card=2000　where　tabname='SALES';　
　
　set　current　refresh　age=any;