IT博客汇 | 对QQ群关系数据库的数据挖掘

对QQ群关系数据库的数据挖掘

Terence发表于 2013-12-11 11:47:00

首先声明一点，本人的目的并非是为了去窥探别人的隐私而去做这件事情，而是纯粹以一个数据挖掘的角度来看待它。QQ群关系数据库已经在上个月也就是11月18日被好事者公布在了网络上，立马遭到了网友的疯转，乌云-漏洞报告平台也在第一时间公布了这个漏洞消息。

该QQ群数据库其实是2011年11月的时候被黑客从一个腾讯漏洞网站上脱裤下来的，里面包含了当时所有QQ号的基本个人信息与所加入群的对应关系以及群的一些基本信息，解压出来后数据库总大小超过了90G，用的是SQL Server 2000，以数据分片的形式存储，共有22个库。虽然其中并不包含密码信息，但可以从这里获得某个QQ号的昵称、年龄、性别和所加入的群的号码和群名称等信息。要知道许多人在加入某些重要群的时候所修改的昵称会是自己的真实姓名，所以无意中会使自己的QQ实名化，然后通过分析所加入的群名称，就基本上能判断该用户的读书工作经历乃至整个关系网了，因为毕竟我们当中的大多数已经越来越离不开QQ，生活中的点点滴滴都有可能有意无意的和QQ扯上了些关系。

就像网上所说的，为什么11年的数据库到了现在才被公布出来，说明那些第一时间获得数据的黑客已经对数据进行了价值利用，直到利用完后才把它随便扔到了网络上，对于这个观点，我表示同意，可见这次的泄露事件影响有多么的可怕。

撇开数据泄漏不说，我们把目光转回这个90个G的庞大数据库上，既然木已成舟，对于我们这些后知后觉的人来说，这么多的数据正好可以作为数据挖掘的练手好机会，虽然有悖道义，但只要本着求是的态度，只用于研究，所以在这里，我保证不会公布任何和个人信息有关的数据。

其实对于这个QQ群数据库能挖掘的数据形式并不多，22个库归结下来就两种表：Group表和QunList表。Group表包含了QQ号的个人基本信息和对应加入的QQ群号，QunList表则包含了群的一些基本信息，这两张表都是以群号的递增顺序排列的，Group表共1100张，QunList表共110张，各自平均分散在11个数据库里，共22个数据库。其中Group表并没有被完全填充，Group1是空表，Group970以后的大部分表也是空的，然后QunList表中的群号比Group表中的要多，我猜测可能这两种表并不是在同一时间被脱裤的。以下是表结构：

Group表

QunList表

随后的思路就比较简单了，我查询一个QQ号，然后从所有的Group表中进行查询遍历，再是把查询到的群号分别在对应的QunList表中查询，以获得群名称，以下是自己设计的查询结果表：

CREATE TABLE [dbo].[QQNumInfo] (
[ID] int NOT NULL IDENTITY(1,1) ,
[QQNum] int NOT NULL ,
[Nick] varchar(20) COLLATE Chinese_PRC_CI_AS NULL ,
[Age] int NULL ,
[Gender] int NULL ,
[QunNum] int NULL ,
[QunTitle] varchar(22) COLLATE Chinese_PRC_CI_AS NULL ,
[NotExist] char(1) COLLATE Chinese_PRC_CI_AS NULL ,
CONSTRAINT [PK_QQInfo] PRIMARY KEY ([ID])
)
ON [PRIMARY]
GO
 
CREATE INDEX [INDEX_QQNum] ON [dbo].[QQNumInfo]
([QQNum] ASC)
ON [PRIMARY]
GO
 
CREATE INDEX [INDEX_QunNum] ON [dbo].[QQNumInfo]
([QunNum] ASC)
ON [PRIMARY]
GO

整个查询逻辑采用存储过程语言t-sql来写，传入QQNum这个参数，首先查找结果表中是否存在记录，如果有则直接返回结果集，NotExist列是用来标记经过查找后返回为空集的QQ号，以便在下次查找的时候能立即返回为空；如果QQ号在结果表中不存在，则进入一系列的查询逻辑并最终将结果存入结果表中并返回结果集。以下是存储过程代码：

