频道栏目
读书频道 > 数据库 > 其他综合 > 高级进阶DB2(第2版)——内部结构、高级管理与问题诊断
2.4.7 MDC、数据库分区、MQT和表分区配合使用
2013-07-18 11:31:21     我来说两句
收藏   我要投稿
数据库内核是数据库系统稳定运行的心脏,DB2数据库内核庞大而复杂。本书从DB2内核组件入手,同时介绍了其与操作系统在进程、共享内存、信号量之间的关系。作者在本书中重点介绍了各个内部组件的层次与功能、内存...  立即去当当网订购
  在数据仓库中,事实表或历史表的大小是摆在设计人员和管理员面前的一个挑战。这些表通常包含数亿行数据,有时候甚至包含数千亿行数据。对于这种规模的表,主要关心以下几点:
●       查询性能
●       将大量新数据插入到这些表中
●       每月或每个季度删除大量过时的数据
  对于这样的特殊查询,除了单独用到我们上面所讲的表分区、数据库分区、MQT和MDC外,有时候还需要这几种技术相互配合以提供更高的性能,因为这几种技术可以相互补充。

1. 三个互补的CREATE TABLE选项

CREATE TABLE语句现在提供了3种方式来组织数据库表中的数据,如表2-6所示。
表2-6  使用CREATE TABLE语句
CREATE TABLE语句中的子句 DB2特性名称
DISTRIBUTE BY HASH DPF——数据库分区特性
ORGANIZE BY DIMENSION MDC——多维集群
PARTITION BY RANGE TP——表分区
 
 
您可以任意组合、使用这些子句,以达到期望的效果。表2-7总结了与这些特性相关的术语,此处用到的其他一些特性也包含在内。
表2-7  DB2特性术语
DB2特性名称 一部分的名称 用于分区数据的列 其他术语
数据分区特性(Data Partitioning
Feature,DPF)
数据库分区 分布键(distribution key) 在之前的版本中,分布键被称作分区键
 
 (续表)    
DB2 特性名称 一部分的名称 用于分区数据的列 其他术语
多维集群(MultiDimensional
Clustering,MDC)
单元格,由一些块组成 块索引
表分区(TP) 数据分区 表分区键  
 

2. 三种技术对比

  每种特性都为分组表中的数据提供了独特的方法,并对解决与事实表或历史表相关的需求有独特的作用。
  DPF是最老的特性,通过它可以将数据库分成多个数据库分区。每个数据库分区有它自己的一组计算资源,包括CPU和存储。在DPF环境中,根据CREATE TABLE语句中指定的分区键,表中的每个行被分布到一个分区上。当处理查询时,请求也相应被划分成多个部分,以便让各个数据库分区各自处理其负责的那些行。实际上,DPF是一种可伸缩特性。DPF可以通过增加数据库分区来提高处理能力。因此,随着表的增长,仍然可以保持较高的查询性能。这种能力常常被称作使用DB2的无共享架构提供线性的可伸缩性。
  DPF的作用不仅仅体现在表设计上,它还是调整和配置整个数据库系统的一种方法。现在已经有了关于配置那样的系统以取得最佳性能、可靠性和增长能力的建议实践。客户可以购买建议的硬件和软件以及配置,它们都放在称作BCU(Balanced Configuration Unit)的解决方案中。
  MDC是在 DB2 V8 中引入的,通过它可以在物理上将在多个维上具有类似值的行聚合在一起放到磁盘上。这种聚合能为常见分析性查询提供高效的 I/O。例如,对于 VCITEM (科目)='71005',VCBRNO(分行号)='厦门分行',并且 VCTRDT(交易日期) ='2012/09'的所有行,可以将它们存储在相同的存储位置,即所谓的块(block)。在 CREATE TABLE语句中定义维的时候,就为每种值的组合预留了存储空间。实际上,MDC 是一个能最大化查询性能的特性,对于数据仓库中常用的查询更是如此。这包括需要根据几个列中的值的组合选择行的查询。例如:
VCTRDT(交易日期)is between "2012/09/01" and "2012/09/31" AND VCITEM (科目)= '71005'AND VCBRNO (分行号)= '厦门分行'

