表分区

缺乏、安全感 2022-07-16 19:17 232阅读 0赞

将数据库分区可提高其性能并易于维护。如果将大型表拆分为多个较小的表，则只访问部分数据的查询可以更快地运行，因为要扫描的数据变得更少。维护任务（例如，重新生成索引或备份表）也可以更快地运行.  
如果将某个表放置在一个物理驱动器上，将相关表放置在另一个驱动器上，则可以提高查询性能，因为当运行涉及表间联接的查询时，多个磁盘头同时读取数据。可以使用 SQL Server 文件组来指定放置表的磁盘。

在对表或索引进行分区前，必须计划创建下列数据库对象：

1、分区函数   2、分区方案

分区函数定义如何根据某些列（称为分区依据列）的值将表或索引的行映射到一组分区。

分区方案将把分区函数指定的每个分区映射到文件组。一张表最多可以有 1000 个分区

创建已分区表或已分区索引分为以下几个步骤：

1、创建分区函数以指定如何分区使用该函数的表或索引。  
分区函数：  
分区函数指定如何对表或索引进行分区。该函数将域映射到一组分区。若要创建分区函数，请指定分区数、分区依据列和每个分区的分区依据列值的范围。请注意，指定分区依据列时，仅可以指定一列。  
\--创建分区函数  
CREATE PARTITION FUNCTION partition\_function\_name ( input\_parameter\_type )---input\_parameter\_type 也就是数据类型(int,varchar.....)  
AS RANGE \[ LEFT | RIGHT \]  
FOR VALUES ( \[ boundary\_value \[ ,...n \] \] )  
\--删除分区函数  
DROP PARTITION FUNCTION partition\_function\_name  
\--修改分区函数  
ALTER PARTITION FUNCTION partition\_function\_name()  
\{  
    SPLIT RANGE ( boundary\_value )--在分区函数中添加一个分区。boundary\_value 确定新分区的范围，因此它必须不同于分区函数的现有边界范围。根据 boundary\_value，数据库引擎将某个现有范围拆分为两个范围。在这两个范围中，新 boundary\_value 所在的范围被视为是新分区  
  | MERGE RANGE ( boundary\_value ) --删除一个分区并将该分区中存在的所有值都合并到剩余的某个分区中。RANGE (boundary\_value) 必须是一个现有边界值，已删除分区中的值将合并到该值中。如果最初保存 boundary\_value 的文件组没有被剩余分区使用，也没有使用 NEXT USED 属性进行标记，则将从分区方案中删除该文件组。合并的分区驻留在最初不保存 boundary\_value 的文件组中。boundary\_value 是一个可以引用变量（包括用户定义类型变量）或函数（包括用户定义函数）的常量表达式。它无法引用 Transact-SQL 表达式。boundary\_value 必须匹配或可以隐式转换为其对应列的数据类型，并且当值的大小和小数位数不匹配其对应 input\_parameter\_type 时，将无法在隐式转换过程中被截断  
\}

2、创建分区方案以指定分区函数的分区在文件组上的位置。  
分区方案：  
分区方案将分区函数生成的分区映射到您定义的一组文件组。  
创建分区方案时，根据分区函数的参数，定义映射表分区的文件组。必须指定足够的文件组来容纳分区数。可以指定所有分区映射到不同文件组、某些分区映射到单个文件组或所有分区映射到单个文件组。如果您希望在以后添加更多分区，还可以指定其他“未分配的”文件组。在这种情况下，SQL Server 用 NEXT USED 属性标记其中一个文件组。这意味着该文件组将包含下一个添加的分区。  
一个分区方案仅可以使用一个分区函数。但是，一个分区函数可以参与多个分区方案。

\--创建分区方案  
CREATE PARTITION SCHEME partition\_scheme\_name  
AS PARTITION partition\_function\_name   
\[ ALL \] TO ( \{ file\_group\_name | \[ PRIMARY \] \} \[ ,...n \] )  
/\*  
如果 partition\_function\_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为 NEXT USED，并且出现显示命名 NEXT USED 文件组的信息。如果指定了 ALL，则单独的 file\_group\_name 将为该 partition\_function\_name 保持它的 NEXT USED 属性。如果在 ALTER PARTITION FUNCTION 语句中创建了一个分区，则 NEXT USED 文件组将再接收一个分区。  
\*/

\--删除分区方案  
DROP PARTITION SCHEME scheme\_name;

