SQLSERVER利用分區(qū)對大數(shù)據(jù)表處理操作手冊

1、SQL SERVER 200利用分區(qū)對大數(shù)據(jù)表處理操作手冊超大型數(shù)據(jù)庫的大小常5常達(dá)到數(shù)百GB,有時甚至要用TB來計算。而單表的數(shù)據(jù)量往往會達(dá)到上億的記錄，并且記錄數(shù)會隨著時間而增長。這不但影響著數(shù)據(jù)庫的運(yùn)行效率，也增大數(shù)據(jù)庫的維護(hù)難度。除了表的數(shù)據(jù)量外，對表不同的訪問模式也可能會影響性能和可用性。這些問題都可以通過對大表進(jìn)行合理分區(qū)得到很大的改善。當(dāng)表和索引變得非常大時，分區(qū)可以將數(shù)據(jù)分為更小、更容易管理的部分來提高系統(tǒng)的運(yùn)行效率。如果系統(tǒng)有多個CPU或是多個磁盤子系統(tǒng)，可以通過并行操作獲得更好的性能。所以對大表進(jìn)行分區(qū)是處理海量數(shù)據(jù)的一種十分高效的方法。本文通過一個具體實例，介紹如何創(chuàng)建和

2、修改分區(qū)表，以及如何查看分區(qū)表。SQL Server 2005是微軟在推出SQL Server 2000后時隔五年推出的一個數(shù)據(jù)庫平臺，它的數(shù)據(jù)庫引擎為關(guān)系型數(shù)據(jù)和結(jié)構(gòu)化數(shù)據(jù)提供了更安全可靠的存儲功能，使用戶可以構(gòu)建和管理用于業(yè)務(wù)的高可用和高性能的數(shù)據(jù)應(yīng)用程序。此外 SQL Server 2005結(jié)合了分析、報表、集成和通知功能。這使企業(yè)可以構(gòu)建和部署經(jīng)濟(jì)有效的 BI 解決方案，幫助團(tuán)隊通過記分卡、Dashboard、 Web Services和移動設(shè)備將數(shù)據(jù)應(yīng)用推向業(yè)務(wù)的各個領(lǐng)域。無論是開發(fā)人員、數(shù)據(jù)庫管理員、信息工作者還是決策者， SQL Server 2005都可以提供出創(chuàng)新的解決方案，

3、并可從數(shù)據(jù)中獲得更多的益處。它所帶來的新特性，如T-SQM增強(qiáng)、數(shù)據(jù)分區(qū)、服務(wù)代理和與.NetFramework的集成等，在易管理性、可用性、可伸縮性和安全性等方面都 有很大的增強(qiáng)。表分區(qū)的具體實現(xiàn)方法：表分區(qū)分為水平分區(qū)和垂直分區(qū)。水平分區(qū)將表分為多個表。每個表包含的列數(shù)相同，但是行更少。例如，可以將一個包含十億行的表水平分區(qū)成 12 個 表，每個小表表示特定年份內(nèi)一個月的數(shù)據(jù)。任何需要特定月份數(shù)據(jù)的查詢只需引用相應(yīng)月份的表。而垂直分區(qū)則是將原始表分成多個只包含較少列的表。水平分區(qū)是最常用分區(qū)方式，本文以水平分區(qū)來介紹具體實現(xiàn)方法。水平分區(qū)常用的方法是根據(jù)時期和使用對數(shù)據(jù)進(jìn)行水平分區(qū)。例如本

