大数据量表的一些优化策略

Tll_W 2014-09-01 11:56:37

大概情况我举个例子，现在有很多大楼，大楼有5到10层吧，每层中每天定时（1小时间隔）会产生一些数据，比如温度等参数。针对此，我设计了一个表，字段包括：
ID：随机生成的字符，关键字
LayerID：楼层的编号
Time：时间
测量数据：温度等类似数据
现在的问题是：如果按照1000个楼，每个楼大约有10层，这样一年的数据大约是1000*10*24*365=7000W左右的数据量，这么多数据，入库查询肯定都是比较慢的，这种情况这个表怎么优化？
我考虑了几个方面，建立索引和分表等，还有其他方面的也可以说说，分不够可再加。

...全文

540 25 打赏收藏转发到动态举报

写回复

用AI写文章

25 条回复

切换为时间正序

请发表友善的回复…

发表回复

Tiger_Zhao 2014-09-30

打赏
举报

先分析需求！
你有保留7亿明细的必要吗？不要想当然。
通常查询也只会是一些统计结果，按需要统计汇总到某个层级，过期明细可以删除的。

Tll_W 2014-09-30

打赏
举报

引用 21 楼 Tiger_Zhao 的回复:

7000W条记录不算很多。查询的优化主要就是两条：索引，统计汇总表。

如果是10年的数据来预估，那就是7亿条记录了，现在我查询主要集中在3个字段：楼号、楼层和时间，比如我要差某楼在一定时间内的数据，或者查询某层（不同楼层编号不同，是唯一的）在一定时间内的数据，我建一个联合聚集索引（楼号、楼层、时间）够吗？

Tiger_Zhao 2014-09-30

打赏
举报

7000W条记录不算很多。
查询的优化主要就是两条：索引，统计汇总表。

Tll_W 2014-09-30

打赏
举报

引用 18 楼 Tiger_Zhao 的回复:

你要处理什么？？？先把需求搞清楚！只有按需优化的系统，没有面面俱到的完美系统。

我的目标就是这些数据存储和查询速度能满足基本要求，不至于超时就行。数据量很明确，大约200多个楼，按照一年的数据量来算就是200个楼*10层*365天*24小时*6个十分钟 = 7000W条记录针对此表，给与一些表的优化就行

Tll_W 2014-09-30

打赏
举报

引用 10 楼 dotnetstudio 的回复:

1、针对楼分表进行存储数据 2、每个楼的表中建立针对楼层、监控点ID和时间的聚族索引这样基本能满足你的需求，如果后续遇到查询效率瓶颈，欢迎再咨询

按照楼分表存储有个问题就是我分区函数用楼号作为分区依据，但是分区方案中我要把所有的楼号都枚举进去吗？

Tiger_Zhao 2014-09-30

打赏
举报

你要处理什么？？？
先把需求搞清楚！
只有按需优化的系统，没有面面俱到的完美系统。

Tll_W 2014-09-30

打赏
举报

引用 16 楼 Tiger_Zhao 的回复:

又是非常非常非常老旧的文件型数据库，一个表（文件）数据太多不得不分开。你所有的楼都是活跃的，分表没有意义。

对于我这种情况，没有方法进行处理了吗？大部分情况下比如昨天的数据入库了，后面应该再次入库的可能性极小了，只是读取出来用而已。

Tiger_Zhao 2014-09-30

打赏
举报

又是非常非常非常老旧的文件型数据库，一个表（文件）数据太多不得不分开。
你所有的楼都是活跃的，分表没有意义。

Tll_W 2014-09-30

打赏
举报

引用 13 楼 dotnetstudio 的回复:

[quote=引用 11 楼 Tll_W 的回复:] [quote=引用 10 楼 dotnetstudio 的回复:] 1、针对楼分表进行存储数据 2、每个楼的表中建立针对楼层、监控点ID和时间的聚族索引这样基本能满足你的需求，如果后续遇到查询效率瓶颈，欢迎再咨询

忘记了，这个表里也有楼号，后续查询偶尔会查询楼在某段时间的所有数据，这种情况咋办?还需要建立索引吗？[/quote] 分表后楼号已经在表名里面了，一个表中就是某一栋楼的所有的记录，索引不需要建在楼号上[/quote] 分表是怎么分？通过代码还是通过数据库的设置？

KeepSayingNo 2014-09-30

打赏
举报

⊙﹏⊙b汗，肯定不是自己枚举建表啊，而是自动建表啊，例如你要插入一条数据，这个数据肯定包含楼层号，那么插入前就判断表是否存在，不存在就建表

haitao 2014-09-30

打赏
举报

当前表：1年7千万条，不算多历史表：只导入，不删改

Tiger_Zhao 2014-09-02

打赏
举报

引用 8 楼 Tll_W 的回复:

[quote=引用 6 楼 Tiger_Zhao 的回复:] A)给的内存太少，不要吝啬，数据库单独一台服务器，内存不限制。 B)要(layerid,time)一起建聚集索引。如果没有单独按time查询的需求，那么单个的(time)索引毫无用处。

大概明白了些，那么如果再多加一个字段，比如楼号，其实楼号跟层号是有关联关系的，这个时候如果我要查某个楼一段时间内的数据，是不是也得建立一个联合索引？[/quote] 要再建一个索引。只有查询条件和索引匹配才能充分利用索引。

Tll_W 2014-09-02

打赏
举报

引用 10 楼 dotnetstudio 的回复:

1、针对楼分表进行存储数据 2、每个楼的表中建立针对楼层、监控点ID和时间的聚族索引这样基本能满足你的需求，如果后续遇到查询效率瓶颈，欢迎再咨询

忘记了，这个表里也有楼号，后续查询偶尔会查询楼在某段时间的所有数据，这种情况咋办?还需要建立索引吗？

sywcf 2014-09-02

打赏
举报

第一：layerid，聚集索引是对的。第二：time字段如果要加索引，那得看这个字段是精确到天的，还是到秒的。如果只是到天的。加索引可以;精确到秒的，加索引，不但起不到加快的效果，反而会变慢。第三：楼主的time条件，是需要到天还是到小时？如果要到小时，那么最好，把time字段拆开，一个只是短日期，另一个存小时，不会再到秒了吧？这样，在日期，小时字段上加索引，才有意义。

KeepSayingNo 2014-09-02