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 发布与订阅的方式来进行读写分离。
不知道大家有没有更好的方案？
最佳答案
0
建议用SQL Server Profiler监测一下竟然是哪里扛不住，再定夺。
收获园豆：5
已经使用过sql server profiler监测过 现在就是进行统计分析的时候比较耗时。我打算将统计分析的结果缓存到磁盘去。
@Ray Wu: 根据“进行统计分析”的SQL的执行计划，看是否可以通过优化索引解决问题
@dudu: 索引没办法再优化了 我每个查询都使用到了聚集索引 用的是聚焦索引查找。
主要是数据量太大的原因，有的问卷有5万份答卷，这样如果问卷是20道题的话，相当于有100万条记录，需要对这100万条记录进行统计分析。再加上如果数据库表本身接近10亿的量的话，效率也会受影响
@Ray Wu:
- 在哪个字段建立聚集索引也要实际查询情况仔细斟酌。
- 使用了聚集索引并不一定是最优的，在覆盖索引上直接查找（没有书签查找）比聚集索引查找更快。
- 如果能够通过某个条件通过聚集索引一下子从10亿数据中筛选出这100万记录，性能也会有明显改善。
@dudu:
我建立的聚集索引就是QID JID TID AID这4个字段。因为从答卷的插入来说 这4个字段也是排序好的。
另外，我之前看了DUDU你写了一篇关于SQL SERVER 2008 复制的文章（replication），不知道效果如何？
@Ray Wu: 感觉聚集索引设计有问题，数据库复制解决不了这个问题。如果统计分析是根据时间来进行的，建议把聚集索引建在时间字段上。
@dudu: 统计分析是根据问卷ID来进行的。下面是一个示例语句：
select qid,aid,count(jid) as [Count] from [QAnswer]
where QID = @QID
group by [qid],[aid]
order by qid,aid asc
@Ray Wu: [QAnswer] 的聚集索引是什么？
@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: 建议另外建一张表，加两个字段aid, JidCount，在用户填写时更新这张表
@dudu: 嗯 我现在的做法是在用户查询的时候将结果缓存到XML文件中。一般1个月以前的问卷都不会再有新的答卷。所以缓存的结果基本上都是有效的。
其他回答(2)
0
没搞过这么大的数据量，如果数据量这么大的话，是不是该考虑非关系型数据库了（NOSql）？关注中。
0
建议在问卷表中添加冗余字段，用来存储每个选项被选中的次数（可使用XML方式存储），这样每次统计时只需要从问卷表（也就是说几有几十万条数据中）进行XML解析编译了。而SQL Server2005和oracle 都提供了很好的XML解析功能
嗯 我们已经做了将统计结果保存为XML文件的操作了。 不过历史库还是要分离出去。
