MQT介紹

DB2 MQT(Materialized Query Table)物化查詢表是提高查詢效能的有效手段，廣泛應用在資料倉儲和大數量的報表查詢系統中。MQT也是高階DBA認證的一個重要考點，本文從應用場景入手，介紹MQT的語法，並通過例項演示具體用法。

MQT，或者叫summary table，stage table，提供了強有力的方法來提高複雜查詢的響應時間，特別是對包含如下操作的查詢：對一個或多個維度的聚合操作，如SUM,Count等；在一組表之間的join和聚合操作。MQT的基本原理是對以上特別需要耗費資源的操作事先計算出來並儲存到單獨的表裡，這樣當查詢的時候優化器就會引導相關查詢到MQT中查詢，由於MQT表裡面已經包含了需要聚集和join的資料，資料量有了大幅度減少，因此能夠大大提高效能。

搭建示例 

為了演示方便，我們建立一個Customer_Order表，裡面包含了10年的客戶訂單的交易資料，大概30萬行記錄。（其中order_status有'Y'和'N'兩種狀態，比例為1：4， trans_dt的範圍為1999-01-01到2008-12-29日，表中資料都是根據某個範圍自動生成的）

--首先建立一個DMS表空間

CREATE TABLESPACE tbsp1 managed by database using (file 'd:\db2\dms\cont' 200M ) ;

--建立customer_order基本表

CREATE TABLE test.customer_order (
 order_id int not null,
 customer_id int not null,
 product_name varchar(10),
 amount int,
 order_status char(1),
 trans_dt date
  )
 IN tbsp1 ;

--在trans_dt交易日期上建立索引

CREATE INDEX test.cust_order_idx ON tpcd.customer_order(trans_dt) CLUSTER;

 

--向基本表中插入300000行資料，可根據需要自己調整
insert into test.customer_order

with temp(counter, order_id, customer_id,product_name,amount, order_status,trans_dt) as
( values(1, 1, int(rand()*1000), 'product' concat char(mod(int(rand()*1000),5)) , mod(int(rand()*10000),999), case 1 when 1 then 'Y' else 'N' end, date(rtrim(char(mod(int(rand()*1000),10)+1999)) concat '-' concat rtrim(char(mod(int(rand()*1000),12)+1)) concat '-' concat rtrim(char(mod(int(rand()*1000),28)+1) )) )
  union all
  select counter+1, counter+1, int(rand()*1000), 'product' concat char(mod(int(rand()*1000),5)) , mod(int(rand()*1000),25), case mod(counter+1,5) when 1 then 'Y' else 'N' end, date(rtrim(char(mod(int(rand()*1000),10)+1999)) concat '-' concat rtrim(char(mod(int(rand()*1000),12)+1)) concat '-' concat rtrim(char(mod(int(rand()*1000),28)+1) ))  from temp
  where counter+1<300001
)
select order_id, customer_id,product_name,amount, order_status,trans_dt from temp;

接下來我們演示對某些複雜查詢，不採用MQT和採用MQT的效能影響。

 

不採用MQT的查詢

現在假設某個應用要經常對客戶訂單的數量和狀態進行分組統計，但只查詢2007年度的資料。最常用的是如下兩條SQL語句，第一條是根據交易時間分組統計，第二條是根據訂單狀態進行分組統計。

SELECT SUM(AMOUNT), TRANS_DT FROM test.CUSTOMER_ORDER WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007' GROUP BY TRANS_DT

SELECT SUM(AMOUNT), order_STATUS FROM test.CUSTOMER_ORDER WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007' GROUP BY order_STATUS

執行這兩條SQL，並收集explain的資訊，發現獲得336行資料返回，其Cost卻高達4040(timeron)。詳細資料可參看表1的對比（由於sohu部落格不支援bmp圖片，儲存成jpg格式有資料顯示失真）

 

                                   表1. 幾種SQL語句應用MQT和不用MQT的效能對比

 

採用MQT的查詢

觀察以上兩條SQL語句，發現儘管Customer_Order中包含了10年的資料，但應用只對2007年度的資料進行統計，因此可以建立如下的MQT表，根據交易時間和訂單狀態進行分組計算，只包含2007年的分組資料。

