19.3 SQL Server表分区详细版（转载）
SQL Server表分区详细版（转载）
表分区介绍
SQL Server分区介绍
在SQL Server中，数据库的所有表和索引都视为已分区表和索引，默认这些表和索引值包含一个分区；也就是说表或索引至少包含一个分区。SQL Server中数据是按水平方式分区，是多行数据映射到单个分区。已经分区的表或者索引，在执行查询或者更新时，将被看作为单个逻辑实体；简单说来利用分区将一个表数据分多个表来存储，对于大数据量的表，将表分成多块查询，若只查询某个分区数据将降低消耗提高效率。需要注意的是单个索引或者表的分区必须位于一个数据库中。在使用大量数据管理时，SQL Server使用分区可以快速访问数据子集，减少io提高效率。
同时不同分区可以存放在不同文件组里，文件组若能存放在不同逻辑磁盘上，则可以实现io的并发使用以提高效率。如下图所示：
创建表分区
未分区的表，相当于只有一个分区，只能存储在一个FileGroup中；对表进行分区后，每一个分区都存储在一个FileGroup，或分布式存储在不同的FileGroup中。对表进行分区的过程，实际上是将逻辑上完整的一个表，按照特定的字段拆分成多个分区，分散到相同或不同的FileGroup中，每一个部分叫做表的一个分区（Partition），一个分区实际上是一个独立的，内部的物理表。也就是说，分区表在逻辑上是一个表，而在物理上是多个完全独立的表。
分区（Partition）的特性是：
- 每一个Partition在FileGroup中都独立存储，分区之间是相互独立的
- 每一个parititon都属于唯一的表对象，
- 每一个Partition 都有唯一的ID，
- 每一个Partition都有一个编号（Partition Number），同一个表的分区编号是唯一的，从1开始递增；
当表分区后，加锁的粒度从表级别降低到分区级别，这使得对一个分区执行更新操作，同时不会影响另一个分区的读取操作。因此，分区可以降低并发查询系统产生死锁和阻塞的概率，提高数据操作的并发度。在创建表时，使用On 子句指定table存储的逻辑位置：
- ON filegroup | "default" ：表示逻辑存储位置是单一的FileGroup；
- ON partition_scheme_name ( partition_column_name ) ：表示逻辑存储位置是分区架构，按照partition_column将table拆分成多个partition，每一个partition都存储在一个指定的Filegroup中；
| CREATE TABLE schema_name . table_name | |
| ( <column_definition> ) | |
| [ ON { partition_scheme_name ( partition_column_name ) | filegroup | "default" } ] | |
| [ WITH ( <table_option> [ ,...n ] ) ] |
从存储空间来理解分区，Partition实际上是表的一部分逻辑存储空间。未分区表的逻辑存储位置是FileGroup，分区表的逻辑存储位置是Partition Scheme，但是，FileGroup指定一个特定的逻辑存储位置，而Partition Scheme是分布式的，能够将数据分布式存储到不同的FileGroup中去。
跟逻辑存储空间相对应的是物理存储空间，物理存储空间是由File指定的，FileGroup是File的集合，每一个File都属于唯一的FileGroup。将table的存储空间拆分到不同的FileGroup中，将table的物理存储空间分布到不同的File中，只不过，不再是某一个FileGroup。实际存储数据的文件仍然是File。
在SQL Server中，File Group和Partition Scheme统称为Data Sapce（数据空间），默认的Data Space是Primary，即主文件组。
第一步：构建文件组和文件
| --添加文件组 | |
| alter database testSplit add filegroup db_fg1 | |
| --添加文件到文件组 | |
| alter database testSplit add file | |
| (name=N'ById1',filename=N'J:\Work\数据库\data\ById1.ndf',size=5Mb,filegrowth=5mb) | |
| to filegroup db_fg1 |
第二步：创建分区函数
要先创建函数
分区函数的作用是提供分区字段的类型和分区的边界值，进而决定分区的数量
| CREATE PARTITION FUNCTION [pf_int](int) | |
| AS RANGE LEFT | |
| FOR VALUES (10, 20) |
分区函数pf_int 的含义是按照int类型分区，分区的边界值是10，20，left表示边界值属于左边界。两个边界值能够分成三个分区，别是(-infinite，10],（10，20],(20,+infinite)。
第三步：创建分区方案
再创建分区方案、应用函数
分区架构的作用是为Parition分配FileGroup，在逻辑上，Partition Scheme和FileGroup是等价的，都是数据存储的逻辑空间，只不过Partition Scheme指定的是多个FileGroup。
| CREATE PARTITION SCHEME [ps_int] | |
| AS PARTITION [pf_int] | |
| TO ([PRIMARY], [db_fg1], [db_fg1]) |
不管是在不同的FileGroup中，还是在相同的FileGroup中，分区都是独立存储的。
分区scheme的所有分区都存储到相同的文件组中：
| CREATE PARTITION SCHEME [ps_int] | |
| AS PARTITION [pf_int] | |
| ALL TO ([PRIMARY]) |
第四步：创建分区表
新建分区表，实际上是在创建Table时，使用on子句指定数据存储的逻辑位置是分区架构（Partition Scheme）
| create table dbo.dt_test | |
| ( | |
| ID int, | |
| code int | |
| ) | |
| on [ps_int] (id) |
查看分区编号
分区编号（Partition Number） 从1开始，从最左边的分区向右依次递增+1，边界值最小的分区编号是1，
例如，对于以下分区函数：
| CREATE PARTITION FUNCTION pf_int_Left (int) | |
| AS | |
| RANGE LEFT | |
| FOR VALUES (10,20); |
分区的边界值（Boundary Value）是10，20， 边界值属于左边界（Range Left），该分区函数 pf_int_Left 划分了三个分区（Partition），范围区间是：(-infinite,10], (10,20], (20,+infinite)，（小括号表示不包括边界值，中括号表示包括边界值），系统分配的分区编号分别是：1，2，3。用户可以通过使用$Partition函数 查看分区编号，调用语法格式是：
| $Partition.Partition_Function(Partition_Column_Value) |
例如，通过$Partition函数 查看分区列值为21时，该行数据所在的分区编号：
| select $Partition.pf_int_left(21) |
由于分区列值是21， 属于范围(20,+infinite)，因此分区编号是：3。
对现有表分区
在SQL Server中，普通表可以转化为分区表，而分区表不能转化为普通表，普通表转化成分区表的过程是不可逆的，将普通表转化为分区表的方法是：
在分区架构（Partition Scheme）上创建聚集索引，就是说，将聚集索引分区。
数据库中已有分区函数（partition function） 和分区架构（Partition scheme）：
| -- create parition function | |
| CREATE PARTITION FUNCTION pf_int_Left (int) | |
| AS RANGE LEFT | |
| FOR VALUES (10,20); | |
| --determine partition number | |
| select $Partition.pf_int_left(21) | |
| CREATE PARTITION SCHEME PS_int_Left | |
| AS PARTITION pf_int_Left | |
| TO ([primary], [primary], [primary]); |
如果在普通表上存在聚集索引，并且聚集索引列是分区列，那么重建聚集索引，就能使表转化成分区表。聚集索引的创建有两种方式：使用clustered 约束（primary key 或 unique约束）创建，使用 create clustered index 创建。
在分区方案（Partition Scheme）上，创建聚集索引
如果聚集索引是使用 create clustered index 创建的，并且聚集索引列就是分区列，使普通表转换成分区表的方法是：删除所有的 nonclustered index，在partition scheme上重建clustered index
1，表dbo.dt_partition的聚集索引是使用 create clustered index 创建的
| create table dbo.dt_partition | |
| ( | |
| ID int, | |
| Code int | |
| ) | |
| create clustered index cix_dt_partition_ID | |
| on dbo.dt_partition(ID) |
2，从系统表Partition中，查看该表的分区只有一个
| select * | |
| from sys.partitions p | |
| where p.object_id=object_id(N'dbo.dt_partition',N'U') |
3，使用partition scheme，重建表的聚集索引
| create clustered index cix_dt_partition_ID | |
| on dbo.dt_partition(ID) | |
| with(drop_existing=on) | |
| on PS_int_Left(ID) |
4，重建聚集索引之后，表的分区有三个
| select * | |
| from sys.partitions p | |
| where p.object_id=object_id(N'dbo.dt_partition',N'U') |
如果表的聚集索引是使用Primary key clustered来创建，并且primary key 就是分区列
在SQL Server中，不能修改约束，将普通表转换成分区表，有两种方式来实现
（1）第一种方式是：在删除clustered constraint 时，将数据移动到分区scheme上；
（2）第二种方式，删除clustered constraint，在分区scheme上重建clustered constraint。
1，在删除clustered 约束时，将数据移动到分区scheme上
使用 alter table drop constraint 命令，在删除聚集索引时，将数据移动到指定的Partition Scheme上，此时该表变成分区的堆表：
| ALTER TABLE schema_name . table_name | |
| DROP [ CONSTRAINT ] constraint_name | |
| [ WITH ( MOVE TO { partition_scheme_name(partition_column_name ) | filegroup | [default] } )] |
move to 选项的作用是将Table移动到新的Location中，如果新的location 是partition scheme，那么在删除clustered 约束时，SQL Server将表数据移动到分区架构中，这种操作和使用 create table on partition scheme创建分区表的作用相同。
| create table dbo.dt_partition_pk | |
| ( | |
| ID int not null constraint pk__dt_partition_ID primary key clustered , | |
| Code int not null | |
| ) | |
| alter table dbo.dt_partition_pk | |
| drop constraint pk__dt_partition_ID | |
| with( move to PS_int_Left(ID)) |
2，删除clustered 约束，在partition scheme上重建clustered 约束
| create table dbo.dt_partition_pk | |
| ( | |
| ID int not null constraint pk__dt_partition_ID primary key clustered , | |
| Code int not null | |
| ) | |
| alter table dbo.dt_partition_pk | |
| drop constraint pk__dt_partition_ID | |
| alter table dbo.dt_partition_pk | |
| add constraint pk__dt_partition_ID primary key clustered(ID) | |
| on PS_int_Left(ID); |
将堆表转换成分区表
使堆表转换成分区，只需要在堆表上创建一个分区的clustered index
| create table dbo.dt_partition_heap | |
| ( | |
| ID int not null, | |
| Code int not null | |
| ) | |
| create clustered index cix_partition_heap_ID | |
| on dbo.dt_partition_heap(ID) | |
| on PS_int_Left(ID) |
普通表=>分区表，不可逆
普通表转化成分区表的过程是不可逆的，普通表能够转化成分区表，而分区表不能转化成普通表。
普通表存储的Location是FileGroup，分区表存储的Location是Partition Scheme，在SQL Server中，存储表数据的Location叫做Data Space。
通过在Partition Scheme上创建Clustered Index ，能够将已经存在的普通表转化成partition table，但是，将Clustered index删除，表仍然是分区表，转化过程（将普通表转换成分区表）是不可逆的；
一个Partition Table 是不能转化成普通表的，即使通过合并分区，使Partiton Table 只存在一个Partition，这个表的仍然是Partition Table，这个Table的Data Space 是Partition Scheme，而不会转化成File Group。
从 sys.data_spaces 中查看Data Space ，共有两种类型，分别是FG 和 PS。
FG是File Group，意味着数据表的数据存储在File Group分配的存储空间，一个Table 只能存在于一个FileGroup中。PS 是Partition Scheme，意味着将数据分布式存储在不同的File Groups中，存储数据的File Group是根据Partition column值的范围来分配的。对于分区表，SQL Server从指定的File Group分配存储空间，虽然一个Table只能指定一个Partition Scheme，但是其数据却分布在多个File Groups中，这些File Groups由Partition Scheme指定，可以相同，也可以不同。
查看Table的Data Space，通过索引的data_space_id 字段来查看各个索引（聚集索引是表本身）数据的存储空间：
| select o.name as TableName,o.type_desc, | |
| i.name as IndexName, | |
| i.index_id,i.type_desc,i.data_space_id, | |
| ds.name as DataSpaceName,ds.type_desc | |
| from sys.indexes i | |
| inner join sys.objects o | |
| on o.object_id=i.object_id | |
| inner join sys.data_spaces ds | |
| on i.data_space_id=ds.data_space_id | |
| where i.object_id=object_id(N'[dbo].[dt_test_partition]') | |
| and i.index_id=0 |
在分区之前，查看Data_space是Name是 Primary File Group
在分区之后，查看Table的 Data Space 是ps_int Partition Scheme
目前无法将Table的Data Space 转化成FG
分区切换
在SQL Server中，对超级大表做数据归档，使用select和delete命令是十分耗费CPU时间和Disk空间的；
SQL Server必须记录相应数量的事务日志，而使用switch操作归档分区表的老数据，十分高效，switch操作不会移动数据，只是做元数据的置换；
因此，执行分区切换操作的时间是非常短暂的，几乎是瞬间完成，但是，在做分区切换时，源表和靶表必须满足一定的条件：
- 表的结构相同：列的数据类型，可空性（nullability）相同；
- 索引结构必须相同：索引键的结构，聚集性，唯一性，列的可空性必须相同；
- 主键约束：如果源表存在主键约束，那么靶表必须创建等价的主键约束；
- 唯一约束：唯一约束可以使用唯一索引来实现；
- 索引键的结构：索引键的顺序，包含列，唯一性，聚集性都必须相同；
- 存储的数据空间（data space）相同：源表和靶表必须创建在相同的FileGroup或Partition Scheme上；
分区切换是将源表中的一个分区，切换到靶表（target_table）中，靶表可以是分区表，也可以不是分区表，switch操作的语法是：
| ALTER TABLE schema_name . table_name | |
| SWITCH [ PARTITION source_partition_number_expression ] | |
| TO target_table [ PARTITION target_partition_number_expression ] |
表分区相关信息查询
全面查看表分区行、索引、文件、界限
| 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 |
分区函数、分区架构
| --查看分区函数 | |
| select * from sys.partition_functions | |
| --查看分区架构 | |
| select * from sys.partition_schemes |
查看分区表每个分区有多少行
| 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('crm.EmailLog') | |
| and i.index_id in (0, 1) | |
| order by p.partition_number |
| SELECT DISTINCT | |
| t.name AS TableName | |
| ,ps.name AS PSName | |
| ,fg.name AS FileGroupName | |
| ,f.name AS [FileName] | |
| ,f.physical_name AS [FilePhysicalName] | |
| ,dds.destination_id AS PartitionNumber --去除注释即可显示文件的分区数 | |
| FROM sys.tables AS t | |
| INNER JOIN sys.indexes AS i | |
| ON (t.object_id = i.object_id) | |
| INNER JOIN sys.partition_schemes AS ps | |
| ON (i.data_space_id = ps.data_space_id) | |
| INNER JOIN sys.destination_data_spaces AS dds | |
| ON (ps.data_space_id = dds.partition_scheme_id) | |
| INNER JOIN sys.filegroups AS fg | |
| ON dds.data_space_id = fg.data_space_id | |
| INNER JOIN sys.database_files f | |
| ON f.data_space_id = fg.data_space_id |
查看分区依据列的指定值所在的分区
| --查询分区依据列为10000014的数据在哪个分区上 | |
| select $partition.bgPartitionFun(2000000) --返回值是2，表示此值存在第2个分区 |
查看分区表中，每个非空分区存在的行数
| --查看分区表中，每个非空分区存在的行数 | |
| select $partition.bgPartitionFun(orderid) as partitionNum,count(*) as recordCount | |
| from bigorder | |
| group by $partition.bgPartitionFun(orderid) |
查看指定分区中的数据记录
| ---查看指定分区中的数据记录 | |
| select * from bigorder where $partition.bgPartitionFun(orderid)=2 |
结果：数据从1000001开始到200W结束
拆分/合并分区/数据移动
---增加新的分区你可以将的分区加到新的文件组里面(也可以是使用过的)
| alter partition scheme [bgPartitionSchema] -----先为分区方案分配文件组 | |
| next used [primary] |
拆分/增加分区
在分区函数中新增一个边界值，即可将一个分区变为2个。放到最前或者最后来拆分就是新增分区
| --分区拆分 | |
| alter partition function bgPartitionFun() | |
| split range(N'1500000') --将第二个分区拆为2个分区 |
注意：如果分区函数已经指定了分区方案，则分区数需要和分区方案中指定的文件组个数保持对应一致。
合并分区
与拆分分区相反，去除一个边界值即可。
| --合并分区 | |
| alter partition function bgPartitionFun() | |
| merge range(N'1500000') --将第二第三分区合并 |
1、ALTER PARTITION FUNCTION 意思是修改分区函数
2、partfunSale()为分区函数名
3、MERGE RANGE意思是合并界限。事实上，合并界限和删除分界值是一个意思。
分区中的数据移动
你或许会遇到这样的需求，将普通表数据复制到分区表中，或者将分区表中的数据复制到普通表中。
那么移动数据这两个表，则必须满足下面的要求。
- 字段数量相同，对应位置的字段相同
- 相同位置的字段要有相同的属性，相同的类型。
- 两个表在一个文件组中
1.创建表时指定文件组
| --创建表 | |
| create table <表名> ( | |
| <列定义> | |
| )on <文件组名> |
2.从分区表中复制数据到普通表
| --将bigorder分区表中的第一分区数据复制到普通表中 | |
| alter table bigorder switch partition 1 to <普通表名> |
3.从普通标中复制数据到分区表中
这里要注意的是要先将分区表中的索引删除，即便普通表中存在跟分区表中相同的索引。
| --将普通表中的数据复制到bigorder分区表中的第一分区 | |
| alter table <普通表名> switch to bigorder partition 1 |
参考文档
文章转载自sql server表分区【最佳实践】:https://www.cnblogs.com/gered/p/14448728.html#_label1_3
浙公网安备 33010602011771号