MQT介绍

DB2 MQT(Materialized Query Table)物化查询表是提高查询性能的有效手段，广泛应用在数据仓库和大数量的报表查询系统中。MQT也是高级DBA认证的一个重要考点，本文从应用场景入手，介绍MQT的语法，并通过实例演示具体用法。

MQT，或者叫summary table，stage table，提供了强有力的方法来提高复杂查询的响应时间，特别是对包含如下操作的查询：对一个或多个维度的聚合操作，如SUM,Count等；在一组表之间的join和聚合操作。MQT的基本原理是对以上特别需要耗费资源的操作事先计算出来并保存到单独的表里，这样当查询的时候优化器就会引导相关查询到MQT中查找，由于MQT表里面已经包含了需要聚集和join的数据，数据量有了大幅度减少，因此能够大大提高性能。

搭建示例 

为了演示方便，我们创建一个Customer_Order表，里面包含了10年的客户订单的交易数据，大概30万行记录。（其中order_status有'Y'和'N'两种状态，比例为1：4， trans_dt的范围为1999-01-01到2008-12-29日，表中数据都是根据某个范围自动生成的）

--首先创建一个DMS表空间

CREATE TABLESPACE tbsp1 managed by database using (file 'd:\db2\dms\cont' 200M ) ;

--创建customer_order基本表

CREATE TABLE test.customer_order (
 order_id int not null,
 customer_id int not null,
 product_name varchar(10),
 amount int,
 order_status char(1),
 trans_dt date
  )
 IN tbsp1 ;

--在trans_dt交易日期上创建索引

CREATE INDEX test.cust_order_idx ON tpcd.customer_order(trans_dt) CLUSTER;

 

--向基本表中插入300000行数据，可根据需要自己调整
insert into test.customer_order

with temp(counter, order_id, customer_id,product_name,amount, order_status,trans_dt) as
( values(1, 1, int(rand()*1000), 'product' concat char(mod(int(rand()*1000),5)) , mod(int(rand()*10000),999), case 1 when 1 then 'Y' else 'N' end, date(rtrim(char(mod(int(rand()*1000),10)+1999)) concat '-' concat rtrim(char(mod(int(rand()*1000),12)+1)) concat '-' concat rtrim(char(mod(int(rand()*1000),28)+1) )) )
  union all
  select counter+1, counter+1, int(rand()*1000), 'product' concat char(mod(int(rand()*1000),5)) , mod(int(rand()*1000),25), case mod(counter+1,5) when 1 then 'Y' else 'N' end, date(rtrim(char(mod(int(rand()*1000),10)+1999)) concat '-' concat rtrim(char(mod(int(rand()*1000),12)+1)) concat '-' concat rtrim(char(mod(int(rand()*1000),28)+1) ))  from temp
  where counter+1<300001
)
select order_id, customer_id,product_name,amount, order_status,trans_dt from temp;

接下来我们演示对某些复杂查询，不采用MQT和采用MQT的性能影响。

 

不采用MQT的查询

现在假设某个应用要经常对客户订单的数量和状态进行分组统计，但只查询2007年度的数据。最常用的是如下两条SQL语句，第一条是根据交易时间分组统计，第二条是根据订单状态进行分组统计。

SELECT SUM(AMOUNT), TRANS_DT FROM test.CUSTOMER_ORDER WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007' GROUP BY TRANS_DT

SELECT SUM(AMOUNT), order_STATUS FROM test.CUSTOMER_ORDER WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007' GROUP BY order_STATUS

执行这两条SQL，并收集explain的信息，发现获得336行数据返回，其Cost却高达4040(timeron)。详细数据可参看表1的对比（由于sohu博客不支持bmp图片，保存成jpg格式有数据显示失真）

 

                                   表1. 几种SQL语句应用MQT和不用MQT的性能对比

 

采用MQT的查询

观察以上两条SQL语句，发现尽管Customer_Order中包含了10年的数据，但应用只对2007年度的数据进行统计，因此可以创建如下的MQT表，根据交易时间和订单状态进行分组计算，只包含2007年的分组数据。

