在 Microsoft SQL Server 2000 数据仓库中使用分区

【 tulaoshi.com - 编程语言 】

　　摘要：本文介绍如何使用分区来改善 SQL Server 2000 Enterprise Edition 中数据仓库的可管理性、查询性能和加载速度，并讨论关系型数据库和分析服务多维数据集中的矢量架构的水平分区。

　　本文讨论数据仓库中数据分区的作用。关系型数据仓库和分析服务多维数据集都支持数据分区。分区的逻辑概念在 Microsoft® SQL Server™ 的两个引擎中是相同的：通过键（例如日期）对数据进行水平分区。在关系型数据库中，分区是通过创建单独的物理表（例如为每个月的数据创建一个表）并且定义一个成员表的联合视图来实现的。与此类似，SQL Server Enterprise Edition 中的分析服务支持显式的多维数据集分区。在关系型数据库和联机分析处理 (OLAP) 引擎中，物理存储的复杂性对于分析用户是不可见的。

　　数据仓库分区的优点：

　　减少加载时间，改善数据库的可维护性。

　　解决从活动数据库中删除旧数据时出现的数据修剪问题。

　　该技术需要创建比非分区系统更复杂的数据分阶段应用程序。本文介绍设计、实现和维护水平分区数据仓库的最佳方法。

　　因为有效的分区计划可以极大地改善查询性能，所以我们极力建议您对大型分析服务系统进行分区。尽管对于某些特定的数据仓库维护问题，对关系型数据仓库进行分区是有效的解决方案，但通常不推荐您这样做。

　　分区视图联接来自一组成员的水平分区数据，使数据看起来象来自同一张表。SQL Server 2000 区分本地分区视图和分布式分区视图。在本地分区视图中，所有相关表和视图驻留在 SQL Server 的同一实例上。在分布式分区视图中，相关表中至少有一张表驻留在其他某个（远程）服务器上。建议您不要将分布式分区视图用于数据仓库应用程序。

　　矢量数据仓库围绕事实（标量）和矢量构建，从物理上通常表示为星形架构和雪花形架构，极少有同时包含事实和矢量的完全非正交化的平面表。由于矢量架构是最常见的关系型数据仓库结构，本文集中讨论这类架构的分区。下面的建议也适用于其他通用数据仓库架构。

　　许多数据仓库管理员会定期将陈旧的数据归档。例如，一个单击流数据仓库可能只将详细数据联机保留三至四个月。其他常见的规则可能是联机保留 13 个月、37 个月或 10 年，当旧数据不在活动窗口中时就归档并从数据库中删除。这种滚动窗口结构是大数据仓库通常采取的做法。

　　在没有分区表的情况下，从数据库中删除旧数据的进程需要一个很大的 DELETE 语句，例如：

DELETE FROM fact_tableWHERE date_key  19990101

　　执行该语句开销会非常大，可能比同一张表的加载进程需要更多的时间。相反，对于分区表，管理员重新定义 UNION ALL 视图以排除最旧的表，然后将该表从数据库中删除（假设已确保备份该表），这个过程几乎可以在瞬间完成。

　　后面我们会讨论到，维护分区表的费用也很高。如果数据修剪是采用分区的唯一原因，设计者应考虑以数据分解的方式从未分区的表中删除旧数据。在低优先级进程上连续运行一个每次删除 1000 行（用set rowcount 1000命令）的脚本，直至删除所有希望删除的数据。该技术可在大系统上有效运用，比创建必要的分区管理系统更为直接。根据加载量和系统使用状况，该技术适合于某些系统，并应该考虑在系统上进行基准测试。

　　加载数据最快的方法是将数据加载至空表或没有索引的表。通过加载至较小的分区表，渐变加载进程的效率将大大提高。

　　一旦已建成支持分区的数据仓库分阶段应用程序，整个系统将变得容易维护。维护活动（包括加载数据、备份和还原表）可以并行地执行，这样可以极大地改善性能。渐变填充下行数据流多维数据集的进程可以被加速和简化。

　　查询速度不应该作为对数据仓库关系型数据库进行分区的理由。对于分区和未分区的事实表，查询性能都差不多。在正确设计的分区数据库中，关系引擎仅在查询计划中包括解析查询所需的相关分区。例如，如果数据库按月分区，查询条件为 2000 年 1 月，则查询计划仅包括 2000 年 1 月的分区。结果查询将对分区表正确执行，与在分区键上带有簇索引的已索引合并表上执行的大体相同。

