SQL Server 2005 分區(qū)模板與實例
一、場景
這一段時間使用SQL Server 2005 對幾個系統(tǒng)進(jìn)行表分區(qū)，這幾個系統(tǒng)都有一些特點，比如數(shù)據(jù)庫某張表持續(xù)增長，給數(shù)據(jù)庫帶來了很大的壓力。
現(xiàn)在假如提供一臺新的服務(wù)器，那么我們應(yīng)該如何規(guī)劃這個數(shù)據(jù)庫呢?應(yīng)該如何進(jìn)行最小宕機(jī)時間的數(shù)據(jù)庫轉(zhuǎn)移呢?如果規(guī)劃數(shù)據(jù)庫呢?
二、環(huán)境準(zhǔn)備
要搭建一個好的系統(tǒng)，首先要從硬件和操作系統(tǒng)出發(fā)，好的設(shè)置和好的規(guī)劃是高性能的前提，下面我就來說說自己的一些看法，歡迎大家提出異議;
1) 對磁盤做RAID0(比如3*300G)，必要時可以考慮RAID5、RAID10;
2) 使用兩張千兆網(wǎng)卡，一張用于外網(wǎng)，一張用于內(nèi)網(wǎng)(這也需要千兆路由器的配合);
3) 邏輯分區(qū)C為系統(tǒng)分區(qū)(50G)，邏輯分區(qū)D為程序安裝分區(qū)(50G)，邏輯分區(qū)E為數(shù)據(jù)庫文件邏輯分區(qū);
4) 安裝Microsoft Windows Server 2003, Enterprise Edition SP2(x64)操作系統(tǒng);
5) D盤格式化的時候使用默認(rèn)分配單元大小，E盤格式為64k分配單元;
6) 安裝Microsoft SQL Server 2005(x64)數(shù)據(jù)庫;
7) 在我們網(wǎng)上鄰居-本地連接-屬性-Microsoft網(wǎng)絡(luò)的文件和打印機(jī)共享-***化網(wǎng)絡(luò)應(yīng)用程序數(shù)據(jù)吞吐量(勾選上);
8) 運行-gpedit.msc-Windows設(shè)置-安全設(shè)置-本地策略-用戶權(quán)限分配-內(nèi)存中鎖定頁面-設(shè)置用戶組(比如Administrators);
9) 運行-services.msc，設(shè)置啟動類型為手動，并且停止除了SQL Server (MSSQLSERVER)之外的SQL Server服務(wù)，除非你對某些服務(wù)需要啟動，比如作業(yè)、全文索引;
10) 設(shè)置虛擬內(nèi)存大小，我通常設(shè)置為4096MB-8192MB;
三、前期工作
在進(jìn)行分區(qū)之前，我們首先要分析這個表的數(shù)據(jù)量(行數(shù))有多少?這個表的存儲空間(物理存儲)有多少?需要確定分區(qū)文件多大為合理?還需要確認(rèn)我們按照表中哪個字段進(jìn)行分區(qū)?后期的維護(hù)是否需要對分區(qū)進(jìn)行管理(比如交換分區(qū)進(jìn)行數(shù)據(jù)歸檔等)?
假設(shè)我們決定以自增ID作為分區(qū)字段(其實應(yīng)該叫分區(qū)數(shù)值類型)，我們就可以使用上面的行數(shù)和存儲空間來計算我們的分區(qū)邊界值了，因為我們確認(rèn)了分區(qū)文件的大小。比如我們表A記錄為：1.5億，占用空間為：700G，如果我們可以接受的文件大小為10G(這個要根據(jù)如果需要做交換分區(qū)和一些存儲空間、硬盤等信息確認(rèn)的)，那么我們的分區(qū)值可以這樣計算：1.5億/(700G/10G)≈200W，也就是：200W，400W，600W等等;
分區(qū)文件在創(chuàng)建的時候就應(yīng)該初始化為包含分區(qū)邊界值數(shù)據(jù)大小，比如上面的分區(qū)文件可以設(shè)置為10G，這樣就不用重新分配空間了。也可以使用定量增長，比如2048MB。
在設(shè)置自增ID為分區(qū)字段，那么通常我們會讓ID成為聚集索引，而且設(shè)置填充因子為100%，這樣我們的數(shù)據(jù)頁就不會有空白了。
如果后期的維護(hù)需要對分區(qū)進(jìn)行管理，比如交換分區(qū)進(jìn)行數(shù)據(jù)歸檔，交換分區(qū)是需要索引對齊的，而索引對齊有兩種：索引對齊;按存儲位置對齊的表。
索引對齊：假如你想讓數(shù)據(jù)與索引分開到不同的文件，可以使用兩個不同的分區(qū)方案，但是使用同一個分區(qū)函數(shù)，這樣就把索引分開了。(如圖1)
存儲位置對齊：創(chuàng)建非聚集索引的時候設(shè)置【數(shù)據(jù)空間規(guī)范】，兩個索引對象可以使用相同的分區(qū)架構(gòu)，并且具有相同分區(qū)鍵的所有數(shù)據(jù)行***將位于同一個文件組中。這就叫存儲位置對齊。(數(shù)據(jù)和索引在同一個文件中)(如圖2)
（圖1）
（圖2）
#p#
四、分區(qū)步驟
下面提供了創(chuàng)建分區(qū)的代碼，其中包括模板還有例子（Ext），這里最主要是注意一些命名規(guī)范，希望對大家有用：
步驟1：為MyDataBase數(shù)據(jù)庫創(chuàng)建2個文件組，如果你不想用PRIMARY作為分區(qū)，你可以創(chuàng)建多一個文件組，文件組=分區(qū)值個數(shù)+1；
- --1.創(chuàng)建文件組
- ALTER DATABASE [數(shù)據(jù)庫名]
- ADD FILEGROUP [FG_表名_字段名_分區(qū)編號]
- --Ext
- ALTER DATABASE [MyDataBase]
- ADD FILEGROUP [FG_User_Id_1]
- ALTER DATABASE [MyDataBase]
- ADD FILEGROUP [FG_User_Id_2]
步驟2：為MyDataBase數(shù)據(jù)庫創(chuàng)建2個文件，文件數(shù)>=文件組數(shù)，一個文件不能屬于兩個不同的分組中，一個分組可以包含多個文件，注意初始化大?。ǜ鶕?jù)需求）和增長大小（百分比和字節(jié)數(shù)）；
- --2.創(chuàng)建文件
- ALTER DATABASE [數(shù)據(jù)庫名]
- ADD FILE
- (NAME = N'FG_表名_字段名_分區(qū)編號_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分區(qū)編號_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
- TO FILEGROUP [FG_表名_字段名_分區(qū)編號];
- ALTER DATABASE [數(shù)據(jù)庫名]
- ADD FILE
- (NAME = N'FG_表名_字段名_分區(qū)編號_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分區(qū)編號_data.ndf',SIZE = 30720KB , FILEGROWTH = 10240KB )
- TO FILEGROUP [FG_表名_字段名_分區(qū)編號];
- --Ext
- ALTER DATABASE [MyDataBase]
- ADD FILE
- (NAME = N'FG_User_Id_1_data',FILENAME = N'E:\DataBase\FG_User_Id_1_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
- TO FILEGROUP [FG_User_Id_1];
- ALTER DATABASE [MyDataBase]
- ADD FILE
- (NAME = N'FG_User_Id_2_data',FILENAME = N'E:\DataBase\FG_User_Id_2_data.ndf',SIZE = 30MB , FILEGROWTH = 10MB )
- TO FILEGROUP [FG_User_Id_2];
步驟3：為MyDataBase數(shù)據(jù)庫創(chuàng)建分區(qū)函數(shù)，分區(qū)值需要根據(jù)需求而變化，前面已經(jīng)做了示范了，這里使用了右分區(qū)，關(guān)于邊界值的理解可以參考：解惑：對SQL Server分區(qū)進(jìn)行合并（刪除）；
- --3.創(chuàng)建分區(qū)函數(shù)
- CREATE PARTITION FUNCTION
- Fun_表名_字段名(數(shù)據(jù)類型) AS
- RANGE RIGHT
- FOR VALUES(邊界值列表)
- --Ext
- CREATE PARTITION FUNCTION
- Fun_User_Id(INT) AS
- RANGE RIGHT
- FOR VALUES(100000000,200000000)
步驟4：為MyDataBase數(shù)據(jù)庫創(chuàng)建分區(qū)方案，因為前面只創(chuàng)建了2個文件組，所以這里使用了PRIMARY默認(rèn)的文件組來保存邊界值之外的數(shù)據(jù)，如果你想創(chuàng)建多一個文件組也可以，如下面的Ext1與Ext2；
- --4.創(chuàng)建分區(qū)方案
- CREATE PARTITION SCHEME
- Sch_表名_字段名AS
- PARTITION Fun_表名_字段名
- TO(文件組列表)
- --Ext1
- CREATE PARTITION SCHEME
- Sch_User_Id AS
- PARTITION Fun_User_Id
- TO([FG_User_Id_1],[FG_User_Id_2],[FG_User_Id_3])
- --Ext2
- CREATE PARTITION SCHEME
- Sch_User_Id AS
- PARTITION Fun_User_Id
- TO([FG_User_Id_1],[FG_User_Id_2],[PRIMARY])
步驟5：為MyDataBase數(shù)據(jù)庫創(chuàng)建一個名為User的表，這個表有3個字段，Id是自增標(biāo)識，并在Id字段中創(chuàng)建聚集索引，填充因子為100%，使用上面創(chuàng)建的Sch_User_Id分區(qū)方案，創(chuàng)建有不同的創(chuàng)建方式，如Ext1、Ext2、Ext3；
- --5.創(chuàng)建表
- --Ext1
- CREATE TABLE [dbo].[User](
- [Id] [int] IDENTITY(1,1) NOT NULL,
- [UserName] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NULL,
- [Age] [int] NULL CONSTRAINT [DF_User_Age] DEFAULT ((0)),
- CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
- (
- [Id] ASC
- )WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id](Id)
- ) ON [Sch_User_Id]([Id])
- --Ext2
- CREATE TABLE [dbo].[User](
- [Id] [int] IDENTITY(1,1) NOT NULL,
- [UserName] [nvarchar](256) COLLATE Chinese_PRC_CI_AS NULL,
- [Age] [int] NULL CONSTRAINT [DF_User_Age] DEFAULT ((0)),
- ) ON [Sch_User_Id]([Id])
- GO
- CREATE CLUSTERED INDEX [IX_User_Id] ON dbo.[User]
- (
- [Id]
- ) WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id](Id)
- GO
- --Ext3
- ALTER TABLE dbo.[User] ADD CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
- (
- Id
- ) WITH( PAD_INDEX = ON, FILLFACTOR = 100, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id](Id)
- GO
步驟6：為User表創(chuàng)建測試數(shù)據(jù)，這里我就模擬從一個存在的OldUser表中導(dǎo)入數(shù)據(jù)到分區(qū)User表，這里需要注意SET IDENTITY_INSERT 表ON 這個選項；
- --6.導(dǎo)入數(shù)據(jù)
- SET IDENTITY_INSERT 表ON
- INSERT INTO dbo.表
- ( [Id]
- ,[UserName]
- ,[Age])
- SELECT
- [Id]
- ,[UserName]
- ,[Age]
- FROM dbo.[OldUser](nolock) WHERE 條件
- SET IDENTITY_INSERT 表OFF
- --Ext
- SET IDENTITY_INSERT [User] ON
- INSERT INTO dbo.[User]
- ( [Id]
- ,[UserName]
- ,[Age])
- SELECT
- [Id]
- ,[UserName]
- ,[Age]
- FROM dbo.[OldUser](nolock) WHERE Id <= 1 and Id > 100000000
- SET IDENTITY_INSERT [User] OFF
步驟7：當(dāng)需要查詢分區(qū)User表記錄所處的分區(qū)情況時，可以使用下面的SQL；
- --7.分區(qū)函數(shù)的記錄數(shù)
- SELECT $PARTITION.分區(qū)函數(shù)(字段) AS Partition_num,
- MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num
- FROM dbo.[User]
- GROUP BY $PARTITION.分區(qū)函數(shù)(字段)
- ORDER BY $PARTITION.分區(qū)函數(shù)(字段);
- --Ext
- SELECT $PARTITION.Fun_User_Id(Id) AS Partition_num,
- MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num
- FROM dbo.[User]
- GROUP BY $PARTITION.Fun_User_Id(Id)
- ORDER BY $PARTITION.Fun_User_Id(Id);
步驟8：其實到這里實例應(yīng)該結(jié)束了吧？在網(wǎng)上看到的所有關(guān)于分區(qū)的文章中貌似都是在這里結(jié)束了，但是還有一點我需要指出：如果創(chuàng)建存儲位置對齊的索引呢？也許通過上面的圖2你已經(jīng)了解了什么是存儲位置對齊，如果還不清楚可以查看：SQL Server 2005 中的分區(qū)表和索引（http://msdn.microsoft.com/zh-cn/library/ms345146%28SQL.90%29.aspx），其實很簡單，如Ext所示，但是主要是理解它的原理和作用；
- --8.創(chuàng)建非聚集索引
- CREATE NONCLUSTERED INDEX IX_表_字段ON dbo.表
- (
- 字段
- ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id]([Id])
- GO
- --Ext
- CREATE NONCLUSTERED INDEX IX_User_UserName ON dbo.[User]
- (
- UserName
- ) WITH( PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id]([Id])
- GO
步驟9：還不想結(jié)束？呵呵，這個包含性索引的創(chuàng)建就當(dāng)是買8送1吧；
- --9.創(chuàng)建包含性索引
- CREATE NONCLUSTERED INDEX [IX_User_UA_Include] ON dbo.[User]
- (
- UserName,
- Age
- )
- INCLUDE ([Id])
- WITH( PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Sch_User_Id]([Id])
- GO
五、注意
上面的代碼中我們把文件與文件組是一 一對應(yīng)起來的，如果我們想更小話文件的話，我們可以在文件組下面創(chuàng)建多個文件，并且設(shè)置文件的***值（MAXSIZE），這樣就會把數(shù)據(jù)分配到不同的物理文件上，但是有一點需要注意，那就是它是一個個的使用文件的，當(dāng)一個用完了才會使用下一個的。
日志文件也可以像上面的做法來做，這樣收縮日志的時候比較方便？刪除日志文件比較方便？
有一點我們可能會混淆，那就是既然可以在一個文件組里面創(chuàng)建多個文件，那么這個跟我們按照Id的自增來分布數(shù)據(jù)是不是等效的？這是有不同的，因為從創(chuàng)建分區(qū)方案的時候我們就發(fā)現(xiàn)文件組和分區(qū)邊界值是對應(yīng)的，所以一段分區(qū)值這些數(shù)據(jù)是分配到以文件組為單位的存儲單元中，并不是文件。
補(bǔ)充一下，那就是在文件組下面創(chuàng)建的文件只能按照設(shè)置的***值（MAXSIZE）來區(qū)分?jǐn)?shù)據(jù)，并不能按照值來區(qū)分，這也算一個不同點吧。
六、后記
如果這些表是寫的多，讀的少：類似記錄日志，我們還有一些方案可以進(jìn)行處理，比如SQL Server 2008的行壓縮、頁壓縮等；比如MySQL的IASM數(shù)據(jù)引擎；或者是使用MySQL的master/slave負(fù)載均衡。
原文鏈接：http://www.cnblogs.com/gaizai/archive/2010/12/31/1923232.html
【編輯推薦】
