db2 分区特性转载

  在数据仓库中，事实表或历史表的大小是摆在设计人员和管理员面前的一个挑战。这些表通常包含数亿行数据，有时候甚至包含数千亿行数据。对于这种规模的表，主要关心以下几点：

查询性能
将大量新数据插入到这些表中
每月或每个季度删除大量过时的数据
随着时间的推移，DB2 继续添加和增强特性，以解决这些需求。DB2 9 for Linux, UNIX, and Windows 中一个重要的增强是表分区特性。这导致以下问题：

有哪些这样的特性？
每个特性对解决上面关心的问题有什么作用？
我应该使用哪些特性？
如何将这些特性结合起来使用以增强效果？
这些就是本文要解决的问题。阅读本文之后，读者将可以：

理解每个特性对于解决相关问题的独特作用。
明白如何有效地将这些特性结合起来使用。
有了这些背景，读者就足以进一步研究他们感兴趣的特性的细节。

特性概述

三个互补的 CREATE TABLE 选项

CREATE table 语句现在提供了三种方式来组织数据库表中的数据。


表 1. DB2 特性
CREATE TABLE 语句中的子句 DB2 特性名称
DISTRIBUTE BY HASH DPF —— 数据库分区特性
ORGANIZE BY DIMENSION MDC —— 多维聚类
PARTITION BY RANGE TP —— 表分区

您可以任意组合使用这些子句，以达到期望的效果。表 2 总结了与这些特性相关的术语，本文中用到的其他一些特性也列在下面。


表 2. DB2 特性术语
DB2 特性名称 一部分的名称 用于分区数据的列 其他术语
数据分区特性（Data Partitioning Feature，DPF） 数据库分区 分布键（distribution key） 在之前的版本中，分布键被称做分区键
多维聚类（Multidimensional Clustering，MDC） 单元格，由一些块组成 维 块索引
表分区（TP） 数据分区 表分区键 

简要对比

每种特性都为分组表中的数据提供了独特的方法，并对解决与事实表或历史表相关的需求有独特的作用。

DPF 是最老的特性，通过它可以将数据库分成多个数据库分区。每个数据库分区有它自己的一组计算资源，包括 CPU 和存储。在 DPF 环境中，根据 CREATE TABLE 语句中指定的分区键，表中的每个行被分布到一个分区上。当处理一个查询时，请求也相应地被划分成多个部分，以便让各个数据库分区各自处理其负责的那些行。实际上，DPF 是一种可伸缩特性。DPF 可以通过增加数据库分区来提高处理能力，因此，随着表的增长，仍然可以保持较高的查询性能。这种能力常常被称作使用 DB2 的无共享架构提供线性的可伸缩性。

DPF 的作用不仅仅体现在表设计上。它还是调整和配置整个数据库系统的一种方法。现在已经有了关于配置那样的系统，以取得最佳性能、可靠性和增长的能力的建议实践。客户可以购买建议的硬件和软件以及配置，它们都放在一个称做 BCU (Balanced Configuration Unit) 的解决方案中。

MDC 是在 DB2 Version 8 中引入的，通过它可以在物理上将在多个维上具有类似值的行聚合在一起放在磁盘上。这种聚合能为常见分析性查询提供高效的 I/O。例如，对于 Product=car，Region=East，并且 SaleMonthYear = Jan09 的所有行，可以将它们存储在相同的存储位置，即所谓的块（block）。在 CREATE table 语句中定义维的时候，就为每种值的组合预留了存储空间。实际上，MDC 是一个能最大化查询性能的特性，对于数据仓库中常用的查询更是如此。这包括需要根据几个列中的值的组合选择行的查询。例如，DATE is between "Jan-01-2004" and "Feb-01-2005" AND Country IS NOT "United States" AND Product="Cell Phones"。

TP 是在 DB2 9 中引入的，与 MDC 类似，它也可以将具有近似值的行存储在一起。但是，TP 的以下特征是 MDC 所不具备的：

