大表统计效率问题

Question

现在有一个7Gtxt文件，里面都是短信和彩信的记录，9000多w条记录。我已经把它们导入到数据库中，现在有一个表是里面的用户(用户数100W)，需要统计每个用户和多少个不同的人发过短信和彩信，发送彩信的条数，短信的条数。
表有如下结构
_id sendNbr(发送的号码) recNbr(接收的号码) type(短信类别，1为短信，2为彩信)
我是在用户的表里先获取一个号码，然后在短信记录的表里进行统计，但这样效率很低很低。请问有什么快速的方法？要不要把数据导入到数据库，还是直接进行文件操作？

Answer 1

用数据库，用group by 实现就好了
要注意的是：
1）因为可能不是每一个用户都有发过短信的， 短信记录表中可能没有这个人的记录。所以要使用外连接；
2） 统计是不同的人，所以要排除重复。

Answer 2

9Gtxt 数据量已经上亿了，建议你使用hadoop+hive 去处理。
附上hive处理语句CREATE EXTERNAL TABLE t_user_mobile_info
(
id string,
sendNbr string,
recNbr string,
type string
)
LOCATION '\path\9G.txt';select sum(if(type = '1',num,0)) as smsNum,sum(if(type = '2',num,0)) as mmsNum,sendNbr from (
select count(distinct recNbr) as num ,sendNbr,type from t_user_mobile_info where sendNbr is not null and recNbr is not null and type is not null group by sendNbr,type ) a group by sendNbr;下面的查询语句直接在oracle里应该也是可以直接运行的。

Answer 3

最效率当然是hadoop+hive了。但公司会为了你这么一个需求去搞一套环境么！用传统的数据库吧！

Answer 4

这么大，用临时表分步搞吧！oracle开多线程，1,2天就可以跑出来

Answer 5

1-2天没那么夸张吧。
我的实际经验是4000w行的胖表做gruop统计也就十几分钟的时间。全表扫描，如果需要的字段都在某个索引里做索引扫描只要几十秒。（相当于瘦表了）lz这个表还是很瘦的。IBM3850 2cup 32G 
如果直接拿9000w和100w关联不管是嵌套查询 hash join 还是排序联合都很慢。
其实应该先对9000w的单独做gruop by 将行数降下来做一个临时表或者子查询table1。然后做一个百万级的关联。
关键是你要的数据是
发件人 收件人1 彩信条数 短信条数
发件人 收件人2 彩信条数 短信条数
还是
发件人 收件人数 彩信条数 短信条数如果是前者那么table1的行数还是很多。
如果是后者 tables1的行数能在一千万以下就很快了

Answer 6

核心思想其实是虽然9000w的大表 其实我们最终要的是100w行的小的结果集。如果直接管理，不管处理器怎么优化实际所做的工作都是把他扩充为一个9000w行 宽度=t1+t2的大的结果集来处理的，并且至少做了9000w次的匹配。这还是最好的情况，实际上肯定比这慢多了。如果先把做9000w的gruop by 缩行。最差只是做了9000w的全扫描+hash gruop by 实际上如果有合适的索引其实做的是做的是索引全扫描。只读一次，分段做范围扫描，省了gruop的时间我的实际经验是2000w关联20w类似lz的要求。直接写3000s，子查询89s。建立合适的索引后20s。最神奇的是oracle。
去看执行计划
直接用关联执行计划是两个全表扫描。用时3000s。
写子查询是先group by 全索引扫描建view 然后hash join 89s
然后在执行直接关联的语句，居然也优化成和子查询一样的执行计划了。也是89s
最后建立合适的索引 20s。