sqlserver 表分区优化

功能描述

适用于sqlserver 2005+
当数据逐年增长，存储量超过5年时，可以考虑根据业务时间进行表分区优化，操作流程如下：

添加文件组

-- 添加文件组
ALTER DATABASE [testdb] ADD FILEGROUP [fg2011]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2012]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2013]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2014]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2015]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2016]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2017]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2018]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2019]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2020]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2021]
GO
ALTER DATABASE [testdb] ADD FILEGROUP [fg2022]
GO

添加数据文件

-- 添加数据文件
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2011', FILENAME = N'd:\database\testdb\f2011.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2011]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2012', FILENAME = N'd:\database\testdb\f2012.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2012]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2013', FILENAME = N'd:\database\testdb\f2013.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2013]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2014', FILENAME = N'd:\database\testdb\f2014.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2014]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2015', FILENAME = N'd:\database\testdb\f2015.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2015]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2016', FILENAME = N'd:\database\testdb\f2016.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2016]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2017', FILENAME = N'd:\database\testdb\f2017.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2017]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2018', FILENAME = N'd:\database\testdb\f2018.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2018]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2019', FILENAME = N'd:\database\testdb\f2019.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2019]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2020', FILENAME = N'd:\database\testdb\f2020.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2020]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2021', FILENAME = N'd:\database\testdb\f2021.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2021]
GO
ALTER DATABASE [testdb] ADD FILE ( NAME = N'f2022', FILENAME = N'd:\database\testdb\f2022.ndf' , SIZE = 10240KB , FILEGROWTH = 102400KB ) TO FILEGROUP [fg2022]
GO
-- 系统视图
-- 查看数据文件与文件分组对应关系
SELECT b.name AS data_space
    , b.is_default
    , a.name AS file_name
    , a.physical_name
    , a.size * 8 / 1024 AS size_m
FROM sys.database_files a
    , sys.data_spaces b
WHERE a.data_space_id = b.data_space_id
;

创建分区函数

-- 创建分区函数
CREATE PARTITION FUNCTION rpf_date (datetime)
AS RANGE RIGHT FOR VALUES (
  cast('2012-01-01' as datetime)
, cast('2013-01-01' as datetime)
, cast('2014-01-01' as datetime)
, cast('2015-01-01' as datetime)
, cast('2016-01-01' as datetime)
, cast('2017-01-01' as datetime)
, cast('2018-01-01' as datetime)
, cast('2019-01-01' as datetime)
, cast('2020-01-01' as datetime)
, cast('2021-01-01' as datetime)
) ;
GO

创建分区方案

-- 创建分区方案
CREATE PARTITION SCHEME rps_date
AS PARTITION rpf_date
TO (fg2011, fg2012, fg2013, fg2014, fg2015, fg2016, fg2017, fg2018, fg2019, fg2020, fg2021, fg2022) ;
GO

创建普通表

-- 创建普通表并初始化数据
SELECT dateadd (mi, rid, CAST ('2011-01-01' AS DATETIME)) AS rq
    , a. * 
INTO t1 
FROM (SELECT row_number () OVER (ORDER BY a.name) AS rid
         , a. *
     FROM sys.tables a
         , sys.tables b) a

-- 数据分布情况统计
SELECT convert (VARCHAR (4), rq, 21) AS rq
    , count (1) AS sl
FROM t1
GROUP BY convert (VARCHAR (4), rq, 21)
ORDER BY 1

创建分区表

-- 创建分区表
CREATE TABLE t1_pt
	(
	rq                           DATETIME NULL,
	rid                          BIGINT NULL,
	name                         SYSNAME NOT NULL,
	object_id                    INT NOT NULL,
	principal_id                 INT NULL,
	schema_id                    INT NOT NULL,
	parent_object_id             INT NOT NULL,
	type                         CHAR (2) NOT NULL,
	type_desc                    NVARCHAR (60) NULL,
	create_date                  DATETIME NOT NULL,
	modify_date                  DATETIME NOT NULL,
	is_ms_shipped                BIT NOT NULL,
	is_published                 BIT NOT NULL,
	is_schema_published          BIT NOT NULL,
	lob_data_space_id            INT NULL,
	filestream_data_space_id     INT NULL,
	max_column_id_used           INT NOT NULL,
	lock_on_bulk_load            BIT NOT NULL,
	uses_ansi_nulls              BIT NULL,
	is_replicated                BIT NULL,
	has_replication_filter       BIT NULL,
	is_merge_published           BIT NULL,
	is_sync_tran_subscribed      BIT NULL,
	has_unchecked_assembly_data  BIT NOT NULL,
	text_in_row_limit            INT NULL,
	large_value_types_out_of_row BIT NULL
	) ON rps_date (rq) 
GO

-- 写入分区表数据
INSERT INTO t1_pt SELECT * FROM t1;

性能评估

-- 数据查询性能评估
-- 普通表
SELECT count(1) 
FROM t1 
WHERE rq>='2013-01-01' AND rq < '2014-01-01'

-- 分区表
SELECT count(1) 
FROM t1_pt
WHERE rq>='2013-01-01' AND rq < '2014-01-01'

• 普通表
  
• 分区表
  
  在不创建任何索引的情况下，普通表与分区表的性能对比情况如下：

类型	消耗（TotalSubtreeCost）
普通表	34.58
分区表	3.63

–END–