一、分區(qū)表簡介

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

分區(qū)表在邏輯上是一個表，而物理上是多個表。在用戶的角度，分區(qū)表和普通表是一樣的，用戶角度感覺不出來?！　　　?/p>

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

二、對表分區(qū)的理由

表分區(qū)這個特性，只有SQL Server企業(yè)版或SQL Server開發(fā)版才有，理解表分區(qū)的概念之前，還得先理解SQL Server中文件和文件組的概念。這篇文章是解釋文件和文件組的。http://www.dbjr.com.cn/article/248808.htm

表分區(qū)主要用于：

• 提供性能：這個是大多人數(shù)分區(qū)的目的，把一個表分部到不同的硬盤或其他存儲介質(zhì)中，會大大提升查詢速度。
• 提高穩(wěn)定性：當(dāng)一個分區(qū)出了問題，不會影響其他分區(qū)，僅僅是當(dāng)前壞的分區(qū)不可用。
• 便于管理：把一個大表分成若干個小表，則備份和恢復(fù)的時候不再需要備份整個表，可以單獨備份分區(qū)。
• 存檔：將一些不太常用的數(shù)據(jù)，單獨存放。如：將1年前的數(shù)據(jù)記錄分到一個專門的存檔服務(wù)器存放。

三、分區(qū)表的操作步驟

分區(qū)表分為三個步驟：

• 定義分區(qū)函數(shù)

  定義分區(qū)構(gòu)架

  定義分區(qū)表

分區(qū)函數(shù)，分區(qū)構(gòu)架和分區(qū)表的關(guān)系如下：分區(qū)表依賴于分區(qū)構(gòu)架，分區(qū)構(gòu)架又依賴分區(qū)函數(shù)。

因此，定義分區(qū)表的順序基本上是定義分區(qū)函數(shù)->定義分區(qū)構(gòu)架->定義分區(qū)表。
實際操作，先定義一張需要分區(qū)的表：

我們以SalesDate列作為分區(qū)列。

第一步、定義分區(qū)函數(shù)：

分區(qū)函數(shù)用于判斷一行數(shù)據(jù)屬于哪個分區(qū)，通過分區(qū)函數(shù)中設(shè)置邊界值來使得根據(jù)行中特定列的值來確定其分區(qū)。

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

定義分區(qū)函數(shù)的語法如下：

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

在上面定義分區(qū)函數(shù)的原型語法中，我們看到其中并沒有涉及到具體的表，因為分區(qū)函數(shù)并不和具體的表綁定。

另外原型中還可以看到Range left和right，這個參數(shù)決定臨界值(也就是剛好等于2004-01-01或2007-01-01的這些與分界值相等的值)應(yīng)該歸于左邊還是右邊。

創(chuàng)建分區(qū)函數(shù)：

--創(chuàng)建分區(qū)函數(shù)
CREATE PARTITION FUNCTION fnPartition(DATE)
   AS RANGE RIGHT
   FOR VALUES('2004-01-01','2007-01-01')

--查看分區(qū)表是否創(chuàng)建成功
SELECT * FROM sys.partition_functions

上述查詢語句顯示結(jié)果如下：

通過系統(tǒng)視圖，可以看見這個分區(qū)函數(shù)已經(jīng)創(chuàng)建成功。

第二步、定義分區(qū)構(gòu)架

定義完分區(qū)函數(shù)僅僅知道了根據(jù)列的值將數(shù)據(jù)分配到不同的分區(qū)。而每個分區(qū)的存儲方式，則需要分區(qū)構(gòu)架來定義。

分區(qū)構(gòu)架語法原型：

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

從原型來看，分區(qū)構(gòu)架僅僅是依賴分區(qū)函數(shù)。分區(qū)構(gòu)架負責(zé)分配每個區(qū)屬于哪個文件組，而分區(qū)函數(shù)是決定哪條數(shù)據(jù)屬于哪個分區(qū)。

基于之前創(chuàng)建的分區(qū)函數(shù),創(chuàng)建分區(qū)構(gòu)架:

--基于之前的分區(qū)函數(shù)創(chuàng)建分區(qū)構(gòu)架schema
CREATE PARTITION SCHEME SchemaForParirion
　AS PARTITION fnPartition    --這個是之前創(chuàng)建的分區(qū)函數(shù)
　TO(FileGroup1,[primary],FileGroup1)    --FileGroup1是自己添加的文件組，因為有兩個分界值，3個分區(qū)，所以要指定3個文件組，也可以使用ALL所謂的分區(qū)指向一個文件組

--查看已創(chuàng)建的分區(qū)構(gòu)架
SELECT * FROM sys.partition_schemes

以上SELECT語句輸出結(jié)果如下：

留意到分區(qū)構(gòu)架已成功創(chuàng)建。

第三步、定義分區(qū)表

有了分區(qū)函數(shù)與分區(qū)構(gòu)架，下面就可以創(chuàng)建分區(qū)表了，表在創(chuàng)建的時候就要決定是否是分區(qū)表了。

雖然在大部分情況下，都是在發(fā)現(xiàn)表太大時，才想到要分區(qū)。但是分區(qū)表只能夠在創(chuàng)建的時候指定為分區(qū)表。

CREATE TABLE OrderRecords
　　(
    　　Id int,
    　　OrderId int,
    　　SalesDate Date 
　　)
　　ON SchemaForParirion(SalesDate)    --SchemaForPartition是剛剛定義的分區(qū)架構(gòu)，括號內(nèi)為指定的分區(qū)列

然后手工向數(shù)據(jù)庫里面添加3條數(shù)據(jù)：

然后執(zhí)行查詢：

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

可以看到，分區(qū)起作用了：

四、分區(qū)表的分割

分區(qū)表的分割，相當(dāng)于新建一個分區(qū)，將原有的分區(qū)需要分割的內(nèi)容插入新的分區(qū)，然后刪除老的分區(qū)的內(nèi)容。
新加入多一個分割點：2009-01-01。如下圖所示：

對于上圖的操作，如果分割時，被分割的分區(qū)3內(nèi)有內(nèi)容需要分割到分區(qū)4，則這些數(shù)據(jù)需要被復(fù)制到分區(qū)4，并刪除分區(qū)3上對應(yīng)的數(shù)據(jù)。

這種操作非常非常消耗IO，并且在分割的過程中鎖定分區(qū)3內(nèi)的內(nèi)容，造成分區(qū)3的內(nèi)容暫時不可用。而且，這個操作生成的日志內(nèi)容將會是被轉(zhuǎn)移數(shù)據(jù)的4倍。

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

分割現(xiàn)有的分區(qū)需要兩個步驟：

• 首先要告訴SQL Server新建立的分區(qū)放到哪個文件組
• 建立新的分割點。

加一條數(shù)據(jù)，致使原表如下：

執(zhí)行那個長查詢，顯示如下：

現(xiàn)在，可以執(zhí)行分割操作了：

--分割出來的分區(qū)數(shù)據(jù)存在在哪個文件組
 ALTER PARTITION SCHEME SchemaForParirion 
    NEXT USED 'PRIMARY'
--添加分割點
 ALTER PARTITION FUNCTION fnPartition()
　　SPLIT RANGE('2009-01-01')

執(zhí)行完之后，再看結(jié)果如下：

五、分區(qū)表的合并

分區(qū)的合并可以旱作是分區(qū)分割的逆操作。分區(qū)的合并需要提供分割點，并且這個分割點必須在現(xiàn)有的分割表中已經(jīng)存在，否則進行合并時就會報錯。

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

合并分區(qū)操作：

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

再來看分區(qū)信息:

在這里應(yīng)該注意到一個問題，假設(shè)已經(jīng)合并了分區(qū)，那么合并之后，文件是存在分區(qū)3的文件組呢，還是分區(qū)4的文件組呢？這個取決于我們剛開始時定義的分區(qū)函數(shù)是left還是right。

如果定義的是left，則左邊的分區(qū)3合并到分區(qū)4。如果是right，則右邊的分區(qū)4合并到分區(qū)3.

到此這篇關(guān)于SQL Server分區(qū)表的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評論