TP是在DB2 V9中引入的,与MDC类似,它也可以将具有近似值的行存储在一起。但是,TP的以下特征是MDC所不具备的:
●       TP支持按照维将表分区成多个数据分区。一种常见的设计是为每个月的数据创建数据分区。MDC则支持定义多个维。
●       通过 TP,用户可以手动地定义每个数据分区,包括将被包括到那个分区的值的范围。MDC则为每种唯一的MDC维值组合自动定义一个单元格(并创建块来存储那个单元格的数据)。
●       每个TP分区是单独的数据库对象(不同于其他作为单个数据库对象的表)。因此,TP支持为TP表附加(attach)和卸除(detach)数据分区。卸除的分区成为常规表,而且必要时可以将每个数据分区放在它自己的表空间中。
  实际上,TP相对其他特性的优势在于为表添加或删除大量数据这个方面,即转入(roll-in)和转出(roll-out)。对于熟悉使用Union All View(UAV)来按日期对历史表分区的读者,TP可以作为一种功能相似但是更为高级的解决方案。表2-8对DPF、MDC、TP的特性做了简要对比。
表2-8  DPF、MDC和TP特性简要对比
特    性 如何组织数据 优    点
DPF 将行均匀地分布在多个数据库分区上 可伸缩性—— 随着数据库的增长增加计算资源(也就是数据库分区),用于高性能计算
MDC 将在多个维上具有近似值的行放在表中相同的物理位置,即所谓的块 查询性能—— 组织数据的方式有利于获得更快的检索速度,对于由多个谓词指定范围的查询尤其有效
TP 将所有行放在同一数据分区的一个指定范围的维中 数据移动—— 通过添加或删除整个数据分区,可以增加或删除大量数据
 

3. 3种技术的互补特性

这3种DB2的高级设计技术既是独立的,又是互补的。当设计表时,对每个特性的使用可以认为是独立的。例如:
●       是否使用MDC和TP对于决定DPF的分布键没有影响。
●       一个列是否被用作MDC维对于是否应该将它作为表分区键没有影响,反之亦然。每个决定都可以单独作出。
 
 
  每个特性的工作方式,例如索引方面,不会随着新的分区特性的引入而改变。例如,当引入MDC时,它的索引方面不会改变DPF在索引方面的工作方式。同样,当引入TP时,也不会改变DPF或MDC在索引方面的行为。在学习这些特性的时候,记住这一点有助于避免陷入困惑。
  通常来讲,一个特性不能用于解决数据库设计中与另外一个特性相关的不足或问题。值得注意的例子有:
●       TP 不能解决与DPF相关的问题,不管这个问题是DPF数据倾斜还是DPF中的管理活动速度变慢。不管有没有同时使用TP,DPF之前的补救方法仍然适用。
●       TP不能用于纠正不好的MDC设计。
  总之,如果存在与DPF、MDC或TP相关的问题,那么还是应该尝试适用于那个特性的解决方法。

4. 表设计

数据仓库中的事实表或历史表非常适合使用上述每种特性,如表2-9所示。
表2-9  事实表拥有适合使用DB2分区特性的特征
特    性 适合的表的特征 事实表的特征
DPF 大型表——大到无法仅依靠单独一组CPU
和I/O通道来处理
事实表是最大的数据库表。它们常常包含数亿行数据,有时候甚至包含数千亿行数据
MDC 结果集返回在多个维上具有近似值的行的查询 事实表(以及通常所说的数据仓库)是为支持这种类型的查询而设计的
TP 周期性地添加大量数据,然后在数据到期后又删除大量数据 在事实表中,常常是每天都添加新数据。通常每月或每个季度都要删除过时的数据
1) 表设计的经验法则
  对于DPF,在选择分布键时,应首选那种能使数据行均匀地分布在多个数据库分区上的列。当不具备这样的条件时,就会造成数据倾斜。这意味着一个或一些数据库分区承担了更重比例的表行,从而造成了性能瓶颈。具有很多不同值的列是较好的选择。还有一种考虑是选择能最大化连接性能的列。
  另一个DPF设计决定是数据库分区的数量。数据库分区的数量不算是表设计方面的考虑。实际上,它是整个系统设计上的考虑,需要根据整个数据库预期的原始数据大小和服务器硬件的能力来决定。很多系统需要的数据库分区不超过20个。但是,最大的系统可能需要更多的数据库分区。由于数据仓库有越来越大的趋势,数据库分区的数量有望增加。
  对于MDC,一个关键的决定是用哪些列作为MDC维。设计上的挑战是根据业务需求找到最佳的一组维和最佳的粒度,使得组织最大化,存储需求最小化。较好的选择是具有以下一部分或全部特征的列:
