QQ群关系库查询解决方案(上)

大家都知道，前阵QQ群关系数据库泄露了，解压后90多个G的数据，涉及7000多万个QQ群的资料瞬间没有隐私。

如果只是几百几千条数据，可能根本用不到解决方案，但是对于这种千万到亿级别的查询，还是需要写一下的，给大家提供技术参考和学习。欠缺的地方希望大家指出，文章分上下两篇，这是上篇。

先介绍下数据库，MSSQL(SQL server)，版本随意，系统当然是Windows，我用的2008 R2。

这个数据库其实是很早以前通过腾讯群接口遍历出来的数据，数据库共24.5G。共有两个版本，一个是数据商人卖的带密码的7z版本，另一个是rar版本，外面被封的就是7z的，两个版本网上其实都有，出于隐私考虑，不提供下载地址。

本文作者：gcz1992

论坛地址：http://bbs.blackbap.org/thread-5318-1-1.html

数据附加

数据库解压后共109GB，其中只有MDF(主数据文件)，没有LDF(日志文件)，如果是手动添加的话，不要附加LDF也可以成功。

首先解决附加数据库的时候出现找不到LDF的问题。直接用SQL语句执行，更快更简。

EXEC sp_attach_db “QunInfo1”, “你的QQ数据库所在目录”
EXEC sp_attach_db “QunInfo1”, “N:\SQL\QQ\QQ数据库\QQ数据库\QunData\QunInfo1_Data.MDF”
EXEC sp_attach_db “GroupData1”, “N:\SQL\QQ\QQ数据库\QQ数据库\QunData\GroupData1_Data.MDF”

数据附加成功后如图：

然后我们继续下一步，Next~

数据分析与建立索引

一共有22个分库，其中11个是QQ群内的数据，11个是群的数据，分别是GroupData 库和QunInfo 库。

数据库里面的表设计如下

QQ数据：

QQ数据（）
QQNum int NOT NULL, //号码
Nick varchar (20) NULL, //昵称
Age int NULL, //年龄
Gender int NULL, //性别
Auth int NULL, //职位
QunNum int NOT NULL, //群号

QQ群数据：

群信息
QunNum int NOT NULL, //群号
CreateDate varchar (10) NULL, //创建时间
Title varchar (22) NULL, //标题
QunText varchar (80) NULL, //公告

两个表的公共字段是 QunNum 群号。

