SQL Server数据库文件的还原与优化

By 有所思 on January 13, 2016 4:00 PM | No Comments

　　这个数据库的文件解压后有几十个GB，分成十几个库，我是参考知乎用户的回帖中介绍的步骤一步一步做下来的，其中有几处错误做了改正。

　　由于这些mdf是从SQL Server 2000上创建的，想要直接附加它们，最高可以使用SQL Server 2008 R2，如果想在SQL Server 2012或更高版本的SQL Server上使用，请参考这里的操作，我这里使用的是SQL Server 2012。

　　在实际操作过程中，请根据实际情况修改路径：

一、附加数据库

　　由于没有日志文件，所以采用执行SQL语句的形式附加进数据库（注意QunInfo01~QunInfo11的命名不要改）。打开Microsoft SQL Server Management Studio，菜单栏右上角找到“新建查询按钮”，打执行下面的语句：

--附加数据库(MDF文件路径可根据需要修改)
sp_attach_single_file_db @dbname= 'GroupData01',@physname= 'D:\数据库\GroupData1_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData02',@physname= 'D:\数据库\GroupData2_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData03',@physname= 'D:\数据库\GroupData3_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData04',@physname= 'D:\数据库\GroupData4_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData05',@physname= 'D:\数据库\GroupData5_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData06',@physname= 'D:\数据库\GroupData6_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData07',@physname= 'D:\数据库\GroupData7_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData08',@physname= 'D:\数据库\GroupData8_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData09',@physname= 'D:\数据库\GroupData9_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData10',@physname= 'D:\数据库\GroupData10_Data.MDF'
go
sp_attach_single_file_db @dbname= 'GroupData11',@physname= 'D:\数据库\GroupData11_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo01',@physname= 'D:\数据库\QunInfo1_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo02',@physname= 'D:\数据库\QunInfo2_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo03',@physname= 'D:\数据库\QunInfo3_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo04',@physname= 'D:\数据库\QunInfo4_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo05',@physname= 'D:\数据库\QunInfo5_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo06',@physname= 'D:\数据库\QunInfo6_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo07',@physname= 'D:\数据库\QunInfo7_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo08',@physname= 'D:\数据库\QunInfo8_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo09',@physname= 'D:\数据库\QunInfo9_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo10',@physname= 'D:\数据库\QunInfo10_Data.MDF'
go
sp_attach_single_file_db @dbname= 'QunInfo11',@physname= 'D:\数据库\QunInfo11_Data.MDF'
go

　　这样，得到了22个数据库，上千张表，查询耗时长，效率低，需要做进一步优化。

二、合并数据库

　　把QunInfo01~11、GroupData01~11分别合并到两个库QunInfo、GroupData，然后使用用分区表提升性能。步骤如下：

1、创建新的库用于合并

　　创建一个名为QunInfo的数据库，设置数据库为简单恢复模式。mdf、ldf文件保存路径根据需要更改，脚本如下：

USE [master]
GO
CREATE DATABASE [QunInfo] ON  PRIMARY 
( NAME = N'QunInfo', FILENAME = N'D:\DATA\QunInfo.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'QunInfo_log', FILENAME = N'D:\DATA\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:\DATA\GroupData.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'GroupData_log', FILENAME = N'D:\DATA\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

2、统一表名

　　修改各个数据库中表的名字：把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'

3、创建临时表

　　在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'

4、表分区

　　对大数据表分区可以加快查询速度。经过估算，QunInfo表格数据加起来近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:\数据库\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:\数据库\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:\数据库\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:\数据库\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:\数据库\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:\数据库\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:\数据库\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:\数据库\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:\数据库\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:\数据库\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:\数据库\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.分区函数的记录数, 执行该查询前请先执行完下一步————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);

