深入浅出分区表与分区索引之四：案例讲解（上）

如果朋友们已经阅读了与分区有关的概念、优点和代码示例，则可能已对此过程有了一个很好的理解；但是，对于每个步骤，都可以使用特定的设置和选项，而且在某些情况下，还必须满足各种条件。本节将帮助大家将这些内容融会贯通起来进行理解。 

范围分区：销售数据 

销售数据的使用方式经常发生变化。当前月份的数据是事务数据，而上一个月份的数据主要用于进行分析。分析通常针对月份、季度和/或年度范围的数据进行。因为不同的分析人员可能希望同时查看大量不断变化的数据，所以通过分区可以更好地隔离此活动。在此方案中，活动数据来自 283 个分支位置，而且是通过两个标准格式的 ASCII 文件传输的。在每个月第一天的上午 3 点之前，所有文件均被放置到一台中央文件服务器上。所有文件按大小进行排列，但每月平均约有 86,000 份销售（订单）。

每个订单平均包含 2.63 个明细项，因此，OrderDetails 文件平均包含 226,180 行。每月增加约 2,500 万个新的 Orders 和 6,400 万个 OrderDetails 行，而历史分析服务器要使两年的数据都处于活动状态以便进行分析。两年的数据刚好低于 6 亿个 Orders 和超过 15 亿个 OrderDetails 行。因为分析通常是在同一季度的不同月份之间进行比较，或与上一年度的相同月份进行比较，所以可以使用范围分区。每个范围的边界都是按月份确定的。

按照之前文章描述的步骤，使用基于 OrderDate 的范围分区对表进行分区。了解这台新服务器的要求后，分析人员打算收集和分析连续六个月的数据，或当前年度与上一年度三个月份（例如 2003 年 1 月到 3 月与 2004 年 1 月到 3 月）的数据。要使磁盘分区最大化并隔离大多数数据组，多个文件组将使用相同的物理磁盘，但是这些文件组将相差六个月以减少磁盘争用。

当前数据是 2004 年 10 月，而所有 283 个存储位置都在本地管理其当前销售。服务器上存储了从 2002 年 10 月到 2004 年 9 月的数据。为了利用新的 16 向多处理器计算机和存储区域网络，每个月的文件存储在一个文件组中，同时位于一个分区镜像 (RAID 1+0) 磁盘集上。对于数据通过文件组在逻辑驱动器上的物理布局，下图描述了每月数据的位置。

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

结果是，如图中的两个逻辑驱动器 [驱动器 E:\ 和 F:\]，相同月份的 Orders 和 OrderDetails 的数据都存储在相同的磁盘上：

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

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

每个文件组的语法都与下面的语法相同，但必须创建所有 24 个文件组。有关创建所有 24 个文件组的完整脚本，请参见 RangeCaseStudyFilegroups.sql 脚本。 

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

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

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

复制代码

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

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

复制代码

 

 

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

1. CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
   
2. AS
   
3. PARTITION TwoYearDateRangePFN TO 
   
4. ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], 
   
