ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2 MQT介绍

DB2 MQT介绍

原创 Linux操作系统 作者:fengjin821 时间:2009-09-16 23:01:22 0 删除 编辑

    有时候,物理数据库结构中的一次简单更改会显著地改进查询性能。除了索引外,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

如果有适当的索引,那么这些查询被作为索引扫描来执行。

 

现在,让我们创建一个 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 时,发出下面的语句:
REFRESH TABLE DB2INST2.SUMMARY_CUSTOMER_ORDER_2001;
现 在,对 MQT 的查询速度快很多,因为 MQT 的大小相当小,它的行很短(与基表的 400 个字节相比,它才 45 个字节)。


------------------------------------------------我是传说中的分割线----------------------


让我们假设,我们经常需要最新的 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;

注:表 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 未被并置。然而,这两个表常常连接在一起。

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 表广播到每个节点。

虽 然在我们的示例中,使用复制的 MQT 的预计成本稍微高了点(101171 vs. 100975)(因为我们正在另外一种空闲系统上运行,这种系统将两个分区放同一台计算机上。)然而,当节点驻留在不同计算机上并且它们之间的网络很忙 时,在这种情况下使用复制的 MQT 的性能优点会变得明显。


所以,当您从以下这样的表中复制数据时,使用复制的 MQT 会有性能方面的优势:


1. 是经常连接的。
2. 很少更新(即使曾经更新过)。
3. 不太大(虽然如果并置的性能优势可以抵消复制的一次性成本,您可能会考虑复制不太更新的大表。)

另外,对于复制的 MQT,不会发生针对 REFRESH IMMEDIATE 表所描述的锁定问题。

------------------------------------------------我是传说中的分割线---------------

 

REFRESH IMMEDIATE vs. REFRESH DEFERRED


REFRESH IMMEDIATE MQT 会象索引那样影响查询的性能。这些影响包括:


加速相关选择(select)语句的性能。
只要有意义,就由优化器自动选择它们。
会降低插入(insert)、更新(update)和删除(delete)语句的性能。
不能直接更新。
可能会占用相当大的磁盘空间。
在更新其基表期间,可能会保留互斥锁。


存在 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 会影响。

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13165828/viewspace-614846/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-29

  • 博文量
    191
  • 访问量
    511525