SqlServer 实现表分区

Love The Way You Lie 2023-10-07 11:00 19阅读 0赞

### 一、背景 ###

之前组长一直在弄可视化方面的性能优化主要还是数据查询方面的优化，他让我实验一下数据库进行表分区之后，查询速度会不会快一点。我得出的结论是，表分区用在700万条数据的表没什么太大的作用，反而影响这个数据量级别的SQL查询速度的因素主要有两个，一个是Sql语句不够优化，另一个是表没建索引或者表索引建的不对。

虽然表分区没起到什么作用，但是不妨碍我把表分区的步骤分享给大家。

首先表分区是按照年来进行分区。

### 二、创建文件组 ###

BEGIN 
    DECLARE @DataBaseName NVARCHAR(50)--数据库名称
    DECLARE @PStartDay DATE--分区开始时间点
    DECLARE @PEndDay DATE --分区截止时间点
    DECLARE @FGStr NVARCHAR(MAX) --文件组名
    DECLARE @sql NVARCHAR(max)
    SET @FGStr = ''
    DECLARE @Y INT	
    -- 设置数据库名	
    SET @DataBaseName = 'TestPartition'
    SET @PStartDay='2013-01-01'
    SET @PEndDay='2020-03-01'
    
    --1.创建文件组
    SET @Y = YEAR(@PStartDay)
    WHILE @Y <= YEAR(@PEndDay)
    BEGIN
        SET @FGStr = @Y
        SET @sql = 'ALTER DATABASE ['+@DataBaseName +'] ADD FILEGROUP [FG_'+@FGStr+']'
        EXEC(@sql)
        SET @Y = @Y+1
    END
    END

### 三、创建文件 ###

BEGIN 
    DECLARE @DataBaseName NVARCHAR(50)--数据库名称
    DECLARE @Location NVARCHAR(50)--保存分区文件的路径
    DECLARE @PStartDay DATE--分区开始时间点
    DECLARE @PEndDay DATE --分区截止时间点
    DECLARE @FGStr NVARCHAR(MAX) --文件组名
    DECLARE @FStr NVARCHAR(50) --文件名
    DECLARE @Size NVARCHAR(50)='1MB'--分区初始化大小,默认为10MB
    DECLARE @FileGrowth NVARCHAR(50)='1MB'--分区文件增量 ，默认值为100MB
    DECLARE @sql NVARCHAR(max)
    	
    -- 设置数据库名	
    SET @DataBaseName = 'TestPartition'
    SET @Location = 'D:\数据库\data\'
    SET @PStartDay='2013-01-01'
    SET @PEndDay='2020-03-01'
    SET @FGStr = ''
    SET @FStr = ''
    DECLARE @Y INT
    
    --2.创建文件
    SET @Y = YEAR(@PStartDay)
    WHILE @Y <=YEAR(@PEndDay)
    BEGIN
        SET @FGStr=@Y
        SET @FStr=@Y
        SET @sql = 'ALTER DATABASE ['+@DataBaseName +']
        ADD FILE
        (NAME = N''FG_'+@FStr+'_data'',FILENAME =                 
        N'''+@Location+'FG_'+@FStr+'_data.ndf'',SIZE = '+@Size+
        ', FILEGROWTH = '+@FileGrowth+' )
        TO FILEGROUP [FG_'+@FGStr+'];'
        EXEC(@sql)
        SET @Y = @Y+1
    END
    
    END

注：执行创建文件SQL语句之前，请确保文件所存放目录存在，否则会创建文件失败。

### 四、创建分区函数 ###

BEGIN 
    DECLARE @ColumnName NVARCHAR(50)--字段名称
    DECLARE @PStartDay DATE--分区开始时间点
    DECLARE @PEndDay DATE --分区截止时间点
    DECLARE @sql NVARCHAR(max)
    DECLARE @FunValueStr NVARCHAR(max) =''
    	
    -- 设置参数	
    SET @ColumnName = 'TimeDomain'
    SET @PStartDay='2013-01-01'
    SET @PEndDay='2020-03-01'
    SET @FGStr = ''
    SET @FStr = ''
    DECLARE @Y INT
    
    --3.创建分区函数
    SET @Y = YEAR(@PStartDay)
    WHILE @Y <=YEAR(@PEndDay)
    BEGIN
        BEGIN
            IF @Y < YEAR(@PEndDay)  
                SET @FunValueStr = @FunValueStr +''''+CAST(@Y AS NVARCHAR(4))+'-01-01'''+',' 
            ELSE
                SET @FunValueStr = @FunValueStr +''''+CAST(@Y AS NVARCHAR(4))+'-01-01'''
        END
        SET @Y = @Y+1
    END
      
    SET @sql = 'CREATE PARTITION FUNCTION 
    Fun_'+@ColumnName +'(datetime) AS RANGE RIGHT FOR VALUES('+@FunValueStr+')'
    EXEC(@sql)
    
    END

### 五、创建分区方案 ###

BEGIN 
    DECLARE @ColumnName NVARCHAR(50)--字段名称
    DECLARE @PStartDay DATE--分区开始时间点
    DECLARE @PEndDay DATE --分区截止时间点
    DECLARE @FGStr NVARCHAR(MAX) --文件组名
    DECLARE @sql NVARCHAR(max)
    DECLARE @FunValueStr NVARCHAR(max) =''
    	
    -- 设置参数
    SET @ColumnName = 'TimeDomain'
    SET @PStartDay='2013-01-01'
    SET @PEndDay='2020-03-01'
    SET @FGStr = ''
    DECLARE @Y INT
    
    --4.创建分区方案
    SET @Y = YEAR(@PStartDay)
    SET @FGStr = ''
    WHILE @Y<=YEAR(@PEndDay)
    BEGIN
        BEGIN
            IF @Y<YEAR(@PEndDay)
                SET @FGStr =@FGStr+'[FG_'+CAST(@Y AS nvarchar(4))+'],'
            ELSE 
                SET @FGStr =@FGStr+'[FG_'+CAST(@Y AS nvarchar(4))+']'
        END
        SET @Y=@Y+1
    END
    SET @sql = 'CREATE PARTITION SCHEME SC_'+@ColumnName+' AS
    PARTITION  Fun_'+@ColumnName+'
    TO('+@FGStr+',[Primary])'
    EXEC(@sql)
    
    END

### 六、进行分区 ###

手动分区步骤：

点击要分区的表右击->存储->创建分区->选择分区列->选择现有分区函数->选择现有分区方案->立即执行

等待分区完成之后执行以下SQL语句查看是否分区完成

select \* from sysfiles

分区完成效果图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200313131835944.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM4OTc0NjM4,size_16,color_FFFFFF,t_70