TP 支持按照一个维将一个表分区成多个数据分区。一种常见的设计是为每个月的数据创建一个数据分区。MDC 则支持定义多个维。
通过 TP，用户可以手动地定义每个数据分区，包括将被包括到那个分区的值的范围。MDC 则为每种惟一的 MDC 维值组合自动定义一个单元格（并创建块来存储那个单元格的数据）。
每个 TP 分区是一个单独的数据库对象（不同于其他作为单个数据库对象的表）。因此，TP 支持为 TP 表附加和卸除数据分区。卸除的分区成为一个常规表。而且，必要时可以将每个数据分区放在它自己的表空间中。
实际上，TP 不同于其他特性的优势在于为表添加或删除大量数据这个方面，即转入和转出。对于熟悉使用 Union All View (UAV) 来按日期对历史表分区的读者，TP 可以作为一种功能相似但是更为高级的解决方案。

表 3 总结了这些特性之间的比较：


表 3. DB2 特性简要对比
特性 特性如何组织数据 优点
DPF 将行均匀地分布在多个数据库分区上 可伸缩性 —— 随着数据库的增长增加计算资源（也就是数据库分区）
MDC 将在多个维上具有近似值的行放在表中相同的物理位置，即所谓的块 查询性能 —— 组织数据的方式有利于获得更快的检索速度，对于由多个谓词指定范围的查询尤其有效
TP 将所有行放在同一个数据分区的一个指定范围的维中 数据移动 —— 通过添加和删除整个数据分区，可以增加和删除大量数据

互补特性

本节详细阐述前面提出的观点，这 “三个朋友” 既是独立的，又是互补的。

当设计一个表时，对每个特性的使用可以认为是独立的。例如，

是否使用 MDC 和 TP 对于决定 DPF 的分布键没有影响。
一个列是否被用作 MDC 维对于是否应该将它作为表分区键没有影响，反之亦然。每个决定都可以单独做出。
每个特性的工作方式，例如索引方面，不会随着新的分区特性的引入而改变。例如，当引入 MDC 时，它的索引方面不会改变 DPF 在索引方面的工作方式。同样，当引入 TP 时，也不会改变 DPF 或 MDC 在索引方面的行为。在学习这些特性的时候，记住这一点有助于避免陷入困惑。

例如，假设您在学习 TP 的过程中碰到了 "TP has global indexes" 这个语句。那么，您应该不会推断 DPF 在处理索引方面的行为会有所变化。在这样的语句中，术语 "global" 仅仅是指索引对于多个 TP 数据分区来说是全局的。

通常来讲，一个特性不能用于解决数据库设计中与另外一个特性相关的不足或问题。值得注意的例子有：

TP 不能解决与 DPF 相关的问题，不管这个问题是 DPF 数据倾斜还是 DPF 中的管理活动速度降慢。不管有没有同时使用 TP，DPF 之前的补救方法仍然适用。
TP 不能用于纠正不好的 MDC 设计。
总之，如果存在与 DPF、MDC 或 TP 相关的问题，那么还是应该尝试适用于那个特性的解决方法。

表设计

数据仓库中的事实表（或历史表）非常适合使用上述每种特性，如下面的表 4 所示。


表 4. 事实表拥有适合使用 DB2 分区特性的特征
特性 适合的表特征 事实表的特征
DPF 大型表 —— 大到无法仅依靠单独一组 CPU 和 I/O 通道来处理 事实表是最大的数据库表。它们常常包含数亿行数据，有时候甚至包含数千亿行数据
MDC 结果集返回在多个维上具有近似值的行的查询 事实表（以及通常所说的数据仓库）是为支持这种类型的查询而设计的
TP 这种类型的表：周期性地添加大量数据，然后在数据到期后又删除大量数据 在事实表中，常常是每天都添加新数据。通常每月或每个季度删除过时的数据

表设计的经验法则

本节让您对于设计决定的性质有一个感性的认识（也仅仅是感性的认识），并给出一些经验法则。后面列出的参考资料提供了更全面的设计指南。

对于 DPF，在选择一个分布键时，应首选那种能使数据行均匀地分布在多个数据库分区上的列。当不具备这样的条件时，就会造成数据倾斜。这意味着一个或一些数据库分区承担了更重比例的表行，从而造成了性能瓶颈。具有很多不同值的列是较好的选择。还有一种考虑是选择能最大化联结性能的列。

