sql server中filegroup与partition解析

0.参考文献：

SQL SERVER 分区表的总结

SQL Server 2005 分区表实践——建立分区表（partition table）

SQL Server中数据库文件的存放方式，文件和文件组 (from CareySon)

T-SQL查询进阶--理解SQL SERVER中的分区表 (from CareySon)

1.基础知识

一直对于表分区和filegroup的概念不是很清晰，今天通过具体的实例来学习什么事filegroup和partition，以及他们的作用。

1.1通过文件组来管理文件的特性

对于用户角度来说，需对创建的对象指定存储的文件组只有三种数据对象：表,索引和大对象（LOB）

使用文件组可以隔离用户和文件，使得用户针对文件组来建立表和索引，而不是实际磁盘中的文件。也就是可以指定将表和索引存储在不同的文件上面。

使用文件组来管理文件可以使得同一文件组内的不同文件分布在不同的硬盘中，极大的提高了IO性能.

SQL SERVER会根据每个文件设置的初始大小和增长量会自动分配新加入的空间，假设在同一文件组中的文件A设置的大小为文件B的两倍，新增一个数据占用三页(Page),则按比例将2页分配到文件A中，1页分配到文件B中.

1.2文件的分类

首要文件:这个文件是必须有的，而且只能有一个。这个文件额外存放了其他文件的位置等信息.扩展名为.mdf
次要文件:可以建任意多个，用于不同目的存放.扩展名为.ndf，用于存放数据，而不是日志。
日志文件：存放日志,扩展名为.ldf

在SQL SERVER 2008之后，还新增了文件流数据文件和全文索引文件.

我们可以通过sys.database_files这个视图查看数据库中的文件情况:

select * from sys.database_files

1.3创建filegroup，并将索引创建在指定的filegroup中

可以通过TSQL语句来创建文件组，也可以通过SSMS来创建文件组，这个在后面会提到。这里不再重复。下面我们重点来介绍如何将索引创建在指定的filegroup中，而不跟数据放在一起。首先来看我创建好的filegroup，已经这些filegroup所对应的files，如下图所示：

然后我们通过如下TSQL语句来测试

View Code

 use TESTDB
 --step1.插入数据
 select * into OrderDetail from AdventureWorks2008R2.Sales.SalesOrderDetail

 --step2:查看表的索引信息，发现所有页都在pagefid=1上面，并且indexid都为0.因为没有创建聚集索引之前是堆表
 dbcc ind ( TESTDB, [dbo.OrderDetail], -1)

--step3:在分区上创建聚集索引，聚集索引不要放在IndexStorage这个filegroup当中，因为聚集索引就是数据本身。
--如果将聚集索引on IndexStorage的话，那么所有数据都将会在IndexStorage这个filegroup所对应的文件上
create clustered index idx_c_SSalesOrderDetailID on  OrderDetail(SalesOrderDetailID) 

--step4:此时发现原先indexid=0的都变成了index=1
dbcc ind ( TESTDB, [dbo.OrderDetail], -1)

--step5:在IndexStorage这个file group上面创建非聚集索引
CREATE NONCLUSTERED INDEX idx_nc_SalesOrderID ON dbo.OrderDetail(SalesOrderID) on IndexStorage
CREATE NONCLUSTERED INDEX idx_nc_CarrierTrackingNumber ON dbo.OrderDetail(CarrierTrackingNumber) on IndexStorage
CREATE NONCLUSTERED INDEX idx_nc_UnitPrice ON dbo.OrderDetail(UnitPrice) on IndexStorage

--step6:再次查看页信息我们发现只有indexid=1的pagefid=1，也就是说聚集索引都在TESTDB.MDF这个文件上，
--而indexid=2,3,4所对应的pagefid=3，表明已经将索引建立到IndexStorage这个filegroup上面去了，对应的是IndexStorage.ndf这个文件。
dbcc ind ( TESTDB, [dbo.OrderDetail], -1)

--step7:创建复合索引，
CREATE NONCLUSTERED INDEX idx_nc_com ON dbo.OrderDetail(SalesOrderID,CarrierTrackingNumber,UnitPrice)

