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

作者：srxh   发布：2014-02-03  

 大家都知道，前阵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语句执行，更快更简。

1. EXEC sp_attach_db “QunInfo1″, “你的QQ数据库所在目录”

3. EXEC sp_attach_db “QunInfo1″, “N:\SQL\QQ\QQ数据库\QQ数据库\QunData\QunInfo1_Data.MDF”

5. EXEC sp_attach_db “GroupData1″, “N:\SQL\QQ\QQ数据库\QQ数据库\QunData\GroupData1_Data.MDF”

数据附加成功后如图：

然后我们继续下一步，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

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. )

21. ————————————————————–

22. – Search QunList

23. –一个嵌套循环查找 一个表一个表找，找到一个插入临时表一条

24. while @dbIdx <= 11

25. begin

26. declare @tblIdx int = 1

27. declare @tblName varchar(50)

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)

34. set @sql = ‘select QQNum, Nick, Age,Gender, Auth,QunNum from ‘

35. set @sql += @tblName + ‘ where QQNum=’ + CONVERT(varchar(15), @QQNum)

37. insert into #QunList(QQNum, Nick,Age,Gender,Auth,QunNum) exec(@sql)

39. print @tblname + ‘ OK’

41. set @tblIdx += 1 –循环递增

42. end

44. set @dbIdx += 1 –外层递增

45. end

47. ———————————————————————–

48. –显示

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)

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)

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 群号

效果如下：

至此查询已成功优化至 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();

5. public DataSet QueryREN(string _QueryREN)

6. {

7. string sqlstr = ” EXEC REN_CX “+ _QueryREN; //执行存储过程

8. SqlDataAdapter da = new SqlDataAdapter(sqlstr, coon);

9. DataSet ds = new DataSet();

10. da.Fill(ds);

11. return ds;

12. }

然后是单击事件：

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. }

效果如下：

方案总结

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

//Silic.Org

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

本文由习科论坛会员gcz1992供稿，给大家提供技术参考和学习，欠缺的地方希望大家指出，文章分上下两篇，上篇已于前几日发布，这是下篇。

一个报告写了这么长时间也算奇葩了，嗯，终于有空写文章了，拖到了现在，这期间我试用了MySQL ，Oracle和Nosql中的Couchbase，事实上都没有MSSQL来的干脆，因为数据的导入导出过程实在太慢了，慢到俺这渣机受不了。

不过在我的试用过程中，MySQL在小数据量的时候有很强大的优势，短小而精悍，用起来十分舒服，在全部导入的情况下，与MSSQL速度比起来虽然 会有些慢，但在建立好索引的情况下，也是可以使用的，没有索引就没有速度。索引就是一个数据查询的灵魂。

QQ群关系库MSSQL到Mysql 的 数据导入

我进行了从MSSQL到 Mysql 的数据转入测试。
（1）推荐使用工具MySQL Migration Toolkit，工具很容易上手，但在Source Database 处和set parameter时要指定数据连接类型， 即加上“charset=utf8”，网上有很多这样的资料大家可以直接百度。另外转换的时间会很长，大家可以用一个表来测试一下速度。

改换完成后效果图如下：

（2）   第二种方法就是自己写一个转换工具，因为数据结构并不复杂。
将数据一条一条来读，然后插入就可以了，而且这种方法同样适用于Oracle等数据库，虽然它也会很慢。

我的导入就是自己写的只是数据太大了，以至于电脑会卡死，后来又写了事务加插入判断才算让它可以好好干活。

工具以及源码春节后我会放到习科论坛，大家可以关注一下。

QQ库数据结构整体优化

已知这个库中Group Data库是其中的关键，因为它保存了用户的昵称，年龄，性别等信息。结构如下：

而这个结构事实上又是以QunNum 来进行的存储，以方便群内成员的查询。

如果我们反其道而行呢？将数据结构进行改变，以QQNum 来进行结构存储，而后划分区段是不是就可以更好的优化查询速度呢？

其实就是对整个库进行一个以QQNum 的一个ASC排序 ，从10000开始一直到库内最大的QQ的一个排序。部分效果如下：

那么如何进行这个排序呢？

先建立新的数据库，以ASC顺序来建立表，然后对数据进行判断，划分存储表与存储位置，随后插入数据。这个也可以用存储过程来干，只不过同样是很漫长的一件事，而且对硬盘空间是一个大考验，当然服务器就很轻松了。

我只进行了很少部分的转换，就如上图，效果就是查本人的渣机会比神机还要快，虽然我的硬盘已经红到爆了。

