您现在的位置：计算机技术学习网 > 技术中心 > 数据库技术 > DB2数据库 >

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

来源：未知责任编辑：智问网络发表时间:2013-08-28 11:49　点击:次

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

p>物化查询表（MQT）是以一次查询的结果为基础定义的表，可以显著提高查询的性能尤其是提高复杂查询的性能，在数据仓库等大型系统中有着广泛的应用。要使 MQT 正常工作必须对其进行刷新，刷新的方式有多种，每一种方式其内部机制也各不相同，相应的其性能也有差别。

p>在本文中，我们将利用 DB2 说明（explain）工具获取 MQT 刷新时相应的访问方案（access plan），通过分析访问方案来理解刷新机制，从而使读者能够在实际应用中合理的选择刷新方式，更大程度的发挥 MQT 在性能提高方面的优势。

p>MQT 刷新方式

p>MQT 可以分为两种类型，一种是系统维护的 MQT，一种是用户维护的 MQT，其中前者有着广泛的应用，因此在本文只讨论系统维护的 MQT。

p>对于系统维护的 MQT 的刷新方式，根据刷新时间的不同可以分为两种，一种是 REFRESH IMMEDIATE 也就是即时刷新，一种是 REFRESH DEFERRED 也就是延迟刷新。

p>使用即时刷新的方式，当对基表进行任何 insert/update/delete 等操作时，MQT 中的数据也自动的进行相应的刷新，其优点是能保证 MQT 中的数据总是最新的，但是由于对基表进行操作的同时还需要维护 MQT，负载有所增加。

p>使用延迟刷新的方式，当对基表进行任何 insert/update/delete 等操作时，MQT 中的数据没有进行相应的刷新，而是等到用户（DBA）阶段性的执行刷新命令时才进行刷新。这种方式适合用在绝大部分时间都是只读的应用系统中，或者数据的更新只发生维护窗口时间。

p>MQT 刷新方式还可以从数据刷新范围的角度分成两类，一类是完全刷新，一类是增量刷新。

p>完全刷新方式是将基表中的所有数据重新进行计算和处理从而更新 MQT 的数据。这种方式的缺点是当基表的数据量大而且 MQT 定义复杂时，刷新过程可能会比较慢。

p>增量更新方式是针对 insert/update/delete 等操作更新过的基表中的那一部分数据，对 MQT 中相应数据进行刷新，而不需要访问基表中所有数据。

p>将两种分类方法结合在一起讨论，即时刷新方式必然是增量刷新方式，延迟刷新方式采用的是完全刷新，但不是所有的延迟刷新方式都是完全刷新，有一种特殊的延迟刷新即“使用登台表（staging table）的延迟刷新”采用的是增量更新。当 MQT 的基表被修改时，变化就会传播过来，并立即被添加到 staging 表中，这样就可以利用 staging 表增量刷新，而不是从头开始重新生成 MQT，从而可以显著提高性能。当刷新操作完成时，staging 表中的数据就会被删除。

p>接下来我们对以上几种刷新方式，通过其访问方案来进行深入的讨论。

p>准备工作

p>我们先创建一个新的数据库名字叫做 MYDB，或者也可以使用一个已有的数据库。本文中所有操作都是在 LinuxAMD64 平台上的 DB2 9.7 版本进行，在其他的版本上也可以得到相似的结果，但不能保证完全一致。

p>连接到数据库之后，创建说明表（explain tables），使用的命令如清单 1 所示。

p>清单 1. 创建数据库，说明表，基表

p> db2start

p> db2 CREATE DB MYDB

p> db2 CONNECT TO MYDB

p> db2 – tvf ~/sqllib/misc/EXPLAIN.DDL

p>对于不同的平台如 Windows 平台或者安装路径的不同，EXPLAIN.DDL 文件的位置会有相应的不同，但都是在 sqllib 目录中。

p>REFRESH IMMEDIATE 方式

