匹配csdn用户数据库与官方用户的重合度

背景：

     CSDN用户数据泄漏导致官方用户的安全受到了严重的威胁，故希望能把两个站的用户数据做个对比，把其中重叠部分的用户筛选出来，并通知相应用户及时修改密码，确保风险将至最低

过程：

     1、获取csdn的用户数据库导入本地

         试用editplus打开提示内存不足，没找到办法，同事的linux下查看了一下，基本的格式如下：

          

         用户名 # 密码 # 邮箱

      用户名 # 密码 # 邮箱

     相应数据结构：

     

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打开文件是写入缓存的，但是实践证明速度很快，应该是没有写入缓存,以下为导入数据的代码

<?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);
?>

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

 

<?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);

 为了搞清楚有那些因素对导入数据的效率产生影响，故根据不同的设置进行了测试

CSDN用户总数据 6428600

当$perpage=500；导入后数据：5,902,000；数据丢失526600 丢失率：8%；数据表引擎：MyISAM；索引：有；总耗时 ：15分钟

当$perpage=200，导入后数据总数：6,210,200；数据丢失：218400；丢失率：3.3%；数据表引擎： MYISAM ；索引：有；总耗时：30分钟

当$perpage=200，导入后数据总数：6,210,200；数据丢失：218400；丢失率：3.3%；数据表引擎：INNODB；索引：有；总耗时：65分钟

当$perpage=200，导入后数据总数：6,210,200；数据丢失：218400；丢失率：3.3%；数据表引擎：MYISAM；索引：无；总耗时：14分钟(数据导入完毕后单独再建索引)

当$perpage=50，导入后数据总数：6,371,200；数据丢失：57400，丢失率：0.8%；数据表引擎：MYISAM；索引：无：总耗时：20分钟

根据以上情况总结如下：

 1、先导入数据后加索引的效率要比先加索引后导入数据的高一倍

 2、InnoDB 在单进程数据插入上的效率要比MYISAM低很多

 3、当perpage=50的情况下数据丢失率在1%以下

  

因为通过浏览器执行会有超时的问题，而且效率地下，故通过命令行方式运行，此过程中遇到一点小麻烦耽搁了不少时间

     起初我执行如下代码：

     php.exe E:\usr\www\importcsdndb.php

     但是一直报错：call to undefined function mysql_connect

     折腾发现没有载入php.ini

     正确代码为：

     php.exe -c E:/usr/local/apache2/php.ini importcsdndb.php

     

2、导入需要匹配的用户数据数据至本地

     命令行进入msyql（不会的自己百度）

     然后执行：mysql>source C:/Users/zhudong/Desktop/userdb.sql

 

 

3、对比筛选用户

      对比程序写好了，切记在命令行下运行：

<?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;
}
?>

 

您看到的以上的代码是非常蹩脚的，因为其效率特别低 ，几百万的数据，要跑10多个小时，怎么能忘记连表查询这么基本的东西呢，以下为修正后的方法

$link = mysql_connect('localhost', 'root', 'admin', true);
mysql_select_db('csdn',$link);
$handle_username = fopen("E:/records_username.txt","a");
while($uid<2181106) {//此处的数字为要对比用户库的最大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;
}
?>

 

总对比时间25分钟，相比较之前10多个小时的执行真是大有提升

总重名用户：34175

占总会员比例：1.7%

 

1.7%的重名用户还是挺严重的，希望本文对各位站长对比出本站的用户有所帮助