程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> 用 DB2 說明工具深入理解物化查詢表(MQT)刷新機制

用 DB2 說明工具深入理解物化查詢表(MQT)刷新機制

編輯:DB2教程

物化查詢表(MQT)是以一次查詢的結果為基礎定義的表,可以顯著提高查詢的性能尤其是提高復雜查詢的性能,在數據倉庫等大型系統中有著廣泛的應用。要使 MQT 正常工作必須對其進行刷新,刷新的方式有多種,每一種方式其內部機制也各不相同,相應的其性能也有差別。

在本文中,我們將利用 DB2 說明(explain)工具獲取 MQT 刷新時相應的訪問方案(Access plan),通過分析訪問方案來理解刷新機制,從而使讀者能夠在實際應用中合理的選擇刷新方式,更大程度的發揮 MQT 在性能提高方面的優勢。

開始之前

在開始討論之前如果有需要可以復習一下物化查詢表的基本概念,可以參考這篇文章《 DB2 基礎 : 物化查詢表簡介》,或者在 DB2 9.7 信息中心上查找相關主題。

DB2 提供多種方式使用說明工具,如 Visual Explain,EXPLAIN 命令 , db2expln 工具等,在本文中我們將使用的是設置 CURRENT EXPLAIN MODE 然後用 db2exfmt 命令來格式化說明表的內容來獲取訪問方案。

MQT 刷新方式

MQT 可以分為兩種類型,一種是系統維護的 MQT,一種是用戶維護的 MQT,其中前者有著廣泛的應用,因此在本文只討論系統維護的 MQT。

對於系統維護的 MQT 的刷新方式,根據刷新時間的不同可以分為兩種,一種是 REFRESH IMMEDIATE 也就是即時刷新,一種是 REFRESH DEFERRED 也就是延遲刷新。

使用即時刷新的方式,當對基表進行任何 insert/update/delete 等操作時,MQT 中的數據也自動的進行相應的刷新,其優點是能保證 MQT 中的數據總是最新的,但是由於對基表進行操作的同時還需要維護 MQT,負載有所增加。

使用延遲刷新的方式,當對基表進行任何 insert/update/delete 等操作時,MQT 中的數據沒有進行相應的刷新,而是等到用戶(DBA)階段性的執行刷新命令時才進行刷新。這種方式適合用在絕大部分時間都是只讀的應用系統中,或者數據的更新只發生維護窗口時間。

MQT 刷新方式還可以從數據刷新范圍的角度分成兩類,一類是完全刷新,一類是增量刷新。

完全刷新方式是將基表中的所有數據重新進行計算和處理從而更新 MQT 的數據。這種方式的缺點是 當基表的數據量大而且 MQT 定義復雜時,刷新過程可能會比較慢。

增量更新方式是針對 insert/update/delete 等操作更新過的基表中的那一部分數據,對 MQT 中相應數據進行刷新,而不需要訪問基表中所有數據。

將兩種分類方法結合在一起討論,即時刷新方式必然是增量刷新方式,延遲刷新方式采用的是完全刷新,但不是所有的延遲刷新方式都是完全刷新,有一種特殊的延遲刷新即“使用登台表(staging table)的延遲刷新”采用的是增量更新。當 MQT 的基表被修改時,變化就會傳播過來,並立即被添加到 staging 表中,這樣就可以利用 staging 表增量刷新,而不是從頭開始重新生成 MQT,從而可以顯著提高性能。當刷新操作完成時,staging 表中的數據就會被刪除。

接下來我們對以上幾種刷新方式,通過其訪問方案來進行深入的討論。

准備工作

我們先創建一個新的數據庫名字叫做 MYDB,或者也可以使用一個已有的數據庫。本文中所有操作都是在 LinuxAMD64 平台上的 DB2 9.7 版本進行,在其他的版本上也可以得到相似的結果,但不能保證完全一致。

連接到數據庫之後,創建說明表(explain tables),使用的命令如清單 1 所示。

