DB2 最佳实践: 物理数据库设计最佳实践，第 2 部分

MDC、数据库分区、视图以及后设计工具介绍

打印本页

将此页作为电子邮件发送

级别： 初级

developerWorks 中国网站编辑团队, 编辑, IBM

2009 年 9 月 24 日

物 理数据库设计是影响数据库性能的一个最重要的因素。物理数据库设计涵盖了所有和数据库物理结构相关的设计功能，比如表规范化和反规范化、索引、物化视图、 数据集群、多维数据集群、表（range）分区还有数据库（hash）分区。本文从MDC、数据库分区、视图以及后设计工具方面阐述物理数据库设计最佳实 践。

数据集群和 MDC 最佳实践

通过 “IBM DB2 for Linux, UNIX, and Windows 最佳实践” 专题，获得最常用的 DB2 9 产品配置实践指南，并使用这些知识提高 DB2 数据服务器的价值。这些最佳实践文章给出了最优化方法的建议，使您能使用 DB2 满足关键的业务数据处理需求。每篇最佳实践文章都为最常用的 DB2 产品配置提供了实践指南。通过应用这些建议，您可以提高 DB2 数据服务器的价值，并且能够始终把握 IBM 在 DB2 方面的技术方向。

• IBM DB2 for Linux, UNIX, and Windows 最佳实践

MDC 是一个同时有不止一个维度数据集群的技术。然而，你也可以对一个维度使用 MDC 集群，就像你可以使用一个集群索引一样。 MDC 的一个好处是它能保证数据一直处于集群状态，永远不需要执行一个重组操作来重新建立较高的集群命中率。

而 且，不同于用 CREATE INDEX 语法创建的传统索引，MDC 是对表中的每一行建立索引，MDC 通过块把表中的数据编入索引。每个块和表所在的表空间中的扩展数据块有相同的大小。当 CREATE INDEX 命令创建索引时，每一行数据页会同时被编入索引，每个 MDC 表索引 BLOCKS 可以包含上千条数据。 MDC 索引，也叫 BLOCK INDEX，通常只有 1/1000 大小基于索引的行，而且不光为索引提供了大量的保留存储空间，同时也为所有 BLOCK INDEX 操作（索引扫描、索引 ANDing、索引 ORing，等等）提供了非常好的性能。

要理解 MDC，你必须首先理解一些基础术语：单元是表容纳数据的部分，它有一套唯一的维度值 ，是每个维度切片相交形成的。块是存储单元等于一个扩展数据块（一个或多个页面），块被用来存储一个单元。

MDC 表的块索引

如 上面所述，除了它们指向的是块而不是记录，块索引在结构上和普通索引是一样的。块索引比普通索引要更小，因为块大小是一个页面中的平均记录数的数倍。如图 4 中所示，同每一行每一个单独的输入相比，在一个块索引中每块都有一个单独的索引。结果就是，一个块索引显著降低了磁盘使用率并明显加快了数据访问的速度。

图 5 中显示的 MDC 表示一个物理组织，比如所有记录有相同的“ Region ”以及“ Year ”值都同每个块或扩展数据块中连在一起。

甚至一个只定义一个维度的 MDC 表也可以从这些 MDC 属性中获益，而且可以替换一个有集群索引的普通表。这个决定应该基于很多因素，包括组成工作负载的查询以及表中数据的性质和分布。

在插入操作过程中自动维护集群

在 MDC 表中自动维护数据集群确保了使用复合块索引。这些索引被用来根据在 INSERTS 操作过程中的表的维度来动态管理和维护数据库的物理集群。当一个插入发生了，合成块索引将会在与被插入记录的维度值相应的逻辑单元中被探测到。

如图 6 所示，在索引中的逻辑单元的键值，它的块 ID（BIDs）的列表完整的给出了表中有这个本地单元维度值的块列表。这限制了给插入记录搜索可用空间的表的扩展数据块的数目。

因 为集群是自动维护的，所以对 MDC 表来说不需要重组操作来重新集群数据。然而，重组仍然被用来释放空间。例如，如果单元有很多分散的块，数据很少能够匹配到，或者，如果表有很多指针溢出， 这个表的重组操作将压缩属于逻辑单元的记录到最少的块中，用时也删除指针溢出对。

使用 MDC 的益处

MDC 的意义非常深远。在某些情况下它能将复杂查询的性能提高 10 倍以上，同时你还可以用它转入和转出数据。还有下面显示的其他好处：

• MDCs 是多维的。例如，数据可以根据 DATE 和 LOCATION 维度完美集群；单元和范围在新数据插入的时候会被自动创建。
• MDCs 能和普通基于 RID 的索引、范围分区和 MQTs 联合使用。
• MDCs 和内部查询并行性、DPF（不共享）并行性、LOAD、BACKUP 和 REORG 操作同时使用。
• MDC 维度，和范围分区表不同，它是动态的；它会在表中自动创建新的单元，因为新单元是表示通过 SQL 操作（包括 JDBC、CLI，等等）或通过使用工具操作（比如 LOAD 和 IMPORT）插入的新的唯一数据。
• MDCs 维护集群，而且正因为这样就不需要 REORGs 来维护集群命中率了。