　　GroupData里面的数据大概有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:\数据库\FG_Group_QunNum_01_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_01];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_02_data',FILENAME = N'D:\数据库\FG_Group_QunNum_02_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_02];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_03_data',FILENAME = N'D:\数据库\FG_Group_QunNum_03_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_03];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_04_data',FILENAME = N'D:\数据库\FG_Group_QunNum_04_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_04];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_05_data',FILENAME = N'D:\数据库\FG_Group_QunNum_05_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_05];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_06_data',FILENAME = N'D:\数据库\FG_Group_QunNum_06_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_06];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_07_data',FILENAME = N'D:\数据库\FG_Group_QunNum_07_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_07];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_08_data',FILENAME = N'D:\数据库\FG_Group_QunNum_08_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_08];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_09_data',FILENAME = N'D:\数据库\FG_Group_QunNum_09_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_09];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_10_data',FILENAME = N'D:\数据库\FG_Group_QunNum_10_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_10];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_11_data',FILENAME = N'D:\数据库\FG_Group_QunNum_11_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_11];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_12_data',FILENAME = N'D:\数据库\FG_Group_QunNum_12_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_12];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_13_data',FILENAME = N'D:\数据库\FG_Group_QunNum_13_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_13];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_14_data',FILENAME = N'D:\数据库\FG_Group_QunNum_14_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_14];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_15_data',FILENAME = N'D:\数据库\FG_Group_QunNum_15_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_15];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_16_data',FILENAME = N'D:\数据库\FG_Group_QunNum_16_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_16];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_17_data',FILENAME = N'D:\数据库\FG_Group_QunNum_17_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_17];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_18_data',FILENAME = N'D:\数据库\FG_Group_QunNum_18_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_18];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_19_data',FILENAME = N'D:\数据库\FG_Group_QunNum_19_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_19];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_20_data',FILENAME = N'D:\数据库\FG_Group_QunNum_20_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
TO FILEGROUP [FG_Group_QunNum_20];

ALTER DATABASE [GroupData]
ADD FILE
(NAME = N'FG_Group_QunNum_21_data',FILENAME = N'D:\数据库\FG_Group_QunNum_21_data.ndf',SIZE = 100MB, FILEGROWTH = 10MB )
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.分区函数的记录数, 执行该查询前请先执行完下一步————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);

5、新建数据表

　　在数据库[QunInfo]新建一个QunList表用于合并QunInfo01~11的所有表格，涉及内容如下：

--创建优化后的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]([QunNum])
) ON [Sch_QunList_QunNum]([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]([QunNum])
) ON [Sch_Group_QunNum]([QunNum])
GO

6、数据表合并

　　把11个数据库都合并到新创建的QunInfo的QunList表中（根据电脑性能，大概要跑一两个小时。E5-2640 16核心15分钟）：

--合并数据
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表（大概跑几个小时。E5-2640 16核心6小时左右）：

--合并数据
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

7、建立索引

　　为GroupData的Group表里的QQnum新建一个索引并对索引进行压缩：

--索引行压缩
USE [GroupData]
GO
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进行页压缩：

--索引页压缩
USE [QunInfo]
GO
ALTER TABLE [QunList]  
REBUILD WITH (DATA_COMPRESSION = PAGE );

　　对GroupData进行页压缩：

--索引页压缩
USE [GroupData]
GO
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、查看压缩结果：

Use [QunInfo] 
EXEC sp_spaceused [QunList]  
Use [GroupData] 
EXEC sp_spaceused [Group]

　　看看每个分区的数据：

SELECT 
    partition = $PARTITION.Fun_QunList_QunNum([QunNum])
    ,rows      = COUNT(*)
    ,min    = MIN([QunNum])
    ,max    = MAX([QunNum])
FROM [dbo].[QunList]
GROUP BY $PARTITION.Fun_QunList_QunNum([QunNum])
ORDER BY PARTITION

10、存储过程

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Qun]    
   (    
       @QunNum int
     )    
    AS    
SELECT  a.[QunNum]
      ,[QQNum]
      ,[Nick]
      ,[Age]
      ,(case when [Gender]=0 then '男' else '女' end) 性别
      ,(case when [Auth]=1 then '成员' when [Auth]=4 then '群主' when [Auth]=2 then '管理' end) 身份
      ,[MastQQ]
      ,[CreateDate]
      ,[Title]
      ,[Class]
      ,[QunText]
  FROM [GroupData].[dbo].[Group] a
  left join [QunInfo].[dbo].[QunList] b
  on a.[QunNum]=b.QunNum
  where  a.[QunNum]=@QunNum order by [Auth] desc