\--修改分区方案  
ALTER PARTITION SCHEME partition\_scheme\_name  
NEXT USED \[ filegroup\_name \]    ---向分区方案中添加文件组或更改分区方案中 NEXT USED 文件组的指定。  
/\*  
在一个分区方案中，只能将一个文件组指定为 NEXT USED。可以指定非空文件组。如果指定了 filegroup\_name 且当前没有文件组被标记为 NEXT USED，则将 filegroup\_name 标记为 NEXT USED。如果指定了 filegroup\_name 且已经存在具有 NEXT USED 属性的文件组，则 NEXT USED 属性从现有的文件组转移到 filegroup\_name。  
如果没有指定 filegroup\_name 且已经存在具有 NEXT USED 属性的文件组，则该文件组将失去其 NEXT USED 状态，因此 partition\_scheme\_name 中将没有 NEXT USED 文件组。  
如果指定了 filegroup\_name 且没有文件组被标记为 NEXT USED，则 ALTER PARTITION SCHEME 将返回一个警告  
\*/

3、创建使用分区方案的表或索引。

若要在创建表或索引时对其进行分区，可以在 CREATE TABLE 或 CREATE INDEX 语句中指定下列内容：  
表用于将分区映射到文件组的分区方案。  
对表进行分区的依据列（分区依据列）。分区依据列必须与分区方案在数据类型、长度和精度方面使用的分区函数中指定的列匹配。如果列已计算，则必须将该列指定为 PERSISTED

测试案例：

alter database grp add filegroup groupsec ---分别添加SECGRP、groupname、groupno、groupsec及文件  
go  
\-alter database grp add file  
(name=fileone,filename='f:\\db\\fileone.ndf',  
size=10MB, MAXSIZE=50MB,FILEGROWTH=10MB)--,  
(NAME=filethr,filename='e:\\db\\filethr.ndf',  
size=5MB,MAXSIZE=50MB,FILEGROWTH=5MB)  
TO FILEGROUP groupsec;  
create partition function test\_par\_fun(int)  
as range left  
for values (1,2,3,4,5,6,7,8,9,10,11,12)  
go

CREATE PARTITION SCHEME test\_par\_sch  
as partition test\_par\_fun  
to (SECGRP,SECGRP,SECGRP,SECGRP,groupname,groupname,groupname,groupname,groupno,groupno,groupno,groupno,groupsec)

create table tab\_partition\_a  
(id bigint identity(1,1) not null  ,year int default(year(getdate())),month int ,address nvarchar(64) default('北京市海淀区。。。。。 ')) on test\_par\_sch(month);

select \* from tab\_partition\_a  
insert into tab\_partition\_a select 2011,MONTH(getdate())-4,'北京市海淀区。。。。 '  
select month(GETDATE())-1

select \* from sys.partition\_functions  
select \* from sys.partition\_parameters  
select \* from sys.partition\_range\_values  
select \* from sys.partition\_schemes  
select \* from sys.partitions

select \* from tab\_partition\_a where $partition.test\_par\_fun(month)=10  
go  
select COUNT(1)as row\_count,$partition.test\_par\_fun(month) as partition  
from tab\_partition\_a  
group by $partition.test\_par\_fun(month)

select \* ,$partition.test\_par\_fun(MONTH) from tab\_partition\_a  
go

实用案例

\----创建文件组  
alter database PSYDBNEW  
add  
filegroup FileUnitNext;  
go  
alter database PSYDBNEW  
add  
filegroup FileUnit019;  
go  
alter database PSYDBNEW  
add  
filegroup FileUnit029;  
go  
alter database PSYDBNEW  
add  
filegroup FileUnit132;  
go  
alter database PSYDBNEW  
add  
filegroup FileUnit1000090;  
go  
alter database psydbnew  
add  
filegroup FileUnit2000009  
go  
alter database psydbnew  
add  
filegroup FileUnit1000094  
GO

