理解SQL SERVER中的分區表(轉）

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

簡介

    分區表是在SQL SERVER2005之後的版本引入的特性。這個特性允許把邏輯上的一個表在物理上分為很多部分。而對於SQL SERVER2005之前版本，所謂的分區表僅僅是分布式視圖,也就是多個表做union操作.

    分區表在邏輯上是一個表，而物理上是多個表.這意味著從使用者的角度來看，分區表和普通表是一樣的。這個概念可以簡單如所示：

   

     而對於SQL SERVER2005之前的版本，是沒有分區這個概念的，所謂的分區僅僅是分布式視圖:

    

     本篇文章所講述的分區表指的是SQL SERVER2005之後引入的分區表特性.

 

為什麼要對錶進行分區

     在回答標題的問題之前，需要說明的是，表分區這個特性只有在企業版或者開發版中才有,還有理解表分區的概念還需要理解SQL SERVER中檔案和檔案組的概念.

     對錶進行分區在多種情境下都需要被用到.通常來說，使用表分區最主要是用於:

•      存檔，比如將銷售記錄中1年前的資料分到一個專門存檔的伺服器中
•      便於管理，比如把一個大表分成若干個小表，則備份和恢複的時候不再需要備份整個表，可以單獨備份分區
•      提高可用性，當一個分區跪了以後，只有一個分區不可用，其它分區不受影響
•      提高效能，這個往往是大多數人分區的目的，把一個表分布到不同的硬碟或其他儲存介質中，會大大提升查詢的速度.

 

分區表的步驟

    分區表的定義大體上分為三個步驟：

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

    分區函數，分區構架和分區表的關係如下：

   

 

 

    分區表依賴分區構架，而分區構架又依賴分區函數.值得注意的是，分區函數並不屬於具體的分區構架和分區表，他們之間的關係僅僅是使用關係.

    下面我們通過一個例子來看如何定義一個分區表:

     假設我們需要定義的分區表結構如下:

    

     第一列為自增列，orderid為訂單id列，SalesDate為訂單日期列，也就是我們需要分區的依據.

     下面我們按照上面所說的三個步驟來實現分區表.

 

 

定義分區函數

    分區函數是用於判定資料行該屬於哪個分區,通過分區函數中設定邊界值來使得根據行中特定列的值來確定其分區，上面例子中，我們可以通過SalesDate 的值來判定其不同的分區.假設我們想定義兩個邊界值(boundaryValue)進行分區,則會產生三個分區,這裡我設定邊界值分別為 2004-01-01和2007-01-01，則前面例子中的表會根據這兩個邊界值分成三個區:

   

    在MSDN中，定義分區函數的原型如下:

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

 

 

   通過定義分區函數的原型，我們看出其中並沒有具體涉及具體的表.因為分區函數並不和具體的表相綁定.上面原型中還可以看到Range left和right.這個參數是決定臨界值本身應該歸於“left”還是“right”：

  

    下面我們根據上面的參數定義分區函數:

    

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

 

定義分區構架

    定義完分區函數僅僅是知道了如何將列的值區分到了不同的分區。而每個分區的儲存方式，則需要分區構架來定義.使用分區構架需要你對檔案和檔案組有點瞭解.

    我們先來看MSDN的分區構架的原型:

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

    從原型來看，分區構架僅僅是依賴分區函數.分區構架中負責分配每個區屬於哪個檔案組，而分區函數是決定如何在邏輯上分區:

    

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

    

 

定義分區表

    接下來就該建立分區表了.表在建立的時候就已經決定是否是分區表了。雖然在很多情況下都是你在發現已經表已經足夠大的時候才想到要把表分區，但是分區表只能夠在建立的時候指定為分區表。

   

    為剛建立的分區表PartitionedTable加入5萬條測試資料，其中SalesDate隨機產生，從2001年到2010年隨機分布.加入資料後，我們通過如下語句來看結果:

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(‘PartitionedTable‘)and i.index_id in (0, 1) order by p.partition_number

 

   可以看到我們分區的資料分布:

   

 

 

 

 

 

分區表的分割

     分區表的分割。相當於建立一個分區，將原有的分區需要分割的內容插入新的分區，然後刪除老的分區的內容,概念如:

     假設我新加入一個分割點：2009-01-01，則概念如下:

    

     通過我們可以看出，如果分割時，被分割的分區3內有內容需要分割到分區4，則這些資料需要被複製到分區4，並刪除分區3上對應資料。

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

     所以我們如果不想因為這種操作給客戶帶來麻煩而被老闆爆菊的話…最好還是把分割點建立在未來（也就是預先建立分割點)，比如2012-01-01。則分區3內的內容不受任何影響。在以後2012的資料加入時，自動插入到分區4.

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

     1.首先告訴SQL SERVER建立立的分區放到哪個檔案組

     2.建立新的分割點

     可以通過如下語句來完成:

    

 

     如果我們的分割構架在定義的時候已經指定了NEXT USED，則直接添加分割點即可。

     通過文中前面查看分區的長語句..再來看:

    

     新的分區已經加入！

 

