3595 次阅读< 笔记 2021-06-22
QQ关系库 MDF文件如何重新建立LMF
（146）

数据库为网上下载的,想研究学习使用。之前建立的网站被关闭了，数据库小白一枚,想在本地搭建一个,搜了无数教程，前前后后经历了一个半月，终得其法。
接下来教程需要极强的动手能力，个人不建议小白进行操作，网上现在还是有现成的裤子的（当我没说）。
1.jpg

一、下载这个版本的SQL SERVER：

文件名 
    cn_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522724.iso 
SHA1 
    AAE0E2D4E41AB7591634D53C7BC76A112F31B617 
文件大小 
    4.34GB 
发布时间 
    2010-05-03 
 
ed2k://|file|cn_sql_server_2008_r2_developer_x86_x64_ia64_dvd_522724.iso|4662884352|E436F05BCB0165FDF7E5E61862AB6BE1|/

二、下载的是群号数据库QunInfo（11个）和群成员数据库GroupData（11个）。

1、首先，解压缩，得到两个库总共22个mdf文件(各11个)。打开Microsoft SQL Server Management Studio，菜单栏右上角找到“新建查询按钮”，打开一个空白页面，那是跑脚本的窗口。由于没有log文件，所以采用以下语句附加进数据库（注意QunInfo01~QunInfo11的命名不要改）：

--附加数据库(MDF文件路径可根据需要修改) 
sp_attach_single_file_db @dbname= 'GroupData01',@physname= 'D:\Backup\Database\QQ\GroupData1.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData02',@physname= 'D:\Backup\Database\QQ\GroupData2.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData03',@physname= 'D:\Backup\Database\QQ\GroupData3.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData04',@physname= 'D:\Backup\Database\QQ\GroupData4.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData05',@physname= 'D:\Backup\Database\QQ\GroupData5.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData06',@physname= 'D:\Backup\Database\QQ\GroupData6.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData07',@physname= 'D:\Backup\Database\QQ\GroupData7.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData08',@physname= 'D:\Backup\Database\QQ\GroupData8.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData09',@physname= 'D:\Backup\Database\QQ\GroupData9.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData10',@physname= 'D:\Backup\Database\QQ\GroupData10.MDF' 
go 
sp_attach_single_file_db @dbname= 'GroupData11',@physname= 'D:\Backup\Database\QQ\GroupData11.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo01',@physname= 'D:\Backup\Database\QQ\QunInfo1.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo02',@physname= 'D:\Backup\Database\QQ\QunInfo2.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo03',@physname= 'D:\Backup\Database\QQ\QunInfo3.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo04',@physname= 'D:\Backup\Database\QQ\QunInfo4.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo05',@physname= 'D:\Backup\Database\QQ\QunInfo5.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo06',@physname= 'D:\Backup\Database\QQ\QunInfo6.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo07',@physname= 'D:\Backup\Database\QQ\QunInfo7.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo08',@physname= 'D:\Backup\Database\QQ\QunInfo8.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo09',@physname= 'D:\Backup\Database\QQ\QunInfo9.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo10',@physname= 'D:\Backup\Database\QQ\QunInfo10.MDF' 
go 
sp_attach_single_file_db @dbname= 'QunInfo11',@physname= 'D:\Backup\Database\QQ\QunInfo11.MDF' 
go

将上述脚本拷贝到空白窗口，修改MDF文件路径后点击工具栏带红色感叹号的“执行”按钮。每一条语句，就是附加一个MDF文件。这样，就得到了22个数据库，QunInfo01~11包含成千个表，这样很不方便查询。虽然可以查询使用，但耗时漫长，效率很低，占用磁盘空间也大，所以我们要优化一下数据库。目的是：提高查询速度跟效率；减少磁盘空间占用。

2、把QunInfo01~11、GroupData01~11分别合并到两个库QunInfo、GroupData，由于数据量太大，用分区表提升性能。方案如下：重新设计表结构，优化表空间设计分区方案合并数据库：使用行压缩，压缩行数据建立索引，优化查询速度最终效果，查询可以秒出结果。

3、创建新的库用于合并

创建一个名为QunInfo的数据库，设置数据库为简单恢复模式。

mdf、ldf文件保存路径可根据需要更改，脚本如下