完整存储过程我也会在年后放到习科论坛，大家多多关注。

NoSQL笔记

这段时间学了点NoSQL，现在分享给大家。

个人认为这个数据库的兴起是一种必然，关系型数据库的应用在大数据时代出现了很大的弊端，所以众多Nosql数据库才会有兴起。

起源时间：“NoSQL”一词，源于 2009 年 6 月 11 日在旧金山举行的一场技术聚会（meetup）。

定义：如果说要一个定义的话，我理解为“开源分布式的非关系型数据库”。

NoSQL开发原因始于大数据时代的到来，Web技术的进步让我们这个时代的网站的数据量急剧增加，于是就要应用到集群，而关系型数据库无法在集群中高效运行。

原因如下：

（1）关系型数据库开发时就不是设计给集群用的。 Oracle RAC 或 Microsoft SQL Server 在集群上使用会使部分功能无法使用，如事务，一致性等，而且查询效率会变低。

（2）更坑爹的就是许可费，。商用的关系型数据库通常按单台服务器计费，所以在集群中使用会非常贵。(对于这一点要说明，NoSQL的众多成员都是开源的，这也是它的优点与特征之一。)

（3）关系型数据库的迁移很麻烦，比如我们现在的QQ库。

所以就促使了创新，促使了NoSQL的开发， 各种 NoSQL 数据库的共同特性是：

■ 不使用关系模型

■ 在集群中运行良好

■ 开源

■ 适用于 21 世纪的互联网公司

■ 无模式

用直白的话说的话就是，支持大数据，支持集群，省钱，速度还快，而且比关系型数据库还要安全一些，这也是我认为它会在以后越来越火的原因。

它最大的特征就是：NoSQL 数据库不使用 SQL，即结构化查询语言。

PS:  一旦想到SQL，我就想到了注入，如果不使用SQL，那么注入攻击是不是也就不复杂存在了呢？究竟是不是呢？
Nosql的种类。

主要包括：键值数据库　文档数据库 列族数据库  图数据库。

键值数据库：存放会话信息，用户配置信息，购物车数据等；

文档数据库：事件记录，内容管理系统及博客平台，网站分析与实时分析，电子商务应用程序等；

列族数据库(大表格式数据模型)：事件记录，内容管理系统与博客平台，计数器，限期使用等；

图数据库：互联数据，安排运输路线、分派货物和基于位置的服务，搜索引擎，推荐引擎等。
Nosql实例

为了更直观的了解NoSQL,小菜就自己搭建了一个 键值数据库，来分享下心得。

数据库：Couchbase

类型：键值数据库

版本：2.2.0-821-rel

搭建完成后效果图：

这个NoSQL数据库使用 Web 8091 端口 作为控制台，很方便。

下面显示键值数据库：

库内储存的值：

值内的内容：

从上面的例子中我们可以看出，它并没关系数据库所谓的表结构，而是直接存储的key值，而且存储中的东西很多，这种存储在 <NoSQL精粹> 一书中被称为聚合，没有固定的内容格式，这就是所谓的无模式。将数据的节点数降到最低，更加适合集群的使用。

如果说关系数据库的存储时一个表的数据是一个人的部分属性，多个表组成一个人的话，那么Nosql就更像是一次把一个人保存进去，包括这个人的身高属性等等存在同一个key内，数据比之关系型数据库更加独立化。

下面我们来进行一个程序控制Couchbase 的实例，源码如下：

1. namespace MemBaseApp

