SQL Server 分區表

標籤：str   adb   c89   行合并   基本   9.png   中文   html   設定   

分區表簡介

　　分區表是SQL Server2005新引入的概念，這個特性在邏輯上將一個表在物理上分為多個部分。(即它允許將一個表格儲存體在不同的物理磁碟裡)。在SQL Server2005之前，分區表實際上是分布式視圖，也就是多個表做union操作。

　　分區表在邏輯上是一個表，而物理上是多個表。在使用者的角度，分區表和普通表是一樣的，使用者角度感覺不出來。　　　　

　　

　　而在SQL Server2005之前，由於沒有分區的概念，所謂的分區僅僅是分布式視圖:

　　　

對錶分區的理由

　　表分區這個特性，只有SQL Server企業版或SQL Server開發版才有，理解表分區的概念之前，還得先理解SQL Server中檔案和檔案組的概念。這篇文章是解釋檔案和檔案組的。http://www.cnblogs.com/kissdodog/p/3156166.html

　　表分區主要用於：

• 提供效能：這個是大多人數分區的目的，把一個表分部到不同的硬碟或其他儲存介質中，會大大提升查詢速度。
• 提高穩定性：當一個分區出了問題，不會影響其他分區，僅僅是當前壞的分區不可用。
• 便於管理：把一個大表分成若干個小表，則備份和恢複的時候不再需要備份整個表，可以單獨備份分區。
• 存檔：將一些不太常用的資料，單獨存放。如：將1年前的資料記錄分到一個專門的存檔伺服器存放。

分區表的操作步驟

　　分區表分為三個步驟：

1. 1. 定義分區函數
   2. 定義分區構架
   3. 定義分區表

　　

　　分區函數，分區構架和分區表的關係如下：分區表依賴於分區構架，分區構架又依賴分區函數。因此，定義分區表的順序基本上是定義分區函數->定義分區構架->定義分區表。
　　實際操作，先定義一張需要分區的表：

 　　

 　　我們以SalesDate列作為分區列。

　第一步，定義分區函數：

　　分區函數用於判斷一行資料屬於哪個分區，通過分區函數中設定邊界值來使得根據行中特定列的值來確定其分區。如上面的分區表，可以通過設定SalesDate的值來判定其不同的分區，加入我們定義了SalesDate的兩個邊界值進行分區，則會產生三個分區，現在設定兩個邊界值分別為2004-01-01和2007-01-01，則上面的表就可以根據這兩個邊界值分出三個分區。

　　(CareySon大牛給的圖，真的放方便理解。)

　　定義分區函數的文法如下：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )AS RANGE [ LEFT | RIGHT ] FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ]

　　在上面定義分區函數的原型文法中，我們看到其中並沒有涉及到具體的表，因為分區函數並不和具體的表綁定。另外原型中還可以看到Range left和right。這個參數決定臨界值(也就是剛好等於2004-01-01或2007-01-01的這些與分界值相等的值)應該歸於左邊還是右邊。

　　

　　建立分區函數：

　　--建立分區函數　　CREATE PARTITION FUNCTION fnPartition(DATE)　　AS RANGE RIGHT　　FOR VALUES(‘2004-01-01‘,‘2007-01-01‘)　　--查看分區表是否建立成功　　SELECT * FROM sys.partition_functions

　　上述查詢語句顯示結果如下：

　　

　　通過系統檢視表，可以看見這個分區函數已經建立成功。

　第二步，定義分區構架

　　定義完分區函數僅僅知道了根據列的值將資料分配到不同的分區。而每個分區的儲存方式，則需要分區構架來定義。

　　分區構架文法原型：

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

　　從原型來看，分區構架僅僅是依賴分區函數。分區構架負責分配每個區屬於哪個檔案組，而分區函數是決定哪條資料屬於哪個分區。

　　

　　基於之前建立的分區函數,建立分區構架:

