利用物化查询表提高 WebSphere Information Integrator 的性能 |
级别: 初级 Anjali Betawadkar-Norwood (anorwood@us.ibm.com), 顾问软件工程师, IBM Silicon Valley Lab 2006 年 10 月 16 日 您可能听说过物化查询表 (MQT) 可以显著改善查询性能。本文展示 MQT 为什么可以改善联邦系统的性能。您将会发现,本文介绍了您想要知道的有关在 WebSphere® Information Integrator 中使用 MQT 的各个方面的内容。 物化查询表 (MQT) 是一个这样的表,它物化了一个涉及一个或多个表或昵称的查询的预先计算的结果。当创建并填充好 MQT 之后,一个任意的后续查询就会由 MQT 来满足,条件是 MQT 匹配该查询的所有部分或一部分。要了解更多信息,请参见 [1]。大家都知道,使用 MQT 高速缓存数据和允许查询优化器作出一个基于成本的决策以使用 MQT 来满足查询可以改善 Database Management System (DBMS) 中的查询性能。在诸如 WebSphere Information Integrator 之类的联邦系统中使用 MQT 出于以下原因可以更加有益:
本文介绍 WebSphere Information Integrator 中的 MQT。展示如何定义适合于工作负载的 MQT,以及如何填充它们。给出在 WebSphere Information Integrator 中使用 MQT 的性能结果。给出用于确定查询是否使用了 MQT 的提示和技巧,如果未使用,是什么防止了 MQT 的使用。还例举了使用 MQT 的限制。 本文首先在 理解 MQT 一节给出一个启发性例子,展示 MQT 如何可以改善查询性能。然后简要解释了 如何作出在查询中使用 MQT 的决策 和 在联邦环境中使用 MQT 的优势。创建和使用 MQT 的步骤 讨论如何选择可能改善工作负载的性能的 MQT。这一节详细介绍了创建一个 MQT、用数据填充它以及为了优化而启用它的所有步骤。关于 故障诊断 的一节帮助您确定为什么没有使用 MQT 来满足查询。这一节还列出了关于使用 MQT 的 限制。本文的最后一节描述我们的内部 性能试验 和使用 MQT 获得的性能优势。 本节介绍一个示例业务场景,并逐步介绍使用 MQT 的需要,以及创建和启用它的步骤。 考虑一家公司的总部在纽约,而数据仓库在圣何塞。数据仓库跟踪该公司在美国销售的所有产品的销售记录。销售数据维护在具有以下模式的表 'sales' 中。
sales 表中的一行是给定日期、城市和州销售的特定产品的总数量和总价格。位于纽约的总部采用一个 WebSphere Information Integrator 系统。WebSphere Information Integrator 系统相应于圣何塞数据库中的 'sales' 表具有一个昵称 'divisional_sales'。 考虑总部的销售经理想要查看加利福尼亚州在 2005 年销售的产品。他们需要的是按城市聚集的信息。 按城市计算总销售的查询可以写成:
经理们会经常运行这类查询,有时会在 City 或 SUM_SALES 列添加更多的过滤条件。经理们希望找到的上一个查询的一个变体是查看在城市圣何塞中销售的产品。 这一要求可以表达为在上一个查询中添加一个谓词 CITY = 'SAN FRANCISCO'。该查询类似于:
当经理们想要找到总销售量小于 $100,000 的城市时,会产生另一个变体。该信息可以表达为在上面的查询中添加一个谓词 SUM_SALES < 100000,如下所示:
销售信息经常更新到 sales 表中,但这不是说经理们必须得到最新的信息。查询的业务环境能够忍受已经过期几个小时甚至几天的信息。在本例中,得出总销售信息是一个计算复杂的任务。而且,计算会对一些稍微不同的条件重复进行多次。一个需要指出的重要特征是,稍微过期的数据是可以接受的。可以定义一个 MQT,使得部分计算可以只执行一次并存储结果,以便后续的查询只需要很少的额外处理就可以从高速缓存的计算结果获得答案。 满足以上三个查询变体的一个可能的 MQT 定义类似于:
MQT 定义中的子句指出 MQT 的行为。稍后的几节中将详细讨论这些子句。一旦 MQT 已经创建并填充好,当用户提交一个查询以找到总销售数据或变体信息时,就会通过使用 aggregate_sales MQT 来满足该查询。 为了使用 MQT,用户不必要更改查询。一旦 MQT 已经创建并用数据填充好了,WebSphere Information Integrator 优化器就可以确定用户查询是否可以通过使用 MQT 来满足,以及使用 MQT 是否会带来性能优势。为了优化器使用 MQT 来取代部分或整个查询,需要满足以下条件:
出于各种原因,MQT 是有益的。下面描述了其中一些原因:
步骤 1:使用 Design Advisor 来确定将会有助于改善查询工作负载性能的 MQT 集合 将会有助于改善工作负载性能的 MQT 的种类可由对查询的分析或通过使用诸如 Design Advisor 之类的工具来确定。本节将讨论确定 MQT 可能对工作负载是有益的。 DB2® Design Advisor 帮助用户创建物化查询表 (MQT) 和索引、重新分配表并转换到多维集群 (MDC) 表,以及删除未用的对象。所有的推荐基于用户提供的一个或多个 SQL 语句。一组相关的 SQL 语句也叫做一个工作负载。用户可以为一个工作负载中的每个语句指定重要级别,并指定工作负载中每个语句执行的频率。Design Advisor 创建一个 SQL 脚本输出,其中包含用于创建推荐的对象的 CREATE INDEX、CREATE SUMMARY TABLE (MQT) 和 CREATE TABLE 语句。 在本文中,我们着重讲述如何使用 Design Advisor 来为联邦查询推荐 MQT。Design Advisor 是通过 DB2 Control Center 或 "db2advis" 命令调用的。 下面这个例子演示如何使用 Design Advisor 来为联邦工作负载推荐 MQT。在这个例子中:
Design Advisor 生成的输出文件包含一组 DDL 语句,用于创建 MQT、刷新和更新 MQT 上的统计信息,以及在其上创建索引。输出文件一生成就可以使用,或者为应用程序的特定需求进行定制。通过从 MQT 删除过滤器(本地)谓词,Design Advisor 为给定查询一般化推荐的 MQT。例如,如果 Design Advisor 是在这样一个查询上调用,该查询从三个表选择数据,并且包含三个联结谓词和两个过滤器谓词,那么推荐的 MQT 将不会包含任何过滤器谓词。如果您认为自己的查询总是使用相同的常量值过滤数据,那么您可能选择在 MQT 中包含一些过滤器谓词。 一旦确定了 MQT 定义,就可以使用 "CREATE TABLE" 语句创建 MQT 了。我们将使用前面 “理解 MQT” 一节中的例子。我们的示例 MQT 类似于:
创建 MQT 时需要仔细地确定要使用的子句。 'DATA INITIALLY DEFERRED' 子句指出,当创建 MQT 时,不会作为 create table 语句的一部分填充 MQT。 'REFRESH DEFERRED' 子句指出,使用 'REFRESH' 语句将用数据填充 MQT。对 MQT 定义中使用的表或昵称进行的数据插入、更新或删除等形式的任何更改将不会自动导致 MQT 中数据的插入、更新或删除。'REFRESH DEFERRED' 的一个替代是 'REFRESH IMMEDIATE' 选项。该选项指定,对 MQT 定义中使用的表进行的数据插入、更新或删除等形式的任何更改将会自动导致 MQT 中数据的更改。该选项对其定义引用昵称的 MQT 不可用。在本文中,我们将通过昵称使用 MQT,因此这些 MQT 将总是定义为 'REFRESH DEFERRED' MQT。 MQT 可以通过设置 'ENABLE QUERY OPTIMIZATION' 子句而对优化器可用。'DISABLE QUERY OPTIMIZATION' 子句可以用来防止优化器考虑使用特定的 MQT。 'REPLICATED' 子句指出,在启用 Data Partitioning Feature (DPF) 的系统的所有分区上建立 MQT 的一个副本。在这样的系统中,有可能指定 MQT 的分区或者将它定义为 REPLICATED MQT。如果 MQT 被定义为 REPLICATED,那么可以使用该 MQT 的操作(比如,联结)就可能获得更好的性能,并通过排列(collocation)将它与相同节点上的数据相关联。 MQT 可以根据以下用数据填充它们时所使用的机制来分类:
步骤 3:使用 SET INTEGRITY 语句使 MQT 脱离 check-pending 状态 该步骤只应用于用户维护的 MQT,并且启用 MQT 的数据填充。在我们的例子中,'aggregate_sales' 被定义为用户维护的 MQT,必须发出下面这个语句使 MQT 脱离 check-pending 状态。
"SET INTEGRITY" 语句指定,MQT 将打开完整性检查,而不检查完整性违反。 该步骤对于系统维护的 MQT 的不是必需的,因为用于填充数据的 REFRESH 语句使系统维护的 MQT 脱离 check-pending 状态。 一旦创建了 MQT,就需要用数据填充它,以反映 MQT 将缓存的查询结果。每当 MQT 的内容基于底层数据被更新时,也需要执行该步骤。
一旦创建了 MQT,您可能就想检查是否应该在 MQT 上创建任何索引。这可通过 "CREATE INDEX" 语句来执行。因为 MQT 是缓存查询结果的本地表,所以在本地表上创建索引的相同步骤也可应用于在 MQT 上创建索引。如果 MQT 具有一个键,那么应该创建惟一的索引以反映这一事实。索引的创建,惟一或不惟一,都有助于以不同的方式从 MQT 读取数据,导致更好的性能。 注意,惟一的索引不能创建在系统维护的 MQT 上。对于我们的例子来说,如果 'City' 是 MQT aggregate_sales 的键,那么 aggregate_sales 是用户维护的 MQT,可在该 MQT 上创建惟一的键,如下所示:
在昵称上定义 “信息约束” 以反映底层表上的约束,有助于甚至在缺少 MQT 的情况下优化性能。当存在 MQT 时,信息约束有时有助于优化器向 MQT 匹配一个查询,这在缺少信息约束时 MQT 是不会考虑匹配的。我们用一个例子来演示这一点: 假设除了前面描述的 DIVISIONAL_SALES 昵称以外,用户还拥有昵称 'OFFICE_INFO'。'OFFICE_INFO' 昵称具有 CITY、OFFICE_ADDR 和 PHONE_NUM 等列。 OFFICE_INFO 昵称中的列 'CITY' 是惟一的,因此在列 CITY 上定义了昵称 OFFICE_INFO 的惟一约束,如下所示:
昵称 DIVISIONAL_SALES 中的列 CITY 总是在昵称 OFFICE_INFO 中找到一个匹配的城市,因此在昵称 OFFICE_INFO 和 DIVISIONAL_SALES 之间的列 CITY 上定义了一个信息参考完整性约束,如下所示:
还与前面描述的一样,假设 MQT aggregate_sales 已经存在。 现在,用户发出一个查询:
因为参考完整性约束确保 DIVISIONAL SALES 中 CITY 的每个值在 OFFICE_INFO 中找到一个匹配,所以优化器可以安全地消除到 OFFICE_INFO 昵称的联结和到 MQT AGGREGATE_SALES 的路由。 步骤 7:使用 runstats 收集 MQT 上的统计信息 一旦创建了 MQT 和相关的索引,就最好在 MQT 上运行 "runstats" 以收集统计信息。WebSphere Information Integrator 优化器是基于成本的,并依赖于所有对象(包括 MQT)的精确统计信息来作出执行计划决策。对于我们的示例 MQT,runstats 可以运行来收集统计信息,如下所示:
这里的 'sample' 是模式,MQT aggregate_sales 是在该模式下创建的。 有两种方式允许为优化考虑 MQT。一个 MQT 也叫做一个维护表(maintained table)。
REFRESH AGE 指定 MQT 中的过期数据是可忍受的。优化器将考虑只在 REFRESH AGE 被设置为非零值且优化级别被设置为 2 或 5 或更高时才使用 MQT 来处理查询。REFRESH AGE 只应用于 'REFRESH DEFERRED' 类型的 MQT。当前支持两个 REFRESH AGE 值。
REFRESH AGE 可以两种方式之一进行设置:
两种参数都以默认值 0 开始,例如,默认情况下不为优化考虑 'REFRESH DEFERRED' MQT。 您可以使用 Visual Explain、Dynamic Explain 或 db2exfmt 工具的输出来找出是否使用 MQT 来满足查询。在本节中,我们将展示 db2exfmt 输出的一个片段来解释如何确定是否使用了 MQT。 当访问计划使用了 MQT 时,db2exfmt 输出将展示一个被访问的 MQT 对象。此外,对于某些查询,db2exfmt 输出中优化的 SQL(这是查询重写的输出)展示了 MQT 的使用而非基本昵称。在 db2exfmt 输出中,优化的 SQL 紧接在最初的 SQL 语句后面。 对于下面整个查询:
注意,该查询被重写来访问 aggregate_sales MQT。
计划片段类似于:
该计划展示了 aggregate_sales MQT 上的一个表扫描。此外,操作符表扫描的详细信息展示了以下信息:
访问计划中使用的对象的列表显示在 db2exfmt 输出的末尾。对于我们的例子来说,显示了以下信息。注意,昵称 divisional_sales 显示为访问计划中使用的对象中的一个。但是单词 '(reference only)' 表明昵称引用在查询中,但是不被访问来满足查询。
存在一些 MQT 优于昵称的限制,以便联邦查询使用 MQT。这些限制可分为三类:
WebSphere Information Integrator 不支持在分区数据库环境中引用昵称的系统管理的物化查询表。请参考 “分区数据库环境中的 MQT 优于昵称限制”,了解关于如何处理该限制的提示。 MQT 优于非关系昵称限制 存在一组特定于每个非关系数据源的 MQT 优于非关系昵称限制。这些限制由必需的谓词所引入,并隐含了特定数据源的昵称之间的关系。例如,Blast 和 HMMER 数据源的昵称使用一些谓词来将值组合到充当传递给 Blast 和 HMMER 工具的输入参数的列。因此,定义在这类昵称上的 MQT 必须指定这些谓词;否则,MQT 上的刷新将会失败,或者使用 MQT 的查询将产生不正确的结果。另一个例子是 XML 昵称的限制,即不能为子 XML 昵称创建 MQT。MQT 必须从根昵称到这个子昵称引用昵称的整个层次结构。 您可在 “MQT 优于非关系昵称限制” 页面找到更多信息。 Design Advisor 推荐的 MQT 限制 Design Advisor 生成的 MQT 有很少的几个限制和考虑因素,如下所示。 Design Advisor 不推荐用户维护的 MQT。因为昵称上的系统维护的 MQT 在分区数据库配置中是不受支持的,所以您不能在这样的环境中使用 Design Advisor。 当 Design Advisor 用于为联邦查询推荐 MQT 时,选项 -r 没有效果。选项 -r 指示 Design Advisor 为考虑的 MQT 使用扩展的统计信息。扩展的统计信息是使用采样在 MQT 定义中引用的对象上收集得到的。因为不支持在昵称上采样,所以 Design Advisor 使用优化器评估的统计信息。 为 MQT 推荐的索引被设计来改善工作负载性能而非 MQT 刷新性能。 如果更新、插入或删除未包含在指定的工作负载中,则不考虑更新推荐的 REFRESH IMMEDIATE MQT 的性能影响。
如果您的查询未使用 MQT,而您认为它应该使用,那么请检查:
要在昵称上测试 MQT 的性能,我使用适应于联邦环境的部分 "TPC-H" 基准测试工作负载。 我们生成了 4GB TPC-H 数据,并把 TPC-H 表分在两个单独物理机器上的两个数据库中。一个数据库包含表 PART、SUPPLIER、PARTSUPP、NATION 和 REGION,另一个数据库包含包含表 LINEITEM、ORDERS 和 CUSTOMERS。两个数据库都在 AIX® V5.2 on 4-CPU 机器上运行 DB2 Universal Database™ Version 8.2 Fixpack 11。在第三个 AIX V5.2, 4-CPU 机器上,我们安装了 WebSphere Information Integrator Version 8.2 Fixpack 11,并创建了一个联邦数据库。在联邦数据库中,我们定义了服务器来访问这两个 DB2 数据库和昵称,以从这些 DB2 数据库映射 TPC-H 表。 图 1. 我们的试验的配置 下一步是为 22 TPC-H 查询中的参数标志插入常量值,以模拟一个典型的工作负载并度量联邦服务器上这些查询的执行时间。 然后我们在这 22 个查询上运行 Design Advisor 以推荐 MQT。使用下面这个命令运行 Design Advisor:
通过从将路由到这些 MQT 的 TPC-H 查询添加一些本地谓词,我们定制了推荐的 MQT。 例如,Design Advisor 为查询 #5 推荐以下 MQT:
通过添加谓词 R_NAME = 'ASIA' 以便新的 MQT 定义变成下面这样,我们定制了这个 MQT。
接下来,我们在联邦数据库中创建并刷新了 MQT,我们再次运行工作负载以度量将路由到 MQT 的查询的执行时间。 下表总结了试验的结果。Design Advisor 推荐 8 个 MQT,对应于下表所示的 8 个 TPC-H 查询。第一列表示 TPC-H 查询号。下两列表示禁用 MQT 和启用 MQT 时以秒为单位的查询执行时间。第四列展示 MQT 引入的改善,即第二列和第三列之间的时间差异,第五列展示改善的百分比。最后一列列出我们对 Design Advisor 推荐的 MQT 所做的定制。请注意,尽管我们的工作负载被定义为每个查询只使用一个 MQT,但是也可能使用多个 MQT 来回答一个查询。MQT 也可以定义来回答多个查询。
*请注意,这些时间只是提供来演示定义在远程表上的 MQT 所能带来的性能改善。它们不是来自兼容的 TPC-H 基准测试,并且不应该拿来与任何现有的正式结果相比较。
WebSphere Information Integrator 为战略性信息集成框架提供基础,这样的框架有助于客户将新的应用程序快速推向市场,从现有资产获得更多的回报,并且可控制 IT 成本。随着更多的客户采用数据联邦技术,客户的下一技术焦点通常是获得更高的性能,因为他们的查询的复杂性在增加。 我们考虑物化查询表 (MQT) 支持一个重要的选项来进一步优化联邦服务器的性能。通过基于您的查询工作负载识别一套适当的 MQT,您可以用预先计算的查询结果定义和填充这些 MQT,并通过利用这些 MQT 允许您的查询工作负载运行得更快。正如本文所演示的,有些情况下的改善是相当显著的。 最好的是,您不需要修改应用程序就可以利用 MQT 的优势。联邦查询编译器作出一个基于成本的决策,并使得使用 MQT 的过程对用户是透明的。我们确信,您会发觉该特性是联邦环境中的一个强大工具。 学习
获得产品和技术
|
利用物化查询表提高 WebSphere Information Integrator 的性能 |
级别: 初级 Anjali Betawadkar-Norwood (anorwood@us.ibm.com), 顾问软件工程师, IBM Silicon Valley Lab 2006 年 10 月 16 日 您可能听说过物化查询表 (MQT) 可以显著改善查询性能。本文展示 MQT 为什么可以改善联邦系统的性能。您将会发现,本文介绍了您想要知道的有关在 WebSphere® Information Integrator 中使用 MQT 的各个方面的内容。 物化查询表 (MQT) 是一个这样的表,它物化了一个涉及一个或多个表或昵称的查询的预先计算的结果。当创建并填充好 MQT 之后,一个任意的后续查询就会由 MQT 来满足,条件是 MQT 匹配该查询的所有部分或一部分。要了解更多信息,请参见 [1]。大家都知道,使用 MQT 高速缓存数据和允许查询优化器作出一个基于成本的决策以使用 MQT 来满足查询可以改善 Database Management System (DBMS) 中的查询性能。在诸如 WebSphere Information Integrator 之类的联邦系统中使用 MQT 出于以下原因可以更加有益:
本文介绍 WebSphere Information Integrator 中的 MQT。展示如何定义适合于工作负载的 MQT,以及如何填充它们。给出在 WebSphere Information Integrator 中使用 MQT 的性能结果。给出用于确定查询是否使用了 MQT 的提示和技巧,如果未使用,是什么防止了 MQT 的使用。还例举了使用 MQT 的限制。 本文首先在 理解 MQT 一节给出一个启发性例子,展示 MQT 如何可以改善查询性能。然后简要解释了 如何作出在查询中使用 MQT 的决策 和 在联邦环境中使用 MQT 的优势。创建和使用 MQT 的步骤 讨论如何选择可能改善工作负载的性能的 MQT。这一节详细介绍了创建一个 MQT、用数据填充它以及为了优化而启用它的所有步骤。关于 故障诊断 的一节帮助您确定为什么没有使用 MQT 来满足查询。这一节还列出了关于使用 MQT 的 限制。本文的最后一节描述我们的内部 性能试验 和使用 MQT 获得的性能优势。 本节介绍一个示例业务场景,并逐步介绍使用 MQT 的需要,以及创建和启用它的步骤。 考虑一家公司的总部在纽约,而数据仓库在圣何塞。数据仓库跟踪该公司在美国销售的所有产品的销售记录。销售数据维护在具有以下模式的表 'sales' 中。
sales 表中的一行是给定日期、城市和州销售的特定产品的总数量和总价格。位于纽约的总部采用一个 WebSphere Information Integrator 系统。WebSphere Information Integrator 系统相应于圣何塞数据库中的 'sales' 表具有一个昵称 'divisional_sales'。 考虑总部的销售经理想要查看加利福尼亚州在 2005 年销售的产品。他们需要的是按城市聚集的信息。 按城市计算总销售的查询可以写成:
经理们会经常运行这类查询,有时会在 City 或 SUM_SALES 列添加更多的过滤条件。经理们希望找到的上一个查询的一个变体是查看在城市圣何塞中销售的产品。 这一要求可以表达为在上一个查询中添加一个谓词 CITY = 'SAN FRANCISCO'。该查询类似于:
当经理们想要找到总销售量小于 $100,000 的城市时,会产生另一个变体。该信息可以表达为在上面的查询中添加一个谓词 SUM_SALES < 100000,如下所示:
销售信息经常更新到 sales 表中,但这不是说经理们必须得到最新的信息。查询的业务环境能够忍受已经过期几个小时甚至几天的信息。在本例中,得出总销售信息是一个计算复杂的任务。而且,计算会对一些稍微不同的条件重复进行多次。一个需要指出的重要特征是,稍微过期的数据是可以接受的。可以定义一个 MQT,使得部分计算可以只执行一次并存储结果,以便后续的查询只需要很少的额外处理就可以从高速缓存的计算结果获得答案。 满足以上三个查询变体的一个可能的 MQT 定义类似于:
MQT 定义中的子句指出 MQT 的行为。稍后的几节中将详细讨论这些子句。一旦 MQT 已经创建并填充好,当用户提交一个查询以找到总销售数据或变体信息时,就会通过使用 aggregate_sales MQT 来满足该查询。 为了使用 MQT,用户不必要更改查询。一旦 MQT 已经创建并用数据填充好了,WebSphere Information Integrator 优化器就可以确定用户查询是否可以通过使用 MQT 来满足,以及使用 MQT 是否会带来性能优势。为了优化器使用 MQT 来取代部分或整个查询,需要满足以下条件:
出于各种原因,MQT 是有益的。下面描述了其中一些原因:
步骤 1:使用 Design Advisor 来确定将会有助于改善查询工作负载性能的 MQT 集合 将会有助于改善工作负载性能的 MQT 的种类可由对查询的分析或通过使用诸如 Design Advisor 之类的工具来确定。本节将讨论确定 MQT 可能对工作负载是有益的。 DB2® Design Advisor 帮助用户创建物化查询表 (MQT) 和索引、重新分配表并转换到多维集群 (MDC) 表,以及删除未用的对象。所有的推荐基于用户提供的一个或多个 SQL 语句。一组相关的 SQL 语句也叫做一个工作负载。用户可以为一个工作负载中的每个语句指定重要级别,并指定工作负载中每个语句执行的频率。Design Advisor 创建一个 SQL 脚本输出,其中包含用于创建推荐的对象的 CREATE INDEX、CREATE SUMMARY TABLE (MQT) 和 CREATE TABLE 语句。 在本文中,我们着重讲述如何使用 Design Advisor 来为联邦查询推荐 MQT。Design Advisor 是通过 DB2 Control Center 或 "db2advis" 命令调用的。 下面这个例子演示如何使用 Design Advisor 来为联邦工作负载推荐 MQT。在这个例子中:
Design Advisor 生成的输出文件包含一组 DDL 语句,用于创建 MQT、刷新和更新 MQT 上的统计信息,以及在其上创建索引。输出文件一生成就可以使用,或者为应用程序的特定需求进行定制。通过从 MQT 删除过滤器(本地)谓词,Design Advisor 为给定查询一般化推荐的 MQT。例如,如果 Design Advisor 是在这样一个查询上调用,该查询从三个表选择数据,并且包含三个联结谓词和两个过滤器谓词,那么推荐的 MQT 将不会包含任何过滤器谓词。如果您认为自己的查询总是使用相同的常量值过滤数据,那么您可能选择在 MQT 中包含一些过滤器谓词。 一旦确定了 MQT 定义,就可以使用 "CREATE TABLE" 语句创建 MQT 了。我们将使用前面 “理解 MQT” 一节中的例子。我们的示例 MQT 类似于:
创建 MQT 时需要仔细地确定要使用的子句。 'DATA INITIALLY DEFERRED' 子句指出,当创建 MQT 时,不会作为 create table 语句的一部分填充 MQT。 'REFRESH DEFERRED' 子句指出,使用 'REFRESH' 语句将用数据填充 MQT。对 MQT 定义中使用的表或昵称进行的数据插入、更新或删除等形式的任何更改将不会自动导致 MQT 中数据的插入、更新或删除。'REFRESH DEFERRED' 的一个替代是 'REFRESH IMMEDIATE' 选项。该选项指定,对 MQT 定义中使用的表进行的数据插入、更新或删除等形式的任何更改将会自动导致 MQT 中数据的更改。该选项对其定义引用昵称的 MQT 不可用。在本文中,我们将通过昵称使用 MQT,因此这些 MQT 将总是定义为 'REFRESH DEFERRED' MQT。 MQT 可以通过设置 'ENABLE QUERY OPTIMIZATION' 子句而对优化器可用。'DISABLE QUERY OPTIMIZATION' 子句可以用来防止优化器考虑使用特定的 MQT。 'REPLICATED' 子句指出,在启用 Data Partitioning Feature (DPF) 的系统的所有分区上建立 MQT 的一个副本。在这样的系统中,有可能指定 MQT 的分区或者将它定义为 REPLICATED MQT。如果 MQT 被定义为 REPLICATED,那么可以使用该 MQT 的操作(比如,联结)就可能获得更好的性能,并通过排列(collocation)将它与相同节点上的数据相关联。 MQT 可以根据以下用数据填充它们时所使用的机制来分类:
步骤 3:使用 SET INTEGRITY 语句使 MQT 脱离 check-pending 状态 该步骤只应用于用户维护的 MQT,并且启用 MQT 的数据填充。在我们的例子中,'aggregate_sales' 被定义为用户维护的 MQT,必须发出下面这个语句使 MQT 脱离 check-pending 状态。
"SET INTEGRITY" 语句指定,MQT 将打开完整性检查,而不检查完整性违反。 该步骤对于系统维护的 MQT 的不是必需的,因为用于填充数据的 REFRESH 语句使系统维护的 MQT 脱离 check-pending 状态。 一旦创建了 MQT,就需要用数据填充它,以反映 MQT 将缓存的查询结果。每当 MQT 的内容基于底层数据被更新时,也需要执行该步骤。
一旦创建了 MQT,您可能就想检查是否应该在 MQT 上创建任何索引。这可通过 "CREATE INDEX" 语句来执行。因为 MQT 是缓存查询结果的本地表,所以在本地表上创建索引的相同步骤也可应用于在 MQT 上创建索引。如果 MQT 具有一个键,那么应该创建惟一的索引以反映这一事实。索引的创建,惟一或不惟一,都有助于以不同的方式从 MQT 读取数据,导致更好的性能。 注意,惟一的索引不能创建在系统维护的 MQT 上。对于我们的例子来说,如果 'City' 是 MQT aggregate_sales 的键,那么 aggregate_sales 是用户维护的 MQT,可在该 MQT 上创建惟一的键,如下所示:
在昵称上定义 “信息约束” 以反映底层表上的约束,有助于甚至在缺少 MQT 的情况下优化性能。当存在 MQT 时,信息约束有时有助于优化器向 MQT 匹配一个查询,这在缺少信息约束时 MQT 是不会考虑匹配的。我们用一个例子来演示这一点: 假设除了前面描述的 DIVISIONAL_SALES 昵称以外,用户还拥有昵称 'OFFICE_INFO'。'OFFICE_INFO' 昵称具有 CITY、OFFICE_ADDR 和 PHONE_NUM 等列。 OFFICE_INFO 昵称中的列 'CITY' 是惟一的,因此在列 CITY 上定义了昵称 OFFICE_INFO 的惟一约束,如下所示:
昵称 DIVISIONAL_SALES 中的列 CITY 总是在昵称 OFFICE_INFO 中找到一个匹配的城市,因此在昵称 OFFICE_INFO 和 DIVISIONAL_SALES 之间的列 CITY 上定义了一个信息参考完整性约束,如下所示:
还与前面描述的一样,假设 MQT aggregate_sales 已经存在。 现在,用户发出一个查询:
因为参考完整性约束确保 DIVISIONAL SALES 中 CITY 的每个值在 OFFICE_INFO 中找到一个匹配,所以优化器可以安全地消除到 OFFICE_INFO 昵称的联结和到 MQT AGGREGATE_SALES 的路由。 步骤 7:使用 runstats 收集 MQT 上的统计信息 一旦创建了 MQT 和相关的索引,就最好在 MQT 上运行 "runstats" 以收集统计信息。WebSphere Information Integrator 优化器是基于成本的,并依赖于所有对象(包括 MQT)的精确统计信息来作出执行计划决策。对于我们的示例 MQT,runstats 可以运行来收集统计信息,如下所示:
这里的 'sample' 是模式,MQT aggregate_sales 是在该模式下创建的。 有两种方式允许为优化考虑 MQT。一个 MQT 也叫做一个维护表(maintained table)。
REFRESH AGE 指定 MQT 中的过期数据是可忍受的。优化器将考虑只在 REFRESH AGE 被设置为非零值且优化级别被设置为 2 或 5 或更高时才使用 MQT 来处理查询。REFRESH AGE 只应用于 'REFRESH DEFERRED' 类型的 MQT。当前支持两个 REFRESH AGE 值。
REFRESH AGE 可以两种方式之一进行设置:
两种参数都以默认值 0 开始,例如,默认情况下不为优化考虑 'REFRESH DEFERRED' MQT。 您可以使用 Visual Explain、Dynamic Explain 或 db2exfmt 工具的输出来找出是否使用 MQT 来满足查询。在本节中,我们将展示 db2exfmt 输出的一个片段来解释如何确定是否使用了 MQT。 当访问计划使用了 MQT 时,db2exfmt 输出将展示一个被访问的 MQT 对象。此外,对于某些查询,db2exfmt 输出中优化的 SQL(这是查询重写的输出)展示了 MQT 的使用而非基本昵称。在 db2exfmt 输出中,优化的 SQL 紧接在最初的 SQL 语句后面。 对于下面整个查询:
注意,该查询被重写来访问 aggregate_sales MQT。
计划片段类似于:
该计划展示了 aggregate_sales MQT 上的一个表扫描。此外,操作符表扫描的详细信息展示了以下信息:
访问计划中使用的对象的列表显示在 db2exfmt 输出的末尾。对于我们的例子来说,显示了以下信息。注意,昵称 divisional_sales 显示为访问计划中使用的对象中的一个。但是单词 '(reference only)' 表明昵称引用在查询中,但是不被访问来满足查询。
存在一些 MQT 优于昵称的限制,以便联邦查询使用 MQT。这些限制可分为三类:
WebSphere Information Integrator 不支持在分区数据库环境中引用昵称的系统管理的物化查询表。请参考 “分区数据库环境中的 MQT 优于昵称限制”,了解关于如何处理该限制的提示。 MQT 优于非关系昵称限制 存在一组特定于每个非关系数据源的 MQT 优于非关系昵称限制。这些限制由必需的谓词所引入,并隐含了特定数据源的昵称之间的关系。例如,Blast 和 HMMER 数据源的昵称使用一些谓词来将值组合到充当传递给 Blast 和 HMMER 工具的输入参数的列。因此,定义在这类昵称上的 MQT 必须指定这些谓词;否则,MQT 上的刷新将会失败,或者使用 MQT 的查询将产生不正确的结果。另一个例子是 XML 昵称的限制,即不能为子 XML 昵称创建 MQT。MQT 必须从根昵称到这个子昵称引用昵称的整个层次结构。 您可在 “MQT 优于非关系昵称限制” 页面找到更多信息。 Design Advisor 推荐的 MQT 限制 Design Advisor 生成的 MQT 有很少的几个限制和考虑因素,如下所示。 Design Advisor 不推荐用户维护的 MQT。因为昵称上的系统维护的 MQT 在分区数据库配置中是不受支持的,所以您不能在这样的环境中使用 Design Advisor。 当 Design Advisor 用于为联邦查询推荐 MQT 时,选项 -r 没有效果。选项 -r 指示 Design Advisor 为考虑的 MQT 使用扩展的统计信息。扩展的统计信息是使用采样在 MQT 定义中引用的对象上收集得到的。因为不支持在昵称上采样,所以 Design Advisor 使用优化器评估的统计信息。 为 MQT 推荐的索引被设计来改善工作负载性能而非 MQT 刷新性能。 如果更新、插入或删除未包含在指定的工作负载中,则不考虑更新推荐的 REFRESH IMMEDIATE MQT 的性能影响。
如果您的查询未使用 MQT,而您认为它应该使用,那么请检查:
要在昵称上测试 MQT 的性能,我使用适应于联邦环境的部分 "TPC-H" 基准测试工作负载。 我们生成了 4GB TPC-H 数据,并把 TPC-H 表分在两个单独物理机器上的两个数据库中。一个数据库包含表 PART、SUPPLIER、PARTSUPP、NATION 和 REGION,另一个数据库包含包含表 LINEITEM、ORDERS 和 CUSTOMERS。两个数据库都在 AIX® V5.2 on 4-CPU 机器上运行 DB2 Universal Database™ Version 8.2 Fixpack 11。在第三个 AIX V5.2, 4-CPU 机器上,我们安装了 WebSphere Information Integrator Version 8.2 Fixpack 11,并创建了一个联邦数据库。在联邦数据库中,我们定义了服务器来访问这两个 DB2 数据库和昵称,以从这些 DB2 数据库映射 TPC-H 表。 图 1. 我们的试验的配置 下一步是为 22 TPC-H 查询中的参数标志插入常量值,以模拟一个典型的工作负载并度量联邦服务器上这些查询的执行时间。 然后我们在这 22 个查询上运行 Design Advisor 以推荐 MQT。使用下面这个命令运行 Design Advisor:
通过从将路由到这些 MQT 的 TPC-H 查询添加一些本地谓词,我们定制了推荐的 MQT。 例如,Design Advisor 为查询 #5 推荐以下 MQT:
通过添加谓词 R_NAME = 'ASIA' 以便新的 MQT 定义变成下面这样,我们定制了这个 MQT。
接下来,我们在联邦数据库中创建并刷新了 MQT,我们再次运行工作负载以度量将路由到 MQT 的查询的执行时间。 下表总结了试验的结果。Design Advisor 推荐 8 个 MQT,对应于下表所示的 8 个 TPC-H 查询。第一列表示 TPC-H 查询号。下两列表示禁用 MQT 和启用 MQT 时以秒为单位的查询执行时间。第四列展示 MQT 引入的改善,即第二列和第三列之间的时间差异,第五列展示改善的百分比。最后一列列出我们对 Design Advisor 推荐的 MQT 所做的定制。请注意,尽管我们的工作负载被定义为每个查询只使用一个 MQT,但是也可能使用多个 MQT 来回答一个查询。MQT 也可以定义来回答多个查询。
*请注意,这些时间只是提供来演示定义在远程表上的 MQT 所能带来的性能改善。它们不是来自兼容的 TPC-H 基准测试,并且不应该拿来与任何现有的正式结果相比较。
WebSphere Information Integrator 为战略性信息集成框架提供基础,这样的框架有助于客户将新的应用程序快速推向市场,从现有资产获得更多的回报,并且可控制 IT 成本。随着更多的客户采用数据联邦技术,客户的下一技术焦点通常是获得更高的性能,因为他们的查询的复杂性在增加。 我们考虑物化查询表 (MQT) 支持一个重要的选项来进一步优化联邦服务器的性能。通过基于您的查询工作负载识别一套适当的 MQT,您可以用预先计算的查询结果定义和填充这些 MQT,并通过利用这些 MQT 允许您的查询工作负载运行得更快。正如本文所演示的,有些情况下的改善是相当显著的。 最好的是,您不需要修改应用程序就可以利用 MQT 的优势。联邦查询编译器作出一个基于成本的决策,并使得使用 MQT 的过程对用户是透明的。我们确信,您会发觉该特性是联邦环境中的一个强大工具。 学习
获得产品和技术
|
如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!
赞0
添加新评论0 条评论