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

fan0124

原帖由 helvcha 于 2010-1-13 11:56 发表
请问一下lz,你的数据库设计工具是什么?
谢谢

我用的是REStudio7.0设计数据库的，以前也用过PD12.5、ERWin设计过，感觉虽然ERStudio虽然是英文的，但是功能比较不错，尤其是导出文件，可以导出成图片、XML等等

〇〇

er studio 也能逆向工程

fan0124

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

谢谢你提出的见解

1、目前对于时间段的查询不是集中在某一天，而是什么时候都有可能，比如：10月、11月、12月三个月的每天早上的9点这个时刻点。所以这个建索引的粒度就一开始就要设计好了，就是这个时间段

的查询不知道会不会引起性能的下降，毕竟是在基础表中

2、如果基础表和统计中间表放在一个数据库中，还是建议使用表的形式。这句话有点不太明白，难道物化视图不是表？物化视图应该也是表吧，只不过是表+定时的JOB，这样就是快速刷新的（不知道

我的理解对不对）

3、目前的处理逻辑是：要求实时性，这样定时的好像就不行了

我一开始的处理逻辑是：每半小时触发一次JOB，在JOB里将我的基础表的数据进行计算、统计，得到的统计数据放到我的17张中间统计表中，当然是按不同的网站来进行统计的，这样统计表中一个网

站一天也就48条记录，取数据就是取时间最晚的那个了。到了第二天然后再触发JOB，类推。

但是业务需求要求是实时，也就是这样的：比如A网站一天有1000次访问，那么在每次访问结束时就要进行一次统计。例如我现在进行了一次访问，看了6个页面，然后结束了访问，关掉了浏览器。结

束的时候就要进行数据的统计，将统计的数据放到中间统计表中，然后我如果40分钟后又进行了一次访问，那结束访问的时候就又再进行一次统计，将统计的数据放到中间统计表中。这样实时要求性

比较高。不知道结合什么样的策略会更好。


目前的表就是：原始表（什么处理都不做的最原始的数据）、中间表（进行了统计的中间表）

fan0124

原帖由 newkid 于 2010-1-13 00:13 发表


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

进行了相应的改进，也再次和业务需求、开发团队交流了相关的意见，进行了改进

1、目前中间表分成了两类：一类就是你说的更高层次，也就是我们说的计算过，统计过的数据，那就是应该和原始数据表放在一个库了是吗。 第二类是和原始表一模一样的拷贝，一方面是为了把

负载分离出来，一方面是为了适应以后业务规则的改变后，比如现在制定的流量来源分类了，有的是是搜索引擎，也许以后它就不是搜索引擎了。为了适应这样的变化，所以要份最原始的数据。如果

这样的话，结构是一摸一样的话就要放到独立的库里了是吗？

2、业务需求的要求确实是要实时触发的。比如：A网站早上9点有一次访问，访问者看了6个页面离开了，结束了一次访问，那在访问结束的时候就要触发JOB对A网站进行一次数据的统计，然后将从

原始数据表中统计过的数据放到17个中间表中，然后9点40的时候又有一次访问了，访问者看了2个页面结束了访问，那么在结束的时候就同样地进行统计。

原先我的思路是：每天每半小时定时触发JOB来执行统计，将数据放到统计表中。这样每个网站每天的记录也就48条，取数据就取时间最新的那一条。

但现在的需求就成了：如果一个网站一天有1000次访问（是按访问次数来界定的），那我就要统计1000次，问题是肯定不会是只有一个网站，假设我的用户网站是500个（实际的是比这多的），每个

网站每天就当只有1000次访问，那就是要触发JOB的次数是：500 X 1000 = 500000次。

这样的高请求，会不会把服务器搞崩溃呢，并发的请求就像你说的肯定会变成串行插入，数据延迟肯定允许的，但是太长了肯定不行吧。

如果表设计的策略已经足够好了：分区啊，通过冗余啊，建索引啊，剩下的就是只能合理的规划服务器了吧，通过分布式来减轻这样的压力不？

