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

commanderan

水平切分表？用分区还是中间表还是物化视图实现？概念性的?

学习ing
mark

fan0124

水平切分表就是把一个大记录的表分成两个或多个结构相同的数据表，比如tbl_day_total，字段有id,name,date,content，记录有1000W条，我水平切分成两张表

比如分成tbl_day_total1、tbl_day_total2，字段是一样的，结构是一样的，但是每张表分担了500W条记录。垂直切分表就是把表的字段切开，比如本来有50个字

段，分成两张表，每张表就包含了25个字段


谢谢各位高手们的指点和讨论


现在已经进行到了数据库表的逻辑结构设计了（贴中所说的关于盘点的数据库）

但是之前说到统计时有两块业务会按时段统计，也就是说要走原始基础数据表，每天原始基础数据表是1000W数据

这个是没有考虑去走中间表的，我在考虑这几个问题

1、能不能在每天插入原始基础数据后，每天晚上11点59分这个样子去触发任务，然后将原始数据表全部导入到一份中间表（该中间表也就是原始数据表的备份），如果

有按时段这个粒度查询的话，我就直接走这个备份表，毕竟每天就只插入一次。但是如果触发的任务执行失败，那按时间段粒度查询这个表岂不是失败了

2、原始基础数据表和统计的中间表有必要分库吗，比如原始基础数据表放在A库，统计的放在B库，放在同一个库会不会造成IOPS过高，因为每天1000W数据量，及

亿级数据量的访问，有必要读写分离吗

3、索引的设计是一开始就考虑好呢，还是在数据量大了以后才加上呢，当然，目前逻辑结构设计阶段我也能清楚索引按哪几个走比较好，但是如果一开始设计好，那插入

数据会不会受影响

4、如果我对原始数据表做个中间表的备份，这样是不是可以避免了在原始数据表上做索引（毕竟插入操作比较多）

还望各位高手拍砖指导

lypch

原帖由 newkid 于 2009-12-25 11:47 发表
1.和你的理解差不多，中间表（或物化视图）就是用来存放粗粒度的统计结果，避免扫描原始数据。
2.如果仅仅是1:30的关系，没有必要保存月统计结果，日统计结果就足够了。
但是不同组合条件的中间表确实会很多。
3,4.如果你的即席查询是无法预计的，或数字是不可加的（比如算不同IP个数，同一个IP不同时段来访问只能算一次）那么就必须访问原始数据。
5.没必要用临时表。直接用SQL返回的数据集。
6.表分区比切分表要好，我在另一贴也说过了。

NewkID好久不见了，问一个问题呀。在存储过程中如何返回数据集啊？

newkid

1. 没搞懂为什么要中间表。为什么不直接查原始数据？难道你的中间表比较小？
2. 假如分库，前提是没有跨库的JOIN，否则你会后悔的。
3. 先想到的就先加，其他的等有必要再加。
4. 同#1, 为什么中间表索引代价就比较小？

newkid

原帖由 lypch 于 2010-1-5 09:23 发表



NewkID好久不见了，问一个问题呀。在存储过程中如何返回数据集啊？

我都是用游标(REF CURSOR)。

ywhgh

不知道你们的业务，按什么条件查询较多。
对这种大表的处理，通常我会做如下处理：
1.事实表按天分区，不建索引。
2.另做一张统计表，每天更新统计前30天（具体时间按情况定）的值，不分区，只保留当前值。
3.如对2有历史查询，再建一张历史统计表，按天分区，保存2中的历史。

cargoj

学习了！
正在做一个简单的网站统计，做法和几位说的一样，把每月原始数据处理一下放中间表里，避免重复处理数据；分开时间段也不会一次消耗很长时间。

fan0124

谢谢newid兄弟的指点和解释，真的受益匪浅！

我是这样想的，因为每天1000W的原始基础数据首先进入到基础数据表，一共三个，我设计的表是tbl_pv_day，tbl_visitor_day，tbl_visitinfo_day

1、如果是按天查询相应的统计数据的话，我就走中间统计表（每天晚上特定时间触发任务统计数据）

2、如果是按时段查询，情况不多，就查询流量的时候要用到时间段粒度，这样是必须要走基础数据表的，由于基础数据表是高IOPS的，插入数据比较频繁，如果我设计

了索引，本身基础数据表又是频繁的插入数据，那查询的性能会影响吧。所以我想能不能设计个中间表，在每天晚上特定的时间触发任务将这一天的基础数据全部插入到

这个中间表（每天1000W条），由于这张中间表肯定不会频繁插入操作的，我按时间段查询的话就走这张表，数据量和基础数据表的一样，但是建立了索引代价比较小

一点，所以我就想到弄个中间表，这样不知道行不行。

3、触发任务是每天晚上23点59分进行好呢，还是00点00分进行，主要是触发任务进行数据统计放到统计的中间表中

newkid

我觉得没有必要用中间表，就直接在基础表上建索引和查询。
除非你的中间表要弄到独立的库去（此时你可以用定时自动刷新的物化视图），否则还是在一个库上, IO更多了，只是转移了时间点而已。

linyisen1985

我们公司的产品和LZ要求有些类似，不过我们比你们低一个数量级，说一下我的看法仅供参考。
基础表肯定是要分区的，我们的基本数据有保留时间的，比如5天，10天或者一个月，每天JOB都会定时调用存储过程对到了时间的相应的表进行分区的删除和新分区的创建，LZ每天千万级的数据访问，这些数据是否都需要保留？
基础表汇总到统计表时，我们用了临时表存储临时数据集，这个很有必要。
现在问题是：LZ的基础表增删太频繁，又没有多少查询，我个人不建议加索引，但是不加索引的话基础表到统计表的统计时间会很长。。。不知有没有更好的办法