SQL Server分區詳解

標籤：des   style   blog   http   io   ar   使用   sp   for   

一、SQL Server分區介紹

在SQL Server中，資料庫的所有表和索引都視為資料分割資料表和索引，預設這些表和索引值包含一個分區；也就是說表或索引至少包含一個分區。SQL Server中資料是按水平方式分區，是多行資料對應到單個分區。已經分區的表或者索引，在執行查詢或者更新時，將被看作為單個邏輯實體，簡單說來利用分區將一個表資料分多個表來儲存，對於大資料量的表，將表分成多塊查詢，若只查詢某個分區資料將降低消耗提高效率。需要注意的是單個索引或者表的分區必須位於一個資料庫中。

在使用大量資料管理時，SQL Server使用分區可以快速存取資料子集，減少io提高效率。同時不同分區可以存放在不同檔案組裡，檔案組若能存放在不同邏輯磁碟上，則可以實現io的並發使用以提高效率。如所示：

二、SQL Server分區建立

雖然分區有這樣的好處，卻不能隨意使用；且不說分區管理的繁瑣，只是跨分區帶來的負面影響就需要我們好好分析是否有必要使用分區。一般分區建立的業務特點：用於統計、曆史資料少使用、資料自增長、可能資料冗餘大、資料量龐大插入量大。

在確定是否合適使用分區前，需瞭解分區是如何建立的，分區的建立包括：

? 建立分區函數

? 建立檔案和檔案組

? 建立分區架構

? 建立分區表或者分區索引

如所示：分區函數定義了分區的具體方式，分區架構使用分區函數和檔案組，確定資料分割配置，表或索引就使用分區架構來實現分區。他們之間是使用關係，一對多的關係。

1 建立分區函數

分區函數定義如何根據某些列的值將表或索引的行映射到指定分區。分區函數制定了分區的方式。

用作索引列時有效所有資料類型都可以用作分割資料行，timestamp 除外。無法指定 ntext、text、image、xml、varchar(max)、nvarchar(max) 或 varbinary(max) 資料類型。

建立分區函數文法：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]

1.1 左/右界限RANGE [ LEFT | RIGHT ]

指定左右既是為了確定邊界值處於左側還是右側。如下例子按時間RANG RIGHT分區:

/*建立分區函數*/

USE [PartionTest];

