SQLServer 分区表与分区索引

分区表概述

分区表是将大型表或索引分解为多个更小、更易管理的部分的技术。每个分区是独立的存储单元，包含表数据的一部分，共享相同的表结构和索引定义。

分区表优势

提高查询性能：仅扫描相关分区，减少I/O操作
简化管理：按分区进行维护操作，降低维护窗口
优化数据生命周期管理：方便实现数据归档和清除
提高并发性能：多个分区可同时进行操作
提高可用性：单个分区故障不影响整个表

分区基础概念

分区函数

分区函数定义如何将数据映射到分区。它接受一个分区列作为输入，并返回分区号。

sql

-- 创建分区函数（按日期范围）
CREATE PARTITION FUNCTION [pf_DateRange](datetime2(7)) 
AS RANGE RIGHT FOR VALUES 
('2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01');

分区方案

分区方案将分区函数定义的分区映射到物理文件组。

sql

-- 创建分区方案
CREATE PARTITION SCHEME [ps_DateRange] 
AS PARTITION [pf_DateRange] 
TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);

分区列

用于确定数据如何分布到不同分区的列
建议选择：
- 频繁用于查询过滤的列
- 具有均匀分布值的列
- 适合范围分区的列（如日期、时间、整数）

分区表设计

1. 选择分区策略

分区类型	适用场景	示例
范围分区	时间序列数据、有序数值	按月份分区销售数据
列表分区	离散值数据	按地区或产品类别分区
哈希分区	随机分布数据	按用户ID分区

2. 规划分区数

初始分区数：根据数据量和增长预期设置，通常12-24个
年度分区：建议按年创建分区，便于管理
预创建分区：提前创建未来6-12个月的分区
最大分区数：SQLServer 2016及以上版本支持15,000个分区

3. 设计分区列

选择合适的数据类型：日期时间或整数类型最佳
避免使用复杂表达式：分区列应直接用于查询
考虑数据分布：确保数据均匀分布到各分区

创建分区表

1. 准备工作

规划文件组和物理存储
创建分区函数
创建分区方案

2. 创建分区表

sql

-- 创建分区表
CREATE TABLE [dbo].[SalesData] (
    [SalesID] INT IDENTITY(1,1) NOT NULL,
    [SalesDate] DATETIME2(7) NOT NULL,
    [ProductID] INT NOT NULL,
    [Quantity] INT NOT NULL,
    [Amount] DECIMAL(18,2) NOT NULL
) ON [ps_DateRange]([SalesDate]);

3. 创建分区索引

sql

-- 创建分区聚集索引
CREATE CLUSTERED INDEX [CIX_SalesData] ON [dbo].[SalesData]
(   [SalesDate] ASC,
    [SalesID] ASC
) ON [ps_DateRange]([SalesDate]);

-- 创建分区非聚集索引
CREATE NONCLUSTERED INDEX [NIX_SalesData_Product] ON [dbo].[SalesData]
(   [ProductID] ASC
) ON [ps_DateRange]([SalesDate]);

分区表维护

1. 查看分区信息

sql

-- 查看分区函数定义
SELECT * FROM sys.partition_functions;

-- 查看分区方案
SELECT * FROM sys.partition_schemes;

-- 查看表分区信息
SELECT 
    t.name AS TableName,
    i.name AS IndexName,
    p.partition_number,
    p.rows,
    pf.name AS PartitionFunction,
    ps.name AS PartitionScheme
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
WHERE t.name = 'SalesData' AND i.type_desc = 'CLUSTERED';

2. 分区维护操作

拆分分区

sql

-- 拆分分区（添加新的分区边界）
ALTER PARTITION SCHEME [ps_DateRange] NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION [pf_DateRange]() SPLIT RANGE ('2023-05-01');

合并分区

sql

-- 合并分区（移除分区边界）
ALTER PARTITION FUNCTION [pf_DateRange]() MERGE RANGE ('2023-01-01');

切换分区

sql

-- 切换分区到目标表
CREATE TABLE [dbo].[SalesData_Archive] (
    [SalesID] INT IDENTITY(1,1) NOT NULL,
    [SalesDate] DATETIME2(7) NOT NULL,
    [ProductID] INT NOT NULL,
    [Quantity] INT NOT NULL,
    [Amount] DECIMAL(18,2) NOT NULL
);

ALTER TABLE [dbo].[SalesData] SWITCH PARTITION 1 TO [dbo].[SalesData_Archive];

分区索引设计

本地分区索引

每个分区索引的分区方式与基表相同
每个分区独立维护
查询性能更好，维护更方便
建议优先使用

全局分区索引

索引作为一个整体维护，不与基表分区匹配
适合大范围查询
维护成本较高

最佳实践

1. 分区设计

选择合适的分区键和分区策略
确保数据均匀分布
预创建未来分区
使用多个文件组提高I/O并行性

2. 性能优化

使用分区消除：确保查询条件包含分区列
避免跨分区查询：设计查询时考虑分区边界
优化索引：为每个分区创建合适的索引

3. 维护策略

定期重组或重建分区索引
监控分区大小和增长趋势
实施分区切换实现高效数据归档
定期备份单个分区（如果使用部分备份）

4. 版本差异

版本	特性差异
SQLServer 2008	支持基础分区功能
SQLServer 2012	引入列存储分区索引
SQLServer 2016	支持15,000个分区，增强分区切换性能
SQLServer 2019	智能查询处理优化分区查询
SQLServer 2022	增强分区管理功能，支持更多分区策略

常见问题与解决方案

1. 如何选择合适的分区列？

考虑查询模式：选择频繁用于WHERE子句的列
考虑数据分布：确保数据均匀分布到各分区
考虑维护需求：选择适合数据生命周期管理的列
避免使用频繁更新的列

2. 如何处理分区表的统计信息？

sql

-- 更新分区表统计信息
UPDATE STATISTICS [dbo].[SalesData] WITH FULLSCAN;

-- 更新特定分区统计信息
UPDATE STATISTICS [dbo].[SalesData] (CIX_SalesData) WITH RESAMPLE, PARTITIONS = 2;

3. 如何监控分区表性能？

sql

-- 查看分区查询性能
SELECT 
    qp.query_plan,
    st.text,
    qs.execution_count,
    qs.total_elapsed_time,
    qs.total_worker_time,
    qs.total_logical_reads,
    qs.total_logical_writes
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE st.text LIKE '%SalesData%';

4. 分区表和非分区表性能比较

小表：非分区表性能更好
大表（>100GB）：分区表性能更好
频繁按分区列查询：分区表性能更好
全表扫描：非分区表可能更快

5. 如何实现分区数据归档？

创建与源表结构相同的归档表
使用ALTER TABLE SWITCH切换分区
验证数据完整性
归档或清除归档表数据

sql

-- 完整归档流程
-- 1. 创建归档表
CREATE TABLE [dbo].[SalesData_Archive] (
    [SalesID] INT IDENTITY(1,1) NOT NULL,
    [SalesDate] DATETIME2(7) NOT NULL,
    [ProductID] INT NOT NULL,
    [Quantity] INT NOT NULL,
    [Amount] DECIMAL(18,2) NOT NULL
    -- 添加与源表相同的约束和索引
);

-- 2. 切换分区
ALTER TABLE [dbo].[SalesData] SWITCH PARTITION 1 TO [dbo].[SalesData_Archive];

-- 3. 验证数据
SELECT COUNT(*) FROM [dbo].[SalesData_Archive];

-- 4. 清除归档数据或备份后删除
TRUNCATE TABLE [dbo].[SalesData_Archive];

分区表监控

1. 监控分区大小

sql

-- 监控分区大小
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    p.partition_number,
    p.rows,
    (SUM(a.total_pages) * 8) / 1024 AS TotalSizeMB
FROM sys.partitions p
JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY p.object_id, i.name, p.partition_number, p.rows
ORDER BY OBJECT_NAME(p.object_id), i.name, p.partition_number;

2. 监控分区查询性能

sql

-- 查看分区消除情况
SELECT 
    qp.query_plan,
    st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE st.text LIKE '%SalesData%' AND qp.query_plan LIKE '%PartitionRange%';

总结

分区表和分区索引是SQLServer中管理大型数据库的重要技术。通过合理的分区设计和维护，可以显著提高查询性能，简化数据库管理，并优化数据生命周期管理。DBA需要根据业务需求和数据特征选择合适的分区策略，定期监控和维护分区表，以充分发挥分区技术的优势。随着SQLServer版本的不断更新，分区功能也在持续增强，提供了更多的优化选项和管理便利。

SQLServer 分区表与分区索引 ​

分区表概述 ​

分区表优势 ​

分区基础概念 ​

分区函数 ​

分区方案 ​

分区列 ​

分区表设计 ​

1. 选择分区策略 ​

2. 规划分区数 ​

3. 设计分区列 ​

创建分区表 ​

1. 准备工作 ​

2. 创建分区表 ​

3. 创建分区索引 ​

分区表维护 ​

1. 查看分区信息 ​

2. 分区维护操作 ​

拆分分区 ​

合并分区 ​

切换分区 ​

分区索引设计 ​

本地分区索引 ​

全局分区索引 ​

最佳实践 ​

1. 分区设计 ​

2. 性能优化 ​

3. 维护策略 ​

4. 版本差异 ​

常见问题与解决方案 ​

1. 如何选择合适的分区列？ ​

2. 如何处理分区表的统计信息？ ​

3. 如何监控分区表性能？ ​

4. 分区表和非分区表性能比较 ​

5. 如何实现分区数据归档？ ​

分区表监控 ​

1. 监控分区大小 ​

2. 监控分区查询性能 ​

总结 ​