再谈大数据量的数据表设计

shiguibao

个人感觉这种情况需要分区和物化视图结合会比较好

wayne_lau

快速刷新的物化视图＋合适的分区

陌上风轻

物化视图和表分区可以考虑了.另外你也可以考虑在网站的访问量低峰的时候才去一些定时的批处理来统计你要做的工作,此时的访客数据可以插入到其他表,到时候要统计总额数据是用你批处理统计结果加上在批处理阶段在插入表的数据的量结合起来.

[ 本帖最后由 陌上风轻 于 2010-1-6 22:30 编辑 ]

fan0124

快速刷新的物化视图我能不能理解为定时触发运算的JOB+得到计算结果后进行存储的中间表（也就是统计表），这个就是指物化视图

fan0124

newid，你说到中间表弄到独立的库去，那就是基础表和中间表进行分离了是吧。但是这样我对基础表进行的统计，然后统计一次后的数据再到另外的库（假设为B）插入

到中间表中，这样会不会降低性能呢。这两天对表又进行了修改，针对业务需求，发现有几个问题好像需要再进一步改变思路才行，有点不解。

1、如果我把中间表、基础表放到两个独立的库中，（先暂且不考虑分布式的架构，读写分离），如果我对基础表统计好了后，再插入到另外的库中的统计表，性能会不会

影响很大。因为我的中间表有17张表，也就是说每次我要统计都要写入17张表，会不会因为数据多而造成性能的问题。

2、先前也说到过物化视图，包括用快速刷新的物化视图，我之前也是想的每天定时触发JOB来完成计算统计，让结果先冗余起来，甚至每天24小时中每半小时触发一次J

OB来完成统计计算。但是业务需求说是“实时统计”，按照网站的实际访问次数的多少来决定JOB的执行次数

比如：网站A今天一天被访问了1000次，那么我在每次访问结束后就要执行一次JOB来进行网站访问的流量统计，然后数据放到17张中间表中，一天是1000次，意味着

要触发1000次，这样岂不是根本不能用快速刷新的物化视图了，因为不可能是定时触发的。我觉得这样是不是表的设计还需要加上什么策略，我觉得就表结构，基础表加

中间表应该是没什么问题吧，但是觉得好像性能上有很大的考验吧，实时的触发的。

3、在触发JOB进行每次统计时，还需要过滤数据，也就是过滤的条件，目前是有三个

屏蔽的URL、屏蔽的域名、屏蔽的IP段

也就是说每次从基础表进行数据统计到中间表的时候，要从这三个表取出对应的数据，将符合其中的数据过滤掉，这样对查询貌似又有性能上的要求了，通过分区加索引

能解决好吗

4、如果就只有通过条件进行数据的过滤进行统计也还好，但是如果某一天这个过滤条件去掉了，那之前的数据要求要重新统计，那我中间表岂不是每天的数据都要重新

统计了。怎么弄呢中间表。

比如：我今天设置了屏蔽的URL有三个，屏蔽的域名有5个，屏蔽的IP段从某一段到某一段，然后这样每天统计数据（加了这些过滤条件的），20天后，我把这些屏蔽

的设置给去掉了，这样到第21天的时候，统计的数据是不加过滤条件的（因为没有屏蔽的设置了），但是前20天的数据也要在去掉屏蔽设置后重新统计。这意味着我要

对前20天每天的统计数据重新计算了，这样中间表如何设计好呢？性能开销这样也太大了吧。如果是40天，50天，那还了得。

5、我有没有必要加上对每个周进行一次统计的中间表，基于3，4点中的考虑

fan0124

附上我的数据库的设计模型

图中红线的就是统计数据时要加上的过滤条件

除基础表标明的，下面的为中间数据表

newkid

原帖由 fan0124 于 2010-1-12 14:25 发表
newid，你说到中间表弄到独立的库去，那就是基础表和中间表进行分离了是吧。但是这样我对基础表进行的统计，然后统计一次后的数据再到另外的库（假设为B）插入

到中间表中，这样会不会降低性能呢。这两天对表又进行了修改，针对业务需求，发现有几个问题好像需要再进一步改变思路才行，有点不解。

1、如果我把中间表、基础表放到两个独立的库中，（先暂且不考虑分布式的架构，读写分离），如果我对基础表统计好了后，再插入到另外的库中的统计表，性能会不会