4、文例子，一個短信發(fā)送記錄表包含最近一年的數(shù)據(jù)，但是只定期訪問本季度的數(shù)據(jù)。在這種情況下，可考慮將數(shù)據(jù)分成四個區(qū)，每個區(qū)只包含一個季度的數(shù) 據(jù)。以下是創(chuàng)建過程：一、最基本,最重要的一步就是創(chuàng)建分區(qū)函數(shù)。創(chuàng)建分區(qū)函數(shù)首先要確定分區(qū)鍵 -既按照哪字段來進(jìn)行分區(qū)。在這里，使用記錄的時間來作為分區(qū)鍵，由于數(shù)據(jù)量的問題,最終決定每個月的數(shù)據(jù)放一個單獨(dú)的分區(qū)。CREATE PARTION FUNCTION FiveYearDateRangePFN(datetime)ASRANGE LEFT FOR VALUES (' 23:59:59.997',-2006 年 9 月' 23:59:

5、59.997',-2006 年 10 月' 23:59:59.997',-2006 年 11 月' 23:59:59.997',-2006 年 12 月' 23:59:59.997',-2007 年 1 月' 23:59:59.997',-2007 年 2 月' 23:59:59.997',-2007 年 3 月' 23:59:59.997',-2007 年 4 月' 23:59:59.997',-2007 年 5 月' 23:59:59.997',-2007

6、 年 6 月'200731 23:59:59.997',-2007 年 7 月' 23:59:59.997',-2007 年 8 月' 23:59:59.997',-2007 年 9 月' 23:59:59.997',-2007 年 10 月' 23:59:59.997',-2007 年 11 月' 23:59:59.997',-2007 年 12 月GO二、上一步是完成一個概念上的分區(qū),接下來要完成一個物理的構(gòu)建,使得屬于不同分區(qū)的數(shù)據(jù)存儲到不同的物理文件上去 .a.創(chuàng)建文件組創(chuàng)建多個文件組主要是

7、為了獲得好的 I/O 平衡。一般情況下，文件組數(shù)最好與分區(qū)數(shù)相同，并且這些文件組通常位于不同的磁盤上。每個文件組可以由一個或多個文件構(gòu)成，而每個分區(qū)必須映射到一個文件組。一個文件組可以由多個分區(qū)使用。為了更好地管理數(shù)據(jù)（例如，為了獲得更精確的備份控制），對分區(qū)表應(yīng)進(jìn)行設(shè)計，以便只有相關(guān)數(shù)據(jù)或邏輯分組的數(shù)據(jù)位于同一個文件組中。使用ALTER DATABAS璐加邏輯文件組名：- -File group for 2006ALTER DATABASE MES ADD FILEGROUP Teaching200609ALTER DATABASE MES ADD FILEGROUP Teaching200

8、610ALTER DATABASE MES ADD FILEGROUP Teaching200611ALTER DATABASE MES ADD FILEGROUP Teaching200612- -File group for 2007ALTER DATABASE MES ADD FILEGROUP Teaching200701ALTER DATABASE MES ADD FILEGROUP Teaching200702ALTER DATABASE MES ADD FILEGROUP Teaching200703ALTER DATABASE MES ADD FILEGROUP Teachin

9、g200704ALTER DATABASE MES ADD FILEGROUP Teaching200705ALTER DATABASE MES ADD FILEGROUP Teaching200706ALTER DATABASE MES ADD FILEGROUP Teaching2007ALTER DATABASE MES ADD FILEGROUP Teaching200708ALTER DATABASE MES ADD FILEGROUP Teaching200709ALTER DATABASE MES ADD FILEGROUP Teaching200710ALTER DATABAS

10、E MES ADD FILEGROUP Teaching200711ALTER DATABASE MES ADD FILEGROUP Teaching200712b.創(chuàng)建物理文件，在這里,為了方便起見，把每個物理文件放到了一個單獨(dú)的文 件組里面 .- -Add file for 2006ALTER DATABASE MESADD FILE(NAME=N'Teaching200609',FILENAME =N'D:MyDataMyLuTeaching200609.ndf',SIZE =5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)TO

11、FILEGROUP Teaching200609ALTER DATABASE MESADD FILE(NAME=N'Teaching200610',FILENAME =N'D:MyDataMyLuTeaching200610.ndf',SIZE =5MB,MAXSIZE = 100MB,FILEGROWTH = 5MB)TO FILEGROUP Teaching200610ALTER DATABASE MESADD FILE(NAME=N'Teaching200611',FILENAME =N'D:MyDataMyLuTeaching20

12、0611.ndf',SIZE =5MB,MAXSIZE =100MB,FILEGROWTH = 5MB)TO FILEGROUP Teaching200611三、創(chuàng)建完分區(qū)函數(shù),接下來就要建立分區(qū)架構(gòu),用來將概念上的分區(qū)和文件組 (物理文件)關(guān)聯(lián)起來。創(chuàng)建分區(qū)函數(shù)后，必須將其與分區(qū)方案相關(guān)聯(lián)，以便將分區(qū)指向至特定的文件組。就是定義實際存放數(shù)據(jù)的媒體與各數(shù)據(jù)塊的對應(yīng)關(guān)系。多個數(shù)據(jù)表可以共用相同的數(shù)據(jù)分區(qū)函數(shù)，一般不共用相同的數(shù)據(jù)分區(qū)方案?？梢酝ㄟ^不同的分區(qū)方案，使用相同的分區(qū)函數(shù)，使不同的數(shù)據(jù)表有相同的分區(qū)條件，但存放在不同的媒介上。創(chuàng)建分區(qū)方案的代碼如下：CREATE PARTION

13、SCHEME FiveYearDateRangePSchemeASPARTION FiveYearDateRangePFN TO( Teaching200609,Teaching200610,Teaching200611,Teaching200612,Teaching200701,Teaching200702,Teaching200703,Teaching200704,Teaching200705,Teaching200706,Teaching2007,Teaching200708,Teaching200709,Teaching200710,Teaching200711,Teaching200

14、712,PRIMARY )GO四、分區(qū)表的基礎(chǔ)架構(gòu)到此就完成了,接下來就要對原有的數(shù)據(jù)表建立分區(qū)SQLSERVE族根據(jù)分區(qū)鍵值的不同，通過分區(qū)函數(shù)的定義將數(shù)據(jù)放到相應(yīng)的分區(qū)。從而把分區(qū)函數(shù)、分區(qū)方案和分區(qū)表三者有機(jī)的結(jié)合起來。創(chuàng)建分區(qū)表的代碼 如下：ALTER TABLE REPORT_ITEMVALUESADDPRIMARY KEY NONCLUSTERED(reportid,rq,itemid)ON FiveYearDateRangePScheme(rq)GO五、維護(hù)分區(qū)分區(qū)的維護(hù)主要設(shè)計分區(qū)的添加、減少、合并和在分區(qū)間轉(zhuǎn)換。可以通過ALTER PARTION FUNCTIONS項 SPLIT MERGED口 ALTER TABL的選項 SWITCH 來實現(xiàn)。SPLI檢多增加一個分區(qū)，而 MEGRE合并或者減少分區(qū)，SWITCH則 是邏輯地在組間轉(zhuǎn)換分區(qū)。在建立分區(qū)表的時候注意一下分區(qū)鍵的選擇。接下來呢，就可以往分區(qū)表里面插入數(shù)據(jù),SQL SERVER根據(jù)分區(qū)鍵的不同將 數(shù)據(jù)放到相應(yīng)的分區(qū) ,我們可以通過如下語句來查看數(shù)據(jù)存在那個分區(qū)中 :select $partion.FiveYearDateRangePFN(teachingdate),teachingdate,*from objteach