在談論分區表這個話題之前，先和大家分享一個案例：<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

2008年秋天的某天，我的團隊接到成都市XX局一個SQL調優的ESS單子。客戶反映查詢統計一次各地市局上報的數據匯總，需要6到15秒才能獲得真正想要的數據，當我和銷售人員趕到客戶數據中心現場后，發現里面布置了很多柜式服務器，每臺服務器都是8核<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />16G內存。和相關技術負責人溝通以及演示業務系統之后，可以肯定不是服務器性能的問題，我詳細分析了他們的數據庫，統計慢的幾張表往往一周的上報數據便會增加1百多萬行，導致他們這個系統剛上線沒多久，某些表產生的數據已經在2000萬行以上，最終我提出了優化方案，業務邏輯層采用存儲過程代替普通的SQL語句，并啟用相關開發平臺的緩存技術；數據庫系統中采用增強索引和規劃分區表進行優化，最終問題解決。

事實上數據庫性能優化是每個優秀的數據庫工程師必須具備的素質之一，而這一節討論的分區表便是性能調優的一種技術。在企業級應用系統中，一個表存儲2千萬行的數據很常見，不可預期的數據也會在逐漸增長，所以數千萬級別的表DBA會常常碰到，而TB級別的數據最終也在所難免，因此了解和掌握性能調優的18般兵器非常重要。

?

我計劃用三篇博文介紹分區表這個主題，分別為：

1，??? 分區表理論解析

2，??? 實戰分區表

3，??? 分區表前傳

? <?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" />

?

大凡在應用系統和數據庫系統中行走江湖多年的朋友，都會面臨數據統計、分析以及歸檔的問題，企業信息化進程加速了各種數據的極具增長，商務智能(BI)的出現和實施著實給信息工作者和決策者帶來了絕妙的體驗，但從 OLTP 向 OLAP 系統加載數據是很頭疼的事，常常需要數分鐘或數小時,解決這一問題的技術之一便是分區表，一旦實施了分區表，這樣的操作往往只需幾秒鐘，太讓人興奮了。而大型表或索引經過分區后更容易進行管理，因為這樣可以快速高效地管理和訪問數據子集，同時維護數據集合的完整性。分區表的數據分布于一個數據庫中的多個文件組單元中，數據是按水平方式分區的（數據分區的多種方式會在分區表前傳中闡述），因此一個表的某些行映射到某個分區，而另外一些行映射到另外某個分區，以此類推。當對數據進行查詢或更新時，表將被視為單個邏輯實體，所以在數據訪問層你會感覺和訪問普通表一樣，而好處就在于可以查詢想要的某個分區，而不必掃描整個表。有一點必須明白，單個表的所有分區都必須位于同一個數據庫中。

分區表支持和標準表相關的所有屬性和功能，包括約束、默認值、標識和時間戳值以及觸發器等。決定是否實現分區主要取決于表當前的大小或將來的大小以及對表執行查詢和維護操作的完善程度。

通常，如果某個大型表同時滿足下列兩個條件，則可能適于進行分區：

1，該表包含或將包含以多種不同方式使用的大量數據

2，維護開銷超過了預定義的維護期

例如，如果對當前年份或當前月份的數據主要執行 SELECT 、INSERT、UPDATE 和 DELETE 操作，而對以前年份或以前月份的數據主要執行 SELECT 查詢，則如果按年份或月份對表進行分區，表的管理要容易些，因為此時對表的維護操作只針對一個數據子集。如果該表沒有分區，那么就需要對整個數據集執行這些操作，這樣就會消耗大量資源。

所以常常根據日期和分類對表進行分區，當然利用某個標識列ID也是很好的選擇。例如，電子商務數據庫的某張表可能包含了近6年的數據，但是只定期訪問本年度或某個月的數據，那么就可以按年份或月份分區，而另外一張表包含了近幾十種類型商品的訂單，那么此時可以為每種類型商品分一個區。