●       用于范围、等于或IN列表谓词
●       用于转入、转出或其他大规模的行删除
●       被GROUP BY或ORDER BY子句引用
●       外键列
●       星型数据库的事实表中JOIN子句中的列
●       粗粒度,也就是说不同的值很少的列
  典型的设计是用表示日期的列作为MDC维,再加上0到3个其他列作为其他维,例如VCITEM(科目)= '71005',VCBRNO(分行机构号)= '厦门分行'。
对于TP,设计决定包括选择用作表分区键的列和分区的数量。通常表分区键是基于时间的列。每个分区与每次转入的数据量相符。例如,每月转出数据的表,对于每个月的数据都有一个分区。对于TP,在设计时需要特别考虑的一点是,需要处理不在CREATE TABLE语句中定义的值范围内的行。
  对于需要每月根据VCTRDT(交易日期)转出(roll-out)数据的数据库,一种典型的设计是使用VCTRDT(交易日期)作为表分区键,并为每个月创建单独的分区。
  通常,有一个MDC维是基于时间的列,这样一来,同一列既可以用于MDC,又可以用于TP。MDC的粒度可以比TP数据库分区更细一些。
表2-10总结了以上内容。
表2-10  设计方面的经验法则总结
分区特性设计决定 经 验 法 则
DPF—— 用作分布键的列 首选是具有很多不同值的列
MDC—— 用作MDC维的列 一种典型的设计是选择一个表示日期的列,再加上0到3个其他列,例如VCITEM(科目)和VCBRNO(分行机构号)
TP—— 用作表分区键的列和分区的数量 选择一个基于时间的列,定义与每次转出的数据量相符的分区
2) 设计的例子
表2-11显示了一些典型的表设计的例子。Cmvca历史传票表代表关系数据仓库中的一个典型的表。Recent Cmvca历史传票表代表运营数据存储中的一个表,这种数据存储实际上是只有最近数据的数据仓库。
表2-11  表设计的例子
分区属性 Cmvca历史传票表 Recent Cmvca历史传票表
DPF—— 用作分布键的列 不采用数据库分区 不采用数据库分区
DPF—— 分区数量 - -
MDC—— 用作维的列 VCTRDT(交易日期Year+Month)=36月
VCITEM(科目)=112
VCBRNO(分行机构号)=30
VCTRDT(交易日期,Days)=90日
VCITEM(科目)=112
VCBRNO(分行机构号)=30
TP—— 用作表分区键的列和分区的数量 VCTRDT(交易日期),每个月一个分区 VCTRDT(交易日期),每个月一个分区
# of rows(1 million per day) 8亿条记录 7000万
# of columns 30 30
# of indexes 4 15
 
注:对于Recent Cmvca历史传票表上的MDC维,另一种设计方案是以更细的粒度定义VCTRDT(交易日期),例如每周或每天。更好的查询性能与增加的存储需求之间的平衡取决于数据和查询的特征。

5. MQT

  MQT和分区特性都是为在相同的情况下(数据仓库事实表或历史表)使用而设计的。因此,为了全面考虑如何使用此处提到的分区特性,需要解决涉及MQT情况下的一些特殊考虑。
  MQT是基于查询结果而定义的表。从另一种角度来看,MQT就像结果集存储在表中的视图。MQT可以减少涉及以下方面的复杂查询的响应时间:
●       基本表中针对数据的聚类(avg、sum、count、max和min)或计算
●       基本表的连接
●       一个或多个较大基本表中常被访问的一部分数据
  当基本表中的数据发生改变时,需要相应地更新MQT。MQT特性为适应各种运营需求而进行更新提供了多种选项。
 
 