p>创建一个基表并插入若干数据，然后创建一个即时刷新的 MQT，使用的语句如清单 2 所示。

p>清单 2. 创建即时刷新的 MQT

p> create table basetable

p> (c1 int not null primary key, c2 int, c3 int, c4 int);

p> insert into basetable

p> values(1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11);

p> create table mqttab as

p> (select c1, c2, c3 from basetable where c1 > 10)

p> data initially deferred refresh immediate;

p> set integrity for mqttab immediate checked not incremental;

p>在清单 2 中创建一个 MQT 名字是 mqttab，其数据是基表中 c1>10 的行并只取每一行的前三个 column。需要说明一下的是，这个 MQT 非常的简单甚至于没有任何应用的价值，但是已经可以满足本文中所讨论问题的需求，这里我们的目标是探讨 MQT 的刷新机制，为了便于理解我们没有使用总结表等类型的 MQT。

p>上文中提到即时刷新的 MQT 能够始终保证数据都是最新的，也就是在任何对基表的 insert/update/delete 等操作时，MQT 中的数据也自动的进行相应的刷新。接下来我们设置 CURRENT EXPLAIN MODE，并向基表 basetable 中插入一条数据，这条数据满足 c1>10，然后来看这个操作的访问方案，使用的命令和访问方案如清单 3 所示。

p>清单 3. 基表的 insert 操作时的访问方案

p> db2 set current explain mode explain

p> db2 "insert into basetable values(12,12,12,12)"

p> db2exfmt -d MYDB -1 -e -o insert1.exfmt

p> db2 set current explain mode no

p> Rows

p> RETURN

p> ( 1)

p> Cost

p> I/O

p> |

p> 1

p> INSERT

p> ( 2)

p> 16.1648

p> 2

p> /---+---\

p> 1 1

p> INSERT TABLE: HQY

p> ( 3) MQTTAB

p> 8.0848 Q1

p> 1

p> /---+---\

p> 1 4

p> TBSCAN TABLE: HQY

p> ( 4) BASETABLE

p> 0.0048 Q4

p> 0

p> |

p> 1

p> TABFNC: SYSIBM

p> GENROW

p> 2) INSERT: (Insert)

p> Input Streams:

p> -------------

p> 4) From Operator #3

p> Estimated number of rows: 1

p> Number of columns: 3

p> Subquery predicate ID: Not Applicable

p> Column Names:

p> ------------

p> +Q6.$C0+Q6.$C1+Q6.$C2

p>在清单 3 的访问方案中我们可以看到，DB2 数据库管理器在执行这条 insert 语句的时候，不但向基表 basetable 中插入了数据（INSERT 操作符 3），而且同时向 MQT mqttab 执行了插入操作（INSERT 操作符 2），这就充分说明了采用即时刷新方式时对 MQT 的刷新是与基表的操作同时完成的。另外在 INSERT 操作符 2 的 detail 中可以看到 Estimated number of rows: 1，说明对于 MQT 的刷新是增量刷新。

p>需要提醒一下的是，由于是在 explain mode 下使用了插入操作，但实际上该插入操作并没有真正执行而只是生成了访问方案，所以如果此时查询 MQT 会发现无法找到这条新数据，下文中的各项操作都是与此类的情况。

p>接下来我们来看对基表的 update 和 delete 操作时的访问方案，使用的命令如清单 4 所示。

p>清单 4. 基表的 update 和 delete 操作

p> db2 set current explain mode explain

p> db2 "update basetable set c2=100 where c1=11"

p> db2exfmt -d MYDB -1 -e -o update1.exfmt

p> db2 "delete from basetable where c1=11"

p> db2exfmt -d MYDB -1 -e -o delete1.exfmt

p> db2 set current explain mode no

p>观察对基表的 update 和 delete 操作时的访问方案，我们会发现与清单 3 中 insert 操作时的访问方案相似，都是在对基表完成 update 或 delete 操作的同时相应的完成对 MQT 的刷新操作。读者可以在本文最后的下载链接中找到 update1.exfmt 和 delete1.exfmt 中的访问方案，限于篇幅不在正文部分显示。

