一直都是在折腾万级别的小小数据库,不知道索引、数据类型等的不同会对效率有多大影响。最近不是密码 泄露吗?就下了个,导入mysql数据库,共两千多万条记录,只留密码字段,其他字段全部删除,进行select、insert等测试,有了索引select的效率明显不同,但在去重复时遇到难题。方法一:
CREATE TABLE newtable SELECT DISTINCT pwd FROM oldtable
这种方式看起来效率最高,但运行时直接把机器拖死,内存一会儿就用完了。方法二:
逐条获取再删除重复(每次提取$num条记录,我的$num=50)
  $result = mysql_query("SELECT MIN(id), pwd FROM tablename WHERE id BETWEEN  $id AND $num GROUP BY pwd");
  while($row = mysql_fetch_row($result)){
    mysql_query("DELETE FROM tablename WHERE id>$row[0] AND pwd='$row[1]'");
  }
  $id += $num;
再通过地址栏或cookie等传递$id,效率太低,处理了100分钟,才删除了30多万条重复请问我应该怎么做,效率才会更高?谢谢

解决方案 »

  1.   

    CREATE TABLE mypwd (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
     pwd VARCHAR(14) NOT NULL DEFAULT '',
     PRIMARY KEY (id)
    )ENGINE=MYISAM, CHARSET='utf8'INSERT INTO mypwd(pwd) SELECT DISTINCT pwd FROM oldtable用的这种方法,oldtable中pwd有索引, 2100万条记录,唯一值1200多万条,110秒就完成了不知道还有没有别的好方法…………
      

  2.   

    创建临时表方法好
    之前一般建议别人这样操作,但不一定能听进去,小数据量倒无所谓
    http://topic.csdn.net/u/20111225/22/7cabedc3-5e9e-42b3-b05b-153ba5a5a67f.html
    操作时候占资源是必须的,,不可避免。。除非你乐意慢慢等待
      

  3.   

    2100w,不知道加unique效率如何,你可试下alter ignore table mypwd add unique(pwd);
    alter table mypwd drop index pwd;
      

  4.   

    用临时表吧。create temporary table ....
      

  5.   

    试试:新建表,设定唯一字段。
    导出sql文件。 
    重新source导入.
      

  6.   

    CREATE TABLE mypwd(
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      pwd VARCHAR(14) NOT NULL DEFAULT '',
      PRIMARY KEY (id),
      UNIQUE KEY pwd(pwd)
    )ENGINE=MYISAM, CHARSET='utf8'INSERT INTO mypwd(pwd) SELECT DISTINCT pwd FROM oldtable采用这种方式,从已简单处理过的1300多W的表中复制出DISTINCT pwd 1200多W条到mypwd,用时11分39秒,E5400的CPU占用率低于60%,内存占用也很少。
    如果不给pwd字段建索引,仅用110秒就复制完,再给pwd字段创建UNIQUE KEY用时9分34秒。
    所以用这种方式还是不错的
      

  7.   

    你可以建唯一键。不要索引。 重复直接报错忽略。select内存不够进,仍要存盘。 而且有distinct. 还要对比重复。 应没有source快。
      

  8.   

    请看我在7楼的回复,如果不给pwd字段建索引,7楼的效率就非常高了,110秒处理完。是在SQLyog中实现的
      

  9.   

    其实呢,可以这样
    新建一B表,用于存放从A表里面筛选出来的数据从A表当中每次获取10000条数据,维护两个数组,一个插入数组,一个重复的数组循环处理10000条数据,如果每次处理时,先检测当前密码是否存在于重复数组,存在,则执行下一条数据(因为已经存在,则不需要入B表了),不存在,则查找当前数据是否存在于B表当中(通过SQL找,在B表当中用PWD建索引),如存在B表当中,则直接存在入重复数组中,如果不存在于B表当中,则直接入插入的数组,这样可以达到批量的插入数据,如果再要快点,可以先把这些数据入文本,再在MYSQL命令行下直接导入即可,速度很快.这是我以前的一些经验,最好放到命令行下跑,不过PHP在命令行下的性能真是不咋的,但完成这部分简单的操作还是不成问题的.这样还有一个好处就是,A表只需要被查找,如果你还进行删除的时候,则这个表的索引会被重建,这样多少会影响你的效率,还有,你之前做删除以及插入不知道你是否有采用批量操作.一条一条插入效率肯定也是会受影响的
      

  10.   


    你可以试试直接 source 直接进入和你select into的速度比较。 
      

  11.   


    请教11楼的 fxs_2008 ,你说的“直接source ”是什么意思?我是新手,麻烦说的稍微详细一点,谢谢
      

  12.   


    sorry, 我试了一下。 你的方法应是最快的。 
      

  13.   

    谢谢 fax_2008 的关注哦。 你说的source是 backup table AS sql dump 吗?
      

  14.   

    今天又试了一下索引对update的影响
    昨天第一次测试表结构如下:
    CREAT TABLE mypwd(
       id INT UNSIGNED NOT NULL AUTO_INCREMENT,
       pwd VARCHAR(14) NOT NULL DEFAULT '',
       pwdmd5 CHAR(32) NOT NULL DEFAULT '',
       PRIMARY KEY (id),
       KEY pwd(pwd),
       KEY pwdmd5(pwdmd5)
    )ENGINE=MYISAM, CHARSET='UTF8'
    然后在php中每次更新5000条pwdmd5,每次15秒上下,如果每次更新条数太多,需要时间太长
    UPDATE mypwd SET pwdmd5=MD5(pwd) WHERE id between 1 and 5000
    ......
    耗时大于180分钟:(
    同时在SQLyog中 SELECT * FROM mypwd WHERE pwd='admin' 耗时44毫秒,瞬间查出啊
    今天进行第二次测试,表结构:
    CREATE TABLE mypwd(
       id INT UNSIGNED NOT NULL AUTO_INCREMENT,
       pwd VARCHAR(14) NOT NULL DEFAULT '',
       pwdmd5 CHAR(32) NOT NULL DEFAULT '',
       PRIMARY KEY (id)
    )ENGINE=MYISAM, CHARSET='UTF8'
    然后继续在php中每次更新pwdmd5,这次不是5000而是10万条,每次仅需要两秒多点
    UPDATE mypwd SET pwdmd5=MD5(pwd) WHERE id between 1 and 100000
    ......
    耗时617秒吧:)
    这个时候再 SELECT * FROM mypwd WHERE pwd='admin' 耗时3分22秒,查出第7166451条记录为pwd='admin'。因为没有索引,需要从第一条记录查到最后一条,又限定了一下查询结果:
    SELECT * FROM mypwd WHERE pwd='admin' LIMIT 1 耗时1分58秒,查出第7166451条记录为pwd='admin'可见索引对update和select效率的影响现在想问一下:实际应用中,我们不可能一会儿建立索引,一会儿删除索引,那么在一个频繁读写的大表中,怎么样才能做到update和select效率的兼顾?MYISAM和INNODE对此有多大影响? 暂不讨论非频繁读写哦(非频繁相对简单些)
      

  15.   

    MYISAM和INNODB,上面最后一行写错了