MS SQL Server：如何按月创建表分区？

拼搏现实的明天。 2022-06-12 02:55 456阅读 0赞

比如有如下表需要以crt\_time为标准按月分区

create table gf_card (
      id            bigint not null,
      crt_time        datetime not null,
      name            varchar(20) not null，
      CONSTRAINT PK_card_id PRIMARY KEY (id asc)
    )

(1).一般会将表按每个月的数据放入不同的文件组中，先给每个月建一个文件组

--创建文件组
    alter database test add filegroup fileGroup01
    alter database test add filegroup fileGroup02
    alter database test add filegroup fileGroup03
    alter database test add filegroup fileGroup04
    alter database test add filegroup fileGroup05
    alter database test add filegroup fileGroup06
    alter database test add filegroup fileGroup07
    alter database test add filegroup fileGroup08
    alter database test add filegroup fileGroup09
    alter database test add filegroup fileGroup10
    alter database test add filegroup fileGroup11
    alter database test add filegroup fileGroup12

如果要删除文件组可使用：

--删除文件组
    alter database test remove filegroup  fileGroup01

(2).一个文件组可以包含多个文件，为了提高性能文件一般放在多个不同的物理盘上，创建了文件组后需要指定这些文件组中包含哪些文件

---创建数据文件到文件组
    alter database test add file (name='test01',filename=N'D:\tmp\data\test01.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup01
    alter database test add file (name='test02',filename=N'D:\tmp\data\test02.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup02
    alter database test add file (name='test03',filename=N'D:\tmp\data\test03.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup03
    alter database test add file (name='test04',filename=N'D:\tmp\data\test04.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup04
    alter database test add file (name='test05',filename=N'D:\tmp\data\test05.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup05
    alter database test add file (name='test06',filename=N'D:\tmp\data\test06.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup06
    alter database test add file (name='test07',filename=N'D:\tmp\data\test07.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup07
    alter database test add file (name='test08',filename=N'D:\tmp\data\test08.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup08
    alter database test add file (name='test09',filename=N'D:\tmp\data\test09.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup09
    alter database test add file (name='test10',filename=N'D:\tmp\data\test10.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup10
    alter database test add file (name='test11',filename=N'D:\tmp\data\test11.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup11
    alter database test add file (name='test12',filename=N'D:\tmp\data\test12.ndf',size=5Mb,filegrowth=5mb) to filegroup fileGroup12

如果要删除数据文件到文件组可使用：

---删除数据文件到文件组
    alter database test remove file  test01

(3). 分区函数是为了划分分区边界，range left表示\{，value\]，(value，\}，range right表示\{，value)，\[value，\}

--创建分区函数
    create partition function [PF_card] (datetime) as range right for values
    ('2017-01-01','2017-02-01','2017-03-01','2017-04-01','2017-05-01','2017-06-01',
    '2017-07-01','2017-08-01','2017-09-01','2017-10-01','2017-11-01','2017-12-01')

如果要删除分区函数可使用：

--删除分区函数
    drop partition function [PF_card]

(4).有了分区函数后，需要指定不同分区的数据放到哪个文件组中

--创建分区方案
    CREATE PARTITION SCHEME [PS_card] AS PARTITION [PF_card]
    TO ([PRIMARY], [fileGroup01], [fileGroup02], [fileGroup03], [fileGroup04], [fileGroup05], 
    [fileGroup06], [fileGroup07], [fileGroup08], [fileGroup09], [fileGroup10], [fileGroup11], [fileGroup12])

如果要删除分区方案

--删除分区方案
    drop PARTITION SCHEME [PS_card]

(5).上面创建了2017年12个月的分区，如果要添加2018年12个月的分区并且复用fileGroup01~fileGroup12

--修改边界值
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup01]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-01-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup02]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-02-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup03]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-03-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup04]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-04-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup05]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-05-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup06]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-06-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup07]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-07-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup08]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-08-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup09]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-09-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup10]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-10-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup11]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-11-01')
    
    ALTER PARTITION SCHEME [PS_card] NEXT USED [fileGroup12]
    alter partition function [PF_card]()  SPLIT RANGE ('2018-12-01')

(6) .按分区创建表和索引

create table gf_card (
      id            bigint not null,
      crt_time        datetime not null,
      name            varchar(20) not null
    ) on [PS_card] (crt_time)

对于分区表，如果要建主键，必须把分区依据列crt\_time包含在内，同时如果要建唯一索引也要把分区依据列crt\_time包含在内，所以分区表不能再单独对id建主键，也不能单独对id建唯一索引，但可以建非唯一索引

-- 将索引放在[PRIMARY]文件组中
    CREATE CLUSTERED INDEX [IX_card_id] ON gf_card (id) ON  [PRIMARY]

由于crt\_time查询时经常会用到，所以在crt\_time上也建索引，并且也将各分区的索引放入各分区中

CREATE NONCLUSTERED INDEX [IX_card_crt_time] ON gf_card (crt_time) on [PS_card] (crt_time)

(7).插入一些数据测试

insert into gf_card (id, crt_time, name) values (99, '2017-07-01', 'n99')
    insert into gf_card (id, crt_time, name) values (100, '2017-07-01', 'n100')
    insert into gf_card (id, crt_time, name) values (101, '2017-07-01', 'n101')
    insert into gf_card (id, crt_time, name) values (200, '2017-07-02', 'n102')
    insert into gf_card (id, crt_time, name) values (600, '2017-07-01', 'n600')

查询表中数据在哪个分区上

select *, $partition.PF_card(crt_time) partitionNum from gf_card

> **id        crt\_time                                   name    partitionNum**  
> 99      2017-01-01 00:00:00.000    n99        2  
> 100    2017-07-01 00:00:00.000    n100     8  
> 101    2017-11-01 00:00:00.000    n101     12  
> 200    2018-01-02 00:00:00.000    n102     14  
> 600    2018-07-01 00:00:00.000    n600      20

从结果中可以看到fileGroup01的分区号为2，fileGroup07的分区号为8，primary的分区号自然就为1 了

--查询数据在哪个分区上
    select $partition.[PF_card]('2017-07-01')

--查询表中数据在哪个分区上
    select $partition.[PF_card](id)
    from gf_card

-- 查询表有哪些分区
    SELECT *
    FROM sys.partitions AS p JOIN sys.tables AS t  ON  p.object_id = t.object_id
      WHERE p.partition_id IS NOT NULL
          AND t.name = 'gf_card';

(8).将分区中的数据删除

比如只删除fileGroup01中的数据，由于直接使用truncate删除分区中的数据要到SQL server2016才支持，所以使用一种通用的方法来删除。  
创建一个与gf\_card列相同的表gf\_card\_tmp，并且此表也要建在fileGroup01中

create table gf_card_tmp (
      id              bigint not null,
      crt_time        datetime not null,
      name            varchar(20) not null
    ) on [fileGroup01]

将分区2中的数据转移到gf\_card\_tmp中，此时gf\_card分区2中就没有数据了

alter table gf_card switch partition 2 to gf_card_tmp

删除gf\_card\_tmp中的数据

truncate table gf_card_tmp

(9).如何查询库中有哪些PartitionScheme和PartitionFunction？

select ps.Name PartitionScheme, pf.name PartitionFunction
     from sys.indexes i
     join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
     join sys.partition_functions pf on pf.function_id = ps.function_id
    where i.object_id = object_id('gf_card')