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

newkid

1. “历史数据不可修改”的原则必须坚持。
   你说的“流量来源”例子，应该放在原始数据中并作为历史保存，原来是1就永远是1, 以后变成3是以后的事。
   新增或者细分都是用新的流量来源ID, 不应该影响历史数据。
   关于“消重”：如果一次访问跨两个日期，要算在哪天还是两天都算？
   你的1.1方法只有一个时间戳，只能算在一个日期。而且要UPDATE就得定位，你用什么条件来找那条记录？
   1.2方法：想想看这个冗余是否值得，而且同样有定位记录的问题。
   我倾向于保持多条记录，在统计的时候用分析函数来做“消重”。

2. "关联"是什么意思？应该坚持规范化设计，就是你说的记录访客ID的方法。

3. "半小时内的再次访问网站还是算同一次访问"也就是说你无法捕捉到“结束访问”这个事件，只能靠定时触发，还是我说的JOB思路。
   我不赞成实时统计，也没有解决方法。

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

   你的统计是要把多条原始数据聚合为一条，如果实时统计，就是说有很多个SESSION都要做统计，都要更新同一行统计数据。这是致命弱点。
   改用JOB后，全局只有一个JOB在运行，也就是只有一个SESSION在做统计，它想更新就更新，想插入就插入，没有别人和它竞争。

4.1 分区的大小以及使用其他方法，你要和DBA一起讨论确保方便管理、维护和分散IO。你也可以建立测试环境，测试在统计一天数据的时候全扫描一个按天的分区，以及按索引扫描一个月分区中的一天，效率差距有多少，值不值得按天分区。
4.2 分区是针对一个库。这个库可能有多个RAC节点，但还是一个库。
4.3 多个库肯定是在多个服务器上。如果你统计的时候需要远程访问基础数据，那会很痛苦的，因此统计数据必须和基础数据同库。此基础数据也可以是原始数据的一个复制，和原始数据不同库。但我建议你们还是从简单的结构开始。如果测试表明分库有很大好处就分库。

fan0124

原帖由 newkid 于 2010-1-22 00:02 发表
1. “历史数据不可修改”的原则必须坚持。
   你说的“流量来源”例子，应该放在原始数据中并作为历史保存，原来是1就永远是1, 以后变成3是以后的事。
   新增或者细分都是用新的流量来源ID, 不应该影响历史数据。
   关于“消重”：如果一次访问跨两个日期，要算在哪天还是两天都算？
   你的1.1方法只有一个时间戳，只能算在一个日期。而且要UPDATE就得定位，你用什么条件来找那条记录？
   1.2方法：想想看这个冗余是否值得，而且同样有定位记录的问题。
   我倾向于保持多条记录，在统计的时候用分析函数来做“消重”。

2. "关联"是什么意思？应该坚持规范化设计，就是你说的记录访客ID的方法。

3. "半小时内的再次访问网站还是算同一次访问"也就是说你无法捕捉到“结束访问”这个事件，只能靠定时触发，还是我说的JOB思路。
   我不赞成实时统计，也没有解决方法。

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

   你的统计是要把多条原始数据聚合为一条，如果实时统计，就是说有很多个SESSION都要做统计，都要更新同一行统计数据。这是致命弱点。
   改用JOB后，全局只有一个JOB在运行，也就是只有一个SESSION在做统计，它想更新就更新，想插入就插入，没有别人和它竞争。

4.1 分区的大小以及使用其他方法，你要和DBA一起讨论确保方便管理、维护和分散IO。你也可以建立测试环境，测试在统计一天数据的时候全扫描一个按天的分区，以及按索引扫描一个月分区中的一天，效率差距有多少，值不值得按天分区。
4.2 分区是针对一个库。这个库可能有多个RAC节点，但还是一个库。
4.3 多个库肯定是在多个服务器上。如果你统计的时候需要远程访问基础数据，那会很痛苦的，因此统计数据必须和基础数据同库。此基础数据也可以是原始数据的一个复制，和原始数据不同库。但我建议你们还是从简单的结构开始。如果测试表明分库有很大好处就分库。