面对高并发，只是分区能够应对吗？

就这个我还是比较困惑

3、确实过滤条件是活的，因为还要加上地区、日期这样的。

4、时间段的查询确实没办法，也不可能用中间表，只能走原始表。那我就是要对原始表进行合理的分区了不？索引的选择粒度就选择时间段，那索引岂不是分出很多，一天就有24个时段。一个月就是

720个时段。

5、这个已经解决，历史数据不可修改，如果因为业务规则变化导致之后的数据变化，那之前的还是保持原样。


高并发下，如果能保证数据表的统计能够比较顺利呢。主要是随着日期的增长，数据是很多的。每天是1000W，这样的情况下进行高并发的统计，有什么策略可以保证性能上的一个稳定呢

fan0124

原帖由 junsansi 于 2009-12-25 10:08 发表
你这种站点统计系统对数据的要求并没有那么高~~~
比如举个例子昨天的详细统计数据今天才能看也没有关系对吧，就是数据的实时性没有太高的要求~~
因此处理数据的时间相对是很富余的~
表设计方面按照我的经验，由于要处理的数据量较大，我觉着数据冗余度越高越好，就说用户最终查询的统计结果，应尽可能提前生成(这可能会导致一大批中间表的产生，但是这确实是有价值的)。比如说当天的相关统计生成之后，应该自动更新关联的月度统计等等设计~~
通过这种方式来逐级平衡查询需求，将一个大的数据查询需求分化成多个小的查询以提交整个系统的访问速度。

我开始也是觉得应该不会太高，但是业务要求的就是：实时统计

也就是按每次访问结束后就要进行统计，中间表一共17个。

现在头疼的就是如何保证这样的并发要求，毕竟每天是1000W数据进来，在这个基础上统计

如果每天就1W条数据，那就不用很担心了

suqibin

和我以前做的一个项目的数据量有点相像，我们用的是数据仓库，是分库统计，有专用的ETL工具，用的是分区和分层统计，把统计范围逐渐缩小，可能达不到你说的实时监控的效果了

newkid

原帖由 fan0124 于 2010-1-18 15:03 发表



进行了相应的改进，也再次和业务需求、开发团队交流了相关的意见，进行了改进

1、目前中间表分成了两类：一类就是你说的更高层次，也就是我们说的计算过，统计过的数据，那就是应该和原始数据表放在一个库了是吗。 第二类是和原始表一模一样的拷贝，一方面是为了把

负载分离出来，一方面是为了适应以后业务规则的改变后，比如现在制定的流量来源分类了，有的是是搜索引擎，也许以后它就不是搜索引擎了。为了适应这样的变化，所以要份最原始的数据。如果

这样的话，结构是一摸一样的话就要放到独立的库里了是吗？

2、业务需求的要求确实是要实时触发的。比如：A网站早上9点有一次访问，访问者看了6个页面离开了，结束了一次访问，那在访问结束的时候就要触发JOB对A网站进行一次数据的统计，然后将从

原始数据表中统计过的数据放到17个中间表中，然后9点40的时候又有一次访问了，访问者看了2个页面结束了访问，那么在结束的时候就同样地进行统计。

原先我的思路是：每天每半小时定时触发JOB来执行统计，将数据放到统计表中。这样每个网站每天的记录也就48条，取数据就取时间最新的那一条。

但现在的需求就成了：如果一个网站一天有1000次访问（是按访问次数来界定的），那我就要统计1000次，问题是肯定不会是只有一个网站，假设我的用户网站是500个（实际的是比这多的），每个

网站每天就当只有1000次访问，那就是要触发JOB的次数是：500 X 1000 = 500000次。

这样的高请求，会不会把服务器搞崩溃呢，并发的请求就像你说的肯定会变成串行插入，数据延迟肯定允许的，但是太长了肯定不行吧。

如果表设计的策略已经足够好了：分区啊，通过冗余啊，建索引啊，剩下的就是只能合理的规划服务器了吧，通过分布式来减轻这样的压力不？

