2009 年 9 月 24 日 物 理数据库设计是影响数据库性能的一个最重要的因素。物理数据库设计涵盖了所有和数据库物理结构相关的设计功能,比如表规范化和反规范化、索引、物化视图、 数据集群、多维数据集群、表(range)分区还有数据库(hash)分区。本文从MDC、数据库分区、视图以及后设计工具方面阐述物理数据库设计最佳实 践。
MDC 是一个同时有不止一个维度数据集群的技术。然而,你也可以对一个维度使用 MDC 集群,就像你可以使用一个集群索引一样。 MDC 的一个好处是它能保证数据一直处于集群状态,永远不需要执行一个重组操作来重新建立较高的集群命中率。 而 且,不同于用 CREATE INDEX 语法创建的传统索引,MDC 是对表中的每一行建立索引,MDC 通过块把表中的数据编入索引。每个块和表所在的表空间中的扩展数据块有相同的大小。当 CREATE INDEX 命令创建索引时,每一行数据页会同时被编入索引,每个 MDC 表索引 BLOCKS 可以包含上千条数据。 MDC 索引,也叫 BLOCK INDEX,通常只有 1/1000 大小基于索引的行,而且不光为索引提供了大量的保留存储空间,同时也为所有 BLOCK INDEX 操作(索引扫描、索引 ANDing、索引 ORing,等等)提供了非常好的性能。 要理解 MDC,你必须首先理解一些基础术语:单元是表容纳数据的部分,它有一套唯一的维度值 ,是每个维度切片相交形成的。块是存储单元等于一个扩展数据块(一个或多个页面),块被用来存储一个单元。 如 上面所述,除了它们指向的是块而不是记录,块索引在结构上和普通索引是一样的。块索引比普通索引要更小,因为块大小是一个页面中的平均记录数的数倍。如图 4 中所示,同每一行每一个单独的输入相比,在一个块索引中每块都有一个单独的索引。结果就是,一个块索引显著降低了磁盘使用率并明显加快了数据访问的速度。 图 4. 行索引和块索引有什么不同 ![]() 图 5 中显示的 MDC 表示一个物理组织,比如所有记录有相同的“ Region ”以及“ Year ”值都同每个块或扩展数据块中连在一起。 图 5. 一个多维集群表 ![]() 甚至一个只定义一个维度的 MDC 表也可以从这些 MDC 属性中获益,而且可以替换一个有集群索引的普通表。这个决定应该基于很多因素,包括组成工作负载的查询以及表中数据的性质和分布。 在 MDC 表中自动维护数据集群确保了使用复合块索引。这些索引被用来根据在 INSERTS 操作过程中的表的维度来动态管理和维护数据库的物理集群。当一个插入发生了,合成块索引将会在与被插入记录的维度值相应的逻辑单元中被探测到。 如图 6 所示,在索引中的逻辑单元的键值,它的块 ID(BIDs)的列表完整的给出了表中有这个本地单元维度值的块列表。这限制了给插入记录搜索可用空间的表的扩展数据块的数目。 图 6. 在‘ YearAndMonth ’,‘ Region ’上的复合块索引 ![]() 因 为集群是自动维护的,所以对 MDC 表来说不需要重组操作来重新集群数据。然而,重组仍然被用来释放空间。例如,如果单元有很多分散的块,数据很少能够匹配到,或者,如果表有很多指针溢出, 这个表的重组操作将压缩属于逻辑单元的记录到最少的块中,用时也删除指针溢出对。 MDC 的意义非常深远。在某些情况下它能将复杂查询的性能提高 10 倍以上,同时你还可以用它转入和转出数据。还有下面显示的其他好处:
下面的例子显示了如何定义一个 MDC 表:
ORGANIZE By 子句定义了集群的维度。这个表同时在 C5 和 C3 上被集群。 C1 是 coarsified C5,它包含很少的不同值(天减少为月)。 注意:由 coarsified 产生的 columns(s) 被使用在 MDC 块索引中,以提高单元级别的数据清除。 MDC 设计的一个关键是谨慎选择集群的维度。如果你选择的集群维度产生太多的单元,存储的成本会显著的增加。知道其中的原因非常重要。在一个 MDC 表中,每个单元都会根据需要的存储块来分配。存储块是设计为和表所在表空间的扩展数据块的大小相等。如果一个单元没有数据,那么存储块的数据是 0 。然而,在一个单元中存储了若干记录的典型表中会造成给单元分配一个或多个存储块。对每个有数据的单元都会有一批通常只包含了被部分填充数据块。因此,在 每个单元(不是数据块)中造成浪费和存储块的大小成比例。新数据库只有个在前面的数据块被填满(或快满)后被创建。如果行被删除,数据库管理器会尝试尽可 能多的重用空间。 存储块被设计为同这个表所在表空间的扩展数据块大小相等。如果这个表的单元数目非常大,存储的浪费也会很大。如 果 MDC 很差而且产生了大量的单元,表的存储需求会非常显著的增加,而且 MDC 的性能也会受损。然而,设计的 MDC 表只会比非 MDC 表大一点,而且在集群和数据转入转出上提供了较大的好处(将在后面讨论) 图 7 显示存储块和单元分配。如图所示,每个单元都包含了一批存储块。绝大多数块被数据填充,不过对于每个单元的最后一个块,却是或多或少被部分填充的。 图 7. MDC 单元存储 ![]() 如果你有样本数据或具体数据,你就可以使用 SQL 来为所有可能的 MDC 设计度量 MDC 需要的单元数目,如下所示:
对一个 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 数据。 想要为一个传统事实表基于 Date、Product Name 和 Region 创建一个 MDC 。这里有一些在创建 MDC 时需要考虑的变量:
开始创建 MDC 如果 MDC 直接创建在 Date, Product 和 Region 列上,每天将会产生 1,000,000 个新的单元(1 x 100,000 x 10),而每年则会产生 365,000,000 个单元。 如果区域中事务非常少,这将会有很多分散甚至是空的页面。这会导致大量没有必要的空间分配数量巨大的单元(页面)用来容纳块数据。这非常不好。 开始 MDC 的创建 使用函数来 coarsify 和限制 MDC 的基数,例如:
使用这个场景中的建议,MDC 将会每年产生 12*26*10 = 3210 个单元或每天 8-9 个单元。这会消除很多页面没有数据的情况,并对 MDC 提供合理的基数来获得性能的好处。 MDC 是设计来为查询和许多删除场景提供极大性能好处。虽然如此,在对使用了集群索引的集群表提供显著的性能好处的同时,MDC 表这么做所带来的开销还是超过了非集群表。 MDC 和非集群表相比第一个开销是:
然而,当和使用集群索引而集群起来的表相比较的话,MDC 有着显著的性能优势:
无 论使用集群索引还是用 MDC 来进行索引,在这两种情况下 DB2 数据库管理器都将在 INSERT 到目标记录的位置时访问索引(块索引的集群索引),并在 INSERT 过程中判断记录的目标位置。再强调一下这个索引非常小,而且树的高度通常很短,因此搜索速度非常快。 MDC 的价值要比集群索引高很多,因为它可以自动保证集群。根据 coarsification 的需求,MDC 的集群率通常可以达到 93%-100% 之间的某个值。相比之下,集群索引虽然可以在一开始就接近 100%,但是随着时间的推移却会变得不集群,因而需要花费时间来对数据重新集群。一般情况下,使用 MDC 在你的数据库上创建和维护数据集群,除非:
使用下面的 MDC 设计的最佳实践:
注意:BLOCK 索引与相应的表相比非常的小,在大多数情况下,你可以忽略它们的存储需求。
数据库分区是一个在数据库中跨多个彼此合作的实例以建立单个大型数据库服务器的水平分布记录的技术。这些实例可以位于一个服务器中、跨多个物理机器、或它们的组合。在 DB2 产品中,这个叫数据库分区功能(DPF)。 数 据库分区允许 DB2 数据库管理器扩充到上百个实例参与的大型数据库系统。这个设计的可伸缩性能使很多复杂查询的工作负载达到线性增长。这样,因为数据库分区接近线性的扩展特 征以及数据规模能达到数百 T 以及上百个 CPUs,数据库分区在数据仓库和 BI 工作负载下变得非常流行。由于每个事物都会产生实例内部的通讯,而这即使很少却也能严重影响在 OLTP 工作负载中常见的短期执行事务类型,因此这个架构在 OLTP 处理上用的较少。 不共享 hash 分区是把记录 hash 分布到逻辑数据分区上。 Hash 分布的主要的设计目的是确保数据均匀分布到所有逻辑节点(因为范围分区容易发生数据倾斜)。这些分区可能存在于一个服务器中或者分布到一批物理机器上,如图 9 所示: 图 9. 表上的 hash 分区 ![]() 不 共享数据库的扩展性已经在大多数查询工作负载接近于线性的扩展上得到了证明。同样,模块化的设计让使它的存储压力、工作压力或它们两个也都线性增长。结果 就是在过去十年中不共享结构在数据仓上的统治地位。数据库分区可以在不影响现有应用代码的情况下使用,而且对它们是完全透明的。使用 redistribution 实用工具,可以在线更改分区策略而应用代码不会受到影响。 最主要的选择就是决定哪些列用来 hash 分割每个表,并且这些列包含数据库分区键值。这有两个目标:
使用下面的数据库分区的最佳实践:
在设计不共享数据仓库分区时的其他问题是如何判断最佳的内存、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 数据库分区能力,提供了可以扩展到相当规模的配置。 图 10. Balanced Warehouse 的产品,2007-2008 ![]()
表分区 应该被用来显著的提高转入和转出数据。它允许管理员添加一个大范围的数据(比如某月新的数据)到一个表中 en-masse 而且更重要的是管理员几乎可以同时从表或从数据库 en-masse 中删除数据。 DB2 数据库系统的唯一异步索引清除技术意味着就算全局索引的数据跨多个范围分区,也可以从表中分离一个范围分区,甚至这个范围分区的相关索引键对进来的查询也 变得立即不可见了。这些键值会被后台进程随后以对负载几乎可以忽略不计的影响不动声色的删除。数据库分区也能通过一个叫分区清除的内部进程带来提高查询性 能的好处,在很多情况下这个进程允许查询编译器选择改良过的查询计划,这是表分区的第二个好处。 表分区也允许你把一个表分成多个范围,存储在一个数据库分区里的一个或多个物理对象中。表分区的目标是对数据进行逻辑重组,使优化数据访问和转出数据更容易。这个表的分割引用程序是透明的,而且因此可以在应用程序开发周期的任何时候进行。 更多关于表分区的信息见文章“最佳实践:数据生命周期管理”。 表分区包括下列其他属性和功能:
下面的例子显示了如何定义一个分区表:
这个语句结果会创建 4 个表对象,每个都存储一个范围的数据,如图 8 所示: 图 8. 数据范围表分区 ![]() 使用下列表分区的最佳实践:
DB2 9 表分区之前版本的分区能力,应用程序经常需要按范围对数据进行分区。通过对每个范围创建有相应约束的表,DBAs 可以通过对所有这些表创建一个 UNION ALL 视图提供一个单独的系统视图。例如:
对每个季度重复表创建 / 约束
然而,表分区为编译器和优化器提供了一个对表的单独的视图。和 UAV 相比,这允对不同的范围使用许更多的侵略性谓词,而且分区数据有更多的一致性模型。在大多数数据库中,表分区现在是实现基于范围的分区的首选方法。下面将讨论一些例外。 UNION ALL 设计方法的优势是: 实用工具能在只包含一个范围的表中执行。最主要的考虑是: 复制,在某个确定范围需要复制而其他范围不需要复制时,使用 UNION ALL 视图可以得到压缩的好处(在 UAV 中的历史表可以被压缩)。 UNION ALL 视图允许设计者存储非压缩数据到需要复制对象中,同时历史数据可以存储在其他压缩对象中。 联邦向终端用户提供了一个联邦数据(存储在多个 IBM 或非 IBM 数据库中)视图。 对于 RORG,如果范围经常发生改变,就需要对范围进行重新集群或压缩。
比起 UNION ALL 分区方法,表分区具有下列优势:
可以用以下步骤来把 UNION ALL 视图迁移到表分区中,而不需要移动数据。
使用下面 UNION ALL 视图分区的最佳实践:
数据库分区、表分区和 MDC 能同时应用在一个设计中。
对于部署大型应用程序这是一个最佳实践方法。 例如:
更多信息请查看文章“最佳实践:数据生命周期管理”。 使用下面的最佳实践:
设计你的分区策略,以使用表分区来作为转出策略,并利用在单个维度的 MDC 作为你的转入策略。 例如,如果你按日转入,按月转出,在 day 上指定一个 MDC 并以 month 为表分区键(支持计算值)。 这个方法就减少了分区数,并简化了 DBA 的管理任务。它利用了 MDC 转入数据的优势(用块索引减少索引 I/O 和日志)。 更多信息见文章“最佳实践:数据生命周期管理”。 对转入和转出数据使用下面的最佳实践:
可以使用表分区来为需要转入庞大的数据量的应用程序加速,这避免了执行一致性检查的需求。 还有一种连接分区的方法:你也可以 ALTER ADD 一个空表到一个表分区中。在表分区添加完成后,你可以用 Load 实用工具来填充表(允许对较老数据的只读访问)或者使用插入(这需要记日志)。 LOAD 可以提供出众的性能,而且既可以从外部文件装载,也可以使用 LOAD from cursor 从一个查询定义装载。 对应用程序使用深度压缩,DB2 9.5 在转入数据时使用这个技术非常容易,因为它提供了自动字典压缩,从而避免了用 REORG 来压缩数据的需求。 更多信息见文章“最佳实践:数据生命周期管理”。 使用下面转入和转出的最佳实践:
MQT 表是基于一个查询结果定义的表。 MQT 包含了预先计算的结果。 MQTs 是一个提高复杂查询响应时间的强大的方式,尤其是有下面几类数据操作需求的查询:
为了加速查询性能,MQTs 可以被用在联邦数据源的别名上,得以在本地保持频繁访问数据。他们可以通过复制来维护(系统维护选项不被支持)。在一个分区数据库中,复制 MQTs 可以用来减少网络流量。 MQT 对应用程序是完全透明的。 MQT 的知识已经被整合进 SQL 和 XQuery 编译器中,它们会判断是否 MQT 应该被用来响应一个查询的所有部分。因此,你可以在不改变应用程序代码的情况下,创建和删除 MQTs,就和你创建和删除索引而不需要更改应用程序一样。 图 11 根据 MQTs 的刷新类型总结了它的特点。在表中,“ Optimization ”表示 DB2 数据库管理器在运行一个查询时,会利用延迟刷新的 MQT,。然而“ No optimization ”显示 MQT 不会被使用,因为他可能已经失效;。也就是说,数据库管理器不知道 MQT 什么时候被刷新了。 图 11. MQT 刷新特点的总结 ![]() 注意:MQTs 会降低基础表上的插入性能。 为了帮助问题诊断,DB2 9 解释工具显示了为什么一个 MQT 在访问路径中不被采用。 使用下列 MQT 设计的最佳实践:
Explain 工具可以向你显示是否使用了设计功能。例如,它可以向你显示在 QEP 中是否访问了索引、是否使用了分区消除以及是否查询被路由到了 MQTs 。 考虑图 12 中 explain 工具显示的 TPC-H 查询关于的 QEP 片段。 图 12. TPC-H 查询 20 的 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 匹配度不够。 图 13. 使用 explain 工具来理解 MQT 选择 ![]() 使用下列 explain 工具的最佳实践:
DB2 设计顾问程序是 DB2 自主计算的一个关键功能。它是一键式解决方案:提供一个工作负载(由用户提供或系统提供)以及可选择的磁盘约束,设计顾问程序建议物理数据库设计选项,这 些选项设计可以优化工作负载。设计顾问程序进行广泛的“假设”分析、数据抽样和关联模式来探索上千种设计排列,这些是人工无法做到的。 设计顾问程序有下列能力:
很多客户反映使用设计顾问程序来动态提高物理数据库设计,在整个生命周期内引导一个查询或提升整个工作负载的性能。当然,你不要不经过考虑就应用来自设计顾问程序的结果。 图 14 强调了设计顾问程序的好处。在这个例子中,一个决策支持数据库运行在 TPC-H 工作负载上而且为数据集创建了合理的索引,也就是说,一个优秀的数据库设计人员设计了这一切,并认为这已经足够了。然后使用设计顾问程序对数据库提出额外 的建议,而应用设计顾问程序的结果带来了 6.5 倍的性能提高。 图 14. 来自于 DB2 设计顾问程序的好处 ![]() 为了提高工作负载性能,可以使用设计顾问程序 MDC 选择能力来获得 MDC 表维度的推荐值,包括基础表上的 coarsifications 。虽然在一个表上可能会推荐一个或多个维度,但是只对那些只有一列且不是组合列的维度才会被考虑。 MDC 选择能力是通过db2advis实用工具使用 -m <advise type> 标记启用的。建议类型( “ C ”对应 MDC 和集群索引、“ I ”对应索引、“ M ”对应 MQT 以及“ P ” 对应数据库分区)可以互相联合。 设计顾问程序提供的 MDC 建议,总会提供良好的密度和在表被转换成 MDC 时会发生的表膨胀总量限制。设计顾问程序中的分析操作不仅包括块索引的好处,而且也包括了 MDC 对插入的影响,以及删除表维度操作的影响。 输出包括出现在 MDC 解决方案中的 coarsified 维度的每个表的生成列描述,以及对每个表的一个 ORGANIZE BY 子句建议。 使用下列设计顾问程序的最佳实践:
物理数据库设计对任何数据库而言都是最重要的品质。它影响数据库的灵活性、有效性、可维护性和可扩展性,就像没有其它方面的数据库管理。虽然数据库设计很复杂,但是一个良好的设计可以提升性能并降低操作风险。掌握这些无疑是专业数据库管理员的基石。 学习
获得产品和技术
|
联系客服