p>最后，我们来看一下当用户手动的刷新 MQT 时其执行方案，使用的命令和访问方案如清单 5 所示。

p>清单 5. 手动刷新采用即时更新方式的 MQT

p> db2 set current explain mode explain

p> db2 "refresh table mqttab"

p> db2exfmt -d MYDB -1 -e -o refresh1.exfmt

p> db2 set current explain mode no

p> Rows

p> RETURN

p> ( 1)

p> Cost

p> I/O

p> |

p> 1

p> TBSCAN

p> ( 2)

p> 0.0048

p> 0

p> |

p> 1

p> TABFNC: SYSIBM

p> GENROW

p>在清单 5 的执行计划可以看到，对于采用即时刷新方式的 MQT，当用户手动刷新时并没有对该 MQT 进行任何操作。访问方案中的 SYSIBM.GENROW 是一个内置函数，它可生成行的表而不使用任何输入，它可以用来生成数据行，然后 TBSCAN 操作符读取数据行。

p>至此，我们已经通过访问方案理解了 MQT 的即时刷新方式，接下来我们继续讨论 MQT 的延迟刷新方式。

p>REFRESH DEFERRED 方式（无登台表，即完全刷新）

p>创建一个基表并插入若干数据，然后创建一个延迟刷新的 MQT，这里基表和 MQT 的表结构与之前的相同，这样也便于我们比较不同刷新方式的异同。使用的语句如清单 6 所示。

p>清单 6. 创建延迟刷新的 MQT

p> create table basetab2

p> (c1 int not null primary key, c2 int, c3 int, c4 int);

p> insert into basetab2

p> values(1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11);

p> create table mqttab2 as

p> (select c1, c2, c3 from basetab2 where c1 > 10)

p> data initially deferred refresh deferred;

p> set integrity for mqttab2 immediate checked not incremental

p>本文开始部分提到，对于延迟刷新方式，当对基表进行任何 insert/update/delete 等操作时，MQT 中的数据没有进行相应的刷新，而是等到用户手动的执行刷新命令时才进行刷新。现在我们设置 CURRENT EXPLAIN MODE，并向基表 basetab2 中插入一条数据，查看其访问方案，使用的命令及得到的访问方案如清单 7 所示。

p>清单 7. 基表的 insert 操作时的访问方案

p> db2 set current explain mode explain

p> db2 "insert into basetab2 values(12,12,12,12)"

p> db2exfmt -d MYDB -1 -e -o insert2.exfmt

p> db2 set current explain mode no

p> Rows

p> RETURN

p> ( 1)

p> Cost

p> I/O

p> |

p> 1

p> INSERT

p> ( 2)

p> 8.0848

p> 1

p> /---+---\

p> 1 4

p> TBSCAN TABLE: HQY

p> ( 3) BASETAB2

p> 0.0048 Q3

p> 0

p> |

p> 1

p> TABFNC: SYSIBM

p> GENROW

p>我们发现清单 7 中访问方案就是一个普通的 insert 操作，只对基表 basetab2 进行了插入操作，并没有对 MQT mqttab2 进行任何的操作，这就可以说明采用延迟更新方式的 MQT 在基表有变化时并没有即时刷新。

p>接下来我们来看对基表的 update 和 delete 操作时的访问方案，使用的命令如清单 8 所示。

p>清单 8. 基表的 update 和 delete 操作

p> db2 set current explain mode explain

p> db2 "update basetab2 set c2=100 where c1=11"

p> db2exfmt -d MYDB -1 -e -o update2.exfmt

p> db2 "delete from basetab2 where c1=11"

p> db2exfmt -d MYDB -1 -e -o delete2.exfmt

p> db2 set current explain mode no

