SQL Server 2005 分區模板與實例

作者: 聽風吹雨來源: 博客園發布時間: 2011-01-03 21:36 閱讀: 757 次推薦: 0 原文鏈接 [收藏]

　　一、場景

　　這一段時間使用SQL Server 2005 對幾個系統進行表分區，這幾個系統都有一些特點，比如數據庫某張表持續增長，給數據庫帶來了很大的壓力。

　　現在假如提供一臺新的服務器，那么我們應該如何規劃這個數據庫呢？應該如何進行最小宕機時間的數據庫轉移呢？如果規劃數據庫呢？

　　二、環境準備

　　要搭建一個好的系統，首先要從硬件和操作系統出發，好的設置和好的規劃是高性能的前提，下面我就來說說自己的一些看法，歡迎大家提出異議；

　　1) 對磁盤做RAID0（比如3*300G），必要時可以考慮RAID5、RAID10；

　　2) 使用兩張千兆網卡，一張用于外網，一張用于內網（這也需要千兆路由器的配合）；

　　3) 邏輯分區C為系統分區（50G），邏輯分區D為程序安裝分區（50G），邏輯分區E為數據庫文件邏輯分區；

　　4) 安裝Microsoft Windows Server 2003, Enterprise Edition SP2（x64）操作系統；

　　5) D盤格式化的時候使用默認分配單元大小，E盤格式為64k分配單元；

　　6) 安裝Microsoft SQL Server 2005（x64）數據庫；

　　7) 在我們網上鄰居-本地連接-屬性-Microsoft網絡的文件和打印機共享-最大化網絡應用程序數據吞吐量（勾選上）；

　　8) 運行-gpedit.msc-Windows設置-安全設置-本地策略-用戶權限分配-內存中鎖定頁面-設置用戶組（比如Administrators）；

　　9) 運行-services.msc，設置啟動類型為手動，并且停止除了SQL Server (MSSQLSERVER)之外的SQL Server服務，除非你對某些服務需要啟動，比如作業、全文索引；

　　10) 設置虛擬內存大小，我通常設置為4096MB-8192MB；

　　三、前期工作

　　在進行分區之前，我們首先要分析這個表的數據量（行數）有多少？這個表的存儲空間（物理存儲）有多少？需要確定分區文件多大為合理？還需要確認我們按照表中哪個字段進行分區？后期的維護是否需要對分區進行管理（比如交換分區進行數據歸檔等）？

　　假設我們決定以自增ID作為分區字段（其實應該叫分區數值類型），我們就可以使用上面的行數和存儲空間來計算我們的分區邊界值了，因為我們確認了分區文件的大小。比如我們表A記錄為：1.5億，占用空間為：700G，如果我們可以接受的文件大小為10G（這個要根據如果需要做交換分區和一些存儲空間、硬盤等信息確認的），那么我們的分區值可以這樣計算：1.5億/（700G/10G）≈200W，也就是：200W，400W，600W等等；

　　分區文件在創建的時候就應該初始化為包含分區邊界值數據大小，比如上面的分區文件可以設置為10G，這樣就不用重新分配空間了。也可以使用定量增長，比如2048MB。

　　在設置自增ID為分區字段，那么通常我們會讓ID成為聚集索引，而且設置填充因子為100%，這樣我們的數據頁就不會有空白了。

　　如果后期的維護需要對分區進行管理，比如交換分區進行數據歸檔，交換分區是需要索引對齊的，而索引對齊有兩種：索引對齊；按存儲位置對齊的表。

　　索引對齊：假如你想讓數據與索引分開到不同的文件，可以使用兩個不同的分區方案，但是使用同一個分區函數，這樣就把索引分開了。（如圖1）

　　存儲位置對齊：創建非聚集索引的時候設置【數據空間規范】，兩個索引對象可以使用相同的分區架構，并且具有相同分區鍵的所有數據行最后將位于同一個文件組中。這就叫存儲位置對齊。（數據和索引在同一個文件中）（如圖2）

（圖1）

（圖2）

　　四、分區步驟

　　下面提供了創建分區的代碼，其中包括模板還有例子（Ext），這里最主要是注意一些命名規范，希望對大家有用：

　　步驟1：為MyDataBase數據庫創建2個文件組，如果你不想用PRIMARY作為分區，你可以創建多一個文件組，文件組=分區值個數+1；

　　--1.創建文件組

ALTER DATABASE [數據庫名]
ADD FILEGROUP [FG_表名_字段名_分區編號]
--Ext
ALTER DATABASE [MyDataBase]
ADD FILEGROUP [FG_User_Id_1]
ALTER DATABASE [MyDataBase]
ADD FILEGROUP [FG_User_Id_2]

