DB2物化查詢表（MQT）刷新機制 物化查詢表（MQT）是以一次查詢的結果為基礎定義的表，可以顯著提高查詢的性能尤其是提高復雜查詢的性能，在數據倉庫等大型系統中有著廣泛的應用。要使 MQT 正常工作必須對其進行刷新，刷新的方式有多種，每一種方式其內部機制也各不相同，相應的其性能也有差別。 在本文中，我們將利用 DB2 說明（explain）工具獲取 MQT 刷新時相應的訪問方案（access plan），通過分析訪問方案來理解刷新機制，從而使讀者能夠在實際應用中合理的選擇刷新方式，更大程度的發揮 MQT 在性能提高方面的優勢。 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 裝載數據等情景的各種刷新方式。