p>同样的我们可以发现，对基表的 update 和 delete 操作时的访问方案也没有对 MQT 的刷新操作。读者可以在本文最后的下载链接中找到 update2.exfmt 和 delete2.exfmt 中的访问方案，限于篇幅不在正文部分显示。

p>最后，我们来看一下当用户手动的刷新 MQT 时其执行方案，在刷新之前我们首先向基表中插入了几条数据并修改了部分已有数据，使用的命令和访问方案如清单 9 所示。

p>清单 9. 手动刷新采用延迟更新方式的 MQT

p> db2 "insert into basetab2 values(12,12,12,12)"

p> db2 "insert into basetab2 values(13,12,12,12)"

p> db2 "insert into basetab2 values(14,12,12,12)"

p> db2 "insert into basetab2 values(15,12,12,12)"

p> db2 "update basetab2 set c2=100 where c1=11"

p> db2 runstats on table hqy.basetab2

p> db2 set current explain mode explain

p> db2 "refresh table mqttab2"

p> db2exfmt -d MYDB -1 -e -o refresh2.exfmt

p> db2 set current explain mode no

p> Rows

p> RETURN

p> ( 1)

p> Cost

p> I/O

p> |

p> 0.333333

p> FILTER

p> ( 2)

p> 35.9682

p> 4

p> +------------+------------------+-----+-------------------------+

p> 1 0 1.33333 1

p> TBSCAN FILTER INSERT DELETE

p> ( 3) ( 4) ( 6) ( 9)

p> 0.0048 0.0404 17.979 17.8015

p> 0 0 2 2

p> | | /---+----\ /---+----\

p> 1 1 1.33333 1 1 1

p> TABFNC: SYSIBM TBSCAN FETCH TABLE: HQY TBSCAN TABLE: HQY

p> GENROW ( 5) ( 7) MQTTAB2 ( 10) MQTTAB2

p> 0.0048 9.89896 Q6 9.72148 Q10

p> 0 1 1

p> | /---+----\ |

p> 1 1.33333 4 1

p> TABFNC: SYSIBM IXSCAN TABLE: HQY TABLE: HQY

p> GENROW ( 8) BASETAB2 MQTTAB2

p> 1.73403 Q7 Q11

p> 0

p> |

p> 4

p> INDEX: SYSIBM

p> SQL091124002956410

p> Q7

p>在清单 11 中我们得到了一个与清单 5 中完全不同而且比较复杂的访问方案，在本文中不会详细解释该访问方案的具体细节，通过观察发现在这个访问方案中对 MQT mqttab2 执行了 INSERT 操作（操作符 6）和 DELETE 操作（操作符 9），其中 INSERT 操作的输入是 FETCH（操作符 7）也就是从基表 basetab2 中获取数据并插入到 mqttab2 中，DELETE 操作的输入是 TBSCAN( 操作符 10) 也就是从 MQT mqttab2 中找到相应的数据进行删除。这个过程就是根据基表的最新数据向 MQT 中插入新数据或者删除无效的数据，这也是为什么只有 INSERT 操作和 DELETE 操作而没有 UPDATE 操作。

p>至此，我们已经通过访问方案理解了 MQT 的延迟刷新方式，接下来我们继续讨论当有登台（staging）表时的 MQT 延迟刷新方式。

p>有登台表的 REFRESH DEFERRED 方式

p>创建一个基表并插入若干数据，然后创建一个延迟刷新的 MQT，并创建一个登台表，基表和 MQT 的表结构与上文相同，差别在于这里创建了登台表 mqttab3_stg，也由此需要多执行一条 SET INTEGRITY 命令，使用的语句如清单 10 所示。

p>清单 10. 创建延迟刷新的 MQT 并创建登台表

p> create table basetab3

p> (c1 int not null primary key, c2 int, c3 int, c4 int);

p> insert into basetab3

p> values(1,1,1,1),(2,2,2,2),(3,3,3,3),(11,11,11,11);

p> create table mqttab3 as

