19.3 SQL Server表分區詳細版（轉載）

SQL Server表分區詳細版（轉載）

SQL Server表分區詳細版（轉載）

表分區介紹

SQL Server分區介紹

在SQL Server中，數據庫的所有表和索引都視為已分區表和索引，默認這些表和索引值包含一個分區；也就是說表或索引至少包含一個分區。SQL Server中數據是按水平方式分區，是多行數據映射到單個分區。已經分區的表或者索引，在執行查詢或者更新時，將被看作為單個邏輯實體；簡單說來利用分區將一個表數據分多個表來存儲，對于大數據量的表，將表分成多塊查詢，若只查詢某個分區數據將降低消耗提高效率。需要注意的是單個索引或者表的分區必須位于一個數據庫中。在使用大量數據管理時，SQL Server使用分區可以快速訪問數據子集，減少io提高效率。

同時不同分區可以存放在不同文件組里，文件組若能存放在不同邏輯磁盤上，則可以實現io的并發使用以提高效率。如下圖所示：

Alt text

創建表分區

未分區的表，相當于只有一個分區，只能存儲在一個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')

Alt text

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')

Alt text

如果表的聚集索引是使用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

Alt text

在分區之后，查看Table的 Data Space 是ps_int Partition Scheme

Alt text

目前無法將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)

Alt text

查看指定分區中的數據記錄

---查看指定分區中的數據記錄
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表分區【最佳實踐】:http://www.rzrgm.cn/gered/p/14448728.html#_label1_3

posted @ 2023-02-06 15:16 平元兄閱讀(2048) 評論(0) 收藏舉報

刷新頁面返回頂部

Michael Shen

平元兄

19.3 SQL Server表分區詳細版（轉載）

SQL Server表分區詳細版（轉載）

表分區介紹

SQL Server分區介紹

創建表分區

第一步：構建文件組和文件

第二步：創建分區函數

第三步：創建分區方案

第四步：創建分區表

查看分區編號

對現有表分區

在分區方案（Partition Scheme）上，創建聚集索引

如果表的聚集索引是使用Primary key clustered來創建，并且primary key 就是分區列

將堆表轉換成分區表

普通表=>分區表，不可逆

分區切換

表分區相關信息查詢

全面查看表分區行、索引、文件、界限

分區函數、分區架構

查看分區表每個分區有多少行

查看分區依據列的指定值所在的分區

查看分區表中，每個非空分區存在的行數

查看指定分區中的數據記錄

拆分/合并分區/數據移動

拆分/增加分區

合并分區

分區中的數據移動

參考文檔

公告