|
在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:sqldatadb1_00.ndf',SIZE = 5MB,FILEGROWTH = 5MB) TO FILEGROUP [db1_fg_00];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_01]',FILENAME='e:sqldatadb1_01.ndf') TO FILEGROUP [db1_fg_01];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_02]',FILENAME='e:sqldatadb1_02.ndf') TO FILEGROUP [db1_fg_02];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_03]',FILENAME='e:sqldatadb1_03.ndf') TO FILEGROUP [db1_fg_03];
ALTER DATABASE [db1] ADD FILE (NAME='[db1_f_04]',FILENAME='e:sqldatadb1_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])?
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
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