CREATE PARTITION FUNCTION [pf_PartionTest01] (datetime) AS RANGE right FOR VALUES (‘2014-12-09‘, ‘2014-12-10‘

/*分區函數查詢*/

SELECT

pf.name 分區函數名稱

,CASE WHEN boundary_value_on_right=1 THEN ‘RIGHT‘ ELSE ‘LEFT‘ END 分區界限方式

,value 分區界限值

FROM sys.partition_functions pf

LEFT JOIN sys.partition_range_values prv ON prv.function_id = pf.function_id

ORDER BY boundary_id

如所示RANGE RIGHT，則界限值屬於右邊。

注意：

1、 業務上多數為使用Range Right ，畢竟右為後期增長的資料，將邊界保留在最新分區有助於界限查詢；比如按每天分區的邏輯是將當天的資料存在當天的分區內，假如當天分區為2014-12-08 00:00.000， Range Right 將2014-12-08 00:00.000的資料歸於2014-12-08當天，RANGE LEFT則只能將此界限時間歸於2014-12-07。與邏輯存在一定差異。

2、 既然有分區界限問題，在合并分區的時候，指定分區是向左還是向右合并是一個值得討論的問題，具體請查看：合并分區（Merge）

2 建立分區架構

分區架構把分區函數指定的分區映射到檔案組

基本文法：CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

2.1 建立特定檔案和檔案組的必要性

在建立分區架構之前若有必要需要建立特定的檔案和檔案組，建立不同檔案組若存放在不同邏輯磁碟可以提高io並發能力，同時不同檔案可以提高容災的能力，在某個檔案發生順壞，其他檔案可以繼續使用。分開檔案儲存體，也可實現不同分區獨立備份，提高了資料恢複速率。

以下樣本建立一個分區函數，將表或索引分為四個分區。 然後建立一個資料分割配置，在其中指定擁有這四個分區中每一個分區的檔案組。 此樣本假定資料庫中已經存在檔案組。

依據已經建立分區函數【pf_PartionTest01】和預設已有檔案組，建立以下分區架構

/*建立分區架構*/

USE [PartionTest];

CREATE PARTITION SCHEME [pc_PartionTest01] AS PARTITION [pf_PartionTest01] TO (‘Primary‘, ‘PartionTest201412‘, ‘PartionTest201412‘)

/*分區架構查詢*/

SELECT

ps.name partition_scheme,

ds.name filegroup,

pf.name partition_function,

pf.type_desc+‘:‘+case when pf.boundary_value_on_right=0 then ‘Left‘ else ‘Rigth‘ end function_type

FROM sys.partition_schemes ps

JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id

JOIN sys.data_spaces ds ON dds.data_space_id=ds.data_space_id

JOIN sys.partition_functions pf ON ps.function_id=pf.function_id

結果如所示：

分區指定檔案組要比分區劃分邊界多一個，分區架構指定了具體分區資料存放在哪個檔案組上。如所示：

3 建立分區表

如所示，只要制定分區架構和分割資料行即可.

4 建立分區索引

在瞭解索引分割區之前，需先瞭解下分區索引與表對齊的概念，若分區與表使用相同的分區架構和相同分割資料行，則說明分區與表的分區方式相同，我們稱為對齊，反之則不對齊。對於已分區的與表對齊的分區索引，在排序的時候，將一次性在記憶體或者tempdb中產生排序表；若是不與分區表對齊，將在每個分區同時產生排序表，因此所需記憶體空間會更多。詳情請見不對齊與對齊索引分割區對效能的影響

以下測試在已分區的表上關於分區索引的區別。

--建立測試表

CREATE TABLE PartionTest01(logid int identity(1,1),orderid int,salesDate datetime) ON pc_PartionTest01([salesDate])--指定資料分割配置
--查詢分區情況

SELECT Object_name(p.object_id) AS [object_name],

id.name AS index_name,

ps.name partition_scheme,

ds.name filegroup,

pf.name partition_function,

pf.type_desc+‘:‘+case when pf.boundary_value_on_right=0 then ‘Left‘ else ‘Rigth‘ end function_type,

p.partition_number,

Isnull(prv.VALUE,‘‘) AS boundy_value,

p.rows

FROM sys.indexes id

JOIN sys.partition_schemes ps ON ps.data_space_id = id.data_space_id

JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id

JOIN sys.data_spaces ds ON ds.data_space_id = dds.data_space_id

JOIN sys.partitions p ON p.object_id = id.object_id AND p.index_id = id.index_id AND dds.destination_id = p.partition_number

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id

LEFT JOIN sys.partition_range_values prv ON prv.function_id = pf.function_id AND prv.boundary_id = p.partition_number - pf.boundary_value_on_right

WHERE Object_name(id.object_id) = ‘PartionTest01‘

4.1 對叢集索引進行分區

測試一：對於非唯一叢集索引分區時，若未在聚集鍵明確指定分區列，預設將在叢集索引鍵列表中添加分割資料行

測試二：對於唯一叢集索引分區時，必須添加分割資料行，如下報錯。添加分割資料行之後建立成功，只有添加了分割資料行才能在單個分區保證唯一。

4.2 對非叢集索引進行分區

測試一：非唯一非叢集索引，自動包含分割資料行為鍵列。

測試二：唯一非聚集，仍然需要指定分割資料行。

測試三：指定包含分割資料行，可成功

4.3 結論

1、 建立唯一索引都需要明確指定分割資料行，以確保表中不存在重複的索引值。

2、 建立非唯一索引若未明確指定分割資料行，則會自動將分割資料行指定為索引鍵列

3、 建立包含非唯一非叢集索引，將不會再添加分割資料行為索引鍵列。

三、SQL Server分區管理1 拆分分區（SPLIT）

拆分分區，就是再已有分區上添加一個新分區。如所示，將分區03拆分成03和04分區，拆分方式先鎖定舊03分區的所有資料，後將舊03分區相關資料移轉到分區04，最後刪除舊03上的對應分區資料；這種操作大量消耗io，造成的io日誌讀寫是轉移資料的四倍。

因此在管理分區上，一般提前添加分區。例如當前03分區沒有資料的情況下，我們完成04分區的添加。

添加分區要提前指定下一個使用檔案組。如下語句所示

--添加分區

USE [PartionTest]

ALTER PARTITION SCHEME [pc_PartionTest01] NEXT USED [PartionTest201412]

USE [PartionTest];

ALTER PARTITION FUNCTION [pf_PartionTest01]() SPLIT RANGE(‘2014-12-11‘)

表示分區添加成功：

2 合并分區（Merge）

分區資料的遷移後，需要分區合并，也就是將無用分區刪除，分區合并同拆分一樣，會造成大量的io消耗；因此通常會將此分區資料切分出去，分區無資料則可以合并相關分區。

由於合并是將兩個分區合并，因此存在是將需要合并的分區界限點往左合并還是往右合并呢？這取決於Range 方式，若Range LEFT 則 往右合并，RANG RIGHT 則往左合并。

通過以上拆分分區得出的分區4，現將分區4合并，有餘Range right分區方式，因此分區4是往左合并，將合并到分區3。以下執行結果可以證明此觀點。

USE [PartionTest];

ALTER PARTITION FUNCTION [pf_PartionTest01]() Merge RANGE(‘2014-12-11‘)

3 切換分區（SWITCH）

將資料分區可以快速有效管理資料子集。可以使用ALTER TABLE …SWITCH PARTITION ..語句高效快速的轉移資料子集。在分區移動時，只是修改相關的中繼資料，並未移動資料。因此切換速度非常快。

切換分區需要符合以下要求：

1、原表與目標表結構必須相同，且必須在同一個檔案組中。

2、目標表或者分區必須是空的。

3、若源表存在聚集分區索引，則要求目標表也需要有同樣的聚集分區索引。

4、源表的所有索引必須與源表分區對齊。

5、若目標表存在索引，約束，外鍵，要求源表必須與目標表相同結構的索引，約束，外鍵。

6、目標表和源表都不能存在複製分發。

7、分割資料行是否可為空白的約束必須相同。

一般切換分區可以做如下處理：

1、 將現有表作為分區分配到現有的資料分割資料表。

ALTER TABLE [dbname].[dbo].[tablename] SWITCH TO [dbname].[dbo].[staging_tablename] PARTITION source_partition_number_expression

2、 將分區從一個資料分割資料表切換到另外一個資料分割資料表中。

ALTER TABLE [dbname].[dbo].[tablename] SWITCH PARTITION source_partition_number_expression TO [dbname].[dbo].[staging_tablename] PARTITION source_partition_number_expression

3、 將一個分區切換到現有表中。

ALTER TABLE [dbname].[dbo].[tablename] SWITCH PARTITION source_partition_number_expression TO [dbname].[dbo].[staging_tablename]

4 $PARTITION

為任何指定的分區函數返回分區號，一組分區列值將映射到該分區號中。

[ database_name. ] $PARTITION.partition_function_name(expression)

如下例子所示：

/*返回某個值屬於某個分區*/

SELECT $PARTITION.pf_PartionTest01(‘2014-12-26 00:00:00.000‘)

/*查詢某個分區的所有值*/

select * from [dbo].[PartionTest01]

where $PARTITION.pf_PartionTest01(salesDate)=2

5 不對齊與對齊索引分割區對效能的影響

對於已分區的與表對齊的分區索引，在排序的時候，將一次性在記憶體或者tempdb中產生排序表；若是不與分區表對齊，將在每個分區同時產生排序表，因此所需記憶體空間會更多。

分區數越多，所需的記憶體越多。每個分區的每個排序表的最小大小為 40 頁，每頁 8 KB。例如，具有 100 個分區的非對齊資料分割索引需要足夠的記憶體才能同時連續地對 4,000 (40 * 100) 頁進行排序。如果有這麼多的可用記憶體，產生操作將成功，但效能可能會降低。如果沒有這麼多可用記憶體，產生操作將失敗。而具有 100 個分區的對齊資料分割索引只需要具有對 40 頁進行排序的記憶體就足夠了，因為不會同時執行排序。

無論是對齊索引還是非對齊索引，如果 SQL Server 對多處理器電腦上的產生操作應用了並行度，需要的記憶體可能會更多。這是因為並行度越高，需要的記憶體就越多。例如，如果 SQL Server 將並行度設定為 4，那麼具有 100 個分區的非對齊資料分割索引將需要使四個處理器同時分別對 4,000 頁（即，共 16,000 頁）進行排序的足夠記憶體。如果資料分割索引是對齊的，需要的記憶體將減少，只要夠四個處理器分別對 40 頁（共 160 頁，即 4 * 40）進行排序就行了。

SQL Server分區詳解