SQL Server表分区是提高数据库性能和管理的宝贵特性，尤其是对大型表。其他主流的关系型数据库也会有表分区的功能，通过将大型表划分为更小、更易于管理的分区，有助于大型表管理。每个分区都可以存储在单独的文件组中，从而提高了查询性能，简化了备份和索引重建等维护任务。
1、定义配分函数
分区函数指示如何将表中的行映射到不同的分区。分区函数和表的分区列必须具有相同的数据类型。
-- 删除分区函数IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = 'PFYear')BEGIN DROP PARTITION FUNCTION PFYear;ENDGO-- 创建分区函数CREATE PARTITION FUNCTION PFYear (date)AS RANGE RIGHT FOR VALUES ('2021-01-01', '2022-01-01', '2023-01-01', '2024-01-01');GO
2、创建分区方案
分区方案将分区映射到特定的文件组。文件组及文件需提前创建好。
-- 删除分区方案IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = 'PSYear')BEGIN DROP PARTITION SCHEME PSYear;ENDGO-- 创建分区方案(提前创建文件组及文件)CREATE PARTITION SCHEME PSYear AS PARTITION PFYearTO (FG2021, FG2022, FG2023, FG2024, [PRIMARY]);GO
3、创建分区表
在创建分区表时，请确保所有唯一索引或主键都包含分区列，以符合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
该结构使用SaleDate作为主键的一部分，并将其与分区列对齐。
插入数据
以下测试按不同年份插入数据，数据将自动分布在不同分区上：
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);
查询分区数据
要查看跨分区的数据分布，可以运行：
SELECT $PARTITION.YearPartitionFunction(SaleDate) AS PartitionNumber, COUNT(*) AS RecordsFROM SalesGROUP BY $PARTITION.YearPartitionFunction(SaleDate);GO
对分区表进行有针对性的维护可以减少停机时间并优化数据库性能。
索引维护
可以在每个分区的基础上重建或重新组织索引，重点关注数据修改频繁的区域。下面命令表示在第三个分区上重建索引。
-- SP_HELP SalesALTER INDEX IX_SaleDate ON Sales REBUILD PARTITION = 3;GO
统计数据更新
保持特定分区的统计信息更新有助于SQL Server查询优化器做出明智的决策，从而提高性能。下面更新第三个分区的统计信息。
UPDATE STATISTICS Sales (IX_SaleDate) WITH RESAMPLE ON PARTITIONS(3);GO
高效的数据管理
SQL Server的分区允许通过分区轻松归档或删除数据。
ALTER TABLE Sales SWITCH PARTITION 10 TO Archive.Sales PARTITION 10;GO
性能考虑
我们有2张日志表 Logs 与 IPRequests 插入数据较频繁。两表没有业务需求，日常偶尔用于查看系统错误信息，给开发同事排错用。我们对两表 Logs 与 IPRequests 都创建了分区，保留10天数据，10天前的数据分别迁移到另一个中间表 LogsMid 与 IPRequestsMid。中间表不用分区，但结构和索引要和原来的表一样。数据迁移到中间表后，中间表会再将数据迁移到另一个归档数据库 T_TempDB 的表 dbo.T_Logs_history 与 dbo.T_IPRequests_history。而表 T_Logs_history 和 T_IPRequests_history 只保留30天数据！
对于分区表的设置，允许锁升级到分区锁，不用升级到表锁。
ALTER TABLE [dbo].[Logs] SET (LOCK_ESCALATION=AUTO)GOALTER TABLE [dbo].[IPRequests] SET (LOCK_ESCALATION=AUTO)GO
更多参考：SQL Server 表选项 LOCK_ESCALATION 对分区的影响
以上各步骤的操作过程，是通过作业自动执行的，分区切换很快。作业分为以下步骤：
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
通过分区及分区维护，我们查询数据性能大大提高了。
◀ 总结 ▶
SQL Server表分区可以显著提高大型数据库的性能、管理性和可扩展性。同样也可以简化备份与恢复。
对于日志表的考虑，如果是比较重要的业务操作日志，个人建议最好单独使用一个数据库。我们知道操作日志非常频繁，数据量也会非常大，但又不是那么重要。单独日志库会大大减少业务库的大小，这样对业务库的备份恢复、数据同步、参数设置等都有非常好的性能。如果操作日志没那么重要，可以不必存储在关系型数据库中，非关系型数据库有较好的扩展性、压缩性、高效搜索、多数据模型等。
阅读原文：原文链接
该文章在 2025/1/10 11:11:10 编辑过
400 186 1886