另一个 DPF 设计决定是数据库分区的数量。数据库分区的数量不算是表设计方面的考虑。实际上，它是整个系统设计上的考虑，需要根据整个数据库预期的原始数据大小和服务器硬件的能力来决定。很多系统需要的数据库分区不超过 20 个。但是，最大的系统可能需要更多的数据库分区。由于数据仓库有越来越大的趋势，数据库分区的数量有望增加。

对于 MDC，一个关键的决定是用哪些列作为 MDC 维。设计上的挑战是找到最佳的一组维和最佳的粒度，使得组织最大化，存储需求最小化。较好的选择是具有以下一部分或全部特征的列：

用于范围、等于或 IN 列表谓词
用于转入、转出或其他大规模的行删除
被 GROUP BY 或 ORDER by 子句引用
外键列
星型数据库的事实表中 join 子句中的列
粗粒度，也就是说不同的值很少的列
典型的设计是用一个表示日期的列作为一个 MDC 维，再加上 0 到 3 个其他列作为其他维，例如 region 和 product_type。

对于 TP，设计决定包括选择用作表分区键的列和分区的数量。通常表分区键是基于时间的列。每个分区与每次转入的数据量相符。例如，每月转出数据的表，对于每个月的数据都有一个分区。对于 TP，在设计时需要特别考虑的一点是，需要处理不在 CREATE table 语句中定义的值范围内的行。

对于需要每月根据 sale_date 转出数据的数据库，一种典型的设计是使用 sale_date 作为表分区键，并为每个月创建一个单独的分区。

通常，有一个 MDC 维是基于时间的列，这样一来，同一个列既可以用于 MDC，又可以用于 TP。MDC 的粒度可以比 TP 数据库分区更细一些。

下面的表总结了以上几点。


表 5. 设计方面的经验法则总结
分区特性设计决定 经验法则
DPF —— 用作分布键的列 首选是具有很多不同值的列
MDC —— 用作 MDC 维的列 一种典型的设计是选择一个表示日期的列，再加上 0 到 3 个其他列，例如 region 和 product_type
TP —— 用作表分区键的列和分区的数量 选择一个基于时间的列。定义与每次转出的数据量相符的分区

设计的例子

表 6 展示了一些典型的表设计的例子。Transactions 历史表代表关系数据仓库中的一个典型的表。Recent transactions 表代表运营数据存储中的一个表，这种数据存储实际上是只有最近数据的一个数据仓库。


表 6. 表设计的例子
分区属性 Transactions 历史表 Recent transactions 表
DPF —— 用作分布键的列 Transaction ID Transaction ID
DPF —— 数据库分区的数量 20 4
MDC —— 用作维的列 Transaction date(Year+Month)=36 values （见注 1）；Account type=5 values; State=51 values Transaction date(days)=90 values; Account type=5 values; State=51 values
TP —— 用作表分区键的列和分区的数量 Transaction date(Year+Month)=1 partition per month Transaction date(Year+Month)=1 partition per month
其他表属性 - - - -
# of rows (1 million per day) 1 billion 90 million
# of columns 30 30
# of indexes 4 15

注 1：对于 Transactions 历史表上的 MDC 维，另一种设计方案是以更细的粒度定义 Transaction 日期，例如每周或每天。更好的查询性能与增加的存储需求之间的平衡取决于数据和查询的特征。

再添上 MQT

MQT 简介

阐明了以上三个特性相互区别、相互补充的关系之后，有必要进一步扩大讨论，谈一谈 MQT（物化查询表）。MQT 和分区特性都是为在相同的情况下（即数据仓库事实表或历史表）使用而设计的。因此，为了全面考虑如何使用本文中谈到的分区特性，需要解决涉及到 MQT 的情况下的一些特殊的考虑。

MQT 是基于一个查询的结果而定义的表。从另一种角度来看，MQT 就像结果集存储在一个表中的一个视图。MQT 可以提高涉及以下方面的复杂查询的响应时间：