5. [FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
   
6. [FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
   
7. [FG19],[FG20],[FG21],[FG22],[FG23],[FG24], 
   
8. [PRIMARY] )
   
9. GO
   

复制代码

通过使用默认的文件组或用户定义的文件组作为未分区的表，或者使用架构创建分区表，可以使用与以前的版本支持的相同语法创建表。哪种方法更好取决于表的填充方式和创建的分区数。从性能角度看，先填充堆再建立群集索引可能要胜过在已经建立索引的表中加载数据。

另外，如果有多个 CPU，您可以通过并行 BULK INSERT 语句将数据加载到表中，然后也以并行方式建立索引。对于 Orders 表，按照正常的方式创建表，然后通过 INSERT SELECT 语句（从 AdventureWorks 示例数据库中提取数据）加载现有的数据。要将 Orders 表建为分区表，请在该表的 ON 子句中指定分区架构。Orders 表是使用以下语法创建的：

1. CREATE TABLE SalesDB.[dbo].[Orders] 
   
2. (
   
3. [PurchaseOrderID] [int] NOT NULL,
   
4. [EmployeeID] [int] NULL,
   
5. [VendorID] [int] NULL,
   
6. [TaxAmt] [money] NULL,
   
7. [Freight] [money] NULL,
   
8. [SubTotal] [money] NULL,
   
9. [Status] [tinyint] NOT NULL,
   
10. [RevisionNumber] [tinyint] NULL,
    
11. [ModifiedDate] [datetime] NULL,
    
12. [ShipMethodID] tinyint NULL,
    
13. [ShipDate] [datetime] NOT NULL, 
    
14. [OrderDate] [datetime] NULL
    
15. CONSTRAINT OrdersRangeYear
    
16. CHECK ([OrderDate] >= '20021001' 
    
17. AND [OrderDate] < '20041001'), 
    
18. [TotalDue] [money] NULL
    
19. ) ON TwoYearDateRangePScheme(OrderDate)
    
20. GO
    

复制代码

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

1. CREATE TABLE [dbo].[OrderDetails](
   
2. [OrderID] [int] NOT NULL,
   
3. [LineNumber] [smallint] NOT NULL,
   
4. [ProductID] [int] NULL,
   
5. [UnitPrice] [money] NULL,
   
6. [OrderQty] [smallint] NULL,
   
7. [ReceivedQty] [float] NULL,
   
8. [RejectedQty] [float] NULL,
   
9. [OrderDate] [datetime] NOT NULL
   
10. CONSTRAINT OrderDetailsRangeYearCK
    
11. CHECK ([OrderDate] >= '20021001' 
    
12. AND [OrderDate] < '20041001'), 
    
13. [DueDate] [datetime] NULL,
    
14. [ModifiedDate] [datetime] NOT NULL 
    
15. CONSTRAINT [OrderDetailsModifiedDateDFLT] 
    
16. DEFAULT (getdate()),
    
17. [LineTotal] AS (([UnitPrice]*[OrderQty])),
    
18. [StockedQty] AS (([ReceivedQty]-[RejectedQty]))
    
19. ) ON TwoYearDateRangePScheme(OrderDate)
    
20. GO
    

复制代码

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

1. INSERT dbo.[Orders]
   
2. SELECT o.[PurchaseOrderID] 
   
3. , o.[EmployeeID]
   
4. , o.[VendorID]
   
5. , o.[TaxAmt]
   
6. , o.[Freight] 
   
7. , o.[SubTotal] 
   
8. , o.[Status] 
   
9. , o.[RevisionNumber] 
   
10. , o.[ModifiedDate] 
    
11. , o.[ShipMethodID] 
    
12. , o.[ShipDate] 
    
13. , o.[OrderDate] 
    
14. , o.[TotalDue] 
    
15. FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
    
16. WHERE ([OrderDate] >= '20021001'
    
17. AND [OrderDate] < '20041001')
    
18. GO
    
19. INSERT dbo.[OrderDetails]
    
20. SELECT od.PurchaseOrderID
    
21. , od.LineNumber
    
22. , od.ProductID
    
23. , od.UnitPrice
    
24. , od.OrderQty
    
25. , od.ReceivedQty
    
26. , od.RejectedQty
    
27. , o.OrderDate
    
28. , od.DueDate
    
29. , od.ModifiedDate
    
30. FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
    
31. JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
    
32. ON o.PurchaseOrderID = od.PurchaseOrderID
    
33. WHERE (o.[OrderDate] >= '20021001' 
    
34. AND o.[OrderDate] < '20041001')
    
35. GO
    

复制代码

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

1. SELECT $partition.TwoYearDateRangePFN(o.OrderDate) 
   
2. AS [Partition Number]
   
3. , min(o.OrderDate) AS [Min Order Date]
   
4. , max(o.OrderDate) AS [Max Order Date]
   
5. , count(*) AS [Rows In Partition]
   
6. FROM dbo.Orders AS o
   
7. GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
   
8. ORDER BY [Partition Number]
   
9. GO
   
10. SELECT $partition.TwoYearDateRangePFN(od.OrderDate) 
    
11. AS [Partition Number]
    
12. , min(od.OrderDate) AS [Min Order Date]
    
13. , max(od.OrderDate) AS [Max Order Date]
    
14. , count(*) AS [Rows In Partition]
    
15. FROM dbo.OrderDetails AS od
    
16. GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
    
17. ORDER BY [Partition Number]
    
18. GO
    

复制代码

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

1. ALTER TABLE Orders
   
2. ADD CONSTRAINT OrdersPK
   
3. PRIMARY KEY CLUSTERED (OrderDate, OrderID)
   
4. GO
   
5. ALTER TABLE dbo.OrderDetails
   
6. ADD CONSTRAINT OrderDetailsPK
   
7. PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
   
8. GO
   

复制代码

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

1. ALTER TABLE Orders
   
2. ADD CONSTRAINT OrdersPK
   
3. PRIMARY KEY CLUSTERED (OrderDate, OrderID)
   
4. ON TwoYearDateRangePScheme(OrderDate)
   
5. GO
   
6. ALTER TABLE dbo.OrderDetails
   
7. ADD CONSTRAINT OrderDetailsPK
   
8. PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
   
9. ON TwoYearDateRangePScheme(OrderDate)
   
10. GO
    

复制代码