SQL Server 分区表

SQL Server 分区表

一. 概念

1.1. 分区表简介

分区表是在SQL SERVER2005之后的版本引入的特性。这个特性允许把逻辑上的一个表在物理上分为很多部分。而对于SQL SERVER2005之前版本，所谓的分区表仅仅是分布式视图,也就是多个表做union操作。分区表在逻辑上是一个表，而物理上是多个表。从用户角度来看，分区表和普通表是一样的。使用分区表的主要目的是为改善大型表以及具有多个访问模式的表的可伸缩性和可管理性。

1.2. 适用场景

• 数据库中某个表的数据很多，在查询数据时会明显感觉到速度很慢，这个时候需要考虑分区表；
• 数据是分段的，如以年份为分隔的数据，对于当年的数据经常进行增删改查操作，而对于往年的数据几乎不做操作或只做查询操作，这种情况可以使用分区表。对数据的操作如果只涉及一部分数据而不是全部数据的情况可以考虑分区表，如果一张表的数据经常使用且不管年份之类的因素经常对其增删改查操作则最好不要分区。

1.3. 优势

• 提高可伸缩性和可管理性：在SQL Server 2005中建立分区，改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。
• 提高性能
• 只有将数据分区分到不同的磁盘上，才会有较大的提升。
• 因为在运行涉及表间联接的查询时，多个磁头可以同时读取数据。

二. 分区表创建

2.1. 创建文件组

alter database testSplit add filegroup FileGroup1
alter database testSplit add filegroup FileGroup2
alter database testSplit add filegroup FileGroup3
alter database testSplit add filegroup FileGroup4
alter database testSplit add filegroup FileGroup5

2.2. 创建数据文件

alter database testSplit add file (name=N'file_1',filename=N'D:\test\file_1.ndf',size=5Mb,filegrowth=5mb) to filegroup FileGroup1
alter database testSplit add file (name=N'file_2',filename=N'D:\test\file_2.ndf',size=5Mb,filegrowth=5mb) to filegroup FileGroup2
alter database testSplit add file (name=N'file_3',filename=N'D:\test\file_3.ndf',size=5Mb,filegrowth=5mb) to filegroup FileGroup3
alter database testSplit add file (name=N'file_4',filename=N'D:\test\file_4.ndf',size=5Mb,filegrowth=5mb) to filegroup FileGroup4
alter database testSplit add file (name=N'file_5',filename=N'D:\test\file_5.ndf',size=5Mb,filegrowth=5mb) to filegroup FileGroup5

2.3. 创建分区函数

CREATE PARTITION FUNCTION [PartitionFun] (INT) AS RANGE LEFT
FOR VALUES (N'200000',N'400000',N'600000',N'800000',N'1000000')

2.4. 创建分区架构

CREATE PARTITION SCHEME [PartitionSchema] AS PARTITION [PartitionFun] TO ([PRIMARY], [FileGroup1], [FileGroup2], [FileGroup3], [FileGroup4], [FileGroup5])

2.5. 创建分区索引

CREATE CLUSTERED INDEX [ClusteredIndex_on_PartitionSchema] ON [dbo].[testtable] (  [UserID] ) 
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PartitionSchema]([UserID])

三. 分区表的使用

3.1. 数据所在分区

SELECT $PARTITION.PartitionFun(450000)

3.2. 查看分区数据

SELECT * from testtable where $PARTITION.PartitionFun(userid)=1

3.3. 查看分区分布

select convert(varchar(50), ps.name) as partition_scheme,
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 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
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 
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('testtable')    --此处是表名
and i.index_id in (0, 1) 
order by p.partition_number