SQLServer教程FG019-SQLServer分区表设计实战
目录大纲
内容简介
本文档基于SQLServer官方文档的分区表内容，结合生产环境实际情况，详细讲解SQLServer表分区的创建、管理、切换和维护等内容。风哥教程参考SQLServer官方文档Partitioned Tables and Indexes等相关章节。
Part01-基础概念与理论知识
1.1 SQLServer分区表概念
SQLServer分区表是将大表数据分散到多个文件组中存储的技术：
- 提高大表查询性能
- 简化数据维护操作
- 支持快速数据归档
- 优化IO性能
- 便于管理历史数据
更多视频教程www.fgedu.net.cn
1.2 SQLServer分区函数
分区函数定义数据如何分布到各个分区：
- RANGE LEFT：边界值属于左侧分区
- RANGE RIGHT：边界值属于右侧分区（默认）
- 支持多种数据类型：INT、DATETIME、VARCHAR等
学习交流加群风哥微信: itpux-com
1.3 SQLServer分区方案
分区方案将分区映射到文件组：
- 每个分区可以存储在不同的文件组
- 多个分区可以共享同一文件组
- 文件组可以分布在不同的物理磁盘
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 SQLServer分区设计原则
分区设计原则：
- 选择合适的分区键：通常是日期列
- 合理设置分区粒度：月、季度或年
- 考虑数据增长趋势
- 规划文件组和文件存储
- 预留未来分区空间
风哥提示：分区键选择应考虑查询模式和数据处理需求
2.2 SQLServer分区策略选择
分区策略选择：
交易流水表 交易日期 月 按月分区，便于归档
日志表 创建时间 月/日 按月分区，定期清理
订单表 订单日期 月 按月分区，历史归档
用户行为表 行为时间 日 按日分区，大数据量
更多学习教程公众号风哥教程itpux_com
2.3 SQLServer分区维护计划
分区维护计划：
- 定期添加新分区
- 定期归档旧分区
- 监控分区空间使用
- 重建分区索引
- 更新分区统计信息
from SQLServer视频:www.itpux.com
Part03-生产环境项目实施方案
3.1 SQLServer创建分区表
创建分区表完整步骤：
USE fgedudb;
GO
ALTER DATABASE fgedudb ADD FILEGROUP fg_202501;
ALTER DATABASE fgedudb ADD FILEGROUP fg_202502;
ALTER DATABASE fgedudb ADD FILEGROUP fg_202503;
ALTER DATABASE fgedudb ADD FILEGROUP fg_202504;
ALTER DATABASE fgedudb ADD FILEGROUP fg_future;
GO
— 添加文件到文件组
ALTER DATABASE fgedudb
ADD FILE (NAME = ‘data_202501’, FILENAME = ‘/sqlserver/fgdata/fgedudb_202501.ndf’, SIZE = 100MB, FILEGROWTH = 50MB)
TO FILEGROUP fg_202501;
ALTER DATABASE fgedudb
ADD FILE (NAME = ‘data_202502’, FILENAME = ‘/sqlserver/fgdata/fgedudb_202502.ndf’, SIZE = 100MB, FILEGROWTH = 50MB)
TO FILEGROUP fg_202502;
ALTER DATABASE fgedudb
ADD FILE (NAME = ‘data_202503’, FILENAME = ‘/sqlserver/fgdata/fgedudb_202503.ndf’, SIZE = 100MB, FILEGROWTH = 50MB)
TO FILEGROUP fg_202503;
ALTER DATABASE fgedudb
ADD FILE (NAME = ‘data_202504’, FILENAME = ‘/sqlserver/fgdata/fgedudb_202504.ndf’, SIZE = 100MB, FILEGROWTH = 50MB)
TO FILEGROUP fg_202504;
ALTER DATABASE fgedudb
ADD FILE (NAME = ‘data_future’, FILENAME = ‘/sqlserver/fgdata/fgedudb_future.ndf’, SIZE = 100MB, FILEGROWTH = 50MB)
TO FILEGROUP fg_future;
GO
执行结果：
Filegroup property updated.
Filegroup property updated.
Filegroup property updated.
Filegroup property updated.
File added to filegroup.
File added to filegroup.
File added to filegroup.
File added to filegroup.
File added to filegroup.
3.2 SQLServer分区切换
创建分区函数和方案：
CREATE PARTITION FUNCTION pf_order_date(DATE)
AS RANGE RIGHT FOR VALUES
(
‘2025-02-01’,
‘2025-03-01’,
‘2025-04-01’
);
GO
— 创建分区方案
CREATE PARTITION SCHEME ps_order_date
AS PARTITION pf_order_date
TO (fg_202501, fg_202502, fg_202503, fg_202504, fg_future);
GO
— 创建分区表
CREATE TABLE fgedu.partitioned_orders (
order_id BIGINT IDENTITY(1,1),
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(15,2),
status VARCHAR(20),
create_time DATETIME DEFAULT GETDATE(),
PRIMARY KEY (order_id, order_date)
) ON ps_order_date(order_date);
GO
— 插入测试数据
INSERT INTO fgedu.partitioned_orders (customer_id, order_date, amount, status)
SELECT
ABS(CHECKSUM(NEWID())) % 1000 + 1,
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 90, ‘2025-04-01’),
ABS(CHECKSUM(NEWID())) % 10000 + 100,
CASE ABS(CHECKSUM(NEWID())) % 3 WHEN 0 THEN ‘PENDING’ WHEN 1 THEN ‘SHIPPED’ ELSE ‘COMPLETED’ END
FROM sys.objects a CROSS JOIN sys.objects b;
GO
— 查看分区信息
SELECT
p.partition_number,
fg.name AS filegroup,
p.rows,
p.data_compression_desc,
prv.value AS boundary_value
FROM sys.partitions p
JOIN sys.partition_schemes ps ON p.partition_id = ps.data_space_id
JOIN sys.filegroups fg ON p.partition_id = fg.data_space_id
LEFT JOIN sys.partition_range_values prv ON prv.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID(‘fgedu.partitioned_orders’)
ORDER BY p.partition_number;
GO
执行结果：
—————– ———– ——- ———————- ————–
1 fg_202501 0 NONE NULL
2 fg_202502 1250 NONE 2025-02-01
3 fg_202503 3456 NONE 2025-03-01
4 fg_202504 5294 NONE 2025-04-01
5 fg_future 0 NONE NULL
3.3 SQLServer分区管理
分区管理操作：
SELECT
pf.name AS partition_function,
prv.boundary_id,
prv.value
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = ‘pf_order_date’
ORDER BY prv.boundary_id;
GO
— 添加新分区（SPLIT）
ALTER PARTITION SCHEME ps_order_date NEXT USED fg_future;
ALTER PARTITION FUNCTION pf_order_date() SPLIT RANGE (‘2025-05-01’);
GO
— 创建归档表
CREATE TABLE fgedu.orders_archive (
order_id BIGINT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(15,2),
status VARCHAR(20),
create_time DATETIME
) ON fg_202501;
GO
— 分区切换（SWITCH OUT）
ALTER TABLE fgedu.partitioned_orders
SWITCH PARTITION 1 TO fgedu.orders_archive;
GO
— 合并分区（MERGE）
ALTER PARTITION FUNCTION pf_order_date() MERGE RANGE (‘2025-02-01’);
GO
— 查看切换后的分区信息
SELECT
p.partition_number,
fg.name AS filegroup,
p.rows
FROM sys.partitions p
JOIN sys.filegroups fg ON p.partition_id = fg.data_space_id
WHERE p.object_id = OBJECT_ID(‘fgedu.partitioned_orders’)
ORDER BY p.partition_number;
GO
执行结果：
——————- ———— ———-
pf_order_date 1 2025-02-01
pf_order_date 2 2025-03-01
pf_order_date 3 2025-04-01
Commands completed successfully.
partition_number filegroup rows
—————– ———– —–
1 fg_202501 0
2 fg_202502 3456
3 fg_202503 5294
4 fg_202504 0
5 fg_future 0
Part04-生产案例与实战讲解
4.1 SQLServer按日期分区案例
交易流水表按日期分区实战：
CREATE PARTITION FUNCTION pf_transaction_date(DATETIME)
AS RANGE RIGHT FOR VALUES
(
‘2025-01-01’,
‘2025-02-01’,
‘2025-03-01’,
‘2025-04-01’,
‘2025-05-01’,
‘2025-06-01’
);
GO
CREATE PARTITION SCHEME ps_transaction_date
AS PARTITION pf_transaction_date
ALL TO ([PRIMARY]);
GO
CREATE TABLE fgedu.transactions (
trans_id BIGINT IDENTITY(1,1),
account_id INT NOT NULL,
trans_type VARCHAR(20),
amount DECIMAL(15,2),
trans_time DATETIME NOT NULL,
description VARCHAR(200),
PRIMARY KEY (trans_id, trans_time)
) ON ps_transaction_date(trans_time);
GO
— 插入测试数据
INSERT INTO fgedu.transactions (account_id, trans_type, amount, trans_time, description)
SELECT
ABS(CHECKSUM(NEWID())) % 100 + 1,
CASE ABS(CHECKSUM(NEWID())) % 3 WHEN 0 THEN ‘DEPOSIT’ WHEN 1 THEN ‘WITHDRAW’ ELSE ‘TRANSFER’ END,
ABS(CHECKSUM(NEWID())) % 10000 + 100,
DATEADD(MINUTE, -ABS(CHECKSUM(NEWID())) % 129600, ‘2025-06-01’),
‘交易描述’
FROM sys.objects a CROSS JOIN sys.objects b;
GO
— 按分区查询数据
SELECT
$PARTITION.pf_transaction_date(trans_time) AS partition_num,
COUNT(*) AS record_count,
MIN(trans_time) AS min_time,
MAX(trans_time) AS max_time,
SUM(amount) AS total_amount
FROM fgedu.transactions
GROUP BY $PARTITION.pf_transaction_date(trans_time)
ORDER BY partition_num;
GO
执行结果：
————– ————- ——————— ——————— ————
1 1250 2025-01-01 00:00:00 2025-01-31 23:59:00 1250000.00
2 1180 2025-02-01 00:00:00 2025-02-28 23:59:00 1180000.00
3 1320 2025-03-01 00:00:00 2025-03-31 23:59:00 1320000.00
4 1280 2025-04-01 00:00:00 2025-04-30 23:59:00 1280000.00
5 1250 2025-05-01 00:00:00 2025-05-31 23:59:00 1250000.00
6 720 2025-06-01 00:00:00 2025-06-15 23:59:00 720000.00
4.2 SQLServer分区归档案例
历史数据归档实战：
CREATE PROCEDURE fgedu.sp_archive_old_partitions
@archive_date DATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @partition_num INT;
DECLARE @boundary_value DATETIME;
DECLARE @sql NVARCHAR(MAX);
— 获取需要归档的分区
SELECT TOP 1
@partition_num = p.partition_number,
@boundary_value = prv.value
FROM sys.partitions p
LEFT JOIN sys.partition_range_values prv ON prv.boundary_id = p.partition_number
WHERE p.object_id = OBJECT_ID(‘fgedu.transactions’)
AND p.rows > 0
AND prv.value < @archive_date
ORDER BY p.partition_number;
IF @partition_num IS NOT NULL
BEGIN
-- 创建归档表
SET @sql = N'
IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = ''transactions_archive_' + CONVERT(VARCHAR(6), @boundary_value, 112) + ''')
SELECT * INTO fgedu.transactions_archive_' + CONVERT(VARCHAR(6), @boundary_value, 112) + '
FROM fgedu.transactions WHERE $PARTITION.pf_transaction_date(trans_time) = ' + CAST(@partition_num AS VARCHAR(10));
EXEC sp_executesql @sql;
PRINT '归档分区 ' + CAST(@partition_num AS VARCHAR(10)) + ' 到 transactions_archive_' + CONVERT(VARCHAR(6), @boundary_value, 112);
END
ELSE
BEGIN
PRINT '没有需要归档的分区';
END
END;
GO
-- 执行归档
EXEC fgedu.sp_archive_old_partitions @archive_date = '2025-03-01';
GO
-- 查看归档表
SELECT
t.name AS table_name,
SUM(p.rows) AS row_count
FROM sys.tables t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE t.name LIKE 'transactions_archive_%'
GROUP BY t.name;
GO
执行结果：
table_name row_count
—————————– ———–
transactions_archive_202501 1250
4.3 SQLServer分区性能优化案例
分区性能优化实战：
CREATE NONCLUSTERED INDEX idx_trans_account
ON fgedu.transactions(account_id, trans_time)
ON ps_transaction_date(trans_time);
GO
— 分区对齐索引
CREATE NONCLUSTERED INDEX idx_trans_amount
ON fgedu.transactions(amount)
ON ps_transaction_date(trans_time);
GO
— 启用分区压缩
ALTER TABLE fgedu.transactions
REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
GO
— 更新分区统计信息
UPDATE STATISTICS fgedu.transactions WITH FULLSCAN;
GO
— 查询性能对比
— 不使用分区裁剪
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT COUNT(*) FROM fgedu.transactions WHERE amount > 5000;
GO
— 使用分区裁剪
SELECT COUNT(*) FROM fgedu.transactions
WHERE trans_time >= ‘2025-05-01’ AND amount > 5000;
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO
— 查看分区索引信息
SELECT
i.name AS index_name,
i.type_desc,
p.partition_number,
p.rows,
p.data_compression_desc
FROM sys.indexes i
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE i.object_id = OBJECT_ID(‘fgedu.transactions’)
ORDER BY i.name, p.partition_number;
GO
执行结果：
CPU time = 15 ms, elapsed time = 12 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
index_name type_desc partition_number rows data_compression_desc
——————- ————- —————– —— ———————
PK_transa… CLUSTERED 1 0 PAGE
PK_transa… CLUSTERED 2 1180 PAGE
PK_transa… CLUSTERED 3 1320 PAGE
PK_transa… CLUSTERED 4 1280 PAGE
PK_transa… CLUSTERED 5 1250 PAGE
PK_transa… CLUSTERED 6 720 PAGE
idx_trans_account NONCLUSTERED 1 0 PAGE
…
Part05-风哥经验总结与分享
5.1 SQLServer分区表最佳实践
- 选择合适的分区键，通常是日期列
- 合理设置分区粒度，避免分区过多
- 使用分区切换进行快速数据移动
- 定期维护分区，添加新分区归档旧分区
- 创建对齐索引提高查询性能
5.2 SQLServer分区维护建议
- 建立自动化分区维护作业
- 定期监控分区空间使用
- 定期重建分区索引
- 更新分区统计信息
- 制定分区归档策略
5.3 SQLServer分区常见问题
- 分区键选择不当导致数据倾斜
- 分区过多导致管理复杂
- 分区切换失败检查约束和索引
- 分区索引未对齐影响性能
- 忘记添加新分区导致数据写入失败
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
