针对sqlserver 使用水平分表优化
1 USE master --备份 2 BACKUP DATABASE NGK_Prod TO DISK ='NGK_Prod.bak' WITH FORMAT 3 --恢复 4 RESTORE DATABASE NGK_Prod FROM DISK = 'NGK_Prod.bak' WITH replace 5 GO 6 7 --********************文件组和文件操作********************* 8 9 --添加文件组 10 USE NGK_Prod; 11 GO 12 ALTER DATABASE NGK_Prod ADD filegroup [MCFileGroup] 13 --ALTER DATABASE NGK_Prod ADD filegroup [MCFileGroup] 14 Go 15 16 --添加文件并把其指向指定文件组 17 USE NGK_Prod; 18 Go 19 alter DATABASE NGK_Prod 20 add file( 21 name = N'MCFile',--文件名 22 FileName = N'E:\公司文件\DPS\DB\MCFile.mdf',--存放路径 23 SIZE = 3MB, 24 maxsize = 100MB, 25 filegrowth = 5MB 26 ) to filegroup MCFileGroup 27 Go 28 29 --修改文件 30 -- use master; 31 -- Go 32 -- alter DATABASE NGK_Prod 33 -- modify file ( name=N'newMCFile',size = 20MB); --可以修改所有属性，列举即可 34 -- Go 35 36 --删除文件 37 --alter DATABASE NGK_Prod remove file newMCFile 38 39 --***********************分区函数和分区方案********************************** 40 41 --分区函数 42 use NGK_Prod 43 Go 44 create partition function partion_function_MC(datetime) --分区函数名 (指标列的数据类型) 如：datetime、int 45 as range right --右边界切分，默认为left 46 for VALUES ('2020-01-01 00:00:00','2021-01-01 00:00:00') --划分边界。如时间划分('2003/01/01', '2004/01/01')，两个时间界限可划分出三个分区 47 Go 48 --查看分区函数是否创建成功 49 use NGK_Prod 50 select * from sys.partition_functions 51 --drop partition FUNCTION partion_function_MC 52 --分区方案 53 use NGK_Prod 54 Go 55 create PARTITION SCHEME partition_scheme_MC 56 as PARTITION partion_function_MC 57 to ([MCFileGroup],[PRIMARY],MCFileGroup) --文件组 注意分区数要与实际分区一致 58 Go 59 --查看分区方案是否创建完成 60 use NGK_Prod 61 select * from sys.partition_schemes 62 63 --在原有的基础上添加分区（可选） 64 -- use 数据库名 65 -- go 66 -- alter partition scheme ps_OrderDate next used [FG4] //修改分区方案ps_OrderDate，定义新新分区使用FG4文件组 67 -- alter partition function pf_OrderDate() split range('2005/01/01') //修改分区函数pf_OrderDate，在末尾添加界限'2005/01/01' 68 -- go 69 --为现有表设置分区方案（可选） 70 -- //为AutoBench表的InsertTime列创建新聚集索引，并绑定Scheme_DateTime分区方案 71 -- CREATE CLUSTERED INDEX IX_CreateDate ON AutoBench (InsertTime) 72 -- -- ON Scheme_DateTime (InsertTime) 73 --注：如原来主键有聚众索引要将其改为非聚集索引，才可添加新聚众索引 74 -- //删除原主键上的聚集索引PK_Product 75 -- ALTER TABLE Product DROP CONSTRAINT PK_Product 76 -- //重新创建主键非聚集索引PK_Product 77 -- ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductID ASC) 78 79 80 --定义分区表 （为新建表设置分区方案） 81 create TABLE mctable( 82 id bigint, 83 createTime datetime, 84 creator nvarchar(50) 85 ) on partition_scheme_MC(createTime) 86 87 88 89 -- 90 -- select * from NGK_Prod.sys.filegroups 91 -- select * from NGK_Prod.sys.database_files 92 -- select * from sys.partition_range_values 93 -- select function_id from sys.partition_functions 94 -- select * from sys.partition_schemes 95 -- SELECT CONVERT(datetime,CONVERT(varchar(10),GETDATE(),101) ,101) 96 97 98 99 100 INSERT INTO mctable VALUES(1, getdate(),'1') 101 INSERT INTO mctable VALUES(2, '2021-01-02 00:00:00','1') 102 SELECT * FROM mctable 103 --查看每个分区数据条数 104 select $partition.partion_function_MC(CreateTime) as partitionNum,count(*) as recordCount 105 from mctable 106 group by $partition.partion_function_MC(CreateTime) 107 108 --查看每个分区详细数据 109 select * from mctable where $partition.partion_function_MC(CreateTime)=3 110 select * from mctable where $partition.partion_function_MC(CreateTime)=4
1 DECLARE 2 @fileGroupName VARCHAR(20), --文件组名（格式为:FG+@Month） 3 @fileName VARCHAR(20), --文件名（格式为:F+@Month） 4 @filePath VARCHAR(100), --文件存放路径（格式为:存放目录路径+@fileName.ndf） 5 @dataBaseName VARCHAR(20), --数据库名 6 @Month VARCHAR(10), --当前时间年月（格式为:yyyymm） 7 @schemeName VARCHAR(20), --分区方案名 8 @partFunctionName VARCHAR(20), --分区函数名 9 @limit VARCHAR(10) --分区界限（以时间分区则为时间字符串，格式为:mm/dd/yyyy) 10 11 SET @fileGroupName='FG201805' 12 SET @Month=CONVERT(varchar(10),GETDATE(),112) 13 SET @fileName=N'MCFile' 14 SET @filePath='E:\公司文件\DPS\DB\MCFile.mdf' 15 SET @dataBaseName='NGK_Prod' 16 SET @schemeName='partition_scheme_MC' 17 SET @partFunctionName='partion_function_MC' 18 SET @limit=CONVERT(varchar(10),GETDATE(),101) 19 20 --语句要指明需要操作的数据库 21 if exists(select * from NGK_Prod.sys.filegroups where name=@fileGroupName) 22 begin 23 print '文件组存在,不需添加' 24 end 25 else 26 begin 27 exec('ALTER DATABASE '+@dataBaseName+' ADD FILEGROUP ['+@fileGroupName+']') 28 print '新增文件组'+@fileGroupName 29 end 30 31 if exists(select * from NGK_Prod.sys.database_files where [state]=0 and (name=@fileName or physical_name=@filePath)) 32 begin 33 print 'ndf文件存在,不需添加' 34 end 35 else 36 begin 37 exec('ALTER DATABASE '+@dataBaseName+' ADD FILE(NAME ='''+@fileName+''',FILENAME = '''+@filePath+''')TO FILEGROUP ['+@fileGroupName+']') 38 print '添加文件'+@fileName+'至文件组'+@fileGroupName 39 end 40 41 if exists(select * from sys.partition_schemes where name=@schemeName) 42 begin 43 exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']') 44 print '修改分区方案，指定下一分区的文件组' 45 end 46 else 47 begin 48 print '分区方案不存在' 49 end 50 51 if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_functions where name=@partFunctionName)) 52 begin 53 if exists(select * from sys.partition_range_values where function_id=(select function_id from sys.partition_schemes where name=@schemeName) and value=CONVERT(datetime,''+@limit+'',101)) 54 begin 55 print '界限已存在' 56 end 57 else 58 begin 59 exec('alter partition function '+@partFunctionName+'() split range('''+@limit+''')') 60 print '修改分区函数，添加划分界限为：'+@limit 61 end 62 end 63 else 64 begin 65 print '分区函数不存在' 66 end
针对多文件分区
--********************文件组和文件操作********************* --添加文件组 USE NGK_DPS_SCAN; GO ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2020_3quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2020_4quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2021_1quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2021_2quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2021_3quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2021_4quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2022_1quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2022_2quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2022_3quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2022_4quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2023_1quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2023_2quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2023_3quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2023_4quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2024_1quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2024_2quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2024_3quarter] ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup_2024_4quarter] Go --ALTER DATABASE NGK_DPS_SCAN ADD filegroup [MCFileGroup] --添加文件并把其指向指定文件组 USE NGK_DPS_SCAN; Go alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2020_3quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2020_3quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2020_3quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2020_4quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2020_4quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2020_4quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2021_1quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2021_1quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2021_1quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2021_2quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2021_2quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2021_2quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2021_3quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2021_3quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2021_3quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2021_4quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2021_4quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2021_4quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2022_1quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2022_1quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2022_1quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2022_2quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2022_2quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2022_2quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2022_3quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2022_3quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2022_3quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2022_4quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2022_4quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2022_4quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2023_1quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2023_1quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2023_1quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2023_2quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2023_2quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2023_2quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2023_3quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2023_3quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2023_3quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2023_4quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2023_4quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2023_4quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2024_1quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2024_1quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2024_1quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2024_2quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2024_2quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2024_2quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2024_3quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2024_3quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2024_3quarter alter DATABASE NGK_DPS_SCAN add file( name = N'MCFile_2024_4quarter', FileName = N'D:\WebDb\DPS_SCAN\MCFile_2024_4quarter.mdf',SIZE = 3MB,maxsize = 100MB,filegrowth = 5MB) to filegroup MCFileGroup_2024_4quarter Go --修改文件 -- use master; -- Go -- alter DATABASE NGK_DPS_SCAN -- modify file ( name=N'newMCFile',size = 20MB); --可以修改所有属性，列举即可 -- Go --删除文件 --alter DATABASE NGK_DPS_SCAN remove file newMCFile --***********************分区函数和分区方案********************************** --分区函数 use NGK_DPS_SCAN Go create partition function partion_function_MC(datetime) --分区函数名 (指标列的数据类型) 如：datetime、int as range right --右边界切分，默认为left for VALUES ('2020-07-01 00:00:00','2020-10-01 00:00:00','2021-01-01 00:00:00','2021-04-01 00:00:00','2021-07-01 00:00:00','2021-10-01 00:00:00','2022-01-01 00:00:00','2022-04-01 00:00:00','2022-07-01 00:00:00','2022-10-01 00:00:00','2023-01-01 00:00:00','2023-04-01 00:00:00','2023-07-01 00:00:00','2023-10-01 00:00:00','2024-01-01 00:00:00','2024-04-01 00:00:00','2024-07-01 00:00:00','2024-10-01 00:00:00') --划分边界。如时间划分('2003/01/01', '2004/01/01')，两个时间界限可划分出三个分区 Go --查看分区函数是否创建成功 use NGK_DPS_SCAN select * from sys.partition_functions --drop partition FUNCTION partion_function_MC --分区方案 use NGK_DPS_SCAN Go create PARTITION SCHEME partition_scheme_MC as PARTITION partion_function_MC to ([PRIMARY],MCFileGroup_2020_3quarter,MCFileGroup_2020_4quarter,MCFileGroup_2021_1quarter,MCFileGroup_2021_2quarter,MCFileGroup_2021_3quarter,MCFileGroup_2021_4quarter,MCFileGroup_2022_1quarter,MCFileGroup_2022_2quarter,MCFileGroup_2022_3quarter,MCFileGroup_2022_4quarter,MCFileGroup_2023_1quarter,MCFileGroup_2023_2quarter,MCFileGroup_2023_3quarter,MCFileGroup_2023_4quarter,MCFileGroup_2024_1quarter,MCFileGroup_2024_2quarter,MCFileGroup_2024_3quarter,MCFileGroup_2024_4quarter) --文件组 注意分区数要与实际分区一致 Go --查看分区方案是否创建完成 use NGK_DPS_SCAN select * from sys.partition_schemes --在原有的基础上添加分区（可选） -- use 数据库名 -- go -- alter partition scheme ps_OrderDate next used [FG4] //修改分区方案ps_OrderDate，定义新新分区使用FG4文件组 -- alter partition function pf_OrderDate() split range('2005/01/01') //修改分区函数pf_OrderDate，在末尾添加界限'2005/01/01' -- go --为现有表设置分区方案（可选） -- //为AutoBench表的InsertTime列创建新聚集索引，并绑定Scheme_DateTime分区方案 -- CREATE CLUSTERED INDEX IX_CreateDate ON AutoBench (InsertTime) -- -- ON Scheme_DateTime (InsertTime) --注：如原来主键有聚众索引要将其改为非聚集索引，才可添加新聚众索引 -- //删除原主键上的聚集索引PK_Product -- ALTER TABLE Product DROP CONSTRAINT PK_Product -- //重新创建主键非聚集索引PK_Product -- ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductID ASC) --定义分区表 （为新建表设置分区方案） CREATE TABLE [dbo].[ScanRecord] ( [ID] int IDENTITY(1,1) NOT NULL, [ApsUsermobile] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [ScanTime] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [BarCode] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [InventoryCode] nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, [CreateTime] datetime not NULL, ShopCode nvarchar(255) COLLATE Chinese_PRC_CI_AS NULL, IsSync int null ) on partition_scheme_MC(createTime) -- create TABLE mctable( -- id bigint, -- createTime datetime, -- creator nvarchar(50) -- ) on partition_scheme_MC(createTime) -- -- select * from NGK_DPS_SCAN.sys.filegroups -- select * from NGK_DPS_SCAN.sys.database_files -- select * from sys.partition_range_values -- select function_id from sys.partition_functions -- select * from sys.partition_schemes -- SELECT CONVERT(datetime,CONVERT(varchar(10),GETDATE(),101) ,101) SELECT * FROM scanrecord INSERT INTO ScanRecord VALUES('','','','', getdate(),'1',1) INSERT INTO ScanRecord VALUES('','','','', '2021-01-02 00:00:00','1',1) SELECT * FROM ScanRecord --查看每个分区数据条数 select $partition.partion_function_MC(CreateTime) as partitionNum,count(*) as recordCount from ScanRecord group by $partition.partion_function_MC(CreateTime) --查看每个分区详细数据 select * from ScanRecord where $partition.partion_function_MC(CreateTime)=2 select * from ScanRecord where $partition.partion_function_MC(CreateTime)=4 select $partition.partion_function_MC(GETDATE())
