SQL Server 分区(PARTITION)

dba 发布于 2024-01-03 阅读(31)

SQL SERVER 分区(PARTITION)

分区说明#

将数据放在不同的物理设备中

分区的好处#

提高可维护性,降低维护难度

提高性能

分区的过程步骤#

创建分区函数
创建分区方案
将分区方案应用到表上

可以在以下对象上分区:
无聚集索引的表(堆)
聚集索引
唯一索引
非聚集索引
注意:聚集索引分区时,需要指定的分区必须包含在聚集键中
如果要将分区的聚集或者非聚集索引不唯一,则分区列无需包含在键中
对于唯一索引(不论聚集非聚集)都必须包含分区列作为唯一索引键的一部分

创建分区函数#

创建分区函数作用:指定根据表的某一列数据,将数据分配或映射到不同的分区中

注意:不能用作分区的字段数据类型是:
ext、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)
自定义的别名数据类型、公共语言运行库(CLR)用户定义数据类型

常用的分区函数使用的数据类型是:日期类型 和 整数类型
表或索引可以创建的最大分区数量为15000个

通常为了简化维护、改进性能,建议将每个分区放置在不同的文件组中
创建文件组时,务必使文件组的数据比分区函数中指定的边界值的数据多一个
文件组应当放置在不同的物理磁盘上,以分摊I/O开销

CREATE PARTITION FUNCTION [分区名] (分区数据类型)AS RANGE [LEFT | RIGHT]FOR VALUES('分割边界值','分割边界值','分割边界值',...);

创建分区方案#

创建分区方案的作用:
将数据放在正确的文件组中
将分区函数创建的分区与文件组对应起来

创建分区方案需要设置的4个参数:
分区方案的名称
分区函数的名称,在分区方案创建之前必须先创建分区函数
ALL,使用该参数,文件组的数量为1
文件组列表

语法:

CREATE PARTITION SCHEME [分区方案名称]AS PARTITION [分区函数名称]TO(对应文件组1,对应文件组2,对应文件组3,对应文件组4);

将分区应用到表上-使用T-SQL#

创建表时指定分区语法:

CREATE TABLE [表名]
(

) ON 分区方案名称(表中与分区对应的字段)

将分区应用到索引上-使用T-SQL#

聚集索引分区:

CREATE CLUSTERED INDEX [索引名]ON [表名](创建索引的字段名)WITH(DROP_EXISTING = ON)ON [分区方案名] (表中与分区方案对应的字段名);

创建表分区-使用SSMS#

选中要分区的表或者索引
image

选择分区的列
image

选择新建分区函数 或 使用已经定义的分区函数
image

选择新建分区方案 或 使用已经建好的分区方案
image

确定分区方案的 范围 和 文件组
image

创建索引分区-使用SSMS#

选择要进行分区的索引,进入分区页面

实例:为表创建分区方案#

新建数据库#

GO-- 创建分区数据库CREATE DATABASE [PandaDatabase2]ON PRIMARY(
    NAME = 'PandaDatabase2',
    FILENAME = 'C:\test2\PandaDatabase2.mdf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
)
LOG ON(
    NAME = 'PandaDatabase2Log',
    FILENAME = 'C:\test2\PandaDatabase2Log.ldf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED,
);
GO

创建数据库文件组#

GOALTER DATABASE [PandaDatabase2]ADD FILEGROUP FileGroup2018;ALTER DATABASE [PandaDatabase2]ADD FILE
(
    NAME = 'PandaDatabase2_2018',
    FILENAME = 'C:\test2\PandaDatabase2_2018.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED
) TO FILEGROUP [FileGroup2018];ALTER DATABASE [PandaDatabase2]ADD FILEGROUP FileGroup2019;ALTER DATABASE [PandaDatabase2]ADD FILE
(
    NAME = 'PandaDatabase2_2019',
    FILENAME = 'C:\test2\PandaDatabase2_2019.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED
) TO FILEGROUP [FileGroup2019];ALTER DATABASE [PandaDatabase2]ADD FILEGROUP FileGroup2020;ALTER DATABASE [PandaDatabase2]ADD FILE
(
    NAME = 'PandaDatabase2_2020',
    FILENAME = 'C:\test2\PandaDatabase2_2020.ndf',
    SIZE = 100MB,
    MAXSIZE = UNLIMITED
) TO FILEGROUP [FileGroup2020];ALTER DATABASE [PandaDatabase2]ADD FILEGROUP FileGroup2021;

GO

创建分区函数#

GO-- 创建分区函数CREATE PARTITION FUNCTION [PandaDatabaseFunction] (DATETIME)AS RANGE LEFTFOR VALUES('20181231','20191231','20201231');
GO

创建分区方案#

GO-- 创建分区方案-- 注意:分区方案中的文件组必须多于分区函数中的个数CREATE PARTITION SCHEME [PandaDatabaseSchema]AS PARTITION [PandaDatabaseFunction]TO(FileGroup2018,FileGroup2019,FileGroup2020,FileGroup2021);
GO

应用到指定表#

GO-- 应用到指定表-- 注意不可以有主键约束USE PandaDatabase2;CREATE TABLE PandaTable4
(
    Id INT,
    Name CHAR(30) NOT NULL,
    join_datetime DATETIME
)ON PandaDatabaseSchema(join_datetime);

测试插入数据#

INSERT INTO [PandaTable4] VALUES(1,'Panda666','2020-3-30');INSERT INTO [PandaTable4] VALUES(2,'Dog666','2019-3-30');INSERT INTO [PandaTable4] VALUES(3,'Dog666','2018-3-30');INSERT INTO [PandaTable4] VALUES(4,'Dog666','2018-2-10');