贝网博客

我的分类
流水记事
源码下载
Asp.net
其它
数据库
Javascript
.Net技术
我的相册
友情链接
博客园
CSDN博客
Start0
最新回复
嗷嗷的cCC
fasdfasdf
[:..
lz这个东西好厉害,我..
哈哈,好照片
不错,以前一直用黄色..
终于找到支持ff的修正..
终于找到支持ff的修正..
新鲜性
看看,试试,好不好使。
分类 =》数据库
Sqlserver2005分区表实现步骤
发表于:2011-01-30 16:39:41
更新于:2011-11-18 10:27:26

在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,最后一个使用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 = $partition.PartionFunction(flag)
      ,rows      = count(*)
      ,minval    = min(flag)
      ,maxval    = max(flag)
  from tbAddressbook
 group by $partition.PartionFunction(flag)
 order by partition

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

发表评论
名称(*):
邮箱:
正文:

©2008 Beinet.cn 版权所有