关于mysql大量数据去重和索引大数据的问题

总数据量20亿，只有两个列，id和name

id和name是多对多的关系，一个id可以有很多name，一个name可能会被很多id用

比如

1 asd
1 qwe
2 fgh
2 asd
3 ljk

这20亿条数据分了10个表，每个表2亿，每个表分别对id和name做了两个普通的btree索引。

数据可能在每个表里有重复，也有可能在不同表里有重复。

比如表1可能有2个
1 asd
1 asd

也有可能表1没有重复的
1 asd
但是表2里有

现在要对这20亿数据去重
可以有
1 asd
1 qwe

但是不能有两个
1 asd

数据库服务器24G内存，建了个memory引擎的表，加了unique 索引，从每个表内单独选取数据，用insert ignore插入，然后再写入到硬盘，对每个表内的重复数据进行了去重，2亿差不多能去掉3000-5000w

但是因为内存大小限制，内存里只能同时放下2亿数据。用myisam引擎的表加unique索引去重，跑了1天都没插完2亿数据（数据库放在ssd上）……

第二个问题：

对name列建立索引，里面有中文有英文，gbk编码。假如其中一个name的内容是：

"我们希望 V2EX 能够成为中文世界中氛围最好的社区"

假如我查询关键词是 “中文世界”，想匹配上面的那句话，如果想用上索引的话，是不是只能用full text 索引了？不过我不需要任何分词之类的功能，只要匹配文中任意位置的字符串就行。建full text 索引好像有点慢。

name

asd

索引

17 条回复 • 2016-05-17 19:57:09 +08:00

vibbow

2014-01-29 20:38:51 +08:00

对于问题1：
我能想到的方案是：
对id和name做hash，按照hash排序重新分表，然后去重。

对于问题2，完全没想法。
不过你或许可以看看Twitter的做法。

nilai

2014-01-29 20:39:19 +08:00

楼主是要整理社工库么？

a2z

2014-01-29 20:45:52 +08:00

@nilai
社工库怎么会只有id和name两列……

这分明是从某微博抓来的社交数据

a2z

2014-01-29 20:49:14 +08:00

@vibbow
大概懂你的意思，加一个第三列，md5(concat(id,name))，然后对md5做unique索引。

不过插入到一个表的时候还是会每插入一条就查询一次索引，如果把两个2亿的表合并，还是起码要查2亿次索引……

vibbow

2014-01-29 20:51:25 +08:00

@a2z 你可以再有个临时表啊，这个表只有id和name。
等临时表积攒够一定量的数据了，再统一处理插入到原始数据里。

vibbow

2014-01-29 20:55:20 +08:00

@a2z 你合并那20亿的数据，全部处理一遍是跑不了的了。

还有其实不仅仅可以有第三列，还可以有第四列
其中第三列保存MD5截取前四个字母，转换成integer，第四列再保存完整的MD5
这样可以先快速通过第三列筛选出可能重复的数据，再根据第四列完整对比
可以节约很多查询的。

vibbow

2014-01-29 20:56:00 +08:00

@a2z integer做索引查询，比varchar应该要快很多的吧（不确定，只是感觉应该是这样）

vibbow

2014-01-29 20:56:21 +08:00

总之就是个用空间换时间，还是用时间换空间的问题。

a2z

2014-01-29 21:00:20 +08:00

@vibbow
嗯，理论上如果md5是完美随机的话，只要前8位就够了，碰撞的概率很低。

kingwkb

2014-01-29 23:34:40 +08:00

@a2z 再加上sha1的前8位，基本不会碰撞

rqrq

2014-01-30 15:33:30 +08:00

第一个问题：
一张表2亿数据，加字段就要等半天，还要计算md5，然后再去重的话……
其实可以先把10张表的数据汇总到一张表new_tbl_all（事先加上id+name的普通联合索引）
然后再建一张不加索引的最终表new_tbl_final
再执行以下sql语句
INSERT INTO new_tbl_final (id, name) SELECT id, name FROM new_tbl_all WHERE id <= 五百万 GROUP BY id, name

自己控制这条sql语句，修改id的值就行（五百万的话，大概要执行400次……）
当然作为一只程序员怎么能容忍手动控制，写个类似分页的脚本让它自己跑吧。
最后再往new_tbl_final表加索引（等吧）

说句题外话，分表不是真正的解决性能问题，维护或者需求有变动就带来各种麻烦。
简单几个字段的话，20亿放到一张表mysql也没问题啊，只要索引正确。
即便性能不行，也可以尝试下表分区，再不行就上pgsql，总会有办法的。

第二个问题：
当然是用lucene、sphinx这种全文引擎，便宜量又足。

czheo

2014-01-30 15:52:18 +08:00

第一个问题，lz要不考虑下hadoop

Livid

MOD

2014-01-30 16:53:47 +08:00

这种事情完全只应该用 Hadoop 来做。

a2z

2014-02-02 19:03:14 +08:00

@Livid
@rqrq
多谢各位

去重用的hadoop，单机单实例，12年中的macbook pro 16g内存 256G SSD，20亿数据，分成5个文本文件一共17g，hadoop sort+去重一共只用了一个半小时……

索引用了coreseek，建索引用了2.5小时，查找速度英文在0.05秒内，中英文混合0.5秒以内。

chengxuan

2014-02-11 00:29:35 +08:00

不错学习啦。。最近也在考虑大数据如何存储和查询。。感谢 @a2z

lulu00147

2014-05-28 10:37:58 +08:00

。。。。问的这么隐晦就说你想弄社工库就得了。。。

jianzhichun

2016-05-17 19:57:09 +08:00

@a2z 楼主您好，我也遇到同样的问题，数据量更大些，请问能不能分享一下详细代码哇，谢谢[email protected]