Sql Server效能最佳化——Partition（建立分區）

 

和壓縮（Compression）相比，資料庫分區（Partition）的操作更為複雜繁瑣。而且與Compression一次操作，終身保持不同，分區是一項需要長期維護周期變更的操作。

分區的意義在於將大資料從物理上切割為幾個相互獨立的小部分，從而在查詢時只取出其中一個或幾個分區，減少影響的資料；另外對於置於不同檔案組的分區，並行查詢的效能也要高於對整個表的查詢效能。

事實上，在SQL Server 2005中就已經包含了資料分割函數，甚至在2005之前，還存在一個叫做“Partitioned Views”的功能，能通過將同樣結構的表Union在一個View中，實作類別似現在分區表的效果。而在SQL Server 2008中，資料分割函數得到了顯著加強，使得我們不僅能夠對錶和索引做分區，而且允許對分區上鎖，而不是之前的全表上鎖。

指定分區列

和Compression一樣，在SQL Server 2008中也提供了分區的嚮導介面。在企業管理器中，需要分區的表上右鍵選擇Storage-》Create Partition:

這裡會列出該表所有的欄位，包括欄位類型、長度、精度及小數位元的資訊，可以選擇其中的任意一一列作為分區列（Patitioning Column），不僅僅是數字或者日期類型，即使是字串類型的列，也可以按照字母順序進行分區。而以下類型的列不可用於分區：text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名、hierarchyid、空間索引或 CLR 使用者定義的資料類型。此外，如果使用計算資料行作為分區列，則必須將該列設為持久化列（Persisit）。

在列表下方，提供了兩個選項：

1. 分配到可用分區表：
   這要求在同一資料庫下有另一張已分好區的表，同時該表的分區列和當前選中的列的類型完全一致。
   這樣的好處是當兩張表在查詢中有關聯時，並且其關聯列就是分區列時，使用同樣的分區策略會更有效率。
2. 將非唯一索引和唯一索引的儲存空間調整為與索引分割區列一致：
   這樣會將表中的所有索引也一同分區，實現“對齊”。這是一個重要而麻煩的選項，具體需求請參閱MSDN（資料分割索引的特殊指導原則）。
   這樣的好處是表和索引的分區一致，一方面查詢時利用索引更為高效，而且在下文提到的移入移出分區也會更為高效。

注意：這裡建議使用叢集索引列作為分區列。一方面索引結構本身就應與查詢相關，那麼分區列與索引一致會保證查詢的最大效率；另一方面，保證索引對齊而且是叢集索引對齊是保證分區的移入移出操作順暢的前提，否則可能會出現無法移入移出的情況，而分區的移入移出又是管理大資料的重要策略——滑動視窗（SlideWindow）策略的基礎操作。另外，如果要進行索引對齊，需要所有索引和表的壓縮模式一致。

分區函數與資料分割配置

選好分區列後，如果沒有應用“分配到可用分區表”選項，接下來則會進入選擇\建立分區函數以及資料分割配置的介面。其中分區函數會指定分區邊界，而資料分割配置則規划了每個分區所儲存的檔案組。

嚮導操作介面如下：

其中Left boundary說明每個分區的邊界值被包含在邊界值左側的分區中，也就是每個分區內的資料約束是<=指定的邊界值，相應的，Right boundary則說明每個分區的邊界值被包含在邊界值右側的分區中，每個分區內的資料約束是<指定的邊界值。

在下方的列表中，列出了當前資料分割配置下現有的分區。其中檔案組（Filegroup）指定了每個分區存放的位置，如果將分區放置於位於不同磁碟中的不同檔案組中，由於不同磁碟的讀寫互不干擾，這將提高分區表平行處理的效率。一般情況下，將所有分區放置在同一個檔案組是比較穩妥的做法。關於檔案組的展開閱讀可以參閱：SQL Server Filegroups。

注意，在這裡最後一個分區是沒有指定邊界的，用於儲存所有>(Left Boundary)或>=(Right boundary)最後一個分區邊界的資料。

如果選擇時間類型的欄位作為分區列，可以通過Set按鈕實現按條件分組：

