快捷導(dǎo)航

SQL SERVER使用表分區(qū)優(yōu)化性能

更新時間：2022年04月08日 10:18:36 作者：暗斷腸

這篇文章介紹了SQL SERVER使用表分區(qū)優(yōu)化性能的方法，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1.簡介

當(dāng)一個表數(shù)據(jù)量很大時候，很自然我們就會想到將表拆分成很多小表，在執(zhí)行查詢時候就到各個小表去查，最后匯總數(shù)據(jù)集返回給調(diào)用者加快查詢速度。比如電商平臺訂單表，庫存表，由于長年累月讀寫較多，積累數(shù)據(jù)都是異常龐大的，這時候，我們可以想到表分區(qū)這個做法，降低運(yùn)維和維護(hù)成本，提高讀寫性能。比如將前半年訂單放一個歷史分區(qū)表，不活躍庫存放一個歷史分區(qū)表。截止到SQL Server 2016，一張表或一個索引最多可以有15000個分區(qū)。

2.表分區(qū)

2.1分區(qū)范圍

分區(qū)范圍是指在要分區(qū)的表中，根據(jù)業(yè)務(wù)選擇表中的關(guān)鍵字段做為分區(qū)邊界條件，分區(qū)后，數(shù)據(jù)所在的具體位置至關(guān)重要，這樣才能在需要時只訪問相應(yīng)的分區(qū)。注意分區(qū)是指數(shù)據(jù)的邏輯分離，不是數(shù)據(jù)在磁盤上的物理位置，數(shù)據(jù)的位置由文件組來決定，所以一般建議一個分區(qū)對應(yīng)一個文件組。

2.2分區(qū)鍵

分區(qū)表中的字段可以作為分區(qū)鍵，比如庫存表中供應(yīng)商ID。對表和索引進(jìn)行分區(qū)的第一步就是定義分區(qū)的關(guān)鍵數(shù)據(jù)。

2.3索引分區(qū)

除了對表的數(shù)據(jù)集進(jìn)行分區(qū)之外，還可以對索引進(jìn)行分區(qū)，使用相同的函數(shù)對表及其索引進(jìn)行分區(qū)通?？梢詢?yōu)化性能。

3.創(chuàng)建表分區(qū)

3.1創(chuàng)建文件組

在這里演示示例當(dāng)中，我根據(jù)業(yè)務(wù)場景在TestDB數(shù)據(jù)庫新增三個文件組，而三個文件組分別對應(yīng)三個分區(qū)。而多個文件組好處是可以按照不同業(yè)務(wù)場景將數(shù)據(jù)放在對應(yīng)文件組當(dāng)中，優(yōu)化性能同時好維護(hù)數(shù)據(jù)。文件組數(shù)量由硬件決定，最好是一個文件組對應(yīng)一個分區(qū)，好維護(hù)。而通常文件組都處于不同磁盤上的，但是由于是演示，我只在一個磁盤中存放。

--創(chuàng)建四個文件組
ALTER DATABASE [TestDB] ADD FILEGROUP SupIDGroup1
ALTER DATABASE [TestDB] ADD FILEGROUP SupIDGroup2
ALTER DATABASE [TestDB] ADD FILEGROUP SupIDGroup3

3.2指定文件組存放路徑

在創(chuàng)建文件組之后，指定文件組存放磁盤位置，文件大小。

--創(chuàng)建四個ndf文件，對應(yīng)到各文件組中，F(xiàn)ILENAME文件存儲路徑
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語句查看文件組存放相關(guān)信息：

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

3.3創(chuàng)建分區(qū)函數(shù)

如何創(chuàng)建表分區(qū)邊界值，我們肯定要根據(jù)業(yè)務(wù)場景來決定。比如我測試庫庫存表有36萬左右數(shù)據(jù)，而有些供應(yīng)商的庫存數(shù)據(jù)遠(yuǎn)遠(yuǎn)比其他供應(yīng)商大，那么我可以考慮使用供應(yīng)商ID字段作為邊界值分區(qū)。例如：根據(jù)T-SQL統(tǒng)計，18080供應(yīng)商庫存數(shù)據(jù)最大，那么我可以根據(jù)18080供應(yīng)商上下分為三個區(qū)。

第一個分區(qū)范圍記錄：供應(yīng)商ID小于等于13570的39097條庫存數(shù)據(jù)。

第二個分區(qū)范圍記錄：供應(yīng)商ID大于13570和小于等于18079的45962條庫存數(shù)據(jù)。

第三個分區(qū)范圍記錄：供應(yīng)商ID大于18079小于等于18080的164937條庫存數(shù)據(jù)。

第四個分區(qū)范圍記錄：供應(yīng)商ID大于18080的111116條庫存數(shù)據(jù)。

根據(jù)上述分區(qū)范圍記錄，我們可以將供應(yīng)商ID作為邊界值設(shè)置，執(zhí)行以下T-SQL語句設(shè)置邊界值：

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

執(zhí)行完畢后如圖所示：

3.4創(chuàng)建分區(qū)方案

執(zhí)行以下T-SQL語句創(chuàng)建分區(qū)方案：

--創(chuàng)建分區(qū)方案
CREATE PARTITION SCHEME PS_SupplierID
AS PARTITION PF_SupplierID TO ([PRIMARY], [SupIDGroup1],[SupIDGroup2],[SupIDGroup3])

執(zhí)行完畢后如圖所示：

3.5創(chuàng)建分區(qū)表

上面那些分區(qū)步驟都是為了接下來創(chuàng)建分區(qū)表這一步驟而準(zhǔn)備的。廢話不多說，現(xiàn)在我們來看看如何創(chuàng)建分區(qū)表。右鍵需要分區(qū)的表->儲存->創(chuàng)建分區(qū)，具體步驟如下圖所示：

3.6創(chuàng)建分區(qū)索引

--創(chuàng)建分區(qū)索引
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

或者

執(zhí)行完畢后如圖所示：

創(chuàng)建好索引之后，我們來看看分區(qū)情況：

--查看各分區(qū)有多少行數(shù)據(jù)
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