清單 1. 創建數據庫,說明表,基表

 db2start 
 db2 CREATE DB MYDB 
 db2 CONNECT TO MYDB 
 db2 – tvf ~/sqllib/misc/EXPLAIN.DDL 

對於不同的平台如 Windows 平台或者安裝路徑的不同,EXPLAIN.DDL 文件的位置會有相應的不同, 但都是在 sqllib 目錄中。

REFRESH IMMEDIATE 方式

創建一個基表並插入若干數據,然後創建一個即時刷新的 MQT,使用的語句如清單 2 所示。

清單 2. 創建即時刷新的 MQT

 create table basetable 
 (c1 int not null primary key, c2 int, c3 int, c4 int); 
 
 insert into basetable 
 values(1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11); 
 
 create table mqttab as 
 (select c1, c2, c3 from basetable where c1 > 10) 
 data initially deferred refresh immediate; 
 
 set integrity for mqttab immediate checked not incremental; 

在清單 2 中創建一個 MQT 名字是 mqttab,其數據是基表中 c1>10 的行並只取每一行的前三個 column。 需要說明一下的是,這個 MQT 非常的簡單甚至於沒有任何應用的價值,但是已經可以滿足本文中所討論問題的需求,這裡我們的目標是探討 MQT 的刷新機制,為了便於理解我們沒有使用總結表等類型的 MQT。

上文中提到即時刷新的 MQT 能夠始終保證數據都是最新的,也就是在任何對基表的 insert/update/delete 等操作時,MQT 中的數據也自動的進行相應的刷新。接下來我們設置 CURRENT EXPLAIN MODE,並向基表 basetable 中插入一條數據,這條數據滿足 c1>10,然後來看這個操作的訪問方案,使用的命令和訪問方案如清單 3 所示。

清單 3. 基表的 insert 操作時的訪問方案

 db2 set current explain mode explain 
 db2 "insert into basetable values(12,12,12,12)" 
 db2exfmt -d MYDB -1 -e -o insert1.exfmt 
 db2 set current explain mode no 
 Rows 
         RETURN 
         (  1) 
          Cost 
          I/O 
          | 
           1 
         INSERT 
         (  2) 
         16.1648 
           2 
        /---+---\ 
        1      1 
      INSERT TABLE: HQY 
      (  3)   MQTTAB 
      8.0848    Q1 
        1 
     /---+---\ 
     1      4 
   TBSCAN TABLE: HQY 
   (  4)   BASETABLE 
   0.0048    Q4 
     0 
    | 
     1 
 TABFNC: SYSIBM 
   GENROW 
 
 2) INSERT: (Insert) 
 Input Streams: 
 ------------- 
 4) From Operator #3 
 
 Estimated number of rows: 1 
 Number of columns: 3 
 Subquery predicate ID: Not Applicable 
 
 Column Names: 
 ------------ 
 +Q6.$C0+Q6.$C1+Q6.$C2 

在清單 3 的訪問方案中我們可以看到,DB2 數據庫管理器在執行這條 insert 語句的時候,不但向基表 basetable 中插入了數據(INSERT 操作符 3),而且同時向 MQT mqttab 執行了插入操作(INSERT 操作符 2),這就充分說明了采用即時刷新方式時對 MQT 的刷新是與基表的操作同時完成的。另外在 INSERT 操作符 2 的 detail 中可以看到 Estimated number of rows: 1,說明對於 MQT 的刷新是增量刷新。

需要提醒一下的是,由於是在 explain mode 下使用了插入操作,但實際上該插入操作並沒有真正執行而只是生成了訪問方案,所以如果此時查詢 MQT 會發現無法找到這條新數據,下文中的各項操作都是與此類的情況。

接下來我們來看對基表的 update 和 delete 操作時的訪問方案,使用的命令如清單 4 所示。

清單 4. 基表的 update 和 delete 操作

 db2 set current explain mode explain 
 
 db2 "update basetable set c2=100 where c1=11" 
 db2exfmt -d MYDB -1 -e -o update1.exfmt 
 
 db2 "delete from basetable where c1=11" 
 db2exfmt -d MYDB -1 -e -o delete1.exfmt 
 
 db2 set current explain mode no 