newid兄，参照你的指导，自己也不断深入研究，现在到搭建物理设计的阶段，解决了很多问题（再次表示感谢）但是还是有几个比较辣手的，有点疑惑

1、基本上就用多条记录，然后来进行消重。但是我有几点疑问

  1.1. 你说的“用分析函数来消重”，我也倾向于在数据库中用SQL语句进行消重，分析函数是接近数据的，所以执行起来肯定很快。但是我要达到消重，会经过很多条件来过滤，最后用类似distinct

   也好，group by也好，这对于1000W记录（仅指每一天），处理上会不会慢呢？1000W记录上筛选（性能测试我还在构建进行，所以现在没办法给出实际的数据参照）

  1.2. 消除重复后的记录（也就是PV记录的消重）应该也是百万，就按1/10来算，100W，那统计后的记录还是这么多，然后插入一张数据表，会不会影响I/0性能，就算只是查询出结果集，那这个

  结果集也有100W，在这个基础上再进行函数分析、统计操作会很慢吗？

2、那次说的“关联”是指单独一张关联表来关联两张表，比如Visitor访客表，Visit访问表，中间再放个关联表，就放两个表的关联ID。“不关联”就是直接把关联ID写到其中一个表中，反范式的设计

3、采用你说的JOB思路，这个是最实际，最可行的。我把需求部门提出的实时响应给直接驳回去了。但是现在定时触发上

  3.1. 我定时肯定就起一个计划任务来执行一个JOB，比如job_totaldata。里面肯定放的统计基础数据表数据的存储过程（每半小时执行一次来统计1000W基础数据），那我通过存储过程是一次

  查询出结果集好呢（一次查询出来，要包含到所有17张中间表的字段，一共201个字段），还是分若干次查询（每次只包含特定几张中间表的字段），一次查询用到201个字段，会不会太多？

  3.2. 半小时触发一次JOB统计一次，对1000W记录进行各种统计计算（有的需要很多过滤条件），这样是不是会很慢

  3.3. 如何分类进行统计？
  
  我们是对公司的5000个网站客户进行统计，那触发统计时，首先是要把基础数据按照不同的网站分类，也就是5000条记录，然后对每个不同的网站再统计数据，难道要循环对5000个网站依次进行

  数据统计？由于都是在存储过程中通过SQL语句实现，觉得有点没思路，SQL语句怎么写能这样区分呢？
  
  3.4. 每小时就有5000条记录插入到17张中间表中，也就是说每半小时就有85000次插入操作，通时基础数据肯定还有继续进来数据的，这样并发会不会对性能损耗很大，要通过读写分离这样的

  方式来减少IO呢，还是用别的方式？

4.分区和分表

  4.1. 1000W记录每天，1个月3亿，1年36亿，表分区后还是一张表，按天分的话，每个小的分区1000W记录，但是这张表一年就是36亿记录，会不会慢（由于还是一张表），用不用在表分区的基

  础上再进行切表？一张表我觉得3亿记录就已经很多了吧？

  4.2. 由于数据都是不删除的，所以当达到一定规模后（一年后）是不是要对数据进行归档处理（数据转移到归档表），然后开始新的一年的数据的统计

fan0124

17张中间表（黄色的）

newkid

1.1 假设你用分析函数 ROW_NUMBER() OVER (PARTITION BY ...)来消重，那么重复的判断条件越复杂(PARTITION BY后面的列越多)开销就越大。所以你必须搭建环境后实测看看效果。
1.2 "那统计后的记录还是这么多" 你是指把消除重复后的数据保存下来？如果它会被反复使用，那就必须保存。
    "结果集也有100W，在这个基础上再进行函数分析、统计操作会很慢吗？"已经比原来小很多了。一天100W不算什么。

2. 关联表适用多对多的情况，你这个访客和访问表是一对多关系，因此只要在访问表中保存访客ID就可以了，这才是规范设计。

