SQL Server中表的分区设计-猿码集

1.什么是表的分区

表的分区是将一个大的表拆分成多个小的表，每个小表称为一个分区，这些分区之间是相互独立的。表的分区可以提高表的查询和维护的效率。通过将表的数据按照某个规则分散在不同的文件组中，可以实现快速定位数据。

2.SQL Server中表的分区规则

2.1 按照整型列进行分区

按照整型列进行分区是最简单、最常见的方式。其原理是根据某个整型列的值进行分区，比如按照年龄、性别、地区等进行分区。


--创建一个按照时间进行分区的表
CREATE TABLE Sales
(
SaleID INT PRIMARY KEY CLUSTERED,
SaleDate DATETIME,
Amount MONEY
) ON RangePartitionScheme(SaleDate)
AS RANGE right FOR VALUES('2017-01-01','2018-01-01','2019-01-01')

2.2 按照字符型列进行分区

按照字符型列进行分区和按照整型列进行分区的思路是一样的，只是将整型换成字符型，一般用于分区列是字符串类型的情况。


--创建一个按照地区进行分区的表
CREATE TABLE Members
(
MemberID INT PRIMARY KEY CLUSTERED,
MemberName VARCHAR(50),
Region VARCHAR(50)
) ON RangePartitionScheme(Region)
AS RANGE RIGHT FOR VALUES('Asia','Europe','Africa','North America','South America','Australia')

2.3 按照日期型列进行分区

按照日期型列进行分区可以按照年、月、日或者小时等进行分区，一般用于某个表的数据具有时间属性的情况。


--创建一个按照小时进行分区的表
CREATE TABLE Log
(
LogID INT PRIMARY KEY CLUSTERED,
LogTime DATETIME,
Content VARCHAR(100)
) ON RANGE LogHourPartitionScheme(LogTime)
AS RANGE RIGHT FOR VALUES('2017-01-01 00:00:00.000','2017-01-01 01:00:00.000',...,'2017-01-31 23:00:00.000')

3.如何实现表的分区

3.1 创建分区函数

分区函数定义了如何将数据行映射到分区。定义分区函数时，必须指定分区列和分区规则，并且分区函数必须是确定性的。

--创建以年为分区条件的分区函数 CREATE PARTITION FUNCTION PartByYear(INT) AS RANGE RIGHT FOR VALUES(2019,2020,2021,2022,2023,2024,2025,2026,2027,2028,2029)

3.2 创建分区方案

分区方案定义了不同分区的存储位置，用于存储分区中数据的数据文件组和文件的数量。


--创建分区方案
CREATE PARTITION SCHEME PartByYearScheme
AS PARTITION PartByYear
TO ([PRIMARY],[Partition01],[Partition02],[Partition03],[Partition04],[Partition05],[Partition06],[Partition07],[Partition08],[Partition09],[Partition10],[Partition11])

3.3 创建分区表

将分区函数和分区方案应用于分区表的创建语句中，即可创建一个分区表。


--创建分区表
CREATE TABLE Sales
(
SaleID INT PRIMARY KEY CLUSTERED,
SaleDate DATETIME,
Amount MONEY
) ON PartByYearScheme(SaleDate)

4.分区表的查询操作

一般查询分区表有两种方式：普通查询和精确查询。

4.1 普通查询

普通查询即是在分区表上执行普通的select查询语句，由SQL Server查询优化器决定查询计划的执行方式。


--查询销售额大于1万元的销售记录
SELECT *
FROM Sales
WHERE Amount>10000

4.2 精确查询

精确查询即是在查询语句中指定分区键和分区值，可以有效提高查询效率。


--查询2022年的销售记录
SELECT *
FROM Sales Partition(6)
WHERE SaleDate>'2022-01-01' AND SaleDate<'2023-01-01'

5.分区表的维护操作

分区表需要进行维护操作，比如分裂分区、合并分区、切换分区等操作。

5.1 分裂分区

可以通过分裂分区的方式将一个分区分为多个分区，可以根据业务需要调整分区表的分区大小。


ALTER PARTITION SCHEME PartByYearScheme
NEXT USED [Partition12]
ALTER PARTITION FUNCTION PartByYear()
SPLIT RANGE('2030')

5.2 合并分区

可以通过合并分区的方式将多个分区合并为一个分区，可以根据业务需要优化分区表的性能。


ALTER PARTITION FUNCTION PartByYear()
MERGE RANGE('2028')

5.3 切换分区

切换分区可以将一个分区中的数据迁移到另一个分区中，可以用于优化分区表的性能或者备份数据。


--创建一个空分区
CREATE TABLE Sales_History
(
SaleID INT PRIMARY KEY CLUSTERED,
SaleDate DATETIME,
Amount MONEY
) ON PartByYearScheme(SaleDate)
CREATE UNIQUE CLUSTERED INDEX UC_SaleID ON Sales_History(SaleID)
--切换分区
ALTER TABLE Sales SWITCH PARTITION 1 TO Sales_History PARTITION 1

6.总结

表的分区是提高表的查询和维护效率的有效方式，在SQL Server中可以通过创建分区函数、分区方案和应用于分区表的方式实现。在查询和维护分区表时，有普通查询和精确查询两种方式，也需要进行分裂分区、合并分区和切换分区等维护操作。

SQL Server中表的分区设计