　　分区的主要缺点是需要管理员创建应用程序来管理分区。在尚未设计、测试和试运行应用程序来管理分区之前，将在关系型数据库中使用水平分区的数据仓库投入正式运行是不恰当的。本文的目的之一就是讨论与分区管理应用程序有关的问题和设计决策。

　　要获得最佳的查询性能，所有的查询都应将条件直接放在事实表中的筛选键上。将约束放在第二张表（例如以日期为矢量的表）的查询将包括所有分区。

　　矢量数据仓库围绕事实（标量）和矢量构建，从物理上通常表示为星形架构和雪花形架构，极少有同时包含事实和矢量的完全非正交化的平面表。典型情况下，矢量数据仓库的管理员仅对事实表进行分区；对矢量表进行分区几乎没有什么好处。在某些情况下，对包含多于一千万个成员的大型矢量表进行分区会有些好处。也可以对非矢量关系型数据仓库进行分区，本文中的一般观点仍然适用。

　　只有充分考虑系统体系结构和设计目标，才能制订有效的分区计划。即使使用相同的架构设计，仅用于填充服务分析多维数据集的关系型数据仓库可能采用一个不同于分析员直接查询的数据仓库的分区结构。带有滚动窗口的系统必须按时间分区，其他系统则不一定。

　　如果数据仓库包括分析服务多维数据集，Microsoft 建议关系型数据仓库和分析服务数据库中的分区应该为并行结构。维护应用程序被简化了：应用程序在关系型数据库中创建新表的同时创建一个新多维数据集分区。管理员仅需要掌握一种分区策略。不过，一个应用程序也可能有充分的理由对两个数据库以不同方式进行分区，唯一降低的将是数据库维护应用程序的复杂性。