群成员和群信息是有规律的，但是QQ信息没有顺序，这样去查询的话就会变得很慢。而每个人都有多个昵称(其中可能包含真名，所以数据量光GroupData 的11个库中就有大概12亿行左右。

这么大的数据量查询，首先要先优化，否则就是神机也会卡，当然超神机除外。生成索引这是加快速度最好的优化方法。

索引例子如下：

IF not EXISTS (SELECT indid FROM GroupData1.dbo.sysindexes WHERE name = ‘ix_QQNum_Group7’)
CREATE INDEX ix_QQNum_Group7 On GroupData1.dbo.Group7 (QQNum);

(更多代码可以去论坛下载)

在这里我要感谢一下习科的 @ska 同学分享他找到的索引，的确比我的更好。因为具体到了字段列，更加快了查询速度，同样在习科论坛，可以去下载~

建立索引的过程会很慢，因为表太多，建议大家可以分开执行，一点一点来，更快。

索引建立好后大约可以优化几千倍（随电脑配置而定），好点的机子会从分钟变成秒，这样肯定在60秒之内了

继续下一步，NEXT~

优化SQL查询-存储过程

我用C#写的WEB查询页面，毕竟是Windows系统，只要有IIS并且支持C#就可以用了。

写代码也是改了很多次，最后发现程序生成的语句，总会拖SQL的后腿，于是决定精简程序，优化SQL建立存储过程，它是最快的。

存储过程代码如下：

(1)查询QQ信息

CREATE PROCEDURE REN_CX @QQNum int=null
AS
BEGIN
declare @sql varchar(8000)
declare @dbIdx int = 1
————————————————————-
if OBJECT_ID(‘tempdb.dbo.#QunList’) is not null
drop table #QunList
–新建了一个临时表
create table #QunList
(
QQNum int,
Nick varchar(20),
Age int,
Gender int,
Auth int,
QunNum int
)
————————————————————–
— Search QunList
–一个嵌套循环查找一个表一个表找，找到一个插入临时表一条
while @dbIdx <= 11
begin
declare @tblIdx int = 1
declare @tblName varchar(50)
while @tblIdx <= 100
begin
set @tblName = ‘GroupData’ + CONVERT(varchar(2), @dbIdx) + ‘.dbo.Group’
set @tblName += CONVERT(varchar(5), (@dbIdx – 1) * 100 + @tblIdx)
set @sql = ‘select QQNum, Nick, Age,Gender, Auth,QunNum from ‘
set @sql += @tblName + ‘ where QQNum=’ + CONVERT(varchar(15), @QQNum)
insert into #QunList(QQNum, Nick,Age,Gender,Auth,QunNum) exec(@sql)
print @tblname + ‘ OK’
set @tblIdx += 1 –循环递增
end
set @dbIdx += 1 –外层递增
end
———————————————————————–
–显示
select * from #QunList
END
GO

(2)查询群消息

–建立查询群信息的存储过程
CREATE PROCEDURE QUNXX_CX @QunNum int=null
as
BEGIN
declare @sql varchar(8000)
set @sql = ‘select QunNum,Title,QunText from QunInfo’ + CONVERT(varchar(5), @QunNum / 10000000 + 1) + ‘.dbo.’
set @sql += ‘QunList’ + CONVERT(varchar(10), @QunNum / 1000000 + 1) + ‘ where QunNum=’ + Convert(varchar(20),@QunNum)
exec(@sql)
print(@sql)
END
GO

(3)查询群成员

–建立群成员的存储过程
CREATE PROCEDURE QUNCY_CX @QunNum int=null
as
BEGIN
declare @sql varchar(8000)
–群成员
set @sql = ‘select * from GroupData’ + CONVERT(varchar(5), @QunNum / 10000000 + 1) + ‘.dbo.’
set @sql += ‘Group’ + CONVERT(varchar(10), @QunNum / 100000 + 1) + ‘ where QunNum=’ + Convert(varchar(20),@QunNum)
exec(@sql)
print(@sql)
END
GO

执行完上面的3个SQL，存储过程就那建好了。

下面为执行：

EXEC REN_CX QQ号
EXEC QUNXX_CX 群号
EXEC QUNCY_CX 群号

效果如下：

至此查询已成功优化至 20 秒以内，使用朋友的神机(8核1T)秒查，使用服务器同样秒查。

C#源码方面

C#方面没什么好说的，我并没有优化UI，因为功能已经实现了。

代码的话直接Button_Click事件 DataSet 读取，GridView.DataSource绑定，拖控件大家都可以完成。

其实编程不难，C#更不难，只看自己学不学。源码我打包放论坛了，如果有什么不懂的可以论坛私信。

选取部分代码：

//数据库连接
SqlConnection coon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[“connectionString”].ConnectionString);
DataSet finaldata = new DataSet();
public DataSet QueryREN(string _QueryREN)
{
string sqlstr = ” EXEC REN_CX “+ _QueryREN; //执行存储过程
SqlDataAdapter da = new SqlDataAdapter(sqlstr, coon);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}

然后是单击事件：

//单击事件
protected void Button1_Click(object sender, EventArgs e)
{
string ren = TextBox1.Text;
try
{
if (ren != null || Convert.ToInt32(ren) > 10000)
{
if (QueryREN(ren) == null || QueryREN(ren).Tables[0].Rows.Count == 0)
{ Label1.Text = “查无此人”; }
else
{
finaldata.Merge(QueryREN(ren));
GridView1.DataSource = finaldata; //绑定数据
GridView1.DataBind();
}
}
else
{
Label1.Text = “重新输入”;
}
}
catch(Exception ex)
{Label1.Text = “BUG:”+ex.ToString(); //BUG测试
}
}

效果如下：

方案总结

这次解决方案的主要是在查询优化上面，索引和存储过程的应用才是大数据查询的关键。每个看起来很好的网站都是由强大的数据处理机制支持的，例如银行的数据秒查等等。而数据的安全就是Web网络飞速时代的安全，希望以后网络安全的发展会越来越好吧，也许 NoSQL将会崛起，大家不妨关注一下。

//Silic.Org

发表评论 取消回复

发表评论取消回复