首页 > 代码库 > GroupData群数据库的还原与优化
GroupData群数据库的还原与优化
一、背景
这个数据库的数据文件mdf大概有83G左右，当还原数据库之后感觉可以做很多性能方面上的调优，合并数据后mdf数据文件大概有59G左右，行压缩后mdf数据文件大概有39G左右，页压缩后mdf数据文件大概有34G左右，这里处于技术研究的目的，讲讲研究的成果分析，不用于商业目的；
二、优化项
我们可以从下面4个不同的方面来优化这两个数据库：
（一）对表进行分区；
（二）创建合适表索引；
（三）使用行压缩，压缩行数据；
（四）重新设计表结构，优化表空间；
三、附加数据库
1．先把11个GroupData（群与成员的关系）数据库附加到数据库，下面的导入SQL语句在原来的基础上做了些修改：统一数据库名，这样做的好处就是后面做处理的时候方便按照顺序执行数据库；
--附加数据库EXEC sp_attach_db "GroupData01", "D:\DBBackup\QunData\GroupData1_Data.MDF"EXEC sp_attach_db "GroupData02", "D:\DBBackup\QunData\GroupData2_Data.MDF"EXEC sp_attach_db "GroupData03", "D:\DBBackup\QunData\GroupData3_Data.MDF"EXEC sp_attach_db "GroupData04", "D:\DBBackup\QunData\GroupData4_Data.MDF"EXEC sp_attach_db "GroupData05", "D:\DBBackup\QunData\GroupData5_Data.MDF"EXEC sp_attach_db "GroupData06", "D:\DBBackup\QunData\GroupData6_Data.MDF"EXEC sp_attach_db "GroupData07", "D:\DBBackup\QunData\GroupData7_Data.MDF"EXEC sp_attach_db "GroupData08", "D:\DBBackup\QunData\GroupData8_Data.MDF"EXEC sp_attach_db "GroupData09", "D:\DBBackup\QunData\GroupData9_Data.MDF"EXEC sp_attach_db "GroupData10", "D:\DBBackup\QunData\GroupData10_Data.MDF"EXEC sp_attach_db "GroupData11", "D:\DBBackup\QunData\GroupData11_Data.MDF"
四、合并数据库
2．修改各个数据库中表的名字：把Group1统一修改为Group01这样格式的，这样做的好处就是在合并数据的时候读取到的数据库的数据是按照顺序插入到表中的，不会造成数据页的拆分；
--格式化表名USE GroupData01GOexec sp_rename ‘Group1‘,‘Group01‘exec sp_rename ‘Group2‘,‘Group02‘exec sp_rename ‘Group3‘,‘Group03‘exec sp_rename ‘Group4‘,‘Group04‘exec sp_rename ‘Group5‘,‘Group05‘exec sp_rename ‘Group6‘,‘Group06‘exec sp_rename ‘Group7‘,‘Group07‘exec sp_rename ‘Group8‘,‘Group08‘exec sp_rename ‘Group9‘,‘Group09‘
3．创建一个名为GroupData的数据库，设置数据库为简单恢复模式；
4．在GroupData数据库中创建一个临时表：tables，用来保存所有的数据库与表的信息，提供数据库合并用；
--创建临时表CREATE TABLE [GroupData].[dbo].[tables]( [db_name] [sysname] NULL, [table_name] [sysname] NULL, [status] [bit] default 0) ON [PRIMARY]select db_name,table_name,status from [GroupData].[dbo].[tables]--生成数据库名称与表名称的对应列表EXEC sp_MSForEachDB ‘USE [?]; --插入表信息 INSERT INTO [GroupData].[dbo].[tables]([table_name]) SELECT name from [?].sys.tables where name like ‘‘Group%‘‘ order by name --更新数据库名称 UPDATE [GroupData].[dbo].[tables] SET [db_name] = ‘‘?‘‘ WHERE [db_name] IS NULL‘
五、优化数据库
5．经过评估，11个GroupData数据库的Group表数据的总和大概有15亿，Group表中QunNum（群号）字段的最大值为100219998（可以通过QunInfo11数据库的QunList110表查询到：SELECT MAX(QunNum) FROM [QunInfo11].[dbo].[QunList110]），从业务的角度，可能需要查询某群的信息，所以这里就以QunNum作为分区，每5百万个群作为一个分区，这样计算那就需要21个文件组，假设群成员都比较平均的话，那每个文件组里面就保存了大概7千万左右的群成员关系；
6．下面是一个创建分区脚本的SQL脚本，执行下面的SQL会生成一个新的脚本，执行那个脚本就可以创建21个文件组、分区函数和分区方案；
--生成分区脚本DECLARE @DataBaseName NVARCHAR(50)--数据库名称DECLARE @TableName NVARCHAR(50)--表名称DECLARE @ColumnName NVARCHAR(50)--字段名称DECLARE @PartNumber INT--分区最大编号DECLARE @PartNumberBegin INT--分区编号开始值DECLARE @PartNumberBeginTemp INT--分区编号开始值临时值DECLARE @PartNumberStr NVARCHAR(50)--分区值字符串DECLARE @Location NVARCHAR(50)--保存分区文件的路径DECLARE @Size NVARCHAR(50)--分区初始化大小DECLARE @FileGrowth NVARCHAR(50)--分区文件增量DECLARE @FunValue INT--分区分段值增量DECLARE @FunValueBegin INT--分区分段值开始值DECLARE @i INT--临时变量DECLARE @sql NVARCHAR(max)--设置下面变量SET @DataBaseName = ‘GroupData‘SET @TableName = ‘Group‘SET @ColumnName = ‘QunNum‘SET @PartNumber = 21SET @PartNumberBegin = 1SET @Location = ‘D:\DBBackup\FG_Group\‘SET @Size = ‘4096MB‘SET @FileGrowth = ‘1024MB‘SET @FunValueBegin = 5000000SET @FunValue = 5000000SET @sql = ‘USE [‘+@DataBaseName +‘]GO‘PRINT @sql + CHAR(13)--1.创建文件组SET @i = 1SET @PartNumberBeginTemp = @PartNumberBeginPRINT ‘--1.创建文件组‘WHILE @i <= @PartNumberBEGIN SET @PartNumberStr = RIGHT(‘0‘ + CONVERT(NVARCHAR,@PartNumberBeginTemp),2) SET @sql = ‘ALTER DATABASE [‘+@DataBaseName +‘]ADD FILEGROUP [FG_‘+@TableName+‘_‘+@ColumnName+‘_‘+@PartNumberStr+‘]‘ PRINT @sql + CHAR(13) SET @i=@i+1 SET @PartNumberBeginTemp = @PartNumberBeginTemp+1END--2.创建文件SET @i = 1SET @PartNumberBeginTemp = @PartNumberBeginPRINT CHAR(13)+‘--2.创建文件‘WHILE @i <= @PartNumberBEGIN SET @PartNumberStr = RIGHT(‘0‘ + CONVERT(NVARCHAR,@PartNumberBeginTemp),2) SET @sql = ‘ALTER DATABASE [‘+@DataBaseName +‘]ADD FILE(NAME = N‘‘FG_‘+@TableName+‘_‘+@ColumnName+‘_‘+@PartNumberStr+‘_data‘‘,FILENAME = N‘‘‘+@Location+‘FG_‘+@TableName+‘_‘+@ColumnName+‘_‘+@PartNumberStr+‘_data.ndf‘‘,SIZE = ‘+@Size+‘, FILEGROWTH = ‘+@FileGrowth+‘ )TO FILEGROUP [FG_‘+@TableName+‘_‘+@ColumnName+‘_‘+@PartNumberStr+‘];‘ PRINT @sql + CHAR(13) SET @i=@i+1 SET @PartNumberBeginTemp = @PartNumberBeginTemp+1END--3.创建分区函数PRINT CHAR(13)+‘--3.创建分区函数‘DECLARE @FunValueStr NVARCHAR(MAX)DECLARE @PNB INTSET @i = 1SET @PNB = 1SET @FunValueStr = convert(NVARCHAR(50),@FunValueBegin) + ‘,‘WHILE @i < @PartNumber-1BEGIN SET @FunValueStr = @FunValueStr + convert(NVARCHAR(50),(@FunValueBegin+@PNB*@FunValue)) + ‘,‘ SET @i=@i+1 SET @PNB=@PNB+1ENDSET @FunValueStr = substring(@FunValueStr,1,len(@FunValueStr)-1)SET @sql = ‘CREATE PARTITION FUNCTION[Fun_‘+@TableName+‘_‘+@ColumnName+‘](INT) ASRANGE RIGHTFOR VALUES(‘+@FunValueStr+‘)‘PRINT @sql + CHAR(13)--4.创建分区方案PRINT CHAR(13)+‘--4.创建分区方案‘DECLARE @FileGroupStr NVARCHAR(MAX) SET @i = 1SET @PartNumberBeginTemp = @PartNumberBeginSET @FileGroupStr = ‘‘WHILE @i <= @PartNumberBEGIN SET @PartNumberStr = RIGHT(‘0‘ + CONVERT(NVARCHAR,@PartNumberBeginTemp),2) SET @FileGroupStr = @FileGroupStr + ‘[FG_‘+@TableName+‘_‘+@ColumnName+‘_‘+@PartNumberStr+‘],‘ SET @i=@i+1 SET @PartNumberBeginTemp = @PartNumberBeginTemp+1ENDSET @FileGroupStr = substring(@FileGroupStr,1,len(@FileGroupStr)-1)SET @sql = ‘CREATE PARTITION SCHEME[Sch_‘+@TableName+‘_‘+@ColumnName+‘] ASPARTITION [Fun_‘+@TableName+‘_‘+@ColumnName+‘]TO(‘+@FileGroupStr+‘)‘PRINT @sql + CHAR(13)--5.分区函数的记录数PRINT CHAR(13)+‘--5.分区函数的记录数‘SET @sql = ‘SELECT $PARTITION.[Fun_‘+@TableName+‘_‘+@ColumnName+‘](‘+@ColumnName+‘) AS Partition_num, MIN(‘+@ColumnName+‘) AS Min_value,MAX(‘+@ColumnName+‘) AS Max_value,COUNT(1) AS Record_numFROM dbo.[‘+@TableName+‘]GROUP BY $PARTITION.[Fun_‘+@TableName+‘_‘+@ColumnName+‘](‘+@ColumnName+‘)ORDER BY $PARTITION.[Fun_‘+@TableName+‘_‘+@ColumnName+‘](‘+@ColumnName+‘);‘PRINT @sql + CHAR(13)
7．下面重新对Group表进行设计，涉及的内容如下：
1) 在GroupData数据库中创建分区表Group，这里已经把原表的ID字段去掉了，这个字段并没有太大的意义；
2) 以[QunNum]和[QQNum]作为聚集索引，而且是唯一的，这个需要开启IGNORE_DUP_KEY = ON选项，这样才可以在批量插入的时候忽略重复值；
3) 对原表[Age]、[Gender]、[Auth]3个字段的数据类型进行了修改，减少占用的空间，
4) 使用刚刚创建好的分区方案，之后创建的索引进行索引对齐；
5) 对表使用行压缩，减少数据库占用空间；
6) 对表进行页压缩会更节省空间？
--创建优化后的Group表CREATE TABLE [dbo].[Group]( [QunNum] [int] NOT NULL, [QQNum] [int] NOT NULL, [Nick] [varchar](20) NULL, [Age] [tinyint] NULL, [Gender] [tinyint] NULL, [Auth] [tinyint] NULL, CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED ( [QunNum] ASC, [QQNum] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW) ON [Sch_Group_QunNum]([QunNum])) ON [Sch_Group_QunNum]([QunNum])GO
（Figure：GroupData原表结构）
（Figure：GroupData新表结构）
8．把11个数据库都合并到新创建的GroupData的Group表中；
--合并数据DECLARE @tablename sysnameDECLARE @dbname sysnameDECLARE @sql NVARCHAR(max)--游标DECLARE @itemCur CURSORSET @itemCur = CURSOR FOR SELECT db_name,table_name from [GroupData].[dbo].[tables]OPEN @itemCurFETCH NEXT FROM @itemCur INTO @dbname,@tablenameWHILE @@FETCH_STATUS=0BEGIN SET @sql = ‘INSERT INTO [GroupData].[dbo].[Group] ([QunNum] ,[QQNum] ,[Nick] ,[Age] ,[Gender] ,[Auth]) SELECT [QunNum] ,[QQNum] ,[Nick] ,[Age] ,[Gender] ,[Auth] FROM [‘+@dbname+‘].[dbo].[‘+@tablename+‘]‘ EXEC(@sql) UPDATE [GroupData].[dbo].[tables] SET status = 1 WHERE db_name = @dbname AND table_name = @tablename --返回SQL PRINT(@sql)PRINT(‘GO‘)+CHAR(13) FETCH NEXT FROM @itemCur INTO @dbname,@tablenameEND CLOSE @itemCurDEALLOCATE @itemCur
9．为Group表的QQNum字段创建一个索引，这个索引在进行表联接的时候会用到；
--索引行压缩CREATE NONCLUSTERED INDEX [IX_Group_QQNum] ON [dbo].[Group] ( [QQNum] ASC)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, DATA_COMPRESSION = ROW) ON [Sch_Group_QunNum]([QunNum])GO
（Figure：GroupData表分区记录数）
（Figure：GroupData数据行压缩前）
（Figure：GroupData数据行压缩后）
（Figure：GroupData数据页压缩后）
（Figure：GroupData索引行压缩前）
（Figure：GroupData索引行压缩后）
怎么行压缩后索引的占用空间比压缩前的还要大呢？
--索引页压缩CREATE NONCLUSTERED INDEX [IX_Group_QQNum] ON [dbo].[Group] ( [QQNum] ASC)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, DATA_COMPRESSION = PAGE) ON [Sch_Group_QunNum]([QunNum])GO
（Figure：GroupData索引页压缩后）
怎么页压缩后索引的占用空间比压缩前的还要大呢？
GroupData群数据库的还原与优化
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。