基本表中的数据上的聚类或计算
基本表的联结
一个或多个较大基本表中常被访问的一部分数据。
当基本表中的数据发生改变时，需要相应地更新 MQT。MQT 特性为适应各种运营需求而进行更新提供了多种选项。

与分区特性比较

下面的表为表 3 增加了关于 MQT 的一行。


表 7. DB2 特性简要对比，包括 MQT
特性 特性如何组织数据 优点
DPF 将行均匀地分布在多个数据库分区上 可伸缩性 —— 随着数据库的增长增加计算资源（也就是数据库分区）
MDC 将在多个维上具有近似值的行放在表中相同的物理位置，即所谓的块 查询性能 —— 组织数据的方式有利于获得更快的检索速度，对于由多个谓词指定范围的查询尤其有效
TP 将所有行放在同一个数据分区的一个指定范围的维中 数据移动 —— 通过添加和删除整个数据分区，可以增加和删除大量数据
MQT 将查询的结果存储在一个表中 查询性能 —— 对于涉及较高代价的操作，例如复杂的联结和表扫描的查询，预先计算其结果集并存储（物化）结果集

与分区特性一起设计 MQT

与分区特性一起设计 MQT 时，在设计上的考虑可以总结为以下几点：

可以在使用分区特性的任意组合的表上创建 MQT。例如，可以在一个或多个使用 MDC 和 TP 的表上创建 MQT。基本表上分区特性的使用不需要考虑将来是否会在这个表上创建 MQT。然而，MQT 的设计却可能受到基本表上使用的分区特性的影响。例如，如果基本表使用了 DPF 进行分区，那么 MQT 的设计就应该考虑是否要在各个数据库分区上复制 MQT。
MQT 也可以使用分区特性。例如，可以使用 MDC 或 TP 对 MQT 分区。
典型的设计

接下来进一步介绍前面的 Transactions 历史表的例子，下面是这些表上定义的一些 MQT：

MQT 1 - transaction totals per day per account type
MQT 2 - Year-to-Date totals per state
关键考虑：查询性能

对这一考虑的描述

现在我们来看看在评价和使用这些特性时关键的考虑角度：性能，尤其是通常的数据仓库业务的用户查询的性能。这些查询有以下特征：

从事实表中选择在几个维上符合标准的行。这意味着要将几个维表与事实表相联结。
使用分组或聚类函数，例如 COUNT、GROUP BY 和 ORDER BY。
返回包括很多行的结果集，从数千行到数百万行。
这些查询是由用户或他们的 BI 工具生成的。这意味着这些查询更多情况下是临时性的，事务处理系统中的性能测试和调优方法对于它们并不适合。
虽然人们倾向于想到更快的性能，但最好还是说成更好的性能。谈到性能，就应该包括以下一些方面：

峰值查询执行性能
查询执行性能的稳定性
对于数据仓库中各种具有不同特征的工作负载的性能
设计数据库以达到性能目标是否容易
为达到性能目标需要付出的代价
与过去相比，现在在设计数据库以达到性能目标的过程中还需要考虑的一点是硬件的发展趋势。随着 CPU 处理能力的不断提高以及存储设备容量的不断扩大，I/O 带宽是一个潜在的性能瓶颈。在这种环境下，I/O 效率是设计时要关键考虑的一点。

本节阐释每个分区特性对查询执行性能的作用。在后面的小节中我们还会谈到转入和转出的性能。

DB2 分区特性发挥作用

使用 DPF 与不使用 DPF 相比可以提供更多的计算资源，从而对性能产生积极的影响。当 DB2 优化器为一个查询形成查询访问计划时，它将工作划分到多个数据库分区上，这些数据库分区是并行工作的。之后，再收集各个数据库分区上得到的结果，并返回给查询提交者。

MDC 对性能的贡献在于提高检索数据的效率。在多个维上具有近似值的数据存储在相同的位置，这使得 I/O 操作变得更高效，而 I/O 操作正是数据仓库中一个常见的瓶颈。而且，MDC 特性还包括块索引，在块索引中，对于每个块的数据（而不是每一行的数据）都有一个条目。这使得执行索引操作时有更高的效率。为了发挥它潜在的性能，必须为 MDC 表设计一组最佳的（或者至少是够好的）维。MDC 只对那些包括维列的查询有好处。MDC 对于查询是完全透明的。最后，MDC 在管理方面有两个值得注意的优点：

