Sqlserver2005分区表实现步骤

php中文网

发布时间：2016-06-07 15:15:26

1562人浏览过

来源于php中文网

原创

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

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

LOVESTUdio多校园网络店铺

主要更新介绍：完美整合Discuz!论坛，实现一站式登陆、退出、注册；同步所有会员资料；新增购物车功能，商品购买更加方便、快捷；新增部分快捷菜单，网站访问更加方便；限制首页商品、店铺标题显示长度；修正会员后台管理不能更改密码的错误；完善商品显示页面所有功能链接；修正后台标签管理部分错误；修正前台学校列表不按后台顺序显示的错误；修正搜索功能中学校名称过长导致显示紊乱的现象；修正

下载

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