MS SQL如何建立表分区：完善表结构管理-猿码集

1. 什么是表分区

表分区是将一个大型表按照一定的规则分割成多个小的部分。通过表分区可以有效地改善数据库的查询性能、提高数据处理的效率、提高数据的可用性和管理数据周期等。同时可以使得对单独的分区进行维护和管理，有效减少了管理的复杂性。

2. 表分区的基本要素

在进行分区之前需要考虑以下三个基本要素：

2.1 分区函数

确定每行数据是应该放在哪个分区中的函数称为分区函数。我们可以使用 SQL Server自带的分区函数或者自定义分区函数。自定义分区函数可以根据具体的业务需要进行定制。

CREATE PARTITION FUNCTION myRangePF1 (datetime)
AS RANGE LEFT FOR VALUES ('20150101', '20160101', '20170101');

2.2 分区方案

确定分区方案需要考虑以下几个因素：

分区函数的类型和参数

分区函数的区间范围和分区的个数

每个分区的名称和位置

CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO ([PRIMARY], myPartition1, myPartition2, myPartition3);

2.3 分区键

每个表必须有一个分区键用于根据分区函数计算出相应的分区值。分区键可以是一个或多个列名组成的列表。

CREATE TABLE myTable(ID INT, Date datetime, Information nvarchar(50))
ON myRangePS1(Date)

3. 按时间进行分区

按照时间进行分区可以有效地管理历史数据。可以将旧数据放置在旧的分区中，并将更加热门的数据放在更新的分区中。这样可以提高数据的查询性能和数据处理的效率。

3.1 创建分区函数

我们先创建一个分区函数，用于按照日期对数据进行分区。

CREATE PARTITION FUNCTION myRangePF2(DATETIME)
AS RANGE LEFT FOR VALUES ('20150101', '20160101', '20170101');

3.2 创建分区方案

我们创建一个分区方案，为每个分区定义一个文件组。创建时需要指定分区函数和每个分区对应的文件组。使用 ALTER PARTITION SCHEME 语句可以修改一个分区方案。

CREATE PARTITION SCHEME myRangePS2
AS PARTITION myRangePF2
TO (FileGrp1,FileGrp2,FileGrp3,[PRIMARY]);

3.3 创建表并指定分区方案

创建表时需要指定分区键和分区方案。

CREATE TABLE TestPartitionTable
(
  ID INT,
  Date DATETIME,
  Info NVARCHAR(MAX)
)
ON myRangePS2(Date);

3.4 插入数据

插入数据时需要指定分区键列的值，SQL Server 会根据分区函数计算出相应的分区。

INSERT TestPartitionTable (ID, Date, Info) VALUES (1, '2014-06-01', 'Information for 2014');
INSERT TestPartitionTable (ID, Date, Info) VALUES (2, '2015-01-01', 'Information for 2015');
INSERT TestPartitionTable (ID, Date, Info) VALUES (3, '2016-05-01', 'Information for 2016');
INSERT TestPartitionTable (ID, Date, Info) VALUES (4, '2017-06-01', 'Information for 2017');

3.5 查询数据

可以使用 WHERE 子句按照日期筛选数据。

SELECT * FROM TestPartitionTable WHERE Date >='2016-01-01';

3.6 增加分区

可以使用 ALTER PARTITION SCHEME 语句增加分区。

ALTER PARTITION SCHEME myRangePS2 NEXT USED [FileGrp4];

4. 按区间大小进行分区

按照数据量的大小进行分区，可以将同样大小的数据放在同一分区中，这样可以简化管理和维护。

4.1 创建分区函数

使用 RANGE RIGHT 关键字可以将范围较大的分区列表放置在表的左侧，而将范围较小的列表放置在右侧。

CREATE PARTITION FUNCTION myRangePF3(INT)
AS RANGE RIGHT FOR VALUES (1000, 5000, 20000);

4.2 创建分区方案

使用 ALTER PARTITION SCHEME 语句可以修改一个分区方案。使用 NEXTUSED 关键字可以将新分配的分区加入至上一个已使用分区的右侧。

CREATE PARTITION SCHEME myRangePS3
AS PARTITION myRangePF3
TO (FileGrp1,FileGrp2,FileGrp3,[PRIMARY]);
ALTER PARTITION SCHEME myRangePS3 
NEXTUSED [FileGrp4];

4.3 创建表并指定分区方案

创建表时需要指定分区键和分区方案。

CREATE TABLE TestPartitionTable2
(
  ID INT,
  Data INT,
  Info NVARCHAR(MAX)
)
ON myRangePS3(Data);

4.4 插入数据

插入数据时需要指定分区键列的值。

INSERT TestPartitionTable2 (ID, Data, Info) VALUES (1, 100, 'Information for 100');
INSERT TestPartitionTable2 (ID, Data, Info) VALUES (2, 2000, 'Information for 2000');
INSERT TestPartitionTable2 (ID, Data, Info) VALUES (3, 10000, 'Information for 10000');
INSERT TestPartitionTable2 (ID, Data, Info) VALUES (4, 30000, 'Information for 30000');

4.5 查询数据

可以使用 WHERE 子句进行数据查询。

SELECT * FROM TestPartitionTable2 WHERE Data > 3000;

4.6 增加分区

可以使用 ALTER PARTITION SCHEME 语句增加分区。

ALTER PARTITION SCHEME myRangePS3
NEXT USED [FileGrp5];

5. 分区表的优点

分区表可以带来如下优点：

提高查询性能。分区表可以只查询有关分区所包含的数据，由于与其他分区无关，因此查询速度会更快。

提高数据处理效率。分区表可以根据数据处理需求，将分区数据放在不同的物理磁盘或文件组中，处理数据时可以充分利用多个 CPU或者磁盘，提高数据处理效率。

提高可用性。分区表可以用于实现分布式处理，使得在某个节点发生故障时，对其他节点没有影响，提高了系统的可用性。

简化管理和维护。分区表允许对单独的分区进行管理和维护，使得管理的复杂性大大降低。

6. 总结

本文介绍了如何在 MS SQL 中使用表分区，讲述了分区函数、分区方案、分区键以及按时间和区间大小进行表分区的方法，同时介绍了分区表的优点。在实际应用中，我们可以根据数据规模、数据处理需求和业务流程等因素合理应用表分区，提高数据库的查询性能、数据处理效率和可用性。

MS SQL如何建立表分区：完善表结构管理