SQL Server表分区详解

 

        分区请三思：

        1、虽然分区可以带来众多的好处，但是同时也增加了实现对象的管理费用和复杂性。因此在进行分区之前要首先仔细的考虑以确定是否应为对象进行分区。

        2、在确定了为对象进行分区后，下一步就要确定分区键和分区数。要确定分区数据，应先评估您的数据中是否存在逻辑分组和模式。

        3、确定是否应使用多个文件分组。为了有助于优化性能和维护，应使用文件组分离数据。文件组是数据库数据文件的逻辑组合，它可以对数据文件进行管理和分配，以便提高数据库文件的并发访问效率。

        为了简化操作，SQL Server 2008中为表分区提供了相关的操作。  

        操作的顺序：      1、先定义文件组      2、指定哪些辅助数据库文件属于这个文件组      3、将表放入到文件组中

        数据库分文件组(指定磁盘)：

        数据实际上是依附于表来存在的，我们将表放入到文件组中，而文件组是一个逻辑的概念，其实体是辅助数据库文件(ndr)，所以就等于将我们指定 的数据放入到了指定的辅助数据库文件中，然后如果将这些辅助数据库文件放入在不同的磁盘分区中，就可以最终实现有针对性的对相应的数据实现性能的优化。

        创建文件组时，定义不同的文件组名称，可以有序地进行下一步表分区的分区映射文件组，如上图(选择数据库，右键查看属性图)。

        一个水平分区表中有多个分区，每个分区对应一个文件组，这样就产生了很多文件组，因此性能也会有所提升，包括I/O性能提升，因为所有分区可以 驻扎在一个不同的磁盘上。另一个好处是可以通过备份文件组单独备份一个分区。此外，SQL Server数据库引擎可以智能判断哪个分区上存放了什么数据，如果不止一个分区被访问，那么还可以借助多处理器实现并行数据检索。这种设计也充分利用了 分区表的优势。

        1、提高可伸缩性和可管理性：在SQL Server 2005中建立分区，改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。 

        2、提高性能

        3、只有将数据分区分到不同的磁盘上，才会有较大的提升。

        4、因为在运行涉及表间联接的查询时，多个磁头可以同时读取数据。

        对SQL Server数据表进行分区的过程分为三个步骤：

        1) 建立分区函数

        2) 建立分区方案

        3) 对表格进行分区

        步骤如下:

1、CREATE PARTITION FUNCTION意思是创建一个分区函数。

2、partfunSale为分区函数名称。

3、AS RANGE RIGHT为设置分区范围的方式为Right，也就是右置方式。

4、FOR VALUES ('20100101','20110101','20120101','20130101')为按这几个值来分区。  

• CREATE PARTITION FUNCTION YearCustomerFollowFunction(fDate) AS RANGE RIGHT FOR VALUES('20100101','20110101','20120101','20130101')    
•   
• -- 分区方案的作用是将分区函数生成的分区映射到文件组中去。分区函数的作用是告诉SQL Server，如何将数据进行分区，而分区方案的作用则是告诉SQL Server将已分区的数据放在哪个文件组中  
• CREATE PARTITION SCHEME YearCustomerFollow AS PARTITION YearCustomerFollowFunction TO(Sale2009,Sale2010,Sale2011,Sale2012,Sale2013)    
•   
• -- 创建分区表 ON YearCustomerFollow 分区方案名  
• CREATE TABLE t_CustomerFollow_history([Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](16) NOT NULL, [fDate][datetime] NOT NULL) ON YearCustomerFollow([fDate])    
•   
• -- 删除一个分区  
• ALTER PARTITION FUNCTION YearCustomerFollowFunction() MERGE RANGE ('20100101')  
•   
• -- 添加一个分区  
• ALTER PARTITION SCHEME YearCustomerFollow NEXT USED [Sale2010]     -- 分区方案  
• ALTER PARTITION FUNCTION YearCustomerFollowFunction() SPLIT RANGE ('20100101')  -- 分界值  
•   
•     

SQL2005分区表

分区表有利于管理海量数据的表和索引,在分区中引入了一个分区键的概念,分区键用于根据某个区间值,特定值列表或散列函数执行数据的聚集.

使用分区表有如下好处:

1. 提高数据的高用性:可用性的提高源自每个分区的独立性.优化器知道这种分区机制,会相应的从查询计划中除去未引用的分区.
2. 减轻管理员负担.
3. 改善某些查询性能,在只读查询的性能方面,分区对两类操作起作用.

l  分区消除:处理查询时,不考虑某些分区.

l  并行操作:并行全表扫描和并行索引区间扫描.

1. 减少资源竞争.

 

脚本:

--首先手工创建文件分组、物理文件；脚本方式创建后边介绍

                       

 

 

---- 创建分区函数

--go

--create partition function MineDateRange(datetime)

--as

