快捷導(dǎo)航

匹配csdn用戶數(shù)據(jù)庫與官方用戶的重合度并將重疊部分的用戶篩選出來

更新時間：2011年12月25日 23:26:00 作者：

CSDN用戶數(shù)據(jù)泄漏導(dǎo)致官方用戶的安全受到了嚴重的威脅，故希望能把兩個站的用戶數(shù)據(jù)做個對比，把其中重疊部分的用戶篩選出來，并通知相應(yīng)用戶及時修改密碼，確保風險將至最低

過程：
1、獲取csdn的用戶數(shù)據(jù)庫導(dǎo)入本地
試用editplus打開提示內(nèi)存不足，沒找到辦法，同事的linux下查看了一下，基本的格式如下：
用戶名 # 密碼 # 郵箱
用戶名 # 密碼 # 郵箱
相應(yīng)數(shù)據(jù)結(jié)構(gòu)：

復(fù)制代碼代碼如下:

 
CREATE TABLE IF NOT EXISTS `csdn_userdb` ( 
　　`id` int(10) NOT NULL auto_increment, 
　　`username` varchar(50) character set gbk NOT NULL, 
　　 `password` varchar(50) character set gbk NOT NULL, 
　　`email` varchar(50) character set gbk NOT NULL, 
　　 PRIMARY KEY (`id`), 
　　KEY `username` (`username`), 
　　KEY `email` (`email`) 
　　) ENGINE=MyISAM DEFAULT CHARSET=gbk AUTO_INCREMENT=1 ; 

一直懷疑fopen打開文件是寫入緩存的，但是實踐證明速度很快，應(yīng)該是沒有寫入緩存,以下為導(dǎo)入數(shù)據(jù)的代碼

復(fù)制代碼代碼如下:

 
<?php 
$link = mysql_connect('localhost', 'root', 'admin', true); 
mysql_select_db('csdn',$link); 
$handle = fopen("C:\Users\zhudong\Desktop\www.csdn.net.sql", "r"); 
while (!feof($handle)){ 
$i++; 
$buffer = fgets($handle); 
list($u,$p,$e) = explode(" # ",$buffer); 
mysql_query("INSERT INTO csdn_userdb(username,password,email) VALUES ('$u','$p','$e')",$link); 
if ($i%1000 == 0) echo $i."\n"; 
} 
fclose($handle); 
?> 

以上代碼效率非常差，故做修改后代碼如下：

復(fù)制代碼代碼如下:

 
<?php 
$link = mysql_connect('localhost', 'root', 'admin', true); 
mysql_select_db('csdn',$link); 
$handle = fopen("C:\Users\zhudong\Desktop\www.csdn.net.sql", "r"); 
$perpage = 50; 
while (!feof($handle)){ 
$i++; 
$buffer = fgets($handle); 
list($u,$p,$e) = explode(" # ",$buffer); 
$insertValue[] = "('$u','$p','$e')"; 
if ($i% $perpage == 0){ $perpage == 0){ 
$instrtValueString = implode(',',$insertValue); 
mysql_query("INSERT INTO csdn_userdb(username,password,email) VALUES $instrtValueString",$link); 
echo $i."\n"; 
$insertValue = array(); 
} 
} 
fclose($handle); 

為了搞清楚有那些因素對導(dǎo)入數(shù)據(jù)的效率產(chǎn)生影響，故根據(jù)不同的設(shè)置進行了測試
CSDN用戶總數(shù)據(jù) 6428600
當$perpage=500；導(dǎo)入后數(shù)據(jù)：5,902,000；數(shù)據(jù)丟失526600 丟失率：8%；數(shù)據(jù)表引擎：MyISAM；索引：有；總耗時：15分鐘
當$perpage=200，導(dǎo)入后數(shù)據(jù)總數(shù)：6,210,200；數(shù)據(jù)丟失：218400；丟失率：3.3%；數(shù)據(jù)表引擎： MYISAM ；索引：有；總耗時：30分鐘
當$perpage=200，導(dǎo)入后數(shù)據(jù)總數(shù)：6,210,200；數(shù)據(jù)丟失：218400；丟失率：3.3%；數(shù)據(jù)表引擎：INNODB；索引：有；總耗時：65分鐘
當$perpage=200，導(dǎo)入后數(shù)據(jù)總數(shù)：6,210,200；數(shù)據(jù)丟失：218400；丟失率：3.3%；數(shù)據(jù)表引擎：MYISAM；索引：無；總耗時：14分鐘(數(shù)據(jù)導(dǎo)入完畢后單獨再建索引)
當$perpage=50，導(dǎo)入后數(shù)據(jù)總數(shù)：6,371,200；數(shù)據(jù)丟失：57400，丟失率：0.8%；數(shù)據(jù)表引擎：MYISAM；索引：無：總耗時：20分鐘
根據(jù)以上情況總結(jié)如下：
1、先導(dǎo)入數(shù)據(jù)后加索引的效率要比先加索引后導(dǎo)入數(shù)據(jù)的高一倍
2、InnoDB 在單進程數(shù)據(jù)插入上的效率要比MYISAM低很多
3、當perpage=50的情況下數(shù)據(jù)丟失率在1%以下

