DB2物化查询表（MQT）刷新机制


DB2物化查询表（MQT）刷新机制
 
物化查询表（MQT）是以一次查询的结果为基础定义的表，可以显著提高查询的性能尤其是提高复杂查询的性能，在数据仓库等大型系统中有着广泛的应用。要使 MQT 正常工作必须对其进行刷新，刷新的方式有多种，每一种方式其内部机制也各不相同，相应的其性能也有差别。
在本文中，我们将利用 DB2 说明（explain）工具获取 MQT 刷新时相应的访问方案（access plan），通过分析访问方案来理解刷新机制，从而使读者能够在实际应用中合理的选择刷新方式，更大程度的发挥 MQT 在性能提高方面的优势。
MQT 刷新方式
MQT 可以分为两种类型，一种是系统维护的 MQT，一种是用户维护的 MQT，其中前者有着广泛的应用，因此在本文只讨论系统维护的 MQT。
对于系统维护的 MQT 的刷新方式，根据刷新时间的不同可以分为两种，一种是 REFRESH IMMEDIATE 也就是即时刷新，一种是 REFRESH DEFERRED 也就是延迟刷新。
使用即时刷新的方式，当对基表进行任何 insert/update/delete 等操作时，MQT 中的数据也自动的进行相应的刷新，其优点是能保证 MQT 中的数据总是最新的，但是由于对基表进行操作的同时还需要维护 MQT，负载有所增加。
使用延迟刷新的方式，当对基表进行任何 insert/update/delete 等操作时，MQT 中的数据没有进行相应的刷新，而是等到用户（DBA）阶段性的执行刷新命令时才进行刷新。这种方式适合用在绝大部分时间都是只读的应用系统中，或者数据的更新只发生维护窗口时间。
MQT 刷新方式还可以从数据刷新范围的角度分成两类，一类是完全刷新，一类是增量刷新。
完全刷新方式是将基表中的所有数据重新进行计算和处理从而更新 MQT 的数据。这种方式的缺点是 当基表的数据量大而且 MQT 定义复杂时，刷新过程可能会比较慢。
增量更新方式是针对 insert/update/delete 等操作更新过的基表中的那一部分数据，对 MQT 中相应数据进行刷新，而不需要访问基表中所有数据。
将两种分类方法结合在一起讨论，即时刷新方式必然是增量刷新方式，延迟刷新方式采用的是完全刷新，但不是所有的延迟刷新方式都是完全刷新，有一种特殊的延迟刷新即“使用登台表（staging table）的延迟刷新”采用的是增量更新。当 MQT 的基表被修改时，变化就会传播过来，并立即被添加到 staging 表中，这样就可以利用 staging 表增量刷新，而不是从头开始重新生成 MQT，从而可以显著提高性能。当刷新操作完成时，staging 表中的数据就会被删除。
接下来我们对以上几种刷新方式，通过其访问方案来进行深入的讨论。
 
准备工作
我们先创建一个新的数据库名字叫做 MYDB，或者也可以使用一个已有的数据库。本文中所有操作都是在 LinuxAMD64 平台上的 DB2 9.7 版本进行，在其他的版本上也可以得到相似的结果，但不能保证完全一致。
连接到数据库之后，创建说明表（explain tables），使用的命令如清单 1 所示。
 
清单 1. 创建数据库，说明表，基表
 
 db2start 
 db2 CREATE DB MYDB 
 db2 CONNECT TO MYDB 
 db2 – tvf ~/sqllib/misc/EXPLAIN.DDL 
 
对于不同的平台如 Windows 平台或者安装路径的不同，EXPLAIN.DDL 文件的位置会有相应的不同， 但都是在 sqllib 目录中。
 
REFRESH IMMEDIATE 方式
创建一个基表并插入若干数据，然后创建一个即时刷新的 MQT，使用的语句如清单 2 所示。
 
清单 2. 创建即时刷新的 MQT
 
 create table basetable 
 (c1 int not null primary key, c2 int, c3 int, c4 int); 
 
 insert into basetable 
 values(1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11); 
 
 create table mqttab as 
 (select c1, c2, c3 from basetable where c1 > 10) 
 data initially deferred refresh immediate; 
 
 set integrity for mqttab immediate checked not incremental; 
 
在清单 2 中创建一个 MQT 名字是 mqttab，其数据是基表中 c1>10 的行并只取每一行的前三个 column。 需要说明一下的是，这个 MQT 非常的简单甚至于没有任何应用的价值，但是已经可以满足本文中所讨论问题的需求，这里我们的目标是探讨 MQT 的刷新机制，为了便于理解我们没有使用总结表等类型的 MQT。
上文中提到即时刷新的 MQT 能够始终保证数据都是最新的，也就是在任何对基表的 insert/update/delete 等操作时，MQT 中的数据也自动的进行相应的刷新。接下来我们设置 CURRENT EXPLAIN MODE，并向基表 basetable 中插入一条数据，这条数据满足 c1>10，然后来看这个操作的访问方案，使用的命令和访问方案如清单 3 所示。
 
清单 3. 基表的 insert 操作时的访问方案
 
 db2 set current explain mode explain 
 db2 "insert into basetable values(12,12,12,12)"
 db2exfmt -d MYDB -1 -e -o insert1.exfmt 
 db2 set current explain mode no 
  Rows
                  RETURN
                  (   1)
                   Cost
                    I/O
                    |
                     1
                  INSERT
                  (   2)
                  16.1648
                     2
                /---+---\
               1           1
            INSERT  TABLE: HQY
            (   3)      MQTTAB
            8.0848        Q1
               1
          /---+---\
         1           4
      TBSCAN  TABLE: HQY
      (   4)     BASETABLE
      0.0048        Q4
         0
        |
         1
 TABFNC: SYSIBM
      GENROW
 
 2) INSERT: (Insert) 
  Input Streams: 
  ------------- 
  4) From Operator #3 
 
 Estimated number of rows: 1
 Number of columns: 3 
  Subquery predicate ID: Not Applicable 
 
  Column Names: 
  ------------ 
  +Q6.$C0+Q6.$C1+Q6.$C2 
 
在清单 3 的访问方案中我们可以看到，DB2 数据库管理器在执行这条 insert 语句的时候，不但向基表 basetable 中插入了数据（INSERT 操作符 3），而且同时向 MQT mqttab 执行了插入操作（INSERT 操作符 2），这就充分说明了采用即时刷新方式时对 MQT 的刷新是与基表的操作同时完成的。另外在 INSERT 操作符 2 的 detail 中可以看到 Estimated number of rows: 1，说明对于 MQT 的刷新是增量刷新。
需要提醒一下的是，由于是在 explain mode 下使用了插入操作，但实际上该插入操作并没有真正执行而只是生成了访问方案，所以如果此时查询 MQT 会发现无法找到这条新数据，下文中的各项操作都是与此类的情况。
接下来我们来看对基表的 update 和 delete 操作时的访问方案，使用的命令如清单 4 所示。
 
清单 4. 基表的 update 和 delete 操作
 
 db2 set current explain mode explain 
 
 db2 "update basetable set c