GO

use QunInfo
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Qun]    
   (    
       @QunNum int
     )    
    AS    
SELECT  a.[QunNum]
      ,[QQNum]
      ,[Nick]
      ,[Age]
      ,(case when [Gender]=0 then '男' else '女' end) 性别
      ,(case when [Auth]=1 then '成员' when [Auth]=4 then '群主' when [Auth]=2 then '管理' end) 身份
      ,[MastQQ]
      ,[CreateDate]
      ,[Title]
      ,[Class]
      ,[QunText]
  FROM [GroupData].[dbo].[Group] a
  left join [QunInfo].[dbo].[QunList] b
  on a.[QunNum]=b.QunNum
  where  a.[QunNum]=@QunNum order by [Auth] desc
GO

use GroupData
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Qun]    
   (    
       @QunNum int
     )    
    AS    
SELECT  a.[QunNum]
      ,[QQNum]
      ,[Nick]
      ,[Age]
      ,(case when [Gender]=0 then '男' else '女' end) 性别
      ,(case when [Auth]=1 then '成员' when [Auth]=4 then '群主' when [Auth]=2 then '管理' end) 身份
      ,[MastQQ]
      ,[CreateDate]
      ,[Title]
      ,[Class]
      ,[QunText]
  FROM [GroupData].[dbo].[Group] a
  left join [QunInfo].[dbo].[QunList] b
  on a.[QunNum]=b.QunNum
  where  a.[QunNum]=@QunNum order by [Auth] desc
GO
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[QQ]    
   (    
       @QQNum int
     )    
    AS    
SELECT  a.[QunNum] 群号码
	  ,[Title] 群名
      ,[QQNum] QQ号码
      ,[Nick] 昵称
      ,[Age]
      ,(case when [Gender]=0 then '男' else '女' end) 性别
      ,(case when [Auth]=1 then '成员' when [Auth]=4 then '群主' when [Auth]=2 then '管理' end) 身份
      ,[MastQQ]
      ,[CreateDate]
      ,[QunText]
  FROM [GroupData].[dbo].[Group] a
  left join [QunInfo].[dbo].[QunList] b
  on a.[QunNum]=b.QunNum
  where  [QQNum]=@QQNum order by CreateDate
GO
USE [QunInfo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[QQ]    
   (    
       @QQNum int
     )    
    AS    
SELECT  a.[QunNum] 群号码
	  ,[Title] 群名
      ,[QQNum] QQ号码
      ,[Nick] 昵称
      ,[Age]
      ,(case when [Gender]=0 then '男' else '女' end) 性别
      ,(case when [Auth]=1 then '成员' when [Auth]=4 then '群主' when [Auth]=2 then '管理' end) 身份
      ,[MastQQ]
      ,[CreateDate]
      ,[QunText]
  FROM [GroupData].[dbo].[Group] a
  left join [QunInfo].[dbo].[QunList] b
  on a.[QunNum]=b.QunNum
  where  [QQNum]=@QQNum order by CreateDate
GO
USE GroupData
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[QQ]    
   (    
       @QQNum int
     )    
    AS    
SELECT  a.[QunNum] 群号码
	  ,[Title] 群名
      ,[QQNum] QQ号码
      ,[Nick] 昵称
      ,[Age]
      ,(case when [Gender]=0 then '男' else '女' end) 性别
      ,(case when [Auth]=1 then '成员' when [Auth]=4 then '群主' when [Auth]=2 then '管理' end) 身份
      ,[MastQQ]
      ,[CreateDate]
      ,[QunText]
  FROM [GroupData].[dbo].[Group] a
  left join [QunInfo].[dbo].[QunList] b
  on a.[QunNum]=b.QunNum
  where  [QQNum]=@QQNum order by CreateDate
GO

　　创建完存储过程之后，查询起来就很方便了：exec dbo.Qun [群号码] 或者exec dbo.QQ [QQ号码]。最后可以删除QunInfo01~11、GroupData01~11这22个库了，只留下QunInfo库和GroupData库。

　　感谢原文作者提供这么详尽的使用方法。

本文结束。

Categories:

Leave a comment


Search