（3）SQL Server表磁區-有解無憂

1.簡介

當一個表資料量很大時候，很自然我們就會想到將表拆分成很多小表，在執行查詢時候就到各個小表去查，最后匯總資料集回傳給呼叫者加快查詢速度，比如電商平臺訂單表，庫存表，由于長年累月讀寫較多，積累資料都是例外龐大的，這時候，我們可以想到表磁區這個做法，降低運維和維護成本，提高讀寫性能，比如將前半年訂單放一個歷史磁區表，不活躍庫存放一個歷史磁區表，截止到SQL Server 2016，一張表或一個索引最多可以有15000個磁區，

2.表磁區

2.1磁區范圍

磁區范圍是指在要磁區的表中，根據業務選擇表中的關鍵欄位做為磁區邊界條件，磁區后，資料所在的具體位置至關重要，這樣才能在需要時只訪問相應的磁區，注意磁區是指資料的邏輯分離，不是資料在磁盤上的物理位置，資料的位置由檔案組來決定，所以一般建議一個磁區對應一個檔案組，

2.2磁區鍵

磁區表中的欄位可以作為磁區鍵，比如庫存表中供應商ID，對表和索引進行磁區的第一步就是定義磁區的關鍵資料，

2.3索引磁區

除了對表的資料集進行磁區之外，還可以對索引進行磁區，使用相同的函式對表及其索引進行磁區通常可以優化性能，

3.創建表磁區

3.1創建檔案組

在這里演示示例當中，我根據業務場景在TestDB資料庫新增三個檔案組，而三個檔案組分別對應三個磁區，而多個檔案組好處是可以按照不同業務場景將資料放在對應檔案組當中，優化性能同時好維護資料，檔案組數量由硬體決定，最好是一個檔案組對應一個磁區，好維護，而通常檔案組都處于不同磁盤上的，但是由于是演示，我只在一個磁盤中存放，

--創建四個檔案組
ALTER DATABASE [TestDB] ADD FILEGROUP SupIDGroup1
ALTER DATABASE [TestDB] ADD FILEGROUP SupIDGroup2
ALTER DATABASE [TestDB] ADD FILEGROUP SupIDGroup3

3.2指定檔案組存放路徑

在創建檔案組之后，指定檔案組存放磁盤位置，檔案大小，

--創建四個ndf檔案，對應到各檔案組中，FILENAME檔案存盤路徑
ALTER DATABASE [TestDB] ADD FILE(
NAME='SupIDGroupFile1',
FILENAME='D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SupIDGroupFile1.ndf',
SIZE=10MB,
FILEGROWTH=10MB)
TO FILEGROUP SupIDGroup1

ALTER DATABASE [TestDB] ADD FILE(
NAME='SupIDGroupFile2',
FILENAME='D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SupIDGroupFile2.ndf',
SIZE=10MB,
FILEGROWTH=10MB)
TO FILEGROUP SupIDGroup2

ALTER DATABASE [TestDB] ADD FILE(
NAME='SupIDGroupFile3',
FILENAME='D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\SupIDGroupFile3.ndf',
SIZE=10MB,
FILEGROWTH=10MB)
TO FILEGROUP SupIDGroup3

注（附上洗掉檔案組T-SQL）：

ALTER DATABASE [TestDB] REMOVE FILE SupIDGroupFile3

可以通過以下T-SQL陳述句查看檔案組存放相關資訊：

SELECT file_id,type,type_desc,data_space_id,name,physical_name,state_desc,size,growth
FROM sys.database_files

3.3創建磁區函式

如何創建表磁區邊界值，我們肯定要根據業務場景來決定，比如我測驗庫庫存表有36萬左右資料，而有些供應商的庫存資料遠遠比其他供應商大，那么我可以考慮使用供應商ID欄位作為邊界值磁區，例如：根據T-SQL統計，18080供應商庫存資料最大，那么我可以根據18080供應商上下分為三個區，

第一個磁區范圍記錄：供應商ID小于等于13570的39097條庫存資料，

第二個磁區范圍記錄：供應商ID大于13570和小于等于18079的45962條庫存資料，

第三個磁區范圍記錄：供應商ID大于18079小于等于18080的164937條庫存資料，

第四個磁區范圍記錄：供應商ID大于18080的111116條庫存資料，

根據上述磁區范圍記錄，我們可以將供應商ID作為邊界值設定，執行以下T-SQL陳述句設定邊界值：

--設定邊界值
CREATE PARTITION FUNCTION PF_SupplierID(int)
AS RANGE LEFT FOR VALUES (13570,18079,18080)

執行完畢后如圖所示：

3.4創建磁區方案

執行以下T-SQL陳述句創建磁區方案：

--創建磁區方案
CREATE PARTITION SCHEME PS_SupplierID
AS PARTITION PF_SupplierID TO ([PRIMARY], [SupIDGroup1],[SupIDGroup2],[SupIDGroup3])

執行完畢后如圖所示：

3.5創建磁區表

上面那些磁區步驟都是為了接下來創建磁區表這一步驟而準備的，廢話不多說，現在我們來看看如何創建磁區表，右鍵需要磁區的表->儲存->創建磁區，具體步驟如下圖所示：

3.6創建磁區索引

--創建磁區索引
CREATE NONCLUSTERED INDEX [NCI_SupplierID] ON dbo.Stock
(
    SupplierID ASC
)
INCLUDE ( [Model],[Brand],[Encapsulation]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

或者

執行完畢后如圖所示：

創建好索引之后，我們來看看磁區情況：

--查看各磁區有多少行資料
SELECT * FROM (
SELECT $PARTITION.PF_SupplierID([SupplierID]) AS Patition,COUNT(*) AS CountRows FROM dbo.Stock
GROUP BY $PARTITION.PF_SupplierID([SupplierID])
)TB ORDER BY Patition

最后我們來看看加了索引之后表資料查詢情況：

4.表磁區的優缺點

優點：
●改善查詢性能：對磁區物件的查詢可以僅搜索自己關心的磁區，提高檢索速度，
●增強可用性：如果表的某個磁區出現故障，表在其他磁區的資料仍然可用，
●維護方便：如果表的某個磁區出現故障，需要修復資料，只修復該磁區即可，
●均衡I/O：可以把不同的磁區映射到不同磁盤以平衡I/O，改善整個系統性能，
缺點：
磁區表相關：已經存在的表沒有方法可以直接轉化為磁區表，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/4126.html

標籤：SQL Server

上一篇：Sqlserver 報錯“引數資料型別 ntext/text 對于 replace 函式的引數 1 無效”的解決方案及原理分析擴展

下一篇：記錄一下無聊的資料庫作業