SqlServer分区表概述（转载）

作者：优优绿园之时尚饰品_834 | 来源：互联网 | 2023-09-24 13:16

什么是分区表一般情况下，我们建立数据库表时，表数据都存放在一个文件里。但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件

什么是分区表

一般情况下，我们建立数据库表时，表数据都存放在一个文件里。

但是如果是分区表的话，表数据就会按照你指定的规则分放到不同的文件里，把一个大的数据文件拆分为多个小文件，还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小，还得到硬件系统的加强，自然对我们操作数据是大大有利的。

所以大数据量的数据表，对分区的需要还是必要的，因为它可以提高select效率，还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦，因为表分区会对数据库产生不必要的开销，除啦性能还会增加实现对象的管理费用和复杂性。

截止到SQL Server 2016，一张表或一个索引最多可以有 15,000 个分区

跟着做，分区如此简单

先跟着做一个分区表（分为11个分区），去除神秘的面纱，然后咱们再逐一击破各个要点要害。

分区是要把一个表数据拆分为若干子集合，也就是把把一个数据文件拆分到多个数据文件中，然而这些文件的存放可以依托一个文件组或这多个文件组，由于多个文件组可以提高数据库的访问并发量，还可以把不同的分区配置到不同的磁盘中提高效率，所以创建时建议分区跟文件组个数相同。

1.创建文件组

可以点击数据库属性在文件组里面添加

T-sql语法：

alter database <数据库名> add filegroup <文件组名>

---创建数据库文件组
alter database testSplit add filegroup ByIdGroup1
alter database testSplit add filegroup ByIdGroup2
alter database testSplit add filegroup ByIdGroup3
alter database testSplit add filegroup ByIdGroup4
alter database testSplit add filegroup ByIdGroup5
alter database testSplit add filegroup ByIdGroup6
alter database testSplit add filegroup ByIdGroup7
alter database testSplit add filegroup ByIdGroup8
alter database testSplit add filegroup ByIdGroup9
alter database testSplit add filegroup ByIdGroup10

2.创建数据文件到文件组里面

可以点击数据库属性在文件里面添加

T-sql语法：

alter database <数据库名称> add file <数据标识> to filegroup <文件组名称>

--<数据标识> （name:文件名，fliename:物理路径文件名，size:文件初始大小kb/mb/gb/tb，filegrowth:文件自动增量kb/mb/gb/tb/%,maxsize:文件可以增加到的最大大小kb/mb/gb/tb/unlimited）