影响很大。因为我的中间表有17张表，也就是说每次我要统计都要写入17张表，会不会因为数据多而造成性能的问题。

2、先前也说到过物化视图，包括用快速刷新的物化视图，我之前也是想的每天定时触发JOB来完成计算统计，让结果先冗余起来，甚至每天24小时中每半小时触发一次J

OB来完成统计计算。但是业务需求说是“实时统计”，按照网站的实际访问次数的多少来决定JOB的执行次数

比如：网站A今天一天被访问了1000次，那么我在每次访问结束后就要执行一次JOB来进行网站访问的流量统计，然后数据放到17张中间表中，一天是1000次，意味着

要触发1000次，这样岂不是根本不能用快速刷新的物化视图了，因为不可能是定时触发的。我觉得这样是不是表的设计还需要加上什么策略，我觉得就表结构，基础表加

中间表应该是没什么问题吧，但是觉得好像性能上有很大的考验吧，实时的触发的。

3、在触发JOB进行每次统计时，还需要过滤数据，也就是过滤的条件，目前是有三个

屏蔽的URL、屏蔽的域名、屏蔽的IP段

也就是说每次从基础表进行数据统计到中间表的时候，要从这三个表取出对应的数据，将符合其中的数据过滤掉，这样对查询貌似又有性能上的要求了，通过分区加索引

能解决好吗

4、如果就只有通过条件进行数据的过滤进行统计也还好，但是如果某一天这个过滤条件去掉了，那之前的数据要求要重新统计，那我中间表岂不是每天的数据都要重新

统计了。怎么弄呢中间表。

比如：我今天设置了屏蔽的URL有三个，屏蔽的域名有5个，屏蔽的IP段从某一段到某一段，然后这样每天统计数据（加了这些过滤条件的），20天后，我把这些屏蔽

的设置给去掉了，这样到第21天的时候，统计的数据是不加过滤条件的（因为没有屏蔽的设置了），但是前20天的数据也要在去掉屏蔽设置后重新统计。这意味着我要

对前20天每天的统计数据重新计算了，这样中间表如何设计好呢？性能开销这样也太大了吧。如果是40天，50天，那还了得。

5、我有没有必要加上对每个周进行一次统计的中间表，基于3，4点中的考虑

1. 我上次说的把中间表放到独立的库，是因为你说中间表是和原始表一模一样的拷贝，你只是为了把负载分离出来。
    如果你这个“中间表”是更高层次的统计数据，不应该分库。
2. 实时触发的不现实，这意味着你的并发插入全部会变成串行插入，因为大家都要更新同一个统计条目（假设你一个网站一天有一条统计数据）
    你必须修改业务需求，允许统计数据有延迟。实际应用中这个延迟根本不要紧，很多用户甚至只需要截至前一天的数据。
3. 你的过滤条件是活的，不可能按过滤条件分区。索引没什么用，因为被过滤掉的毕竟是少数。
4. 无法用中间表，这相当于即兴查询，只能查原始数据。
5. 如果用中间表，意味着历史数据不可修改，不能用今天的过滤条件来套用过去的数据。只能够修改需求，接受“过去的统计数据基于过去的过滤条件”这样的事实。

〇〇

好

helvcha

请问一下lz,你的数据库设计工具是什么?
谢谢

lonesashimi

如果查询时间段的要求是集中在某一天的话，基础表可以按照时间字段进行分区，并建分区索引。这个我在100w记录的生产库中没发现对在线事务处理有什么影响。这样，我觉得与基础表相同结构的中间表没有必要建立了。
可以用job定时将基础表的数据的统计结果放到统计中间表中；或者使用物化视图，由oracle来完成这个同步任务。但是记得物化视图应用于数据仓库比较好。如果基础表和统计中间表放在一个数据库中。还是建议使用表的形式。
另外，对于统计粒度要好好规划，既要考虑到基于“统计中间表”的查询效率，还要考虑到能够尽可能的适应统计的变化。如果粒度掌握不好，还需要对已经统计的信息，重新处理。但是如果统计的要求改变（例如统计字段的变化），这个就比较麻烦了。在生成统计数据时，可以使用merge，同时实现insert和update操作，但是没有具体研究过，其效率如何。只是在原来的生产库中没有发现什么问题。但毕竟比lz的数据量小很多。