建站
咨詢
售后
建站咨詢
新聞中心
一、場(chǎng)景
這一段時(shí)間使用SQL Server 2005 對(duì)幾個(gè)系統(tǒng)進(jìn)行表分區(qū)，這幾個(gè)系統(tǒng)都有一些特點(diǎn)，比如數(shù)據(jù)庫(kù)某張表持續(xù)增長(zhǎng)，給數(shù)據(jù)庫(kù)帶來(lái)了很大的壓力。
現(xiàn)在假如提供一臺(tái)新的服務(wù)器，那么我們應(yīng)該如何規(guī)劃這個(gè)數(shù)據(jù)庫(kù)呢?應(yīng)該如何進(jìn)行最小宕機(jī)時(shí)間的數(shù)據(jù)庫(kù)轉(zhuǎn)移呢?如果規(guī)劃數(shù)據(jù)庫(kù)呢?
二、環(huán)境準(zhǔn)備
要搭建一個(gè)好的系統(tǒng)，首先要從硬件和操作系統(tǒng)出發(fā)，好的設(shè)置和好的規(guī)劃是高性能的前提，下面我就來(lái)說(shuō)說(shuō)自己的一些看法，歡迎大家提出異議;
1) 對(duì)磁盤(pán)做RAID0(比如3*300G)，必要時(shí)可以考慮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ù)庫(kù)文件邏輯分區(qū);
4) 安裝Microsoft Windows Server 2003, Enterprise Edition SP2(x64)操作系統(tǒng);
5) D盤(pán)格式化的時(shí)候使用默認(rèn)分配單元大小，E盤(pán)格式為64k分配單元;
6) 安裝Microsoft SQL Server 2005(x64)數(shù)據(jù)庫(kù);
7) 在我們網(wǎng)上鄰居-本地連接-屬性-Microsoft網(wǎng)絡(luò)的文件和打印機(jī)共享-***化網(wǎng)絡(luò)應(yīng)用程序數(shù)據(jù)吞吐量(勾選上);
8) 運(yùn)行-gpedit.msc-Windows設(shè)置-安全設(shè)置-本地策略-用戶權(quán)限分配-內(nèi)存中鎖定頁(yè)面-設(shè)置用戶組(比如Administrators);
9) 運(yùn)行-services.msc，設(shè)置啟動(dòng)類型為手動(dòng)，并且停止除了SQL Server (MSSQLSERVER)之外的SQL Server服務(wù)，除非你對(duì)某些服務(wù)需要啟動(dòng)，比如作業(yè)、全文索引;
10) 設(shè)置虛擬內(nèi)存大小，我通常設(shè)置為4096MB-8192MB;
三、前期工作
在進(jìn)行分區(qū)之前，我們首先要分析這個(gè)表的數(shù)據(jù)量(行數(shù))有多少?這個(gè)表的存儲(chǔ)空間(物理存儲(chǔ))有多少?需要確定分區(qū)文件多大為合理?還需要確認(rèn)我們按照表中哪個(gè)字段進(jìn)行分區(qū)?后期的維護(hù)是否需要對(duì)分區(qū)進(jìn)行管理(比如交換分區(qū)進(jìn)行數(shù)據(jù)歸檔等)?
假設(shè)我們決定以自增ID作為分區(qū)字段(其實(shí)應(yīng)該叫分區(qū)數(shù)值類型)，我們就可以使用上面的行數(shù)和存儲(chǔ)空間來(lái)計(jì)算我們的分區(qū)邊界值了，因?yàn)槲覀兇_認(rèn)了分區(qū)文件的大小。比如我們表A記錄為：1.5億，占用空間為：700G，如果我們可以接受的文件大小為10G(這個(gè)要根據(jù)如果需要做交換分區(qū)和一些存儲(chǔ)空間、硬盤(pán)等信息確認(rèn)的)，那么我們的分區(qū)值可以這樣計(jì)算：1.5億/(700G/10G)≈200W，也就是：200W，400W，600W等等;
分區(qū)文件在創(chuàng)建的時(shí)候就應(yīng)該初始化為包含分區(qū)邊界值數(shù)據(jù)大小，比如上面的分區(qū)文件可以設(shè)置為10G，這樣就不用重新分配空間了。也可以使用定量增長(zhǎng)，比如2048MB。
在設(shè)置自增ID為分區(qū)字段，那么通常我們會(huì)讓ID成為聚集索引，而且設(shè)置填充因子為100%，這樣我們的數(shù)據(jù)頁(yè)就不會(huì)有空白了。
如果后期的維護(hù)需要對(duì)分區(qū)進(jìn)行管理，比如交換分區(qū)進(jìn)行數(shù)據(jù)歸檔，交換分區(qū)是需要索引對(duì)齊的，而索引對(duì)齊有兩種：索引對(duì)齊;按存儲(chǔ)位置對(duì)齊的表。
索引對(duì)齊：假如你想讓數(shù)據(jù)與索引分開(kāi)到不同的文件，可以使用兩個(gè)不同的分區(qū)方案，但是使用同一個(gè)分區(qū)函數(shù)，這樣就把索引分開(kāi)了。(如圖1)
存儲(chǔ)位置對(duì)齊：創(chuàng)建非聚集索引的時(shí)候設(shè)置【數(shù)據(jù)空間規(guī)范】，兩個(gè)索引對(duì)象可以使用相同的分區(qū)架構(gòu)，并且具有相同分區(qū)鍵的所有數(shù)據(jù)行***將位于同一個(gè)文件組中。這就叫存儲(chǔ)位置對(duì)齊。(數(shù)據(jù)和索引在同一個(gè)文件中)(如圖2)
（圖1）
（圖2）
#p#
四、分區(qū)步驟
下面提供了創(chuàng)建分區(qū)的代碼，其中包括模板還有例子（Ext），這里最主要是注意一些命名規(guī)范，希望對(duì)大家有用：
步驟1：為MyDataBase數(shù)據(jù)庫(kù)創(chuàng)建2個(gè)文件組，如果你不想用PRIMARY作為分區(qū)，你可以創(chuàng)建多一個(gè)文件組，文件組=分區(qū)值個(gè)數(shù)+1；
- --1.創(chuàng)建文件組
- ALTER DATABASE [數(shù)據(jù)庫(kù)名]
- ADD FILEGROUP [FG_表名_字段名_分區(qū)編號(hào)]
- --Ext
- ALTER DATABASE [MyDataBase]
- ADD FILEGROUP [FG_User_Id_1]
- ALTER DATABASE [MyDataBase]
- ADD FILEGROUP [FG_User_Id_2]
步驟2：為MyDataBase數(shù)據(jù)庫(kù)創(chuàng)建2個(gè)文件，文件數(shù)>=文件組數(shù)，一個(gè)文件不能屬于兩個(gè)不同的分組中，一個(gè)分組可以包含多個(gè)文件，注意初始化大小（根據(jù)需求）和增長(zhǎng)大?。ò俜直群妥止?jié)數(shù)）；
- --2.創(chuàng)建文件
- ALTER DATABASE [數(shù)據(jù)庫(kù)名]
- ADD FILE
- (NAME = N'FG_表名_字段名_分區(qū)編號(hào)_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分區(qū)編號(hào)_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
- TO FILEGROUP [FG_表名_字段名_分區(qū)編號(hào)];
- ALTER DATABASE [數(shù)據(jù)庫(kù)名]
- ADD FILE
- (NAME = N'FG_表名_字段名_分區(qū)編號(hào)_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分區(qū)編號(hào)_data.ndf',SIZE = 30720KB , FILEGROWTH = 10240KB )
- TO FILEGROUP [FG_表名_字段名_分區(qū)編號(hào)];
- --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ù)庫(kù)創(chuàng)建分區(qū)函數(shù)，分區(qū)值需要根據(jù)需求而變化，前面已經(jīng)做了示范了，這里使用了右分區(qū)，關(guān)于邊界值的理解可以參考：解惑：對(duì)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ù)庫(kù)創(chuàng)建分區(qū)方案，因?yàn)榍懊嬷粍?chuàng)建了2個(gè)文件組，所以這里使用了PRIMARY默認(rèn)的文件組來(lái)保存邊界值之外的數(shù)據(jù)，如果你想創(chuàng)建多一個(gè)文件組也可以，如下面的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ù)庫(kù)創(chuàng)建一個(gè)名為User的表，這個(gè)表有3個(gè)字段，Id是自增標(biāo)識(shí)，并在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)建測(cè)試數(shù)據(jù)，這里我就模擬從一個(gè)存在的OldUser表中導(dǎo)入數(shù)據(jù)到分區(qū)User表，這里需要注意SET IDENTITY_INSERT 表ON 這個(gè)選項(xiàng)；
- --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ū)情況時(shí)，可以使用下面的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：其實(shí)到這里實(shí)例應(yīng)該結(jié)束了吧？在網(wǎng)上看到的所有關(guān)于分區(qū)的文章中貌似都是在這里結(jié)束了，但是還有一點(diǎn)我需要指出：如果創(chuàng)建存儲(chǔ)位置對(duì)齊的索引呢？也許通過(guò)上面的圖2你已經(jīng)了解了什么是存儲(chǔ)位置對(duì)齊，如果還不清楚可以查看：SQL Server 2005 中的分區(qū)表和索引（http://msdn.microsoft.com/zh-cn/library/ms345146%28SQL.90%29.aspx），其實(shí)很簡(jiǎn)單，如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é)束？呵呵，這個(gè)包含性索引的創(chuàng)建就當(dāng)是買(mǎi)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
五、注意
上面的代碼中我們把文件與文件組是一 一對(duì)應(yīng)起來(lái)的，如果我們想更小話文件的話，我們可以在文件組下面創(chuàng)建多個(gè)文件，并且設(shè)置文件的***值（MAXSIZE），這樣就會(huì)把數(shù)據(jù)分配到不同的物理文件上，但是有一點(diǎn)需要注意，那就是它是一個(gè)個(gè)的使用文件的，當(dāng)一個(gè)用完了才會(huì)使用下一個(gè)的。
日志文件也可以像上面的做法來(lái)做，這樣收縮日志的時(shí)候比較方便？刪除日志文件比較方便？
有一點(diǎn)我們可能會(huì)混淆，那就是既然可以在一個(gè)文件組里面創(chuàng)建多個(gè)文件，那么這個(gè)跟我們按照Id的自增來(lái)分布數(shù)據(jù)是不是等效的？這是有不同的，因?yàn)閺膭?chuàng)建分區(qū)方案的時(shí)候我們就發(fā)現(xiàn)文件組和分區(qū)邊界值是對(duì)應(yīng)的，所以一段分區(qū)值這些數(shù)據(jù)是分配到以文件組為單位的存儲(chǔ)單元中，并不是文件。
補(bǔ)充一下，那就是在文件組下面創(chuàng)建的文件只能按照設(shè)置的***值（MAXSIZE）來(lái)區(qū)分?jǐn)?shù)據(jù)，并不能按照值來(lái)區(qū)分，這也算一個(gè)不同點(diǎn)吧。
六、后記
如果這些表是寫(xiě)的多，讀的少：類似記錄日志，我們還有一些方案可以進(jìn)行處理，比如SQL Server 2008的行壓縮、頁(yè)壓縮等；比如MySQL的IASM數(shù)據(jù)引擎；或者是使用MySQL的master/slave負(fù)載均衡。
當(dāng)前標(biāo)題：SQLServer2005分區(qū)模板與實(shí)例
當(dāng)前地址：http://www.5511xx.com/article/ccccosj.html