觀察對基表的 update 和 delete 操作時的訪問方案,我們會發現與清單 3 中 insert 操作時的訪問方案相似,都是在對基表完成 update 或 delete 操作的同時相應的完成對 MQT 的刷新操作。讀者可以在本文最後的下載鏈接中找到 update1.exfmt 和 delete1.exfmt 中的訪問方案,限於篇幅不在正文部分顯示。

最後,我們來看一下當用戶手動的刷新 MQT 時其執行方案,使用的命令和訪問方案如清單 5 所示。

清單 5. 手動刷新采用即時更新方式的 MQT

  
 db2 set current explain mode explain 
 db2 "refresh table mqttab" 
 db2exfmt -d MYDB -1 -e -o refresh1.exfmt 
 db2 set current explain mode no 
 Rows 
   RETURN 
   (  1) 
    Cost 
    I/O 
    | 
     1 
   TBSCAN 
   (  2) 
   0.0048 
     0 
    | 
     1 
 TABFNC: SYSIBM 
   GENROW 

在清單 5 的執行計劃可以看到,對於采用即時刷新方式的 MQT,當用戶手動刷新時並沒有對該 MQT 進行任何操作。訪問方案中的 SYSIBM.GENROW 是一個內置函數,它可生成行的表而不使用任何輸入,它可以用來生成數據行,然後 TBSCAN 操作符讀取數據行。

至此,我們已經通過訪問方案理解了 MQT 的即時刷新方式,接下來我們繼續討論 MQT 的延遲刷新方式。

REFRESH DEFERRED 方式(無登台表,即完全刷新)

創建一個基表並插入若干數據,然後創建一個延遲刷新的 MQT,這裡基表和 MQT 的表結構與之前的相同,這樣也便於我們比較不同刷新方式的異同。使用的語句如清單 6 所示。

清單 6. 創建延遲刷新的 MQT

 create table basetab2 
 (c1 int not null primary key, c2 int, c3 int, c4 int); 
 
 insert into basetab2 
 values(1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11); 
 
 create table mqttab2 as 
 (select c1, c2, c3 from basetab2 where c1 > 10) 
 data initially deferred refresh deferred; 
 
 set integrity for mqttab2 immediate checked not incremental 

本文開始部分提到,對於延遲刷新方式,當對基表進行任何 insert/update/delete 等操作時,MQT 中的數據沒有進行相應的刷新,而是等到用戶手動的執行刷新命令時才進行刷新。現在我們設置 CURRENT EXPLAIN MODE,並向基表 basetab2 中插入一條數據,查看其訪問方案,使用的命令及得到的訪問方案如清單 7 所示。

清單 7. 基表的 insert 操作時的訪問方案

 db2 set current explain mode explain 
 db2 "insert into basetab2 values(12,12,12,12)" 
 db2exfmt -d MYDB -1 -e -o insert2.exfmt 
 db2 set current explain mode no 
 
    Rows 
      RETURN 
      (  1) 
       Cost 
       I/O 
       | 
        1 
      INSERT 
      (  2) 
      8.0848 
        1 
     /---+---\ 
     1      4 
   TBSCAN TABLE: HQY 
   (  3)   BASETAB2 
   0.0048    Q3 
     0 
    | 
     1 
 TABFNC: SYSIBM 
   GENROW 

我們發現清單 7 中訪問方案就是一個普通的 insert 操作,只對基表 basetab2 進行了插入操作,並沒有對 MQT mqttab2 進行任何的操作,這就可以說明采用延遲更新方式的 MQT 在基表有變化時並沒有即時刷新。

接下來我們來看對基表的 update 和 delete 操作時的訪問方案,使用的命令如清單 8 所示。

清單 8. 基表的 update 和 delete 操作

 db2 set current explain mode explain 
 
 db2 "update basetab2 set c2=100 where c1=11" 
 db2exfmt -d MYDB -1 -e -o update2.exfmt 
 
 db2 "delete from basetab2 where c1=11" 
 db2exfmt -d MYDB -1 -e -o delete2.exfmt 
 
 db2 set current explain mode no 

