SQL Server 2005/2008 自带的分区表

 

　　SQL Server自2005版本开始引入了分区特性，允许将逻辑上的一个数据表在物理上分成许多分区表，检索数据时可直接根据数据信息及分区规则直接去检索相应的分区表，从而避免了频繁检索所有数据，大大提升了的数据响应速度。

 

一、前言

　　当数据库某个表的数据量达到某个数量级（如百万级）时，进行数据查询操作往往需耗费较多时间，这时候你就可以考虑使用表分区或分数据库了。表分区就是将一个大表按照一定的规则分成若干个小表，这样可减少检索的性能开销，但是分库分表往往需配套一个底层逻辑数据接口程序，所有数据的增删改查操作均通过接口程序进行，从而程序员在程序开发时无需关心底层的分库分表规则，良好的降低了程序和数据的耦合度。

　　当某几个表特别庞大而其余比较正常时，可简单考虑使用SQL Server自带的表分区功能，分区后数据表在物理上存在多个地方，但在逻辑上与分表前并无差别，因此，分表前后无需改动程序。

　　使用SQl Server分区表的优点：

　　1)提高数据库性能：分区后查询可根据规则去对应的分区表查询，缩小了查询范围，大大提升了速度。若将分区表分不到不同的硬盘或其他存储介质中，则会进一步提升查询速度，因为I/O的速度很大程序上限制了数据库的操作速度。

　　2)便于管理：把一个大表分成若干个小表，备份和恢复时不需要操作整个数据表，可单独操作分区表。

　　3)提高可用性：当一个分区跪了后，其余分区还可用，不受影响，降低了数据库损坏的风险。

　　4)方便存档：可将历史数据存放到专门的存档服务器中，降低主要服务器的性能开销。

 

二、创建分区表

1、定义分表规则。根据表数据定义数据表划分的规则，可根据时间、地域等进行划分，如将近几年的订单记录按照年份进行划分。

2、创建数据库文件组。文件组可以将不同的分区表放在不同的文件组里，既便于理解又能提高运行速度。创建步骤，数据库->右键->属性->文件组，点击添加，如图2-1所示。

图2-1 创建文件组

3、创建数据库文件。数据库文件用于存放数据，存放于不同文件便于管理和维护。创建数据库文件如图2-2所示。

图2-2 创建文件

　　创建时注意事项：1)将不同文件存放于不同的文件组。2)条件允许，可将不同的文件存放于不同的硬盘分区中，最好是放在不同的独立硬盘中，可加快数据库的读取速度。

4、创建分区函数。分区函数用来告诉SQL Server表分区的规则，是表分区一个关键步骤。按照本分区实例，将订单表Orders分成了3个分区表（假设订单是表最早记录是2012年）：①2012-01-01（包含2012-01-01）到2013-01-01之间的数据 ②2013-01-01（包含2013-01-01）到2014-01-01之间的数据 ③2014-01-01（包含2014-01-01）之后的数据。

　  区函数创建语句如下所示：

--partFunOrder为分区函数 right表示将分界点数据放于右表
create partition function partFunOrder(datetime)
as range right for values('20130101','20140101')

5、创建分区方案。分区方案的作用是将分区函数生成的分区映射到文件组中。分区函数定义了分区的规则，分区方案定义了分区后的数据存放的文件组。

　  区方案创建语句如下所示：

--partSchOrder为分区方案名 to后面跟分区对应存放的文件组
create partition scheme partSchOrder
as partition partFunOrder
to(Orders_2012,Orders_2013,Orders_2014)

6、创建分区表。注意应用分区函数partFunOrder，并指定分区字段。分区表创建后不能再创建聚集索引。

　  分区表创建语句如下所示：

--创建分区表
--on partSchOrder([CreateTime]) 指定创建分区函数，指定创建分区依据字段
--注意此处不能创建聚集索引，ID不能设置为主键
create table Orders(
    [ID] [int] identity(1,1) not null,
    [ProductName] [varchar](50) not null,
    [CreateTime] [datetime] not null
)on partSchOrder([CreateTime])

　  完成上述操作，分区表已创建完成，物理分离，逻辑一致的一个表。查看属性，可看到表已属于分区表。

 

