针对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 = NMCFile,--文件名
 22 FileName = NE:\公司文件\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=NMCFile
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

 

针对sqlserver 使用水平分表优化

上一篇:Microsoft SQL Server 双机热备份,实时同步


下一篇:JDBC连接数据库和改进