USE [master] 
GO 
CREATE DATABASE [QunInfo] ON  PRIMARY  
( NAME = N'QunInfo', FILENAME = N'D:\Backup\Database\QQ\QunInfo.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
 LOG ON  
( NAME = N'QunInfo_log', FILENAME = N'D:\Backup\Database\QQ\QunInfo_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) 
GO 
 
ALTER DATABASE [QunInfo] SET COMPATIBILITY_LEVEL = 100 
GO 
 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
begin 
EXEC [QunInfo].[dbo].[sp_fulltext_database] @action = 'enable' 
end 
GO 
 
ALTER DATABASE [QunInfo] SET ANSI_NULL_DEFAULT OFF  
GO 
 
ALTER DATABASE [QunInfo] SET ANSI_NULLS OFF  
GO 
 
ALTER DATABASE [QunInfo] SET ANSI_PADDING OFF  
GO 
 
ALTER DATABASE [QunInfo] SET ANSI_WARNINGS OFF  
GO 
 
ALTER DATABASE [QunInfo] SET ARITHABORT OFF  
GO 
 
ALTER DATABASE [QunInfo] SET AUTO_CLOSE OFF  
GO 
 
ALTER DATABASE [QunInfo] SET AUTO_CREATE_STATISTICS ON  
GO 
 
ALTER DATABASE [QunInfo] SET AUTO_SHRINK OFF  
GO 
 
ALTER DATABASE [QunInfo] SET AUTO_UPDATE_STATISTICS ON  
GO 
 
ALTER DATABASE [QunInfo] SET CURSOR_CLOSE_ON_COMMIT OFF  
GO 
 
ALTER DATABASE [QunInfo] SET CURSOR_DEFAULT  GLOBAL  
GO 
 
ALTER DATABASE [QunInfo] SET CONCAT_NULL_YIELDS_NULL OFF  
GO 
 
ALTER DATABASE [QunInfo] SET NUMERIC_ROUNDABORT OFF  
GO 
 
ALTER DATABASE [QunInfo] SET QUOTED_IDENTIFIER OFF  
GO 
 
ALTER DATABASE [QunInfo] SET RECURSIVE_TRIGGERS OFF  
GO 
 
ALTER DATABASE [QunInfo] SET  DISABLE_BROKER  
GO 
 
ALTER DATABASE [QunInfo] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
GO 
 
ALTER DATABASE [QunInfo] SET DATE_CORRELATION_OPTIMIZATION OFF  
GO 
 
ALTER DATABASE [QunInfo] SET TRUSTWORTHY OFF  
GO 
 
ALTER DATABASE [QunInfo] SET ALLOW_SNAPSHOT_ISOLATION OFF  
GO 
 
ALTER DATABASE [QunInfo] SET PARAMETERIZATION SIMPLE  
GO 
 
ALTER DATABASE [QunInfo] SET READ_COMMITTED_SNAPSHOT OFF  
GO 
 
ALTER DATABASE [QunInfo] SET HONOR_BROKER_PRIORITY OFF  
GO 
 
ALTER DATABASE [QunInfo] SET  READ_WRITE  
GO 
 
ALTER DATABASE [QunInfo] SET RECOVERY SIMPLE  
GO 
 
ALTER DATABASE [QunInfo] SET  MULTI_USER  
GO 
 
ALTER DATABASE [QunInfo] SET PAGE_VERIFY CHECKSUM   
GO 
 
ALTER DATABASE [QunInfo] SET DB_CHAINING OFF  
GO

创建一个名字为GroupData的库，配置同上：

USE [master] 
GO 
CREATE DATABASE [GroupData] ON  PRIMARY  
( NAME = N'GroupData', FILENAME = N'D:\Backup\Database\QQ\GroupData.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) 
 LOG ON  
( NAME = N'GroupData_log', FILENAME = N'D:\Backup\Database\QQ\GroupData_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) 
GO 
 
ALTER DATABASE [GroupData] SET COMPATIBILITY_LEVEL = 100 
GO 
 
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) 
begin 
EXEC [GroupData].[dbo].[sp_fulltext_database] @action = 'enable' 
end 
GO 
 
ALTER DATABASE [GroupData] SET ANSI_NULL_DEFAULT OFF  
GO 
 
ALTER DATABASE [GroupData] SET ANSI_NULLS OFF  
GO 
 
ALTER DATABASE [GroupData] SET ANSI_PADDING OFF  
GO 
 
ALTER DATABASE [GroupData] SET ANSI_WARNINGS OFF  
GO 
 
ALTER DATABASE [GroupData] SET ARITHABORT OFF  
GO 
 
ALTER DATABASE [GroupData] SET AUTO_CLOSE OFF  
GO 
 
ALTER DATABASE [GroupData] SET AUTO_CREATE_STATISTICS ON  
GO 
 
ALTER DATABASE [GroupData] SET AUTO_SHRINK OFF  
GO 
 
ALTER DATABASE [GroupData] SET AUTO_UPDATE_STATISTICS ON  
GO 
 
ALTER DATABASE [GroupData] SET CURSOR_CLOSE_ON_COMMIT OFF  
GO 
 
ALTER DATABASE [GroupData] SET CURSOR_DEFAULT  GLOBAL  
GO 
 
ALTER DATABASE [GroupData] SET CONCAT_NULL_YIELDS_NULL OFF  
GO 
 
ALTER DATABASE [GroupData] SET NUMERIC_ROUNDABORT OFF  
GO 
 
ALTER DATABASE [GroupData] SET QUOTED_IDENTIFIER OFF  
GO 
 
ALTER DATABASE [GroupData] SET RECURSIVE_TRIGGERS OFF  
GO 
 
ALTER DATABASE [GroupData] SET  DISABLE_BROKER  
GO 
 
ALTER DATABASE [GroupData] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
GO 
 
ALTER DATABASE [GroupData] SET DATE_CORRELATION_OPTIMIZATION OFF  
GO 
 
ALTER DATABASE [GroupData] SET TRUSTWORTHY OFF  
GO 
 
ALTER DATABASE [GroupData] SET ALLOW_SNAPSHOT_ISOLATION OFF  
GO 
 
ALTER DATABASE [GroupData] SET PARAMETERIZATION SIMPLE  
GO 
 
ALTER DATABASE [GroupData] SET READ_COMMITTED_SNAPSHOT OFF  
GO 
 
ALTER DATABASE [GroupData] SET HONOR_BROKER_PRIORITY OFF  
GO 
 
ALTER DATABASE [GroupData] SET  READ_WRITE  
GO 
 
ALTER DATABASE [GroupData] SET RECOVERY SIMPLE  
GO 
 
ALTER DATABASE [GroupData] SET  MULTI_USER  
GO 
 
ALTER DATABASE [GroupData] SET PAGE_VERIFY CHECKSUM   
GO 
 
ALTER DATABASE [GroupData] SET DB_CHAINING OFF  
GO

4、修改各个数据库中表的名字：把QunList1统一修改为QunList01这样格式的，这样做的好处就是在合并数据的时候读取到的数据库的数据是按照顺序插入到表中的，不会造成数据页的拆分。

--格式化表名 
USE QunInfo01 
GO 
exec sp_rename 'QunList1','QunList01' 
exec sp_rename 'QunList2','QunList02' 
exec sp_rename 'QunList3','QunList03' 
exec sp_rename 'QunList4','QunList04' 
exec sp_rename 'QunList5','QunList05' 
exec sp_rename 'QunList6','QunList06' 
exec sp_rename 'QunList7','QunList07' 
exec sp_rename 'QunList8','QunList08' 
exec sp_rename 'QunList9','QunList09' 
 
USE GroupData01 
GO 
exec 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'

在QunInfo、GroupData数据库中分别创建一个临时表：tables，用来保存所有的数据库与表的信息，提供数据库合并用。

--创建临时表 
use [QunInfo] 
CREATE TABLE [QunInfo].[dbo].[tables]( 
    [db_name] [sysname] NULL, 
    [table_name] [sysname] NULL, 
    [status] [bit] default 0 
) ON [PRIMARY] 
 
--生成数据库名称与表名称的对应列表 
EXEC sp_MSForEachDB 'USE [?]; 
    --插入表信息 
    INSERT INTO [QunInfo].[dbo].[tables]([table_name]) 
        SELECT name from [?].sys.tables where name like ''QunList%'' order by name 
    --更新数据库名称 
    UPDATE [QunInfo].[dbo].[tables] SET [db_name] = ''?'' WHERE [db_name] is NULL' 
 
use [GroupData] 
--创建临时表 
CREATE TABLE [GroupData].[dbo].[tables]( 
    [db_name] [sysname] NULL, 
    [table_name] [sysname] NULL, 
    [status] [bit] default 0 
) ON [PRIMARY] 
 
--生成数据库名称与表名称的对应列表 
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、对大数据表分区可以加快查询速度。经过估算，所有表格数据加起来近9千万行。我们查询的时候大多用群号字段，所以用这个群号的字段[QunNum]作为分区，每一千万做一个分区，最大的群号为100219998，这样就有11个分区。以下是分区脚本：

USE [QunInfo] 
GO 
 
--1.创建文件组 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_01] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_02] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_03] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_04] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_05] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_06] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_07] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_08] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_09] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_10] 
 
