每秒900，一天430万个数据，如何划分分区大小？

conger_eel 2016-08-29 09:03:44
有个监控项目，16个传感器，采样频率50Hz，需要保持原始数据，也就是每20毫秒插入16个数据，一秒900个。项目需要保存至少3年的数据。

客户端的业务逻辑很简单，就是查询一段时间的数据，没有其他删除修改的操作。初步设计表格为：

create table SYSTEM.AC_TEST

(

  record_time TIMESTAMP(3) not null,

  ac_1        FLOAT,

  ac_2        FLOAT,

  ac_3        FLOAT,

  ac_4        FLOAT,

  ac_5        FLOAT,

  ac_6        FLOAT,

  ac_7        FLOAT,

  ac_8        FLOAT,

  ac_9        FLOAT,

  ac_10       FLOAT,

  ac_11       FLOAT,

  ac_12       FLOAT,

  ac_13       FLOAT,

  ac_14       FLOAT,

  ac_15       FLOAT,

  ac_16       FLOAT

);

alter table SYSTEM.AC_TEST

add constraint RECORD_DATE primary key (RECORD_TIME);

这样
1）方便对比同一时间各个传感器的数据
2）节省了存储空间，提升写入速度

另外时间作为主键，便于查询，主要是基于时间段的数据查询。

按这种设计方式算了一下，1天有430万条数据，一个月是1.3亿，一年15亿条。想到分区来提高检索效率。
分区以时间来分区，到底是按1天分一个区，还是7天，还是一个月呢？ 主要是想提高一下检索的效率。

自己也了相应的测试
1） 插入1天数据（430万）
2） 插入7天数据（3024万）

分别测试查询半个小时的数据
Select ac_1 From ac_test 