這樣可以很方便得通過設定起止時間將表按照指定時間段自動分區，但之後依然需要手動指定每個分區的檔案組。

制定好資料分割配置之後可以通過Estimate sotrage預估每個分區的行數、空間佔用情況，不過除非需要以佔用空間或行數來規劃你的分區策略，一般不建議在這裡進行預估，因為如果對空表來說，預估的結果當然都是0，而如果表中已經包含大量資料，預估則會花費比較長的時間。

建立分區

通過以上設定，分區已經基本完畢，在嚮導的最後，可以選擇是建立指令碼還是立即執行分區操作。

我們可以查看在不同情況下建立分區的指令碼的情況：

1.在表沒有索引的情況下：

BEGIN TRANSACTIONCREATE PARTITION FUNCTION [TestFunction](datetime) AS RANGE LEFT FOR VALUES (N'2010-01-01T00:00:00', N'2010-02-01T00:00:00', 
N'2010-03-01T00:00:00', N'2010-04-01T00:00:00', N'2010-05-01T00:00:00', N'2010-06-01T00:00:00')CREATE PARTITION SCHEME [TestScheme] AS PARTITION [TestFunction] TO ([PRIMARY], [PRIMARY], [PRIMARY], 
[PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account] (    [birthday])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday])DROP INDEX [ClusteredIndex_on_TestScheme_634025264502439124] ON [dbo].[Account] WITH ( ONLINE = OFF )COMMIT TRANSACTION

這裡先建立Partition Function以及Partition Scheme，之後在分區列上建立叢集索引並按照資料分割配置分區，最後刪除了這一索引。</>

2.在表有索引的情況下：

如果原先沒有叢集索引：

CREATE CLUSTERED INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account] (    [birthday])WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [TestScheme]([birthday])DROP INDEX [ClusteredIndex_on_TestScheme_634025229911990663] ON [dbo].[Account] WITH ( ONLINE = OFF )

這和沒有索引的情況一樣，如果表原先存在叢集索引，則指令碼變為：

CREATE CLUSTERED INDEX [IX_id] ON [dbo].[Account] (    [id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])

可以看到原有的叢集索引（IX_id）在資料分割配置上被重建了。

如果選擇了“對齊索引”選項，則會對所有索引都應用分區：

CREATE CLUSTERED INDEX [IX_id] ON [dbo].[Account] (    [id] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])CREATE NONCLUSTERED INDEX [UIX_birthday] ON [dbo].[Account] (    [birthday] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [TestScheme]([birthday])CREATE NONCLUSTERED INDEX [UIX_name] ON [dbo].[Account] (    [name] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

這裡不僅對叢集索引IX_id進行了分區，也對非叢集索引UIX_name和UIX_birthday進行了分區。

注意事項

1. 對一張表分好區後不可以進行再次分區，同時也沒有直接取消表分區的方法。
2. 如果要查看資料分割資料表的分區狀態以及每個分區中的行數和佔用空間，可以通過Storage-》Management Compression查看。同時可以在這裡為每個分區指定壓縮方式。
3. 如果分區表索引沒有對齊，則不可以對該表進行切入切出（Switch in/out）操作，同樣也不能執行滑動視窗操作。
4. 分區實際上是在每個分區表都添加了約束，相應的插入操作的效能也會受到影響。
5. 即使進行了分區，如果查詢的條件欄位和分區列並沒有關聯，效能也未必會得到提升。

附：對分區並行查詢的說明

由於我在實際操作中主要考慮並行查詢方面的效率，所以文章裡只是略略帶過，但評論中有人提到，所以摘錄整理一些資料在下面：

1. 並行查詢肯定需要多核支援，單核下並行是不可能的。
2. 在2005中，如果有兩個以上的Partition，一個線程對應一個Partition，所以如果有10個線程，卻只有3個分區的話，就會有7個線程被浪費。
3. 在2008中，這一問題被改進，所有的線程都被投入到所有的Partition中。具體可以參看Partitioning enhancements in SQL Server 2008。

原文地址:http://www.cnblogs.com/smjack/archive/2010/02/23/1671943.html