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

早妖

　　摘要：本文介绍如何使用分区来改善 SQL Server 2000 Enterprise Edition 中数据仓库的可管理性、查询性能和加载速度，并讨论关系型数据库和分析服务多维数据集中的矢量架构的水平分区。 　　概述 　　本文讨论数据仓库中数据分区的作用。关系型数据仓库和分析服务多维数据集都支持数据分区。分区的逻辑概念在 Microsoft® SQL Server™ 的两个引擎中是相同的：通过键（例如日期）对数据进行水平分区。在关系型数据库中，分区是通过创建单独的物理表（例如为每个月的数据创建一个表）并且定义一个成员表的联合视图来实现的。与此类似，SQL Server Enterprise Edition 中的分析服务支持显式的多维数据集分区。在关系型数据库和联机分析处理 (OLAP) 引擎中，物理存储的复杂性对于分析用户是不可见的。 　　 　　数据仓库分区的优点： 　　 　　大大缩短查询时间。 　　 　　 　　减少加载时间，改善数据库的可维护性。 　　 　　 　　解决从活动数据库中删除旧数据时出现的数据修剪问题。 　　该技术需要创建比非分区系统更复杂的数据分阶段应用程序。本文介绍设计、实现和维护水平分区数据仓库的最佳方法。 　　 　　因为有效的分区计划可以极大地改善查询性能，所以我们极力建议您对大型分析服务系统进行分区。尽管对于某些特定的数据仓库维护问题，对关系型数据仓库进行分区是有效的解决方案，但通常不推荐您这样做。 　　 　　在 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 dat <