|
簡介
MQT（Materialized Query Table，物化查詢表） 物化了涉及一個或多個表或昵稱的查詢的預(yù)先計算結(jié)果。而后續(xù)的查詢可以通過全部或部分匹配 MQT，并由 DB2 來補(bǔ)償剩余的查詢功能，從而達(dá)到提高查詢性能的目的。本文將會介紹 DB2 中 MQT 匹配的基本原理，并基于此指出如何設(shè)計 MQT 從而能使得查詢獲得更高的匹配率從而提高查詢效率。
MQT 匹配原理
MQT 在 OLAP 場景下能夠有效提高復(fù)雜查詢響應(yīng)時間，尤其是有下面幾類數(shù)據(jù)操作需求的查詢：
在一個或多個維度上聚合數(shù)據(jù)。
在多個表之間連接數(shù)據(jù)。
數(shù)據(jù)來自于一個常見的數(shù)據(jù)訪問子集—也就是該子集會被頻繁訪問，MQT 能夠避免重復(fù)計算。
MQT 對應(yīng)用程序是完全透明的。MQT 的相關(guān)信息已經(jīng)被整合進(jìn) DB2 SQL 編譯器中，它們會判斷是否 MQT 應(yīng)該被用來響應(yīng)一個完整查詢或者查詢的一部分。因此，用戶可以在不改變應(yīng)用程序代碼的情況下，創(chuàng)建和刪除 MQTs，就和創(chuàng)建和刪除索引而不需要更改應(yīng)用程序一樣。
而如何做到上面的透明性，這是由 DB2 SQL 編譯器的 MQT 匹配算法來完成。如果我們把自己作為 MQT 匹配算法的作者，最容易想到的就是 MQT 需要滿足以下條件才能夠被匹配：MQT 中包含查詢需要的所有行 (Record)；MQT 中包含查詢需要的所有列 (Column)；MQT 中行的冗余度與查詢結(jié)果一致。或者通過某種程度的補(bǔ)償能夠達(dá)到上述 3 個條件，那么 MQT 才有可能匹配對應(yīng)查詢。在 DB2 中也是遵循上述基本原理來進(jìn)行匹配。其大致步驟如下：1) 在查詢重寫 (Rewrite) 階段，DB2 編譯器會針對目前所有可能被匹配的 MQT 進(jìn)行分析，并選擇一個最優(yōu)的 MQT 匹配執(zhí)行方案和不用 MQT 的執(zhí)行方案。2) 在查詢優(yōu)化 (Optimizer) 階段，會計算上述兩種方案的成本，并選擇成本最優(yōu)的方案作為最終執(zhí)行方案。需要注意的一點(diǎn)是在第一步中選擇最優(yōu) MQT 匹配方案是一種啟發(fā)式的選擇 (rule/heuristic based)，并沒有真正計算成本。而且在這個過程中，可能匹配的 MQT 數(shù)目越多，需要的匹配過程越復(fù)雜，對應(yīng)的編譯時間越長。所以說并不是 MQT 越多越好，一方面 MQT 會占用存儲空間，同時會增加編譯時間。用戶需要針對性地創(chuàng)建 MQT，保證其能夠真正帶來性能上的提升。而匹配的具體算法就不在這里詳細(xì)闡述。如果讀者有興趣，可以在參考資源 2 中找到具體細(xì)節(jié)。
根據(jù)上面介紹的原理，以下 6 種查詢可以利用 MQT 來提高性能。本文將針對每種查詢舉例加以介紹：
MQT 能夠精確匹配查詢；
查詢結(jié)果集是 MQT 的子集；
查詢中連接的表數(shù)目多于 MQT；
查詢中連接的表是 MQT 中表的子集，需滿足引用完整性 (Reference Integrity, RI)；
查詢中包含 MQT 中不存在的列，需滿足功能依賴；
查詢對應(yīng)的聚集級別 (aggregation level) 高于 MQT。
為了對上面的 6 種情況進(jìn)行詳細(xì)介紹，先創(chuàng)建一些示例表以方便通過實(shí)例來闡述這些原理。如清單 1 所示，表 Product 和 Customer 是維表 (dimension table)，且分別定義了唯一鍵；表 Sales 是事實(shí)表 (fact table)，它通過 PROD_ID 和 CUST_ID 的外鍵約束來保證引用完整性。至于表 Product 上定義的函數(shù)依賴 (Functional Dependency)，我們將在后面詳細(xì)討論。另外，在實(shí)驗(yàn)的過程中人為的設(shè)置了基本表和 MQT 表的統(tǒng)計信息，使得編譯器在選擇查詢計劃總認(rèn)為使用 MQT 的代價低。這并不影響 MQT 匹配的過程，而且簡化了討論。
清單 1. 創(chuàng)建示例中的表
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;
MQT 能夠精確匹配查詢
這種情況是最容易理解的。當(dāng) MQT 能夠精確匹配查詢時，通常情況下，從 MQT 中獲取數(shù)據(jù)的性能會優(yōu)于執(zhí)行相應(yīng)查詢，故選擇 MQT 的執(zhí)行方案通常會勝出。清單 2 中給出一個示例。其中 MQT SALES_PROD 基于外鍵連接事實(shí)表 Sales 與維表 Product。而查詢則是同樣的 Join 操作。這時，MQT 能匹配這個查詢。在清單 2 中可以看到 MQT 和查詢的詳細(xì)內(nèi)容，并且打印出的執(zhí)行計劃和拓展診斷信息明確的顯示了 SALES_PROD 在查詢匹配時被利用了。
清單 2. 精確的 MQT 匹配
--MQT definition: join for SALES and PRODUCT
CREATE TABLE MQTSCH.SALES_PROD AS
(SELECT P.PROD_ID, PROD_DESC, AMOUNT
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
refresh table MQTSCH.SALES_PROD;
--artifical statistics
runstats on table MQTSCH.SALES_PROD;
update syscat.tables set card=10 where tabname='SALES_PROD';
--collect the explain information
DELETE FROM EXPLAIN_INSTANCE;
explain plan for SELECT P.PROD_ID, PROD_DESC, AMOUNT
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID;
!db2exfmt -1 -d mqtdb -o join.plan;
下面是 join.plan 打印出的執(zhí)行計劃和診斷信息：
Access Plan:
-----------
Total Cost: 10.3414
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
10
TBSCAN
( 2)
10.3414
1
|
10
TABLE: MQTSCH
SALES_PROD
Q1
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "MQTSCH ".
"SALES_PROD".
Diagnostic Identifier: 2
Diagnostic Details: EXP0149W The following MQT was used (from those
considered) in query matching: "MQTSCH ".
"SALES_PROD".
查詢結(jié)果集是 MQT 的子集
這種情況也很容易理解。當(dāng)查詢結(jié)果集是 MQT 的子集時，這意味著查詢需要的行與列在 MQT 中都能找到，而 DB2 只需要在對應(yīng) MQT 上執(zhí)行剩余的謂詞 (predicate) 及計算 (head expression) 即可。如圖 2 所示，這種 MQT 只需要被計算一次就可以被多次重用。清單 3 則給出了該場景的一個具體例子。
圖 1. 查詢結(jié)果集是 MQT 子集示意圖
查看原圖（大圖）
清單 3. 查詢結(jié)果集是 MQT 子集匹配
--MQT definition: join for SALES and PRODUCT
CREATE TABLE MQTSCH.SALES_PROD AS
(SELECT P.PROD_ID, PROD_DESC, AMOUNT
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
refresh table MQTSCH.SALES_PROD;
--artifical statistics
runstats on table MQTSCH.SALES_PROD;
update syscat.tables set card=10 where tabname='SALES_PROD';
--collect the explain information
DELETE FROM EXPLAIN_INSTANCE;
explain plan for SELECT P.PROD_ID, PROD_DESC, AMOUNT
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID and AMOUNT > 10000;
!db2exfmt -1 -d mqtdb -o joinsub.plan;
下面是 join_sub.plan 打印出的執(zhí)行計劃和診斷信息。有一點(diǎn)需要注意的是清單 2 與清單 3 中的 TBSCAN 并不完全相同。清單 3 中的 TBSCAN 包含謂詞 (10000 < Q1.AMOUNT)。
Access Plan:
-----------
Total Cost: 10.5194
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
3.33333
TBSCAN
( 2)
10.5194
1
|
10
TABLE: MQTSCH
SALES_PROD
Q1
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "MQTSCH ".
"SALES_PROD".
Diagnostic Identifier: 2
Diagnostic Details: EXP0149W The following MQT was used (from those
considered) in query matching: "MQTSCH ".
"SALES_PROD".
查詢中連接的表數(shù)目多于 MQT
根據(jù)前面介紹的 MQT 匹配原理，這種情況成立的前提是 MQT 完成所有連接后得到的結(jié)果集需要是查詢中對應(yīng)表完成連接后結(jié)果集的超集。如果 MQT 包含查詢中沒有的謂詞并且過濾掉一部分結(jié)果集，則該 MQT 無法進(jìn)行匹配。
圖 2. 查詢中連接的表數(shù)目多于 MQT 匹配示意圖
查看原圖（大圖）
清單 4. 查詢中連接的表數(shù)目多于 MQT 匹配
--MQT definition: join for SALES and PRODUCT
CREATE TABLE MQTSCH.SALES_PROD AS
(SELECT P.PROD_ID, PROD_DESC, AMOUNT, CUST_ID
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
refresh table MQTSCH.SALES_PROD;
--artifical statistics
runstats on table MQTSCH.SALES_PROD;
update syscat.tables set card=10 where tabname='SALES_PROD';
--collect the explain information
DELETE FROM EXPLAIN_INSTANCE;
explain plan for SELECT P.PROD_ID, PROD_DESC, AMOUNT, C.CUST_ID, CUST_NAME
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S, MQTSCH.CUSTOMER C
WHERE P.PROD_ID = S.PROD_ID
AND S.CUST_ID = C.CUST_ID;
!db2exfmt -1 -d mqtdb -o join_rejoin.plan;
下面是 join_rejoin.plan 打印出的執(zhí)行計劃和診斷信息：
Access Plan:
-----------
Total Cost: 18.8629
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
20
^NLJOIN
( 2)
18.8629
2
/------+-------\
1 20
TBSCAN FETCH
( 3) ( 4)
9.72148 36.0967
1 4
| /---+----\
1 20 20
TABLE: MQTSCH IXSCAN TABLE: MQTSCH
SALES_PROD ( 5) CUSTOMER
Q2 35.3323 Q1
4
|
20
INDEX: SYSIBM
SQL100124231518010
Q1
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0022W Index has no statistics. The index
"SYSIBM "."SQL100124231518010" has not had
runstats run on it. This can lead to poor
cardinality and predicate filtering estimates.
Diagnostic Identifier: 2
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "MQTSCH ".
"SALES_PROD".
Diagnostic Identifier: 3
Diagnostic Details: EXP0149W The following MQT was used (from those
considered) in query matching: "MQTSCH ".
"SALES_PROD".
查詢中連接的表是 MQT 中的子集，需滿足 RI
前面提到，MQT 匹配的前提是 MQT 僅包含且正好包含查詢所需要的數(shù)據(jù)行。因此，如果一個 MQT 中連接 (Join) 的表多于查詢中的表，一般不能用這個 MQT 來匹配，因?yàn)轭~外的 Join 操作會影響 MQT 所包含的行及對應(yīng)的冗余度。然而，如果額外的 Join 操作是基于 RI（引用完整性）的，那么它不會增加或刪除任何行，編譯器能夠利用這個事實(shí)在上述情況中匹配 MQT。這種基于 RI 的 Join 操作在事實(shí)表和維表之間是很常見的。
通過例子來說明。如圖 4 所示，SALES_PROD_CUST 這個 MQT 基于外鍵連接了事實(shí)表 Sales 與兩個維表 Product 和 Customer。而查詢則是在 Sales 和 Product 的 Join 操作。這時，MQT 能匹配這個查詢。 在清單 5 中可以看到 MQT 和查詢的詳細(xì)內(nèi)容，并且打印出的執(zhí)行計劃和拓展診斷信息明確的顯示了 SALES_PROD_CUST 在查詢匹配時被利用了。
圖 3. 含有額外 RI-Join 時的 MQT 匹配示意圖
查看原圖（大圖）
清單 5. 含有額外 RI-Join 時的 MQT 匹配
--MQT definition: with extra RI-Joins
CREATE TABLE MQTSCH.SALES_PROD_CUST AS
(SELECT P.PROD_ID, CAT_DESC, AMOUNT, C.CUST_ID, CUST_NAME
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S, MQTSCH.CUSTOMER C
WHERE P.PROD_ID = S.PROD_ID
AND S.CUST_ID = C.CUST_ID )
DATA INITIALLY DEFERRED REFRESH DEFERRED;
refresh table MQTSCH.SALES_PROD_CUST;
--artifical statistics
runstats on table MQTSCH.SALES_PROD_CUST;
update syscat.tables set card=10 where tabname='SALES_PROD_CUST';
--collect the explain information
DELETE FROM EXPLAIN_INSTANCE;
explain plan for SELECT P.PROD_ID, CAT_DESC, AMOUNT
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID;
!db2exfmt -1 -d mqtdb -o join_redud.plan;
下面是 join_redun.plan 打印出的執(zhí)行計劃和診斷信息：
Access Plan:
-----------
Total Cost: 10.3414
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
10
TBSCAN
( 2)
10.3414
1
|
10
TABLE: MQTSCH
SALES_PROD_CUST
Q1
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "MQTSCH ".
"SALES_PROD_CUST".
Diagnostic Identifier: 2
Diagnostic Details: EXP0149W The following MQT was used (from those
considered) in query matching: "MQTSCH ".
"SALES_PROD_CUST".
查詢中包含 MQT 中不存在的列，需滿足功能依賴
在開始介紹接下來的 MQT 匹配原理之前，有必要先簡單介紹一下功能依賴 (Functional Dependency) 這個概念。Y 功能依賴于 X，是指 Y 的值由 X 決定，即每個 X 的值精確的對應(yīng)著一個 Y 的值，記作 X -> Y。
根據(jù)定義，關(guān)系表上的所有列都功能依賴于主鍵或唯一鍵。例如，清單 1 中 Product 表，PROD_ID ->CAT_ID。對于非鍵列之間的功能依賴，DB2 通過定義一個參考約束 (informational constraint) 來實(shí)現(xiàn)。如清單 1 中在 Product 表上 FD1 和 FD2，分別定義了 CAT_ID -> CAT_DESC 和 CAT_ID -> GROUP_ID 這兩個函數(shù)依賴。在參考約束定義中：
關(guān)鍵字 DETERMETED BY 準(zhǔn)確地表達(dá)了函數(shù)依賴的含義；
NOT ENFORCED 表示在執(zhí)行增刪改時 DB2 并不驗(yàn)證數(shù)據(jù)來保證約束的完整性；
ENABLE QUERY OPTIMIZATION 告訴編譯器可以利用這個函數(shù)依賴來重寫和優(yōu)化查詢。
由于 DB2 并不強(qiáng)制函數(shù)依賴的這種約束的完整性，根據(jù)這個函數(shù)依賴優(yōu)化的查詢結(jié)果可能是錯誤的，因此，需要注意函數(shù)依賴的定義和維護(hù)。
繼續(xù) MQT 匹配的討論。我們知道，MQT 匹配時，要求查詢中需要的列都能從 MQT 中找到。那么如果查詢中包含 MQT 不存在的列呢？函數(shù)依賴讓這種匹配也變成可能。DB2 根據(jù)這些函數(shù)依賴重寫查詢，通過 MQT 和基本表的 re-join 來獲得 MQT 缺少的列。
在清單 6 的例子中，MQT SALES_BY_CAT 的定義包含列 CAT_ID，統(tǒng)計每類產(chǎn)品的銷售總量，然而查詢卻希望獲得 CAT_DESC 和銷售總量，而 CAT_DESC 不在 MQT 中。如果沒有函數(shù)依賴，這個 MQT 是不能匹配的。而正是 Product 上的函數(shù)依賴 CAT_ID -> CAT_DESC 讓這個 MQT 的匹配變成可能。
拓展診斷信息 (Extended Diagnostic Information) 段揭示了編譯器的整個處理過程：診斷信息 1 中 EXP0073W 說明由于查詢中包含 MQT 沒有的列，這個 MQT 不能匹配；診斷 3 中 EXP0149W 表示當(dāng)編譯器收集到函數(shù)依賴后，用 MQT 匹配了這個查詢。優(yōu)化后的語句和執(zhí)行計劃一致的顯示 MQT 的匹配以及 re-join 操作的生成。
由于 CAT_ID 不是具有唯一性的鍵，優(yōu)化后的語句中利用 DISTINCT 來去除重復(fù)，對應(yīng)了查詢計劃的 SORT 操作。一般情況下，這個 DISTINCT 是作用在維表上，開銷很小。
清單 6. 含有額外 RI-Join 時的 MQT 匹配
--MQT missing columns, with FD
CREATE TABLE MQTSCH.SALES_BY_CAT AS
(SELECT CAT_ID, SUM(AMOUNT) AS TOTAL, COUNT(*) AS CNT
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID
GROUP BY CAT_ID )
DATA INITIALLY DEFERRED REFRESH IMMEDIATE;
refresh table MQTSCH.SALES_BY_CAT;
runstats on table MQTSCH.SALES_BY_CAT;
update syscat.tables set card=10 where tabname='SALES_BY_CAT';
--collect the explain information
DELETE FROM EXPLAIN_INSTANCE;
explain plan for SELECT CAT_DESC, SUM(AMOUNT)
FROM MQTSCH.PRODUCT P, MQTSCH.SALES S
WHERE P.PROD_ID = S.PROD_ID
GROUP BY CAT_ID, CAT_DESC;
!db2exfmt -1 -d mqtdb -o fd.plan;
下面是 fd.plan 打印出的執(zhí)行計劃和診斷信息：
Optimized Statement:
-------------------
SELECT Q3.CAT_DESC AS "CAT_DESC", Q1.TOTAL
FROM MQTSCH.SALES_BY_CATAS Q1,
(SELECT DISTINCTQ2.CAT_DESC, Q2.CAT_ID
FROM MQTSCH.PRODUCT AS Q2) AS Q3
WHERE (Q1.CAT_ID = Q3.CAT_ID)
Access Plan:
-----------
Total Cost: 98.6553
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
0.4
HSJOIN
( 2)
98.6553
10
/---+----\
10 1
TBSCAN TBSCAN
( 3) ( 4)
74.9814 23.5881
9 1
| |
10 1
TABLE: MQTSCH SORT
SALES_BY_CAT ( 5)
Q1 23.5366
1
|
200
TBSCAN
( 6)
23.4286
1
|
200
TABLE: MQTSCH
PRODUCT
Q2
Extended Diagnostic Information:
--------------------------------
Diagnostic Identifier: 1
Diagnostic Details: EXP0073W The following MQT or statistical view was
not eligible because one or more data filtering
predicates from the query could not be matched with
the MQT: "MQTSCH "."SALES_BY_CAT".
Diagnostic Identifier: 2
Diagnostic Details: EXP0148W The following MQT or statistical view was
considered in query matching: "MQTSCH ".
"SALES_BY_CAT".
Diagnostic Identifier: 3
Diagnostic Details: EXP0149W The following MQT was used (from those
considered) in query matching: "MQTSCH ".
"SALES_BY_CAT".
|