SQL Server分區詳解Partition

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

一、SQL Server分區介紹

      在SQL Server中，資料庫的所有表和索引都視為資料分割資料表和索引，預設這些表和索引值包含一個分區；也就是說表或索引至少包含一個分區。

SQL Server中資料是按水平方式分區，是多行資料對應到單個分區。已經分區的表或者索引，在執行查詢或者更新時，將被看作為單個邏輯實體；

簡單說來利用分區將一個表資料分多個表來儲存，對於大資料量的表，將表分成多塊查詢，若只查詢某個分區資料將降低消耗提高效率。需要注意的是單個索引或者表的分區必須位於一個資料庫中。

在使用大量資料管理時，SQL Server使用分區可以快速存取資料子集，減少io提高效率。

      同時不同分區可以存放在不同檔案組裡，檔案組若能存放在不同邏輯磁碟上，則可以實現io的並發使用以提高效率。如所示：

二、SQL Server分區建立

雖然分區有很多好處，卻不能隨意使用；且不說分區管理的繁瑣，只是跨分區帶來的負面影響就需要我們好好分析是否有必要使用分區。

一般分區建立的業務特點：用於統計、曆史資料少使用、資料自增長、可能資料冗餘大、資料量龐大插入量大。

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

1、建立分區函數

2、建立檔案和檔案組

3、建立分區架構

4、建立分區表或者分區索引

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

他們之間是使用關係，一對多的關係。

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 ]

指定左右既是為了確定邊界值處於左側還是右側。如所示RANGE RIGHT，則界限值屬於右邊。

/*建立分區函數*/USE [PartionTest];CREATE PARTITION FUNCTION [pf_PartionTest01] (datetime) AS RANGE right FOR VALUES (‘2014-12-09‘, ‘2014-12-10‘/*分區函數查詢*/SELECTpf.name 分區函數名稱,CASE WHEN boundary_value_on_right=1 THEN ‘RIGHT‘ ELSE ‘LEFT‘ END 分區界限方式,value 分區界限值FROM sys.partition_functions pfLEFT JOIN sys.partition_range_values prv ON prv.function_id = pf.function_idORDER BY boundary_id

查詢結果如下：

 

注意：

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、既然有分區界限問題，在合并分區的時候，指定分區是向左還是向右合并?

2、建立分區架構

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

CREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )[ ; ]

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

在建立分區架構之前若有必要需要建立特定的檔案和檔案組：

1、建立不同檔案組若存放在不同邏輯磁碟可以提高io並發能力；

2、同時不同檔案可以提高容災的能力，在某個檔案發生順壞，其他檔案可以繼續使用。

3、分開檔案儲存體，也可實現不同分區獨立備份，提高了資料恢複速率。

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

/*建立分區架構*/USE [PartionTest];CREATE PARTITION SCHEME [pc_PartionTest01] AS PARTITION [pf_PartionTest01] TO (‘Primary‘, ‘PartionTest201412‘, ‘PartionTest201412‘)
/*分區架構查詢*/SELECTps.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_typeFROM sys.partition_schemes psJOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_idJOIN sys.data_spaces ds ON dds.data_space_id=ds.data_space_idJOIN sys.partition_functions pf ON ps.function_id=pf.function_id

結果如所示：

3、建立分區表

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

4、建立索引分割區三、SQL Server分區管理  

SQL Server分區詳解Partition