站长论坛

标题: 在SQL Server 2000数据仓库中使用分区 [打印本页]

---

作者: tzlink    时间: 2008-12-24 17:16
标题: 在SQL Server 2000数据仓库中使用分区
分区视图联接来自一组成员的水平分区数据，使数据看起来象来自同一张表。SQL Server 2000 区分本地分区视图和分布式分区视图。在本地分区视图中，所有相关表和视图驻留在 SQL Server 的同一实例上。在分布式分区视图中，相关表中至少有一张表驻留在其他某个(远程)服务器上。建议您不要将分布式分区视图用于数据仓库应用程序。

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

分区的优点

数据修剪：

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

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

DELETE FROM fact_table

WHERE date_key < 19990101

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

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

加载速度：

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

可维护性：

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

查询速度：

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

分区的缺点

复杂性:

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

查询设计约束:

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

设计时要考虑的因素:

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

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

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

分区设计概述

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

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

语法示例:

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

创建 1999 年事实表:

CREATE TABLE [dbo].[sales_fact_19990101] (

[date_key] [int] NOT NULL

CHECK ([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 NULL

CHECK ([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]

AS

SELECT * FROM [dbo].[sales_fact_19990101]

UNION ALL

SELECT * 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

---

作者: tzlink    时间: 2008-12-24 17:16
标题: 在SQL Server 2000数据仓库中使用分区
您应该看到查询计划中仅包括表 1999。将该查询计划与主键已删除的相同表生成的查询计划相比较，我们会发现：表 2000 仍然被排除。将这些计划与在已删除 date_key 约束的架构上生成的查询计划进行对比。这些约束被删除的情况下，表 1999 和表 2000 都被包括在查询中。

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

将条件直接应用于事实表:

要获得最佳的查询性能，所有的查询都应将条件直接放在事实表中的筛选键上。将约束放在第二张表(例如日期矢量表)的查询将包括所有分区。对 UNION ALL 事实表的标准星号联合查询工作良好,将条件放在任意未分区的矢量表的属性上，以标准方式创建星号查询 WHERE 子句,包括分区矢量(日期)的属性。

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

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

分区键的选择

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

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

使多少数据保持联机状态?这项决策的主要依据是业务要求，同时要考虑保持大量数据联机的费效比。

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

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

定义分区的范围

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

date_key < 19990101

date_key BETWEEN 1990101 AND 19990131

date_key BETWEEN 19990201 AND 19990229

...

date_key BETWEEN 19991201 AND 19991231

date_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 上定义的自定义元数据表。

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

分区名称:

1、创建分区的日期;

2、分区中数据的日期范围;

3、分区开始联机的日期(加入 UNION ALL 视图);

4、分区不再联机的日期(从视图中丢弃);

5、丢弃分区的日期。

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

创建新分区

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

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

1、获取模板表定义(通常为最新的分区);

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

使用 ADD 方法对表进行实例化，使用智能命名约定，用几行代码即可完成这项任务。

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

填充分区

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

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

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

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

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

定义UNION ALL视图

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

合并分区

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

1、用要加载的数据创建文本文件，按簇索引的顺序排序;

2、批量加载到空的日分区;

3、创建所有的非簇索引。

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

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

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

---

欢迎光临 站长论坛 (http://tzlink.com/bbs/)

Powered by Discuz! X3.2