同樣的我們可以發現,對基表的 update 和 delete 操作時的訪問方案也沒有對 MQT 的刷新操作。讀者可以在本文最後的下載鏈接中找到 update2.exfmt 和 delete2.exfmt 中的訪問方案,限於篇幅不在正文部分顯示。

最後,我們來看一下當用戶手動的刷新 MQT 時其執行方案,在刷新之前我們首先向基表中插入了幾條數據並修改了部分已有數據,使用的命令和訪問方案如清單 9 所示。

清單 9. 手動刷新采用延遲更新方式的 MQT

  
 db2 "insert into basetab2 values(12,12,12,12)" 
 db2 "insert into basetab2 values(13,12,12,12)" 
 db2 "insert into basetab2 values(14,12,12,12)" 
 db2 "insert into basetab2 values(15,12,12,12)" 
 db2 "update basetab2 set c2=100 where c1=11" 
 db2 runstats on table hqy.basetab2 
 db2 set current explain mode explain 
 db2 "refresh table mqttab2" 
 db2exfmt -d MYDB -1 -e -o refresh2.exfmt 
 db2 set current explain mode no 
                   Rows 
                   RETURN 
                   (  1) 
                    Cost 
                    I/O 
                    | 
                   0.333333 
                   FILTER 
                   (  2) 
                   35.9682 
                     4 
    +------------+------------------+-----+-------------------------+ 
     1      0           1.33333           1 
   TBSCAN    FILTER          INSERT          DELETE 
   (  3)    (  4)          (  6)          (  9) 
   0.0048    0.0404          17.979          17.8015 
     0      0            2             2 
    |      |          /---+----\        /---+----\ 
     1      1        1.33333     1      1      1 
 TABFNC: SYSIBM  TBSCAN       FETCH  TABLE: HQY    TBSCAN  TABLE: HQY 
   GENROW    (  5)       (  7)    MQTTAB2   ( 10)    MQTTAB2 
          0.0048       9.89896    Q6     9.72148    Q10 
           0         1             1 
           |       /---+----\          | 
           1     1.33333     4         1 
       TABFNC: SYSIBM  IXSCAN  TABLE: HQY     TABLE: HQY 
          GENROW    (  8)   BASETAB2      MQTTAB2 
                1.73403    Q7         Q11 
                  0 
                 | 
                  4 
              INDEX: SYSIBM 
             SQL091124002956410 
                 Q7 

在清單 11 中我們得到了一個與清單 5 中完全不同而且比較復雜的訪問方案,在本文中不會詳細解釋該訪問方案的具體細節,通過觀察發現在這個訪問方案中對 MQT mqttab2 執行了 INSERT 操作(操作符 6)和 DELETE 操作(操作符 9),其中 INSERT 操作的輸入是 FETCH(操作符 7)也就是從基表 basetab2 中獲取數據並插入到 mqttab2 中,DELETE 操作的輸入是 TBSCAN( 操作符 10) 也就是從 MQT mqttab2 中找到相應的數據進行刪除。這個過程就是根據基表的最新數據向 MQT 中插入新數據或者刪除無效的數據,這也是為什麼只有 INSERT 操作和 DELETE 操作而沒有 UPDATE 操作。

至此,我們已經通過訪問方案理解了 MQT 的延遲刷新方式,接下來我們繼續討論當有登台(staging)表時的 MQT 延遲刷新方式。

有登台表的 REFRESH DEFERRED 方式

創建一個基表並插入若干數據,然後創建一個延遲刷新的 MQT,並創建一個登台表,基表和 MQT 的表結構與上文相同,差別在於這裡創建了登台表 mqttab3_stg,也由此需要多執行一條 SET INTEGRITY 命令,使用的語句如清單 10 所示。

