SQL Server中分割區表的用法

2022-05-21 13:02:19

一、分割區表簡介

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

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

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

二、對錶分割區的理由

表分割區這個特性，只有SQL Server企業版或SQL Server開發版才有，理解表分割區的概念之前，還得先理解SQL Server中檔案和檔案組的概念。這篇文章是解釋檔案和檔案組的。https://www.jb51.net/article/248808.htm

表分割區主要用於：

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

三、分割區表的操作步驟

分割區表分為三個步驟：

定義分割區函數

定義分割區構架

定義分割區表

分割區函數，分割區構架和分割區表的關係如下：分割區表依賴於分割區構架，分割區構架又依賴分割區函數。

因此，定義分割區表的順序基本上是定義分割區函數->定義分割區構架->定義分割區表。
實際操作，先定義一張需要分割區的表：

我們以SalesDate列作為分割區列。

第一步、定義分割區函數：

分割區函數用於判斷一行資料屬於哪個分割區，通過分割區函數中設定邊界值來使得根據行中特定列的值來確定其分割區。

如上面的分割區表，可以通過設定SalesDate的值來判定其不同的分割區，假如我們定義了SalesDate的兩個邊界值進行分割區，則會生成三個分割區，現在設定兩個邊界值分別為2004-01-01和2007-01-01，則上面的表就可以根據這兩個邊界值分出三個分割區。

定義分割區函數的語法如下：

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 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('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。

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

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

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

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

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

--分割出來的分割區資料存在在哪個檔案組
 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.

到此這篇關於SQL Server分割區表的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。