三、分区表的增删改查操作（与普通表操作一致）

1、插入测试数据。

insert Orders([ProductName],[CreateTime]) values('钢笔','2012-01-05')  --分区1
insert Orders([ProductName],[CreateTime]) values('笔记本','2012-08-26')  --分区1
insert Orders([ProductName],[CreateTime]) values('餐巾纸','2013-04-11')  --分区2
insert Orders([ProductName],[CreateTime]) values('电风扇','2013-05-15')  --分区2 
insert Orders([ProductName],[CreateTime]) values('电动车','2013-11-07')  --分区2 
insert Orders([ProductName],[CreateTime]) values('AD钙奶','2014-02-07')  --分区3 
insert Orders([ProductName],[CreateTime]) values('长安CS75','2014-06-18')  --分区3 
insert Orders([ProductName],[CreateTime]) values('显示器','2014-07-11')  --分区3  
insert Orders([ProductName],[CreateTime]) values('酸奶','2014-08-19')  --分区3 

2、查看Orders表数据，如图3-1所示。

图3-1 表数据

3、查看某条数据存放于哪个表，使用$PARTITION函数，这个函数可以调用分区函数，并返回数据所在物理分区的编号。使用语法：

$PARTITION.分区函数名(表达式)

　  如查询创建时间为2013-05-05的记录保存的分区号语句如下：

select $PARTITION.partFunOrder('2013-05-15')

4、分区表的统计查询

　  查询分区表存放的数据：

select * from Orders where $PARTITION.partFunOrder(CreateTime)=1
select * from Orders where $PARTITION.partFunOrder(CreateTime)=2
select * from Orders where $PARTITION.partFunOrder(CreateTime)=3

　  统计分区表的数据：

select $PARTITION.partFunOrder(CreateTime) as 分区编号,COUNT(ID) as 记录数 from Orders group by $PARTITION.partFunOrder(CreateTime)

5、修改数据分区关键字值后，SQL Server会自动将数据移动至对应的分区表中。

 

四、分区操作（删除、新增）

1、合并/删除分区，就是将分区函数中的相应分界值删除。如将上述的分区1和2合并，即将2012-01-01到2014-01-01的数据存放于一个分区，执行语句如下：

--删除分界点2013-01-01，将分区1和2合并
ALTER PARTITION FUNCTION partFunOrder() MERGE RANGE ('2013-01-01')  

2、新增分区，先新增一个文件组用于存放新分区表数据。假设现在到了2015年，需要将2015年的订单数据与2014年分开，则需新建2015分区。

　  1)首先，新增文件组Orders_2015，如图4-1所示。

图4-1 新增文件组Orders_2015

　　2)新增文件，并指定文件组Orders_2015，如图4-2所示。

图4-2 新增文件

　  3)然后指定分区方案partSchOrder使用文件组Orders_2015，语句如下：

--分区方案新增指定有效文件组
alter partition scheme partSchOrder next used [Orders_2015]

　  4)修改分区函数，新增分界点2015-01-01，代码如下：　

--新增分界点2015-01-01
alter partition function partFunOrder() split range('2015-01-01')

　  执行完成后，新的分区Orders_2015已创建完成。

 

五、普通表与分区表的转换

1、普通表转成分区表

　   由于分区表除分区字段外的其余字段不能存在聚集索引，因此普通表转换成分区表前先检查表是否存在聚集索引，若存在则删除。确保表中不存在其余聚集索引后，创建一个新的聚集索引，并使用分区方案，创建代码如下所示。

--创建聚集索引，并使用之间创建的分区方案partSchOrder
create clustered index CT_Order on Orders([CreateTime])
on partSchOrder([CreateTime])

　　执行完后，查看表属性，发现表已经转换成了分区表。　  

2、分区表转为普通表。删除所有分界点，分区表只有一个分区，与普通表一致。 

--删除所有分界点
ALTER PARTITION FUNCTION partFunOrder() MERGE RANGE ('2013-01-01') 
ALTER PARTITION FUNCTION partFunOrder() MERGE RANGE ('2014-01-01') 
ALTER PARTITION FUNCTION partFunOrder() MERGE RANGE ('2015-01-01')