以某一日志表robot_golds_log为例按月分区ctime分区 从2022年6月1日到-2022年12月1日
一,创建文件组
2022-06-01 1654012800
2022-07-01 1656604800
2022-08-01 1659283200
2022-09-01 1661961600
2022-10-01 1664553600
2022-11-01 1667232000
2022-12-01 1669824000
N个边界值确定 N+1 个分区
use sblack go ALTER DATABASE sblack ADD FILEGROUP order1; go ALTER DATABASE sblack ADD FILEGROUP order2; go ALTER DATABASE sblack ADD FILEGROUP order3; go ALTER DATABASE sblack ADD FILEGROUP order4; go ALTER DATABASE sblack ADD FILEGROUP order5; go ALTER DATABASE sblack ADD FILEGROUP order6; go ALTER DATABASE sblack ADD FILEGROUP order7; go ALTER DATABASE sblack ADD FILE ( NAME = order1dat1, FILENAME = 'E:\psdata\order1.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP order1; go ALTER DATABASE sblack ADD FILE ( NAME = order2dat2, FILENAME = 'E:\psdata\order2.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP order2; go ALTER DATABASE sblack ADD FILE ( NAME = order3dat3, FILENAME = 'E:\psdata\order3.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP order3; go ALTER DATABASE sblack ADD FILE ( NAME = order4dat4, FILENAME = 'E:\psdata\order4.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP order4; go ALTER DATABASE sblack ADD FILE ( NAME = order5dat5, FILENAME = 'E:\psdata\order5.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP order5; go ALTER DATABASE sblack ADD FILE ( NAME = order6dat6, FILENAME = 'E:\psdata\order6.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP order6; go ALTER DATABASE sblack ADD FILE ( NAME = order7dat7, FILENAME = 'E:\psdata\order7.ndf', SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) TO FILEGROUP order7; go
二,创建分区函数
CREATE PARTITION FUNCTION PF_Orders_Ctime(ctime) AS RANGE RIGHT FOR VALUES ( 1654012800, 1656604800, 1659283200, 1661961600, 1664553600, 1667232000, )
三,创建分区方案
CREATE PARTITION SCHEME PS_Orders AS PARTITION PF_Orders_Ctime TO (order1, order2, order3, order4,order5,order6,order7);
四,创建使用分区的表
--创建分区表语法
create table <表名> (
<列定义>
)on<分区方案名>(分区列名)
use sblack go CREATE TABLE [dbo].[robot_golds_log]( [id] [BIGINT] IDENTITY(1,1) NOT NULL, [robot_id] [INT] NOT NULL, [source_type] [SMALLINT] NOT NULL, [kind_id] [INT] NOT NULL, [room_id] [INT] NOT NULL, [score] [BIGINT] NOT NULL, [play_time] [INT] NOT NULL, [ctime] [INT] NOT NULL, [memo] [NVARCHAR](256) NULL, [score_type] [TINYINT] NOT NULL, CONSTRAINT [PK_robot_golds_log1_tmp] PRIMARY KEY CLUSTERED ( [id] ASC, [ctime] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PS_Orders]([ctime]) ) ON [PS_Orders] ([ctime])
索引也基本也要创建分区索引,例:
CREATE NONCLUSTERED INDEX [ix_ctime_source_type] ON [dbo].[robot_golds_log] ( [ctime] ASC, [source_type] ASC )WITH (online = ON) ON [PS_Orders]([ctime]) GO
五,查看依据分区列所在的分区
select $partition.PF_Orders_Ctime(1654099200) 返回2 说明在第2个分区
六,查看每个非空分区存在的行数
SELECT $partition.PF_Orders_Ctime(ctime) as partitionNum,count(*) as recordCount FROM dbo.robot_golds_log GROUP BY $partition.PF_Orders_Ctime(ctime)
七,查看各个分区的数据信息
SELECT PARTITION = $PARTITION.PF_Orders_Ctime(ctime), ROWS = COUNT(*), MinVal = MIN(ctime), MaxVal = MAX(ctime) FROM [dbo].[robot_golds_log] GROUP BY $PARTITION.PF_Orders_Ctime(ctime) ORDER BY PARTITION
八,拆分分区
alter partition function PF_Orders_Ctime() split range(1688140800) --2023-07-01
如果分区函数已经指定了分区方案,则分区数需要和分区方案中指定的文件组个数保持对应一致
九,合并分区
alter partition function PF_Orders_Ctime() merge range(1688140800)
十,分区移动
切换分区为3的数据从分区表到归档表
ALTER TABLE dbo.robot_golds_log SWITCH PARTITION 3
TO dbo.robot_golds_log_2022
注意:归档表 robot_golds_log_2022 要和 原表在一个文件组上 创建表的时候指定文件组 on [文件组]
把归档表的数据切到分区表
ALTER TABLE dbo.robot_golds_log_2022 SWITCH TO
dbo.robot_golds_log PARTITION 3
归档表要添加约束
alter table robot_golds_log_2022 ADD CONSTRAINT CK_robot_golds_log_ctime CHECK(ctime>=1656604800 and ctime<1659283200) 第3个分区的起始时间