--step8:默认情况下会使用Primary这个filegroup，filefid=1.
dbcc ind ( TESTDB, [dbo.OrderDetail], -1)

总结：

在分区上创建聚集索引，聚集索引不要放在IndexStorage这个filegroup当中，因为聚集索引就是数据本身。如果将聚集索引on IndexStorage的话，那么所有数据都将会在IndexStorage这个filegroup所对应的文件上。
在创建非聚集索引的时候，通过在创建索引语句的最后加上 on [filegroup]指定需要将这个索引放在哪一个filegroup当中，如果不加的话会使用默认filegroup，我们这里的默认filegroup是priamry。

1.4使用多个文件的好处

使用多个文件分布数据到多个硬盘中可以极大的提高IO性能.放在一个磁盘中基本没有效果。

场景描述

应用程序发来大量的并发语句在修改同一张表格里的记录，而表格架构设计以及用户业务逻辑使得这些修改都集中在同一个页面，或者数量不多的几个页面上。这些页面有的时候也被称为Hot Page。这样的瓶颈通常只会发生在并发用户比较多的、典型的OLTP系统上。这种瓶颈是无法通过提高硬件配置解决的，只有通过修改表格设计或者业务逻辑，让修改分散到尽可能多的页面上，才能提高并发性能。

在现实环境里，可以试想下面的情形。一个股票交易系统，每一笔交易都会有一个流水号，是递增且不可重复的。而客户发过来的交易请求，都要存储在同一张交易表里。每一个新的交易，都要插入一条新记录。如果设计者选择在流水号上建聚集索引（这也是很自然的），就容易遇到Hot Page的PAGELATCH资源瓶颈。在同一时间，只能有一个用户插入一笔交易。

怎样才能解决或者缓解这种瓶颈呢？

最简单的方法，是换一个数据列建聚集索引，而不要建在Identity的字段上。这样表格里的数据就按照其他方式排序，同一时间的插入就有机会分散在不同的页面上。
如果实在是一定要在Identity的字段上建聚集索引，建议根据其他某个数据列在表格上建立若干个分区（Partition）。把一个表格分成若干个分区，可以使得接受新数据的页面数目增加。

还是以上面那个股票交易系统为例子。不同的股票属于不同的行业。开发者可以根据股票的行业属性，将一张交易表分成若干个分区。在SQL Server里，已分区表（Partitioned Table）的每个分区都是一个独立的存储单位。分属不同分区的数据行是严格分开存储的。所以同一个时间发生的交易记录，因其行业不同，也会被分别保存在不同的分区里。这样，在同一个时间点，可以插入不同行业的交易记录。每个分区上的Hot Page（接受新数据插入的page）就不那么hot了。

在我的事例中，是有一张SalesOrderDetail表，其数据量很大，我希望按照UnitPrice这个字段进行分区。下面来看具体步骤。

step1：创建filegroup

在sql server中好像没有create filegroup的说法，只是在现成的数据库中添加filegroup而已。下面的代码中首先创建数据库，然后添加四个filegroup，tsql代码如下所示：

View Code

--step1------
--创建数据库
create database TEST
USE MASTER
GO
--40万行分成5个文件组，PRIMARY加下面四个文件组，
--命名规则:FG_数据库名_表名_字段名_流水号
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_1;
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_2;
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_3;
ALTER DATABASE TEST ADD FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_4;
GO

执行完以后我们可以在TEST数据库的properties中看到我们添加的四个filegroup，如下图所示：

step2：为filegroup添加数据文件

在创建完filegroup以后，我们为每一个filegroup创建一个次要数据文件，因为每一个数据库只能有一个primary datafile，也就是mdf文件，但是可以有多个次要数据文件，也就是.ndf文件。为filegroup创建ndf数据文件的TSQL语句如下图所示:

View Code

--step2------------------
USE TEST
GO
--给每个文件组加个次数据文件，也就是.ndf文件
--文件命名规则:文件组名_data_流水号
ALTER DATABASE TEST
ADD FILE (
          NAME=N'FG_TEST_SalesOrderDetail_UnitPrice_1_data_1',
          FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG_TEST_SalesOrderDetail_UnitPrice_1_data_1.ndf',
          SIZE=2MB,
          FILEGROWTH=10%
          )
