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

2009 年 5 月 20 日 物 理数据库设计是影响数据库性能的一个最重要的因素。物理数据库设计涵盖了所有和数据库物理结构相关的设计功能，比如表规范化和反规范化、索引、物化视图、 数据集群、多维数据集群、表（range）分区还有数据库（hash）分区。本文从表规范化和反规范化、索引设计方面阐述最佳实践。 更多最佳实践，请参考 DB2 最佳时间专题：DB2 for Linux, UNIX, and Windows 最佳实践。 内容提要 物理数据库设计是影响数据库性能的一个最重要的因素。物理数据库设计涵盖了所有和数据库物理结构相关的设计功能，比如表规范化和反规范化、索引、物化视图、数据集群、多维数据集群、表（range）分区还有数据库（hash）分区。 良好的物理数据库设计不仅能够降低硬件资源使用率（I/O,CPU 和网络），而且还可以并提高你的管理效率。良好的物理数据库设计依次对你的业务提供了下面好处： 增加使用数据库的应用程序性能，更快的响应时间和更高的终端用户满意度。 减少 IT 管理成本，可以为你提供管理广泛数据库的能力，并能更快的适应这个应用程序的需求变化。而且还可以缩短应用程序的需求的响应时间。 减少 IT 硬件开销 更好的缩短用于备份和恢复花费的时间 图 1 显示了一个物理数据库系统的说明。这 3 个深黑框的垂直矩形显示了 3 个不同的物理数据库系统；其他的正方形和矩形框表示的是磁盘上的存储块；所有着色的符号显示表中（比如地理或月份）的数据值。 在 这个例子中，一个表已经在 3 个实例 P1、P2 和 P3 上进行了哈希分区。同时，这个表也以 month 进行了范围分区，数据可以很容易的按月添加和删除。这也间接有助于用 month 的谓词查询。数据在每个表中被用多维集群（MDC）进行了集群，而且这是在每个 range 分区中进一步集群。表中的记录也建立了普通的基于记录的（RID-based）索引。对这个表创建一个物化查询表（MQT），它包括聚合数据（比如地理上 的平均销售），这些已经编入索引和 MDC 中了。 图 1. 物理数据库系统图解 最佳实践 表规范化和非规范化 对于大多数通用数据库系统使用第 3 范式（3NF）来规范你的表，维度查询用星型模式或雪花模型，以及对基础广泛的数据仓库 IBM 分层数据结构，在线分析处理（OLAP）和商业智能（BI）。 索引设计 使用工作负载谓词和主外键，来设计一个索引的基础集合。索引是一个最重要的物理数据库设计功能之一（对于 INSERT、UPDATE 和 DELETE 操作而言，索引和即时的 MQTs 会产生负面影响）。 数据集群和 MDC MDC 可用来提高查询性能以及转入和转出数据。 数据库分区（不共享哈希分区）。 对于大型 BI 应用程序，可以使用数据库分区以提高其可扩展性。 在选择分区键时，同时还要关注分区键值的高基数并提高连接中的表并置。对不共享数据库来说，使用哈希分区完全是为了数据仓库。 表（range）分区 使用范围集群表（RCTs）来进行对数据的快速直接的访问。 基于转入和转出特点来设计表分区。根据 month 或财季来进行分区是很好的策略。 UNION ALL 视图分区 UNION ALL 视图，允许视图底层的不同对象有不同的特征。在一般的同质性中，它提供了更干净也更好维护的架构。然而，也有例外，那就需要有混合和匹配的能力。当某个范围的数据需要复制而其他范围不需要复制时，使用 UNION ALL 视图可从压缩中得到好处。 较之 UNION ALL 视图，利用数据库分区来获得决策支持系统、商业智能、数据仓库和报告工作量的可扩展性更好。 使用表分区来提高恢复效率和转出效率。 表分区和 MDC 的数据转入和转出 使用表分区来转出或在一个单独的维度通过使用 MDC 来转入。 在同一个数据库中的数据库分区，表（范围）分区，和 MDC 在同一个数据库设计中，通过实现数据库分区、表分区和 MDC，来部署大规模的应用程序。 MQTs 对数据库分区和提高对聚合数据的查询访问，使用复制 MQTs 来提高连接中的匹配。 通 过维护 MQT 统计信息的数据来实时帮助查询编译器来查找 MQTs，定义参考完整性（包括 MQT 中被定义为 NOT NULL 的 FK 列），并定义函数依赖。避免有问题的 MQT 设计因为使用 EXISTS、NOT EXISTS 和 SELECT DISTINCK 子句而发生路由困难，除非 MQT 和查询非常匹配。 对现有数据库使用 post-design 工具可以改善设计 使用解释工具来帮助我们了解你的设计选择。 使用 DB2 设计顾问程序来对物理数据库设计改善（索引、MQTs 和分区）生成计划。如果这样做了，需要提供输入一批查询而不是一次一个查询。这让设计顾问程序可以在整个工作负载中做出取舍。 利用 DB2 9.5 工作负载管理器（WLM），、Query Patroller、快照脚本或语句事件监控器来自动抓取 SQL 语句以输入解释工具以及 DB2 设计顾问程序。 回页首 介绍物理数据库设计 数据库设计分三个阶段执行： 逻辑数据库设计，包括：收集需求和具体关系模型。 把逻辑设计转换成表定义（通常由一个应用程序开发人员执行），包括：部署前设计、表定义、规范化、主外键关系和基础索引。 部署后物理数据库设计（通常由数据库管理员执行），包括提高性能，减少 I/O 和精简管理任务。 物理数据库设计涵盖了数据库设计中影响数据库在磁盘上的具体结构的各个方面，如上面的条目 2 和 3 。 虽然你可以独立于数据库最终使用的平台之外来进行逻辑设计，但是大量物理数据库属性仍依赖于目标 DBMS 的具体内容和语义。物理数据库设计包括下面的属性： 表规范化 表非规范化 索引 集群 MDC 数据库分区 范围分区 MQTs 内存分配 数据库存储拓扑 数据库存储对象分配 本文包含了所有属性，除了“数据库存储拓扑”和“数据库存储对象分配”这些属性包含在“数据库存储最佳实践”白皮书中。这篇白皮书以及在它里面涉及的文章可以在 DeveloperWorks 的信息管理区里面找到：（ 物理数据库设计和数据库本一样古老，第一个关系型数据库原型诞生于 1970 年。因为关系型数据库很先进，所以新的技术被引入以提高操作效率。数据库设计的最初问题是关于表规范化和索引选择，这两点都会在后面讨论到。 今 天，我们可以通过正确的分割数据、分布数据和提高索引数据来达到 I/O 降低的目的。所有这些创新都提高了数据库的能力，扩展了物理数据库设计的范围，以及增加了设计选择，这也导致优化数据结构变得更复杂。虽然引入新的物理数 据库设计能力主导了 1980s 和 1990s ，然而从那以后就致力于通过自动化和最佳实践来简化过程。 绝大多数物理数据库设计功能和属性都以在运行中减少 I/O 使用为主要目的。然而，在较低程度上，有的在“物理设计方面”帮助提高管理效率和减少 CPU 或网络使用。另外，在 DB2 分区环境中，数据库设计会影响并行度，例如，并行查询处理。 DB2 9.5 数据库系统提供的功能以及工具，已经实现了本文中的最佳实践。 回页首 读者要求 你熟悉所描述的物理数据库设计功能。因此，本文对于每一个功能只有很简略的描述。本文关注的是应用这些功能的最佳实践。关于每个功能的详细信息请参考 DB2 产品文档。 回页首 物理数据库设计的目的 一个高质量的物理数据库设计要达到下面的目标： 将 I/O 减到最小。 均衡设计功能，同时用来优化查询性能、事务性能和维护操作。 提高数据库管理效率，比如数据的转入和转出。 提高管理任务性能，比如索引创建或备份和恢复的过程。 把备份和恢复所需要花费的时间减到最小。 回页首 表规范化和非规范化的最佳实践 表规范化就是通过减少它的关系直到最简的表格。在建立一个逻辑上的关系型数据库设计中，表规范化是一个关键步骤。规范化有助于避免冗余和不一致的数据；它通常是一个逻辑上的数据模式练习，整个结果在物理设计中实现。 部署一个规范化设计有如下目标： 消除冗余数据，例如在多个表中存储相同的数据。 通过在表中只存储相关数据来强制有效数据的依赖，并把关系数据拆分到多个相关表中。 将系统在数据结构和未来增长中的灵活性最大化 规范化 规范化的两到三个主要策略是： 第三范式在在线交易处理（OLTP）和很多通用数据库中使用，包括企业数据仓库（也叫做原子仓库）。 星型模式和雪花模式是规范化的三维模式，并且在数据仓库和 OLAP 中经常使用。 第三范式（3NF） 第三范式是由第一范式和第二范式中的规则组成的。下面是第三范式中的规则： 消除重复组。为每个相关属性的集合建立单独的表，并为每个表建立一个主键。 消除重复的列以及每个表中重复的数据。 把应用到一个表中的多行柱形数据的子集，移动到不同的表中。 在表之间用外键来建立联系 消除那些与键值无关的列。如果属性不能解释一个键值，那就把它们移到另外一个表中。 删除不依赖于主键的列 数据库设计中的第一范式，第二范式和第三范式 下面的图表显示了数据库设计的第一范式，第二范式，第三范式。 非规范化模式 第一范式： 为了让非规范化模式遵守第一范式，重复的数据元素组、客户地址行和客户名称被规范化到不同的表中。 第二范式： 这个模式要遵守第二范式就必须遵守第一范式，并且所有属性必须完全依赖于一个组合键。 第三范式： 这个模式要遵守第三范式就必须消除所有传递依赖。当一个在非键值域的值取决于另一个非键值域，也就是非组合键的一部分中的值时，就发生了传递依赖。 星型模式和雪花模式 星型模式和雪花模式在数据仓库 BI 系统中已经变得非常普遍。星型模式的基础是从它的维度中分离出系统的事实表。维度是作为数据的属性来定义的，比如 location 或 customer name、或部分描述、事实表参考和数据相关的具体时间。 例 如，一个部分描述通常不会随着时间流逝而变化，因此它可以定义为一个维度。与此相反，部分每日销售是随时间变化的，因此是事实。之所以叫做星型模式是因为 它的典型特点是一个保存了随着时间变化的大型的中央事实表，被一批维度表所环绕，其中存放着和事实表中事件条目的相关属性。 雪花模式简单的扩充了星型模式。在一个雪花模式设计中，较低基数的属性经常从星型模式中的一个维度表移动到另外一个维度表中，并将这两个维度表建立起关系。 非规范化 和规范化相比，非规范化是压缩表数目的处理过程，因此有可能增加数据库中的冗余数据。非规范化可以非常有效的减少复杂性或进行表连接的数目，并通过减少表的数目来减少数据库的复杂性。非规范化的主要目的是将一个系统的性能最大化并降低系统管理的复杂度。 IBM 分层数据结构 IBM 分层数据结构为用户提供了多级的。每一层提供了不同的细节和数据摘要的级别来满足用户的需要，以便用户（分析者和执行人员）访问。数据年龄随着层次增加而 增长（更多的表和每个表更少的数据）。这个结构是专门为混合工作负载、查询性能、快速合并新的数据源以及部署新的应用程序而设计的。 分层结构启用并行的装载、查询、归档和维护，而不需要牺牲查询性能。多级数据可以用于多种类型的分析。 图 2 显示了 5 层的 IBM 分层数据结构 图 2. IBM 分层数据结构 利用这个模式，数据库仓库管理员可以： 使用可视工具来优化设计多层数据仓库模式。 使用首选抽取、转换和装载（ETL）软件用来自于无数企业数据源规模、速度和丰富的变化来大块装入数据仓库的中间过渡层。 使用 SQL 数据仓库工具（SQWs）维护性能上和商业访问层上的分析结构，- 或者替换在仓库中的手工编码的 SQL 流程。 关于这个多层结构的更多细节信息请参考“ Best Practices for Creating Scalable High Quality Data Warehouses with DB2 ”。 使用以下规范化和非规范化的最佳实践： 只要有可能就对大多数 OLTP 和通用数据库使用第三范式设计，以在系统设计中保持灵活性。这是经过考验的规范化模式。 对 于那些性能要求非常高的数据仓库和数据集市，一个星型模式或雪花模式通常是最优的维度查询处理模式。当然，还要验证星型模式或雪花模式是否遵守你设计的在 规范化的逻辑数据模式中的关系。在“ Best Practices for Data Life Cycle Management ”白皮书中有更多关于关系型数据结构的用户的逻辑模式信息。 有广泛基础的数据仓库可以有很多用途，比如图 1 所示的可操作数据仓库、报告、OLAP 和立方体、，通常使用分层数据结构。分层数据结构是一个强大的范式，在这里，有限的文字不能详细描述。详细信息请参见“更多阅读”章节。 考虑非规范化那些非常小的表，每个记录长度是 30 或更少字节。数据库多余的表增加了查询的复杂性和管理的复杂度。 回页首 索引设计最佳实践 索引对性能来说非常关键。数据库使用它们以达到下列目的： 应用谓词来提高快速查询数据在数据库中的位置，减少查阅的行数。 避免 ORDER BY 和 GROUP BY 子句产生的索引。 引导连接的顺序。 提供 index-only 的访问，这避免了访问数据页的成本。 作为在关系数据库中强制唯一性约束的唯一方法。 然而，索引需要额外的硬件资源： 它们增加 UPDATE、INSERT、DELETE 和 LOAD 操作的额外的 CPU 和 I/O 成本。 它们会增加准备时间，因为它们为优化器提供更多的选择。 他们会使用非常多的磁盘存储。 在 DB2 数据库系统中，一个 B+ 树结构被用作实现索引的底层结构。所有数据都存储在叶子节点，而且键值被随意的链接进一个双向方式中以提供双向的索引扫描。如果指定了 DISALLOW REVERSE SCANS，那么索引不能被反向扫描（不过物理上它是和一个 ALLOW REVERSE SCANS 索引是一样的）。 集群索引 集群索引（特殊索引）告诉数据库管理器这个表中的索引对象必须根据索引定义在磁盘上被集群在一起。例如，如果集群索引被定义在一个日期键上，那么，数据库管理器将尝试在磁盘上、在表对象中，把有相同日期的记录存储在彼此周围。 图 3 中的表定义了两个基于记录的索引： 一个是在“ Region ”上的集群索引 。 另外一个索引在“ Year ”上。 图 3. 一个集群索引的普通表 这个集群的价值是，如果后续查询有在集群属性上的谓词，就只需要运行已经大幅度减少的 I/O 。例如，一个以日期为条件进行的销售查询，如果被查询的日期的相关记录就在附近，因此这只需非常少的 I/O 。 然而，集群索引不仅仅是到数据库的一个指示器。而且当新数据被插入到数据库中时，DB2 内核将尝试把这些记录放在有相同或相似属性的记录附近。如果空间不可用的话，新增加或更改的记录会被重定向到其他非集群位置（也就是说不在相关记录附近）。 当一个 INSERT 发生时（或对集群键值的一个 UPDATE）DB2 内核会扫描集群索引来为这个记录判断一个恰当的位置。因此，在一个有集群 INDEX 的表上的 INSERT 和一些 UPDATE 操作会导致对索引访问的开销，这在非集群索引上不会发生。 因此，集群索引提供了接近于集群的集群，而且随着时间推移数据经常变得不在集群。 REORG 实用工具可以把数据记录重组为完美的集群顺序，虽然这可能是一个费时而且日志密集的操作。 要创建一个集群索引，如下面例子显示的，只需将 CLUSTER 键值简单添加到创建索引语句中，在这里一个集群索引 MyIndex 将基于 T1 表中的 C1 列被创建。每个表中只能有一个集群索引。 CREATE INDEX MyIndex on T1 (C1) CLUSTER 随着时间的推移数据集群会影响使用集群索引的时间，所以使用 MDC 进行集群是最佳实践的首选，因为它在任何时候都能保证集群，并提供了同时对多个维度进行集群的并行性。请参见在后面的关于如何判断使用何种方法中对 MDC 的讨论。 利用下面定义索引的最佳实践： 在一个数据库中对每一个主键（PK）和大多数外键（FKs）建立索引。大多数连接发生在主键和外键之间，因此只要有可能就在所有 PKs 和 FKs 上建立索引。索引建立在 FKs 上可以提高参照完整性检查的性能。 为主键明确提供一个索引。如果不指定，DB2 数据库管理器则会用系统生成的名字自动对主键建立索引。自动生成索引时，系统生成的名字很难管理。 在 WHERE 子句中频繁出现的列，是建立索引非常好的候选。如果谓词只提供了很小的过滤的情况下是这个规则的一个例外。一个例子就是一个不等式，比如 WHERE cost <>4 。因为只提供有限过滤，所以在不等式中很少使用索引。 在用于相等和范围查询的列上指定索引。 为每个和维度连接的事实表中列的集合创建一个索引。这些列不必是外键的一部分。创建索引允许星型连接访问来计划使用动态位图说因 ANDing 。考虑在事实表的列组合上创建索引。 例如，如果 PRODKEY 和 STOREKEY 连接到生产并且各自存储维度，那么可以考虑在 PRODKEY、STOREKEY 上创建一个索引。这将有助于一个hub 或 cartesian星型连接访问计划。 使用 db2pd 命令，这显示了从最高到最低使用索引的时间。这有助于判断哪个索引经常被使用，例如： db2pd -db MY_DATABASE -tcbstats index 该 指数是参照使用 IID，对于这个索引，它能被链接到 SYSIBM.SYSINDEXES 的 IID 。输出的最后（下面分两段显示）是一个索引统计信息的列表。“ Scans ”显示每个索引上的读取访问，这个输出中的其他指示器提供了对这个索引的写 / 更新活动的深入理解。 输出的左边： 输出的右边： 使用 DB2 设计顾问程序来显示对一个特定工作负载发现哪个索引永远不会被访问，就可以被删除。 仅在绝对必要的情况下添加索引。记住，索引会极大的影响 INSERT、UPDATE 和 DELETE 性能并需要存储空间。 为了减少频繁重组的需求，在一个集群索引创建时指定一个恰当的 PCTFREE 在索引的每个叶子节点创建时留出一定比例的空闲空间。在未来的活动中，记录可以插入到索引中，以便减少页分割的可能性。页分离导致索引页面不连续，这会导致索引页面预取的效率降低。 注意：指定的 PTCFEE 是在你创建关系型索引或重组时就保留出来的。 删除并重建或重组关系型索引，会创建一个新的页面集合且基本上连续并能提高索引页面预取性能。虽然会更耗时和耗费资源，但是 REORG TABLE 使用工具同样确保数据页的集群。在扫描访问大量数据页面时，集群对索引有很大的帮助。 使用范围或用 ORDER BY 子句来测试查询，以判断集群的维度。 集群索引会导致 INSERTs 和一些 UPDATE 的额外的开销。如果你的工作负载需要使用大量索引，你需要权衡集群对查询的好处和给 INSERTS 和 UPDATES 带来的额外开销。在大多数情况下，好处远远大于成本，不过也有例外。 避免或删除冗余索引。 一个冗余索引的例子就是：一个索引有一个account number 列，而另外一个索引也有和第一个索引相同的 account number 列。有相同或相似的列的索引会使查询优化更复杂，占用存储，并严重影响 INSERT、UPDATE 和 DELETE 性能，而且通常没有好处。 虽然 DB2 数据库系统提供了动态位图索引、索引 ANDing、和索引 ORing，但是如果这些列经常在 WHERE 子句中被指定，最佳实践是指定组合索引也称作被多列索引。 为一个组合索引选择引导列。 引导列应该反映出经常被 WHERE 子句使用的列。 WHERE 子句中使用的引导列也被称为“匹配索引查询”，DB2 数据库系统只能从上到下的访问 B-tree 索引。如果一个索引的引导列不在 WHERE 子句中，优化器可能仍然会使用这个索引，但是优化器会被强制采用一个针对整个索引的“不匹配索引扫描”。 参考资料 学习 参 考 S. Lightstone, T. Teorey, T. Nadeau 的书籍：“Physical Database Design: the database professional's guide to exploiting indexes, views, storage, and more”， Morgan Kaufmann Press, 2007. ISBN: 0123693896。 获得产品和技术 现在可以免费使用 DB2。下载 DB2 Express-C，这是为社区提供的 DB2 Express Edition 的免费版本，它提供了与 DB2 Express Edition 相同的核心数据特性，为构建和部署应用程序奠定了坚实的基础。 下载 信息管理软件试用版，体验它们强大的功能。 讨论 参与 developerWorks blog 并加入 developerWorks 社区。 关于作者   developerWorks 中国网站编辑团。