下面的例子显示了如何定义一个 MDC 表：

CREATE TABLE T1 (c1 DATE, c2 INT, c3 INT, c4 DOUBLE,                    c5 INT generated always as (INT(C1)/100) )                    ORGANIZE BY DIMENSIONS (c5, c3)                    

ORGANIZE By 子句定义了集群的维度。这个表同时在 C5 和 C3 上被集群。 C1 是 coarsified C5，它包含很少的不同值（天减少为月）。

注意：由 coarsified 产生的 columns(s) 被使用在 MDC 块索引中，以提高单元级别的数据清除。

MDC 设计的一个关键是谨慎选择集群的维度。如果你选择的集群维度产生太多的单元，存储的成本会显著的增加。知道其中的原因非常重要。在一个 MDC 表中，每个单元都会根据需要的存储块来分配。存储块是设计为和表所在表空间的扩展数据块的大小相等。如果一个单元没有数据，那么存储块的数据是 0 。然而，在一个单元中存储了若干记录的典型表中会造成给单元分配一个或多个存储块。对每个有数据的单元都会有一批通常只包含了被部分填充数据块。因此，在 每个单元（不是数据块）中造成浪费和存储块的大小成比例。新数据库只有个在前面的数据块被填满（或快满）后被创建。如果行被删除，数据库管理器会尝试尽可 能多的重用空间。

存储块被设计为同这个表所在表空间的扩展数据块大小相等。如果这个表的单元数目非常大，存储的浪费也会很大。如 果 MDC 很差而且产生了大量的单元，表的存储需求会非常显著的增加，而且 MDC 的性能也会受损。然而，设计的 MDC 表只会比非 MDC 表大一点，而且在集群和数据转入转出上提供了较大的好处（将在后面讨论）

图 7 显示存储块和单元分配。如图所示，每个单元都包含了一批存储块。绝大多数块被数据填充，不过对于每个单元的最后一个块，却是或多或少被部分填充的。

如果你有样本数据或具体数据，你就可以使用 SQL 来为所有可能的 MDC 设计度量 MDC 需要的单元数目，如下所示：

SELECT COUNT(*) FROM (SELECT DISTINCT COL1, COL2, COL3 FROM MY_FAV_TABLE) AS NUM_DISTINCT;

对一个 3 维的 MDC 表来说，COL1、COL2 和 COL3 是 MDC 的维度。结果数乘以表的扩展数据块大小将得出一个在表被转换为 MDC 过程中扩展数据块增长（不是大小）的上限。

MDC 的其他核心价值是 DB2 数据库管理器在表的 MDC 维度上自动为 MDC 表创建索引。这些特殊的索引对块而非行建立索引，这是可行的。因为在 MDC 表中数据会一直保持集群，因此在相同块中的所有的行同样确保有相同的键值。这是由于在 MDC 中数据是对块进行索引而非记录，所以块索引通常是传统基于行的索引的 1/1000 大小。结果就是在相关查询运行时的性能好处，并把 INSERT、UPDATE 和 DELETE 操作的开销减到了最少。

MDC 提供的功能方便了数据的转入和转出。索引条目指向一个块而不是一行，块索引又比典型的（rowID）RID 索引要小很多。因此，当 MDC 在转入处理时只有很少的块索引 I/O，这是因为块索引只在块被写满的时候被更新一次（而不是每一行插入）。由于 MDC 重用已经存在的空块不需要分页，所以插入也很快。插入时加的锁也少了，因为他们发生在一个块级别而不是行级别。表在转入和转出后不需要 REORG 数据。

MDC 存储场景

想要为一个传统事实表基于 Date、Product Name 和 Region 创建一个 MDC 。这里有一些在创建 MDC 时需要考虑的变量：

• 一年有 365 天。
• XYZ 公司有 100,000 个产品。
• XYZ 公司有 10 个区域。

开始创建 MDC

如果 MDC 直接创建在 Date， Product 和 Region 列上，每天将会产生 1,000,000 个新的单元（1 x 100,000 x 10），而每年则会产生 365,000,000 个单元。

如果区域中事务非常少，这将会有很多分散甚至是空的页面。这会导致大量没有必要的空间分配数量巨大的单元（页面）用来容纳块数据。这非常不好。

开始 MDC 的创建

使用函数来 coarsify 和限制 MDC 的基数，例如：

• 如果你在 Date 上使用月份函数，那么每年你会有 12 条记录。
• 如果你截取 Production Name 来挑出产品名称的第一个字母，你就会有 26 条可能的结果。
• 保留 Region 的 10 个结果。

使用这个场景中的建议，MDC 将会每年产生 12*26*10 = 3210 个单元或每天 8-9 个单元。这会消除很多页面没有数据的情况，并对 MDC 提供合理的基数来获得性能的好处。

MDC 运行时的开销 / 收益

MDC 是设计来为查询和许多删除场景提供极大性能好处。虽然如此，在对使用了集群索引的集群表提供显著的性能好处的同时，MDC 表这么做所带来的开销还是超过了非集群表。 MDC 和非集群表相比第一个开销是：

