首页新闻找找看学习计划

10亿记录的分区讨论,高手进

1
悬赏园豆:5 [已解决问题] 解决于 2014-10-10 09:33

我们做的是一个问卷调查系统,每天新增的数据量在300万~500万(答卷表),总数据量已经接近10亿。

答卷表结构:

QID 问卷ID

JID  参与ID

TID 题目ID

AID 答案ID

已经用sql server针对QID做了分区,但是服务器还是有点扛不住。

因为数据经常需要做统计分析(统计每个选项被选择了多少次),同时又会被不断的写入。

所以考虑将历史数据转移到另一个服务器上。

暂定的方案是每天晚上执行作业,将3个月以前的记录移动到历史库里面。(历史库基本上用于统计分析)

后续会考虑用sql server 发布与订阅的方式来进行读写分离

不知道大家有没有更好的方案?

Ray Wu的主页 Ray Wu | 初学一级 | 园豆:5
提问于:2012-05-22 10:02
< >
分享
最佳答案
0

建议用SQL Server Profiler监测一下竟然是哪里扛不住,再定夺。

收获园豆:5
dudu | 高人七级 |园豆:39222 | 2012-05-22 11:23

已经使用过sql server profiler监测过 现在就是进行统计分析的时候比较耗时。我打算将统计分析的结果缓存到磁盘去。

Ray Wu | 园豆:5 (初学一级) | 2012-05-22 14:34

@Ray Wu: 根据“进行统计分析”的SQL的执行计划,看是否可以通过优化索引解决问题

dudu | 园豆:39222 (高人七级) | 2012-05-22 16:01

@dudu: 索引没办法再优化了 我每个查询都使用到了聚集索引 用的是聚焦索引查找。

主要是数据量太大的原因,有的问卷有5万份答卷,这样如果问卷是20道题的话,相当于有100万条记录,需要对这100万条记录进行统计分析。再加上如果数据库表本身接近10亿的量的话,效率也会受影响

Ray Wu | 园豆:5 (初学一级) | 2012-05-23 09:12

@Ray Wu: 

  • 在哪个字段建立聚集索引也要实际查询情况仔细斟酌。
  • 使用了聚集索引并不一定是最优的,在覆盖索引上直接查找(没有书签查找)比聚集索引查找更快。
  • 如果能够通过某个条件通过聚集索引一下子从10亿数据中筛选出这100万记录,性能也会有明显改善。
dudu | 园豆:39222 (高人七级) | 2012-05-23 10:21

@dudu: 

我建立的聚集索引就是QID JID TID AID这4个字段。因为从答卷的插入来说 这4个字段也是排序好的。

另外,我之前看了DUDU你写了一篇关于SQL SERVER 2008 复制的文章(replication),不知道效果如何?

Ray Wu | 园豆:5 (初学一级) | 2012-05-23 15:08

@Ray Wu: 感觉聚集索引设计有问题,数据库复制解决不了这个问题。如果统计分析是根据时间来进行的,建议把聚集索引建在时间字段上。

dudu | 园豆:39222 (高人七级) | 2012-05-23 15:39

@dudu: 统计分析是根据问卷ID来进行的。下面是一个示例语句:

select qid,aid,count(jid) as [Count] from [QAnswer]
where QID = @QID
group by [qid],[aid]
order by qid,aid asc

得到的结果可以参考:http://www.sojump.com/report/1419465.aspx

Ray Wu | 园豆:5 (初学一级) | 2012-05-23 16:42

@Ray Wu: [QAnswer] 的聚集索引是什么?

dudu | 园豆:39222 (高人七级) | 2012-05-23 17:44

@dudu: 是这个表的所有4个字段,QID,JID,TID,AID

前面的语句弄错了,应该是:

select tid,aid,count(jid) as [Count] from [QAnswer] 

where QID = @QID group by [tid],[aid] 

order by tid,aid asc

这个是相当于获取一个问卷(QID)的所有题目(TID)的所有选项(AID)的被选择次数。

一个问卷可能会有20多道题目,会有上千个人填写(jid)。需要每个选项的被选择次数

Ray Wu | 园豆:5 (初学一级) | 2012-05-23 20:51

@Ray Wu: 建议另外建一张表,加两个字段aid, JidCount,在用户填写时更新这张表

dudu | 园豆:39222 (高人七级) | 2012-05-23 21:14

@dudu: 嗯 我现在的做法是在用户查询的时候将结果缓存到XML文件中。一般1个月以前的问卷都不会再有新的答卷。所以缓存的结果基本上都是有效的。

Ray Wu | 园豆:5 (初学一级) | 2012-05-24 14:58
其他回答(2)
0

没搞过这么大的数据量,如果数据量这么大的话,是不是该考虑非关系型数据库了(NOSql)?关注中。

。! | 园豆:181 (初学一级) | 2012-05-22 11:22
0

建议在问卷表中添加冗余字段,用来存储每个选项被选中的次数(可使用XML方式存储),这样每次统计时只需要从问卷表(也就是说几有几十万条数据中)进行XML解析编译了。而SQL Server2005和oracle 都提供了很好的XML解析功能

ReadQi | 园豆:311 (菜鸟二级) | 2012-05-23 11:30

嗯 我们已经做了将统计结果保存为XML文件的操作了。 不过历史库还是要分离出去。

支持(0) 反对(0) Ray Wu | 园豆:5 (初学一级) | 2012-05-23 15:03
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册