ALTER DATABASE [QunInfo] 
ADD FILEGROUP [FG_QunList_QunNum_11] 
 
 
--2.创建文件 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_01_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_01_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_01]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_02_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_02_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_02]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_03_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_03_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_03]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_04_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_04_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_04]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_05_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_05_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_05]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_06_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_06_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_06]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_07_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_07_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_07]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_08_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_08_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_08]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_09_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_09_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_09]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_10_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_10_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_10]; 
 
ALTER DATABASE [QunInfo] 
ADD FILE 
(NAME = N'FG_QunList_QunNum_11_data',FILENAME = N'D:\Backup\Database\QQ\FG_QunList_QunNum_11_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB ) 
TO FILEGROUP [FG_QunList_QunNum_11]; 
 
 
--3.创建分区函数 
CREATE PARTITION FUNCTION 
[Fun_QunList_QunNum](INT) AS 
RANGE RIGHT 
FOR VALUES(10000000,20000000,30000000,40000000,50000000,60000000,70000000,80000000,90000000,100000000) 
 
 
--4.创建分区方案 
CREATE PARTITION SCHEME 
[Sch_QunList_QunNum] AS 
PARTITION [Fun_QunList_QunNum] 
TO([FG_QunList_QunNum_01],[FG_QunList_QunNum_02],[FG_QunList_QunNum_03],[FG_QunList_QunNum_04],[FG_QunList_QunNum_05],[FG_QunList_QunNum_06],[FG_QunList_QunNum_07],[FG_QunList_QunNum_08],[FG_QunList_QunNum_09],[FG_QunList_QunNum_10],[FG_QunList_QunNum_11]) 
 
 
--5.分区函数的记录数 
SELECT $PARTITION.[Fun_QunList_QunNum](QunNum) AS Partition_num, 
  MIN(QunNum) AS Min_value,MAX(QunNum) AS Max_value,COUNT(1) AS Record_num 
FROM dbo.[QunList] 
GROUP BY $PARTITION.[Fun_QunList_QunNum](QunNum) 
ORDER BY $PARTITION.[Fun_QunList_QunNum](QunNum);

GroupData01~11里面的数据大概有15亿，以群号作为分区依据，每五百万为一组，可分为21个文件组：

USE [GroupData] 
GO 
 
--1.创建文件组 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_01] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_02] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_03] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_04] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_05] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_06] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_07] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_08] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_09] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_10] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_11] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_12] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_13] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_14] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_15] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_16] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_17] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_18] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_19] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_20] 
 