2. {

3. class Program

4. {

5. static void Main(string[] args)

6. {

7. //配置服务器

8. var mbcc = new CouchbaseClientConfiguration();

9. //设置各种超时时间

10. mbcc.SocketPool.ReceiveTimeout = new TimeSpan(0, 0, 2);

11. mbcc.SocketPool.ConnectionTimeout = new TimeSpan(0, 0, 4);

12. mbcc.SocketPool.DeadTimeout = new TimeSpan(0, 0, 10);

13. //使用默认的数据库

14. mbcc.Urls.Add(new Uri(“http://127.0.0.1:8091/pools/default”));

15. //建立一个Client，装入Client的配置

16. var client = new CouchbaseClient(mbcc);

17. //添加一条数据

18. var item = client.Cas(StoreMode.Add, “new1″, “Hello World! I am guoker,i am 1,测试文字”);

19. var item2 = client.Cas(StoreMode.Add, “new2″, “Hello World! I am guoker,i am 2″);

20. var item3 = client.Cas(StoreMode.Add, “new3″, “Hello World! I am guoker,i am 3″);

21. //获取刚添加的数据

22. Console.WriteLine(client.Get(“new1″));

23. Console.WriteLine(“完成！”);

24. Console.ReadLine();

25. }

26. }

27. }

运行效果：

大家可以看到源码里面完全没有SQL语句，只是调用了数据库提供的dll，就完成了插入与读取的功能，这也说明了NoSQL 确实无法像原来关系数据库那样用 结构化查询语言 进行注入。

NoSQL真的无法注入吗？

没结构化查询语言就无法注入吗？不，凡事没有绝对，前几天翻墙时就找到了一些相关的资料，通过程序所使用程序语言来对NoSQL的一些注入，javaScript、php、.net、java等语言因为可以直接对数据的调用而成为新的注入语言。

世上没有完美的程序语言,总会有虫子的存在，就像有光，就会有阴影。注入安全必将永远存在于数据库的首要安全行列。

NoSQL的其它缺点与前景：

本人测试漏洞：因为依旧拥有登录功能，弱口令与端口爆破依旧会存在。

现在的NoSQL 还没有一个统一的标准，鱼龙混杂，而且各类太多，在使用上还有很多未知BUG，而且应用还是很少，所以关系型数据库依旧占有很大的市场，它就像个未成年的孩子一样等待人们的开发和更新维护，不过我相信随着大数据时代的进步，它总有一天会长大的。

大数据时代

甲：喂，前两天你去上海了？
乙：嗯，你怎么知道的？
甲：你说说不是有定位吗？
乙：靠，那我老婆不是也知道了。
甲：你不会。。。

上面的对话中的事大概在现在生活中并不少见，我们的隐私不知不觉中就会被泄露，不管你想不想被人知道。

在大数据时代，人们的一举一动都被互联网所监控，除非你是一个与世隔绝的野人，否则就不可能不被监控，至于那些信息被谁所拥有，它是否该拥有，被用来干嘛，我们一无所知。。。

我们是可悲的，也是幸运的，我们幸运的出生在了足不出户观天下，千里传音的时代，拥有着历史上从未出现过的文明盛世，又可悲的丢掉了所有的隐私。

信息安全的保护重于泰山，而大多的人们还不太明白这一道理，这也许会造成很多的悲剧，几乎可以预见。

在一部电影《网络末日战》中，黑客掌握了可怜受害者的几乎所有信息，银行卡，身份证件，学籍，户口，甚至知道他女儿的信息，以至于他只能接受黑客的所有安排，听从黑客指挥，这也许就是数据被恶意使用的实例。

为了数据的安全我们可以做什么呢？

数据正在被各种泄露，每天，每夜，每分钟。。。设想一下，腾讯的数据库如果可以任意查看（不是没有可能，支付宝都泄了，何况腾讯呢。），每个人的QQ，姓名，电话，住址，常用网站，出行日志，性格，心情等等，anything,都被记录下来的话，是不是就可以预测出这个人未来几天的事呢？

我们通过心情与日志的分析，得知行程，分析IP得知现住地，电话，聊天记录与密码分析，得到他的常用密码，是不是就和上面一样可以控制这个人呢？不听话就把他钱全转到红十字会啊，有木有，太邪恶了。

而这次QQ群数据的泄露，如此简单，让人不得不对现在的网络心寒。

更别提以前那些多少万，千万的数据了，不是黑客们太聪明，是大家对数据的太不重视了，以至于，这些信息的外泄。

其实大数据有很多用处的，以庞大的数据作为支撑，我们就可以预测出很多未来，甚至可以用来建造一个世界也说不定呢。

如果我们掌握足够多的信息，里面包含了地图，天气，新闻，身份，性格，财富，然后以某种规律运行，是不是就可以虚拟一个世界？是不是就可以预知未来呢？再如果，我们先是足够的物理支持，我们能不能建造一个属于自己的世界呢？

也许是我想多了，可是谁又知道我们是不是NPC，在玩地球on line呢。。。这个世界难道不像一个游戏吗？各种规则，力学，元素周期表等等都表明了我们在一个已经规定好了的空间中生存。好无聊啊。

自从一个外国人看了中国易经，造成电脑后，世界都因此翻天覆地了，我们懂了好多东西，也许电脑就是我们打开未来的钥匙，只用程序员能找到规律，改变世界吧，哈哈，最近看电影看多了，欢迎大家吐嘈。。
。