Body

                                                                      ——正确的物化查询表（MQT）策略可以使查询响应时间变得确实不同一般

原文链接:
http://www.ibmsystemsmag.com/ibmi/administrator/db2/MQTs-for-Business-Intelligence-Success/?page=2

现在，商业智能和数据仓库产品，在给用户业务带来价值方面，变得越来越有力，越来越多样化。这些产品的核心当然是用户在线交易系统中收集的数据。而其中主要的一个步骤是将这些数据整合、分类、汇总以便提取出有用的信息。典型的应用场景是，用户会重复查询同一个静态数据集。举一个例子，用户想要总结昨天或者上个星期的销售情况或者某阶段末（如月末或年末）的摘要报告，而下一阶段在线数据还在不间断积累。如果数据库管理系统可以提供一种机制来事先为用户归纳、汇总数据就太好了。通过创建和使用物化查询表（Materialized query tables， MQTs），IBM DB2 for i5/OS为你实现了这个功能。

MQTs是在i5/OS的V5R3版本引入的，在V5R4版本做了增强。一个物化查询表可以被看成是一个物化视图或者一个自动汇总的表；通过预先执行一部分查询并且存储结果，它也可以用于显著提高复杂查询的性能。由于不再查询一个或多个具体的表，而是查询一个MQT，查询优化器不用要求最终用户或程序修改SQL，就可以识别MQTs并且隐式地重写查询，以获得更好的性能。在某些情况下，使用MQT的查询性能会快好几个数量级。

MQTs对于使用复杂连接与聚合的查询尤其有益，因为这些查询会读取和处理大量数据。商业智能和数据仓库会分阶段地加载数据，这是MQT特别适用的应用场景。按日、按周以及按月来处理数据的方式提供了一个天然的契机来刷新或更新MQTs。在这种情况下，MQTs不需要与其对应的表保持数据同步，刷新MQT只要作为已有日终，周终或月结的批处理操作一部分就可以了。

找到查询语句

为了说明MQTs如何提高如多个用户对同一组表查询几乎相同信息这样的查询的性能，让我们先来查看下性能可能会受益于MQT的SQL语句。例如，10个部门的经理都会在每周一早上查询销售趋势的数据。这就会导致10组SQL分组查询语句都查相同的信息。每周一早上都运行这些的查询是一件浪费时间及资源的事情。利用MQTs与查询优化器的重写查询（query-rewrite）功能，可以极大地缩短响应时间和减少资源使用，同时仍会完成各个部门的查询。通过使用这样一个方法——创建和导入一个存有所有销售数据，以年、周以及部门分组的MQT，随后让优化器处理这个MQT——10个查询中的每个查询都可以访问MQT的预汇总数据，而不是去相应基表中读取和处理详细数据。最终用户或应用程序不需要改变它们的查询，优化器为其重写查询。此外，作为周末处理流程的一部分，MQT可以在那时被更新，准备好汇总数据集，以备周一早上的查询。

为了在这样的应用场景中确定一个潜在的SQL语句集，我们可以打开System i导航器的SQL 查询计划缓存查看器。SQL查询计划缓存包含了所有当前的查询计划以及相关的运行态信息。在System i导航器中右键单击数据库文件夹，然后选择“SQL计划高速缓存”->“显示语句”，可以打开这个视图（如下图1）。

图1

在这种情况下，用户感兴趣的语句是用户程序中运行时间最长的那些。为了找到它们，可以选择“引用以下对象的语句”过滤器，并填上模式名称和表中列名。通过刷新得到SQL语句列表，并将它们按SQL执行次数进行排序，最终可以找到想要的查询语句。用户还可以根据运行时间这一列进行排序，这样就可以看到从耗时最长到最短的语句列表了。在语句列表中，若干查询都用到了同样的几个表，并且只有查询的列和使用不同的查询条件这样的细微差别。基于上述两点，我们就知道哪些语句是创建一个MQT的候选语句了。

用户最感兴趣的是运行频率最高的语句。如下面的例子，这是一个经理用到的查询语句：
SELECT YEAR, WEEK, DEPARTMENT, SUM(SALES), COUNT (DISTINCT ORDER_NO)
FROM ORDERS_TABLE
WHERE DEPARTMENT = 'ABC123'
GROUP BY YEAR, WEEK, DEPARTMENT
ORDER BY YEAR, WEEK, DEPARTMENT;

设计这个应用场景的时候，用户已经知道关于这个查询和潜在数据的一些详细信息了：ORDERS_TABLE包含3年的1百万条数据，每年52周，共有25个部门；数据如按YEAR，WEEK 以及 DEPARTMENT 分组，最多会有3900个不同的组。

