SQL Server 2005對海量資料的處理

 超大型資料庫的大小常常達到數百GB，有時甚至要用TB來計算。而單表的資料量往往會達到上億的記錄，並且記錄數會隨著時間而增長。這不但影響著資料庫的執行效率，也增大資料庫的維護難度。除了表的資料量外，對錶不同的訪問模式也可能會影響效能和可用性。這些問題都可以通過對大表進行合理分割槽得到很大的改善。當表和索引變得非常大時，分割槽可以將資料分為更小、更容易管理的部分來提高系統的執行效率。如果系統有多個CPU或是多個磁碟子系統，可以通過並行操作獲得更好的效能。所以對大表進行分割槽是處理海量資料的一種十分高效的方法。本文通過一個具體例項，介紹如何建立和修改分割槽表，以及如何檢視分割槽表。

1、SQL Server 2005

  SQL Server 2005是微軟在推出SQL Server 2000後時隔五年推出的一個資料庫平臺，它的資料庫引擎為關係型資料和結構化資料提供了更安全可靠的儲存功能，使使用者可以構建和管理用於業務的高可用和高效能的資料應用程式。此外，SQL Server 2005結合了分析、報表、整合和通知功能。這使得企業可以構建和部署經濟有效的BI解決方案，幫助團隊通過記分卡、Dashboard、Web Services和移動裝置將資料應用推向業務的各個領域。無論是開發人員、資料庫管理員、資訊工作者還是決策者，SQL Server 2005都可以提供出創新的解決方案，並可從資料中獲得更多的益處。

  它所帶來的新特性，如T-SQL的增強、資料分割槽、服務代理和與.Net Framework的整合等，在易管理性、可用性、可伸縮性和安全性等方面都有很大的增強。

2、表分割槽的具體實現方法

  表分割槽分為水平分割槽和垂直分割槽。水平分割槽將表分為多個表。每個表包含的列數相同，但是行更少。例如，可以將一個包含十億行的表水平分割槽成 12 個表，每個小表表示特定年份內一個月的資料。任何需要特定月份資料的查詢只需引用相應月份的表。而垂直分割槽則是將原始表分成多個只包含較少列的表。水平分割槽是最常用分割槽方式，本文以水平分割槽來介紹具體實現方法。

  水平分割槽常用的方法是根據時期和使用對資料進行水平分割槽。例如本文例子，一個簡訊傳送記錄表包含最近一年的資料，但是隻定期訪問本季度的資料。在這種情況下，可考慮將資料分成四個區，每個區只包含一個季度的資料。

2.1、建立檔案組

  建立分割槽表先要建立檔案組，而建立多個檔案組主要是為了獲得好的 I/O 平衡。一般情況下，檔案組數最好與分割槽數相同，並且這些檔案組通常位於不同的磁碟上。每個檔案組可以由一個或多個檔案構成，而每個分割槽必須對映到一個檔案組。一個檔案組可以由多個分割槽使用。為了更好地管理資料(例如，為了獲得更精確的備份控制)，對分割槽表應進行設計，以便只有相關資料或邏輯分組的資料位於同一個檔案組中。使用 ALTER DATABASE，新增邏輯檔案組名：
ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1]
  DeanDB為資料庫名稱，FG1檔案組名。建立檔案組後，再使用 ALTER DATABASE 將檔案新增到該檔案組中：
ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG1',
FILENAME = N'C:DeanDataFG1.ndf' , SIZE = 3072KB ,
FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]
  類似的建立四個檔案和檔案組，並把每一個儲存資料的檔案放在不同的磁碟驅動器裡。

2.2、建立分割槽函式

  建立分割槽表必須先確定分割槽的功能機制，表進行分割槽的標準是通過分割槽函式來決定的。建立資料分割槽函式有RANGE “LEFT | / RIGHT”兩種選擇。代表每個邊界值在區域性的哪一邊。例如存在四個分割槽，則定義三個邊界點值，並指定每個值是第一個分割槽的上邊界 (LEFT) 還是第二個分割槽的下邊界 (RIGHT)。程式碼如下：
