Sqlserver2005分区表实现步骤

在SqlServer2005中新增了分区表的支持，对于一些大数据量的表，我们可以对它进行分区，以便提供更好的性能，下面是我的一些体会和详细的分区步骤了，
假设要操作的数据库名为db1,表名为tb1(id 自增int, insertTime DateTime)，表内保存了2006~2010年共5年的数据，要做5个分区：

 

0、分区键的选择
分区的关键是要选择好分区键，就是在插入数据时，新的数据按什么条件插入到需要的分区，一般而言，分区键一般要满足下面2个条件：
a、常用的检索能保证检索结果在同一个分区内
b、能把数据均匀分布到各个分区
这里如果tb1是报表，主要根据时间来检索数据的话，那么分区键可以用insertTime，根据时间段进行分区
如果tb1是用户表，主要根据id进行数据检索，因为id是自增字段，那么可以根据id对分区数进行取模（比如5个分区就是id%5）

1、新建文件组
你需要为数据库创建新的文件组，可以理解为分区，就是数据分别存储到几个文件组中，下面是建5个文件组：
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_00];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_01];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_02];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_03];
ALTER DATABASE [db1] ADD FILEGROUP [db1_fg_04];
go

2、为新建的文件组添加文件，指示插入这些文件组的数据具体对应到哪个物理文件：
-- 下面的sql如果省略SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB，表示按默认值
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_00]', FILENAME='e:\sqldata\db1_00.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB) TO FILEGROUP [db1_fg_00];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_01]', FILENAME='e:\sqldata\db1_01.ndf') TO FILEGROUP [db1_fg_01];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_02]', FILENAME='e:\sqldata\db1_02.ndf') TO FILEGROUP [db1_fg_02];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_03]', FILENAME='e:\sqldata\db1_03.ndf') TO FILEGROUP [db1_fg_03];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_04]', FILENAME='e:\sqldata\db1_04.ndf') TO FILEGROUP [db1_fg_04];
go

3、创建分区函数，这个函数的作用是定义分区数据的左右边界
如果用insertTime作为分区键，且每个分区保存1年的数据，那么创建的函数如下：
CREATE PARTITION FUNCTION [PartionFunction](datetime)
AS RANGE LEFT FOR VALUES (     -- LEFT表示左边界(小于等于),RIGHT表示右边界(大于)
    N'2006-12-31 23:59:59.997',--小于等于该时间,保存在第1个分区
    N'2007-12-31 23:59:59.997',--小于等于该时间,保存在第2个分区
    N'2008-12-31 23:59:59.997',--小于等于该时间,保存在第3个分区
    N'2009-12-31 23:59:59.997',--小于等于该时间,保存在第4个分区
    N'2010-12-31 23:59:59.997',--小于等于该时间,保存在第5个分区
)
如果用id%5作为分区键, 那么函数如下（因为对5取模，所以值只有0~4）
CREATE PARTITION FUNCTION [PartionFunction](int)
AS RANGE LEFT FOR VALUES (0, 1, 2, 3, 4)
注：函数的参数也允许使用函数，例如：DateAdd(ms, -3, '2010-12-31 12:13:14.997')
 

4、创建分区架构，这个架构根据上面的函数结果，指示符合该结果的数据要保存到哪个分区
CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO
([db1_fg_00], [db1_fg_01], [db1_fg_02], [db1_fg_03], [db1_fg_04], [PRIMARY])
注1：架构的参数个数必须等于函数的参数个数+1，最后一个使用PRIMARY文件组即可(不符合分区条件的所有数据都会放入最后一个文件组)
注2：可以不创建文件组，所有参数都使用PRIMARY，这样所有的分区数据都在同一个文件组里，如果所有分区文件都在同一个分区，那么可以不创建文件组，全部使用同一个文件组，比如：
CREATE PARTITION SCHEME [PartionStruct] AS PARTITION [PartionFunction] TO 
([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]) 

5、对需要分区的表进行分区
需要说明的是：第1到第4步，只是为数据库创建分区支持，ok后，可以使用上面创建的分区函数和架构对数据库里的多个表进行分区
5.1、首先删除tb1的主键（因为分区键必须是主键和聚集索引之一）
5.2、如果使用insertTime作为分区键，那么把id和insertTime作为主键，并创建聚集索引：
ALTER TABLE tb1 ADD CONSTRAINT PK_tb1
    PRIMARY KEY CLUSTERED(insertTime, id)
    ON PartionStruct(insertTime)
go
5.3、如果使用id%5作为分区键，那么要先新增一个计算列flg，并把flg和id作为主键，并创建聚集索引：
--添加持久计算列（也可以不使用计算列，添加普通列，这样的话，每次都要手动去写这个字段的值，比较麻烦）
ALTER TABLE tb1 ADD flg AS id % 20 PERSISTED NOT NULL
go
--增加计算列为聚集索引
ALTER TABLE tb1 ADD CONSTRAINT PK_tb1
    PRIMARY KEY CLUSTERED(flg, id)
    ON PartionStruct(flg)
go

6、分区完成
到这里，我们要做的分区工作已经完成，但是这里还需要做一件事情，检查我们的数据，是不是正确分区了：
执行下面的SQL，可以看到，根据这个分区函数处理后，各个分区储存的数据量，如果每个分区的rows字段数目基本差不多，说明数据的分布还是比较均匀的
select partition_number,rows 
  from sys.partitions 
 where object_id=object_id('tbAddressbook') --and partition_number=$partition.分区函数名(值)
或者用下面的语句（比较慢）
select partition = $partition.PartionFunction(flag)
      ,rows      = count(*)
      ,minval    = min(flag)
      ,maxval    = max(flag)
  from tbAddressbook
 group by $partition.PartionFunction(flag)
 order by partition

最后要说明的是，对tb1表做了分区后，所有的检索语句，最好都加上分区键作为检索条件

/*

--合并201310这个分区

ALTER PARTITION FUNCTION DATE_PF ()

MERGE RANGE (201310);

 

-- 拆分分区

ALTER PARTITION SCHEME [DATE_PS]

NEXT USED [PRIMARY]

GO

ALTER PARTITION FUNCTION DATE_PF ()

SPLIT RANGE (20130301)

*/

 

/*

-- 切换分区，新分区表必须是空表，才能切换

CREATE TABLE [fqb0](

[ID] [bigint] NOT NULL,-- Identity(1,1),

[IMEI] [varchar](100) NOT NULL,

[dt] [int] NOT NULL

)

GO

-- 新分区表要有同样的聚集索引

CREATE CLUSTERED INDEX [fqb0_key] ON [fqb0]

(

[dt] ASC

)

GO

--下面的partition 1这个1，可以通过上面的SQL:查看分区数据量，来确定

alter table fqb switch partition 1 to fqb0

 */

 

---

首发：http://beinet.cn