一般而言，衡量大型表是以數據為標準的，但對于適合分區的大型表，衡量大型表更重要的是對數據訪問的性能，如果對于某些表的訪問和維護有較嚴重的性能問題，就可以視為大型表，就應該考慮通過更好的設計和分區來解決性能問題。

創建分區表必須經過如下三個步驟：

1，? 創建分區函數

2，? 創建映射到分區函數的分區方案

3，? 創建使用該分區方案的分區表

?

分區函數

?

分區函數是數據庫中的一個獨立對象，它將表的行映射到一組分區，所以分區函數解決的是HOW的問題，即表如何分區的問題。創建分區函數時，必須指明數據分區的邊界點以及分區依據列，這樣便知道如何對表或索引進行分區。分區函數的創建語法如下：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

AS RANGE [ LEFT | RIGHT ]

FOR VALUES ( [ boundary_value [ ,...n ] ] )

[ ; ]

分區函數語法的相關解釋：

1，? 創建一個分區函數和創建一個普通的數據庫對象（例如表）沒什么區別。所以根據標準語法走就OK了。 2，? partition_function_name是分區函數的名稱。分區函數名稱在數據庫內必須唯一，并且符合標識符的規則。

3，? input_parameter_type是用于分區的列的數據類型，習慣把它稱為分區依據列。當用作分區列時，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名數據類型或 CLR 用戶定義數據類型外，其他所有數據類型均有效。分區依據列是在 CREATE TABLE 或 CREATE INDEX 語句中指定的。

4，? boundary_value [ ,...n ]中的boundary_value是邊界值（或邊界點的值），n代表可以最多有n個邊界值，即n指定 boundary_value 提供的值的數目，但n不能超過 999。所創建的分區數等于 n + 1。不必按順序列出各值。如果值未按順序列出，則 Database Engine 將對這些邊界值進行排序，創建分區函數并返回一個警告，說明未按順序提供值。如果 n 包括任何重復的值，則數據庫引擎將返回錯誤。邊界值的取值一定是和分區依據列相關的，所以只能使用 CREATE TABLE 或 CREATE INDEX 語句中指定的一個分區列。

5，? LEFT | RIGHT 指定boundary_value [ ,...n ] 的每個boundary_value屬于每個邊界值間隔的哪一側（左側還是右側）。如果未指定，則默認值為 LEFT。

例如我們可以依據某個表的int列來創建分區函數:

create partition function MyPF1(int)

range left??? --默認是left，所以可以省略left

for values(500000,1000000,1500000)

很明顯，這個分區函數創建了4個分區，因為此時n=3,所以分區總數是n+1=4。而那個int分區依據列表明將要分區的那個表里面一定有一列是int類型，是分區依據列。這個分區函數我們用的是range left，各個分區的取值范圍如下表：

分區	取值范圍
1	(負無窮,500000]
2	[500001,1000000]
3	[1000001,1500000]
4	[1500001,正無窮)

?

如果換成range right，即創建分區函數時代碼如下：

create partition function MyPF1(int)

range right

for values(500000,1000000,1500000)

那么各個分區的取值范圍如下表：

分區	取值范圍
1	(負無窮,499999]
2	[500000,999999]
3	[1000000,1499999]
4	[1500000,正無窮)

?

我們還可以根據日期列創建分區函數，例如：

create partition function MyPF2(datetime)

range right

for values('2008/01/01', '2009/01/01')

這個分區函數非常適合查詢和歸檔某一年的數據。各個分區的取值范圍如下表：

分區	取值范圍
1	<=2007/12/31
2	[2008/01/01,2008/12/31]
3	>=2009/01/01

當然我們也可以根據月份分區，而分區依據列支持的數據類型非常多，參照項目的實際情況選擇最能表示分區的列類型。 接分區表理論解析(下)

總結

以上是生活随笔為你收集整理的分区表理论解析(上)：SQL Server 2k52k8系列(一)的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。