清單 10. 創建延遲刷新的 MQT 並創建登台表

 create table basetab3 
 (c1 int not null primary key, c2 int, c3 int, c4 int); 
 
 insert into basetab3 
 values(1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11); 
 
 create table mqttab3 as 
 (select c1, c2, c3 from basetab3 where c1 > 10)" 
 data initially deferred refresh deferred; 
 
 create table mqttab3_stg for mqttab3 propagate immediate; 
 
 set integrity for mqttab3 materialized query immediate unchecked; 
 set integrity for mqttab3_stg staging immediate unchecked; 
 
 db2 describe table hqy.MQTTAB3_STG 
 Data type Column 
 Column name schema Data type name Length Scale Nulls 
 ---------- ------- ----------- ------- ----- ------ 
 C1 SYSIBM INTEGER 4 0 No 
 C2 SYSIBM INTEGER 4 0 Yes 
 C3 SYSIBM INTEGER 4 0 Yes 
 GLOBALTRANSID SYSIBM CHARACTER 8 0 No 
 GLOBALTRANSTIME SYSIBM CHARACTER 13 0 No 
 OperaTIONTYPE SYSIBM INTEGER 4 0 No 
 
 6 record(s) selected. 

在清單 10 的最後描述了登台表 mqttab3_stg 的表結構,可以看到登台表中包括了 MQT mqttab3 的所有的列,也就是說登台表能夠捕捉基表的變化並保存更新 MQT 所需的所有數據。

我們來看一下,同樣是延遲更新,有了登台表之後會有什麼樣的變化。設置 CURRENT EXPLAIN MODE,向基表 basetab3 中插入一條數據然後來看這個操作的訪問方案,使用的命令和得到的訪問方案如清單 11 所示。

清單 11. 基表的 insert 操作時的訪問方案

 db2 set current explain mode explain 
 db2 "insert into basetab3 values(12,12,12,12)" 
 db2exfmt -d MYDB -1 -e -o insert3.exfmt 
 db2 set current explain mode no 
 
   Rows 
         RETURN 
         (  1) 
          Cost 
          I/O 
          | 
           1 
         INSERT 
         (  2) 
         16.236 
           2 
        /---+---\ 
        1     58 
      INSERT TABLE: HQY 
      (  3)  MQTTAB3_STG 
      8.0848    Q1 
        1 
     /---+---\ 
     1     1000 
   TBSCAN TABLE: HQY 
   (  4)   BASETAB3 
   0.0048    Q4 
     0 
    | 
     1 
 TABFNC: SYSIBM 
   GENROW 

在清單 11 中我們得到了一個與清單 7 完全不同卻與清單 3 十分類似的訪問方案。清單 3 中的 MQT 采用即時刷新方式,對基表 insert 操作時同時向 MQT mqttab 進行了操作,而在清單 11 中的 MQT 采用的是延遲刷新方式,在訪問方案中唯一的差別是其沒有同時更新 MQT,而是同時對登台表進行了操作。

接下來我們來看對基表的 update 和 delete 操作時的訪問方案,使用的命令如清單 12 所示。

清單 12. 基表的 update 和 delete 操作

 db2 set current explain mode explain 
 
 db2 "update basetab2 set c2=100 where c1=11" 
 db2exfmt -d MYDB -1 -e -o update2.exfmt 
 
 db2 "delete from basetab2 where c1=11" 
 db2exfmt -d MYDB -1 -e -o delete2.exfmt 
 
 db2 set current explain mode no 

同樣的我們可以發現,對基表的 update 和 delete 操作時的訪問方案也沒有對 MQT 的刷新操作,而是對登台表進行了相應的操作。讀者可以在本文最後的下載鏈接中找到 update3.exfmt 和 delete3.exfmt 中的訪問方案,限於篇幅不在正文部分顯示。

最後,我們來看一下當用戶手動的刷新 MQT 時其執行方案,使用的命令和訪問方案如清單 13 所示。

