Sql Server2008 表分区实现（一）

最新推荐文章于 2025-03-17 17:58:34 发布

baduiliao3311

最新推荐文章于 2025-03-17 17:58:34 发布

阅读量156

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/XieDong/articles/4719929.html

版权

从事两年的数据分析类软件开发工作，一直用的是sql数据库，接触的数据量越来越大，当一个表的行数达到Millions级时，在执行查询，更新，连接查询时，效率成了我最头疼的事情，很担心那天因为数据的问题，导致系统奔溃，所以最近研究了sql的数据表或索引分区。

sql表分区分为三部分：分区函数，分区方案，分区表或索引。

1.创建分区函数

分区函数是定义如何根据某些列（称为分区依据列）的值将表或索引的行映射到一组分区。在选择数据库->存储下有分区函数，但是只有启用Powershell,没有新建，暂时没有明白powershell是什么东东，只能通过T-sql来创建分区函数，分区函数的关键字为PARTITION。代码如下：

CREATE PARTITION FUNCTION [fun_zpzjb] (varchar(4)) 
AS RANGE
RIGHT
 FOR VALUES (N'2011', N'2012', N'2013', N'2014', N'2015')
GO

这个分区函数说明是以一个长度为4的字符字段（年度）分区，需要注意的是关键字RIGHT，表示等于号（=）是区间靠右的，即区间6为A>=2015，如果你想等于号（=）区间靠左，也可以用LEFT，区间1为A<=2011。区间划分如图：

分区值	1	2	3	4	5	6
分区值	A<2011	2011<=A<2012	2012<=A<2013	2013<=A<2014	2014<=A<2015	A>=2015

在建立分区函数时应注意：

若要创建分区函数，请指定分区数、分区依据列和每个分区的分区依据列值的范围。
指定分区依据列时，仅可以指定一列。
一张表最多可以有 1000 个分区。

2.创建分区方案

创建分区方案前必须创建文件组和存放文件。分区文件可以只有一个文件组，也可以有多个文件组，文件可以存储到不同的盘符中，官方说放到不同的盘符中可以提高读写速度，这个我没有试过。创建文件可以设置，直接给个图吧（注意先建文件组，后建文件）：

下面就是创建分区方案，创建分区方案的关键字为PARTITION SCHEME,语法如下：

CREATE PARTITION SCHEME [sch_zpzjb] 
AS PARTITION [fun_zpzjb] 
TO ([fg_zpzjb1], [fg_zpzjb1], [fg_zpzjb1], [fg_zpzjb1], [fg_zpzjb2], [fg_zpzjb2])
GO

这个分区方案表示应用了[fun_zpzjb]分区函数，前四个分区映射到文件组【fg_zpzjb1】上，后两个分区映射到【fg_zpzjb2】上。

3.创建分区表或索引

我这里是为了做数据测试，应用了一个已经存在的表，表里面的数据行数达到800万行，表是已存在，索引也建了，所以我通过删除索引，在无分区表上创建分区。语法如下：

drop index IX_History_ReferenceOrderID
GO

CREATE NONCLUSTERED INDEX IX_History_ReferenceOrderID 
ON Production.TransactionHistory (col1，col2)
ON [sch_zpzjb] (col2) ; 
GO

新建分区索引的时候，通过on [sch_zpzjb] (col2) 调用定义的分区方案。到这里，整个分区已经建好了，但是我们怎么知道我们的分区建好了呢，下面通过一个查询，查看分区的结果：

select ps.name as scheme_name,
p.partition_number,
convert(varchar(10), ds2.name) as filegroup,
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary,
str(p.rows, 9) as rows   from sys.indexes i       --索引
inner join sys.partition_schemes ps on i.data_space_id=ps.data_space_id        --分区方案
inner join sys.destination_data_spaces dds on ps.data_space_id = dds.partition_scheme_id    --分区数据空间映射
inner join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id --数据空间(文件组)
inner join sys.partitions p on dds.destination_id = p.partition_number and p.object_id = i.object_id and p.index_id = i.index_id             --分区数据
inner join sys.partition_functions pf on ps.function_id = pf.function_id          --分区函数
left JOIN sys.Partition_Range_values v on pf.function_id = v.function_id and v.boundary_id = p.partition_number - pf.boundary_value_on_right  --分界
WHERE i.object_id = object_id('sb_zpzjb_fg')
and i.index_id in (0, 1) 
order by p.partition_number