SqlServer分区设置（按时间）
分区表
通常单表数据量到达百万级别就需要使用分库、分区、分表操作来达到一个均衡的效果。
新创建的数据表的数据会默认存在数据库的.mdf文件里，而分区表数据会按照指定的分区方案（Scheme）存储在不同文件里。
优点：
- 均衡I/O: 分区文件映射到不同磁盘可以平衡I/O效率，改善系统性能
- 增加可用性：单个分区出现问题，其余分区正常使用。
- 提高检索速度：按分区可避免全表扫描
缺点：
- 管理难度提升，需要管理的对象：Group列表、File列表、Scheme、Func、定时任务
以下流程最终效果为：一个表对应一个方案；一个方案对应一个函数对应多个文件组；一个文件组对应一个文件
创建流程：
1）新建分区文件组
2）新建分区文件
3）新建分区函数与分区方案
4）新建表/绑定现有表
创建分区表 （T_sql方式）
1）创建分区文件组
一个文件组对应一个文件，那为什么还需要文件组这个概念存在？
DBA：文件组是数据库一个负载均衡的维度，在这里体现不出优点来
-- 创建文件组
ALTER DATABASE [db_hmTest] ADD FILEGROUP db_hmTest_OrderGroup202105;
ALTER DATABASE [db_hmTest] ADD FILEGROUP db_hmTest_OrderGroup202106;
ALTER DATABASE [db_hmTest] ADD FILEGROUP db_hmTest_OrderGroup202107;
ALTER DATABASE [db_hmTest] ADD FILEGROUP db_hmTest_OrderGroup202108;
2）创建文件
每个数据库都有个默认的文件组【PRIMARY】
-- 创建文件
ALTER DATABASE [db_hmTest] ADD
FILE(NAME=N'db_hmTest_Order202105',FILENAME=N'D:\DB\Partition\db_hmTest_Order202105.mdf',SIZE=5MB,FILEGROWTH=1MB,MAXSIZE=UNLIMITED)
TO FILEGROUP [db_hmTest_OrderGroup202105]
ALTER DATABASE [db_hmTest] ADD
FILE(NAME=N'db_hmTest_Order202106',FILENAME=N'D:\DB\Partition\db_hmTest_Order202106.mdf',SIZE=5MB,FILEGROWTH=1MB,MAXSIZE=UNLIMITED)
TO FILEGROUP [db_hmTest_OrderGroup202106]
ALTER DATABASE [db_hmTest] ADD
FILE(NAME=N'db_hmTest_Order202107',FILENAME=N'D:\DB\Partition\db_hmTest_Order202107.mdf',SIZE=5MB,FILEGROWTH=1MB,MAXSIZE=UNLIMITED)
TO FILEGROUP [db_hmTest_OrderGroup202107]
ALTER DATABASE [db_hmTest] ADD
FILE(NAME=N'db_hmTest_Order202108',FILENAME=N'D:\DB\Partition\db_hmTest_Order202108.mdf',SIZE=5MB,FILEGROWTH=1MB,MAXSIZE=UNLIMITED)
TO FILEGROUP [db_hmTest_OrderGroup202108]
3）创建分区函数与分区方案
按照以下设置，效果为：
db_hmTest_OrderGroup202105文件保存范围： (-∞，2021-06-01T00:00:000)
db_hmTest_OrderGroup202106文件保存范围： [2021-06-01T00:00:000，2021-07-01T00:00:000)
db_hmTest_OrderGroup202107文件保存范围： [2021-07-01T00:00:000，2021-08-01T00:00:000)
db_hmTest_OrderGroup202108文件保存范围： [2021-08-01T00:00:000，+∞)
-- 创建分区函数
CREATE PARTITION FUNCTION HmTest_Order_Func(DATETIME2(2))
AS RANGE RIGHT FOR
VALUES(N'2021-06-01T00:00:00.000',N'2021-07-01T00:00:00.000',N'2021-08-01T00:00:00.000')
-- 创建分区方案
CREATE PARTITION SCHEME HmTest_Order_Scheme AS PARTITION HmTest_Order_Func
TO([db_hmTest_OrderGroup202105],[db_hmTest_OrderGroup202106],[db_hmTest_OrderGroup202107],[db_hmTest_OrderGroup202108])
4）新建表/绑定现有表
通常情况下id作为表的主键，但在这里是按add_time作为分区关联列的。所以我选的方案是用id+add_time做联合主键-聚合索引
-- 创建表并绑定分区方案
IF OBJECT_ID(N'[dbo].[t_order]',N'U') IS NOT NULL
DROP TABLE [t_order];
CREATE TABLE [t_order](
id INT NOT NULL,
product NVARCHAR(200) NOT NULL,
size FLOAT NOT NULL,
add_time DATETIME2(2) NOT NULL
PRIMARY KEY(id,[add_time])
) ON HmTest_Order_Scheme([add_time])
-- 绑定现有表
CREATE CLUSTERED INDEX PK_hmTest_order_id_addTime ON
[dbo].[t_order_01]([id],[add_time])
WITH(SORT_IN_TEMPDB=OFF,DROP_EXISTING=OFF,ONLINE=OFF) ON
[HmTest_Order_Scheme]([add_time]);
存储过程 - 动态分区
通常做法是，数据库会做定时任务，每月第一天执行下面的存储过程，每月产生一个文件组和文件
-- 新建存储过程，每月新增一个文件组
CREATE PROCEDURE [Prop_HmOrder_AutoExtend_Partition]
AS
BEGIN
DECLARE @FilePath VARCHAR(100), --文件路径
@FileName VARCHAR(100), --文件名称
@FileSize VARCHAR(100), --文件大小
@FileGrowth VARCHAR(100), --文件增长
@FileMaxLimit VARCHAR(100), --文件最大限制
@FileGroupName VARCHAR(100), --文件组名称
@Database VARCHAR(100), --操作数据库
@CurrentDateTimeByYearAndMonth VARCHAR(100), --当前时间,年月
@SchemeName VARCHAR(100), --分区方案名称
@PartitionName VARCHAR(100), --分区函数名称
@sql VARCHAR(400); -- 赋值文件属性
SET @FileSize = '5MB';
SET @FileGrowth = '1MB';
SET @FileMaxLimit = 'unlimited';
SET @CurrentDateTimeByYearAndMonth = LEFT(CONVERT(VARCHAR(30), GETDATE(), 112), 6);
SET @FileName = 'db_hmTest_Order' + @CurrentDateTimeByYearAndMonth;
SET @FilePath = 'D:\DB\Partition';
-- 赋值数据库属性
SET @Database = 'db_hmTest';
-- 赋值文件组属性
SET @FileGroupName = 'db_hmTest_OrderGroup' + @CurrentDateTimeByYearAndMonth;
-- 赋值分区属性
SET @SchemeName = 'HmTest_Order_Scheme';
SET @PartitionName = 'HmTest_Order_Func()';
-- 创建文件组
SET @sql = 'alter database ' + @Database + ' add filegroup ' + @FileGroupName + '';
EXEC (@sql);
-- 创建文件，并绑定文件组
SET @sql = 'alter database ' + @Database + ' add file (name=''' + @FileName + ''',' + 'filename=''' + @FilePath + '\'
+ @FileName + '.mdf'',' + 'size = ' + @FileSize + ',' + 'filegrowth = ' + @FileGrowth + ',' + 'maxsize = '
+ @FileMaxLimit + '' + ')' + 'to filegroup ' + @FileGroupName;
EXEC (@sql);
-- 修改分区方案
SET @sql = 'alter partition scheme ' + @SchemeName + ' next used ' +
@FileGroupName + '';
EXEC (@sql);
-- 修改分区函数
SET @sql = 'alter partition function ' + @PartitionName + ' split range (N''' +CONVERT(VARCHAR(30), DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0), 120)+''')'
EXEC (@sql);
END;
GO
杂项列表
1）db_hmTest_OrderGroup202108会包含[2021-08-01T00:00:000，+∞)。后期动态新增文件组时，数据库会自动把[2021-09-01T00:00:000，+∞)里的数据分配到db_hmTest_OrderGroup202109
2）表一定要建主键，不然检索速度很慢
3）通过时间字段做分区关联列时，其它字段不可单独做聚合索引