Where record_time >= to_timestamp('2016-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

 And  record_time <=to_timestamp('2016-08-01 00:30:00', 'yyyy-mm-dd hh24:mi:ss');


结果是分别需要00:01:00 和 00:06:42 （用pl/sql的explain for测试的）

是不是表明需要按照天来分区了？ 或者更小？

不过按天分区，1分钟的查询也比较耗时了，有没有更好地提高检索效率的问题。

另外这个表还在不停的增加数据，插入数据的时候会不会影响检索的效率？

谢谢各位大侠了！

...全文
809 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
jdsnhan 2016-09-08
  • 打赏
  • 举报
 回复
这样的采集监控系统用实时数据库更好，关系型数据库用于存储汇总分析后的数据
conger_eel 2016-09-08
  • 打赏
  • 举报
 回复
引用 17 楼 bw555 的回复:
考虑建中间统计表吧，定时更新中间表，统计从中间表进行查询统计 每次都从原始数据统计，这么大数据量肯定会比较慢的
用户的查询主要是 一段时间的数据显示，并不是统计总数的。那这样的检索必然是在原始数据检索的。
conger_eel 2016-09-08
  • 打赏
  • 举报
 回复
引用 20 楼 jdsnhan 的回复:
这样的采集监控系统用实时数据库更好，关系型数据库用于存储汇总分析后的数据
谢谢你的建议，对实时数据库不是太了解。
conger_eel 2016-09-07
  • 打赏
  • 举报
 回复
引用 15 楼 ghx287524027 的回复:
[quote=引用 14 楼 conger_eel 的回复:] [quote=引用 13 楼 ghx287524027 的回复:] [quote=引用 11 楼 conger_eel 的回复:] [quote=引用 10 楼 ghx287524027 的回复:] [quote=引用 9 楼 conger_eel 的回复:] 如何进一步提高检索的效率，谢谢各位！ 可否在分区的基础上再子分区，比如按天分区后，在每一天里面再按照小时 分区，这样效率会提高吗？
你用的是哪个版本的oracle，如果利用组合分区，索引是建到子分区上的，就看你的业务是不是需要用到子分区[/quote] 这个和oracle 的版本有什么关系呢？ 用的是oracle 11g release 2. 我的业务很简单就是按照时间查询的，就是利用时间查询数据而已。 组合分区的话，先按时间分区后，里面按啥分区了？貌似没这个需求了，里面都是一些数值了。 [/quote] 有关系啊，10g支持的组合分区比较少。11g相对就多一些了~比如range-range组合在10g中好像是不支持的，但是11g支持[/quote] 哦，学习了。 一上来我就用的11g ，没用过10g 我这种方式应该不需要 组合分区了把？[/quote] 看你业务需求，不需要子分区的话，那就直接一层分区就可以了。组合分区和子分区说的是一个意思哈[/quote] 非常感谢！
bw555 2016-09-07
  • 打赏
  • 举报
 回复
找到个例子，参考下，估计这样能快点 
select sum( *) from
(select count(*) cn from t_table_SS PARTITION (P200709_1)
union all
select count(*) cn from t_table_SS PARTITION (P200709_2)
);
bw555 2016-09-07
  • 打赏
  • 举报
 回复
另外我记得查询分区表时可以指定只从某个分区进行检索，具体写法忘记了
bw555 2016-09-07
  • 打赏
  • 举报
 回复
考虑建中间统计表吧，定时更新中间表，统计从中间表进行查询统计 每次都从原始数据统计，这么大数据量肯定会比较慢的
conger_eel 2016-09-05
  • 打赏
  • 举报
 回复
引用 13 楼 ghx287524027 的回复:
[quote=引用 11 楼 conger_eel 的回复:] [quote=引用 10 楼 ghx287524027 的回复:] [quote=引用 9 楼 conger_eel 的回复:] 如何进一步提高检索的效率，谢谢各位！ 可否在分区的基础上再子分区，比如按天分区后，在每一天里面再按照小时 分区，这样效率会提高吗？
你用的是哪个版本的oracle，如果利用组合分区，索引是建到子分区上的，就看你的业务是不是需要用到子分区[/quote] 这个和oracle 的版本有什么关系呢？ 用的是oracle 11g release 2. 我的业务很简单就是按照时间查询的，就是利用时间查询数据而已。 组合分区的话，先按时间分区后，里面按啥分区了？貌似没这个需求了，里面都是一些数值了。 [/quote] 有关系啊，10g支持的组合分区比较少。11g相对就多一些了~比如range-range组合在10g中好像是不支持的，但是11g支持[/quote] 哦，学习了。 一上来我就用的11g ，没用过10g 我这种方式应该不需要 组合分区了把？
ghx287524027 2016-09-05
  • 打赏
  • 举报
 回复
引用 11 楼 conger_eel 的回复:
[quote=引用 10 楼 ghx287524027 的回复:] [quote=引用 9 楼 conger_eel 的回复:] 如何进一步提高检索的效率，谢谢各位！ 可否在分区的基础上再子分区，比如按天分区后，在每一天里面再按照小时 分区，这样效率会提高吗？
你用的是哪个版本的oracle，如果利用组合分区，索引是建到子分区上的，就看你的业务是不是需要用到子分区[/quote] 这个和oracle 的版本有什么关系呢？ 用的是oracle 11g release 2. 我的业务很简单就是按照时间查询的，就是利用时间查询数据而已。 组合分区的话，先按时间分区后，里面按啥分区了？貌似没这个需求了，里面都是一些数值了。 [/quote] 有关系啊，10g支持的组合分区比较少。11g相对就多一些了~比如range-range组合在10g中好像是不支持的，但是11g支持
conger_eel 2016-09-05
  • 打赏
  • 举报
 回复
自己顶一下，哈哈哈哈
ghx287524027 2016-09-05
  • 打赏
  • 举报
 回复
引用 14 楼 conger_eel 的回复:
[quote=引用 13 楼 ghx287524027 的回复:] [quote=引用 11 楼 conger_eel 的回复:] [quote=引用 10 楼 ghx287524027 的回复:] [quote=引用 9 楼 conger_eel 的回复:] 如何进一步提高检索的效率，谢谢各位！ 可否在分区的基础上再子分区，比如按天分区后，在每一天里面再按照小时 分区，这样效率会提高吗？
你用的是哪个版本的oracle，如果利用组合分区，索引是建到子分区上的，就看你的业务是不是需要用到子分区[/quote] 这个和oracle 的版本有什么关系呢？ 用的是oracle 11g release 2. 我的业务很简单就是按照时间查询的，就是利用时间查询数据而已。 组合分区的话，先按时间分区后，里面按啥分区了？貌似没这个需求了，里面都是一些数值了。 [/quote] 有关系啊，10g支持的组合分区比较少。11g相对就多一些了~比如range-range组合在10g中好像是不支持的，但是11g支持[/quote] 哦，学习了。 一上来我就用的11g ，没用过10g 我这种方式应该不需要 组合分区了把？[/quote] 看你业务需求，不需要子分区的话，那就直接一层分区就可以了。组合分区和子分区说的是一个意思哈
conger_eel 2016-09-02
  • 打赏
  • 举报
 回复
引用 10 楼 ghx287524027 的回复:
[quote=引用 9 楼 conger_eel 的回复:] 如何进一步提高检索的效率，谢谢各位！ 可否在分区的基础上再子分区，比如按天分区后，在每一天里面再按照小时 分区，这样效率会提高吗？
你用的是哪个版本的oracle，如果利用组合分区，索引是建到子分区上的，就看你的业务是不是需要用到子分区[/quote] 这个和oracle 的版本有什么关系呢？ 用的是oracle 11g release 2. 我的业务很简单就是按照时间查询的，就是利用时间查询数据而已。 组合分区的话，先按时间分区后，里面按啥分区了？貌似没这个需求了，里面都是一些数值了。
ghx287524027 2016-09-01
  • 打赏
  • 举报
 回复
引用 9 楼 conger_eel 的回复:
如何进一步提高检索的效率，谢谢各位！ 可否在分区的基础上再子分区，比如按天分区后，在每一天里面再按照小时 分区，这样效率会提高吗？
你用的是哪个版本的oracle，如果利用组合分区，索引是建到子分区上的，就看你的业务是不是需要用到子分区
conger_eel 2016-09-01
  • 打赏
  • 举报
 回复
如何进一步提高检索的效率，谢谢各位！ 可否在分区的基础上再子分区，比如按天分区后，在每一天里面再按照小时 分区，这样效率会提高吗？
conger_eel 2016-09-01
  • 打赏
  • 举报
 回复
顶一下，呵呵呵呵
ghx287524027 2016-08-30
  • 打赏
  • 举报
 回复
引用 4 楼 conger_eel 的回复:
[quote=引用 3 楼 ghx287524027 的回复:] [quote=引用 1 楼 conger_eel 的回复:] 另外 分别测试一下1天和7天的 主键的索引树高度都是2 （Blevel) ，既然索引树高度是一样的，为何检索的效率会下降这么多啊？谢谢各位。
按天分区就可以，具体执行效率问题看一下执行计划[/quote] 谢谢啊，主要是不明白 索引树高度一样，为何检索效率差这么多？ 另外执行计划 explain plan for 测试了，是一个1分钟，一个7分钟的。 这个是在我的PC上测试的， 双核3.0G，内存3G，如果换服务器具体能够提高多少呢？[/quote] 语句上没什么可优化的了，本来就是很简单的一条语句。建个索引，然后看看那执行计划怎么走的，有没有走索引
conger_eel 2016-08-30
  • 打赏
  • 举报
 回复
