QQ群關系庫查詢解決方案(上)

正則室 2015-04-14

展開全文

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

大家都知道，前陣QQ群關系數據庫泄露了，解壓后90多個G的數據，涉及7000多萬個QQ群的資料瞬間沒有隱私。

如果只是幾百幾千條數據，可能根本用不到解決方案，但是對于這種千萬到億級別的查詢，還是需要寫一下的，給大家提供技術參考和學習。欠缺的地方希望大家指出，文章分上下兩篇，這是上篇。

先介紹下數據庫，MSSQL(SQL server)，版本隨意，系統當然是Windows，我用的2008 R2。

這個數據庫其實是很早以前通過騰訊群接口遍歷出來的數據，數據庫共24.5G。共有兩個版本，一個是數據商人賣的帶密碼的7z版本，另一個是rar版本，外面被封的就是7z的，兩個版本網上其實都有，出于隱私考慮，不提供下載地址。

本文作者：gcz1992

論壇地址：http://bbs./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

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 的實例，源碼如下：

namespace MemBaseApp
{
class Program
{
static void Main(string[] args)
{
//配置服務器
var mbcc = new CouchbaseClientConfiguration();
//設置各種超時時間
mbcc.SocketPool.ReceiveTimeout = new TimeSpan(0, 0, 2);
mbcc.SocketPool.ConnectionTimeout = new TimeSpan(0, 0, 4);
mbcc.SocketPool.DeadTimeout = new TimeSpan(0, 0, 10);
//使用默認的數據庫
mbcc.Urls.Add(new Uri(“http://127.0.0.1:8091/pools/default”));
//建立一個Client，裝入Client的配置
var client = new CouchbaseClient(mbcc);
//添加一條數據
var item = client.Cas(StoreMode.Add, “new1″, “Hello World! I am guoker,i am 1,測試文字”);
var item2 = client.Cas(StoreMode.Add, “new2″, “Hello World! I am guoker,i am 2″);
var item3 = client.Cas(StoreMode.Add, “new3″, “Hello World! I am guoker,i am 3″);
//獲取剛添加的數據
Console.WriteLine(client.Get(“new1″));
Console.WriteLine(“完成！”);
Console.ReadLine();
}
}
}

運行效果：

大家可以看到源碼里面完全沒有SQL語句，只是調用了數據庫提供的dll，就完成了插入與讀取的功能，這也說明了NoSQL 確實無法像原來關系數據庫那樣用結構化查詢語言進行注入。

NoSQL真的無法注入嗎？

沒結構化查詢語言就無法注入嗎？不，凡事沒有絕對，前幾天翻墻時就找到了一些相關的資料，通過程序所使用程序語言來對NoSQL的一些注入，javaScript、php、.net、java等語言因為可以直接對數據的調用而成為新的注入語言。

世上沒有完美的程序語言,總會有蟲子的存在，就像有光，就會有陰影。注入安全必將永遠存在于數據庫的首要安全行列。

NoSQL的其它缺點與前景：

本人測試漏洞：因為依舊擁有登錄功能，弱口令與端口爆破依舊會存在。

現在的NoSQL 還沒有一個統一的標準，魚龍混雜，而且各類太多，在使用上還有很多未知BUG，而且應用還是很少，所以關系型數據庫依舊占有很大的市場，它就像個未成年的孩子一樣等待人們的開發和更新維護，不過我相信隨著大數據時代的進步，它總有一天會長大的。

大數據時代

甲：喂，前兩天你去上海了？
乙：嗯，你怎么知道的？
甲：你說說不是有定位嗎？
乙：靠，那我老婆不是也知道了。
甲：你不會。。。

上面的對話中的事大概在現在生活中并不少見，我們的隱私不知不覺中就會被泄露，不管你想不想被人知道。

在大數據時代，人們的一舉一動都被互聯網所監控，除非你是一個與世隔絕的野人，否則就不可能不被監控，至于那些信息被誰所擁有，它是否該擁有，被用來干嘛，我們一無所知。。。

我們是可悲的，也是幸運的，我們幸運的出生在了足不出戶觀天下，千里傳音的時代，擁有著歷史上從未出現過的文明盛世，又可悲的丟掉了所有的隱私。

信息安全的保護重于泰山，而大多的人們還不太明白這一道理，這也許會造成很多的悲劇，幾乎可以預見。

在一部電影《網絡末日戰》中，黑客掌握了可憐受害者的幾乎所有信息，銀行卡，身份證件，學籍，戶口，甚至知道他女兒的信息，以至于他只能接受黑客的所有安排，聽從黑客指揮，這也許就是數據被惡意使用的實例。

為了數據的安全我們可以做什么呢？

數據正在被各種泄露，每天，每夜，每分鐘。。。設想一下，騰訊的數據庫如果可以任意查看（不是沒有可能，支付寶都泄了，何況騰訊呢。），每個人的QQ，姓名，電話，住址，常用網站，出行日志，性格，心情等等，anything,都被記錄下來的話，是不是就可以預測出這個人未來幾天的事呢？

我們通過心情與日志的分析，得知行程，分析IP得知現住地，電話，聊天記錄與密碼分析，得到他的常用密碼，是不是就和上面一樣可以控制這個人呢？不聽話就把他錢全轉到紅十字會啊，有木有，太邪惡了。

而這次QQ群數據的泄露，如此簡單，讓人不得不對現在的網絡心寒。

更別提以前那些多少萬，千萬的數據了，不是黑客們太聰明，是大家對數據的太不重視了，以至于，這些信息的外泄。

其實大數據有很多用處的，以龐大的數據作為支撐，我們就可以預測出很多未來，甚至可以用來建造一個世界也說不定呢。

如果我們掌握足夠多的信息，里面包含了地圖，天氣，新聞，身份，性格，財富，然后以某種規律運行，是不是就可以虛擬一個世界？是不是就可以預知未來呢？再如果，我們先是足夠的物理支持，我們能不能建造一個屬于自己的世界呢？

也許是我想多了，可是誰又知道我們是不是NPC，在玩地球on line呢。。。這個世界難道不像一個游戲嗎？各種規則，力學，元素周期表等等都表明了我們在一個已經規定好了的空間中生存。好無聊啊。

自從一個外國人看了中國易經，造成電腦后，世界都因此翻天覆地了，我們懂了好多東西，也許電腦就是我們打開未來的鑰匙，只用程序員能找到規律，改變世界吧，哈哈，最近看電影看多了，歡迎大家吐嘈。。
。

本站是提供個人知識管理的網絡存儲空間，所有內容均由用戶發布，不代表本站觀點。請注意甄別內容中的聯系方式、誘導購買等信息，謹防詐騙。如發現有害或侵權內容，請點擊一鍵舉報。