CREATE TABLE mqt.summary_cust_order as

 ( select sum(amount) as amount, order_status, trans_dt from test.customer_order

   where TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007'

   group by trans_dt,order_status )

 data initially deferred refresh deferred

 IN tbsp1 ;

data inintally deferred是指当create MQT table的时候不会往表里插数据,用户需要发出refresh table命令。refresh deferred是指当对基表进行更改的时候并不对MQT进行实时更新，当用户发出refresh table的时候才会将基表数据统计到MQT表中。refresh deferred对应的的refresh immediate指的是当对基表数据更新的时候实时更新MQT数据，后面还会讲到这两种差别。

 

创建完MQT表后，用户需要发出refresh table命令加载数据

refresh table mqt.summary_cust_order

通过select count(*) from  mqt.summary_cust_order,发现里面包含672条数据。

这时，我们再去执行前面两条SQL语句，通过Explain plan访问计划可以看到查询已经重写到MQT表，优化器会自动选择MQT作为查询表，由于访问的行数只有672行，因此estimated cost 只有32（timeron)，性能达到明显改善。基表的数据量越大，MQT的优势就越明显。

以下是创建了MQT后的查询访问计划,通过Optimized Statement可知，对Customer_Order的访问已变为对SUMMARY_CUST_ORDER的访问，这种查询重写由优化器决定，对应用来说是透明的。

SELECT SUM(AMOUNT), TRANS_DT FROM test.CUSTOMER_ORDER WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007' GROUP BY TRANS_DT

Total Cost:   32.119

Estimated Returned rows: 336

Original Statement:
------------------
SELECT SUM(AMOUNT), order_STATUS
FROM test.CUSTOMER_ORDER
WHERE TRANS_DT BETWEEN '1/1/2007' AND '12/31/2007'
GROUP BY order_STATUS

Optimized Statement:
-------------------
SELECT Q3.$C1, Q3.$C0 AS "ORDER_STATUS"
FROM
   (SELECT Q2.$C0, SUM(Q2.$C1)
   FROM
      (SELECT Q1."ORDER_STATUS", Q1."AMOUNT"
      FROM MQT.SUMMARY_CUST_ORDER AS Q1) AS Q2
   GROUP BY Q2.$C0) AS Q3

Refresh deferred和Refresh Immediate

前面我们举例说明了refresh deferred（延迟更新）,即当对基表数据进行更改的时候并不会对MQT表数据进行实时统计和计算，只有当用户发出 refresh table的时候才会加载数据。这对于数据查询同步要求不高的时候是很高的方案，但某些情况下，应用要求立即看到同步的数据，即当基表数据进行增删改的时候，其结果需要理解反应到MQT表中，不需要用户干预，这就是Refresh Immediate。但需要指出的是，refresh immediate在同步统计数据的同时，也会付出一些代价的，如：1. 会影响基表insert/update/delete的时间，因为数据要同步到MQT中；2.在对基表操作的时候，需要对MQT加锁。

在表1，统计了插入一条SQL语句在refresh deferred和refresh immediate两种情况下的执行效率，可以发现在使用refresh immediate的时候cost高达64(timeron), 而refresh deferred只有7.6，差距还是比较大的。因此，对于实时性要求不高的数据仓库和报表系统来说，可优先考虑Refresh deferred.

Current Refresh Age注册变量

特别需要注意的是存在一个current refresh age注册变量，DB2优化器根据这个值来决定是否选择MQT，这个变量可以是0或99999999999999（或ANY)。current refresh age的初始值是0，可以通过set current refresh age <>来设置。

1.当采用refresh immediate时，如果current refresh age是缺省值（为0），DB2优化器会优先考虑是否执行MQT表。

2.当采用refresh deferred时，如果current refresh age=0，那么优化器一般不会考虑用MQT。只有当current refresh age=ANY（或99999999999999)时，DB2优化器才会考虑执行MQT表。但设成ANY时要特别小心，因为MQT表的数据和base的数据不同步，产生的结果会有差异。

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

转载于:http://blog.itpub.net/23577591/viewspace-704164/