Sqlserver分区笔记

当一个数据表太大，且数据还在进一步增长情况下，查询会越来越慢，这个时候我们就考虑怎么优化，表分区是其中一个方式，将一个文件分成多个文件，使得数据文件的读取速度变快。下面直接进行分区语句创建，根据语句来理解分区如何使用。

1、分区即为分文件，创建文件组，文件组对应一个文件

--1、创建文件组
ALTER  DATABASE  demo_metro ADD  FILEGROUP  Q_5S_fileGroup_202101;
--2、创建分区文件
ALTER DATABASE demo_metro ADD  FILE (NAME =N'Q_5S_file_202101',FILENAME=N'D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Q_5S_file_202101.ndf',size=5Mb,filegrowth=5mb) TO  FILEGROUP  Q_5S_fileGroup_202101

2、创建分区函数

--3、创建分区函数
CREATE PARTITION FUNCTION [Q_5S_PartitionFun](DATETIME) AS RANGE RIGHT FOR VALUES (N'2021-01-01')

分区函数指定分区规则，本例按照月份分文件，将文件按照时间轴2021-01-01分割成2个部分，2021-01-01之前一个文件，2021-01-01之后一个文件

3、创建分区方案

--4、创建分区方案
CREATE PARTITION SCHEME [Q_5S_PartitionSchema] AS PARTITION [Q_5S_PartitionFun] TO ([PRIMARY], Q_5S_fileGroup_202101)

分区方案就是为分区函数添加文件，本例2021-01-01之前写入[PRIMARY],2021-01-01之后的数据写入 Q_5S_fileGroup_202101

根据下图，可以较好的理解分区方案，每个文件组文件对应一个时间段。

4、为表设置分区方案（为分区条件字段设置聚集索引）

资料说明主键Id不可为聚集索引，需要为非聚集索引才行(未验证)

--5、为现有表设置分区方案
CREATE CLUSTERED INDEX IX_CreateDate ON Q_5S(Time)
ON [Q_5S_PartitionSchema] (Time)

5、至此分区已经完成，插入数据，数据自动归属各自分区（按照时间区分）

6、分区完成，但是既然是按照时间区分，那么自然需要按照时间动态创建分区文件，比如每个月生成一个新的文件组和文件，如下图

直接上存储过程，存储过程需要在每月的一号执行一次（使用作业，作业使用方式后续补全）

-- =============================================  
-- Author:      zzheng  
-- Create date: 2021-01-28  
-- Description: 动态分区
-- =============================================  
ALTER PROCEDURE  [dbo].[proc_create_table_partition]  
    (@now DATETIME,@dbname NVARCHAR(20),@tablename NVARCHAR(50),@schemanname NVARCHAR(50),@functionname NVARCHAR(50))  
AS  
BEGIN  
    --文件组名称  
    DECLARE  @fileGroupName VARCHAR(32);  
    --文件名称  
    DECLARE  @fileName VARCHAR(512);  
    -- 保存202001这样的日期  
    DECLARE @yyyymm VARCHAR(8);  
    -- 保存 2021-01-01 这样的日期  
    DECLARE @yyyy_mm_dd VARCHAR(10);
    SET @yyyymm='_'+CONVERT (VARCHAR(6),@now,112);
    SET @yyyy_mm_dd= CONVERT (VARCHAR(7),@now,120)+'-01';
    --定义文件组名称  
    SET @fileGroupName=@tablename+'_fileGroup'+@yyyymm;
    --判断组是否已经创建  
    IF NOT  EXISTS(SELECT  1 FROM  sys.filegroups WHERE  name =@fileGroupName)   
    BEGIN   
        --创建文件组，因为alter database 这些是不支持事务的，所以只能通过条件来控制  
        EXEC('alter database '+@dbname+' add filegroup '+@fileGroupName);
    END
    --print @yyyymm;
    --创建文件，并分追加到组中  
    IF NOT  EXISTS(SELECT 1 FROM sys.database_files WHERE name=@tablename+'_file'+@yyyymm) 
	  BEGIN    
        --查询主文件目录  
		--D:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\test
        SELECT  @fileName=SUBSTRING(physical_name,1,LEN(physical_name)-LEN(name)-4) FROM  master.sys.master_files WHERE type_desc='ROWS' and name=@dbname   
        --完整的文件名称  
         SET @fileName=@fileName+@tablename+'_file'+@yyyymm+'.ndf';  
          
        --创建文件  
         EXEC('alter database '+@dbname+' add file (name=N'''+@tablename+'_file'+@yyyymm+''',filename=N'''+@fileName+''',size=10Mb,filegrowth=2mb) to filegroup '+@fileGroupName)
        --修改分区方案(方案依赖函数，所以先修改方案)  
         exec('ALTER PARTITION SCHEME ['+@schemanname+']  NEXT USED '+@fileGroupName)
        --修改分区函数，添加分区  
		--ALTER PARTITION FUNCTION [COP_5S_PartitionFun]() SPLIT RANGE(N''+@yyyy_mm_dd);
		 exec('alter partition function '+@functionname+'() split range('''+@yyyy_mm_dd+''')')
         
     END      
END

7、附上查询分区数据

--$partition.COP_5S_PartitionFun(time)=3：分区编码
SELECT * FROM dbo.COP_5S WHERE  $partition.COP_5S_PartitionFun(time)=3
--根据时间点（分区函数分隔时间）来查询该分区的数据
SELECT COUNT(1) FROM COP_5S(NOLOCK) WHERE $partition.COP_5S_PartitionFun(time)=$partition.COP_5S_PartitionFun('2021-02-01')

写在最后的一些经验：

表分区之后删除很麻烦，需要彻底删除文件、文件组、分区函数、分区方案、表分区字段索引。而删除这些的前提是表没有分区或者不是当前绑定的当前分区方案，因此可以新建一个临时分区策略用于绑定（无法直接解绑），绑定完之后数据自动移到新的方案文件下，之后可以删除旧的策略、函数、文件、文件组；

--1、删除旧索引
DROP INDEX IX_Time_Data_Point
--2、绑定新方案，假如已经创建好方案Temp_PartitionSchema
CREATE CLUSTERED INDEX IX_Time_Data_Point ON data_Points(Time)
ON [Temp_PartitionSchema] (Time)