1) 与分区特性的比较(参见表2-12)
表2-12  MDC、DPF、TP和MQT特性简要对比
特    性 如何组织数据 优    点
DPF 将行均匀地分布在多个数据库分区上 可伸缩性—— 随着数据库的增长增加计算资源(也就是数据库分区)
MDC 将在多个维上具有近似值的行放在表中相同的物理位置,即所谓的块 查询性能—— 组织数据的方式有利于获得更快的检索速度,对于由多个谓词指定范围的查询尤其有效
TP 将所有行放在同一数据分区的一个指定范围的维中 数据移动—— 通过添加或删除整个数据分区,可以增加或删除大量数据
MQT 将查询的结果存储在表中 查询性能—— 对于涉及较高代价的操作,例如复杂的连接和表扫描的查询,预先计算结果集并存储(物化)结果集
 
2) 与分区特性一起设计MQT
与分区特性一起设计MQT时,在设计上需要考虑以下几点:
●       可以在使用分区特性的任意组合的表上创建 MQT。例如,可以在一个或多个使用 MDC和TP的表上创建MQT。基本表上分区特性的使用不需要考虑将来是否会在这个表上创建MQT。然而,MQT的设计却可能受到基本表上使用的分区特性的影响。例如,如果基本表使用DPF进行分区,那么MQT的设计就应该考虑是否要在各个数据库分区上复制MQT。
●       MQT也可以使用分区特性。例如,可以使用MDC或TP对MQT分区。
3) 典型的设计
接下来进一步介绍前面的Recent Cmvca历史传票表的例子,下面是这些表上定义的一些 MQT:
●       MQT 1——每个分行机构每种科目每天的交易汇总
●       MQT 2——每个分行机构每天每月每年的交易汇总

6. 关键考虑:查询性能

1) 对这一考虑的描述
  现在我们来看看在评价和使用这些特性时关键的考虑角度:性能,尤其是通常的数据仓库业务的用户查询的性能。这些查询具有以下特征:
●       从事实表中选择在几个维上符合标准的行,这意味着需要将几个维表与事实表相连接。
●       使用分组或聚类函数,例如COUNT、GROUP BY和ORDER BY。
●       返回包括很多行的结果集,从数千行到数百万行。
●       这些查询是由用户或他们的BI工具生成的。这意味着这些查询在更多情况下是临时性的,事务处理系统中的性能测试和调优方法对于它们并不适合。
  虽然人们倾向于获得更快的性能,但最好还是说成更好的性能。谈到性能,就应该包括以下一些方面:
●       峰值查询执行性能。
●       查询执行性能的稳定性。
●       对于数据仓库中各种具有不同特征的工作负载的性能。
●       设计数据库以达到性能目标是否容易。
●       为达到性能目标需要付出的代价。
  与过去相比,现在在设计数据库以达到性能目标的过程中还需要考虑的一点是硬件的发展趋势。随着CPU处理能力的不断提升以及存储设备容量的不断扩大,I/O带宽是一个潜在的性能瓶颈。在这种环境下,I/O效率是设计时要关键考虑的一点。
上面我们讲解了每种设计技术(MQT、DPF、TP和MDC)对查询执行性能的作用。下面我们讲解这些技术如何影响转入和转出的性能。
2) DB2分区特性发挥的作用
  使用DPF可以提供更多的计算资源,从而对性能产生积极的影响。当DB2优化器为查询形成查询访问计划时,它将工作划分到多个数据库分区上,这些数据库分区是并行工作的。之后,再收集各个数据库分区上得到的结果,并返回给查询提交者。
  MDC对性能的贡献在于提高检索数据的效率。在多个维上具有近似值的数据存储在相同的位置,这使得I/O操作变得更高效,而I/O操作正是数据仓库中一个常见的瓶颈。而且,MDC的特性还包括块索引。在块索引中,对于每个块的数据(而不是每一行的数据)都有一个条目,这使得执行索引操作时有更高的效率。为了发挥它潜在的性能,必须为 MDC表设计一组最佳的(或者至少是够好的)维。MDC只对那些包括维列的查询有好处。MDC对于查询是完全透明的。最后,MDC在管理方面有两个值得注意的优点:
