使用實例化的查詢表加速 DB2 UDB EEE 中的查詢
|
|
|
|
|
|
|
?
|
|
|
級別: 初級
Alexander Kuznetsov
, 芝加哥，伊利諾斯州
2002 年 8 月 01 日
有時候，物理數據庫結構中的一次簡單更改會引人注目地改進查詢性能。除了索引外，DB2 UDB 還為您提供了總結表（實例化的查詢表），在許多情況下，這些表比索引更有效。本文將提供一些示例來演示使用總結表的優點。
簡介
有時候，物理數據庫結構中的一次簡單更改會顯著地改進查詢性能。除了索引外，DB2? Universal Database? 還為您提供實例化的查詢表（在版本 7.2 和更早的發行版中，稱為“總結表”），在許多情況下，這些表比索引更有效。其實，實例化的查詢表（materialized query table，MQT）是根據查詢結果定義的表。本文將描述一些示例，在這些示例中，與單獨使用索引相比，MQT 提供更有效的性能改進。
優點：避免重復計算
MQT 可以幫助您避免對于每次查詢重復計算（如 SUM）。讓我們假設有一個名為 CUSTOMER_ORDER 的表，它存儲了好幾年的客戶訂單。該表的記錄超過一百萬條，平均行寬為 400 個字節。現在，假設我們必須對 2001 年的訂單運行多次查詢，并且我們只需要表中的三列，如下所示：
select SUM(AMOUNT), trans_dt
from db2inst2.CUSTOMER_ORDER
where trans_dt between '1/1/2001' and '12/31/2001'
group by trans_dt
|
或
select SUM(AMOUNT), status
from db2inst2.CUSTOMER_ORDER
where trans_dt between '1/1/2001' and '12/31/2001'
group by status
|
如果有適當的索引，那么這些查詢被作為索引掃描來執行。 清單 1是執行計劃的摘錄，它表明使用索引掃描運行查詢的預計成本是 152455。
清單 1. 對 CUSTOMER_ORDER 表運行查詢的成本
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select SUM(AMOUNT), trans_dt
from db2inst2.CUSTOMER_ORDER
where trans_dt between '1/1/2001' and '12/31/2001'
group by trans_dt
Estimated Cost = 152455
Estimated Cardinality = 378
（這里省略了一些行）
Subsection #2:
Access Table Name = DB2INST2.CUSTOMER_ORDER ID = 2,591
| #Columns = 1
| Index Scan: Name = DB2INST2.CUST_ORD_TRANS_DT ID = 4
| | Index Columns:
| | | 1: TRANS_DT (Ascending)
（這里省略了一些行）
End of section
|
現在，讓我們創建一個 MQT，它包含我們所需的列和行，包括總和計算。
CREATE TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2001 AS
(SELECT SUM(AMOUNT) AS TOTAL_SUM,
TRANS_DT,
STATUS
FROM DB2INST2.CUSTOMER_ORDER
WHERE TRANS_DT BETWEEN '1/1/2001' AND '12/31/2001'
GROUP BY TRANS_DT,
STATUS)
DATA INITIALLY DEFERRED REFRESH DEFERRED;
|
子句 DATA INITIALLY DEFERRED 表示：數據不作為 CREATE TABLE 語句的一部分插入到表中。而是您必須執行 REFRESH TABLE 語句來填充表。子句 REFRESH DEFERRED 表示：表中的數據僅作為發出 REFRESH TABLE 語句時的快照反映查詢結果。有關創建 MQT 的更多信息，請參閱 SQL Reference。
當我們準備填充剛才創建的 MQT 時，發出下面的語句：
REFRESH TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2001;
|
現在，對 MQT 的查詢速度快很多，因為 MQT 的大小相當小，它的行很短（與基表的 400 個字節相比，它才 45 個字節）。 清單 2 顯示了由 dynexpln 生成的執行計劃的摘錄，它表明了一個顯著的性能改進，與上一個計劃的預計成本 152455 相比，它只有 101。
清單 2. 對 MQT 運行查詢減少了成本
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select sum(total_sum), trans_dt
from db2inst2.summary_customer_order_2001
where trans_dt between '1/1/2001' and '12/31/2001'
group by trans_dt
Estimated Cost = 101
Estimated Cardinality = 25
這里省略了一些行
Subsection #1:
Access Summary Table Name = DB2INST2.SUMMARY_CUSTOMER_ORDER_2001 ID = 2,44
| #Columns = 2
| Relation Scan
（這里省略了一些行）
| | Sortheap Allocation Parameters:
| | | #Rows = 21
| | | Row Width = 45
| | Piped
（這里省略了一些行）
|
注：如果 2001 年 CUSTOMER_ORDER 中的數據在刷新之后又進行了更新，則需要再次刷新 MQT。
優點：避免資源集中式掃描
讓我們假設，我們經常需要最新的 2002 年總計。過去在 2002 年 1 月 3 日運行得非常快的報告，在 5 月就運行得慢多了，因為 2002 年的數據量增加了。正如我們前面所描述的那樣，查詢在 CUSTOMER_ORDER 表上作為索引掃描執行。
現在，我們應該考慮 MQT 能如何幫助我們改進性能。然而，因為數據始終在更新而且我們需要最新數據，所以不能使用 REFRESH DEFERRED，因為下一次更新基表時，MQT 將不與基表同步。現在，讓我們用 REFRESH IMMEDIATE 和 ENABLE QUERY OPTIMIZATION 選項來創建 MQT。
CREATE TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2002 AS(
SELECT
TRANS_DT,
STATUS,
COUNT(*) AS COUNT_ALL,
SUM(AMOUNT) AS SUM_AMOUNT,
COUNT(AMOUNT) AS COUNT_AMOUNT
FROM DB2INST2.CUSTOMER_ORDER
GROUP BY TRANS_DT,
STATUS)
DATA INITIALLY DEFERRED
REFRESH IMMEDIATE
ENABLE QUERY OPTIMIZATION;
|
REFRESH IMMEDIATE 表示：在用 REFRESH TABLE 語句填充 MQT 后，MQT 的內容始終是最新的。
重要事項：為了使優化器能夠自動選擇 MQT，ENABLE QUERY OPTIMIZATION 必須是有效的（這是缺省值）。
其它語法說明：所有聚合都出現在 SELECT 列表的末尾。另外，雖然我們的業務只關注 SUM(AMOUNT)，但我們仍必須將 COUNT(*) 和 COUNT(AMOUNT) 包括在全查詢中。原因很容易記住。讓我們假設正在從基表中刪除一個給定日期的所有記錄：
DELETE FROM DB2INST2.CUSTOMER_ORDER WHERE TRANS_DT = ?/1/2002?
|
現在，DB2 必須檢測到特定日期的所有記錄都已消失并刪除 MQT 中的所有相應記錄。有了 COUNT 字段就可以使 DB2 快速執行它，而不必掃描表或其索引。僅當 AMOUNT 列可空時，才需要 COUNT(AMOUNT)。
現在，該填充 MQT 并刷新其統計信息了：
REFRESH TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2002;
RUNSTATS ON TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2002 WITH DISTRIBUTION;
|
現在，讓我們看一下查詢性能是如何改進的（Estimated Cost = 392）。 清單 3是查詢執行計劃的摘錄：
清單 3. 優化器選擇使用 MQT
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select SUM(AMOUNT), trans_dt
from db2inst2.customer_order
where trans_dt >= '1/1/2002'
group by trans_dt
Estimated Cost = 392
Estimated Cardinality = 268
（這里省略了一些行）
Subsection #1:
Access Summary Table Name = DB2INST2.SUMMARY_CUSTOMER_ORDER_2002 ID = 2,46
| #Columns = 2
| Relation Scan
|
注：表 CUSTOMER_ORDER（不是總結表）是在查詢中指定的。優化器已經自動選擇使用 MQT。
無論何時修改 CUSTOMER_ORDER 表，互斥的表鎖就會在 SUMMARY_CUSTOMER_ORDER_2002 上保留，直到事務結束為止。只有同時具有聚合函數和 REFRESH IMMEDIATE 選項的 MQT 才會這樣。因此，修改 CUSTOMER_ORDER 中相關字段（包括所有插入和刪除）的事務必須很短，以減少鎖爭用。這個問題不適用于用 REFRESH DEFERRED 選項創建的 MQT，也不適用于復制的 MQT（在下一節中描述）。
優點：通過使用復制的 MQT 避免廣播
讓我們假設在分區環境中有一個名為 CUSTOMER_DATA 的大表。該表 CUSTOMER_DATA 與它的子表并置（Collocate）。分區鍵是系統生成的整數 CUSTOMER_ID。表 CUSTOMER_DATA 有一個對另一個表 ZIP_CODE 的引用。表 CUSTOMER_DATA 和 ZIP_CODE 未被并置。然而，這兩個表常常連接在一起。讓我們研究一下 清單 4中所示的訪問計劃。
清單 4. 與 ZIP_CODE 的連接會引起跨節點廣播
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select c.*, z.zip, z.state_name, z.country_name
from db2inst2.customer_address c join db2inst2.zip_code z on
c.zip_cd = z.zip_cd
Estimated Cost = 100975
Estimated Cardinality = 255819
Coordinator Subsection:
Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 0, 1
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 0, 1
Access Table Queue ID = q1 #Columns = 38
Return Data to Application
| #Columns = 38
Subsection #1:
Access Table Queue ID = q2 #Columns = 4
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Nested Loop Join
| Access Table Name = DB2INST2.CUSTOMER_ADDRESS ID = 2,591
| | #Columns = 35
| | Index Scan: Name = DB2INST2.CU_ZIP_CD ID = 2
| | | Index Columns:
| | | | 1: ZIP_CD (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Data Prefetch: Eligible 162
| | | Index Prefetch: Eligible 162
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Insert Into Asynchronous Table Queue ID = q1
| | | Broadcast to Coordinator Node
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q1
Subsection #2:
Access Table Name = DB2INST2.ZIP_CODE ID = 2,590
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 4
| | #Sort Key Columns = 1
| | | Key 1: ZIP_CD (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 4479
| | | Row Width = 36
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Insert Into Asynchronous Table Queue ID = q2
| |
Broadcast to All Nodes of Subsection 1
| | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q2
End of section
|
ZIP_CODE 表不會經常更新（因為不常有新的郵政編碼），但會經常成為連接目標。每次發出導致連接的查詢時，必須將 ZIP_CODE 表廣播到每個節點。
這對于要使用 復制的 MQT 來說，可能是個好情況，它基于可能已經在單個分區節點組中創建的表，但您需要在節點組中的所有數據庫分區中進行復制，以便啟用頻繁訪問的數據的并置。要創建復制的 MQT，調用帶 REPLICATED 關鍵字的 CREATE TABLE 語句。
CREATE TABLE DB2INST2.SUMMARY_ZIP_CODE AS (SELECT * FROM DB2INST2.ZIP_CODE)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE ENABLE QUERY OPTIMIZATION REPLICATED;
|
定義中不允許有聚合。ZIP_CODE 表在 ZIP_CD 上有唯一的索引。讓我們填充該表，在其上創建索引并更新統計信息：
REFRESH TABLE DB2INST2.SUMMARY_ZIP_CODE;
CREATE INDEX AAA_TTT ON DB2INST2.SUMMARY_ZIP_CODE(ZIP_CD);
RUNSTATS ON TABLE DB2INST2.SUMMARY_ZIP_CODE WITH DISTRIBUTION AND DETAILED INDEXES ALL;
|
現在，優化器自動選擇使用復制的表，這樣，每次運行查詢時，不必將 ZIP_CODE 表廣播到每個節點。
清單 5. 通過使用復制的 ZIP_CODE 表，避免某些跨節點廣播
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
select c.*, z.zip, z.state_name, z.country_name
from db2inst2.customer_address c join db2inst2.zip_code z on
c.zip_cd = z.zip_cd
Estimated Cost = 101171
Estimated Cardinality = 255819
Coordinator Subsection:
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 0, 1
Access Table Queue ID = q1 #Columns = 38
Return Data to Application
| #Columns = 38
Subsection #1:
Access Summary Table Name = DB2INST2.SUMMARY_ZIP_CODE ID = 2,47
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Insert Into Sorted Temp Table ID = t1
| | #Columns = 4
| | #Sort Key Columns = 1
| | | Key 1: ZIP_CD (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 8958
| | | Row Width = 36
| | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 4
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Access Table Name = DB2INST2.CUSTOMER_ADDRESS ID = 2,591
| | #Columns = 35
| | Index Scan: Name = DB2INST2.CU_ZIP_CD ID = 2
| | | Index Columns:
| | | | 1: ZIP_CD (Ascending)
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | Data Prefetch: Eligible 162
| | | Index Prefetch: Eligible 162
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Insert Into Asynchronous Table Queue ID = q1
| | | Broadcast to Coordinator Node
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q1
End of section
|
雖然在我們的示例中，使用復制的 MQT 的預計成本稍微高了點（101171 vs. 100975）（因為我們正在另外一種空閑系統上運行，這種系統將兩個分區放同一臺計算機上。）然而，當節點駐留在不同計算機上并且它們之間的網絡很忙時，在這種情況下使用復制的 MQT 的性能優點會變得明顯。
所以，當您從以下這樣的表中復制數據時，使用復制的 MQT 會有性能方面的優勢：
- 是經常連接的。
- 很少更新（即使曾經更新過）。
- 不太大（雖然如果并置的性能優勢可以抵消復制的一次性成本，您可能會考慮復制不太更新的大表。）
另外，對于復制的 MQT，不會發生針對 REFRESH IMMEDIATE 表所描述的鎖定問題。
REFRESH IMMEDIATE vs. REFRESH DEFERRED
REFRESH IMMEDIATE MQT 會象索引那樣影響查詢的性能。這些影響包括：
- 加速相關選擇（select）語句的性能。
- 只要有意義，就由優化器自動選擇它們。
- 會降低插入（insert）、更新（update）和刪除（delete）語句的性能。
- 不能直接更新。
- 可能會占用相當大的磁盤空間。
- 在更新其基表期間，可能會保留互斥鎖。
要查看對更新性能的影響，請參閱 清單 6（仍沒有 MQT）中所示的 INSERT 語句的 EXPLAIN 輸出。
清單 6. 對基本 ZIP_CODE 表執行的 INSERT 操作
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
insert into db2inst2.zip_code(zip_cd, zip, state_cd, state_name,
country_name) values (60606, '60606', 'IL', 'Illinois',
'United States')
Estimated Cost = 25
Estimated Cardinality = 1
（這里省略了一些行）
|
現在，讓我們添加用 REFRESH IMMEDIATE 選項創建的 MQT，并查看 清單 7 中所示的 EXPLAIN 輸出。
清單 7. 用 REFRESH IMMEDIATE 創建的 MQT 上的 INSERT 會增加性能成本
-------------------- SECTION ---------------------------------------
Section = 1
SQL Statement:
insert into db2inst2.zip_code(zip_cd, zip, state_cd, state_name,
country_name) values (60606, '60606', 'IL', 'Illinois',
'United States')
Estimated Cost = 50
Estimated Cardinality = 1
（這里省略了一些行）
|
在這個特殊示例中，當存在 REFRESH IMMEDIATE MQT 時，插入記錄的預計成本是雙倍的。另一方面，REFRESH DEFERRED MQT 沒有降低插入、更新和刪除語句的性能。
適度使用 REFRESH IMMEDIATE MQT，以僅僅優化頻繁運行且當前數據很重要的查詢。一些 MQT 不適于立即刷新條件。可以在 SQL Reference中找到準確的規則。
讓優化器決定
優化器可以根據以下條件選用用 REFRESH IMMEDIATE 選項創建的 MQT 來代替其基表：
- 基表、MQT 及其索引的當前統計信息。
- CURRENT QUERY OPTIMIZATION 設置的值。
如果 CURRENT REFRESH AGE 設置選項設置為 ANY，則優化器可以使用用 REFRESH DEFERRED 選項創建的 MQT。在 SQL Reference中詳細描述了 CURRENT QUERY OPTIMIZATION 和 CURRENT REFRESH AGE 設置選項。
為優化器提供 MQT、創建適當的索引并使統計信息保持最新。并讓優化器選擇是使用基表還是使用總結表。在某些情況下，優化器將選擇不使用 MQT。
不管 CURRENT REFRESH AGE 和 CURRENT QUERY OPTIMIZATION 設置選項的值是什么，您都可以直接在 SELECT 語句的 WHERE 子句中用 REFRESH DEFERRED 和 REFRESH IMMEDIATE 來指定 MQT。
結束語
正如我們所看到的那樣，如果正確應用了 MQT，那么它們在各種情況下會非常有用。上面的示例演示了如何應用 MQT 來改進查詢性能。雖然 MQT 使用起來十分方便，但需要額外的磁盤空間。用 REFRESH DEFERRED 選項創建的 MQT 不會影響對基表執行插入、更新和刪除的性能，而用 REFRESH IMMEDIATE 選項創建的 MQT 會影響。
關于作者
|
|
|
|
|
Alexander Kuznetsov 在軟件設計、開發和數據庫管理方面已經有十四年的經驗。目前，他正在設計 DB2 UDB EEE 中多 TB 級群集數據庫。Alexander 是 IBM 認證的高級技術專家（DB2 群集）和 IBM 認證的解決方案專家（數據庫管理和應用程序開發）。可以通過 comp.databases.ibm-db2 新聞組與他聯系。
|