SQL Server表分區(qū)是提高數(shù)據(jù)庫性能和管理的寶貴特性，尤其是對大型表。其他主流的關(guān)系型數(shù)據(jù)庫也會有表分區(qū)的功能，通過將大型表劃分為更小、更易于管理的分區(qū)，有助于大型表管理。每個分區(qū)都可以存儲在單獨的文件組中，從而提高了查詢性能，簡化了備份和索引重建等維護(hù)任務(wù)。
1、定義配分函數(shù)
分區(qū)函數(shù)指示如何將表中的行映射到不同的分區(qū)。分區(qū)函數(shù)和表的分區(qū)列必須具有相同的數(shù)據(jù)類型。
-- 刪除分區(qū)函數(shù)IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PFYear')BEGIN DROP PARTITION FUNCTION PFYear;ENDGO-- 創(chuàng)建分區(qū)函數(shù)CREATE PARTITION FUNCTION PFYear (date)AS RANGE RIGHT FOR VALUES ('2021-01-01', '2022-01-01', '2023-01-01', '2024-01-01');GO
2、創(chuàng)建分區(qū)方案
分區(qū)方案將分區(qū)映射到特定的文件組。文件組及文件需提前創(chuàng)建好。
-- 刪除分區(qū)方案IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PSYear')BEGIN DROP PARTITION SCHEME PSYear;ENDGO-- 創(chuàng)建分區(qū)方案(提前創(chuàng)建文件組及文件)CREATE PARTITION SCHEME PSYear AS PARTITION PFYearTO (FG2021, FG2022, FG2023, FG2024, [PRIMARY]);GO
3、創(chuàng)建分區(qū)表
在創(chuàng)建分區(qū)表時，請確保所有唯一索引或主鍵都包含分區(qū)列，以符合SQL Server的要求。
CREATE TABLE Sales ( SaleID int IDENTITY(1,1), SaleDate date, TotalAmount money, CustomerID int, ProductID int, Quantity int, PRIMARY KEY (SaleDate, SaleID)) ON PSYear (SaleDate);GO
該結(jié)構(gòu)使用SaleDate作為主鍵的一部分，并將其與分區(qū)列對齊。
插入數(shù)據(jù)
以下測試按不同年份插入數(shù)據(jù)，數(shù)據(jù)將自動分布在不同分區(qū)上：
INSERT INTO Sales (SaleDate, TotalAmount, CustomerID, ProductID, Quantity)VALUES ('2018-03-15', 120.50, 1, 101, 2), ('2019-07-22', 75.00, 2, 102, 1), ('2020-05-11', 200.00, 3, 103, 5), ('2021-12-01', 150.00, 4, 104, 3);
查詢分區(qū)數(shù)據(jù)
要查看跨分區(qū)的數(shù)據(jù)分布，可以運行：
SELECT $PARTITION.YearPartitionFunction(SaleDate) AS PartitionNumber, COUNT(*) AS RecordsFROM SalesGROUP BY $PARTITION.YearPartitionFunction(SaleDate);GO
對分區(qū)表進(jìn)行有針對性的維護(hù)可以減少停機(jī)時間并優(yōu)化數(shù)據(jù)庫性能。
索引維護(hù)
可以在每個分區(qū)的基礎(chǔ)上重建或重新組織索引，重點關(guān)注數(shù)據(jù)修改頻繁的區(qū)域。下面命令表示在第三個分區(qū)上重建索引。
-- SP_HELP SalesALTER INDEX IX_SaleDate ON Sales REBUILD PARTITION = 3;GO
統(tǒng)計數(shù)據(jù)更新
保持特定分區(qū)的統(tǒng)計信息更新有助于SQL Server查詢優(yōu)化器做出明智的決策，從而提高性能。下面更新第三個分區(qū)的統(tǒng)計信息。
UPDATE STATISTICS Sales (IX_SaleDate) WITH RESAMPLE ON PARTITIONS(3);GO
高效的數(shù)據(jù)管理
SQL Server的分區(qū)允許通過分區(qū)輕松歸檔或刪除數(shù)據(jù)。
ALTER TABLE Sales SWITCH PARTITION 10 TO Archive.Sales PARTITION 10;GO
性能考慮
我們有2張日志表 Logs 與 IPRequests 插入數(shù)據(jù)較頻繁。兩表沒有業(yè)務(wù)需求，日常偶爾用于查看系統(tǒng)錯誤信息，給開發(fā)同事排錯用。我們對兩表 Logs 與 IPRequests 都創(chuàng)建了分區(qū)，保留10天數(shù)據(jù)，10天前的數(shù)據(jù)分別遷移到另一個中間表 LogsMid 與 IPRequestsMid。中間表不用分區(qū)，但結(jié)構(gòu)和索引要和原來的表一樣。數(shù)據(jù)遷移到中間表后，中間表會再將數(shù)據(jù)遷移到另一個歸檔數(shù)據(jù)庫 T_TempDB 的表 dbo.T_Logs_history 與 dbo.T_IPRequests_history。而表 T_Logs_history 和 T_IPRequests_history 只保留30天數(shù)據(jù)！
對于分區(qū)表的設(shè)置，允許鎖升級到分區(qū)鎖，不用升級到表鎖。
ALTER TABLE [dbo].[Logs] SET (LOCK_ESCALATION=AUTO)GOALTER TABLE [dbo].[IPRequests] SET (LOCK_ESCALATION=AUTO)GO
更多參考：SQL Server 表選項 LOCK_ESCALATION 對分區(qū)的影響
以上各步驟的操作過程，是通過作業(yè)自動執(zhí)行的，分區(qū)切換很快。作業(yè)分為以下步驟：
declare @now date;declare @next_day nvarchar(10);declare @prio_day datetime;declare @next_fg nvarchar(50);declare @sql nvarchar(500);set @now = getdate()set @next_day = convert(varchar(10),@now,120) set @prio_day = dateadd(d,-10,@now) set @next_fg = N'filegroup_'+convert(nvarchar(10),datediff(D,'2020-01-01',@now)%10+1)
SET @sql = N'ALTER PARTITION SCHEME PS_DateTime NEXT USED ' + @next_fg + '; 'set @sql = @sql + N'ALTER PARTITION FUNCTION PF_DateTime() SPLIT RANGE(''' + @next_day + ''');'exec(@sql);
ALTER TABLE Logs SWITCH PARTITION 1 TO LogsMidALTER TABLE IPRequests SWITCH PARTITION 1 TO IPRequestsMid
ALTER PARTITION FUNCTION PF_DateTime() MERGE RANGE(@prio_day);
insert into T_TempDB.dbo.T_Logs_history select * from dbo.LogsMid;GOinsert into T_TempDB.dbo.T_IPRequests_history select * from dbo.IPRequestsMid;GO
truncate table dbo.LogsMid;GOtruncate table dbo.IPRequestsMid;GO
update statistics dbo.Logs;GOupdate statistics dbo.IPRequests;GO
DELETE FROM T_TempDB.dbo.T_Logs_history WHERE OperationTime <= DATEADD(M,-1,GETDATE())GODELETE FROM T_TempDB.dbo.T_IPRequests_history WHERE AddTime <= DATEADD(M,-1,GETDATE())GO
通過分區(qū)及分區(qū)維護(hù)，我們查詢數(shù)據(jù)性能大大提高了。
? 總結(jié) ?
SQL Server表分區(qū)可以顯著提高大型數(shù)據(jù)庫的性能、管理性和可擴(kuò)展性。同樣也可以簡化備份與恢復(fù)。
對于日志表的考慮，如果是比較重要的業(yè)務(wù)操作日志，個人建議最好單獨使用一個數(shù)據(jù)庫。我們知道操作日志非常頻繁，數(shù)據(jù)量也會非常大，但又不是那么重要。單獨日志庫會大大減少業(yè)務(wù)庫的大小，這樣對業(yè)務(wù)庫的備份恢復(fù)、數(shù)據(jù)同步、參數(shù)設(shè)置等都有非常好的性能。如果操作日志沒那么重要，可以不必存儲在關(guān)系型數(shù)據(jù)庫中，非關(guān)系型數(shù)據(jù)庫有較好的擴(kuò)展性、壓縮性、高效搜索、多數(shù)據(jù)模型等。
閱讀原文：原文鏈接
該文章在 2025/1/10 11:11:10 編輯過
400 186 1886