MDC 块索引意味着需要的 RID 索引更少。管理方面的一个优点是用于索引的存储空间减少了。
由于新行是插在表中具有近似值的行附近的位置，因此数据仍然是聚合的，而不需要运行 REORG 实用程序。
TP 通过分区排除提高查询性能。例如，假设 Transactions 历史表有 36 个分区，每个月对应一个分区。对于一个选择过去 12 个月的数据的查询，优化器知道不必扫描这 12 个月之外的其他分区的数据。这种分区排除既适用于索引扫描，也适用于表扫描。TP 只对那些包括表分区键列的查询有利。

关键考虑：插入新数据

对这一考虑的描述

将来自运营系统的新数据插入到仓库中的事实表，这个过程称作 ETL、摄取（ingest）、填充数据仓库或者转入。下面的例子演示了可能遇到的各种情况。

例 1 - 使用 LOAD 每日摄入数据

在业务日结束后，运营系统中包含五十万到两亿条记录的多个平面文件如期到来。
一个客户脚本使用 DB2 Load 实用程序将每个文件装载到事实表中。这是在一个每夜批处理窗口中当表离线的时候完成的。
在下一个业务日一开始，查询这个表的用户就可以看到前一天的数据。
例 2 - 使用 insert 的准实时摄取

每过 30 分钟，一个包含 1 万到 10 万条记录的文件到来。
当该文件到来时，用户编写的一个程序将这些记录添加到一个 staging 表中，然后使用 insert 语句将这些记录添加到事实表中。
例 3 - MQT 刷新

在有 MQT 的时候，它们被看作将新数据添加到数据仓库这个过程中的一部分。比较特别的是，这种情况下可以使用 MQT 刷新策略。通常，ETL 过程是手动地指定何时更新 MQT，而不是让更新自动发生。

对于例 1，很可能是在每夜更新的所有任务完成之后立即更新。
对于例 2，MQT 通常是每天更新一次。因此，即使底层的数据是周期性地更新的，访问 MQT 的查询全天返回的都是相同的结果。
DB2 分区特性发挥作用

DB2 分区特性对于转入会有帮助，但是有时候也会带来新情况，客户在转入过程中要适当考虑这一点。

通过 DPF 可以更快速地添加数据，因为每个数据库分区可以并行工作。另一方面，DPF 又需要考虑将行发送到适当的数据分区。

与不使用 MDC 相比，使用 MDC 可以改善转入过程。其优点包括：

更少的物理 I/O：MDC 表的 RID 索引更少，因此在转入期间更新索引时需要的物理 I/O 更少。
更快的插入：MDC 表减少了页面争用和锁，因此有助于使用多个并行的流来执行插入。
并发的业务查询能拥有更好的性能：MDC 表减少了页面争用的锁，这一点同时也有利于并发业务查询的性能。
另一方面，如果使用 MDC，建议预先根据 MDC 维对数据进行排序。

在某些情况下，TP 有助于转入操作。TP 允许将行添加到一个分区，然后再在准备好的时候将那个分区附加到表上。然而，在这里的例子中，这个选项并不适用。还记得吗，我们的示例表（Transactions 历史表）对于每个月都有一个单独的分区，而我们是每天添加一次或多次数据。在这种情况下，在一个月开始之前，即这个月还没有开始每天添加数据之前，需要为这个表添加一个空白的分区。

最后，MQT 还增加了转入过程中要考虑的因素。特别是，需要决定何时更新 MQT。

关键考虑：删除数据

对这一考虑的描述

当数据在数据仓库中存放了一段时间后，对于业务用户来说它就不再有用了，因此需要删除它们，为新数据腾出空间。这个过程称作转出、清洗（purging）和归档。下面的例子演示了可能遇到的各种不同的情况。

通常，转出涉及到以下业务规则，并关系到如何使用 DB2 分区特性的问题：