TO FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_1;

ALTER DATABASE TEST
ADD FILE (
          NAME=N'FG_TEST_SalesOrderDetail_UnitPrice_2_data_1',
          FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG_TEST_SalesOrderDetail_UnitPrice_2_data_1.ndf',
          SIZE=2MB,
          FILEGROWTH=10%
          )
TO FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_2;

ALTER DATABASE TEST
ADD FILE (
          NAME=N'FG_TEST_SalesOrderDetail_UnitPrice_3_data_1',
          FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG_TEST_SalesOrderDetail_UnitPrice_3_data_1.ndf',
          SIZE=2MB,
          FILEGROWTH=10%
          )
TO FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_3;

ALTER DATABASE TEST
ADD FILE (
          NAME=N'FG_TEST_SalesOrderDetail_UnitPrice_4_data_1',
          FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG_TEST_SalesOrderDetail_UnitPrice_4_data_1.ndf',
          SIZE=2MB,
          FILEGROWTH=10%
          )
TO FILEGROUP FG_TEST_SalesOrderDetail_UnitPrice_4;
GO

执行完上述语句以后，我们可以查看TEST数据库的file properties，如下图所示：

我们可以看到四个文件的大小都是2MB。

step3：创建分区函数

在当前数据库中创建一个函数，该函数可根据指定列的值将表或索引的各行映射到分区。使用 CREATE PARTITION FUNCTION 是创建已分区表或索引的第一步。在 SQL Server 2012 中，一张表或一个索引最多可以有 15,000 个分区。

创建分区函数的具体如法如下：

View Code

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

在本事例中，需要有5个分区，本实例创建的分区函数如下所示：

View Code

--step3
--创建分区函数
--分区函数命名：fn_Partition_表名_字段
--40000
CREATE PARTITION FUNCTION fn_Partition_SalesOrderDetail_UnitPrice(money)
AS 
RANGE RIGHT 
FOR VALUES(500,1000,1500,2000);
GO

其中RANGE LEFT|RIGHT表示当间隔值由数据库引擎按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧，就是等于号在哪一边）。如果未指定，则默认值为 LEFT。比如在我的分区函数中指定的间隔是(500,1000,1500,2000)，并且是RANGE RIGHT，那么我的范围就是

分区	1	2	3	4	5
值	<500	>=500 and <1000	>=1000 and <1500	>=1500 and <2000	>=2000

step4：建分区架构

创建分区架构的TSQL如下所示：

View Code

--step4
--创建分区架构
--分区架构命名：Sch_表名_字段名
CREATE PARTITION SCHEME Sch_SalesOrderDetail_UnitPrice
AS PARTITION fn_Partition_SalesOrderDetail_UnitPrice
TO 
([PRIMARY],[FG_TEST_SalesOrderDetail_UnitPrice_1],[FG_TEST_SalesOrderDetail_UnitPrice_2],[FG_TEST_SalesOrderDetail_UnitPrice_3],[FG_TEST_SalesOrderDetail_UnitPrice_4]);
GO

从上述TSQL中我们可以发现，在创建分区架构的时候关联了分区函数以及具体的5个filegroup。

注意：这里并不一定必须要求有5个filegroup，我们可以填写相同的filegroup，但是需要填写5次filegroup，因为有5个分区。

step5：创建分区表并填充数据

View Code

--创建SalesOrderDetail表
select * into SalesOrderDetail from AdventureWorks2008R2.Sales.SalesOrderDetail

--修改分区属性
ALTER TABLE SalesOrderDetail
ADD CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID PRIMARY KEY 
(SalesOrderDetailID,UnitPrice)
ON [Sch_SalesOrderDetail_UnitPrice]([UnitPrice])

需要注意的是分区列UnitPrice必须有唯一约束或者是聚集索引。所以在这里我创建聚集索引的时候将UnitPrice列也添加进去了。如果不讲UnitPrice设为聚集索引，也就是让此列唯一，那么在执行上述命令的时候会报如下错误：

Msg 1908, Level 16, State 1, Line 2
Column 'UnitPrice' is partitioning column of the index 'PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID'. Partition columns for a unique index must be a subset of the index key.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