alter database testSplit add file 
(name=N'ById1',filename=N'J:\Work\数据库\data\ById1.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup1
alter database testSplit add file 
(name=N'ById2',filename=N'J:\Work\数据库\data\ById2.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup2
alter database testSplit add file 
(name=N'ById3',filename=N'J:\Work\数据库\data\ById3.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup3
alter database testSplit add file 
(name=N'ById4',filename=N'J:\Work\数据库\data\ById4.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup4
alter database testSplit add file 
(name=N'ById5',filename=N'J:\Work\数据库\data\ById5.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup5
alter database testSplit add file 
(name=N'ById6',filename=N'J:\Work\数据库\data\ById6.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup6
alter database testSplit add file 
(name=N'ById7',filename=N'J:\Work\数据库\data\ById7.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup7
alter database testSplit add file 
(name=N'ById8',filename=N'J:\Work\数据库\data\ById8.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup8
alter database testSplit add file 
(name=N'ById9',filename=N'J:\Work\数据库\data\ById9.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup9
alter database testSplit add file 
(name=N'ById10',filename=N'J:\Work\数据库\data\ById10.ndf',size=5Mb,filegrowth=5mb)
to filegroup ByIdGroup10

执行完成后，右键数据库看文件组跟文件里面是不是多出来啦这些文件组跟文件。

3.使用向导创建分区表

右键到要分区的表--- >> 存储 --- >> 创建分区 --- >>显示向导视图 --- >> 下一步 --- >> 下一步。。

这里举例说下选择列的意思：

假如你选择的是int类型的列：那么你的分区可以指定为1--100W是一个分区，100W--200W是一个分区....

假如你选择的是datatime类型：那么你的分区可以指定为：2014-01-01--2014-01-31一个分区，2014-02-01--2014-02-28一个分区...

根据这样的列数据规则划分，那么在那个区间的数据，在插入数据库时就被指向那个分区存储下来。

我这里选用orderid int类型 --- >> 下一步 --- >>

左边界右边界：就是把临界值划分给上一个分区还是下一个分区。一个小于号，一个小于等于号。

然后下一步下一步最后你会得到分区函数和分区方案。

USE [testSplit]
GO
BEGIN TRANSACTION

--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')

--创建分区方案
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([PRIMARY], [ByIdGroup1], [ByIdGroup2], [ByIdGroup3], [ByIdGroup4], [ByIdGroup5], [ByIdGroup6], [ByIdGroup7], [ByIdGroup8], [ByIdGroup9], [ByIdGroup10])

--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] 
(
    [OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])

--删除分区索引
DROP INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] WITH ( ONLINE = OFF )

COMMIT TRANSACTION

执行上面向导生成的语句。分区完成。。

4.秀一下速度。

首先我在表中插入啦1千万行数据。给表分啦11个分区。前十个分区里面一个是100W条数据。。

说两句：

可见反常现象，扫描次数跟逻辑读取次数都是无分区表的2倍之多，但查询速度却是快啦不少啊。这就是分区的神奇之处啊，所以要相信这世界一切皆有可能。

分区函数，分区方案，分区表，分区索引

1.分区函数

指定分依据区列（依据列唯一），分区数据范围规则，分区数量，然后将数据映射到一组分区上。

创建语法：

create partition function 分区函数名(<分区列类型>) as range [left/right] 
for values (每个分区的边界值,....)

--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')

然而，分区函数只定义了分区的方法，此方法具体用在哪个表的那一列上，则需要在创建表或索引是指定。

删除语法：

--删除分区语法
drop partition function <分区函数名>

--删除分区函数 bgPartitionFun
drop partition function bgPartitionFun

需要注意的是，只有没有应用到分区方案中的分区函数才能被删除。

2.分区方案

指定分区对应的文件组。

创建语法：

--创建分区方案语法
create partition scheme <分区方案名称> as partition <分区函数名称> [all]to (文件组名称,....)

All关键字

上面关键字[all]是可选的，如果使用all，后面小括号中只能跟一个文件组名称，所有的分区都会被归入这个文件组。

Next Used文件组

分区方案的Next Used文件组用于如果分区函数以后新增了分区，那么默认会将新增的分区放到Next Used文件组，如果create partition scheme语句小括号中声明的文件组数量多于分区函数的指定的分区数，那么第一个多出来的文件组将被默认作为Next Used文件组（如果使用了all关键字，那么小括号中声明的文件组就是Next Used文件组）。例如下面的例子中[ByIdGroup4]将作为Next Used文件组。

CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000', N'2000', N'3000')
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup2], [ByIdGroup3],[ByIdGroup4],[ByIdGroup5])
/*
在上面的语句中
分区1000对应文件组[ByIdGroup1]
分区2000对应文件组[ByIdGroup2]
分区3000对应文件组[ByIdGroup3]

分区[ByIdGroup4]和[ByIdGroup5]为CREATE PARTITION SCHEME语句多声明的两个文件组，将使用第一个多声明的文件组[ByIdGroup4]作为Next Used文件组，[ByIdGroup5]会被CREATE PARTITION SCHEME语句忽略掉
*/

如果create partition scheme语句小括号中声明的文件组数量等于分区函数的指定的分区数，那么分区方案就没有Next Used文件组，例如如下分区方案中就没有创建Next Used文件组

--创建分区函数
CREATE PARTITION FUNCTION [bgPartitionFun](int) AS RANGE LEFT FOR VALUES (N'1000000', N'2000000', N'3000000', N'4000000', N'5000000', N'6000000', N'7000000', N'8000000', N'9000000', N'10000000')
--创建分区方案,所有分区在一个组里面
CREATE PARTITION SCHEME [bgPartitionSchema] AS PARTITION [bgPartitionFun] TO ([ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1], [ByIdGroup1])

如果分区方案中没有Next Used文件组，那么使用alter partition function的split语句（后面会提到）为分区函数新增分区时，会出现以下错误：

警告: 分区方案 'XXXX' 没有任何下次使用的文件组。分区方案未更改。

如果在创建分区方案的时候没有设置Next Used文件组也不用担心，可以使用ALTER PARTITION SCHEME语句给分区方案指定Next Used文件组，格式如下所示

ALTER PARTITION SCHEME <分区方案名称>
NEXT USED <文件组名称>

此外使用上面的ALTER PARTITION SCHEME语句指定Next Used文件组还有个作用，如果Next Used关键字后面指定的文件组名称不在当前分区方案中，那么该文件组会被加入到当前分区方案中，也就是说用ALTER PARTITION SCHEME语句指定Next Used文件组还可以用来给分区方案新增文件组，新增的文件组将被作为Next Used文件组来使用，这也是目前给分区方案新增文件组的唯一方法，否则只能将分区方案删除后重建。

删除语法：

--删除分区方案语法
drop partition scheme<分区方案名称>

--删除分区方案 bgPartitionSchema
drop partition scheme bgPartitionSchema1

只有没有分区表，或索引使用该分区方案时，才能对其删除。

3.分区表

创建语法：

--创建分区表语法
create table <表名> (
  <列定义>
)on<分区方案名>(分区列名)

--创建分区表
create table BigOrder (
   OrderId              int                  identity,
   orderNum             varchar(30)          not null,
   OrderStatus          int                  not null default 0,
   OrderPayStatus       int                  not null default 0,
   UserId               varchar(40)          not null,
   CreateDate           datetime             null default getdate(),
   Mark                 nvarchar(300)        null
)on bgPartitionSchema(OrderId)

如果在使用CREATE TABLE语句创建表时也创建主键或唯一索引，那么主键或唯一索引中也必须要包含分区列。例如下面的例子中主键聚集索引[PK_T_People_Partition]就包含了分区列[Age]:

CREATE TABLE [dbo].[T_People_Partition](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Age] [int] NOT NULL,
 CONSTRAINT [PK_T_People_Partition] PRIMARY KEY CLUSTERED 
(
    [Id] ASC,
    [Age] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) on [AgeIn10YearsSchema]([Age])
) on [AgeIn10YearsSchema]([Age])

如果不想将分区列放入到主键或唯一索引中，可以在使用CREATE TABLE语句创建表时先不创建主键或唯一索引，之后再创建主键或唯一索引，这样分区列就不是主键或唯一索引的必选列了，不过这里还是强烈建议将分区列加入到主键或唯一索引中，这样可以大大提升索引的查询性能（后面会讲到这一点）。

4.分区索引

创建语法：

--创建分区索引语法
create <索引分类> index <索引名称> 
on <表名>(列名)
on <分区方案名>(分区依据列名)

--创建分区索引
CREATE CLUSTERED INDEX [ClusteredIndex_on_bgPartitionSchema_635342971076448165] ON [dbo].[BigOrder] 
(
    [OrderId]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [bgPartitionSchema]([OrderId])

使用分区索引查询，可以避免多个cpu操作多个磁盘时产生的冲突。索引对于提高查询性能非常有效，因此，一般应该考虑为分区表建立索引，为分区表建立索引与为普通表建立索引的语法一直，但是，其行为与普通索引有所差异。默认情况下，分区表中创建的索引使用与分区表相同分区架构和分区列，这样，索引将于表对齐。将表与其索引对齐，可以使管理工作更容易进行，对于滑动窗口方案尤其如此。若要启动分区切换，表的所有索引都必须对齐。在创建索引时，也可以指定不同的分区方案（Schema）或单独的文件组（FileGroup）来存储索引，这样SQL Server 不会将索引与表对齐。

在已分区的表上创建索引（分区索引）时，应该注意以下事项： 

唯一索引

　　建立唯一索引（聚集或者非聚集）时，分区列必须出现在索引列中。此限制将使SQL Server只调查单个分区，并确保表中宠物的新键值。如果分区依据列不可能包含在唯一键中，则必须使用DML触发器，而不是强制实现唯一性。

非唯一索引

　　对非唯一的聚集索引进行分区时，如果未在聚集键中明确指定分区依据列，默认情况下SQL Server 将在聚集索引列中添加分区依据列。对非唯一的非聚集索引进行分区时，默认情况下SQL Server 将分区依据列添加为索引的包含性列，以确保索引与基表对齐，若果索引中已经存在分区依据列，SQL Server 将不会像索引中添加分区依据列。

分区表明细信息

这里的语法，我就不写啦，自己看语句分析吧。简单的很。。

1.查看分区依据列的指定值所在的分区

--查询分区依据列为10000014的数据在哪个分区上
select $partition.bgPartitionFun(2000000)  --返回值是2，表示此值存在第2个分区

2.查看分区表中，每个非空分区存在的行数

--查看分区表中，每个非空分区存在的行数
select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCount
from bigorder
group by  $partition.bgPartitionFun(orderid)

3.查看指定分区中的数据记录

---查看指定分区中的数据记录
select * from bigorder where $partition.bgPartitionFun(orderid)=2

结果：数据从1000001开始到200W结束

4.查看指定分区函数中的分区详细信息

我们创建一个SqlServer函数GetPartitionFunctionRange，将分区函数的名称传入这个函数，就可以得到该分区函数的所有分区信息

CREATE FUNCTION [dbo].[GetPartitionFunctionRange]
(    
     @partition_function_name nvarchar(50)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT PARTITION_RANGE_VALUES.*
    FROM SYS.PARTITION_RANGE_VALUES inner join sys.partition_functions on PARTITION_RANGE_VALUES.function_id=partition_functions.function_id 
    where partition_functions.name=@partition_function_name
)

使用GetPartitionFunctionRange函数查询分区函数AgeIn10Years的分区信息，查询结果的列Value显示了AgeIn10Years分区函数的所有分区值

select * from [dbo].[GetPartitionFunctionRange]('AgeIn10Years')

分区的拆分与合并以及数据移动

1.拆分分区

在分区函数中新增一个边界值，即可将一个分区变为2个。

--分区拆分
alter partition function bgPartitionFun()
split range(N'1500000')  --将第二个分区拆为2个分区

注意：如果分区函数已经指定了分区方案，那么指定的分区方案必须要包含Next Used文件组，否则拆分分区时会报错，这个已经在前面提到了。

2.合并分区

与拆分分区相反，去除一个边界值即可。

--合并分区
alter partition function bgPartitionFun()
merge range(N'1500000')  --将第二第三分区合并

merge语句会合并一个分区并将该分区中存在的所有值都合并到剩余的某个分区中，上面merge range (boundary_value) 语句括号中的boundary_value必须是一个现有边界值（上面的boundary_value就是1500000），已删除分区中的值将合并到该值中。如果最初保存 boundary_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。

3.分区中的数据移动

你或许会遇到这样的需求，将普通表数据复制到分区表中，或者将分区表中的数据复制到普通表中。

那么移动数据这两个表，则必须满足下面的要求。

字段数量相同，对应位置的字段相同
相同位置的字段要有相同的属性，相同的类型。
两个表在一个文件组中

1.创建表时指定文件组

--创建表
create table <表名> (
  <列定义>
)on <文件组名>

2.从分区表中复制数据到普通表

--将bigorder分区表中的第一分区数据复制到普通表中
alter table bigorder switch partition 1 to <普通表名>

3.从普通标中复制数据到分区表中

这里要注意的是要先将分区表中的索引删除，即便普通表中存在跟分区表中相同的索引。

--将普通表中的数据复制到bigorder分区表中的第一分区
alter table <普通表名> switch to bigorder partition 1

分区视图

分区视图是先建立带有字段约束的相同表,而约束不同,例如,第一个表的id约束为0--100W,第二表为101万到200万.....依次类推。

创建完一系列的表之后，用union all 连接起来创建一个视图，这个视图就形成啦分区视同。

很简单的，这里我主要是说分区表，就不说分区视图啦。。

查看数据库分区信息

SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber