SQL SERVER之分区表创建

SQL SERVER分区表的建立
以某一日志表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个分区的起始时间

posted @ 2022-06-02 16:11 sblack 阅读(354) 评论(0) 收藏举报

刷新页面返回顶部

黑大叔

SQL SERVER之分区表创建

公告