[求助] 千万级的表怎么去重复？

Question

一直都是在折腾万级别的小小数据库，不知道索引、数据类型等的不同会对效率有多大影响。最近不是密码 泄露吗？就下了个，导入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多万条重复请问我应该怎么做，效率才会更高？谢谢

Answer 1

另建一表，创建主键，然后直接 insert into 另建一表 select * from oldtable

Answer 2

请问一下，这不是复制oldtable了吗？

Answer 3

不知道你说的是不是下面这样：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

Answer 4

我要做的不是删除字段，而是删除重复的数据。
表中只有id INT NOT NULL AUTO_INCREMENT 和pwd VARCHAR(14)两项
id pwd
 1 123
 2 ABC
 3 456
 4 123
.......
我的意思是删除重复的pwd，如上面的123

Answer 5

先用定制的select <> group by pwd INTO <outfile>
然后再load data into <newtable>呢
只要前边的select能保证速度，那么应该可行。

Answer 6

CREATE TABLE mypwd (
pwd VARCHAR(14) PRIMARY KEY ,
)ENGINE=MYISAM, CHARSET='utf8'INSERT INTO mypwd(pwd) SELECT pwd FROM oldtable

Answer 7

回 iihero ： group by 有点恐怖
SELECT pwd FROM tablename WHERE ID between 1 and 1000000 GROUP BY pwd
group by 1万条记录用时0.033秒，10万条用时2秒，100万条就用了48秒，两千万条时间恐怕就太长了吧

Answer 8

CREATE TABLE mypwd (
pwd VARCHAR(14) PRIMARY KEY ,
)ENGINE=MYISAM, CHARSET='utf8'INSERT INTO mypwd(pwd) SELECT pwd FROM oldtable既然pwd是PRIMARY KEY，那insert时出现重复的话不会出现报错吗？

Answer 9

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最后用的这种方法，1千多万条数据几分钟就OK了，效率还不错吧。不知道有没有更好的方法

Answer 10

INSERT IGNORE INTO mypwd(pwd) SELECT pwd FROM oldtable

Answer 11

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秒。
所以用这种方式还是不错的回 ACMAIN_CHM :
INSERT IGNORE INTO mypwd(pwd) SELECT pwd FROM oldtable
这种方法耗时14分6秒，比
INSERT INTO mypwd(pwd) SELECT DISTINCT pwd FROM oldtable
多了一点点，不知道多出来的时间是不是用在IGNORE上了，谢谢你哦！我上面的方法也是从你这儿学来的。

Answer 12

我的意思是找出一个select语句，效率最高的。
不好，看到上面的回帖，貌似已经有了答案。

Answer 13

今天又试了一下索引对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对此有多大影响？ 暂不讨论非频繁读写哦（非频繁相对简单些）

Answer 14

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

Answer 15

谢谢 iihero 刚才又试了下建表时不要id字段，只要pwd和pwdmd5字段，不建索引
CREATE TABLE mypwd(
  pwd VARCHAR(14) NOT NULL DEFAULT '',
  pwdmd5 CHAR(32) NOT NULL DEFAULT ''
)ENGINE=MYISAM, CHARSET='UTF8'然后 INSERT INTO mypwd SELECT DISTINCT pwd FROM pwd
30秒就搞定，pwd表包含两千多万条记录，唯一值1300多万条,其中pwd字段有索引
然后直接在SQLyog中 UPDATE mypwd SELECT DISTINCT pwd FROM pwd  13分25秒完成SELECT * FROM mypwd WHERE pwd='exp123456' 耗时4秒，在第800万条查出
SELECT * FROM mypwd WHERE pwd='exp123456' LIMIT 1 耗时2秒，在第800万条查出更正一下我在第17楼中说的，第二次测试时的select速度：
这个时候再 SELECT * FROM mypwd WHERE pwd='admin' 耗时3分22秒，查出第7166451条记录为pwd='admin'。因为没有索引，需要从第一条记录查到最后一条，又限定了一下查询结果：
SELECT * FROM mypwd WHERE pwd='admin' LIMIT 1 耗时1分58秒，查出第7166451条记录为pwd='admin'
不知道为什么当时select那么慢，刚才又试，其实没有那么慢，基本跟刚才没有id字段时的速度一样
SELECT * FROM mypwd WHERE pwd='admin' 用时4秒
加LIMIT 1限定，用时2秒