--range right(left) for values (

--'2010-01-01',

--'2011-01-01',

--'2012-01-01')

--go

 

 

--

---- 创建分区方案

--go

--create partition scheme Mine_Orders

--as

--partition MineDateRange

--to (test2010, test2011, test2012,test2013)

--go

 

 

-- 创建分区表

--go

--create table dbo.OrdersTest

--(

--   OrderID     int          not null

--  ,CustomerID  varchar(10)  not null

--  ,EmployeeID  int          not null

--  ,OrderDate   datetime     not null

--)

--on Mine_Orders(OrderDate)

--go

 

-- 创建聚集分区索引

--create clustered index IXC_OrdersTest on dbo.OrdersTest(OrderDate)

--go

 

--插入数据

--INSERT INTO [HyMineSecurityMonitor].[dbo].[OrdersTest]

--           ([OrderID]

--           ,[CustomerID]

--           ,[EmployeeID]

--           ,[OrderDate])

--     VALUES

--           (7

--           ,'ffff',7

--           ,'2015-10-10 12:20:23')

 

 

--查询数据

--select * from OrdersTest

 

--查看每个分区的数据分布情况

--SELECT     partition = $partition .MineDateRange(OrderDate), rows = count(*), minval = min(OrderDate), maxval = max(OrderDate)

--FROM         dbo.OrdersTest

--GROUP BY $partition .MineDateRange(OrderDate)

--ORDER BY partition

 

--------------------------------------------修改分区----------------------------------------------------

--添加文件分组

--ALTER DATABASE HyMineSecurityMonitor ADD FILEGROUP [test2014]

 

--添加物理文件

--ALTER DATABASE HyMineSecurityMonitor

--ADD FILE

--(NAME = N'test2014',FILENAME = N'D:\DataBase\testDB\test2014.ndf',SIZE = 5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)

--TO FILEGROUP [test2014]

 

--修改分区函数新增一个分区

--go

--alter partition function MineDateRange()

--split range('2013-01-01')

--go

 

--修改分区方案新增一个文件

--go

--alter partition scheme Mine_Orders next used [test2014]

--go

 

--修改分区函数合并一个分区

--go

--alter partition function PF_Orders_OrderDateRange()

--merge range('2013-01-01')

--go

 

 

--------------------------------------------------------分区表数据迁移-------------------------------------------

--SQL Server 2005 分区表分区切换的三种形式：

--

--1. 切换分区表的一个分区到普通数据表中：Partition to Table；（普通表：dbo.Orders_1998）

   

--     create table dbo.Orders_1998

--     (

--        OrderID     int          not null

--       ,CustomerID  varchar(10)  not null

--       ,EmployeeID  int          not null

--       ,OrderDate   datetime     not null

--     ) on [test2012]

--

--     alter table dbo.OrdersTest switch partition 3 to dbo.Orders_1998

 

       --1). 普通表必须建立在分区表切换分区所在的文件组上。

       --2). 普通表的表结构跟分区表的一致；

       --3). 普通表上的索引要跟分区表一致。

       --4). 普通表必须是空表，不能有任何数据。

 

--2. 切换普通表数据到分区表的一个分区中：Table to Partition；（普通表：dbo.Orders_1998）

 

        --5).普通表必须加上和分区数据范围一致的check 约束条件。

 

--       alter table dbo.Orders_1998 add constraint CK_Orders1998_OrderDate

--       check (OrderDate>='1998-01-01' and OrderDate<'1999-01-01')

--

--       alter table dbo.Orders_1998 switch to dbo.OrdersTest partition 3

 

--3. 切换分区表的分区到另一分区表：Partition to Partition。(分区表dbo.Orders、dbo.OrdersArchive)

--        alter table dbo.Orders switch partition 1  to dbo.OrdersArchive partition 1

--        alter table dbo.Orders switch partition 2  to dbo.OrdersArchive partition 2

--        alter table dbo.Orders switch partition 3  to dbo.OrdersArchive partition 3

 

    实际上，分区表分区切换并没有真正去移动数据，而是 SQL Server 在系统底层改变了表的元数据。因此分区表分区切换是高效、快速、灵活的。利用分区表的分区切换功能，我们可以快速加载数据到分区表。卸载分区数据到普通表，然后 truncate 普通表，以实现快速删除分区表数据。快速归档不活跃数据到历史表。

    

 

--查看一共多少个分区 *******safun是分区函数,&PARTITION是调用分区函数， OrderCreateDate是分区字段

select * from sys.partition_functions

--查看该日期在哪个分区

select $PARTITION.safun ('2014-05-01') 

--查看某个分区数据

select * from SA_Order where $PARTITION.safun(OrderCreateDate)=2

--查看所有数据分别在哪个分区

select $PARTITION.safun(OrderCreateDate) as 分区编号,count(OrderCode) as 记录数 from SA_Order group by $PARTITION.safun(OrderCreateDate)