引用 2 楼 wmxcn2000 的回复:
按天分区就可以了 PS : 怎么把表 放在了 SYSTEM 下呢，你建立一个新的用户吧；
谢谢， 只是做个示例测试的，刚开始学oracle， 用户这块不是太清楚的，随便龙的 另外想问一下，这个查询效率还能不能进一步提高？ 利用分区索引？这个record-time 时间已经算是了把？ 这方面不是太清楚
conger_eel 2016-08-30
  • 打赏
  • 举报
 回复
引用 3 楼 ghx287524027 的回复:
[quote=引用 1 楼 conger_eel 的回复:] 另外 分别测试一下1天和7天的 主键的索引树高度都是2 （Blevel) ，既然索引树高度是一样的，为何检索的效率会下降这么多啊？谢谢各位。
按天分区就可以，具体执行效率问题看一下执行计划[/quote] 谢谢啊，主要是不明白 索引树高度一样，为何检索效率差这么多？ 另外执行计划 explain plan for 测试了，是一个1分钟，一个7分钟的。 这个是在我的PC上测试的， 双核3.0G，内存3G，如果换服务器具体能够提高多少呢？
ghx287524027 2016-08-30
  • 打赏
  • 举报
 回复
引用 1 楼 conger_eel 的回复:
另外 分别测试一下1天和7天的 主键的索引树高度都是2 （Blevel) ，既然索引树高度是一样的，为何检索的效率会下降这么多啊？谢谢各位。
按天分区就可以，具体执行效率问题看一下执行计划
conger_eel 2016-08-30
  • 打赏
  • 举报
 回复