在执行完上面的操作以后我们再去看看ndf文件有没有变化，如下图所示，我们发现ndf文件大小有增长，这表明已经往这几个分区中写入了数据。

上边的表结构是通过select * into语句来创建表的，这种方式没有普遍性，下面我们通过create table来创建表结构：

View Code

--创建表结构
CREATE TABLE [SalesOrderDetail](
    [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetail_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED([SalesOrderDetailID] ASC)--主键非聚集索引
)

--在分区列上创建聚集索引，并且引入分区架构
create clustered index IXC_SalesOrderDetail_UnitPrice on dbo.SalesOrderDetail(UnitPrice)
ON [Sch_SalesOrderDetail_UnitPrice]([UnitPrice])

--在rowguid和ModifiedDate上面添加约束
ALTER TABLE [SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_rowguid]  DEFAULT (newid()) FOR [rowguid]
GO
ALTER TABLE [SalesOrderDetail] ADD  CONSTRAINT [DF_SalesOrderDetail_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
GO

step5'：创建分区表并填充数据

在上面语句中，我们发现：

创建主键约束的时候我们指定使用的是nonclustered index，如果不指明的话那么默认创建的是聚集索引。但是一张表只能有一个聚集索引，而分区列上又必须有聚集索引，所以我们这里要显式声明primary key为nonclustered index。
然后在UnitPrice列上面创建聚集索引，并且指明分区架构，也就是ON [Sch_SalesOrderDetail_UnitPrice]([UnitPrice])。

在创建好表结构以后，我们往里面插入数据。如果一条一条插入数据比较慢的话，我们可以在AdventureWorks2008R2.Sales.SalesOrderDetail表中导入，导入语句如下：

View Code

--插入数据,SalesOrderDetailID是标识列，identity约束
Insert into SalesOrderDetail(CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount) 
 select CarrierTrackingNumber,OrderQty,    ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount from AdventureWorks2008R2.Sales.SalesOrderDetail

6.查看分区表各分区数据情况（数据行数，最大最小 UnitPrice值）

执行如下查询命令

View Code

select partition = $partition.fn_Partition_SalesOrderDetail_UnitPrice(UnitPrice)
      ,rows      = count(*)
      ,minval    = min(UnitPrice)
      ,maxval    = max(UnitPrice)
  from dbo.SalesOrderDetail
 group by $partition.fn_Partition_SalesOrderDetail_UnitPrice(UnitPrice)
 order by partition

其中fn_Partition_SalesOrderDetail_UnitPrice(UnitPrice)是分区函数，UnitPrice是列名。

使用第一种方法，也就是select * into的方法导入数据，其查询结果为：

partition   rows        minval                maxval
----------- ----------- --------------------- ---------------------
1           88053       1.3282                469.794
2           12243       539.99                986.5742
3           9582        1000.4375             1466.01
4           939         1700.99               1971.9942
5           10500       2024.994              3578.27

我们可以看到每一个分区上面都有数据。有些总数据量小于2MB，所以ndf文件大小没有改变。如果ndf文件文件大小变化不大，我们可以多执行几次上面的数据导入语句。

使用第二种方法插入数据，一共执行了三次，其最后文件大小如下图所示：

分区上存储的数据统计信息如下：

partition   rows        minval                maxval
----------- ----------- --------------------- ---------------------
1           264159      1.3282                469.794
2           36729       539.99                986.5742
3           28746       1000.4375             1466.01
4           2817        1700.99               1971.9942
5           31500       2024.994              3578.27

从上述查询结果我们可以发现在partition4(对应FG_TEST_SalesOrderDetail_UnitPrice_3_data_1.ndf这个次要数据文件)中，数据行只有2817条，这也说明了为什么在上图中只有FG_TEST_SalesOrderDetail_UnitPrice_3_data_1.ndf这个文件大小没有增长，依然是2048KB。

查看partition状态的的四个视图

View Code

--查看partition的四个视图
select * from sys.partition_functions--查看分区函数
select * from sys.partition_parameters
select * from sys.partition_range_values--查看分区函数对应的分区范围
select * from sys.partition_schemes--查看分区架构