SQL Server 2005 中的分区表和索引

图 12：分区表的顺序

12 个逻辑驱动器都位于 RAID 1+0 配置中，因此 Orders 和 OrderDetails 数据所需的总磁盘数为 48 个。存储区域网络支持 78 个磁盘，而另外 30 个用于事务日志、TempDB、系统数据库和其他更小的表，例如 Customers（900 万）和 Products（386,750 行）。Orders 和 OrderDetails 表都使用相同的边界条件、磁盘位置和分区架构。结果是（只看图 13 中的两个逻辑驱动器 [驱动器 E:\ 和 F:\]），相同月份的 Orders 和 OrderDetails 的数据都存储在相同的磁盘上：

图 13：磁盘阵列上盘区位置的范围分区

虽然看起来很复杂，但创建过程非常简单。设计分区表最难的部分在于从大量数据源传输数据，即 283 个存储位置都必须使用一种标准的传输机制。但是，中央服务器上只定义了一个 Orders 表和一个 OrderDetails 表。要将两个表都创建为分区表，请先创建分区函数和分区架构。分区架构定义分区在磁盘上的物理位置，因此必须存在文件组。在此表中，文件组是必需的，因此下一步是创建文件组。每个文件组的语法都与下面的语法相同，但必须创建所有 24 个文件组。有关创建所有 24 个文件组的完整脚本，请参见 RangeCaseStudyFilegroups.sql 脚本。

注意：如果没有指定相应的驱动器号，将无法运行此脚本；但是此脚本包含一个“setup”表，可以修改此表以简化测试。您可以将驱动器号/位置更改为一个驱动器，以测试和学习语法。同时，确保将文件大小调整为 MB 而不是 GB，并根据可用的磁盘空间考虑指定一个较小的初始大小。

将为 SalesDB 数据库创建 24 个文件和文件组。每个文件和文件组都具有相同的语法，只是位置、文件名和文件组名不相同：

ALTER DATABASE SalesDB
ADD FILE
(NAME = N'SalesDBFG1File1',
FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf',
SIZE = 20GB,
MAXSIZE = 35GB,
FILEGROWTH = 5GB)
TO FILEGROUP [FG1]
GO

创建所有 24 个文件和文件组后，即可定义分区函数和分区架构。要验证文件和文件组，请分别使用 sp_helpfile 和 sp_helpfilegroup。

分区函数将在 OrderDate 列中进行定义。使用的数据类型为 datetime，而且两个表都需要存储 OrderDate 才能根据此值对两个表进行分区。实际上，如果根据相同的键值对两个表进行分区，则分区键值属于重复信息，但它对于获得对齐优点又是必需的。而且，在大多数情况下，应该是一个相当窄的列（datetime 数据类型为 8 个字节）。如本文前面的“为范围分区创建分区函数”部分所述，此函数将是一个范围分区函数，其中的第一个边界条件位于 LEFT（第一个）分区中。

CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20021031 23:59:59.997',      -- 2002 年 10 月
'20021130 23:59:59.997',   -- 2002 年 11 月
'20021231 23:59:59.997',   -- 2002 年 12 月
'20030131 23:59:59.997',   -- 2003 年 1 月
'20030228 23:59:59.997',   -- 2003 年 2 月
'20030331 23:59:59.997',   -- 2003 年 3 月
'20030430 23:59:59.997',   -- 2003 年 4 月
'20030531 23:59:59.997',   -- 2003 年 5 月
'20030630 23:59:59.997',   -- 2003 年 6 月
'20030731 23:59:59.997',   -- 2003 年 7 月
'20030831 23:59:59.997',   -- 2003 年 8 月
'20030930 23:59:59.997',   -- 2003 年 9 月
'20031031 23:59:59.997',   -- 2003 年 10 月
'20031130 23:59:59.997',   -- 2003 年 11 月
'20031231 23:59:59.997',   -- 2003 年 12 月
'20040131 23:59:59.997',   -- 2004 年 1 月
'20040229 23:59:59.997',   -- 2004 年 2 月
'20040331 23:59:59.997',   -- 2004 年 3 月
'20040430 23:59:59.997',   -- 2004 年 4 月
'20040531 23:59:59.997',   -- 2004 年 5 月
'20040630 23:59:59.997',   -- 2004 年 6 月
'20040731 23:59:59.997',   -- 2004 年 7 月
'20040831 23:59:59.997',   -- 2004 年 8 月
'20040930 23:59:59.997')   -- 2004 年 9 月
GO

因为包含了最左侧和最右侧的边界情况，所以此分区函数将创建 25 个分区。该表将保留第 25 个分区为空白。不需要为这个空分区指定特殊的文件组（因为其中永远不会包含数据）作为限制表数据的约束。要将数据定向至相应的磁盘，可以使用分区架构将分区映射到文件组。分区架构将为 24 个将要包含数据的文件组使用明确的文件组名，而为第 25 个空分区使用 PRIMARY 文件组。

CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
AS
PARTITION TwoYearDateRangePFN TO
( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6],
[FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
[FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
[FG19],[FG20],[FG21],[FG22],[FG23],[FG24],
[PRIMARY] )
GO

通过使用默认的文件组或用户定义的文件组作为未分区的表，或者使用架构创建分区表，可以使用与以前的版本支持的相同语法创建表。哪种方法更好取决于表的填充方式和创建的分区数。从性能角度看，先填充堆再建立群集索引可能要胜过在已经建立索引的表中加载数据。另外，如果有多个 CPU，您可以通过并行 BULK INSERT 语句将数据加载到表中，然后也以并行方式建立索引。对于 Orders 表，按照正常的方式创建表，然后通过 INSERT SELECT 语句（从 AdventureWorks 示例数据库中提取数据）加载现有的数据。要将 Orders 表建为分区表，请在该表的 ON 子句中指定分区架构。Orders 表是使用以下语法创建的：

CREATE TABLE SalesDB.[dbo].[Orders]
(
[PurchaseOrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID]   tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[TotalDue] [money] NULL
) ON TwoYearDateRangePScheme(OrderDate)
GO

因为 OrderDetails 表也将使用此架构，而且必须包含 OrderDate，所以使用以下语法创建 OrderDetails 表：

CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[LineNumber] [smallint] NOT NULL,
[ProductID] [int] NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL,
[RejectedQty] [float] NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrderDetailsRangeYearCK
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[DueDate] [datetime] NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [OrderDetailsModifiedDateDFLT]
DEFAULT (getdate()),
[LineTotal]  AS (([UnitPrice]*[OrderQty])),
[StockedQty]  AS (([ReceivedQty]-[RejectedQty]))
) ON TwoYearDateRangePScheme(OrderDate)
GO

加载数据的下一步是通过两个 INSERT 语句处理的。这两个语句使用新的 AdventureWorks 数据库（从中复制数据）。请安装 AdventureWorks 示例数据库以复制此数据：

INSERT dbo.[Orders]
SELECT o.[PurchaseOrderID]
, o.[EmployeeID]
, o.[VendorID]
, o.[TaxAmt]
, o.[Freight]
, o.[SubTotal]
, o.[Status]
, o.[RevisionNumber]
, o.[ModifiedDate]
, o.[ShipMethodID]
, o.[ShipDate]
, o.[OrderDate]
, o.[TotalDue]
FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
WHERE ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001')
GO
INSERT dbo.[OrderDetails]
SELECT    od.PurchaseOrderID
, od.LineNumber
, od.ProductID
, od.UnitPrice
, od.OrderQty
, od.ReceivedQty
, od.RejectedQty
, o.OrderDate
, od.DueDate
, od.ModifiedDate
FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
ON o.PurchaseOrderID = od.PurchaseOrderID
WHERE (o.[OrderDate] >= '20021001'
AND o.[OrderDate] < '20041001')
GO

现在，数据已加载到分区表中，您可以使用新的内置系统函数来确定数据所在的分区。下面的查询很有用，因为它将返回包含数据的每个分区的以下信息：每个分区内存在的行数以及最小和最大 OrderDate。此查询不会返回不包含行的分区。

SELECT $partition.TwoYearDateRangePFN(o.OrderDate)
AS [Partition Number]
, min(o.OrderDate) AS [Min Order Date]
, max(o.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
SELECT $partition.TwoYearDateRangePFN(od.OrderDate)
AS [Partition Number]
, min(od.OrderDate) AS [Min Order Date]
, max(od.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.OrderDetails AS od
GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
ORDER BY [Partition Number]
GO

最后，在填充表后，可以建立群集索引。在本例中，群集索引将根据主键进行定义，因为分区键标识两个表（对于 OrderDetails，在索引中添加 LineNumber 以确保唯一性）。为分区表建立索引的默认行为是将索引与同一架构中的分区表对齐，而该架构是不需要指定的。

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO

指定分区架构的完整语法如下：

ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON TwoYearDateRangePScheme(OrderDate)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
ON TwoYearDateRangePScheme(OrderDate)
GO

连接分区表

连接对齐的表时，SQL Server 2005 提供了通过一个或多个步骤连接表的选项，通过此选项，可以先连接各个分区，然后将子集加起来。不管如何连接分区，SQL Server 都会评估是否可以实现某种程度的分区消除。

分区消除

在下面的查询中，数据是从上一个方案中创建的 Orders 和 OrderDetails 表中查询的。该查询将只返回第三个季度的信息。通常，第三个季度包含订单处理较慢的月份，但在 2004 年，这些月份是订单最多的一些月份。在本例中，我们关心的是第三季度的 Products 趋势（订购的数量及其订单日期）。为了确保连接对齐的分区表时能够受益于分区消除，必须指定每个表的分区范围。在本例中，因为 Orders 表的主键是 OrderDate 和 OrderID 的组合键，这些表之间的连接显示表之间的 OrderDate 必须相等。SARG（搜索参数）将应用于两个分区表。检索此数据的查询如下：

SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty
FROM dbo.Orders AS o
INNER JOIN dbo.OrderDetails AS od
ON o.OrderID = od.OrderID
AND o.OrderDate = od.OrderDate
WHERE o.OrderDate >= '20040701'
AND o.OrderDate <= '20040930 11:59:59.997'
GO

如图 14 所示，查看实际或预测的示例输出时，要查看一些关键元素：首先（使用 SQL Server Management Studio），将光标悬停在所访问的表上时，您会看到“Estimated Number of Executions”或“Number of Executions”。在本例中，可以看到一个季度或三个月的数据。每个月都有自己的分区，而且查看此数据时可以看到执行了三次：每个表一次。