　　SQL Server 数据库中的分区表可以使用可更新或可查询（不可更新）的分区视图。在这两种情况下，表分区都是由每个分区都包含正确数据的 CHECK 约束来创建的。一个可更新的分区视图支持对视图进行 INSERT (或 UPDATE 或 DELETE）操作，并将操作推入至正确的基础表。这很有益处，但数据仓库应用程序通常需要进行批量加载，而这是无法通过视图执行的。下表总结了可更新和可查询分区视图的要求、优点和缺点。

　　要求优点缺点可更新的分区视图  CHECK 约束强制使用的分区键

　　主键的分区键部分

　　无其他数据库限制的分区键部分

　　在成员表上定义的 UNION ALL 视图 查询性能：查询计划仅包括解析相关查询所需的成员表。

　　维护应用程序的简易性：数据可以被加载至 UNION ALL 视图，然后插入合适的成员表中 加载性能：通过视图加载数据的速度太慢，以至这种方式对大多数的数据仓库应用程序来说是不实用的。

　　灵活性：数据库设计对分区键可能要求额外的约束。 可查询的分区视图  CHECK 约束强制使用的分区键

　　在成员表上定义的 UNION ALL 视图 查询性能：查询计划仅包括解析查询所必要的成员表。

　　加载性能：可高效地直接将数据批量加载至成员表。

　　存储：尽管推荐声明主键并在主键上创建索引的做法，但分区视图不要求主键索引。 视图最多可有 256 个成员表。

　　必须创建维护应用程序来管理分区和加载。

　　Microsoft 建议的做法是定义主键，并将事实表设计为本地（单个服务器上）的分区联合视图。大多数情况下，该定义会产生可更新的分区视图，但数据仓库维护应用程序应设计为直接将大多数数据批量加载至成员表（而不是通过视图进行）。

　　以下代码示例用来说明定义成员表和联合视图以及将数据插入视图的语法：

-- 创建 1999 年事实表CREATE TABLE [dbo].[sales_fact_19990101] (　 [date_key] [int] NOT NULLCHECK ([date_key] BETWEEN 19990101 AND 19991231),　 [product_key] [int] NOT NULL ,　 [customer_key] [int] NOT NULL ,　 [promotion_key] [int] NOT NULL ,　 [store_key] [int] NOT NULL ,　 [store_sales] [money] NULL ,　 [store_cost] [money] NULL ,　 [unit_sales] [float] NULL)ALTER TABLE [sales_fact_19990101]ADD PRIMARY KEY ([date_key], [product_key], [customer_key], [promotion_key], [store_key]);-- 创建 2000 年事实表CREATE TABLE [dbo].[sales_fact_20000101] (　 [date_key] [int] NOT NULLCHECK ([date_key] BETWEEN 20000101 AND 20001231),　 [product_key] [int] NOT NULL ,　 [customer_key] [int] NOT NULL ,　 [promotion_key] [int] NOT NULL ,　 [store_key] [int] NOT NULL ,　 [store_sales] [money] NULL ,　 [store_cost] [money] NULL ,　 [unit_sales] [float] NULL)ALTER TABLE [sales_fact_20000101]ADD PRIMARY KEY ([date_key], [product_key], [customer_key], [promotion_key], [store_key]);--创建 UNION ALL 视图。CREATE VIEW [dbo].[sales_fact]ASSELECT * FROM [dbo].[sales_fact_19990101]UNION ALLSELECT * FROM [dbo].[sales_fact_20000101]--现在插入几行数据，例如：INSERT INTO [sales_fact]VALUES (19990125, 347, 8901, 0, 13, 5.3100, 1.8585, 3.0)INSERT INTO [sales_fact]VALUES (19990324, 576, 7203, 0, 13, 2.1000, 0.9450, 3.0)INSERT INTO [sales_fact]VALUES (19990604, 139, 7203, 0, 13, 5.3700, 2.2017, 3.0)INSERT INTO [sales_fact]VALUES (20000914, 396, 8814, 0, 13, 6.4800, 2.0736, 2.0)INSERT INTO [sales_fact]VALUES (20001113, 260, 8269, 0, 13, 5.5200, 2.4840, 3.0)

　　要验证分区是否正常工作，请使用查询分析器来显示查询计划，例如：

SELECT TOP 2 * FROM sales_fact WHERE date_key = 19990324

　　您应该看到查询计划中仅包括表 1999。将该查询计划与主键已删除的相同表生成的查询计划相比较，我们会发现：表 2000 仍然被排除。将这些计划与在已删除 date_key 约束的架构上生成的查询计划进行对比。这些约束被删除的情况下，表 1999 和表 2000 都被包括在查询中。

　　请注意，在通常情况下，在大型表上执行查询时，使用TOP N语法是好的做法，因为它可以迅速返回结果并使用最少的服务器资源。查看分区表的查询计划时，这一点尤为重要，因为由SELECT *语句生成的查询计划很难解析。对于偶尔进行观察的人而言，尽管在查询执行期间，查询中仅使用相关的表，但表面看起来好象查询计划包括了 UNION ALL 视图的所有组件表。

　　要获得最佳的查询性能，所有的查询都应将条件直接放在事实表中的筛选键上。将约束放在第二张表（例如日期矢量表）的查询将包括所有分区。对 UNION ALL 事实表的标准星号联合查询工作良好：

　　包括分区矢量（日期）的属性。

　　在分区矢量架构上设计查询与在未分区的架构上设计完全一样，只不过将日期条件直接放在事实表中的日期键上时日期条件最为有效。

　　如果每一分区表的索引中的第一个列为带日期的簇索引，转到所有分区解析某一特定查询的开销相对较小。编写预定义的查询时应尽可能提高其效率，例如那些生成标准报表或渐变更新下行数据流数据库的查询。

　　事实表可以在多个矢量上进行分区，但是大多数人可能仅按日期进行分区。如前面描述的，日期分区可以支持简单的滚动窗口管理, 较旧的分区甚至可以被保存在不同的位置，或者减少索引次数。同样，大多数对数据仓库的查询是按日期进行筛选的。

　　对于按日期分区的应用程序，决策变量为：

　　如何设计日期键？数据仓库最好对矢量表和事实表使用代理键，这是得到广泛认可的。对于按日期分区的事实表，建议的做法为使用 yyyymmdd 形式的智能整数代理键。作为整数，与 8 字节的 datetime 相比较，该键仅使用 4 个字节。许多数据仓库使用 datetime 类型的自然日期键。

　　如何确定分区的大小？尽管上面的示例使用年分区，但大多数系统会划分得更细致，例如月、星期或天。尽管我们会注意到用户查询通常是按月或周进行的，但最重要的因素还是系统总体规模和可管理性。您可能还记得，任何一个 SQL 查询最多可以引用 256 张表。对于维护多于一个月的数据的数据仓库，按天来分区的 UNION ALL 视图会超过该界限。作为一个好的规则，如果事实表仅按日期分区，那么最好按星期分区。

　　如何定义分区的范围？BETWEEN 语法最直接、可读性最强、执行效率最高。以下述形式的按月分区为例：

date_key  19990101date_key BETWEEN 1990101 AND 19990131date_key BETWEEN 19990201 AND 19990229...date_key BETWEEN 19991201 AND 19991231date_key  19991231

　　请注意其中的第一个和最后一个分区：即使您认为决不会有数据进入这些分区，这仍是一个定义分区的好方法，这样可以覆盖所有可能的日期值。同时，请注意尽管 1999 年不是闰年，但二月分区仍覆盖 2 月 29 日。该结构使设计创建分区和约束的应用程序时不需要判断是否为闰年。

　　关于如何按日期分区的详细讨论同样适用于采用其他可能的分区键时的情形。

　　数据加载：如果新的数据具有与其他矢量对齐的明显倾向，或者例如，如果每个存储或附件是由不同的系统分发的，这些就是自然的分区键。

　　多维数据集数据查询：尽管并没有技术原因要求以相同的方式对关系型数据库和分析服务多维数据集进行分区，但这是通常采用的做法。如果做出这个假设，维护应用程序将得以简化。这样，即使关系型数据库的存在仅是用于填充分析服务多维数据集，在选择分区键时也应该考虑到一般的查询模式。

　　命名水平分区事实表的成员表的规则应该从分区设计自然地引出。为获得最大的通用性，请在标题中使用完整的分区开始日期：即使分区每年进行一次，[sales_fact_yyyymmdd] 也比 [sales_fact_yyyy] 好。

　　如果数据库支持多个大小的分区，命名约定应该反映每个分区的时间范围。例如，月分区使用 sales_fact_20001101m，日分区使用 sales_fact_20001101d。

　　成员表的名称对通过视图访问数据的最终用户是隐藏的，因此成员表的名称应该面向执行维护的应用程序。

　　如果关系型数据库仅用于支持分析服务多维数据集，就不必定义 UNION ALL 视图。这种情况下，该应用程序就不会受 256 个表的限制，但是建议您不要以这种无法定义 UNION ALL 视图的方式来对关系型数据仓库进行分区。

　　在分区管理已能够自动进行并通过测试之前，分区的数据仓库不应该正式投入使用。分区管理系统是一种简单的应用程序，该系统的一般要求在下面讨论。

　　下面的讨论假设分区是按日期进行的。

　　稳定的分区管理系统应由元数据驱动。只要确保能够编程访问元数据，就可以把元数据存储在任何位置。大多数数据仓库系统使用在数据仓库 SQL Server 或 Microsoft SQL Server Meta Data Services 上定义的自定义元数据表。

　　不论元数据的存储机制是什么，元数据的内容必须包括每个分区的以下信息：

　　创建分区的日期

　　分区中数据的日期范围

　　分区开始联机的日期（加入 UNION ALL 视图）

　　分区不再联机的日期（从视图中丢弃）

　　丢弃分区的日期

　　作为数据仓库整个管理系统的一部分的其他元数据表，应该跟踪何时以及有多少数据被加载到每个分区。

　　分区管理系统的首要任务是创建新分区。应该安排周期性运行的任务，来创建用作下一个分区的新表。

　　执行该任务有许多有效的方式。建议的方法为使用 SQL-DMO（分布式管理对象）来创建与现有分区具有相同结构和索引的新表，但新表具有新的表名、索引名、分区键约束定义、文件组等等：

　　修改表和索引的 Name 属性，检查约束 Text 属性和其他属性；

　　使用 ADD 方法对表进行实例化。

　　使用智能命名约定，用几行代码即可完成这项任务。

　　如本文后面将要讨论的那样，您的应用程序可以将分析服务分区用于数据仓库系统的多维数据集。如果这样，在 RDBMS 中创建分区的脚本和程序可以使用决策支持对象 (DSO) 继续创建相应的多维数据集分区。

　　前面提到过，数据可以被加载入 UNION ALL 视图。理论上，这是表分区结构的一大功能，但在实践中不推荐将其用于数据仓库应用程序。不能将数据批量加载到 UNION ALL 视图；对于大到必须对表进行分区的数据仓库来说，加载进程将会太慢。

　　相反，数据仓库应用程序的设计必须使每一个周期都可以把数据快速加载到相应的目标表。如果数据分阶段应用程序在 SQL Server 数据转换服务 (DTS) 中实现，动态属性任务可以很容易地更改数据泵任务或批量插入任务的目标表的名称。

　　只要新分区没有加入 UNION ALL 视图，就不需要在系统停机时间加载数据。

　　数据仓库分阶段应用程序应该设计为可以处理不属于当前分区的新数据。如果数据仓库加载进程不是在一个夜晚完成，就可能发生这种特殊情况。其他系统要处理不断到来的旧数据。系统的设计必须考虑到这些例外情况的可能性、频率和数据量。

　　如果旧数据以足够低的量到达，最简单的设计就是使用可更新的 UNION ALL 视图来加载所有不属于当前分区的数据。

　　一旦渐变加载成功完成，就必须重新修订 UNION ALL 视图。仍然建议使用 SQL-DMO 完成本任务：使用 ALTER 方法更改 VIEW 对象的 TEXT 属性。从上面所述的元数据表中导出视图定义中要包括的分区列表是最佳途径。

　　表面上看来，将若干分区合并至单个较大分区似乎是多余的。不过，对于日加载量巨大同时加载窗口很小的数据仓库，通过下列措施可以显著改善加载性能：

　　批量加载到空的日分区。

　　创建所有的非簇索引。

　　通过重新创建 UNION ALL 视图，使新分区保持联机。

　　通过自日分区插入、重新创建索引和重新生成 UNION ALL 视图，每周创建和填充新的周分区。然后就可以丢弃日分区。

　　数据变得陈旧后就移动至周甚至月分区，这样更多的分区可以联机保留在 UNION ALL 视图中。 在 SQL Server 2000 分析服务中使用分区

　　SQL Server Enterprise Edition 中的分析服务显式支持分区多维数据集，这种分区多维数据集与关系型数据库中的分区表相当。对于中等或大型的多维数据集，分区可以大大改善查询性能、加载性能，并使多维数据集维护更容易。分区可以按一个或多个矢量来设计，但多维数据集通常仅按日期矢量分区。分区多维数据集的渐变加载（包括创建新分区）应该由自定义应用程序执行。

　　注意：分区可以在本地存储或分布在多个物理服务器上。尽管跨多个服务器的分布式分区对非常大的系统可能有好处，但我们的测试表明，多维数据集尺寸达数万亿字节时，分布式分区解决方案才能提供最大的好处。本文仅考虑本地分区多维数据集。分区多维数据集的渐变加载（包括创建新分区）应该由自定义应用程序执行。

　　对多维数据集进行分区将极大地改善查询性能。甚至中等大小的多维数据集（基于 100 GB 来自关系型数据库的数据）也会从分区中受益。多维数据集分区的优势在多用户加载的情况下更显著。

　　每个应用程序查询性能的改善随多维数据集结构、使用方式和分区设计的不同而变化。如果仅需要按月分区多维数据集中一个月的数据，那么查询就只访问一个分区。一般情况下，放弃单个分区中的大多维数据集，转而采用精心设计的本地分区策略，我们预计查询性能可以平均改善 100% 至 1000%。

　　对于关系型数据仓库，分析服务系统管理员可能选择仅在多维数据集中保留最新的数据。如果是单个分区，清除旧数据的唯一方法就是重新处理多维数据集。通过按日期矢量分区，管理员可以不关闭系统就丢弃旧分区。

　　从管理的观点来看，分区是在不影响其他分区的情况下可以被独立添加和丢弃的数据单位。这有助于在系统中管理数据的生命周期。每个多维数据集分区单独存储在一组文件中。由于分区文件相对较小，备份和还原这些数据文件的操作更容易管理。这对大小在 2 GB 以下的分区文件尤为明显。这种情况下，存档和还原实用程序也会有效。如果多维数据集的一部分损坏，或发现这部分包含不正确或不一致的数据，那么可以仅重新处理该分区，这比处理整个多维数据集更为迅速。另外，为节省空间，也可能更改以及合并旧分区的存储模式和设计。

　　不同的分区可以使用不同的数据源。单个多维数据集可以组合多个关系型数据库的数据。例如，建立企业数据仓库时，可以使来自欧洲和北美的数据驻留在不同的服务器上。如果多维数据集按地理分区，在逻辑上多维数据集可以合并这些完全隔离的数据源。单个多维数据集定义的多个源服务器上的关系架构必须几乎完全相同，这样才能正常工作。

　　可以并行加载多个分区，因此分区多维数据集的加载速度可以比未分区多维数据集更快。后面我们将讨论，要并行处理分区，您必须获取第三方工具或创建一个简单的自定义工具。在多处理器计算机上，性能改善很明显。并行处理工具应该可以将 CPU 利用率提高到 90%。通常情况下，每两个处理器同时处理一到两个分区可获得这样高的性能。例如，在一个所有的处理器都用来处理多维数据集的四处理器的计算机上，您可能希望同时处理两到四个分区。如果试图处理的分区的个数多于您拥有的处理器的个数，性能将显著降低。每两个处理器处理一个分区是比较保守的；理想的数字取决于来自源数据库的数据流的速度、聚合设计、存储和其他一些因素。