• 在一个非集群表上的 INSERT 操作，不需要通过索引访问来判断记录被存放在磁盘的什么位置。相比之下在 MDC 表上，在选择哪个块中有空间存放插入的记录之前，需要通过访问 MDC 复合块索引来判断记录属于哪个单元。
• 如果 MDC 表包括一个产生的列来 coarsify 其中的一个维度，每个 INSERT 将引起很少的处理开销来为这一列计算生成值 , 就像 DB2 中的所有生成列一样被物化，也就是被计算出来然后填充在记录中。

然而，当和使用集群索引而集群起来的表相比较的话，MDC 有着显著的性能优势：

• 与 一个集群索引相比，索引维护可以显著的减少 INSERTs，因为 DB2 数据库管理器仅只有在第一个键值添加到块中时的时候才更新块索引——不像一个 RID 索引，每一行插入表中都需要对索引进行更新。也就是说，如果每块有 1000 条记录，索引更新率就是 RID 索引更新率的 1/1000 。
• 索引更新也非常廉价，因为索引更小而且因此所以在树中有更少的层。在 B+ 树中有更少的层次，则意味着需要更少的处理来为索引插入判断目标的叶子节点。

无 论使用集群索引还是用 MDC 来进行索引，在这两种情况下 DB2 数据库管理器都将在 INSERT 到目标记录的位置时访问索引（块索引的集群索引），并在 INSERT 过程中判断记录的目标位置。再强调一下这个索引非常小，而且树的高度通常很短，因此搜索速度非常快。

判断什么时候使用 MDC，什么时候使用集群索引

MDC 的价值要比集群索引高很多，因为它可以自动保证集群。根据 coarsification 的需求，MDC 的集群率通常可以达到 93%-100% 之间的某个值。相比之下，集群索引虽然可以在一开始就接近 100%，但是随着时间的推移却会变得不集群，因而需要花费时间来对数据重新集群。一般情况下，使用 MDC 在你的数据库上创建和维护数据集群，除非：

• MDC 需要 coarsification，而且你不能将一个生成列添加到你的表中。
• 你不能也不需要在表增长过程中将表的结果生成 MDC 版本。良好设计的 MDC 表一般比非 MDC 表大 2-15% 。
• 你会发现，由于 coarsification 的原因，MDC 集群将提供稍小的集群率（例如，93%）。而使用集群索引的话，为了提高集群率你需要周期性的执行 REORG 来达到这个比率。

使用下面的 MDC 设计的最佳实践：

• 从找出等于、不等、范围和排序谓词使用的列开始，请选择出你的 MDC 候选列。维度必须匹配你的转入范围才能提高数据转入效率。
• 记住，要争取密度！为每个已经存在的单元分配一个扩展数据块 – ——不管单元中有多少条记录。这样可以通过最优的空间利用率来增强 MDC，尽量高密度的填充数据块。
• 控制表的膨胀。保持尽可能低的单元数目并限制存储扩展。 5% 到 10% 的增长对任何单个表都是合理的范围（本文在下面章节讨论 MDC 单元），但是也有例外，甚至也有两倍于平常的增长，不过很少。

注意：BLOCK 索引与相应的表相比非常的小，在大多数情况下，你可以忽略它们的存储需求。