3.1 一次查出来好。你只用一个字段，ORACLE也会把整行读入内存。
3.2 半小时的数据量怎么还有1000W? 尽量用时间戳筛选数据，只统计这一段时间，统计结果是增量的。前提是你的指标都是可加的(ADDITIVE)
3.3 看不懂为什么要循环，GROUP BY不就行了？
3.4 插入统计数据虽然很多但是成批插入的。如果你把统计和原始数据分库，那么你生成统计数据时要读远程数据，效率低下而且也会给源数据库造成IO负担。
4.1 切表比分区有什么好处？分区已经相当于把表切块了。ORACLE对付3亿的表一点问题也没有。
4.2 你可以用分区交换的方法把旧数据撤出去，原始数据保留多久看你们需要。归档时要重建全局索引的，会有停机时间。

〇〇

学习

fan0124

原帖由 newkid 于 2010-2-5 00:25 发表
1.1 假设你用分析函数 ROW_NUMBER() OVER (PARTITION BY ...)来消重，那么重复的判断条件越复杂(PARTITION BY后面的列越多)开销就越大。所以你必须搭建环境后实测看看效果。
1.2 "那统计后的记录还是这么多" 你是指把消除重复后的数据保存下来？如果它会被反复使用，那就必须保存。
    "结果集也有100W，在这个基础上再进行函数分析、统计操作会很慢吗？"已经比原来小很多了。一天100W不算什么。

2. 关联表适用多对多的情况，你这个访客和访问表是一对多关系，因此只要在访问表中保存访客ID就可以了，这才是规范设计。

3.1 一次查出来好。你只用一个字段，ORACLE也会把整行读入内存。
3.2 半小时的数据量怎么还有1000W? 尽量用时间戳筛选数据，只统计这一段时间，统计结果是增量的。前提是你的指标都是可加的(ADDITIVE)
3.3 看不懂为什么要循环，GROUP BY不就行了？
3.4 插入统计数据虽然很多但是成批插入的。如果你把统计和原始数据分库，那么你生成统计数据时要读远程数据，效率低下而且也会给源数据库造成IO负担。
4.1 切表比分区有什么好处？分区已经相当于把表切块了。ORACLE对付3亿的表一点问题也没有。
4.2 你可以用分区交换的方法把旧数据撤出去，原始数据保留多久看你们需要。归档时要重建全局索引的，会有停机时间。

newid兄，我目前还在搭建测试环境中，看了你的讲解

1. 确实，目前开销的实际效果还要等测试环境中跑跑，压力测试下看具体的处理情况（准备是先以1000W数据位测试点，制造数据真是花时间的说）

2. 我目前最困惑的难点（也是重点）就是这个定时执行的JOB的SQL编写和处理性能上了

   2.1. 确实newid兄你说的没错，半小时不可能数据量都是1000W，按半小时触发执行一次统计，一天执行48次，第一次执行的时候肯定基础数据表里的数据也许就几千，第二次执行时就增加

   到几万，到最后几次执行的时候，可能就是1000W数据量。我是按日数据量的最大值来做考虑的，如果在1000W的基础上进行复杂的统计、归类、计算这样的操作是不是SQL语句写的好坏决定

   性比较大（我就担心执行定时统计时的性能，插入应该还好调优的）

   2.2. 说要循环是这个意思（这也是最困惑的地方，SQL该怎么写），下面的图是基础数据表（1张），是把5000个网站的统计数据都记录在基础表中，比如网站1，网站2，网站3。。。（见下图）

   每个网站每天的PV量、访问次数、每次访问的时间肯定各不相同。现在问题就是如何进行分类计算统计。

   首先，我肯定是要去重复，将1000W（就按这个日最大值的数据量）条记录去重复，按照网站分类，5000个网站，也就是说分成5000条记录了，这个没错，group by可以实现。

   但是第二步，我要对这5000个网站每个网站的PV，访问次数，独立访客数，平均访问时长等计算出来，然后分类放到每个网站对应的那条记录中，比如说网站1，1000W数据中肯定要首先筛选

   网站ID为1的记录，然后将这些记录进行条件筛选、对比、计算，得到想要的统计数据。

   问题是一个Group by，中间的SQL语句写出来很复杂的吧

   所以我想到难不成要循环来完成？因为5000个网站，记录分布在1000W中，很多数据是根据编号的不同进行计算、过滤得到聚合在一条记录（每个网站对应一条记录）

   PS：如何解决这个网站分类的记录统计呢？

   比如：网站流量分析中间表，有网站平均浏览量，网站人均浏览量，网站平均访问次数这些要统计的数据字段，但是这个中间表每次统计都会将5000个网站的统计信息都放进去，也就是5000

    条记录，但是中间这个统计的过程（从基础数据表到中间表）是一个SQL来完成呢，还是分几次统计来完成（可能就要多条SQL语句了，那就是多次连接数据库了吧？）