●       MDC块索引意味着需要的RID索引更少。管理方面的一个优点是用于索引的存储空间减少了。
●       由于新行是插在表中具有近似值的行附近的位置,因此数据仍然是聚合的,而不需要运行REORG实用程序。
  TP 通过分区排除来提高查询性能。例如,假设Cmvca历史传票表有36个分区,每个月对应一个分区。对于一个选择过去12个月的数据的查询,优化器知道不必扫描这12个月之外的其他分区的数据。这种分区排除既适用于索引扫描,也适用于表扫描。TP只对那些包括表分区键列的查询有利。

7. 关键考虑:插入新数据

1) 对这一考虑的描述
  将来自运营系统的新数据插入到数据仓库中的事实表,这个过程称作 ETL、摄取(ingest)、填充数据仓库或转入。下面的例子演示了可能遇到的各种情况。
例2-1  使用LOAD每日摄入数据。
●       在业务日结束后,运营系统中包含50万到两亿条记录的多个平面文件如期到来。
●       客户脚本使用DB2 LOAD实用程序将每个文件装载到事实表中。这是在每夜批处理窗口中当表离线的时候完成的。
●       下一个业务日一开始,查询这个表的用户就可以看到前一天的数据。
例2-2  使用insert的准实时摄取。
●       每过30分钟,一个包含1万到10万条记录的文件到来。
●       当该文件到来时,用户编写的程序将这些记录添加到staging表中,然后使用insert 语句将这些记录添加到事实表中。
例2-3  MQT刷新。
  在有MQT的时候,它们被看做将新数据添加到数据仓库这个过程中的一部分。比较特别的是,这种情况下可以使用MQT刷新策略。通常,ETL过程是手动地指定何时更新MQT,而不是让更新自动发生。
●       对于例2-1,很可能是在每夜更新完所有任务之后立即更新。
●       对于例2-2,MQT通常是每天更新一次。因此,即使底层的数据是周期性更新的,访问MQT的查询全天返回的也都是相同的结果。
2) DB2分区特性发挥的作用
  DB2分区特性对于转入会有帮助,但是有时候也会带来新情况,客户在转入过程中要适当考虑这一点。
  通过DPF可以更快速地添加数据,因为每个数据库分区可以并行工作。另一方面,DPF又需要考虑将行发送到适当的数据分区。
与不使用MDC相比,使用MDC可以改善转入过程。其优点包括:
●       更少的物理I/O:MDC表的RID索引更少,因此在转入期间更新索引时需要的物理I/O更少。
●       更快的插入:MDC表减少了页面争用和锁,因此有助于使用多个并行的流来执行插入。
●       并发的业务查询能拥有更好的性能:MDC表减少了页面争用的锁,这一点同时也有利于提高并发业务查询的性能。
另一方面,如果使用MDC,建议预先根据MDC维对数据进行排序。
  在某些情况下,TP有助于转入操作。TP允许将行添加到一个分区,然后再在准备好的时候将那个分区附加(attach)到表上。然而,在这里的例子中,这个选项并不适用。还记得吗?我们的示例表(Transactions历史表)对于每个月都有一个单独的分区,而我们是每天一次或多次添加数据。在这种情况下,在一个月开始之前,即这个月还没有开始每天添加数据之前,需要为这个表附加(attach)一个空白的分区。
最后,MQT还增加了转入过程中要考虑的因素。特别是,需要决定何时更新MQT。

8. 关键考虑:删除数据

1) 对这一考虑的描述
  当数据在数据仓库中存放了一段时间后,对于业务用户来说就不再有用了,因此需要删除它们,为新数据腾出空间。这个过程称作转出、清洗(purging)或归档。下面列出了可能遇到的各种不同情况。
通常,转出涉及以下业务规则,并关系到如何使用DB2分区特性的问题:
●       删除过了一定时间的行:这是最简单也是最常见的业务需求。在传统的历史表中,一般的期限为36个月。对于最近历史表,一般期限为60到180天。
●       根据业务规则删除到了一定时间的行:在这种情况下,有些行虽然到了一般的清除时间,但是仍然需要保留。例如,为了为争议或调查提供证据,可能需要保留某个历史事务(例如银行需要保存数据以备人民银行检查)。
●       使数据仍然可以被访问,但是释放存储:这种情况有时候称作归档,而不是转出。在这种情况下,行必须对用户查询可见,但是需要将这些很少被访问的行转移到更便宜的、性能更低的存储上。
  通常,MQT也需要放进来考虑。MQT也需要更新以删除相应的汇总数据。例如,如果从事实表中删除了 2012年3月的数据,那么也需要删除MQT中关于那个月的汇总数据。