ALTER PROCEDURE [dbo].[LookupQQNum] @QQ INT
AS
BEGIN
	SET NOCOUNT ON --防止在jdbc里无法返回结果集
	CREATE TABLE #tmp (
		[ID] INT NOT NULL IDENTITY(1,1) ,
		[QQNum] INT NOT NULL ,
		[Nick] VARCHAR(20) COLLATE Chinese_PRC_CI_AS NULL ,
		[Age] INT NULL ,
		[Gender] INT NULL ,
		[QunNum] INT NULL ,
		[QunTitle] VARCHAR(22) COLLATE Chinese_PRC_CI_AS NULL ,
		[NotExist] CHAR(1) COLLATE Chinese_PRC_CI_AS NULL
	)
	INSERT INTO #tmp SELECT QQNum,Nick,Age,Gender,QunNum,QunTitle,NotExist FROM QQNumInfo WHERE QQNum=@QQ
	IF exists(SELECT QQNum FROM #tmp)
	BEGIN
		DECLARE @IsNotExist CHAR(1)
		SELECT TOP 1 @IsNotExist=NotExist FROM #tmp
		IF @IsNotExist IS null
			SELECT ID,QQNum,Nick,Age,Gender,QunNum,QunTitle FROM #tmp
		ELSE
			SELECT ID,QQNum,NotExist FROM #tmp
	END
	ELSE
	BEGIN
		DECLARE @table_num INT
		DECLARE @database_num INT
		SET @table_num=2
		WHILE @table_num<=970
		BEGIN
			SET @database_num=(@table_num-1)/100+1
			EXEC (
				'insert into #tmp(QQNum,Nick,Age,Gender,QunNum) select QQNum,Nick,Age,Gender,QunNum from GroupData'
				+ @database_num + '.dbo.Group' + @table_num + ' where QQNum=' + @QQ
			)
			SET @table_num=@table_num+1
		END
		INSERT INTO #tmp(QQNum,Nick,Age,Gender,QunNum) SELECT QQNum,Nick,Age,Gender,QunNum FROM GroupData11.dbo.Group1001 WHERE QQNum=@QQ
		INSERT INTO #tmp(QQNum,Nick,Age,Gender,QunNum) SELECT QQNum,Nick,Age,Gender,QunNum FROM GroupData11.dbo.Group1002 WHERE QQNum=@QQ
		INSERT INTO #tmp(QQNum,Nick,Age,Gender,QunNum) SELECT QQNum,Nick,Age,Gender,QunNum FROM GroupData11.dbo.Group1003 WHERE QQNum=@QQ
		IF not exists(SELECT QQNum FROM #tmp)
		BEGIN
			INSERT INTO #tmp(QQNum,NotExist) VALUES(@QQ,'1')
			INSERT INTO QQNumInfo(QQNum,NotExist) VALUES(@QQ,'1')
			SELECT ID,QQNum,NotExist FROM #tmp
		END
		ELSE
		BEGIN
			DECLARE cursor_QunNum CURSOR FOR SELECT QunNum FROM #tmp
			DECLARE @temp INT
			DECLARE @QunList_Num INT
			DECLARE @QunInfo_Num INT
			OPEN cursor_QunNum
			FETCH NEXT FROM cursor_QunNum INTO @temp
			WHILE @@FETCH_STATUS=0
			BEGIN
				SET @QunList_Num=(@temp-1)/1000000+1
				SET @QunInfo_Num=(@QunList_Num-1)/10+1
				EXEC (
					'update #tmp set QunTitle=(select Title from QunInfo' + @QunInfo_Num + '.dbo.QunList'
					+ @QunList_Num + ' where QunNum=' + @temp + ') where QunNum=' + @temp
				)
				FETCH NEXT FROM cursor_QunNum INTO @temp
			END
			CLOSE cursor_QunNum
			INSERT INTO QQNumInfo(QQNum,Nick,Age,Gender,QunNum,QunTitle) SELECT QQNum,Nick,Age,Gender,QunNum,QunTitle FROM #tmp
			SELECT ID,QQNum,Nick,Age,Gender,QunNum,QunTitle FROM #tmp
		END
	END
END

存储过程代码量不多，就70行的样子，但毕竟面对的是90个G的海量数据，经过几次测试发现查询一个QQ号平均耗时29分钟，我的测试机配置是台式一代i3双核超线程CPU+2G内存+500G SATA2硬盘，采用的数据库是SQL Server 2008 R2 SP2。所以也只能作为测试用途，这么长的查询时间是谁也受不了的

最后上一张查询结果返回集合的截图吧，当然是经过打码的：

　» 转载请注明来源：Terence的窝 » 《对QQ群关系数据库的数据挖掘》

对QQ群关系数据库的数据挖掘

相关日志