　　--基於之前的分區函數建立分區構架schema　　CREATE PARTITION SCHEME SchemaForParirion　　AS PARTITION fnPartition    --這個是之前建立的分區函數　　TO(FileGroup1,[primary],FileGroup1)    --FileGroup1是自己添加的檔案組，因為有兩個分界值，3個分區，所以要指定3個檔案組，也可以使用ALL所謂的分區指向一個檔案組　　--查看已建立的分區構架　　SELECT * FROM sys.partition_schemes

　　以上SELECT語句輸出結果如下：

　　

　　留意到分區構架已成功建立。

　第三步：定義分區表

　　有了分區函數與分區構架，下面就可以建立分區表了，表在建立的時候就要決定是否是分區表了。雖然在大部分情況下，都是在發現表太大時，才想到要分區。但是分區表只能夠在建立的時候指定為分區表。

　　CREATE TABLE OrderRecords　　(    　　Id int,    　　OrderId int,    　　SalesDate Date 　　)　　ON SchemaForParirion(SalesDate)    --SchemaForPartition是剛剛定義的分區架構，括弧內為指定的分區列

　　然後手工向資料庫裡面添加3條資料：

　　

　　然後執行查詢：

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 rowsfrom sys.indexes i join sys.partition_schemes ps on i.data_space_id = ps.data_space_id join sys.destination_data_spaces ddson 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_numberand 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_idand v.boundary_id = p.partition_number - pf.boundary_value_on_right WHERE i.object_id = object_id(‘OrderRecords‘)    --此處是表名and i.index_id in (0, 1) order by p.partition_number

　　可以看到，分區起作用了：

　　

分區表的分割

　　分區表的分割，相當於建立一個分區，將原有的分區需要分割的內容插入新的分區，然後刪除老的分區的內容。
　　新加入多一個分割點：2009-01-01。如所示：

 　　　

　　對於的操作，如果分割時，被分割的分區3內有內容需要分割到分區4，則這些資料需要被複製到分區4，並刪除分區3上對應的資料。

　　這種操作非常非常消耗IO，並且在分割的過程中鎖定分區3內的內容，造成分區3的內容暫時不可用。而且，這個操作產生的日誌內容將會是被轉移資料的4倍。　

　　因此，最好在建表的時候，就要考慮到以後的分割點，比如預判到2014-01-01,2016-01-01。

　　分割現有的分區需要兩個步驟：

 

1. 首先要告訴SQL Server建立立的分區放到哪個檔案組
2. 建立新的分割點。

 

　　加一條資料，致使原表如下：

　　

　　執行那個長查詢，顯示如下：

　　

　　現在，可以執行分割操作了：

　　--分割出來的分區資料存在在哪個檔案組　　ALTER PARTITION SCHEME SchemaForParirion NEXT USED ‘PRIMARY‘　　--添加分割點　　ALTER PARTITION FUNCTION fnPartition()　　SPLIT RANGE(‘2009-01-01‘)

　　執行完之後，再看結果如下：

　　

分區表的合并

　　分區的合并可以旱作是分區分割的逆操作。分區的合并需要提供分割點，並且這個分割點必須在現有的分割表中已經存在，否則進行合并時就會報錯。

　　例如，對以上例子，根據2009-01-01來進行合并：

　　

 　　合并分區操作：

　　--提供分割點，合并分區　　ALTER PARTITION FUNCTION fnPartition()　　MERGE RANGE(‘2009-01-01‘)

　　再來看分區資訊:

 　　

　　在這裡應該注意到一個問題，假設已經合并了分區，那麼合并之後，檔案是存在分區3的檔案組呢，還是分區4的檔案組呢？這個取決於我們剛開始時定義的分區函數是left還是right。如果定義的是left，則左邊的分區3合并到分區4。如果是right，則右邊的分區4合并到分區3.

 　　

　　最後附上一句：CareySon大牛，我愛你。

　　本文學習自：http://www.cnblogs.com/CareySon/archive/2011/12/30/2307766.html

SQL Server 分區表