2) DB2分区特性发挥的作用
为了支持转出,针对不同的业务需求,DB2分区有一些值得注意的特性。
先说DPF,这个特性对转出作用不大。使用DPF与不使用DPF相比,转出(roll-out)操作相差不大。
  MDC和TP都能为转出操作带来好处。在任何DB2版本中,一个特性可能比另一个特性能提供更好的转出性能,但是随着时间的推移,这两个特性应该大致相当。在比较这些特性时,更重要的是看每个特性在某些转出情况下特有的优势。
  对于基本的、常见的转出情况(也就是说,只是删除到了一定时间的行),TP显然是首选。如果使用TP,那么转出可以通过简单的DETACH操作来完成。而且,TP是唯一适合以下情况的特性:
●       将转出的数据移动到另外一个位置(也就是说移动到表或数据库中),而不仅仅是删除它们。
●       根据业务逻辑把较老的、很少被访问的行转移到更便宜的存储中,但是用户查询仍然可以看到它们。
MDC是唯一适合以下情况的特性,与基本转出相比,下面这些情况要求更高,但是不大常见:
●       客户希望在并发查询活动期间执行转出,但是他们不能接受在DETACH操作期间 TP暂时持有的超级排它锁的影响。
●       客户不希望修改他们的应用程序或脚本。也就是说,他们不想用TP的DETACH语句代替他们的DELETE语句。
●       客户想删除除时间维(表在这一列上进行数据分区)以外的其他维上的大量数据。
●       客户想根据业务规则删除到了一定时间的行。在这种情况下,他们可以发出一条 SELECT 语句来识别符合条件的行,然后再DELETE那些行。
  对于MQT,当从MQT中删除相应的汇总数据时,建议在MQT上使用表分区,并与基本表定义一样的数据分区。

9. 删除数据的其他选项

  虽然转出是删除过时数据的常见方式,但是应该注意到,客户有时候也使用其他方式来删除数据,这些方式不需要借助分区特性。这些方式有:
●       刷新表:在某些数据仓库中,整个表每年才删除一次,然后装载用于替代的表,这个新表包含了除不再需要的数据以外的所有数据。
●       清洗:在某些最近历史表中,每过一些天就删除整个表,并重新创建表,数据被放在有更长历史的另一个表中。然后,重新创建的空表又可以摄取新的数据,直到清洗日的到来。
  上面我们介绍了DB2的表设计特性:表分区、MDC、DPF和MQT。协同使用这些特性,可帮助我们解决在查询性能、插入新数据和删除过期数据方面关心的问题。表2-13总结了这些高级设计特性是如何解决各种客户需求的。
 
表2-13  DB2 特性如何解决客户需求
客 户 需 求 优    点
查询性能 每个特性都以自己的方式为提高查询性能做出贡献,使用更多的特性将导致更好的性能
转入 在大多数客户场景中,MDC 可以带来最大的好处,TP 可以在某些不常见的情况下带来好处
转出 对于简单、常见的转出场景,TP 可以带来最大的好处,MDC 则适合处理 TP 不适合的其他转出情况
 
注意:
一般情况下,选择数据库分区的可能性比较小,首先是因为在DB2 V9之前由于没有表分区,数据库分区是我们唯一和无奈的选择。而在DB2 V9有了表分区以后,相对来说选择数据库分区就不是必须的了。其次,如果部署数据库分区,就必须购买高昂的DPF授权,并且在部署起来相当复杂。当然,如果我们的数据量非常大,并且单台机器的硬件资源已经达到了极限,这种情况下可以考虑使用数据库分区。
您对本文章有什么意见或着疑问吗?请到论坛讨论您的关注和建议是我们前行的参考和动力  
上一篇:2.4.6 物化查询表及应用案例
下一篇:2.4.8 表压缩和索引压缩
相关文章
图文推荐
排行
热门
最新书评
文章
下载
读书
特别推荐

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训 | 举报中心

版权所有: 红黑联盟--致力于做实用的IT技术学习网站