3、有必要用临时表吗，还是由上面的2引发出来，分几步，第一步将网站分类出来，放到临时表中，然后在这个表的基础上再对每个网站分类统计各种信息。但是性能会影响吧

4、统计字段有201个，这一下子统计出201个字段，会太多了吧？（说实话，我第一次遇上要一次查询出来201个字段，感觉太多了，唉，17个中间表有201个字段要统计）

5、统计有必要分开统计不，就是先统计前5个中间表的信息，然后再来统计5张中间表的数据

6、 目前按时间戳分区是比较好的，有必要按网站ID来组合分区不？怎么分呢（因为2引发出来的问题）

newkid

这贴都被置顶了？
1. 利用 SELECT FROM DUAL CONNECT BY的办法可以制造大量数据，但如果你要随机性很好的话，随机函数开销还是挺大的。
2.1 是不是有一些统计指标不能叠加的，也就是说无法利用前面的统计结果？尽量把这些东西隔离出来，能利用累计的就尽量利用。
2.2 你举一个统计指标作例子，说明它的计算公式，假设单独统计一个网站你打算怎么做，然后我们看看有没有办法用GROUP BY来成批完成。
3. 临时表的原则：假如这个临时结果被反复利用就用临时表；否则，SQL中的WITH 子查询就够了。
4. 前面说了ORACLE是按数据块来读的，一次访问所有列肯定优于多次访问单独的列。
5. 还是同问题3, 看你的结果有没有可能被后面的利用。
6. 如果2解决了按时间分区就可以。

songyang2009

good

fan0124

原帖由 newkid 于 2010-2-5 23:39 发表
这贴都被置顶了？
1. 利用 SELECT FROM DUAL CONNECT BY的办法可以制造大量数据，但如果你要随机性很好的话，随机函数开销还是挺大的。
2.1 是不是有一些统计指标不能叠加的，也就是说无法利用前面的统计结果？尽量把这些东西隔离出来，能利用累计的就尽量利用。
2.2 你举一个统计指标作例子，说明它的计算公式，假设单独统计一个网站你打算怎么做，然后我们看看有没有办法用GROUP BY来成批完成。
3. 临时表的原则：假如这个临时结果被反复利用就用临时表；否则，SQL中的WITH 子查询就够了。
4. 前面说了ORACLE是按数据块来读的，一次访问所有列肯定优于多次访问单独的列。
5. 还是同问题3, 看你的结果有没有可能被后面的利用。
6. 如果2解决了按时间分区就可以。

还是有点惊喜的，帖子被置为精华了，也被置顶了几天。谢谢斑竹！也感谢这么长时间以来各位高手的真知灼见

newid，这个统计，我给出详细的计算公式及策略，目前最大的难点就是如何在短时间内将这201个字段统计出来，其实整个平台系统最关键的就是

把数据统计聚合起来，至于应用层面的，没什么很难的。


1、基础数据表结构

由于是对公司的客户网站进行流量及访客行为统计分析，则目前我设计的有一张页面基础数据表


对字段的解释（方便识别数据的意义）

     页面基础数据表：pd_page

page_id  页面编号（自增的ID）

page_siteid  网站编号（统计的网站）

page_sitetitle  网站标题

page_domain  受访域名

page_url  受访页面URL

page_visittime 访问时间

