【数据库】表分区

介绍：

    分区表在逻辑上是一个表，而物理上是多个表。从用户角度来看，分区表和普通表是一样的。使用分区表的主要目的是为改善大型表以及具有多个访问模式的表的可伸缩性和可管理性。表分区简单理解就是表还是原先的表，但是根据不同条件分块的把数据存放到不同文件下。以实现块级查询的简单化，不比每次都要查询表中的所有数据，而是根据条件查询其中一个块数据。还是比较好理解的，使用就是针对一些表数据量特别的大，提高查询速度。分区表最好是有些规则的比如表是根据年份，月份增长的。有一定规律。

    分区表在整个查询中还是看作一个整体表来进行查询的，不要与分表混为一谈。

分区步骤概括：

      （1）创建数据库文件组

　　（2）创建数据库文件

　　（3）创建分区函数

　　（4）创建分区方案

　　（5）创建分区表

第一步骤：

    添加文件组：文件组可以任意设置添加。名称只要不重复即可。

第二步骤：

    添加文件：文件也是可以任意添加，但是不要忘记分配文件组，当前文件所属文件组。该文件可是真的生成数据库文件的哦。

 

第三步骤：

    创建分区函数：重点来了。创建分区函数的目的是告诉 SQL Server 以什么方式对分区表进行分区

    创建分区函数Transact-SQL语法：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

参数：　　

　　partition_function_name：分区函数的名称。 分区函数名称在数据库内必须唯一，并且符合标识符的规则。

　　input_parameter_type：用于分区的列的数据类型。 当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 用户定义数据类型外，所有数据类型均有效。

　　boundary_value：为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。 如果 boundary_value 为空，则分区函数使 partition_function_name 将整个表或索引映射到单个分区。 只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。

　　LEFT | RIGHT 指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value [ ,...n ] 属于每个边界值间隔的哪一侧（左侧还是右侧）。 如果未指定，则默认值为 LEFT。

示例代码：

--增加分区函数
CREATE PARTITION FUNCTION Function_DateTime ( DATETIME )
AS RANGE RIGHT  
FOR VALUES('2018-01-01', '2019-01-01')

创建成功之后在当前数据库下可以看到你的分区函数：

第四步骤：

    创建分区方案：分区方案的作用则是告诉 SQL Server 将已分区的数据放在哪个文件组中。

    创建分区方案的Transact-SQL语法：

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

参数：

　　partition_scheme_name：分区方案的名称。 分区方案名称在数据库中必须是唯一的，并且符合标识符规则。

　　partition_function_name：使用分区方案的分区函数的名称。 分区函数所创建的分区将映射到在分区方案中指定的文件组。 partition_function_name 必须已经存在于数据库中。 单个分区不能同时包含 FILESTREAM 和非 FILESTREAM 文件组。

　　ALL：指定所有分区都映射到在 file_group_name 中提供的文件组，或映射到主文件组（如果指定了 [PRIMARY]。 如果指定了 ALL，则只能指定一个 file_group_name。

　　file_group_name | [ PRIMARY ] [ ,...n]：指定用来持有由 partition_function_name 指定的分区的文件组的名称。 file_group_name 必须已经存在于数据库中。
　　如果指定了 [PRIMARY]，则分区将存储于主文件组中。 如果指定了 ALL，则只能指定一个 file_group_name。 分区分配到文件组的顺序是从分区 1 开始，按文件组在 [,...n] 中列出的顺序进行分配。 在 [,...n] 中，可以多次指定同一个 file_group_name。 如果 n 不足以拥有在 partition_function_name 中指定的分区数，则 CREATE PARTITION SCHEME 将失败，并返回错误。
　　如果 partition_function_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为 NEXT USED，并且出现显示命名 NEXT USED 文件组的信息。 如果指定了 ALL，则单独的 file_group_name 将为该 partition_function_name 保持它的 NEXT USED 属性。 如果在 ALTER PARTITION FUNCTION 语句中创建了一个分区，则 NEXT USED 文件组将再接收一个分区。 若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。
　　在 file_group_name[ 1,...n] 中指定主文件组时，必须像在 [PRIMARY] 中那样分隔 PRIMARY，因为它是关键字。