　　步驟2：為MyDataBase數據庫創建2個文件，文件數>=文件組數，一個文件不能屬于兩個不同的分組中，一個分組可以包含多個文件，注意初始化大小（根據需求）和增長大小（百分比和字節數）；

　　--2.創建文件

ALTER DATABASE [數據庫名]
ADD FILE
(NAME = N'FG_表名_字段名_分區編號_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分區編號_data.ndf',SIZE = 30MB, FILEGROWTH = 10% )
TO FILEGROUP [FG_表名_字段名_分區編號];
ALTER DATABASE [數據庫名]
ADD FILE
(NAME = N'FG_表名_字段名_分區編號_data',FILENAME = N'E:\DataBase\FG_表名_字段名_分區編號_data.ndf',SIZE = 30720KB , FILEGROWTH = 10240KB )
TO FILEGROUP [FG_表名_字段名_分區編號];
--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數據庫創建分區函數，分區值需要根據需求而變化，前面已經做了示范了，這里使用了右分區，關于邊界值的理解可以參考：SQL Server 合并（刪除）分區解惑；

　　--3.創建分區函數

CREATE PARTITION FUNCTION
Fun_表名_字段名(數據類型) AS
RANGE RIGHT
FOR VALUES(邊界值列表)
--Ext
CREATE PARTITION FUNCTION
Fun_User_Id(INT) AS
RANGE RIGHT
FOR VALUES(100000000,200000000)

　　步驟4：為MyDataBase數據庫創建分區方案，因為前面只創建了2個文件組，所以這里使用了PRIMARY默認的文件組來保存邊界值之外的數據，如果你想創建多一個文件組也可以，如下面的Ext1與Ext2；

　　--4.創建分區方案

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數據庫創建一個名為User的表，這個表有3個字段，Id是自增標識，并在Id字段中創建聚集索引，填充因子為100%，使用上面創建的Sch_User_Id分區方案，創建有不同的創建方式，如Ext1、Ext2、Ext3；

　　--5.創建表

--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表創建測試數據，這里我就模擬從一個存在的OldUser表中導入數據到分區User表，這里需要注意SET IDENTITY_INSERT 表ON 這個選項；

　　--6.導入數據

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：當需要查詢分區User表記錄所處的分區情況時，可以使用下面的SQL；

　　--7.分區函數的記錄數

SELECT $PARTITION.分區函數(字段) AS Partition_num,
MIN(Id) AS Min_value,MAX(Id) AS Max_value,COUNT(1) AS Record_num
FROM dbo.[User]
GROUP BY $PARTITION.分區函數(字段)
ORDER BY $PARTITION.分區函數(字段);
--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：其實到這里實例應該結束了吧？在網上看到的所有關于分區的文章中貌似都是在這里結束了，但是還有一點我需要指出：如果創建存儲位置對齊的索引呢？也許通過上面的圖2你已經了解了什么是存儲位置對齊，如果還不清楚可以查看：SQL Server 2005 中的分區表和索引，其實很簡單，如Ext所示，但是主要是理解它的原理和作用；

　　--8.創建非聚集索引

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：還不想結束？呵呵，這個包含性索引的創建就當是買8送1吧；

　　--9.創建包含性索引

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

　　五、注意

　　上面的代碼中我們把文件與文件組是一一對應起來的，如果我們想更小話文件的話，我們可以在文件組下面創建多個文件，并且設置文件的最大值（MAXSIZE），這樣就會把數據分配到不同的物理文件上，但是有一點需要注意，那就是它是一個個的使用文件的，當一個用完了才會使用下一個的。

　　日志文件也可以像上面的做法來做，這樣收縮日志的時候比較方便？刪除日志文件比較方便？

　　有一點我們可能會混淆，那就是既然可以在一個文件組里面創建多個文件，那么這個跟我們按照Id的自增來分布數據是不是等效的？這是有不同的，因為從創建分區方案的時候我們就發現文件組和分區邊界值是對應的，所以一段分區值這些數據是分配到以文件組為單位的存儲單元中，并不是文件。

　　補充一下，那就是在文件組下面創建的文件只能按照設置的最大值（MAXSIZE）來區分數據，并不能按照值來區分，這也算一個不同點吧。

　　六、后記

　　如果這些表是寫的多，讀的少：類似記錄日志，我們還有一些方案可以進行處理，比如SQL Server 2008的行壓縮、頁壓縮等；比如MySQL的IASM數據引擎；或者是使用MySQL的master/slave負載均衡。

　　七、參考文獻

　　SQL Server 2005 中的分區表和索引