page_visitseconds  单个页面访问时长

page_searchflag  搜索引擎标记  0-没使用搜索引擎  1-使用了搜索引擎

page_searchparent 搜索引擎父级编号  

page_search  搜索引擎（为数值类型）

0-其他  1-百度  2-谷歌  3-必应  4-搜狗  5-搜搜  6-狗狗  7-中国雅虎  8-爱问  9-中搜  10-8684  11-去哪儿

12-百度知道  13-百度贴吧  14-百度新闻  15-百度图片  16-百度地图

注：对于百度来说，百度搜索引擎是搜索引擎父级，而百度知道是隶属于百度搜索引擎，所以百度知道是单独的子级。类似于一个产品A，属于

某个产品分类，这里的搜索引擎父级和搜索引擎就类似于产品行业分类，1级产品分类，2级分类。

page_keyword  搜索的关键字

page_pv  PV值

page_visitorflag 访客标记  0-新访客  1-回访客（一天24小时内，使用相同一个Cookie算做一个相同的访客，第一次访问网站为新访客）

page_identify  访客身份标识  38位长的一个字符串，是从Cookie中取出的，用来识别访客的唯一性

page_ip  IP地址（这里存为的数值类型，将中间的点给去掉了，计算的时候要转换为一个大的数字）

page_isp  网络接入商类型  

  0-其他  1-中国电信  2-中国移动  3-中国联通  4-中国网通  5-中国铁通  6-中国卫通

  7-长城宽带  8-**宽带  9-中国教育和科研计算机网  10-方正宽带  11-艾普宽带

page_browserparent  浏览器父级编号

page_browser 浏览器（类似上面的搜索引擎一样的道理，分为父级）

  0-其他  1-IE6.0  2-IE7.0  3-IE8.0  4-IE9.0  5-FireFox 6-Safari  7-Chrome  8-Opera  

  9-遨游Maxthon  10-腾讯TT  11-360安全浏览器  12-搜狗浏览器  13-世界之窗The World   

page_osparent 操作系统父级编号

page_os  操作系统（类似上面的搜索引擎一样的道理，分为父级）

  0-其他  1-Win2000  2-WinServer2003  3-WinXP  4-WinVista  5-WinServer2008  6-Win7  7-linux  8-MacOS
  
page_resolution  分辨率 （字符类型）

page_lang  语言  0-其他  1-中文  2-英文  3-法文  4-法文  5-日文  6-俄文  7-德文

page_cookie  Cookie支持  0-不支持  1-支持

page_java  Java支持  0-不支持  1-支持

page_flash  Flash支持  0-不支持  1-支持

page_client 终端类型  0-其他  1-电脑  2-手机

page_createtime  记录创建时间

page_delflag   删除标记

page_verflag  版本标记

page_sourtraffic  0-其他  1-直接流量  2-推荐网站  3-搜索引擎  4-Email来源

page_visitid  访问ID（用来区分第几次访问的）

page_sourdomain 来路域名 （通过什么域名过来的）

page_soururl 来路页面（通过什么页面过来的）  

page_uniquepv  唯一浏览量  （1-唯一  0-是重复的刷新）

page_prevpage 上一页

page_nextpage 下一页

page_enterflag 进入标记（1-是  0-否  是否是入口页面）

page_exitflag 退出标记（1-是  0-否  是否是退出页面）

page_jumpflag 跳出标记（1-是  0-否  是否是跳出页面）

page_browsername 浏览器名称

page_osname 操作系统名称

page_searchname 搜索引擎名称

page_areaid 地区ID

page_areaname  地区名称


<一>、统计数据的计算公式和示例

制造的数据都是符合业务要求的，假设现在只统计一天内网站ID为1的网站A，所有的统计都要加上共同的条件：网站ID=1 的过滤

中间表的字段数：205（所有中间表需要统计的字段数量）

基础表的数据量：1000W

统计模块：目前只统计流量统计模块的


1、流量统计模块（统计可聚合为1条记录）

1.1. 流量分析

网站浏览量：page_id的记录数和