分區的合并

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

     假設我們需要根據2009-01-01來合并分區,概念如下:

    

     只需要使用merge參數：

    

     再來看分區資訊:

    

    

 

 

     這裡值得注意的是，假設分區3和分區4不再一個檔案組，則合并後應該存在哪個檔案組呢？換句話說，是由分區3合并到分區4還是由分區4合并到分區3？這個 需要看我們的分區函數定義的是left還是right.如果定義的是left.則由左邊的分區3合并到右邊的分區4.反之，則由分區4合并到分區3:

    

 

 

總結

    本文從講解了SQL SERVER中分區表的使用方式。分區表是一個非常強大的功能。使用分區表相對傳統的分區視圖來說，對於減少DBA的管理工作來說，會更勝一籌！

 

 

 

sqlserver表分區

原理就類似於把一個表的資 料放在不同的分區裡面,當查詢時,如果都在同一個單獨的分區內的話,就不用進行全表掃描,因此在這些情況下可以提高查詢的效率,但如果所有分區都要查詢所 有資料的話,分區並不會提高效率.

12345678910111213141516171819202122232425262728293031

--建立分區表過程一共分為三步:建立分區函數、建立資料分割配置、建立分區表USE [CardID]GOBEGIN TRANSACTION----建立分區函數CREATE PARTITION FUNCTION [SlotecardFunction](datetime) AS RANGE left FOR VALUES (N‘2014-03-26T00:00:00‘, N‘2014-04-26T00:00:00‘, N‘2014-05-26T00:00:00‘, N‘2014-06-26T00:00:00‘, N‘2014-07-26T00:00:00‘, N‘2014-08-26T00:00:00‘, N‘2014-09-26T00:00:00‘)--查看分區函數是否建立成功 --select * from sys.partition_functions--建立資料分割配置 關聯到分區函數CREATE PARTITION SCHEME [Slotecard] AS PARTITION [SlotecardFunction] TO ([PRIMARY], [fg1], [fg2], [fg3], [fg4], [fg5], [fg6], [fg7]) --查看已建立的資料分割配置--select * from sys.partition_schemesALTER TABLE [dbo].[ak_SloteCardTimes] DROP CONSTRAINT [PK_ak_SloteCardTimes]--這裡要注意一個文法，因為現在表已經存在了，那麼就不能再通過CREATE TABLE的方式來建立分區表了，而是通過建立一個叢集索引的方式。但又把它刪除掉。--但是，如果表上面已經有一個叢集索引呢？肯定會出錯，因為一個表只能有一個叢集索引。那麼該怎麼辦呢？--我們發現嚮導會這樣做，先把原先的叢集索引改為非聚聚的。ALTER TABLE [dbo].[ak_SloteCardTimes] ADD  CONSTRAINT [PK_ak_SloteCardTimes] PRIMARY KEY NONCLUSTERED (    [RecordID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] --建立叢集索引CREATE CLUSTERED INDEX [ClusteredIndex_on_Slotecard_635317831823593750] ON [dbo].[ak_SloteCardTimes] (<br> [SloteCardTime])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [Slotecard]([SloteCardTime]) --刪除叢集索引DROP INDEX [ClusteredIndex_on_Slotecard_635317831823593750] ON [dbo].[ak_SloteCardTimes] WITH ( ONLINE = OFF ) COMMIT TRANSACTION

除了提供了建立分區的嚮導之外，還有一個管理分區的嚮導，主要是可以做SWITCH,MERGE,SPLIT這些操作。也可以查看資料 也可以手動建立新的分區暫存資料表

12345678

select $PARTITION.Slotecard([SloteCardTime]) as 分區編號,count([RecordID]) as 記錄數 from [ak_SloteCardTimes] group by $PARTITION.Slotecard([SloteCardTime])    -- 查詢某個分區--這裡我們要用到$PARTITION 函數，這個函數可以協助我們查詢某個分區的資料，還可以檢索某個值所隸屬的分區號。$PARTITION 函數的進一步細節可以查看MSDN--查詢資料分割資料表Order的第一個分區，代碼如下：   select*from [ak_SloteCardTimes]    where $partition.Slotecard   ([SloteCardTime])=1

 

理解SQL SERVER中的分區表(轉）