ALTER DATABASE [GroupData] 
ADD FILEGROUP [FG_Group_QunNum_21] 
 
 
--2.创建文件 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_01_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_01_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_01]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_02_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_02_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_02]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_03_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_03_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_03]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_04_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_04_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_04]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_05_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_05_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_05]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_06_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_06_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_06]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_07_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_07_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_07]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_08_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_08_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_08]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_09_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_09_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_09]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_10_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_10_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_10]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_11_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_11_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_11]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_12_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_12_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_12]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_13_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_13_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_13]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_14_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_14_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_14]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_15_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_15_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_15]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_16_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_16_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_16]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_17_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_17_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_17]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_18_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_18_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_18]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_19_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_19_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_19]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_20_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_20_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_20]; 
 
ALTER DATABASE [GroupData] 
ADD FILE 
(NAME = N'FG_Group_QunNum_21_data',FILENAME = N'D:\Backup\Database\QQ\FG_Group_QunNum_21_data.ndf',SIZE = 4096MB, FILEGROWTH = 100MB ) 
TO FILEGROUP [FG_Group_QunNum_21]; 
 
 
--3.创建分区函数 
CREATE PARTITION FUNCTION 
[Fun_Group_QunNum](INT) AS 
RANGE RIGHT 
FOR VALUES(5000000,10000000,15000000,20000000,25000000,30000000,35000000,40000000,45000000,50000000,55000000,60000000,65000000,70000000,75000000,80000000,85000000,90000000,95000000,100000000) 
 
 
--4.创建分区方案 
CREATE PARTITION SCHEME 
[Sch_Group_QunNum] AS 
PARTITION [Fun_Group_QunNum] 
TO([FG_Group_QunNum_01],[FG_Group_QunNum_02],[FG_Group_QunNum_03],[FG_Group_QunNum_04],[FG_Group_QunNum_05],[FG_Group_QunNum_06],[FG_Group_QunNum_07],[FG_Group_QunNum_08],[FG_Group_QunNum_09],[FG_Group_QunNum_10],[FG_Group_QunNum_11],[FG_Group_QunNum_12],[FG_Group_QunNum_13],[FG_Group_QunNum_14],[FG_Group_QunNum_15],[FG_Group_QunNum_16],[FG_Group_QunNum_17],[FG_Group_QunNum_18],[FG_Group_QunNum_19],[FG_Group_QunNum_20],[FG_Group_QunNum_21]) 
 
 
--5.分区函数的记录数 
SELECT $PARTITION.[Fun_Group_QunNum](QunNum) AS Partition_num, 
  MIN(QunNum) AS Min_value,MAX(QunNum) AS Max_value,COUNT(1) AS Record_num 