清單 13. 手動刷新采用延遲更新方式並有登台表的 MQT

 db2 "insert into basetab3 values(12,12,12,12)" 
 db2 "insert into basetab3 values(13,12,12,12)" 
 db2 "insert into basetab3 values(14,12,12,12)" 
 db2 "insert into basetab3 values(15,12,12,12)" 
 db2 "update basetab3 set c2=100 where c1=11" 
 
 db2 runstats on table hqy.basetab3 
 
 db2 set current explain mode explain 
 db2 "refresh table mqttab3" 
 db2exfmt -d MYDB -1 -e -o refresh3.exfmt 
 db2 set current explain mode no 
 
 
              Rows 
             RETURN 
             (  1) 
              Cost 
              I/O 
              | 
             0.333333 
             FILTER 
             (  2) 
             514.308 
             61.0015 
    +------------+------+-------------------+ 
     1      0           8.02174e-07 
   TBSCAN    FILTER           INSERT 
   (  3)    (  4)           (  6) 
   0.0048    0.076           514.124 
     0      0            61.0015 
    |      |           /----+-----\ 
     1      1       8.02174e-07    1000 
 TABFNC: SYSIBM  TBSCAN       FILTER   TABLE: HQY 
   GENROW    (  5)       (  7)     MQTTAB3 
          0.0048       506.044     Q6 
           0        60.0015 
           |          | 
           1       0.000501359 
       TABFNC: SYSIBM      DELETE 
          GENROW       (  8) 
                    506.044 
                    60.0015 
                  /----+-----\ 
               0.000501359    1000 
                UPDATE   TABLE: HQY 
                (  9)     MQTTAB3 
                506.04      Q7 
                60.001 
               /----+-----\ 
            0.000501359    1000 
             FETCH   TABLE: HQY 
             ( 10)     MQTTAB3 
             506.036     Q8 
             60.0005 
           /----+-----\ 
        0.000501359    1000 
         TBSCAN   TABLE: HQY 
         ( 11)     MQTTAB3 
         505.995 
          60 
          | 
        0.000501359 
         TEMP 
         ( 12) 
         504.729 
          60 
          | 
        0.000501359 
         >^NLJOIN 
         ( 13) 
         504.271 
          60 
        /----+-----\ 
     0.000501359    40 
      FILTER     TBSCAN 
      ( 14)     ( 20) 
      491.357    144.813 
       59       7 
       |       | 
     0.000501359    1000 
      FILTER   TABLE: HQY 
      ( 15)     MQTTAB3 
      491.168     Q19 
       59 
       | 
       58 
      TBSCAN 
      ( 16) 
      487.826 
       59 
       | 
       58 
      SORT 
      ( 17) 
      487.184 
       59 
       | 
       58 
      DELETE 
      ( 18) 
      482.288 
       59 
     /---+----\ 
    58      58 
   TBSCAN  TABLE: HQY 
   ( 19)   MQTTAB3_STG 
   13.6476    Q9 
    1 
    | 
    58 
 TABLE: HQY 
  MQTTAB3_STG 
    Q10 

在清單 13 中我們看到了一個非常復雜的訪問方案,限於篇幅不能詳細的解釋,簡要的說,在這個訪問方案中,首先讀取了登台表 mqttab3_stg 並刪除了登台表的數據(DELETE 操作符 18),接著用登台表中讀取的數據與 MQT mqttab3 進行合並(NLJOIN 操作符 13)並放入臨時表中(TEMP 操作符 12),然後根據臨時表中的數據對 MQT mqttab3 依次進行了更新(UPDATE 操作符 9)、刪除(DELETE 操作符 8)和插入(INSERT 操作符 6)。在整個過程中沒有任何用到基表 basetab3 的操作,這就說明此時的刷新是完全基於登台表 mqttab3_stg 進行的延遲刷新。

至此,我們已經通過訪問方案理解了 MQT 的即時刷新方式、延遲刷新方式以及有登台表的延遲刷新方式的特點和機制,讀者還可以繼續深入探討其他情景,如定義復雜的 MQT(如使用 COUNT 和 GROUP BY),或者使用 LOAD 裝載數據等情景的各種刷新方式。

總結

在文中介紹了 MQT的刷新方式並利用說明工具獲取其訪問方案,使讀者能夠較為深入的理解這些刷新方式,從而能夠在實際應用中選擇合適的刷新方式,更大程度的提高應用系統的性能。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved