为扩展而对表进行分区

13 分钟

分区决策几乎是永久性的。选择不佳的分区键会使性能比未分区的表更差，重新分区多 TB 表需要完全重新生成它，且停机数小时。在设计过程中选择的分区键和索引对齐方式决定了分区是改进系统还是创建无法轻松撤消的维护噩梦。

表分区将大型表划分为更小、更易于管理的部分（分区），同时将它们保留为单个逻辑表。应用程序会看到一个表，但数据库引擎管理可以独立维护、存档或查询的多个物理段。

了解分区概念

分区涉及多个关键组件：一个 分区函数 ，用于定义数据的划分方式、将分区映射到文件组的 分区方案 ，以及确定每个行所属的 分区的分区列 。了解这些概念有助于设计有效的分区策略。

评估性能和运营优势

分区通过以下方式提升查询性能：分区消除（查询按分区键筛选时仅访问相关分区，如 1 个月而非 120 个月）、并行处理（多个分区跨 CPU 内核同时处理）、按分区计算统计信息（比全表计算更快）以及索引查找（较小的分区意味着较浅的 B 树）。

操作优势包括精细维护，您可以在当前分区上重新生成索引，而旧分区继续保持在线状态；通过元数据操作，能够在数秒内将旧分区切换到存档表，实现快速归档；由于可以独立维护分区，因而提升了可用性；通过将旧分区迁移到更便宜、更慢的存储实现分层存储。

例如，假设一家具有 1.2 TB 交易表的金融服务公司，其中按日期（90% 查询）筛选的查询会扫描整个表。实施每月分区后，通过分区消除机制，查询性能提高了10到20倍；索引在每个分区的重建时间从6小时缩短至20分钟；使用分区切换，旧数据的归档从4小时的锁定减少到只需几秒；通过将较旧的分区移动到更便宜的存储空间，存储成本降低了40%。

了解何时使用分区

下表显示了分区有助于简化时与增加不必要复杂性时的对比：

Scenario	使用分区？	为什么
查询 80% 以上的时间会筛选特定列（日期、区域）	是的	分区消除仅访问相关的分区
定期存档旧数据（每月、季度）	是的	以秒为单位切换分区，而不是进行 `DELETE` 操作。
只需要重新生成最近数据的索引	是的	在旧分区保持联机状态时重新生成当前分区
具有分层存储需求的大型表（多 TB 级）	是的	将较旧的分区移动到更便宜的存储
大多数查询都会扫描整个表或筛选各种列	否	扫描所有分区 — 性能比非分区表更差
单行查找或小范围扫描很常见	否	分区会增加开销，无需带来好处
无明确列与查询模式匹配	否	无法选择有效的分区键

创建分区组件

以下示例显示了三个组件：分区函数、分区方案和分区表：

-- Create partition function based on date ranges
-- Use RANGE RIGHT for datetime columns to keep same-day values together
CREATE PARTITION FUNCTION PF_OrderDate (DATETIME2)
    AS RANGE RIGHT FOR VALUES 
    ('2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01');

-- Create partition scheme mapping to a single filegroup (recommended)
-- Use multiple filegroups only for tiered storage or independent backups
CREATE PARTITION SCHEME PS_OrderDate
    AS PARTITION PF_OrderDate ALL TO ([PRIMARY]);

-- Create partitioned table
-- Include partition column in primary key for clustered index alignment
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10,2),
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
) ON PS_OrderDate(OrderDate);

此示例为 Orders 表创建季度分区。分区函数定义四个边界值（1 月、4 月、7 月、10 月）创建 5 个分区：2024 年之前的数据为 1 个，2024 年每个季度为 4 个。分区方案将所有分区映射到 PRIMARY 文件组。 Orders 表使用 OrderDate 列作为分区键，该列必须包含在主键中才能正确对齐索引。

选择分区策略

分区键是最重要的决策。选择不当，分区的弊大于利。理想的分区键出现在大多数查询的子句中 WHERE ，创建合理均衡的分区，并与维护模式保持一致。

以下键选择条件可帮助你选择正确的分区键：

查询模式：超过 80% 的查询通过此列筛选
数据分布：均匀分布（没有具有 90 个数据% 的单分区）
维护一致性：匹配归档/清除模式（基于时间的归档的日期列）
稳定性：INSERT 后的值不会更改（避免对可更新列进行分区）

了解范围分区

范围分区根据值范围（最常见的日期）划分数据。每个分区都包含一个特定的范围（1 月数据、2 月数据等）。这是最常用的策略。

下面是范围分区效果最佳的位置：

时序数据（订单、日志、事务）
顺序数据（发票编号、订单 ID）
数值范围（工资带、价格层）

下表显示了常见的分区模式：

图案	何时使用
每日	大容量系统，短保留期
每周	中等容量，6-12 个月存储期限
每月	最常用的是平衡分区的数量和大小
每季度一次	较低数据量，多年数据保留期
每年	存档场景，长期历史数据

