栏目导航: 首页 > 网站运营 > 开发设计 > 内容

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

www.hx99.net    时间: 2015-03-03    阅读: 次     整理: 华西安全网

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

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

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

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

本文作者:gcz1992

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

数据附加

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

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

 

  1. EXEC sp_attach_db "QunInfo1", "你的QQ数据库所在目录"
  2.  
  3. EXEC sp_attach_db "QunInfo1", "N:\SQL\QQ\QQ数据库\QQ数据库\QunData\QunInfo1_Data.MDF"
  4.  
  5. EXEC sp_attach_db "GroupData1", "N:\SQL\QQ\QQ数据库\QQ数据库\QunData\GroupData1_Data.MDF"

数据附加成功后如图:

1.jpg

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

 

数据分析与建立索引

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

数据库里面的表设计如下

QQ数据:

  1. QQ数据()
  2. QQNum int NOT NULL, //号码
  3. Nick varchar (20) NULL, //昵称
  4. Age int NULL, //年龄
  5. Gender int NULL, //性别
  6. Auth int NULL, //职位
  7. QunNum int NOT NULL, //群号

QQ群数据:

 

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

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

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

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

索引例子如下:

 

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

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


 

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

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

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

继续下一步,NEXT~

 

优化SQL查询-存储过程

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

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

存储过程代码如下:

 

(1)查询QQ信息

 

  1. CREATE PROCEDURE REN_CX @QQNum int=null
  2. AS
  3. BEGIN
  4. declare @sql varchar(8000)
  5. declare @dbIdx int = 1
  6. -------------------------------------------------------------
  7. if OBJECT_ID('tempdb.dbo.#QunList') is not null
  8. drop table #QunList
  9.  
  10. --新建了一个临时表
  11. create table #QunList
  12. (
  13. QQNum int,
  14. Nick varchar(20),
  15. Age int,
  16. Gender int,
  17. Auth int,
  18. QunNum int
  19. )
  20.  
  21. --------------------------------------------------------------
  22. -- Search QunList
  23. --一个嵌套循环查找 一个表一个表找,找到一个插入临时表一条
  24. while @dbIdx <= 11
  25. begin
  26. declare @tblIdx int = 1
  27. declare @tblName varchar(50)
  28.  
  29. while @tblIdx <= 100
  30. begin
  31. set @tblName = 'GroupData' + CONVERT(varchar(2), @dbIdx) + '.dbo.Group'
  32. set @tblName += CONVERT(varchar(5), (@dbIdx - 1) * 100 + @tblIdx)
  33.  
  34. set @sql = 'select QQNum, Nick, Age,Gender, Auth,QunNum from '
  35. set @sql += @tblName + ' where QQNum=' + CONVERT(varchar(15), @QQNum)
  36.  
  37. insert into #QunList(QQNum, Nick,Age,Gender,Auth,QunNum) exec(@sql)
  38.  
  39. print @tblname + ' OK'
  40.  
  41. set @tblIdx += 1 --循环递增
  42. end
  43.  
  44. set @dbIdx += 1 --外层递增
  45. end
  46.  
  47. -----------------------------------------------------------------------
  48. --显示
  49.  
  50. select * from #QunList
  51. END
  52. GO

 

(2)查询群消息

 

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

(3)查询群成员

  1. --建立群成员的存储过程
  2. CREATE PROCEDURE QUNCY_CX @QunNum int=null
  3. as
  4. BEGIN
  5. declare @sql varchar(8000)
  6.  
  7. --群成员
  8. set @sql = 'select * from GroupData' + CONVERT(varchar(5), @QunNum / 10000000 + 1) + '.dbo.'
  9. set @sql += 'Group' + CONVERT(varchar(10), @QunNum / 100000 + 1) + ' where QunNum=' + Convert(varchar(20),@QunNum)
  10.  
  11. exec(@sql)
  12. print(@sql)
  13. END
  14. GO

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

下面为执行:

 

  1. EXEC REN_CX QQ
  2. EXEC QUNXX_CX 群号
  3. EXEC QUNCY_CX 群号

效果如下:

2.jpg

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

 

C#源码方面

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

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

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

选取部分代码:

 

  1. //数据库连接
  2. SqlConnection coon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString);
  3. DataSet finaldata = new DataSet();
  4. public DataSet QueryREN(string _QueryREN)
  5. {
  6. string sqlstr = " EXEC REN_CX "+ _QueryREN; //执行存储过程
  7. SqlDataAdapter da = new SqlDataAdapter(sqlstr, coon);
  8. DataSet ds = new DataSet();
  9. da.Fill(ds);
  10. return ds;
  11. }


然后是单击事件:

  1. //单击事件
  2. protected void Button1_Click(object sender, EventArgs e)
  3. {
  4. string ren = TextBox1.Text;
  5. try
  6. {
  7. if (ren != null || Convert.ToInt32(ren) > 10000)
  8. {
  9. if (QueryREN(ren) == null || QueryREN(ren).Tables[0].Rows.Count == 0)
  10. { Label1.Text = "查无此人"; }
  11. else
  12. {
  13. finaldata.Merge(QueryREN(ren));
  14. GridView1.DataSource = finaldata; //绑定数据
  15. GridView1.DataBind();
  16. }
  17. }
  18. else
  19. {
  20. Label1.Text = "重新输入";
  21. }
  22. }
  23. catch(Exception ex)
  24. {Label1.Text = "BUG:"+ex.ToString(); //BUG测试
  25. }
  26. }


 

效果如下:

 未命名-1.jpg

方案总结

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

//Silic.Org

本文来源:华西安全网[http://www.hx99.net]
发表评论】【告诉QQ好友】【错误报告】【加入收藏】【关闭