19.1 SQL Server创建分区表
SQL Server创建分区表
简介
表分区可以将表的数据存储在多个物理部分或分区中。每个分区都有相同的列，但有不同的行集。
对大型表使用表分区。有如下好处：
- 更快地备份和维护一个或多个分区。
- 更快、更高效地传输或访问数据子集，同时保持整个数据收集的完整性。
- 可以大大提高查询性能。
创建分区表
步骤如下：
- 创建保存表分区的文件组（默认有一个PRIMARY文件组）。
- 创建次数据文件（ndf），并且指派到新创建的文件组中（默认有一个主数据文件叫mdf）
- 创建一个分区函数，根据指定列的值将表的行映射到对应分区中。
- 创建将分区表映射到新文件组的分区方案
- 根据分区方案创建表。
示例：
首先创建一个数据库用于示例：
create database PartitionTest;;
1）创建文件组
创建数据库时，SQL Server至少创建了两个文件：数据文件和日志文件：
数据文件包含数据和对象，如表、索引和视图。
日志文件包含恢复数据库中事务的信息。
SQL Server可以将数据存储在多个数据文件中，并使用文件组对数据文件进行分组。默认情况下，数据文件属于PRIMARY文件组。
要向数据库中添加更多文件组，可以使用ALTER DATABASE ... ADD FILEGROUP语句。
文件组就相当于一个文件夹，可以将文件组中的文件放在一起。文件组中的文件可以是数据文件，也可以是日志文件。
首先，将三个文件组添加到PartitionTest数据库：
USE PartitionTest;
ALTER DATABASE PartitionTest
ADD FILEGROUP FileGroup1;
ALTER DATABASE PartitionTest
ADD FILEGROUP FileGroup2;
ALTER DATABASE PartitionTest
ADD FILEGROUP FileGroup3;
然后可以使用以下语句验证当前数据库的文件组：
SELECT
name
FROM sys.filegroups
WHERE type = 'FG';
第二步，将物理文件分配给文件组：
注意：执行下面SQL之前先要创建路径（文件夹）：
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\PartitionTest，否则会报错。
ALTER DATABASE PartitionTest ADD FILE
(
NAME = datafile1,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\PartitionTest\datafile1.ndf',
SIZE = 10 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP FileGroup1;
ALTER DATABASE PartitionTest ADD FILE
(
NAME = datafile2,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\PartitionTest\datafile2.ndf',
SIZE = 10 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP FileGroup2;
ALTER DATABASE PartitionTest ADD FILE
(
NAME = datafile3,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\PartitionTest\datafile3.ndf',
SIZE = 10 MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024 KB
) TO FILEGROUP FileGroup3;
本例中，我们将C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\PartitionTest文件夹中的三个文件分配给3个文件文件组，每个文件组一个文件。
最后，使用以下语句验证文件组分配情况：
SELECT
name as filename,
physical_name as file_path
FROM sys.database_files
where type_desc = 'ROWS';
2） 创建分区函数
分区函数是一个数据库对象，该对象根据指定列的值将表的行映射到分区。该列称为分区列。
分区函数使用分区列的值并返回分区值。此外，它定义了分区和分区边界的数量。
在本示例中，分区将基于主键Id列的值。分区函数将创建三个分区：
CREATE PARTITION FUNCTION PF1 (int)
AS RANGE LEFT
FOR VALUES ('5000000','10000000');
PF1是分区函数的名称。它采用数据类型为int的参数。- 其中
5000000,10000000是两个边界值，两个边界值就划分出了三个分区，LEFT决定了边界值属于边界值左边的分区，因此三个分区的值为：值<=5000000，值>5000000 and 值<=10000000，值>10000000，如果为RIGHT，则三个分区的值为：值<5000000，值>=5000000 and 值<10000000，值>=10000000 - 因此
AS RANGE LEFT FOR VALUES指定了三个边界，其中Id在5000000之前（包含5000000）的行将属于分区1，Id在5000000之后和10000000之前（包含10000000）的行将归属分区2，Id在10000000之后的行将归分区3。
3） 创建分区方案
分区方案是将分区函数返回的分区，映射到文件组的数据库对象
以下语句创建一个分区方案，将PF1返回的分区映射到文件组：
CREATE PARTITION SCHEME PS1
AS PARTITION PF1
TO ([FileGroup1], [FileGroup2], [FileGroup3]);
4）创建分区表
以下语句基于PS1分区方案创建分区表：
CREATE TABLE PartitionTable1 (
Id int primary KEY identity,
UserName varchar(10),
)
ON PS1 (Id);
ON PS1 (Id);子句指定表的分区方案和分区列Id。- 分区列必须包含在聚集索引中，否则会出现错误。
5）测试并查看分区信息
下面的INSERT语句向PartitionTable1表中添加8000000行随机数据：
Use PartitionTest;
go
declare @xings nvarchar(2056) = N'赵钱孙李周郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林***锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁';
declare @mings nvarchar(2056) = N'嘉懿煜城懿轩烨伟苑博伟泽熠彤鸿煊博涛烨霖烨华煜祺智宸正豪昊然明杰诚立轩立辉峻熙弘文熠彤鸿煊烨霖哲瀚鑫鹏致远俊驰雨泽烨磊晟睿天佑文昊修洁黎昕远航旭尧鸿涛伟祺轩越泽浩宇瑾瑜皓轩擎苍擎宇志泽睿渊楷瑞轩弘文哲瀚雨泽鑫磊梦琪忆之桃慕青问兰尔岚元香初夏沛菡傲珊曼文乐菱痴珊恨玉惜文香寒新柔语蓉海安夜蓉涵柏水桃醉蓝春儿语琴从彤傲晴语兰又菱碧彤元霜怜梦紫寒妙彤曼易南莲紫翠雨寒易烟如萱若南寻真晓亦向珊慕灵以蕊寻雁映易雪柳孤岚笑霜海云凝天沛珊寒云冰旋宛儿绿真盼儿晓霜碧凡夏菡曼香若烟半梦雅绿冰蓝灵槐平安书翠翠风香巧代云梦曼幼翠友巧听寒梦柏醉易访旋亦玉凌萱访卉怀亦笑蓝春翠靖柏夜蕾冰夏梦松书雪乐枫念薇靖雁寻春恨山从寒忆香觅波静曼凡旋以亦念露芷蕾千兰新波代真新蕾雁玉冷卉紫山千琴恨天傲芙盼山怀蝶冰兰山柏翠萱乐丹翠柔谷山之瑶冰露尔珍谷雪乐萱涵菡海莲傲蕾青槐冬儿易梦惜雪宛海之柔夏青亦瑶妙菡春竹修杰伟诚建辉晋鹏天磊绍辉泽洋明轩健柏煊昊强伟宸博超君浩子骞明辉鹏涛炎彬鹤轩越彬风华靖琪明诚高格光华国源宇晗昱涵润翰飞翰海昊乾浩博和安弘博鸿朗华奥华灿嘉慕坚秉建明金鑫锦程瑾瑜鹏经赋景同靖琪君昊俊明季同开济凯安康成乐语力勤良哲理群茂彦敏博明达朋义彭泽鹏举濮存溥心璞瑜浦泽奇邃祥荣轩';
declare @username nvarchar(10);
declare @money money;
declare @count bigint=8000000;--数据条数
while @count>0
begin
--set @money=RAND()*10000000;
set @username=SUBSTRING(@xings,Cast(floor((RAND()*(len(@xings)-1))+1) as int),1);
set @username+=SUBSTRING(@mings,Cast(floor((RAND()*(len(@mings)-1))+1) as int),1);
if RAND()>0.4
begin
set @username+=SUBSTRING(@mings,Cast(floor((RAND()*(len(@mings)-1))+1) as int),1);
end
--insert into account(Money,UserName) values(@money,@username);
insert into PartitionTable1(UserName) values(@username);
set @count-=1;
end
执行完后查看一下：
select * from PartitionTable1
Ok，一共八百万条数据。
尝试从表中查询一条数据：
ok，不到一秒钟，没有问题。
要检查每个分区的行，请使用以下查询：
SELECT
p.partition_number AS partition_number,
f.name AS file_group,
p.rows AS row_count
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'PartitionTable1'
order by partition_number;
可以看到，分区1中存了500万条数据，分区2存了3000000条数据，一共八百万。只有超过了一千万数据才会存入分区3中。
然后我们来看看数据库文件中：
可以看到，第一个和第二个分区的文件，肯定是填充了数据了，第三个文件还是创建文件时初始大小10MB，其实第三个文件根本还没有数据。
要确定表是否已分区：
如果表 PartitionTable 已分区，或者表上的任何非聚集索引已分区，则以下查询将返回一行或多行。 如果未对表进行分区，并且表上没有非聚集索引分区，则不会返回任何行。
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, *
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
JOIN sys.partition_schemes ps
ON i.data_space_id = ps.data_space_id
WHERE t.name = 'PartitionTable1';
GO
查看已分区表的边界值
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName,
p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc,
r.boundary_id, r.value AS BoundaryValue
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
WHERE
t.name = 'PartitionTable1'
AND i.type <= 1
ORDER BY SchemaName, t.name, i.name, p.partition_number;
执行：
查看已分区表的分区列
SELECT
t.[object_id] AS ObjectID
, SCHEMA_NAME(t.schema_id) AS SchemaName
, t.name AS TableName
, ic.column_id AS PartitioningColumnID
, c.name AS PartitioningColumnName
, i.name as IndexName
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
AND i.[type] <= 1 -- clustered index or a heap
JOIN sys.partition_schemes AS ps
ON ps.data_space_id = i.data_space_id
JOIN sys.index_columns AS ic
ON ic.[object_id] = i.[object_id]
AND ic.index_id = i.index_id
AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column
JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
AND ic.column_id = c.column_id
WHERE t.name = 'PartitionTable1';
查看每个分区的值的范围描述
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName,
p.partition_number AS PartitionNumber, f.name AS PartitionFunctionName, p.rows AS Rows, rv.value AS BoundaryValue,
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A'
ELSE
CASE WHEN f.boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>='
WHEN f.boundary_value_on_right = 0 THEN '>'
ELSE '>='
END + ' ' + ISNULL(CONVERT(varchar(64), rv2.value), 'Min Value') + ' ' +
CASE f.boundary_value_on_right WHEN 1 THEN 'and <'
ELSE 'and <=' END
+ ' ' + ISNULL(CONVERT(varchar(64), rv.value), 'Max Value')
END AS TextComparison
FROM sys.tables AS t
JOIN sys.indexes AS i
ON t.object_id = i.object_id
JOIN sys.partitions AS p
ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN sys.partition_schemes AS s
ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
ON f.function_id = r.function_id and r.boundary_id = p.partition_number
LEFT JOIN sys.partition_range_values AS rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
LEFT JOIN sys.partition_range_values AS rv2
ON f.function_id = rv2.function_id
AND p.partition_number - 1= rv2.boundary_id
WHERE
t.name = 'PartitionTable1'
AND i.type <= 1
ORDER BY t.name, p.partition_number;
更多信息参考官方文档