示例代码：

CREATE PARTITION SCHEME Scheme_DateTime
AS PARTITION Function_DateTime
TO ('2018', '2019','2019')

注意这里一定要写多一个不然回报：

这是什么原因哪？明明2个分区文件组，分区函数也是2个，为什么会多于哪，其实他的意思是当这个数据之后的怎么办，简单理解就是你现在设置了2018,2019，哪2020年的怎么办。所有想我示例多设置一个就好了。

第五步骤：

    创建分区表：如果在表中创建有主键、唯一索引、聚集索引，则分区依据列必须为该列之一。即分区依据列必须建立在主键、唯一索引、聚集索引之上。

    创建分区方案的Transact-SQL语法：

CREATE TABLE [TableTest]
(
    id INT IDENTITY(1,1) NOT NULL,
    introduce varchar(50)  NULL,
    describe varchar(50) NOT NULL,
    addTime DATETIME NOT NULL
) ON SchemeNAME (字段)

这个没什么好说的就是创建表，然后就说一个字段参数：他是分区列依据。

代码示例：

CREATE TABLE [TableTest]
(
    id INT IDENTITY(1,1) NOT NULL,
    introduce varchar(50)  NULL,
    describe varchar(50) NOT NULL,
    addTime DATETIME NOT NULL
) ON Scheme_DateTime (addTime)

 

 测试结果：

插入一些数据：

查询数据所在物理分区表：

在分区表中使用一般的SELECT语句无法知道数据是分别存放在哪几个不同的物理表中，若要知道数据分别存放的物理表，可以使用$PARTITION函数，该函数可以调用分区函数并返回数据所在物理分区的编号

查询分区表数据详情：

sql脚本：

--查询组信息
select * from sys.filegroups

--增加分区函数
CREATE PARTITION FUNCTION Function_DateTime ( DATETIME )
AS RANGE RIGHT  
FOR VALUES('2018-01-01', '2019-01-01')

--查询分区函数
SELECT * FROM sys.partition_functions

--创建分区方案
CREATE PARTITION SCHEME Scheme_DateTime
AS PARTITION Function_DateTime
TO ('2018', '2019','2019')

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

--创建分区表
CREATE TABLE [TableTest]
(
    id INT IDENTITY(1,1) NOT NULL,
    introduce varchar(50)  NULL,
    describe varchar(50) NOT NULL,
    addTime DATETIME NOT NULL
) ON Scheme_DateTime (addTime)


--插入数据测试
INSERT INTO [dbo].[TableTest] (introduce,describe ,addTime) VALUES ('介绍1' ,'描述1' ,'2017-10-20');
INSERT INTO [dbo].[TableTest] (introduce,describe ,addTime) VALUES ('介绍2' ,'描述2' ,'2018-10-20');
INSERT INTO [dbo].[TableTest] (introduce,describe ,addTime) VALUES ('介绍3' ,'描述3' ,'2017-12-20');
INSERT INTO [dbo].[TableTest] (introduce,describe ,addTime) VALUES ('介绍4' ,'描述4' ,'2018-11-20');
INSERT INTO [dbo].[TableTest] (introduce,describe ,addTime) VALUES ('介绍5' ,'描述5' ,'2017-11-20');

--查询物理分区表
SELECT $PARTITION.Function_DateTime('2020-10-20')

--查看物理分区表中存放的记录
SELECT * FROM [TableTest] WHERE $PARTITION.Function_DateTime(addTime) = 1
SELECT * FROM [TableTest] WHERE $PARTITION.Function_DateTime(addTime) = 2
SELECT * FROM [TableTest] WHERE $PARTITION.Function_DateTime(addTime) = 3

  --查询每个分区的总数
 SELECT $PARTITION.Function_DateTime(addTime) AS 分区编号, COUNT(1) AS 记录数
 FROM [TableTest]
 GROUP BY $PARTITION.Function_DateTime(addTime)