在语句列表中选择这个select语句，右击选择“使用SQL语句”，此时会打开一个“运行SQL 脚本”窗口，并显示这个select语句。在“运行SQL 脚本”工具栏中选择“VisualExpain”->“说明”，这样就能看到这个语句的查询优化计划了（如图2）。

图2
 

此时会进行表扫描并得到一个临时的、独立的哈希表，而VE的运行结果就是显示了对这个哈希表的排序列表扫描。
 

创建一个MQT

下一步就是创建一个满足该查询的MQT了。MQT是数据库的对象，通过SQL语句CREATE TABLE创建的。在System i导航器中，在想创建MQT的数据库下，找到Tables目录，然后右键单击“新建”->“具体化查询表”。在第一个选项卡页中，用户可以设置MQT表名，数据库名。在第二个选项卡页中，用户可以指定select语句以及设置刷新表的选项。Select语句会变成MQT定义的一部分，并且为其关联一个查询。在用户输入SQL语句后，可以点击“检查语法”按钮来做语法检查。“预览结果”是一个很好的功能，使用这个按钮可以显示输入的SQL语句的结果，这样用户就可以确定以后使用MQT返回的结果是否是自己想要的了。图3展示了在System i导航器中创建MQT的对话框。基于用户的分析结果，输入下面的查询语句：
SELECT YEAR, WEEK, DEPARTMENT, SUM(SALES) AS SUM_SALES, COUNT(DISTINCT ORDER_NO) AS ORDER_NO
FROM ORDERS_TABLE
GROUP BY YEAR, WEEK, DEPARTMENT

图3

用户需要明确是否需要将数据初始化填入MQT中，如果不点击“以select语句结果填充表”选项，MQT会直到用户手动刷新时才进行数据填充。这个选项对应的是CREATE 语句的DATA INITIALLY IMMEDIATE 和DATA INITIALLY DEFERRED子句，他们分别用于指定MQT在创建时或刷新时填充数据。当DATA INITIALLY DEFERRED被指定时，在刷新MQT前最好不要使用“启用查询优化”选项，这样做是为了保证MQT在用于查询时是有数据的。

下面，用户可以选择是否启用查询优化。如果用户想要让优化器在优化阶段考虑使用MQTs，需要勾选“启用查询优化”。这个复选框对应CREATE 语句的ENABLE/DISABLE QUERY OPTIMIZATION子句，并指明MQT是否能使用在优化其他查询的过程中。注：在System i导航器中找到表定义，或使用ALTER TABLE语句可以使能或关闭查询优化，也可以将已有的SQL表转换成MQT。

“显示SQL”按钮可以显示相应的CREATE TABLE的SQL语句：
CREATE SUMMARY TABLE ORDERS.YEAR_WEEK_DEPARTMENT_MQT AS (SELECT YEAR , WEEK , DEPARTMENT , SUM ( SALES ) SUM_SALES , COUNT ( DISTINCT ORDER_NO ) ORDER_NO
FROM ORDERS.ORDERS_TABLE
GROUP BY YEAR , WEEK , DEPARTMENT )
DATA INITIALLY DEFERRED
REFRESH DEFERRED
MAINTAINED BY USER
ENABLE QUERY OPTIMIZATION;

请注意其中的REFRESH DEFERRED和MAINTAINED BY USER这两个子句显示为必有的，但System i导航器会帮用户把这两项填上。它表明用户需要处理MQT的刷新与数据维护。目前MQT只有MAINTAINED BY USER这一个选项。点击“OK”就可以创建MQT了。这个YEAR_WEEK_DEPARTMENT_MQT表现在应该包含3900条数据。

优化器对MQT的使用

下一步的工作就是要去验证优化器是否会使用这个新创建的MQT。为了使优化器使用MQT, 定义MQT时的select语句必须包括待优化查询中所有的列。优化器也可以弥补查询和MQT定义的不同，例如，在只包含部分表的连接，不同的聚合，或使用比MQT更多的选择谓词的时候,查询仍然会使用MQT。

同时，如果查询涉及到的多个表都创建过MQT，优化器可能会在此查询中替代入多个MQT。这是优化器在重写查询的过程。
让我们回到图2中的SQL脚本，这是最常用的查询之一。当MQT被创建后，我们可以查看优化器是否会使用它。MQT是由用户进行维护，除非用户告知优化器去使用MQT，否则优化器是不会使用它们的。为了保证优化器使用MQT，必须在QAQQINI选项中更改以下选项：