• Coarsify 一些维度来提高数据的密度。使用生成列来 coarsifications 表中基数非常少的列。例如，基于日期列的一部分 month-of-year 上创建一列，或使用（INT（列名））/100 来把 DATE 列从 Y-M-D 格式转换成 Y-M 。
  

  CREATE TABLE Sales                        (SALES_DATE DATE,                        REGION CHAR(12),                        PRODUCT CHAR(30), …                        MONTH GENERATED ALWAYS AS                        ((INTEGER(DATE)/100) …                        ORGANIZE BY (MONTH, REGION, PRODUCT)                        

  
  

  查询：

  select * from sales                        where sales_date> ” 2006/03/03 ” and date< “ 2007/01/01 ” ..

  
  

  编译器会生成附加的谓词：

  month>=200603 and month<=200701

  
  

  为了减少空间浪费，可以指定一个更小的表空间扩展数据块，这会减少你的 MDC 块大小。

• 不要选择太多的维度。很少有设计能在 MDC 有 3 个以上的维度却不产生过多的存储需求。
  

  你拥有的维度越多，单元基数就会以指数级相应的增长。这使得控制表膨胀率 10% 的设计目标变得几乎不可能实现。如果表过度膨胀（例如超过两倍大小）不仅需要更多的存储，同时也会因为部分填充块的 I/O 增加而使集群失去优势。

  一个简单的例子：考 虑一个有三个维度适合集群的表，每个有 10,000 唯一值。如果这些列彼此之间没有相互关联，那么对所有三个维度的集群就没有 coarsification，这会产生 10,000 x 10,000 x 10,000 个单元，每个单元有一个部分填充块。如果每块是 1MB，那么这个设计上的疏忽产生的代价就将是 500,000TB ！

• 考虑只有一个维度的 MDC 。和传统单维度集群索引相比，单个维度的 MDC 仍然有相当多的优点。有如下理由：
  • 集群是有保证的。
  • MDC 表中的索引是用block创建的，而非row。这种索引大小只有传统基于行的索引的 1/1000 。
  • 用 MDC 转出数据，提高了 DELETE 性能。 DB2 9.5 中在 MDC 上的 RID 索引更新是异步的。
  • MDC 可以很方便的转入数据。
  • 使 用单维 MDC（如果需要可以 coarsification）强制进行集群来替代集群索引。集群索引需要付出很大的努力（不能保证集群质量）来集群数据，而且随着时间的推移，数据将变 得不再集群。与之相比，MDC 保证了数据集群性，避免了重组数据的需求（见上面“ MDC Scenario ”章节中的 coarsification）。
• 准备修补（在一个测试数据库上）。在寻找一个好的 MDC 设计过程中，可能需要尝试也可能会失败。可以使用带有– m C 选项（C 是集群搜索）的 DB2 设计顾问程序。也可以使用 db2mdcsizer 实用工具，这在某个 DB2 产品版本的 AlphaWorks 中提供。 MDC 的修改不会影响你的应用程序编程。
• 对一个有代表性的工作负载使用 DB2 设计顾问程序的 MDC 选择能力来为现有表找出恰当的 MDC 维度。

数据库分区（不共享 hash 分区）的最佳实践

数据库分区是一个在数据库中跨多个彼此合作的实例以建立单个大型数据库服务器的水平分布记录的技术。这些实例可以位于一个服务器中、跨多个物理机器、或它们的组合。在 DB2 产品中，这个叫数据库分区功能（DPF）。

数 据库分区允许 DB2 数据库管理器扩充到上百个实例参与的大型数据库系统。这个设计的可伸缩性能使很多复杂查询的工作负载达到线性增长。这样，因为数据库分区接近线性的扩展特 征以及数据规模能达到数百 T 以及上百个 CPUs，数据库分区在数据仓库和 BI 工作负载下变得非常流行。由于每个事物都会产生实例内部的通讯，而这即使很少却也能严重影响在 OLTP 工作负载中常见的短期执行事务类型，因此这个架构在 OLTP 处理上用的较少。

不共享 hash 分区是把记录 hash 分布到逻辑数据分区上。 Hash 分布的主要的设计目的是确保数据均匀分布到所有逻辑节点（因为范围分区容易发生数据倾斜）。这些分区可能存在于一个服务器中或者分布到一批物理机器上，如图 9 所示：

不 共享数据库的扩展性已经在大多数查询工作负载接近于线性的扩展上得到了证明。同样，模块化的设计让使它的存储压力、工作压力或它们两个也都线性增长。结果 就是在过去十年中不共享结构在数据仓上的统治地位。数据库分区可以在不影响现有应用代码的情况下使用，而且对它们是完全透明的。使用 redistribution 实用工具，可以在线更改分区策略而应用代码不会受到影响。

最主要的选择就是决定哪些列用来 hash 分割每个表，并且这些列包含数据库分区键值。这有两个目标：

1. 在各个分区间均匀的分布数据。这要求选择那些高基数值的列为分区键，以确保所有行能均匀的分布到各个逻辑分区上。
2. 把在 join 处理中数据库分区之间的数据传输减少到最小。如果在 WHERE 子句中包含分区键，那么将对被连接的行的表并置（避免移动数据）。

使用下面的数据库分区的最佳实践：

• 选择用大量值（高基数）作为分区键，以确保表中的行能均匀的分布到各个分区。唯一键是一个很好的候选。
• 为了提高表并置，可以对一个经常进行连接的表使用分区键作为连接列（假如这些列有很高的基数来满足均匀分布行的需求）。
• 选择能获得高基数并能在分区键中均匀分布行的最小的列数。减少分区键中的列数提高了列出现在连接谓词中的可能性（提高表并置的几率）。
• 确保唯一索引是分区键的超集。
• 对小表（表大小只有或不到数据库大小的 3% 或不到最大的表的 5% ，是一个合理的法则）或经常被更新的表，使用复制 MQTs ，目的是：
  • 提高表并置和减少网络间的数据移动。
  • 有助于在连接中的表并置
• 允许提前计算表中的值，从而提高在一个分区数据库环境中经常执行连接的性能。
  

  例如：

  CREATE TABLE R_EMPLOYEE                        AS (                        SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME,                        WORKDEPT                        FROM EMPLOYEE                        )                        DATA INITIALLY DEFERRED REFRESH IMMEDIATE                        IN REGIONTABLESPACE                        REPLICATED;

  
  

  要更新复制物化查询表中的内容，可以运行下面的语句：

  REFRESH TABLE R_EMPLOYEE;

  
  

  注意：在使用 REFRESH 语句后，你应该在复制表中运行你在其他表中运行的 RUNSTATS 语句。

• 考虑到不同的值和在相应实施表的列中的数据倾斜，应该把最大的维度和事实表进行并置。
• 复制小的维度，这里“ small ”是相对的，而且是基于安装的可用存储。
• 复制一个水平的或者垂直的维度子集：
  • 将主键上剩余的所有维度进行分区。
  • 在为了提高表并置而创建了一个复制表后，不要忘了收集表和索引的统计信息（或者使用 DB2 自动收集统计信息功能）。不要忘了在复制 MQT 表中实现与你在基础表中定义相同的索引。
  • 如果他们很小而且很少被更新，就定义复制 MQTs 为 REFRESH IMMEDIATE 。否则就使用一个延迟更新策略。
• 把大型表分布在多个分区上。如果小表的记录不到一百万条，就可以只放在一个数据库分区中。

在设计不共享数据仓库分区时的其他问题是如何判断最佳的内存、CPUs、总线、存储能力、存储带宽的组合，那么，它们分别需要多少呢？

要解决这个问题，IBM 提供了 IBM Balanced Warehouse，它基于不共享架构的数据库系统。它是通过成功的客户运行开发的 IBM 最佳实践。

Balanced Warehouse 和 Balanced Configuration Units（BCU）

Balanced Warehouse 又被称作 Balanced Configuration Units（BCU）的构件。这些构件是为了性能进行预配置、预测试而调试过的，用以提供一个理想的数量和比例的资源。 BCU 结合了数据库配置和硬件组成的最佳实践，尽可能的简化数据仓库的安装和配置。几十个资源率和数据库配置的最佳实践都被整合进了 Balanced Warehouse 。

图 10：显示了 Balanced Warehouse 在 2007 年和 2008 年提供的多种产品。你可以看到，Balanced Warehouse 现在提供了 3 种产品：C、D 和 E 。这 3 种产品提供了持续增强能力和可扩展性的解决方案。 C 级别是一个提供给市场的入门级别，或可以集成进一台服务器中的系统。 D 和 E 级利用 DB2 数据库分区能力，提供了可以扩展到相当规模的配置。

表（范围）分区的最佳实践

表分区 应该被用来显著的提高转入和转出数据。它允许管理员添加一个大范围的数据（比如某月新的数据）到一个表中 en-masse 而且更重要的是管理员几乎可以同时从表或从数据库 en-masse 中删除数据。

DB2 数据库系统的唯一异步索引清除技术意味着就算全局索引的数据跨多个范围分区，也可以从表中分离一个范围分区，甚至这个范围分区的相关索引键对进来的查询也 变得立即不可见了。这些键值会被后台进程随后以对负载几乎可以忽略不计的影响不动声色的删除。数据库分区也能通过一个叫分区清除的内部进程带来提高查询性 能的好处，在很多情况下这个进程允许查询编译器选择改良过的查询计划，这是表分区的第二个好处。

表分区也允许你把一个表分成多个范围，存储在一个数据库分区里的一个或多个物理对象中。表分区的目标是对数据进行逻辑重组，使优化数据访问和转出数据更容易。这个表的分割引用程序是透明的，而且因此可以在应用程序开发周期的任何时候进行。

更多关于表分区的信息见文章“最佳实践：数据生命周期管理”。

表分区包括下列其他属性和功能：

• 每个范围可以在不同的表空间中。
• 范围可以单独扫描。
• 某些 BI 风格的查询性能通过表分区得到了提高。
• 新的 ALTER ATTACH/DETACH 语句方便了转入和转出数据。
  • 新的 ATTACH 转入选项
  • 新的 DETACH 转出选项
• SET INTEGRITY 现在可以在线进行了（允许对老数据进行读 / 写访问）。
• 在新的范围中，对在 ATTACH+SET INTEGRITY 操作使用 ADD 加 LOAD 操作。

下面的例子显示了如何定义一个分区表：

CREATE TABLE SALES(SALE_DATE DATE, CUSTOMER INT, … )                    PARTITION BY RANGE(SALE_DATE)                    (STARTING ‘ 1/1/2006 ’ ENDING ‘ 3/31/2008 ’ ,                    STARTING ‘ 4/1/2006 ’ ENDING ‘ 6/30/2008,                    STARTING ‘ 7/1/2006 ’ ENDING ‘ 9/30/2008 ’ ,                    STARTING ‘ 10/1/2006 ’ ENDING ’ 12/31/2012 ’ );

这个语句结果会创建 4 个表对象，每个都存储一个范围的数据，如图 8 所示：

使用下列表分区的最佳实践：

• 使用表（范围）分区将范围数据快速删除（转出）。匹配范围分区的周期来转入和转出范围。例如，如果你需要根据月份转入和转出数据，那么使用月份进行范围分区是一个合理的策略。
• 在 DATE 列上的分区。转入和转出场景几乎总是基于时间的。改进过的查询执行计划（QEP）选择使用分区清除 以及那些机会的大部分也都基于数据谓词。
• 限制范围的个数。不要忘记每个范围都是至少有两个扩展数据块的表对象。要避免设计过多的分区数。调整的规则是每个范围有最少 50MB 的数据（最好每个范围有几 G 数据）。确保范围大小和你通常转出数据的大小一致。
• 添加一个新的范围，可使用 ADD 加 LOAD 操作，这通常比 ATTACH+SET INTEGRITY 操作更好，因为除非范围很小，否则后者的索引维护日志会非常大。
  
  • LOAD 实用工具增量维护索引，并对一个事件只写一个日志记录，而忽略有多少行被插入到表中。
• 考虑把不同的表分区放到不同的表空间中，可以方便备份和恢复。表分区可以通过表空间来备份和恢复。
• 避免全局索引，因为这可能会很大，它应该存放在它们自己单独的表空间中。
• 通过让范围分区键成为集群索引（非 MDC）的引导键来确保并保持数据集群。如果你的集群索引没有把分区键放在前面，那么数据将不会被正确的集群。例如，

  PARTITION BY RANGE (Month, Region)                        CREATE INDEX … (Month, Region, Department) CLUSTER

  
  
• 使用页面级别的取样来减少 RUNSTATS 时间。一个 10-20% 的样本率，提供了良好质量的统计信息以及主要的性能得到了提高。

UNION ALL 视图（UAVs）分区的最佳实践

DB2 9 表分区之前版本的分区能力，应用程序经常需要按范围对数据进行分区。通过对每个范围创建有相应约束的表，DBAs 可以通过对所有这些表创建一个 UNION ALL 视图提供一个单独的系统视图。例如：

Create Table TestQ1 (Col 1 date)                    Alter Table TestQ1                    add constraint q1_chk                    (month(dt) in (1,2,3)

对每个季度重复表创建 / 约束

Create View Test as                    Select * from TestQ1                    Union                    Select * from TestQ2

然而，表分区为编译器和优化器提供了一个对表的单独的视图。和 UAV 相比，这允对不同的范围使用许更多的侵略性谓词，而且分区数据有更多的一致性模型。在大多数数据库中，表分区现在是实现基于范围的分区的首选方法。下面将讨论一些例外。

UNION ALL 设计方法的优势是：

实用工具能在只包含一个范围的表中执行。最主要的考虑是：

复制，在某个确定范围需要复制而其他范围不需要复制时，使用 UNION ALL 视图可以得到压缩的好处（在 UAV 中的历史表可以被压缩）。

UNION ALL 视图允许设计者存储非压缩数据到需要复制对象中，同时历史数据可以存储在其他压缩对象中。

联邦向终端用户提供了一个联邦数据（存储在多个 IBM 或非 IBM 数据库中）视图。

对于 RORG，如果范围经常发生改变，就需要对范围进行重新集群或压缩。

• 大量使用的范围，可以分离到有额外索引或 MQTs 单独的表容器中，以以优化数据访问。
• 这被用来减少 REORG 的粒度。

比起 UNION ALL 分区方法，表分区具有下列优势：

• 准备时间更短（一个表，而不是一个视图中的多个表）。
• 管理更简单（一个表，而不是多个表）。
• 在转入和转出范围时，更少编目锁。
• 支持跨所有范围的唯一索引。
• 可以更好的处理复杂查询。
• 更简单的 EXPLAINs（使用 explain 工具）。

把 UNION ALL 视图迁移为表分区

可以用以下步骤来把 UNION ALL 视图迁移到表分区中，而不需要移动数据。

1. 创建一个分区表，它有一个不影响现有数据库分区的虚拟分区。这需要相同的页面大小和扩展数据块大小。
2. ALTER ATTACH 视图中的所有表。
3. 删除掉虚拟分区。
4. 运行完所有 TABLE ATTACH 命令后运行 SET INTEGRITY 。为了加速一致性检查：
   • 删除所有索引。
   • 在一致性检查完成后重建索引。

使用下面 UNION ALL 视图分区的最佳实践：

• 使用数据库分区来获得比 UNION ALL 视图更好的扩展性。
• 迁移 UNION ALL 视图到表分区中。新开发的应用程序使用范围分区，要求实施表分区而不是 UNION ALL 视图，除非你对 UAV 优势列表中的一种或多种情况有很强的需求。
• 实现把 UNION ALL 应用程序迁移到表分区，以获得 DB2 9.5 自动字典压缩提供的深度压缩的好处。

数据库分区、表分区和 MDC 在同一个数据库中的最佳实践

数据库分区、表分区和 MDC 能同时应用在一个设计中。

• 可以应用数据库分区获得扩展性，并确保在逻辑分区之间均匀分布数据。
• 表分区可以方便查询分区消除和数据转出。
• MDC 可以用来提高查询性能和方便转入数据。

对于部署大型应用程序这是一个最佳实践方法。

例如：

CREATE TABLE TestTable (A INT, B INT, C INT, D INT … )                    IN Tablespace A, Tablespace B, Tablespace C …                    INDEX IN Tablespace B DISTRIBUTE BY HASH (A) PARTITION BY RANGE (B)                    (STARTING FROM (100) ENDING (300) EVERY (100)) ORGANIZE BY DIMENSIONS (C,D)

更多信息请查看文章“最佳实践：数据生命周期管理”。

使用下面的最佳实践：

• 要部署大型应用程序，最好在同一个数据库设计中实现数据库分区、表分区和 MDC 。

使用表分区和 MDC 最佳实践来转入和转出数据

设计你的分区策略，以使用表分区来作为转出策略，并利用在单个维度的 MDC 作为你的转入策略。

例如，如果你按日转入，按月转出，在 day 上指定一个 MDC 并以 month 为表分区键（支持计算值）。

这个方法就减少了分区数，并简化了 DBA 的管理任务。它利用了 MDC 转入数据的优势（用块索引减少索引 I/O 和日志）。

更多信息见文章“最佳实践：数据生命周期管理”。

对转入和转出数据使用下面的最佳实践：

• 使用表分区来转出，并用单个维度上的 MDC 来转入。

使用表分区最佳实践来转入海量数据

可以使用表分区来为需要转入庞大的数据量的应用程序加速，这避免了执行一致性检查的需求。

还有一种连接分区的方法：你也可以 ALTER ADD 一个空表到一个表分区中。在表分区添加完成后，你可以用 Load 实用工具来填充表（允许对较老数据的只读访问）或者使用插入（这需要记日志）。

LOAD 可以提供出众的性能，而且既可以从外部文件装载，也可以使用 LOAD from cursor 从一个查询定义装载。

对应用程序使用深度压缩，DB2 9.5 在转入数据时使用这个技术非常容易，因为它提供了自动字典压缩，从而避免了用 REORG 来压缩数据的需求。

更多信息见文章“最佳实践：数据生命周期管理”。

使用下面转入和转出的最佳实践：

• 使用表分区来转入海量数据。
• ALTER ADD 一个空表到一个表分区，并使用 Load 实用工具来填充它。

MQT 最佳实践

MQT 表是基于一个查询结果定义的表。 MQT 包含了预先计算的结果。 MQTs 是一个提高复杂查询响应时间的强大的方式，尤其是有下面几类数据操作需求的查询：

• 在一个或多个维度上聚合数据。
• 在一组表之间连接聚合数据。
• 数据来自于一个常见的数据访问子集——也就是来自于“热”水平或垂直数据库分区。
• 在分区数据库环境中，数据是对一个表的再分区，或表的一部分。

为了加速查询性能，MQTs 可以被用在联邦数据源的别名上，得以在本地保持频繁访问数据。他们可以通过复制来维护（系统维护选项不被支持）。在一个分区数据库中，复制 MQTs 可以用来减少网络流量。

MQT 对应用程序是完全透明的。 MQT 的知识已经被整合进 SQL 和 XQuery 编译器中，它们会判断是否 MQT 应该被用来响应一个查询的所有部分。因此，你可以在不改变应用程序代码的情况下，创建和删除 MQTs，就和你创建和删除索引而不需要更改应用程序一样。

图 11 根据 MQTs 的刷新类型总结了它的特点。在表中，“ Optimization ”表示 DB2 数据库管理器在运行一个查询时，会利用延迟刷新的 MQT，。然而“ No optimization ”显示 MQT 不会被使用，因为他可能已经失效；。也就是说，数据库管理器不知道 MQT 什么时候被刷新了。

注意：MQTs 会降低基础表上的插入性能。

为了帮助问题诊断，DB2 9 解释工具显示了为什么一个 MQT 在访问路径中不被采用。

使用下列 MQT 设计的最佳实践：

• 对你打算用来创建 MQT 的查询语句，使用相同或更高的隔离级别。为了减少约束，隔离级别是：RR、RS、CS 和 UR 。
• 关注那些使用大量资源的查询。这些查询是利用 MQTs 来提高性能的最好的机会。
• 对 MQTs 数目，设置一个你愿意维持的限制。这么做有两个理由：
  • 每个 MQT 都会使用磁盘存储空间。
  • 每个 MQT 都会增加查找最优 QEP 的复杂度，延长查询编辑时间。
• 为 MQTs 判断一个磁盘总量限制。通常，不要分配超过一个数据仓库磁盘总量的 10% - 20% 给 MQT 。
• 考虑在 MQTs 使用索引，并在索引创建后执行 runstats 。尝试创建一个可以用于多个查询的 MQT 。常常这样的 MQT 不能完全匹配一个查询，而且可能需要索引。
• 帮助查询编译器找到匹配的 MQTs（MQT 的路由非常复杂）。利用下面的技术向编译器提供尽可能多的信息：
  • 保持 MQTs 的统计信息实时更新。
  • 在 MQT 中的外键列上，启用参考完整性（指定非强制性 RI 以避免系统开销）。确保 FK 列非空。
  • 避免有问题的 MQT 设计造成路由困难。尽量避免使用 EXISTS、NOT EXISTS 和 SELECT DISTINCT，除非 MQT 完全匹配一个查询，这些谓词会使查询编译器很难使用 MQT 。

后设计工具来提高现有数据库设计

Explain 工具的最佳实践

Explain 工具可以向你显示是否使用了设计功能。例如，它可以向你显示在 QEP 中是否访问了索引、是否使用了分区消除以及是否查询被路由到了 MQTs 。

考虑图 12 中 explain 工具显示的 TPC-H 查询关于的 QEP 片段。

QEP 明确的显示了 PARTSUPP 需要访问 TPCD.UXPS_PK2KSC 索引和 PARTSUPP 表自己的信息。你如何判断原因？

查 看 operator(15)，你可以看到 FETCH 聚集需要访问 PARTSUPP 表，因为索引包含 PS_PARTKEY 和 PS_SUPPKEY 列，却不包括 PS_AVAILQTY 列。强烈建议把 PS_AVAILQTY 列也加到索引中，这样你可以避免在辅助方案中访问 PARTSUPP 表，会因此提高性能。

图 13 中显示的 explain 输出表明了优化器在 QEP 中考虑了那些 MQTs 却没有选择，以及没有选择的原因。这个原因可能是由于成本或 MQT 匹配度不够。

使用下列 explain 工具的最佳实践：

• 利用 explain 工具来帮助理解你的设计选择。

DB2 设计顾问程序的最佳实践

DB2 设计顾问程序是 DB2 自主计算的一个关键功能。它是一键式解决方案：提供一个工作负载（由用户提供或系统提供）以及可选择的磁盘约束，设计顾问程序建议物理数据库设计选项，这 些选项设计可以优化工作负载。设计顾问程序进行广泛的“假设”分析、数据抽样和关联模式来探索上千种设计排列，这些是人工无法做到的。

设计顾问程序有下列能力：

• 索引选择
• MQT 选择
• MDC 选择
• 分区选择（对数据库分区）
• 业界领先的工作负载压缩

很多客户反映使用设计顾问程序来动态提高物理数据库设计，在整个生命周期内引导一个查询或提升整个工作负载的性能。当然，你不要不经过考虑就应用来自设计顾问程序的结果。

图 14 强调了设计顾问程序的好处。在这个例子中，一个决策支持数据库运行在 TPC-H 工作负载上而且为数据集创建了合理的索引，也就是说，一个优秀的数据库设计人员设计了这一切，并认为这已经足够了。然后使用设计顾问程序对数据库提出额外 的建议，而应用设计顾问程序的结果带来了 6.5 倍的性能提高。

DB2 设计顾问程序的 MDC 选择能力

为了提高工作负载性能，可以使用设计顾问程序 MDC 选择能力来获得 MDC 表维度的推荐值，包括基础表上的 coarsifications 。虽然在一个表上可能会推荐一个或多个维度，但是只对那些只有一列且不是组合列的维度才会被考虑。

MDC 选择能力是通过db2advis实用工具使用 -m 标记启用的。建议类型（ “ C ”对应 MDC 和集群索引、“ I ”对应索引、“ M ”对应 MQT 以及“ P ” 对应数据库分区）可以互相联合。

设计顾问程序提供的 MDC 建议，总会提供良好的密度和在表被转换成 MDC 时会发生的表膨胀总量限制。设计顾问程序中的分析操作不仅包括块索引的好处，而且也包括了 MDC 对插入的影响，以及删除表维度操作的影响。

输出包括出现在 MDC 解决方案中的 coarsified 维度的每个表的生成列描述，以及对每个表的一个 ORGANIZE BY 子句建议。

使用下列设计顾问程序的最佳实践：

• 提供能广泛代表你的工作负载的输入，不要只为一个查询运行设计顾问程序。这使设计顾问程序生成可以应用于整个工作负载的建议，而不是仅仅针对一个查询。这或许会损害其他部分的工作负载。
• 包括输入像 INSERT、UPDATE 和 DELETE 这些发生在你工作负载的操作，这样设计顾问程序能对查询的缺点和优势进行建模（关于添加新的设计功能）。例如，新的索引是否在不利于维护之外能提高查询执行时间。
• 使用 DB2 设计顾问程序的 MDC 选择能力（在大小超过 12 个扩展数据块大小的表上）来获得 MDC 的建议维度，以提高工作负载性能。
• 使用 Query Patroller 或 DB2 9.5 负载管理器，来自动以某种格式抓取你的具体工作负载作为设计顾问程序的输入。

结论

物理数据库设计对任何数据库而言都是最重要的品质。它影响数据库的灵活性、有效性、可维护性和可扩展性，就像没有其它方面的数据库管理。虽然数据库设计很复杂，但是一个良好的设计可以提升性能并降低操作风险。掌握这些无疑是专业数据库管理员的基石。

参考资料

• 通过 developerWorks Information Management 专区 学习关于 Information Management 的更多知识。在这里可以找到技术文档、how-to 文章、培训、下载、产品信息等等。
  
  
• 通过 Information Management 专区 DB2 9 技术资源中心 了解 DB2 产品家族的更多产品信息和相关技术。
  
  
• 通过 DB2 V9.7 信息中心，了解 DB2 的详细产品信息和相关技术等全面的内容。
  
  
• 通过 DB2 pureXML wiki 查找演示程序、免费下载、技术文章等的链接。
  
  
• 随时关注 developerWorks 技术活动 和 网络广播。
  
  

• 使用可直接从 developerWorks 下载的 IBM 产品评估试用软件 构建您的下一个开发项目。
  
  
• 现在可以免费使用 DB2。下载 DB2 Express-C，这是为社区提供的 DB2 Express Edition 的免费版本，它提供了与 DB2 Express Edition 相同的核心数据特性，为构建和部署应用程序奠定了坚实的基础。
  
  

• 参与 developerWorks blogs 并加入 developerWorks 社区。

关于作者

developerWorks 中国网站编辑团。