CREATE TABLE mqt.summary_cust_order as

 ( select sum(amount) as amount, order_status, trans_dt from test.customer_order

   where TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007'

   group by trans_dt,order_status )

 data initially deferred refresh deferred

 IN tbsp1 ;

data inintally deferred是指當create MQT table的時候不會往表裡插資料,使用者需要發出refresh table命令。refresh deferred是指當對基表進行更改的時候並不對MQT進行實時更新，當使用者發出refresh table的時候才會將基表資料統計到MQT表中。refresh deferred對應的的refresh immediate指的是當對基表資料更新的時候實時更新MQT資料，後面還會講到這兩種差別。

 

建立完MQT表後，使用者需要發出refresh table命令載入資料

refresh table mqt.summary_cust_order

通過select count(*) from  mqt.summary_cust_order,發現裡面包含672條資料。

這時，我們再去執行前面兩條SQL語句，通過Explain plan訪問計劃可以看到查詢已經重寫到MQT表，優化器會自動選擇MQT作為查詢表，由於訪問的行數只有672行，因此estimated cost 只有32（timeron)，效能達到明顯改善。基表的資料量越大，MQT的優勢就越明顯。

以下是建立了MQT後的查詢訪問計劃,通過Optimized Statement可知，對Customer_Order的訪問已變為對SUMMARY_CUST_ORDER的訪問，這種查詢重寫由優化器決定，對應用來說是透明的。

SELECT SUM(AMOUNT), TRANS_DT FROM test.CUSTOMER_ORDER WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007' GROUP BY TRANS_DT

Total Cost:   32.119

Estimated Returned rows: 336

Original Statement:
------------------
SELECT SUM(AMOUNT), order_STATUS
FROM test.CUSTOMER_ORDER
WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007'
GROUP BY order_STATUS

Optimized Statement:
-------------------
SELECT Q3.$C1, Q3.$C0 AS "ORDER_STATUS"
FROM
   (SELECT Q2.$C0, SUM(Q2.$C1)
   FROM
      (SELECT Q1."ORDER_STATUS", Q1."AMOUNT"
      FROM MQT.SUMMARY_CUST_ORDER AS Q1) AS Q2
   GROUP BY Q2.$C0) AS Q3

Refresh deferred和Refresh Immediate

前面我們舉例說明了refresh deferred（延遲更新）,即當對基表資料進行更改的時候並不會對MQT表資料進行實時統計和計算，只有當使用者發出 refresh table的時候才會載入資料。這對於資料查詢同步要求不高的時候是很高的方案，但某些情況下，應用要求立即看到同步的資料，即當基表資料進行增刪改的時候，其結果需要理解反應到MQT表中，不需要使用者干預，這就是Refresh Immediate。但需要指出的是，refresh immediate在同步統計資料的同時，也會付出一些代價的，如：1. 會影響基表insert/update/delete的時間，因為資料要同步到MQT中；2.在對基表操作的時候，需要對MQT加鎖。

在表1，統計了插入一條SQL語句在refresh deferred和refresh immediate兩種情況下的執行效率，可以發現在使用refresh immediate的時候cost高達64(timeron), 而refresh deferred只有7.6，差距還是比較大的。因此，對於實時性要求不高的資料倉儲和報表系統來說，可優先考慮Refresh deferred.

Current Refresh Age註冊變數

特別需要注意的是存在一個current refresh age註冊變數，DB2優化器根據這個值來決定是否選擇MQT，這個變數可以是0或99999999999999（或ANY)。current refresh age的初始值是0，可以通過set current refresh age <>來設定。

1.當採用refresh immediate時，如果current refresh age是預設值（為0），DB2優化器會優先考慮是否執行MQT表。

2.當採用refresh deferred時，如果current refresh age=0，那麼優化器一般不會考慮用MQT。只有當current refresh age=ANY（或99999999999999)時，DB2優化器才會考慮執行MQT表。但設成ANY時要特別小心，因為MQT表的資料和base的資料不同步，產生的結果會有差異。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23577591/viewspace-704164/，如需轉載，請註明出處，否則將追究法律責任。