面对高并发，只是分区能够应对吗？

就这个我还是比较困惑

3、确实过滤条件是活的，因为还要加上地区、日期这样的。

4、时间段的查询确实没办法，也不可能用中间表，只能走原始表。那我就是要对原始表进行合理的分区了不？索引的选择粒度就选择时间段，那索引岂不是分出很多，一天就有24个时段。一个月就是

720个时段。

5、这个已经解决，历史数据不可修改，如果因为业务规则变化导致之后的数据变化，那之前的还是保持原样。


高并发下，如果能保证数据表的统计能够比较顺利呢。主要是随着日期的增长，数据是很多的。每天是1000W，这样的情况下进行高并发的统计，有什么策略可以保证性能上的一个稳定呢

1. 你说的复制数据的“另一方面”理由不成立，你复制的相当于只是数据仓库里的“事实表”，事实表即使在源库里也应当是不变的。而你说的相当于SLOW CHANGING DIMENSION, 你要能知道每个网站的属性变化历史，这属于维度的管理。
   复制数据就是为了分离负载，如果还在原库那就没必要复制。

2. 如果并发变成了串行，所有事务就会在一个地方排队，不管你怎么努力都没用的。这种设计不可扩缩，用户体验很糟糕。
   如果允许统计延迟，那就设计一个JOB来统计，在允许的延迟频率触发，比如每10分钟统计一次，按结束访问的时间戳来扫描。这样所有对统计表的更新就只有一个SESSION在做，不影响原始数据的并发插入。

4. 要确定你的查询条件可以用于定位分区。分得太细可能没有意义，在分区内按索引扫描就很好了。

gvora

学习~

ilovemilk

good，学习

fan0124

原帖由 newkid 于 2010-1-18 22:49 发表


1. 你说的复制数据的“另一方面”理由不成立，你复制的相当于只是数据仓库里的“事实表”，事实表即使在源库里也应当是不变的。而你说的相当于SLOW CHANGING DIMENSION, 你要能知道每个网站的属性变化历史，这属于维度的管理。
   复制数据就是为了分离负载，如果还在原库那就没必要复制。

2. 如果并发变成了串行，所有事务就会在一个地方排队，不管你怎么努力都没用的。这种设计不可扩缩，用户体验很糟糕。
   如果允许统计延迟，那就设计一个JOB来统计，在允许的延迟频率触发，比如每10分钟统计一次，按结束访问的时间戳来扫描。这样所有对统计表的更新就只有一个SESSION在做，不影响原始数据的并发插入。

4. 要确定你的查询条件可以用于定位分区。分得太细可能没有意义，在分区内按索引扫描就很好了。

newid,谢谢你给了很多思路，向你学习！

1、关于页面基础数据

    确实你说到SCD，我才突然想起来，我这种情况的确属于DW中的SCD（数据仓库我还理解的不深入，运用的很少，还需要请教），可能内容有所变化，但是变化的频率肯定不大。就是这张表（中

途插入图片不知道怎么插，上传附件在下面了）：

下图中的基础数据表：页面

这个页面是记录原始的访问情况，也就是我们说的PV记录。在中间有个流量来源，按照业务的需求是分为了  1-直接流量  2-推荐网站  3-搜索引擎  4-Email来源 这四种，但是业务需求说可能

这个有变化，要把流量来源抽离出这个页面基础表，再复制个原始数据表。

需求分析团队给我说的就是：比如有个网站，我们以前一直是按照直接流量来统计的，标记为1。后来这个网站成为了个搜索引擎公司，于是再统计这个URL流量的时候，我们就要归结为搜索引擎，

标记为3，而不是1了。这样是不是要重新放个原始数据表，结构上和这个“页面”基础表相同，就是少了流量来源，然后再原始数据表的基础上才是这个基础数据表，就多了个流量来源。但是我觉得

