您现在的位置是：主页 > DATA >

QQ群关系数据库-qqGroup.zip

2021-06-23 14:06:16DATA 82415人已围观

免费预览：

该QQ群数据库其实是2011年11月的时候被黑客从一个腾讯漏洞网站上脱裤下来的，里面包含了当时所有QQ号的基本个人信息与所加入群的对应关系以及群的一些基本信息，解压出来后数据库总大小超过了90G，用的是SQL Server 2000，以数据分片的形式存储，共有22个库。本次上传附件非原压缩包大小，用ZIP重新打包过，一共是34.8G

一、下载这个版本的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|/

安装方法详见：SQL Server 2008 安装图解

二、你下载的是群号数据库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包含的表如下图，这样很不方便查询。

image.png
image.png
而GroupData01~11共11个库有上千张表.

虽然可以查询使用，但耗时漫长，效率很低，占用磁盘空间也大，所以我们要优化一下数据库。

目的是：提高查询速度跟效率；减少磁盘空间占用。

2、把QunInfo0111、GroupData0111分别合并到两个库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的所有表格，涉及内容如下：

在QunInfo数据库中创建分区表QunList，去掉没有太大意义的ID字段；

以[QunNum]作为聚集索引，而且是唯一的，这个需要开启IGNORE_DUP_KEY = ON选项，这样才可以在批量插入的时候忽略重复值；

对原表的[MastQQ]字段从int类型变成smallint ，[CreateDate]字段从varchar(10)类型变为date，数据类型修改是为了减少表占用的空间；

使用刚刚创建好的分区方案，之后创建的索引进行索引对齐；

对表使用行压缩，减少数据库占用空间。

脚本如下：

--创建优化后的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
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、到此一切优化操作结束。查查数据库占用空间：

Use [QunInfo]
EXEC sp_spaceused [QunList]
Use [GroupData]
EXEC sp_spaceused [GroupData]
结果如下：

哎哟，碉堡了，QunInfo只占用了4.5G，优化之前占用8.5G。

GroupData原来的mdf文件大概有83GB，优化压缩之后mdf文件只占34GB。

两个库所有空间加起来大概65GB左右。

再看看每个分区的数据：

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

image.png
image.png
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 Qun [群号码]

查询一个QQ好的脚本：

exec QQ [QQ号码]

结果都是秒出。

接下来可以右键删除这22个库了：QunInfo0111、GroupData0111，只留下QunInfo库和GroupData库。



数据下载地址如下：

扫码免登录支付
本文章为付费文章，是否支付18宇宙币后完整阅读？

如果您已购买过该文章，[登录帐号]后即可查看

上一篇：环保油漆企业通用dedecms模板

下一篇：50个全球免费电子数据库一览

相关文章

随机图文

本栏排行

QQ群关系数据库-qqGroup.zip

本栏推荐

QQ群关系数据库-qqGroup.zip

猜你喜欢

站点信息

  • 文章统计： 442 篇文章
  • 微信公众号：扫描二维码，关注我们
}); });