CREATE PARTITION FUNCTION [SendSMSPF](datetime)　
AS RANGE RIGHT FOR VALUES ('20070401', '20070701', '20071001')
2.3、建立分割槽方案

  建立分割槽函式後，必須將其與分割槽方案相關聯，以便將分割槽指向至特定的檔案組。就是定義實際存放資料的媒體與各資料塊的對應關係。多個資料表可以共用相同的資料分割槽函式，一般不共用相同的資料分割槽方案。可以通過不同的分割槽方案，使用相同的分割槽函式，使不同的資料表有相同的分割槽條件，但存放在不同的媒介上。建立分割槽方案的程式碼如下：
CREATE PARTITION SCHEME [SendSMSPS] AS PARTITION [SendSMSPF]
TO ([FG1], [FG2], [FG3], [FG4])
2.4、建立分割槽表

  建立好分割槽函式和分割槽方案後，就可以建立分割槽表了。分割槽表是通過定義分割槽鍵值和分割槽方案相聯絡的。插入記錄時，SQL SERVER會根據分割槽鍵值的不同，通過分割槽函式的定義將資料放到相應的分割槽。從而把分割槽函式、分割槽方案和分割槽表三者有機的結合起來。建立分割槽表的程式碼如下：
CREATE TABLE SendSMSLog
([ID] [int] IDENTITY(1,1) NOT NULL,
[IDNum] [nvarchar](50) NULL,
[SendContent] [text] NULL
[SendDate] [datetime] NOT NULL,
) ON SendSMSPS(SendDate)
2.5、檢視分割槽表資訊

  系統執行一段時間或者把以前的資料匯入分割槽表後，我們需要檢視資料的具體儲存情況，即每個分割槽存取的記錄數，那些記錄存取在那個分割槽等。我們可以通過$partition.SendSMSPF來檢視，程式碼如下：
SELECT $partition.SendSMSPF(o.SendDate)
AS [Partition Number]
, min(o.SendDate) AS [Min SendDate]
, max(o.SendDate) AS [Max SendDate]
, count(*) AS [Rows In Partition]
FROM dbo.SendSMSLog AS o
GROUP BY $partition.SendSMSPF(o.SendDate)
ORDER BY [Partition Number]
在查詢分析器裡執行以上指令碼。

2.6、維護分割槽

  分割槽的維護主要設計分割槽的新增、減少、合併和在分割槽間轉換。可以通過ALTER PARTITION FUNCTION的選項SPLIT，MERGE和ALTER TABLE的選項SWITCH來實現。SPLIT會多增加一個分割槽，而MEGRE會合並或者減少分割槽，SWITCH則是邏輯地在組間轉換分割槽。

3、效能對比

  我們對2650萬資料，儲存空間佔用約4G的單表進行效能對比，測試環境為IBM365，CPU 至強2.7G*2、記憶體 16G、硬碟 136G*2，系統平臺為Windows 2003 SP1+SQL Server 2005 SP1。

說明：

1、根據時間檢索某一天記錄所耗時間
2、單條記錄插入所耗時間
3、根據時間刪除某一天記錄所耗時間
4、統計每月的記錄數所需時間

  從表1可以看出，對分割槽表進行操作比未分割槽的表要快，這是因為對分割槽表的操作採用了CPU和I/O的並行操作，檢索資料的資料量也變小了，定位資料所耗時間變短。

4、結束語

  對海量資料的處理一直是一個令人頭痛的問題。分離的技術是所有設計者們首先考慮的問題，不管是分離應用程式功能還是分離資料訪問，如果加以了合理規劃，都能十分有效的解決大資料表的執行效率低和維護成本高等問題。SQL Server 2005新增的表分割槽功能，可以對資料進行合理分割槽，當使用者在訪問部分資料時，SQL Server最佳化引擎可以根據資料的實體存放，找出最佳的執行方案，而不至於大海撈針。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-553579/，如需轉載，請註明出處，否則將追究法律責任。