1.簡(jiǎn)介

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

2.表分區(qū)

2.1分區(qū)范圍

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

2.2分區(qū)鍵

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

2.3索引分區(qū)

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

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

3.1創(chuàng)建文件組

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

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

3.2指定文件組存放路徑

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

--創(chuàng)建四個(gè)ndf文件，對(duì)應(yīng)到各文件組中，F(xiàn)ILENAME文件存儲(chǔ)路徑
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

可以通過(guò)以下T-SQL語(yǔ)句查看文件組存放相關(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ù)場(chǎng)景來(lái)決定。比如我測(cè)試庫(kù)庫(kù)存表有36萬(wàn)左右數(shù)據(jù)，而有些供應(yīng)商的庫(kù)存數(shù)據(jù)遠(yuǎn)遠(yuǎn)比其他供應(yīng)商大，那么我可以考慮使用供應(yīng)商ID字段作為邊界值分區(qū)。例如：根據(jù)T-SQL統(tǒng)計(jì)，18080供應(yīng)商庫(kù)存數(shù)據(jù)最大，那么我可以根據(jù)18080供應(yīng)商上下分為三個(gè)區(qū)。

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

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

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

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

根據(jù)上述分區(qū)范圍記錄，我們可以將供應(yīng)商ID作為邊界值設(shè)置，執(zhí)行以下T-SQL語(yǔ)句設(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語(yǔ)句創(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ū)步驟都是為了接下來(lái)創(chuàng)建分區(qū)表這一步驟而準(zhǔn)備的。廢話不多說(shuō)，現(xiàn)在我們來(lái)看看如何創(chuàng)建分區(qū)表。右鍵需要分區(qū)的表->儲(chǔ)存->創(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)建好索引之后，我們來(lái)看看分區(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

最后我們來(lái)看看加了索引之后表數(shù)據(jù)查詢情況：

4.表分區(qū)的優(yōu)缺點(diǎn)

優(yōu)點(diǎn)：

• 改善查詢性能：對(duì)分區(qū)對(duì)象的查詢可以僅搜索自己關(guān)心的分區(qū)，提高檢索速度。
• 增強(qiáng)可用性：如果表的某個(gè)分區(qū)出現(xiàn)故障，表在其他分區(qū)的數(shù)據(jù)仍然可用。
• 維護(hù)方便：如果表的某個(gè)分區(qū)出現(xiàn)故障，需要修復(fù)數(shù)據(jù)，只修復(fù)該分區(qū)即可。
• 均衡I/O：可以把不同的分區(qū)映射到不同磁盤以平衡I/O，改善整個(gè)系統(tǒng)性能。

缺點(diǎn)：

分區(qū)表相關(guān)：已經(jīng)存在的表沒(méi)有方法可以直接轉(zhuǎn)化為分區(qū)表。

到此這篇關(guān)于SQL SERVER使用表分區(qū)優(yōu)化性能的文章就介紹到這了。希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。

最新評(píng)論