一、分區(qū)表簡(jiǎn)介

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

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

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

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

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

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

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

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

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

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

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

  定義分區(qū)表

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

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

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

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

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

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

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

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

在上面定義分區(qū)函數(shù)的原型語(yǔ)法中，我們看到其中并沒(méi)有涉及到具體的表，因?yàn)榉謪^(qū)函數(shù)并不和具體的表綁定。

另外原型中還可以看到Range left和right，這個(gè)參數(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

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

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

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

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

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

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

從原型來(lái)看，分區(qū)構(gòu)架僅僅是依賴(lài)分區(qū)函數(shù)。分區(qū)構(gòu)架負(fù)責(zé)分配每個(gè)區(qū)屬于哪個(gè)文件組，而分區(qū)函數(shù)是決定哪條數(shù)據(jù)屬于哪個(gè)分區(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    --這個(gè)是之前創(chuàng)建的分區(qū)函數(shù)
　TO(FileGroup1,[primary],FileGroup1)    --FileGroup1是自己添加的文件組，因?yàn)橛袃蓚€(gè)分界值，3個(gè)分區(qū)，所以要指定3個(gè)文件組，也可以使用ALL所謂的分區(qū)指向一個(gè)文件組

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

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

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

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

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

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

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

然后手工向數(shù)據(jù)庫(kù)里面添加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)于新建一個(gè)分區(qū)，將原有的分區(qū)需要分割的內(nèi)容插入新的分區(qū)，然后刪除老的分區(qū)的內(nèi)容。
新加入多一個(gè)分割點(diǎn)：2009-01-01。如下圖所示：

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

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

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

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

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

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

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

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

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

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

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

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

例如，對(duì)以上例子，根據(jù)2009-01-01來(lái)進(jìn)行合并：

合并分區(qū)操作：

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

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

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

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

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