引用 6 楼 ghx287524027 的回复:
[quote=引用 4 楼 conger_eel 的回复:] [quote=引用 3 楼 ghx287524027 的回复:] [quote=引用 1 楼 conger_eel 的回复:] 另外 分别测试一下1天和7天的 主键的索引树高度都是2 （Blevel) ，既然索引树高度是一样的，为何检索的效率会下降这么多啊？谢谢各位。
按天分区就可以，具体执行效率问题看一下执行计划[/quote] 谢谢啊，主要是不明白 索引树高度一样，为何检索效率差这么多？ 另外执行计划 explain plan for 测试了，是一个1分钟，一个7分钟的。 这个是在我的PC上测试的， 双核3.0G，内存3G，如果换服务器具体能够提高多少呢？[/quote] 语句上没什么可优化的了，本来就是很简单的一条语句。建个索引，然后看看那执行计划怎么走的，有没有走索引[/quote] 两次的执行计划是： 
Plan Hash Value  : 2937004892 

------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes    | Cost | Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             | 90001 | 31230347 | 4987 | 00:01:00 |
|   1 |   TABLE ACCESS BY INDEX ROWID | AC_TEST     | 90001 | 31230347 | 4987 | 00:01:00 |
| * 2 |    INDEX RANGE SCAN           | RECORD_DATE | 90001 |          |  251 | 00:00:04 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("RECORD_TIME">=TIMESTAMP' 2016-08-01 00:00:00.000000000' AND "RECORD_TIME"<=TIMESTAMP' 2016-08-01 00:30:00.000000000')

Plan Hash Value  : 3955607741 

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes   | Cost  | Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         | 90001 | 2880032 | 33453 | 00:06:42 |
|   1 |   TABLE ACCESS BY INDEX ROWID | AC_TEST | 90001 | 2880032 | 33453 | 00:06:42 |
| * 2 |    INDEX RANGE SCAN           | PK_TIME | 90001 |         |  1936 | 00:00:24 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("RECORD_TIME">=TIMESTAMP' 2016-08-01 00:00:00.000000000' AND "RECORD_TIME"<=TIMESTAMP' 2016-08-01 00:30:00.000000000')
看结果应该是走了索引的，只检索了30分钟的数据（50*60*30）。 帮忙看看是否是这样的
加载更多回复（2）
实战：上亿数据如何秒查
最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度有从半小时以上(甚至查不出)到秒查的质变。从修改SQL查询语句逻辑到决定创建存储过程实现，花了我3天多的时间，在此总结一下，希望对朋友们有帮助。数据背景首先项目是西门子中国在我司实施部署的MES项目，由于项目是在产线上运作(3 years+)，数据累积很大。在项目的数据库中，大概上亿条数据的表有5个以上，千万级数据的表...
实战：上亿数据如何秒查？
实战：上亿数据如何秒查？ ...
耗时3天，上亿数据如何做到秒级查询？
最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度由从半小时以上（甚至查不出）到秒查的质变。从修改 SQL 查询语句逻辑到决定创建存储过程实现，花了我 3 天多的时间，在此总结一下，希望对朋友们有帮助。 数据背景 首先项目是西门子中国在我司实施部署的 MES 项目，由于项目是在产线上运作(3 years+)，数据累积很大。 在项目的数据库中，大概上亿条数据的表有 5 个以上，千万级数据的表 10 个以上，百万级数据的表，很多… （历史问题，当初实施无人监管，无人监控数...
耗时 3 天，上亿数据如何做到秒级查询？
最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度由从半小时以上(甚至查不出)到秒查的质变。从修改 SQL 查询语句逻辑到决定创建存储过程实现，花了我 3 天多的时间，在此总结一下，希望对朋友们有帮助。数据背景首先项目是西门子中国在我司实施部署的 MES 项目，由于项目是在产线上运作(3 years+)，数据累积很大。在项目的数据库中，大概上亿条数据的表有 5 个以上，千万级数据的表 10...
基于一个MES项目应发的思考：上亿数据如何秒查？
最近在忙着优化集团公司的一个报表。优化完成后，报表查询速度有从半小时以上(甚至查不出)到秒查的质变。从修改SQL查询语句逻辑到决定创建存储过程实现，花了我3天多的时间，在此总结一下，希望对朋友们有帮助。 数据背景 首先项目是西门子中国在我司实施部署的MES项目，由于项目是在产线上运作(3 years+)，数据累积很大。在项目的数据库中，大概上亿条数据的表有5个以上，千万级数据的表10个以上，百万级数据的表，很多… (历史问题，当初实施无人监管，无人监控数据库这块的性能问题。ps:我刚入职不久…) 不
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章