• MATERIALIZED_QUERY_TABLE_USAGE —— 应该指定为*USER或者*ALL。
• MATERIALIZED_QUERY_TABLE_REFRESH_AGE ——一个用于指定MQT数据使用时间的时间戳。如果MQT没有REFRESH TABLE选项时时应指定为*ANY。

为了测试使用这些QAQQINI选项下的查询，需要从“运行SQL 脚本”进入“选项”下拉菜单并选择“更改查询属性”（图4）。

图4

你会发现这个运行SQL脚本的作业已经被选择了。 如果需要修改INI选项，首先需要选择在哪个库中存放我们的INI文件，然后点击"编辑选项"按键。需要注意的是，默认的整个系统的INI文件是存放在QUSRSYS库中的，请一定不要修改它的内容。编辑窗口会允许你将MQT的默认值修改为*USER或*ANY。还要注意的是，当关闭对话框或者切换到另一行时，对INI文件进行的修改会自动进行保存。然后退出“编辑选项”窗口并且在改变查询属性对话框中选择“确定”。

现在查询所需的INI文件已经设置好了，你可以使用“说明”按键区执行声明并查看优化器是否会使用我们创建的MQT。图5中展示了最新优化后的Visual Explain对话框，我们可以看到MQT已经被使用并且查询时间也减少了。如果这个展示的画面很大的话，我们可以查看下拉菜单并且选择突出显示具体化查询表来高亮图中的MQT。

图5

使用MQT的好处

现在我们了解了MQT对于应用程序的作用，也在产品数据库中创建了它，这里我们可以通过另一个工具来告诉我们MQT在多大程度上被使用了。
为了做到这一点，我们要从MQT基于的基础表开始。从应用程序模式表文件夹中找到表，右击并选择“显示MQT”。图6显示了这张表对应的MQT，更重要的是，MQT的使用情况也被显示了出来。我们可以通过“上一次查询使用”和“上一次查询使用计数”功能来查看MQT是如何被使用的。这看上去是一次成功的MQT部署。DB2引擎现在可以使用MQT通过关键字去查找一列。如果只使用基础表，DB2 for i5/OS引擎需要查找成千上万的列去定位一个给定的部门并执行聚合。

图6

由于MQT在没有刷新的情况下本身只包含静态数据，用户需要在MQT对应的基础表发生变化时（如插入，更新或删除）去刷新或维护MQT数据。这可以通过以下的方法去实现，右击表文件夹下的MQT，选择数据菜单，点击“刷新表”，或者执行REFRESH TABLE语句。刷新操作清除并运行MQT查询来重新注入MQT数据。另外，还可以执行插入，更新或删除语句来直接改变MQT的数据。刷新MQT的策略需要考虑基础表和MQT间数据差异的承受程度，数据量的改变情况以及MQT查询所需要的时间。

在MQT刷新时还需要考虑它的性能。由于刷新操作是去执行之前定义好的查询语句，这种查询和直接查询基础表是属于同一种查询优化方式，因此需要在MQT定义的基础表上创建合适的索引。我们可以在V5R4上使用索引顾问创建对刷新操作有帮助的索引，要使用这一功能，需要在数据库或特定表上右击并选择“索引顾问程序”（图7）。

图7
 

还有另一个关于MQT性能的要素，那就是可以在MQT本身创建索引。这种和普通表一样的索引策略，甚至会向优化器提供更多信息去提高性能，这种一般用于MQT表数据量很大并且/或者连接了其他表的情况。图5中的Visual Explain图表就表现了表扫描的使用情况，这只是在基础表上创建索引去提高MQT性能的一个例子。直接在MQT表上创建索引的语法和普通的语法并没有区别，都是使用CREATE INDEX语法。

以下是一个创建索引的例子：
CREATE INDEX YEAR_WEEK_DEPARTMENT_MQT_IX ON YEAR_WEEK_DEPARTMENT_MQT (DEPARTMENT);

为了帮助我们去决定索引是否有创建的价值，可以使用MQT的“索引顾问程序”。在数据库中查找到MQT表，右击并选择“索引顾问程序”，这个功能会提供优化器建议创建的索引以提高性能。

成功的关键

本文主要讲述了如何在数据库表上定义MQT去提高性能。由于MQT的核心是我们定义的查询，因此我们也可以在包含连接语句的复杂查询中使用MQT。通过正确的MQT和索引策略，我们可以使查询时间以及用户对应用程序的采用变得大不一样。更详细的关于MQT的内容，请参阅 “Creating and using materialized query tables in IBM DB2 for i5/OS”。
 

 
作者：Jim Flanagan，Theresa Euler
翻译：彭哲华，苗雨