利用物化查询表提高 WebSphere Information Integrator 的性能

简介

物化查询表 (MQT) 是一个这样的表，它物化了一个涉及一个或多个表或昵称的查询的预先计算的结果。当创建并填充好 MQT 之后，一个任意的后续查询就会由 MQT 来满足，条件是 MQT 匹配该查询的所有部分或一部分。要了解更多信息，请参见 [1]。大家都知道，使用 MQT 高速缓存数据和允许查询优化器作出一个基于成本的决策以使用 MQT 来满足查询可以改善 Database Management System (DBMS) 中的查询性能。在诸如 WebSphere Information Integrator 之类的联邦系统中使用 MQT 出于以下原因可以更加有益：

联邦查询一般在远程系统上执行部分（或整个）查询，并通过网络将即时结果返回给联邦系统。由于网络的延迟，以联邦模式运行查询一般比只访问本地数据的相同查询要慢一些。涉及昵称的 MQT 使得远程数据本地可用，因此省去了通过网络到远程数据源的来回。

如果需要从中获得查询数据的一个远程 DBMS 不可用，那么在查询的访问计划决定使用 MQT 而非远程数据来满足查询时，查询将仍然能够产生结果。

本文介绍 WebSphere Information Integrator 中的 MQT。展示如何定义适合于工作负载的 MQT，以及如何填充它们。给出在 WebSphere Information Integrator 中使用 MQT 的性能结果。给出用于确定查询是否使用了 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 年销售的产品。他们需要的是按城市聚集的信息。

按城市计算总销售的查询可以写成：

SELECT　CITY,　SUM(PRICE)　AS　SUM_SALES　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY;　

经理们会经常运行这类查询，有时会在 City 或 SUM_SALES 列添加更多的过滤条件。经理们希望找到的上一个查询的一个变体是查看在城市圣何塞中销售的产品。

这一要求可以表达为在上一个查询中添加一个谓词 CITY = 'SAN FRANCISCO'。该查询类似于：

SELECT　CITY,　SUM(PRICE)　AS　SUM_SALES　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　CITY='SAN　FRANCISCO'　
AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005';　

当经理们想要找到总销售量小于 $100,000 的城市时，会产生另一个变体。该信息可以表达为在上面的查询中添加一个谓词 SUM_SALES < 100000，如下所示：

SELECT　CITY,　SUM(PRICE)　AS　SUM_SALES　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY　
HAVING　SUM(PRICE)　<　100000;　

销售信息经常更新到 sales 表中，但这不是说经理们必须得到最新的信息。查询的业务环境能够忍受已经过期几个小时甚至几天的信息。在本例中，得出总销售信息是一个计算复杂的任务。而且，计算会对一些稍微不同的条件重复进行多次。一个需要指出的重要特征是，稍微过期的数据是可以接受的。可以定义一个 MQT，使得部分计算可以只执行一次并存储结果，以便后续的查询只需要很少的额外处理就可以从高速缓存的计算结果获得答案。

满足以上三个查询变体的一个可能的 MQT 定义类似于：

CREATE　TABLE　AGGREGATE_SALES　(CITY,　TOTAL_SALES)　AS　
(SELECT　CITY,　SUM(PRICE)　
FROM　DIVISIONAL_SALES　
WHERE　STATE　='CA'　AND　TRANSACTION_DATE　BETWEEN　'01/01/2005'　AND　'12/31/2005'　
GROUP　BY　CITY)　
DATA　INITIALLY　DEFERRED　REFRESH　DEFERRED　
ENABLE　QUERY　OPTIMIZATION　
MAINTAINED　BY　SYSTEM;　

MQT 定义中的子句指出 MQT 的行为。稍后的几节中将详细讨论这些子句。一旦 MQT 已经创建并填充好，当用户提交一个查询以找到总销售数据或变体信息时，就会通过使用 aggregate_sales MQT 来满足该查询。

如何作出在查询中使用 MQT 的决策

为了使用 MQT，用户不必要更改查询。一旦 MQT 已经创建并用数据填充好了，WebSphere Information Integrator 优化器就可以确定用户查询是否可以通过使用 MQT 来满足，以及使用 MQT 是否会带来性能优势。为了优化器使用 MQT 来取代部分或整个查询，需要满足以下条件：

MQT 应用用户查询考虑应用的谓词的相同集合或子集。即使 MQT 只包含用户查询应用的谓词的一个子集，它仍然是可以使用的，因为优化器通过向从 MQT 返回的数据添加缺少的谓词进行了弥补。

MQT 选择用户查询考虑选择的列的相同集合或超集。如果查询比相应匹配的 MQT 选择较少的列，那么优化器可以消除不必要的列。

其他条件与 MQT 中数据的新鲜性和用于运行查询满足的优化级别有关。“如何使用和创建 MQT” 一节中将介绍这些条件。

在联邦环境中使用 MQT 的优势

出于各种原因，MQT 是有益的。下面描述了其中一些原因：

计算昂贵的查询的性能可以得到改善。缓存昂贵计算的结果一次，并多次使用缓存的结果，可以带来性能优势。它也使用较少的 CPU，并防止系统过载。

对多个查询公共的计算可以使用 MQT 来表示。MQT 使用数据填充一次，被多个查询重用，从而带来性能优势。

MQT 将数据本地缓存在联邦系统中，可以避免从后端数据源到传输数据的昂贵循环。

如果向查询提供数据的后端数据源断开了，并且优化器选择使用 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。在这个例子中：

组成工作负载的查询从文本文件 'my_queries.sql' 读取，

工作负载应用于数据库 'mydb' 中定义的对象上，且

MQT DDL 语句保存在文件 'my_rec_mqts.sql' 中。

Advisor 将推荐 REFRESH DEFFERED MQT。

db2advis　-d　mydb　-i　my_queries.sql　-m　M　-o　my_rec_mqts.sql　-u　-k　OFF

Design Advisor 生成的输出文件包含一组 DDL 语句，用于创建 MQT、刷新和更新 MQT 上的统计信息，以及在其上创建索引。输出文件一生成就可以使用，或者为应用程序的特定需求进行定制。通过从 MQT 删除过滤器（本地）谓词，Design Advisor 为给定查询一般化推荐的 MQT。例如，如果 Design Advisor 是在这样一个查询上调用，该查询从三个表选择数据，并且包含三个联结谓词和两个过滤器谓词，那么推荐的 MQT 将不会包含任何过滤器谓词。如果您认为自己的查询总是使用相同的常量值过滤数据，那么您可能选择在 MQT 中包含一些过滤器谓词。

步骤 2：创建 MQT

一旦确定了 MQT 定义，就可以使用 "CREATE TABLE" 语句创建 MQT 了。我们将使用前面 “理解 MQT” 一节中的例子。我们的示例 MQT 类似于：

CREATE　TABLE　AGGREGATE_SALES　(CITY,　TOTAL_SALES)　AS　
(SELECT　CITY,　SUM(PRICE)　
FROM　DIVISI