例如，电子商务平台按每月对订单进行分区，这样当前月份的查询可以访问一个分区，季度报告可以访问3个分区，而年终分析可以使用12个分区，并去除旧的年份分区。

可以通过在分区函数中定义边界来创建范围分区：

-- RANGE RIGHT creates 5 partitions: <100000, 100000-199999, 200000-299999, 300000-399999, >=400000
CREATE PARTITION FUNCTION PF_InvoiceNumber (INT)
    AS RANGE RIGHT FOR VALUES 
    (100000, 200000, 300000, 400000);

按分类值进行分区

可以使用 RANGE 与字符串或分类值（如区域）进行分区。分区函数根据排序顺序放置值。此方法适用于地理范围分布、多租户系统或部门数据，特别是在查询时频繁按类别进行筛选的情况下。

以下示例按区域对数据进行分区：

-- Partition by region
CREATE PARTITION FUNCTION PF_Region (NVARCHAR(50))
    AS RANGE LEFT FOR VALUES ('East', 'North', 'South', 'West');

CREATE PARTITION SCHEME PS_Region
    AS PARTITION PF_Region ALL TO ([PRIMARY]);

CREATE TABLE RegionalData (
    DataID INT NOT NULL,
    Region NVARCHAR(50) NOT NULL,
    Value DECIMAL(10,2),
    CONSTRAINT PK_RegionalData PRIMARY KEY (DataID, Region)
) ON PS_Region(Region);

实现索引分区

对表进行分区时，索引可以对齐或非对齐。对齐索引使用与表相同的分区方案，而非对齐索引使用不同的分区或无分区。默认情况下，已分区表上的非聚集索引继承表的分区方案。

了解对齐索引与非对齐索引

对齐索引 使用与表相同的分区函数。每个索引分区都与表分区匹配，可实现快速分区切换、简化维护和更好的分区消除。

非对齐索引 使用不同的分区或不使用分区。这些功能无法进行分区切换，并且不支持超过 1,000 个分区的表。

需要对存档进行分区切换、需要独立重新生成特定分区或查询模式筛选分区键时，请使用对齐索引。

例如，假设一个按 OrderDate 分区的 Orders 表，并在 CustomerID 上具有非聚集索引。将对齐分区与同一 OrderDate 方案结合使用，可以通过切换分区、独立重新生成当前索引和删除旧分区来存档旧月份，而不会影响整个表。

可以使用与基表相同的分区方案创建分区索引：

-- Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_Customer
    ON Orders(CustomerID)
    ON PS_OrderDate(OrderDate);

-- Create partitioned columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SalesData_CS
    ON SalesData(Revenue, Region)
    ON PS_SalesDate(SaleDate);

管理分区操作

创建分区表后，需要随时间推移管理它们。常见作包括查询分区元数据、随着数据增长添加新分区，以及在存档期间删除旧分区。这些作使用 $PARTITION 函数和 ALTER PARTITION FUNCTION 语句。

查询分区信息

可以使用函数 $PARTITION 查看分区信息。下面是一个示例：

-- View partition information
SELECT 
    $PARTITION.PF_OrderDate(OrderDate) AS PartitionNumber,
    MIN(OrderDate) AS MinDate,
    MAX(OrderDate) AS MaxDate,
    COUNT(*) AS RowCount
FROM Orders
GROUP BY $PARTITION.PF_OrderDate(OrderDate)
ORDER BY PartitionNumber;

添加新分区边界

可以使用拆分分区以添加新边界值 ALTER PARTITION FUNCTION。下面是一个示例：

-- Split partition to add new boundary
ALTER PARTITION FUNCTION PF_OrderDate()
    SPLIT RANGE ('2024-11-01');

此语句将新的边界值（2024 年 11 月 1 日）添加到分区函数，将现有分区拆分为两个分区。包含从 10 月到 12 月的日期的分区现在变为两个分区：一个为 10 月，另一个分区用于 11 月到 12 月。

存档和删除分区

您可以使用ALTER PARTITION FUNCTION和MERGE RANGE合并分区以存档旧数据。下面是一个示例：

-- Merge partitions to archive old data
ALTER PARTITION FUNCTION PF_OrderDate()
    MERGE RANGE ('2023-12-31');

应用分区最佳做法

以下最佳做法有助于避免实现后难以更正的常见分区错误：

使索引与表分区保持一致：对表和索引使用相同的分区方案来启用分区切换和维护
监视数据分布：定期检查分区统计信息，以确定不平衡的分区并验证分区消除
自动执行分区管理：计划作业以在达到边界并存档旧分区之前添加新分区
避免过度分区：目标是在每个分区中达到数百万行，而不是数千行，因为过多的分区会导致开销
在主键中包含分区键：在分区方案中聚集索引对齐所必需的

分区需要仔细规划。选择的分区键和索引对齐方式确定是获得性能还是创建复杂性。正确实现时，分区通过更快的查询、高效的存档和简化的维护来转换大型表管理。

反馈

此页面是否有帮助？