FROM dbo.[Group] 
GROUP BY $PARTITION.[Fun_Group_QunNum](QunNum) 
ORDER BY $PARTITION.[Fun_Group_QunNum](QunNum);

6、接下来在数据库[QunInfo]新建一个QunList表用于合并QunInfo01~11的所有表格，涉及内容如下：1) 在QunInfo数据库中创建分区表QunList，去掉没有太大意义的ID字段；2) 以[QunNum]作为聚集索引，而且是唯一的，这个需要开启IGNORE_DUP_KEY = ON选项，这样才可以在批量插入的时候忽略重复值；3) 对原表的[MastQQ]字段从int类型变成smallint ，[CreateDate]字段从varchar(10)类型变为date，数据类型修改是为了减少表占用的空间；4) 使用刚刚创建好的分区方案，之后创建的索引进行索引对齐；5) 对表使用行压缩，减少数据库占用空间。脚本如下：

--创建优化后的QunList表

use [QunInfo]
CREATE TABLE [dbo].[QunList](

[QunNum] [int] NOT NULL, 
[MastQQ] [smallint] NULL, 
[CreateDate] [date] NULL, 
[Title] [varchar](22) NULL, 
[Class] [varchar](38) NULL, 
[QunText] [varchar](80) NULL,

CONSTRAINT [PK_QunList2] PRIMARY KEY CLUSTERED
(

[QunNum] 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_QunList_QunNum
) ON Sch_QunList_QunNum
GO

同理对GroupData进行同样的操作，新建一个Group表：

use GroupData

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
) ON Sch_Group_QunNum
GO

7、数据表合并

把11个数据库都合并到新创建的QunInfo的QunList表中（根据电脑性能，大概要跑一两个小时）：

--合并数据

DECLARE @tablename sysname
DECLARE @dbname sysname
DECLARE @sql NVARCHAR(max)

--游标
DECLARE @itemCur CURSOR
SET @itemCur = CURSOR FOR

SELECT db_name,table_name from [QunInfo].[dbo].[tables]

OPEN @itemCur
FETCH NEXT FROM @itemCur INTO @dbname,@tablename
WHILE @@FETCH_STATUS=0

BEGIN

 
SET @sql = ' 
INSERT INTO [QunInfo].[dbo].[QunList] 
       ([QunNum] 
       ,[MastQQ] 
       ,[CreateDate] 
       ,[Title] 
       ,[Class] 
       ,[QunText]) 
SELECT [QunNum] 
  ,[MastQQ] 
  ,[CreateDate] 
  ,[Title] 
  ,[Class] 
  ,[QunText] 
FROM ['+@dbname+'].[dbo].['+@tablename+']' 

EXEC(@sql) 
 
UPDATE [QunInfo].[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,@tablename 
END  
CLOSE @itemCur 
DEALLOCATE @itemCur 
--运行插入脚本时，可以查看进度 
SELECT * from [QunInfo].[dbo].[tables]

把GroupData01~11里的数据合并到GroupData库里的Group表（大概跑几个小时，老爷机另算）：

--合并数据 
DECLARE @tablename sysname 
DECLARE @dbname sysname 
DECLARE @sql NVARCHAR(max) 
 
--游标 
DECLARE @itemCur CURSOR 
SET @itemCur = CURSOR FOR  
    SELECT db_name,table_name from [GroupData].[dbo].[tables] 
 
OPEN @itemCur 
FETCH NEXT FROM @itemCur INTO @dbname,@tablename 
WHILE @@FETCH_STATUS=0 
 
BEGIN 
     
    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,@tablename 
END  
 
CLOSE @itemCur 
DEALLOCATE @itemCur

为GroupData的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

8、上面数据插入时已经做了行压缩，接下来对QunInfo进行页压缩：

--页压缩 
ALTER TABLE [QunList]   
REBUILD WITH (DATA_COMPRESSION = PAGE ); 
--对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

9、到此一切优化操作结束。查查数据库占用空间：

QQ数据库
本文为作者admin发布，未经允许禁止转载！
上一篇 下一篇
评论
暂无评论 >_<
加入评论