网站独立访客：page_identfiy不重复值的记录数和

网站IP数：page_ip不重复值的记录数和

网站访问次数：page_visitid不重复值的记录数和

网站人均浏览量：公式= 网站浏览量/网站独立访客

网站平均浏览量：公式= 网站浏览量/网站访问次数

网站唯一浏览量：公式= 网站浏览量/page_unique值为1的记录数和

网站非跳出浏览量：公式= 网站浏览量/page_jumpflag值为0的记录数和

网站平均访问时长：公式= 网站的总访问时长（page_visitseconds的累加）/网站访问次数

网站跳出率：公式= 只访问了一个页面的访问次数（page_jumpflag为1的不重复page_visitid记录数和）/网站访问次数 * 100%


1.2. 流量来源分析

流量来源分类：page_sourtype不重复值的记录数和

不同分类浏览量：page_sourtype不重复值的记录数和

不同分类独立访客：每种page_sourtype对应的不重复page_visitor记录数和

不同分类IP数：每种page_sourtype对应的不重复page_ip记录数和

不同分类访问次数：每种page_sourtype对应的不重复page_visitid记录数和

不同分类访问次数百分比：公式= 不同分类访问次数/网站访问次数（不重复page_visitid记录数和）* 100%

不同分类平均浏览量：公式= 不同分类浏览量/网站浏览量（page_id记录数和）* 100%

不同分类访问时长：每种page_sourtype对应的page_visitseconds累加和

不同分类平均访问时长：公式= 不同分类访问时长/网站访问时长（page_visitseconds累加和）

不同分类跳出率：公式= 不同分类


1.3. 来路域名分析

来路域名：不重复的page_sourdomain

来路域名浏览量：每个不同的page_sourdomain对应的记录数和

来路域名独立访客：每个不同的page_sourdomain对应的不重复page_visitor记录数和

来路域名IP数：每个不同的page_sourdomain对应的不重复page_ip记录数和

来路域名访问次数：每个不同的page_sourdomain对应的不重复page_visitid记录数和


1.4. 来路页面分析

来路页面：不重复的page_soururl

来路页面浏览量：每个不同的page_soururl对应的记录数和

来路页面独立访客：每个不同的page_sourdourl对应的不重复page_visitor记录数和

来路页面IP数：每个不同的page_sourdourl对应的不重复page_ip记录数和

来路页面访问次数：每个不同的page_soururl对应的不重复page_visitid记录数和




问题：

1、这目前只是对流量统计模块的所有统计字段进行统计，这个SQL怎么去写

2、一共有3W个网站（虽然现在公司的资源是6000网站，但是说考虑到增长，3W个网站），这是对一个网站统计聚合出一条记录，3W个网站那就是

3W条记录

3、上面的流量统计是聚合为一条记录的，但是访客环境统计就不是一条记录，多条记录

比如：按分辨率来统计，对于网站ID为1的网站统计，就会出现多种分辨率的情况，那就是多条记录

这样的情况怎么和聚合为一条的结合

是不是单独再统计，但这样就会多次查询，貌似会影响性能

4、中间表的策略：我目前是30分钟统计一次，然后记录都是插入，如果要取统计结果，就取时间最晚的一条记录（创建时间）

是这样的策略好呢，还是每一次统计，就把之前的那条统计记录给删除掉，这样一个网站一天就保持一条记录

这样哪种的效率更好呢？

5、分区按天分区，一年365天，我表空间不可能设置为几百个，怎么样平均将分区分配到各个表空间呢（分区我理解的就是分到各个表空间，分担

IO压力）

6、用不用按照网站ID来配合分区呢，3W个网站

7、开发组提到说要分表，说在分区的基础上，每个月再分张表出来，这样性能能好吗？

fan0124

统计出来的格式例如就像这样的：

网站ID   PV   独立访客   IP数    访问次数   网站平均访问时长（秒）  

  1      35      20       20       8             20.35

  2      100     10       25       40            16.00

这只是一部分，每个中间表就对应这样的一个格式，但大多是聚合为一条记录，访客环境是多条记录