p> (select c1, c2, c3 from basetab3 where c1 > 10)"

p> data initially deferred refresh deferred;

p> create table mqttab3_stg for mqttab3 propagate immediate;

p> set integrity for mqttab3 materialized query immediate unchecked;

p> set integrity for mqttab3_stg staging immediate unchecked;

p> db2 describe table hqy.MQTTAB3_STG

p> Data type Column

p> Column name schema Data type name Length Scale Nulls

p> ---------- ------- ----------- ------- ----- ------

p> C1 SYSIBM INTEGER 4 0 No

p> C2 SYSIBM INTEGER 4 0 Yes

p> C3 SYSIBM INTEGER 4 0 Yes

p> GLOBALTRANSID SYSIBM CHARACTER 8 0 No

p> GLOBALTRANSTIME SYSIBM CHARACTER 13 0 No

p> OPERATIONTYPE SYSIBM INTEGER 4 0 No

p> 6 record(s) selected.

p>在清单 10 的最后描述了登台表 mqttab3_stg 的表结构，可以看到登台表中包括了 MQT mqttab3 的所有的列，也就是说登台表能够捕捉基表的变化并保存更新 MQT 所需的所有数据。

p>我们来看一下，同样是延迟更新，有了登台表之后会有什么样的变化。设置 CURRENT EXPLAIN MODE，向基表 basetab3 中插入一条数据然后来看这个操作的访问方案，使用的命令和得到的访问方案如清单 11 所示。

p>清单 11. 基表的 insert 操作时的访问方案

p> db2 set current explain mode explain

p> db2 "insert into basetab3 values(12,12,12,12)"

p> db2exfmt -d MYDB -1 -e -o insert3.exfmt

p> db2 set current explain mode no

p> Rows

p> RETURN

p> ( 1)

p> Cost

p> I/O

p> |

p> 1

p> INSERT

p> ( 2)

p> 16.236

p> 2

p> /---+---\

p> 1 58

p> INSERT TABLE: HQY

p> ( 3) MQTTAB3_STG

p> 8.0848 Q1

p> 1

p> /---+---\

p> 1 1000

p> TBSCAN TABLE: HQY

p> ( 4) BASETAB3

p> 0.0048 Q4

p> 0

p> |

p> 1

p> TABFNC: SYSIBM

p> GENROW

p>在清单 11 中我们得到了一个与清单 7 完全不同却与清单 3 十分类似的访问方案。清单 3 中的 MQT 采用即时刷新方式，对基表 insert 操作时同时向 MQT mqttab 进行了操作，而在清单 11 中的 MQT 采用的是延迟刷新方式，在访问方案中唯一的差别是其没有同时更新 MQT，而是同时对登台表进行了操作。

p>接下来我们来看对基表的 update 和 delete 操作时的访问方案，使用的命令如清单 12 所示。

p>清单 12. 基表的 update 和 delete 操作

p> db2 set current explain mode explain

p> db2 "update basetab2 set c2=100 where c1=11"

p> db2exfmt -d MYDB -1 -e -o update2.exfmt

p> db2 "delete from basetab2 where c1=11"

p> db2exfmt -d MYDB -1 -e -o delete2.exfmt

p> db2 set current explain mode no

p>同样的我们可以发现，对基表的 update 和 delete 操作时的访问方案也没有对 MQT 的刷新操作，而是对登台表进行了相应的操作。读者可以在本文最后的下载链接中找到 update3.exfmt 和 delete3.exfmt 中的访问方案，限于篇幅不在正文部分显示。

p>最后，我们来看一下当用户手动的刷新 MQT 时其执行方案，使用的命令和访问方案如清单 13 所示。

p>清单 13. 手动刷新采用延迟更新方式并有登台表的 MQT

p> db2 "insert into basetab3 values(12,12,12,12)"

p> db2 "insert into basetab3 values(13,12,12,12)"

p> db2 "insert into basetab3 values(14,12,12,12)"

