功能描述
适用于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–