sql server 分区存储
2023-04-06 15:56 Evan.Pei 阅读(227) 评论(0) 编辑 收藏 举报为什么要数据库分区：
通过对大型表或索引进行分区，可以具有以下可管理性和性能优点。
- 可以快速、高效地传输或访问数据的子集，同时又能维护数据收集的完整性。
- 可以更快地对一个或多个分区执行维护或数据保留操作。
- 可以根据经常运行的查询类型提高查询性能。
- 数据库数据太大影响查询速度，可以将数据库进行横向按时间切割存储。
分区步骤：
1；建立分区几个分区建立几个
--第一步:创建四个文件组,你想分几个区 参数：数据库名,关键字,分组名 alter database phpDB add filegroup ByIdGroup1 alter database phpDB add filegroup ByIdGroup2
2；第二步：每个分区文件的次数据文件
ALTER DATABASE phpDB ADD FILE( NAME='File1', FILENAME='D:\DB_FenQu1\phpDBFile1.ndf',--路径 SIZE=5MB,--初始大小 FILEGROWTH=5MB)--每次增长值 TO FILEGROUP ByIdGroup1--指向分区名 --第二个次数据文件 ALTER DATABASE phpDB ADD FILE( NAME='File2', FILENAME='E:\DB_FenQu2\phpDBFile2.ndf', SIZE=5MB, FILEGROWTH=5MB) TO FILEGROUP ByIdGroup2
3；查询数据库文件信息
SELECT name,type_desc,physical_name,state_desc,size FROM sys.database_files
4；创建分区函数确定字段类型及分区的限制，留意【数据类型】
--例如2023-04-30（第一个分区放2023-04-30以前的数据）后面类推
--这里注意个数要比分组少一个，因为分组ByDateGroup2是分组条件以外的数据存放
--例如上面的分组是三个这里就只能写两个条件因为还有一个是放条件以外的数据 CREATE PARTITION FUNCTION pf_Dtime(DATETIME) AS RANGE LEFT FOR VALUES ('2023-04-30')
5；创建分区方案
--创建分区方案，这里写所有分组 CREATE PARTITION SCHEME ps_Dtime AS PARTITION pf_Dtime TO ([ByIdGroup1],[ByIdGroup2])
新表 我们已经创建了分区方案了，接下来就是把分区方案应用到数据表上，这就是创建分区表。 create table <表名> ( <列定义> )on<分区方案名>(分区列名)
6.创建分区表 已经存在的表
右击要分区的表-->存储-->创建分区-->选择分区列(这里UpByMemberID)-->选择分区函数
7；建立分区索引 temp 为表名，id为列名，dtime为列名
CREATE NONCLUSTERED INDEX [ixdtime] ON temp ( [dtime] ASC ) INCLUDE ([id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) GO
8；在插入数据数据会以数据为期限放入对应的分区中
9；查看每个分区的数据行数；
select $PARTITION.pf_Dtime([dtime]) as Patition,COUNT(*) countRow from dbo.temp group by $partition.pf_Dtime([dtime])
10.ndf 次要数据库文件的还原数据库 mdf主要文件，一个库只能一个，ndf一个库多个可选，ldf日志文件。
USE master; GO CREATE DATABASE cst ON (FILENAME = 'D:\Program Files\MS_SQLSERVER_DATA\cst.ndf'), (FILENAME = 'D:\Program Files\MS_SQLSERVER_DATA\cst_log.ldf') FOR ATTACH; GO
怎么增加分区
对于已经分好的区 需要在现有的分区方法中 加入新的分区策略 操作：
1. alter database phpDB add filegroup ByIdGroup6 --加文件组
2.加次要数据库文件 ALTER DATABASE phpDB ADD FILE( NAME='File6', FILENAME='G:\DB_FenQu6\phpDBFile6.ndf', SIZE=5MB, FILEGROWTH=5MB) TO FILEGROUP ByIdGroup6
3.修改分区方案加入新分区 ALTER PARTITION SCHEME ps_Dtime NEXT USED ByIdGroup6; GO
4.分区方法加入策略 alter partition function pf_Dtime() split range('2023-09-30')
--查询分区行数 select $PARTITION.pf_Dtime([dtime]) as Patition,COUNT(*) countRow from dbo.temp group by $partition.pf_Dtime([dtime]) --查询数据库文件信息 SELECT name,type_desc,physical_name,state_desc,size FROM sys.database_files
-----------------------------------------------------------------------动态创建分区如下：
---------------运维分区的实现 ---------------注意：数据库分组分区一旦建立就无法删除(只能转移数据后再删除表)，请留意使用。 ----------1 新建三个分区 ALTER database phpDB add filegroup ByDateGroup2023 ALTER database phpDB add filegroup ByDateGroup2024 ALTER database phpDB add filegroup ByDateGroup2025 ALTER database phpDB add filegroup ByDateGroupOther ----------2 新建数据文件 ALTER DATABASE phpDB ADD FILE( NAME='dbFile1', FILENAME='D:\DB_Subregion\YWDBFile1.ndf',--路径 SIZE=3MB,--初始大小 FILEGROWTH=1MB)--每次增长值 TO FILEGROUP ByDateGroup2023--指向分区名 ALTER DATABASE phpDB ADD FILE( NAME='dbFile2', FILENAME='D:\DB_Subregion\YWDBFile2.ndf',--路径 SIZE=3MB,--初始大小 FILEGROWTH=1MB)--每次增长值 TO FILEGROUP ByDateGroup2024--指向分区名 ALTER DATABASE phpDB ADD FILE( NAME='dbFile3', FILENAME='D:\DB_Subregion\YWDBFile3.ndf',--路径 SIZE=3MB,--初始大小 FILEGROWTH=1MB)--每次增长值 TO FILEGROUP ByDateGroup2025--指向分区名 ALTER DATABASE phpDB ADD FILE( NAME='dbFile4', FILENAME='D:\DB_Subregion\YWDBFileOther.ndf',--路径 SIZE=3MB,--初始大小 FILEGROWTH=1MB)--每次增长值 TO FILEGROUP ByDateGroupOther--指向分区名 -------3新建分组函数，这里注意个数要比分组少一个，因为分组ByDateGroupOther是分组条件以外的数据存放 CREATE PARTITION FUNCTION pf_Dtime(DATETIME) AS RANGE LEFT FOR VALUES ('2024-01-01','2025-01-01','2026-01-01') ------4这里注意个数要和分组相同个数 CREATE PARTITION SCHEME ps_Dtime AS PARTITION pf_Dtime TO ([ByDateGroup2023],[ByDateGroup2024],[ByDateGroup2025],[ByDateGroupOther]) ------5针对已经存在的表手动去右键存储中关联分组，确保关联的表为【DATETIME】类型。 ------6新建表的话，CREATE TABLE tName(列...)ON ps_Dtime(列) 进行关联。 ------7针对需要加多分区用下面的存储过程。 ---EXEC Proc_Partition '2023-08-31' --条件在本年的最后一天判断，写后年的第一天为条件 CREATE PROC [dbo].[Proc_Partition] @yearFirstDay NVARCHAR(20)--判断条件 AS BEGIN DECLARE @Database VARCHAR(100),--操作数据库 @FilePath VARCHAR(100),--文件存放路径 @FileName VARCHAR(100),--文件名称'dbfile'+日期 @CurrentDateTimeByDay VARCHAR(100),--当前时间,日 @FileGroupName VARCHAR(100),--文件组名称 @SchemeName VARCHAR(100),--分区方案名称 @FunName VARCHAR(100),--分区函数名称 @sql VARCHAR(400);-- 赋值文件属性 SELECT @Database=Name FROM Master..SysDataBases WHERE DbId=(SELECT Dbid FROM Master..SysProcesses WHERE Spid = @@spid) SET @CurrentDateTimeByDay =CONVERT(VARCHAR(10), cast(@yearFirstDay as datetime), 112);--20230411 SET @FileName = 'dbfile' + @CurrentDateTimeByDay;--dbfile20230411 SET @FilePath = 'D:\DB_Subregion\'; SET @SchemeName='ps_Dtime';--方案名称 SET @FunName='pf_Dtime()';--分区函数名称 --SET @yearFirstDay=DATENAME(mm,GETDATE())+DATENAME(dd,GETDATE()); -- 赋值文件组属性 SET @FileGroupName = 'filegroup' + @CurrentDateTimeByDay;--分组名 -- 创建文件组 SET @sql = 'alter database ' + @Database + ' add filegroup ' + @FileGroupName + ''; EXEC (@sql); -- 创建文件，并绑定文件组 SET @sql = ' alter database ' + @Database + ' add file (name=''' + @FileName + ''',' + ' filename=''' + @FilePath+@FileName + '.ndf'',' + 'size =3MB,' + 'filegrowth = 1MB )' + ' to filegroup ' + @FileGroupName; EXEC (@sql); -- 修改分区方案 SET @sql = 'alter partition scheme ' + @SchemeName + ' next used ' + @FileGroupName + ''; EXEC (@sql); -- 修改分区函数 SET @sql = 'alter partition function ' + @FunName + ' split range (N''' +@yearFirstDay+''')' EXEC (@sql); PRINT @yearFirstDay END GO ------------------------------- 查看信息 --查询分区范围 select * from sys.partition_range_values --查询分区行数 select $PARTITION.pf_Dtime([dtime]) as Patition,COUNT(*) countRow from dbo.Table1 group by $partition.pf_Dtime([dtime]) --查询数据库文件信息 SELECT name,type_desc,physical_name,state_desc,size FROM sys.database_files
