使用实例化的查询表加速 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 新闻组与他联系。
|