SqlServer动态分区表的使用方法

最近由于项目的需要，有一张数据表的数据量突增，导致查询的效率变低，即使优化了一定的索引结构后，既然不能解决，当然可以通过分表分库的方法进行解决，但是暂时又不想去改变程序结构。通过查阅了一些资料，打算采用SqlServer分区表的功能来进行尝试。由于这张数据标需要按日进行分区，所以我们需要一种方法，能够实现动态的分区表生成。以下就上述的目前展开方法的讨论，步骤如下：

1. 创建分区文件组：

ALTER DATABASE [dbname] ADD FILEGROUP filegroup20220304;

2. 创建分区文件

ALTER DATABASE [dbname] ADD FILE(NAME=N'dbfile20220304',FILENAME=N'D:\CallCenter\Db\dbfile20220304.mdf',SIZE=10MB,FILEGROWTH=10MB,MAXSIZE=UNLIMITED) TO FILEGROUP [filegroup20220304]

3. 创建分区函数

CREATE PARTITION FUNCTION PARTITION_FUNCTION_CtiRecordLogInfo(DATETIME) AS RANGE RIGHT FOR VALUES()

这个我们指定分区函数的分区参数为datetime类型，分区值暂时为空

4. 创建分区方案

CREATE PARTITION SCHEME CtiRecordLogInfo_Scheme AS PARTITION PARTITION_FUNCTION_CtiRecordLogInfo TO(filegroup20220304)

5. 绑定到新建表/已有数据表

--将分区方案绑定到新建表
CREATE TABLE [dbo].[Cti_RecordLogInfoTest](
	[F_Id] [varchar](50) NOT NULL,
	[F_LogTime] [datetime] NOT NULL,
	[F_Param] [varchar](200) NULL
) ON CtiRecordLogInfo_Scheme(F_LogTime)
--将分区方案绑定到现有表
CREATE CLUSTERED INDEX PK_CtiRecordLogInfo_id_logtime ON [dbo].[Cti_RecordLogInfoTest]([F_Id],[F_LogTime]) WITH(SORT_IN_TEMPDB=OFF,DROP_EXISTING=OFF,ONLINE=OFF) ON CtiRecordLogInfo_Scheme(F_LogTime);

至此为止，我们就已经为数据表创建了一个分区，接下来我们进一步就需要来动态创建日分区了，这就需要配合定时调用存储过程来实现了，基本的存储过程如下：

-- 新建存储过程，每日新增一个文件组
CREATE PROCEDURE [Cti_RecordLogInfo_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),                      --操作数据库
            @CurrentDateTimeByDay VARCHAR(100), --当前时间,日
            @SchemeName VARCHAR(100),                    --分区方案名称
            @PartitionName VARCHAR(100),                 --分区函数名称
            @sql VARCHAR(400);    -- 赋值文件属性
    SET @FileSize = '5MB';
    SET @FileGrowth = '1MB';
    SET @FileMaxLimit = 'unlimited';
    SET @CurrentDateTimeByDay = CONVERT(VARCHAR(30), GETDATE(), 112);
    SET @FileName = 'dbfile' + @CurrentDateTimeByDay;
    SET @FilePath = 'D:\CallCenter\Db';
    -- 赋值数据库属性
    SET @Database = 'dbname';
    -- 赋值文件组属性
    SET @FileGroupName = 'filegroup' + @CurrentDateTimeByDay;
    -- 赋值分区属性
    SET @SchemeName = 'cti_recordloginfo_Scheme';
    SET @PartitionName = 'cti_recordloginfo_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(10), getdate(), 120)+''')'	
	EXEC (@sql);
END;
GO

有时为了验证分区数据插入到了对应的分区文件，可以通过以下的语句验证每个分区的记录数：

SELECT $PARTITION.PARTITION_FUNCTION_CtiRecordLogInfoTest(F_LogTime) AS 分区编号, COUNT(1) AS 记录数 
FROM Cti_RecordLogInfoTest
GROUP BY $PARTITION.PARTITION_FUNCTION_CtiRecordLogInfoTest(F_LogTime)

至此我们完成了数据表的动态分区功能。