p> db2 "insert into basetab3 values(15,12,12,12)"

p> db2 "update basetab3 set c2=100 where c1=11"

p> db2 runstats on table hqy.basetab3

p> db2 set current explain mode explain

p> db2 "refresh table mqttab3"

p> db2exfmt -d MYDB -1 -e -o refresh3.exfmt

p> db2 set current explain mode no

p> Rows

p> RETURN

p> ( 1)

p> Cost

p> I/O

p> |

p> 0.333333

p> FILTER

p> ( 2)

p> 514.308

p> 61.0015

p> +------------+------+-------------------+

p> 1 0 8.02174e-07

p> TBSCAN FILTER INSERT

p> ( 3) ( 4) ( 6)

p> 0.0048 0.076 514.124

p> 0 0 61.0015

p> | | /----+-----\

p> 1 1 8.02174e-07 1000

p> TABFNC: SYSIBM TBSCAN FILTER TABLE: HQY

p> GENROW ( 5) ( 7) MQTTAB3

p> 0.0048 506.044 Q6

p> 0 60.0015

p> | |

p> 1 0.000501359

p> TABFNC: SYSIBM DELETE

p> GENROW ( 8)

p> 506.044

p> 60.0015

p> /----+-----\

p> 0.000501359 1000

p> UPDATE TABLE: HQY

p> ( 9) MQTTAB3

p> 506.04 Q7

p> 60.001

p> /----+-----\

p> 0.000501359 1000

p> FETCH TABLE: HQY

p> ( 10) MQTTAB3

p> 506.036 Q8

p> 60.0005

p> /----+-----\

p> 0.000501359 1000

p> TBSCAN TABLE: HQY

p> ( 11) MQTTAB3

p> 505.995

p> 60

p> |

p> 0.000501359

p> TEMP

p> ( 12)

p> 504.729

p> 60

p> |

p> 0.000501359

p> >^NLJOIN

p> ( 13)

p> 504.271

p> 60

p> /----+-----\

p> 0.000501359 40

p> FILTER TBSCAN

p> ( 14) ( 20)

p> 491.357 144.813

p> 59 7

p> | |

p> 0.000501359 1000

p> FILTER TABLE: HQY

p> ( 15) MQTTAB3

p> 491.168 Q19

p> 59

p> |

p> 58

p> TBSCAN

p> ( 16)

p> 487.826

p> 59

p> |

p> 58

p> SORT

p> ( 17)

p> 487.184

p> 59

p> |

p> 58

p> DELETE

p> ( 18)

p> 482.288

p> 59

p> /---+----\

p> 58 58

p> TBSCAN TABLE: HQY

p> ( 19) MQTTAB3_STG

p> 13.6476 Q9

p> 1

p> |

p> 58

p> TABLE: HQY

p> MQTTAB3_STG

p> Q10

p>在清单 13 中我们看到了一个非常复杂的访问方案，限于篇幅不能详细的解释，简要的说，在这个访问方案中，首先读取了登台表 mqttab3_stg 并删除了登台表的数据（DELETE 操作符 18），接着用登台表中读取的数据与 MQT mqttab3 进行合并（NLJOIN 操作符 13）并放入临时表中（TEMP 操作符 12），然后根据临时表中的数据对 MQT mqttab3 依次进行了更新（UPDATE 操作符 9）、删除（DELETE 操作符 8）和插入（INSERT 操作符 6）。在整个过程中没有任何用到基表 basetab3 的操作，这就说明此时的刷新是完全基于登台表 mqttab3_stg 进行的延迟刷新。

p>至此，我们已经通过访问方案理解了 MQT 的即时刷新方式、延迟刷新方式以及有登台表的延迟刷新方式的特点和机制，读者还可以继续深入探讨其他情景，如定义复杂的 MQT（如使用 COUNT 和 GROUP BY），或者使用 LOAD 装载数据等情景的各种刷新方式。