\---向文件组中增加文件  
alter database psydbnew  
add file  
(  
 name=FileUnit019,  
 filename='D:/DataBaseFile/PartitionDataBase/Data/psydbnew\_Unit019.ndf',  
 size=5MB,  
 maxsize=500MB,  
 FileGrowth=5MB  
)  
To FileGroup FileUnit019  
GO  
alter database psydbnew  
add file  
(  
 name=FileUnit2000009,  
 filename='D:/DataBaseFile/PartitionDataBase/Data/psydbnew\_Unit2000009.ndf',  
 size=5MB,  
 maxsize=500MB,  
 FileGrowth=5MB  
)  
To FileGroup FileUnit2000009  
go  
alter database psydbnew  
add file  
(  
 name=FileUnit029,  
 filename='D:/DataBaseFile/PartitionDataBase/Data/psydbnew\_Unit029.ndf',  
 size=5MB,  
 maxsize=500MB,  
 FileGrowth=5MB  
)  
To FileGroup FileUnit029  
go  
alter database psydbnew  
add file  
(  
 name=FileUnit132,  
 filename='D:/DataBaseFile/PartitionDataBase/Data/psydbnew\_Unit132.ndf',  
 size=5MB,  
 maxsize=500MB,  
 FileGrowth=5MB  
)  
To FileGroup FileUnit132  
go  
alter database psydbnew  
add file  
(  
 name=FileUnit1000090,  
 filename='D:/DataBaseFile/PartitionDataBase/Data/psydbnew\_Unit1000090.ndf',  
 size=5MB,  
 maxsize=500MB,  
 FileGrowth=5MB  
)  
To FileGroup FileUnit1000090  
go  
alter database psydbnew  
add file  
(  
 name=FileUnit1000094,  
 filename='D:/DataBaseFile/PartitionDataBase/Data/psydbnew\_Unit1000094.ndf',  
 size=5MB,  
 maxsize=500MB,  
 FileGrowth=5MB  
)  
To FileGroup FileUnit1000094  
alter database psydbnew  
add file  
(  
 name=FileUnitNext,  
 filename='D:/DataBaseFile/PartitionDataBase/Data/psydbnew\_UnitNext.ndf',  
 size=5MB,  
 maxsize=500MB,  
 FileGrowth=5MB  
)  
To FileGroup FileUnitNext  
go  
\---创建分区函数  
create partition function PartFunction\_TradeLogs(varchar(7))  
 AS RANGE  LEFT FOR VALUES('19','1000090','132','29','2000009','1000094');  
 go  
 --创建分区框架（方案）  
 Create Partition Scheme PartSchema\_TradeLogs  
 AS Partition PartFunction\_TradeLogs  
 To  
 (FileUnit019,FileUnit1000090,FileUnit132,FileUnit029,FileUnit2000009,FileUnit1000094,FileUnitNext);  
go  
\---创建表  
 create  table TradeLogs  
(  
 TradeLogs\_ID numeric(18,0) identity(1,1) not null,  
 TradeLogs\_Customer\_UnitID varchar(7) not null ,  
 TradeLogs\_Customer\_UserID bigint not null,  
 TradeLogs\_ModulePath  varchar(256) not null,  
 TradeLogs\_OperationContent  varchar(max),  
 TradeLogs\_BeginDate   datetime,  
 TradeLogs\_EndDate   datetime,  
 TradeLogs\_PC\_IP    varchar(32),  
 CONSTRAINT \[PK\_TradeLogs\] PRIMARY KEY NONCLUSTERED  
  (  
   TradeLogs\_ID ASC  
  )on \[Primary\]

)  
on PartSchema\_TradeLogs (TradeLogs\_Customer\_UnitID)  
go  
\-- 创建聚集分区索引

create Unique clustered index index\_Clustered\_UnitID ON TradeLogs (TradeLogs\_ID,TradeLogs\_Customer\_UnitID)  
\--为分区表设置主键

alter table PartSchema\_TradeLogs ADD Constraint PK\_TradeLogs Primary  key (TradeLogs\_ID)

\-- 查看分区表每个分区的数据分布情况  
select TradeLogs.TradeLogs\_Customer\_UnitID, partition = $partition.PartFunction\_TradeLogs(TradeLogs\_Customer\_UnitID)  
      ,rows      = count(\*)  
  from TradeLogs  
 group by $partition.PartFunction\_TradeLogs(TradeLogs\_Customer\_UnitID),TradeLogs.TradeLogs\_Customer\_UnitID  
 order by partition

--insert TradeLogs select '19','011','000111222333555','','',Getdate(),GetDate()+1,'192.168.10.133'  
 -- insert TradeLogs select '132','021','000111222333555','','',Getdate(),GetDate()+1,'192.168.11.133'  
 --  insert TradeLogs select '29','031','000111222333555','','',Getdate(),GetDate()+1,'192.168.101.133'  
 --   insert TradeLogs select '1000090','041','000111222333555','','',Getdate(),GetDate()+1,'192.168.103.133'

\----查询文件组和文件语句

SELECT df.\[name\], df.physical\_name, df.\[size\], df.growth, f.\[name\]\[filegroup\], f.is\_default

FROM sys.database\_files df JOIN sys.filegroups f ON df.data\_space\_id = f.data\_space\_id