删除到了一定年龄的行：这是最简单也是最常见的业务需求。在传统的历史表中，一般的期限为 36 个月。对于最近历史表，一般期限为 60 到 180 天。
根据业务规则删除到了一定年龄的行：在这种情况下，有些行虽然到了一般的退休年龄，但是仍然需要保留。例如，为了为争议或调查提供证据，可能需要保留某个历史事务。
使数据仍然可以被访问，但是释放存储：这种情况有时候称作归档，而不是转出。在这种情况下，行必须对用户查询可见，但是需要将这些很少被访问的行转移到更便宜的、性能更低的存储上。这种业务需求可以通过 Tivoli Hierarchical Storage Manager (HSM) 之类的工具来解决。
通常，MQT 也需要放进来考虑。通常，MQT 也需要更新，以删除相应的总结数据。例如，如果从事实表中删除了 2003 年 3 月的数据，那么也需要删除 MQT 中关于那个月的总结数据。

DB2 分区特性发挥作用

为了支持转出，针对不同的业务需求，DB2 分区特性有一些值得注意的特性。

先说 DPF，这个特性对转出作用不大。使用 DPF 与不使用 DPF 相比，转出操作相差不大。

MDC 和 TP 都为转出操作带来好处。在任何 DB2 版本中，一个特性可能比另一个特性提供更好的转出性能，但是随着时间的推移，这两个特性应该大致相当。在比较这些特性时，更重要的是看每个特性在某些转出情况下特有的优势。

对于基本的、常见的转出情况（也就是说，只是删除到了一定年龄的行），TP 显然是首选。如果使用 TP，那么转出可以通过一个简单的 DETACH 操作来完成。而且，TP 是惟一适合以下情况的特性：

将转出的数据移动到另外一个位置（也就是说移动到一个表或数据库中），而不仅仅是删除它。
使用 Tivoli HSM 之类的工具将这些较老的、很少被访问的行转移到更便宜的存储中，但是用户查询仍然可以看到它们。
MDC 是惟一适合以下情况的特性，与基本转出相比，下面这些情况要求更高，但是不大常见：

客户希望在并发查询活动期间执行转出，但是他们不能接受在 DETACH 操作期间 TP 暂时需要的 zlock 的影响。
客户不希望修改他们的应用程序或脚本。也就是说，他们不想用 TP 的 DETACH 语句代替他们的 DELETE 语句。
客户想删除除时间维（表是在这一列上进行数据分区的）以外的其他维上的大量数据。
客户想根据业务规则删除到了一定年龄的行。在这种情况下，他们可以发出一条 SELECT 语句来识别符合条件的行，然后再 DELETE 那些行。
对于 MQT，当从 MQT 中删除相应的总结数据时，建议在 MQT 上使用表分区，并与基本表定义一样的数据分区。

删除数据的其他选项

虽然转出是删除过时数据的常见方式，但是应该注意到，客户有时候也使用其他方式来删除数据，这些方式不需要借助分区特性。这些方式有：

刷新表：在某些数据仓库中，整个表每年删除一次，然后装载一个替代的表，这个新表包含了除不再需要的数据以外的所有数据。
清洗：在某些最近历史表中，每过一些天就删除整个表，并重新创建表，数据将放在有更长历史的另一个表中。然后，重新创建的空表又可以摄取新的数据，直到清洗日的到来。
结束语

本文介绍了以下 DB2 表设计特性：表分区、MDC、DPF 和 MQT。这些特性并肩作战，一起解决客户在查询性能、插入新数据和删除老数据方面关心的问题。下面的表总结了这些 DB2 特性如何解决各种客户需求。


表 8. DB2 特性如何解决客户需求
客户需求 优点
查询性能 每个特性都以它自己的方式为提高查询性能作出贡献。使用更多的特性将导致更好的性能。
转入 在大多数客户场景中，MDC 可以带来最大的好处。TP 可以在某些不常见的情况下带来好处。
转出 对于简单、常见的转出场景，TP 可以带来最大的好处。MDC 则适合处理 TP 不适合的其他转出情况。