完全没必要，你觉得呢？就算是现在流量来源变化成3了，以前的为1的我肯定不会变了。

     如果流量来源以后会增加（基本几率很小）或者会细分（比如将推荐网站再细分为小类），这样我的数据在流量来源的标识上前后肯定不一样，还是继续维持“历史数据不可修改”的原则呢，之前

的是哪样就是哪样，不用改。

   
     然后还是针对整个“页面”基础表，需求说要进行消重，要再多个消重表，结构字段完全一样。因为现在我设计的这个“页面”基础表，策略是记录每次PV，

比如：新打开了一个页面，这个时候要记录一次PV，就写入一条记录到“页面基础表”，如果对整个页面刷新了2次，那就继续插入2条记录到“页面基础表”

但是需求说为了后面的统计分析需要，需要消重处理。也就是要除掉这种刷新的情况。如果这样的话，我想了两种策略：

1.1. 还是就一张“页面基础表”，对于一个相同页面反复刷新的情况，我就不重新插入记录了，只是在这条记录上的字段浏览量（PV）上累加，也就是更新记录的操作了。

1.2. 加一张结构与“页面基础表”一摸一样的表，记录消重后的记录，比如：新开一个页面后，PV为1，插入一条记录，然后刷新了3次，由于是相通的页面，因此我这张表就1条记录，而先前的

“页面基础表”就是4条记录，一个是没消重的基础表，一个是消重的。

    不知道选哪种策略好？插入和更新记录的性能比较哪个更大呢？


2、关联的问题

   对于基础数据表，目前三张，页面 1000W条/每天， 访问次数 差不多200W条/每天 ，访客  最少了，顶多10W条/每天

   但是基础数据表之间要不要关联呢，联系是很紧密的，一个访客每天的访问次数可以为多个，每次访问会看多个页面，一个页面会被多个访客看到。对于大数据量的，有的说关联性能低了，有的说

关联还是必要的。我之前看过个帖子说：两张1000W记录的表，建立关联了，查询比不建立关联查询要性能高，不知道是真是假，没试验过。

我个人觉得适当关联吧，不知道是否得当？不要中间的关联表，直接将关联的字段写在某个表中，比如页面和访客的两张基础表，我就将访客的ID在页面的基础表中加上，多一个字段，这样建立联系。


3、还是实时统计的问题：将统计定时触发执行，只是定时的间隔更短，这个确实不错，（自己怎么就没想过呢，晕死），控制在10分钟触发一次，一天也就144次。但是如果真的要实时触发呢，也就

是每次访问结束的时候触发JOB来统计，但是有个条件，同一个人访问，每次访问之间的间隔是半小时，也就是说访问结束后，半小时内的再次访问网站还是算同一次访问。这样实时触发是不是能行


如果要实时触发的统计：

3.1. 并发很高时，这个会造成很高的IO，但是如果要实现，是不是必须从整体架构上也要考虑，比如要RAC，不然抗不住

3.2. 实时触发怎么让并发不会变成串行的请求呢？通过什么策略可以改进呢，

3.3. 是否需要更多的服务器来支撑才行

3.4. 通过什么方式可以来实现实时触发呢？用现在比较好的hadoop？


还有你说的这句“这样所有对统计表的更新就只有一个SESSION在做，不影响原始数据的并发插入。”不是很懂，要解释下

4、表分区的策略

4.1. 对于我设计的日1000W数据的记录，1个月是3亿，表分区我最近都在学习研究，区间分区，列表分区，Hash分区，组合分区，我感觉好像我就只能用区间分区好些吧

按照时间戳来分区，如果按天的时间戳分区的话，那不是一年下来，要分出365个，会不会多了？

分区的这个时间戳的度上把握到哪个度好呢，是天，还是周，还是月

4.2. 分区是针对一台数据库服务器呢，还是多个？如果是分区在多个服务器上，那会不会性能影响？

4.3. 多个库的建立是不是建立在多个服务器上呢，比如我肯定为了分离负载，可能有的统计就放到另外的库中，原始的在一个库中


下面的图一个是基础表的，一个是整体的，（修改后的差不多完整版的了）