SQL SERVER使用表分割區優化效能

2022-04-08 13:02:02

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 ServerMSSQL11.MSSQLSERVERMSSQLDATASupIDGroupFile1.ndf',
SIZE=10MB,
FILEGROWTH=10MB)
TO FILEGROUP SupIDGroup1

ALTER DATABASE [TestDB] ADD FILE(
NAME='SupIDGroupFile2',
FILENAME='D:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATASupIDGroupFile2.ndf',
SIZE=10MB,
FILEGROWTH=10MB)
TO FILEGROUP SupIDGroup2

ALTER DATABASE [TestDB] ADD FILE(
NAME='SupIDGroupFile3',
FILENAME='D:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATASupIDGroupFile3.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