復(fù)制代碼代碼如下:

 
因為通過瀏覽器執(zhí)行會有超時的問題，而且效率地下，故通過命令行方式運行，此過程中遇到一點小麻煩耽擱了不少時間 
起初我執(zhí)行如下代碼： 
php.exe E:\usr\www\importcsdndb.php 
但是一直報錯：call to undefined function mysql_connect 
折騰發(fā)現(xiàn)沒有載入php.ini 
正確代碼為： 
php.exe -c E:/usr/local/apache2/php.ini importcsdndb.php 

2、導(dǎo)入需要匹配的用戶數(shù)據(jù)數(shù)據(jù)至本地
命令行進入msyql（不會的自己百度）
然后執(zhí)行：mysql>source C:/Users/zhudong/Desktop/userdb.sql
3、對比篩選用戶
對比程序?qū)懞昧?，切記在命令行下運行：

復(fù)制代碼代碼如下:

 
<?php 
$link = mysql_connect('localhost', 'root', 'admin', true); 
mysql_select_db('csdn',$link); 
$handle_username = fopen("E:/records_username.txt","a"); 
//$handle_email = fopen("E:/records_email.txt","a"); 
$username_num = $email_num = $uid = 0; 
while ($uid<2181106) { 
$nextuid=$uid+10000; 
$query = mysql_query("SELECT * FROM pw_members WHERE uid>'$uid' AND uid<'$nextuid'"); 
while ($rt = mysql_fetch_array($query,MYSQL_ASSOC)) { 
$username = $rt['username']; 
$email = $rt['email']; 
$query2 = mysql_query("SELECT * FROM scdn_userdb WHERE username='$username' OR email='$email'"); 
while ($rt2 = mysql_fetch_array($query2,MYSQL_ASSOC)) { 
if ($rt['password'] = md5($rt2['password'])) { 
if ($rt2['username'] == $username) { 
$username_num++; 
fwrite($handle_username,'OWN:'.$rt['uid'].'|'.$rt['username'].'|'.$rt['password'].'|'.$rt['email'].' CSDN:'.$rt2['username'].'|'.$rt2['password'].'|'.$rt2['email']."\r\n"); 
echo 'username_num='.$username_num."\r\n"; 
continue; 
} 
/* 
if ($rt2['email'] == $email) { 
$email_num++; 
fwrite($handle_email,'OWN:'.$rt['uid'].'|'.$rt['username'].'|'.$rt['password'].'|'.$rt['email'].' CSDN:'.$rt2['username'].'|'.$rt2['password'].'|'.$rt2['email']."\r\n"); 
echo 'email_num='.$email_num."\r\n"; 
} 
*/ 
} 
} 
mysql_free_result($query2); 
} 
$uid = $nextuid; 
} 
?> 

您看到的以上的代碼是非常蹩腳的，因為其效率特別低，幾百萬的數(shù)據(jù)，要跑10多個小時，怎么能忘記連表查詢這么基本的東西呢，以下為修正后的方法

復(fù)制代碼代碼如下:

 
$link = mysql_connect('localhost', 'root', 'admin', true); 
mysql_select_db('csdn',$link); 
$handle_username = fopen("E:/records_username.txt","a"); 
while($uid<2181106) {//此處的數(shù)字為要對比用戶庫的最大ID 
$nextuid= $uid+10000; 
$query = mysql_query("SELECT m.uid,m.username,m.password,m.email,u.password as csdn_password,u.email as csdn_email FROM own_members m LEFT JOIN csdn_userdb u USING(username) WHERE m.uid>'$uid' AND m.uid<='$nextuid' AND u.username!=''"); 
while ($rt = mysql_fetch_array($query,MYSQL_ASSOC)) { 
if ($rt['password'] == md5($rt['csdn_password'])) { 
$username_num++; 
fwrite($handle_username,'OWN:'.$rt['uid'].'|'.$rt['username'].'|'.$rt['password'].'|'.$rt['email'].' CSDN:'.$rt['username'].'|'.$rt['csdn_password'].'|'.$rt['csdn_email']."\r\n"); 
echo 'username_num='.$username_num."\r\n"; 
} 
} 
$uid = $nextuid; 
echo 'uid='.$uid; 
} 
?> 