每天3亿条数据，要求能够实时显示数据以及历史查询，如何设计结构 ？

conger_eel 2016-07-29 05:00:03

现在有个监测项目，传感器的采样频率为50Hz，也就是1秒50个数据，传感器数目60个，要求保存3年的数据，算了一天的数据量是接近3亿条，做的是一个Web系统，同时要求这些数据能够实时显示和查询历史数据。

个人的想法，为了保证查询的实时性，采用临时表+历史表的设计方式， 临时表存储一段时间的实时数据（比如一天的时间），隔一段时间将实时数据从临时表中转移到历史表中，web 实现显示的时候 就查临时表，查历史就历史表，布置可行否？效率如何，或者有没有更好地设计方式， 谢谢各位了！
...全文
4221 27 打赏 收藏 转发到动态 举报
写回复
用AI写文章
27 条回复
切换为时间正序
请发表友善的回复…
发表回复
miniminipotato 2019-11-06
  • 打赏
  • 举报
 回复
本人是小白，刚做关于机床实时监控的项目，传入数据是xml文件类型，我想问一下，有没有什么示例实现实时数据存入数据库和前端实时显示的，借鉴一下
qq_41979432 2019-10-10
  • 打赏
  • 举报
 回复
你好 楼主的数据能不能提供做算法分析，学生党一枚 没有数据 尤其是水质 大气这种
weixin_39441513 2019-01-31
  • 打赏
  • 举报
 回复
这个很简单，实时显示数据，可以用socket来实现，来一条数据就推送给前端。如果想把数据保留下来，来一条数据就存储到数据库。新建一个数据库，这个数据库可以按日期建表。mysql一张表到达几百万条数据，服务器配置好可以到达几千万，查询就会有影响。我平常数据日志不重要的，都放在日志文件里，按照日期建的。
zgl263885058 2017-11-27
  • 打赏
  • 举报
 回复
引用 23 楼 myzoft 的回复:
为什么没有人想到用实时数据库呢，对于这种场景瞬间秒杀
就是啊，这不就是实时数据库干的事情吗？
myzoft 2017-10-02
  • 打赏
  • 举报
 回复
为什么没有人想到用实时数据库呢，对于这种场景瞬间秒杀
zz22255666 2017-07-31
  • 打赏
  • 举报
 回复
楼主，问题解决没，求分享
conger_eel 2016-08-09
  • 打赏
  • 举报
 回复
引用 20 楼 lieshengren 的回复:
[quote=引用 17 楼 conger_eel 的回复:] [quote=引用 16 楼 lieshengren 的回复:] 在程序端实现，实时显示有各种方法。 不建议把频繁操作压到数据库这端来。。。 前端一个线程压缓冲队列或者栈，另外一个触发去写数据。 了不起断电 死进程丢数据就是。 在普通项目用数据库来保证 多客户端的数据完整性，是不是有点舍本逐末。
现在是客户端为WEB 端， 要实时的显示的话，考虑到各浏览器的兼容问题，好像办法不多啊，能想到的方法的就是AJAX 了。 请问大侠还有其他的好方法吗？谢谢。 这样就可以减轻数据库端的压力了。 当然这个实时显示可以允许延迟的，我们做的就是尽可能减小延迟而已。 [/quote] 就因为是web系统，所以根本不需要那么高的刷新效率。 1秒1条已经够了。 以前一个客户端线性报表项目，试验过1秒/条 已经比较容易引起浏览器端crash了。虽然是外网访问。 内网高刷新估计可行，但是也要考虑到人机交互一般没有那么高的刷新需求。 你控制下采样率，写数据可以按照50hz来，同步显示的话，1hz足够了。。除非你要在客户端实时保存数据，这个也可以曲线法来实现的。[/quote] 1秒钟刷新浏览器，取50个数据，测过浏览器还能承受啊，主要是延时比较厉害。
巴拉莱卡 2016-08-08
  • 打赏
  • 举报
 回复
引用 17 楼 conger_eel 的回复:
[quote=引用 16 楼 lieshengren 的回复:] 在程序端实现，实时显示有各种方法。 不建议把频繁操作压到数据库这端来。。。 前端一个线程压缓冲队列或者栈，另外一个触发去写数据。 了不起断电 死进程丢数据就是。 在普通项目用数据库来保证 多客户端的数据完整性，是不是有点舍本逐末。
现在是客户端为WEB 端， 要实时的显示的话，考虑到各浏览器的兼容问题，好像办法不多啊，能想到的方法的就是AJAX 了。 请问大侠还有其他的好方法吗？谢谢。 这样就可以减轻数据库端的压力了。 当然这个实时显示可以允许延迟的，我们做的就是尽可能减小延迟而已。 [/quote] 就因为是web系统，所以根本不需要那么高的刷新效率。 1秒1条已经够了。 以前一个客户端线性报表项目，试验过1秒/条 已经比较容易引起浏览器端crash了。虽然是外网访问。 内网高刷新估计可行，但是也要考虑到人机交互一般没有那么高的刷新需求。 你控制下采样率，写数据可以按照50hz来，同步显示的话，1hz足够了。。除非你要在客户端实时保存数据，这个也可以曲线法来实现的。
conger_eel 2016-08-05
  • 打赏
  • 举报
 回复
引用 18 楼 mingqing6364 的回复:
我做过一个类似的项目，检测温湿度的，用的是DHT11，布置了8个点，每5秒左右一次数据，0.2HZ 8个DHT11连接到单片机上，再由单片机串口传递数据到服务器，服务器是500不到的棒子机，Z3735F/2G/32G/WIN10 数据库用的是Mysql 服务器每接受到一条数据，就往JK_N1这个表中写入，表结构如下： 

CREATE TABLE `kfjk_n1` (
  `time` datetime NOT NULL,
  `P10_T` tinyint(2) DEFAULT NULL,
  `P10_R` tinyint(2) DEFAULT NULL,
  `P11_T` tinyint(2) DEFAULT NULL,
  `P11_R` tinyint(2) DEFAULT NULL,
  `P12_T` tinyint(2) DEFAULT NULL,
  `P12_R` tinyint(2) DEFAULT NULL,
  `P13_T` tinyint(2) DEFAULT NULL,
  `P13_R` tinyint(2) DEFAULT NULL,
  `P14_T` tinyint(2) DEFAULT NULL,
  `P14_R` tinyint(2) DEFAULT NULL,
  `P15_T` tinyint(2) DEFAULT NULL,
  `P15_R` tinyint(2) DEFAULT NULL,
  `P16_T` tinyint(2) DEFAULT NULL,
  `P16_R` tinyint(2) DEFAULT NULL,
  `P17_T` tinyint(2) DEFAULT NULL,
  `P17_R` tinyint(2) DEFAULT NULL,
  `P20_T` tinyint(2) DEFAULT NULL,
  `P20_R` tinyint(2) DEFAULT NULL,
  `P21_T` tinyint(2) DEFAULT NULL,
  `P21_R` tinyint(2) DEFAULT NULL,
  `P22_T` tinyint(2) DEFAULT NULL,
  `P22_R` tinyint(2) DEFAULT NULL,
  `P23_T` tinyint(2) DEFAULT NULL,
  `P23_R` tinyint(2) DEFAULT NULL,
  `P24_T` tinyint(2) DEFAULT NULL,
  `P24_R` tinyint(2) DEFAULT NULL,
  `P25_T` tinyint(2) DEFAULT NULL,
  `P25_R` tinyint(2) DEFAULT NULL,
  `P26_T` tinyint(2) DEFAULT NULL,
  `P26_R` tinyint(2) DEFAULT NULL,
  `P27_T` tinyint(2) DEFAULT NULL,
  `P27_R` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
为什么我会把多个点的数据放一条记录里面： 1）每个点的数据采集时间都会有毫秒级的间隔，这点时间间隔可以忽略 2）节省了存储空间，提升写入速度 3）方便对比同一时间（实际上应该说是相近时间）各个点的温湿度情况 原本计划建立16个监测点，实际只做了8个，所以这个表里面有些字段是空的 留空是因为有计划扩展，如果短时间不扩的话，还是建议有多少个监测点就给多少个字段 DHT11只能测到整数部分的，小数部分无力测量，所以我使用tinyint(2)存储 另外以时间做主键，不建立任何索引，这样可以保证插入效率最大化 为避免数据库过大，每天会定时压缩数据 按每5秒一次检测来算，24小时大概会产生17280条数据 写了个计划，将前一天每10分钟的数据聚合成一条，取平均值，保留小数后两位，这样一天的数据就只有144条了 同时会将原始数据删除 如果你的服务器配置足够强劲，那么我还是建议你定期对数据预处理一下，查询精度要求不高的话，直接查询处理后的数据，提升效率。 最后 在其他PC上查阅实时数据，我这里用的是C/S架构，直接通过数据包发送到客户端上显示 查阅历史数据才会连接数据库
非常感谢你的经验和分享。谢谢！ 我的这边的业务逻辑很简单，就是存储和显示原始数据，不用对数据做任何处理，主要的问题就是如何应付大容量，高频数据的读取和存储的问题。
mingqing6364 2016-08-04
  • 打赏
  • 举报
 回复
我做过一个类似的项目，检测温湿度的，用的是DHT11，布置了8个点，每5秒左右一次数据，0.2HZ 8个DHT11连接到单片机上，再由单片机串口传递数据到服务器，服务器是500不到的棒子机，Z3735F/2G/32G/WIN10 数据库用的是Mysql 服务器每接受到一条数据，就往JK_N1这个表中写入，表结构如下： 

CREATE TABLE `kfjk_n1` (
  `time` datetime NOT NULL,
  `P10_T` tinyint(2) DEFAULT NULL,
  `P10_R` tinyint(2) DEFAULT NULL,
  `P11_T` tinyint(2) DEFAULT NULL,
  `P11_R` tinyint(2) DEFAULT NULL,
  `P12_T` tinyint(2) DEFAULT NULL,
  `P12_R` tinyint(2) DEFAULT NULL,
  `P13_T` tinyint(2) DEFAULT NULL,
  `P13_R` tinyint(2) DEFAULT NULL,
  `P14_T` tinyint(2) DEFAULT NULL,
  `P14_R` tinyint(2) DEFAULT NULL,
  `P15_T` tinyint(2) DEFAULT NULL,
  `P15_R` tinyint(2) DEFAULT NULL,
  `P16_T` tinyint(2) DEFAULT NULL,
  `P16_R` tinyint(2) DEFAULT NULL,
  `P17_T` tinyint(2) DEFAULT NULL,
  `P17_R` tinyint(2) DEFAULT NULL,
  `P20_T` tinyint(2) DEFAULT NULL,
  `P20_R` tinyint(2) DEFAULT NULL,
  `P21_T` tinyint(2) DEFAULT NULL,
  `P21_R` tinyint(2) DEFAULT NULL,
  `P22_T` tinyint(2) DEFAULT NULL,
  `P22_R` tinyint(2) DEFAULT NULL,
  `P23_T` tinyint(2) DEFAULT NULL,
  `P23_R` tinyint(2) DEFAULT NULL,
  `P24_T` tinyint(2) DEFAULT NULL,
  `P24_R` tinyint(2) DEFAULT NULL,
  `P25_T` tinyint(2) DEFAULT NULL,
  `P25_R` tinyint(2) DEFAULT NULL,
  `P26_T` tinyint(2) DEFAULT NULL,
  `P26_R` tinyint(2) DEFAULT NULL,
  `P27_T` tinyint(2) DEFAULT NULL,
  `P27_R` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
为什么我会把多个点的数据放一条记录里面： 1）每个点的数据采集时间都会有毫秒级的间隔，这点时间间隔可以忽略 2）节省了存储空间，提升写入速度 3）方便对比同一时间（实际上应该说是相近时间）各个点的温湿度情况 原本计划建立16个监测点，实际只做了8个，所以这个表里面有些字段是空的 留空是因为有计划扩展，如果短时间不扩的话，还是建议有多少个监测点就给多少个字段 DHT11只能测到整数部分的，小数部分无力测量，所以我使用tinyint(2)存储 另外以时间做主键，不建立任何索引，这样可以保证插入效率最大化 为避免数据库过大，每天会定时压缩数据 按每5秒一次检测来算，24小时大概会产生17280条数据 写了个计划，将前一天每10分钟的数据聚合成一条，取平均值，保留小数后两位，这样一天的数据就只有144条了 同时会将原始数据删除 如果你的服务器配置足够强劲，那么我还是建议你定期对数据预处理一下，查询精度要求不高的话，直接查询处理后的数据，提升效率。 最后 在其他PC上查阅实时数据，我这里用的是C/S架构，直接通过数据包发送到客户端上显示 查阅历史数据才会连接数据库
conger_eel 2016-08-04
  • 打赏
  • 举报
 回复
引用 16 楼 lieshengren 的回复:
在程序端实现，实时显示有各种方法。 不建议把频繁操作压到数据库这端来。。。 前端一个线程压缓冲队列或者栈，另外一个触发去写数据。 了不起断电 死进程丢数据就是。 在普通项目用数据库来保证 多客户端的数据完整性，是不是有点舍本逐末。
现在是客户端为WEB 端， 要实时的显示的话，考虑到各浏览器的兼容问题，好像办法不多啊，能想到的方法的就是AJAX 了。 请问大侠还有其他的好方法吗？谢谢。 这样就可以减轻数据库端的压力了。 当然这个实时显示可以允许延迟的，我们做的就是尽可能减小延迟而已。
巴拉莱卡 2016-08-03
  • 打赏
  • 举报
 回复
在程序端实现，实时显示有各种方法。 不建议把频繁操作压到数据库这端来。。。 前端一个线程压缓冲队列或者栈，另外一个触发去写数据。 了不起断电 死进程丢数据就是。 在普通项目用数据库来保证 多客户端的数据完整性，是不是有点舍本逐末。
专注or全面 2016-08-02
  • 打赏
  • 举报
 回复
换一下设计思路可否，将你一次采集的数据存储成一行 这么一整，你数据总行数就少了很多了
conger_eel 2016-08-02
  • 打赏
  • 举报
 回复
引用 14 楼 x_wy46 的回复:
换一下设计思路可否，将你一次采集的数据存储成一行 这么一整，你数据总行数就少了很多了
恩，想过啊，比如50个数据 合在一起存成 string ，中间用空格隔开，一秒钟存一个，数据量行是减少了很多， 但是有个问题，就是现在需要实时显示，尽力控制延时。如果这些存的话，客户端从数据库中，读数据的话，需要把一行的string 转换为 50个数字 后，再显示，会存在解压的时间导致显示延迟的问题。 貌似容量和延时会存在一定的矛盾啊
conger_eel 2016-08-01
  • 打赏
  • 举报
 回复
引用 12 楼 yenange 的回复:
[quote=引用 11 楼 conger_eel 的回复:] [quote=引用 9 楼 yenange 的回复:] 每天一个表吧。 用 sql 作业， 每天凌晨23:59:59 时将此表改名为当天的日期， 并创建一个新表就是了。
谢谢，这个时候就有个问题这个23:59.59分钟同时又读写操作啊，这时候还有数据进来啊[/quote] 晚上这个时候的读操作不会太多了， 影响不大的， 有影响也只是一瞬间。 有数据进来， 最多也就是最后一秒钟的数据进了第二天的表， 可以在那个作业中处理： 在更改表名后用 delay 等待 1 分钟， 然后将漏网的数据插入回前一天的表中， 并在当前表删除漏网的数据。 [/quote] 非常感谢。
吉普赛的歌 2016-07-31
  • 打赏
  • 举报
 回复
引用 11 楼 conger_eel 的回复:
[quote=引用 9 楼 yenange 的回复:] 每天一个表吧。 用 sql 作业， 每天凌晨23:59:59 时将此表改名为当天的日期， 并创建一个新表就是了。
谢谢，这个时候就有个问题这个23:59.59分钟同时又读写操作啊，这时候还有数据进来啊[/quote] 晚上这个时候的读操作不会太多了， 影响不大的， 有影响也只是一瞬间。 有数据进来， 最多也就是最后一秒钟的数据进了第二天的表， 可以在那个作业中处理： 在更改表名后用 delay 等待 1 分钟， 然后将漏网的数据插入回前一天的表中， 并在当前表删除漏网的数据。
conger_eel 2016-07-30
  • 打赏
  • 举报
 回复
引用 9 楼 yenange 的回复:
每天一个表吧。 用 sql 作业， 每天凌晨23:59:59 时将此表改名为当天的日期， 并创建一个新表就是了。
谢谢，这个时候就有个问题这个23:59.59分钟同时又读写操作啊，这时候还有数据进来啊
conger_eel 2016-07-30
  • 打赏
  • 举报
 回复
引用 7 楼 ap0405140 的回复:
[quote=引用 5 楼 conger_eel 的回复:] [quote=引用 2 楼 ap0405140 的回复:] 每天3亿条数据,算下来每秒写入记录3000多次.. 建议分服务器存储.
数据量积累到一定量就 搬到其他服务器上去？ 这个究竟多大需要搬移？ 能否具体一点？谢谢[/quote] 请测试每秒写入3000多次情况下,服务器的磁盘IO能力是否能应付. 如果不能,则需拆分为多台服务器分担IO压力,例如每10个传感器的数据存在一个服务器上,这样需要6台服务器. [/quote] 可以改为每1秒钟 写入3000个数据（先把数据缓存下来再写的)，而不是1秒钟写入3000次的，利用sqlbulkcopy 貌似效率还可以。算了一下，每个数据就是20Byte 数据，总共一秒钟就56k的数据量，sqlserver 应该可以承受这样的I/O 操作。 初步想法，因为需要设计到读实时数据的问题，需要考虑到一定的实时性（有一定的延迟是可以忍受的），需要临时存放数据的地方，定期的把临时数据读取并存储到历史数据库， 并数据汇总整理。 现在的有几个问题： 1. 临时存放的地方，是建一个临时表好，还是建一个临时库好？看资料，临时表在会话结束后不就消失了，那如何在临时表读数据呢？如何保证临时表一直存在呢？ 2. 定期去读临时数据后把数据删除的问题，因为采集程序一直再写这个临时表，同时读写删除会不会有问题，存在死锁的问题？ 3. 历史数据库的设计问题，毕竟这个数据量很大，如何设计使得查询的效率高些？想到的分表，分区的方法，究竟采用哪种方式更好些呢？ 问题太多了，请大虾们指导，谢谢！
吉普赛的歌 2016-07-30
  • 打赏
  • 举报
 回复
每天一个表吧。 用 sql 作业， 每天凌晨23:59:59 时将此表改名为当天的日期， 并创建一个新表就是了。
conger_eel 2016-07-29
  • 打赏
  • 举报
 回复
引用 7 楼 ap0405140 的回复:
[quote=引用 5 楼 conger_eel 的回复:] [quote=引用 2 楼 ap0405140 的回复:] 每天3亿条数据,算下来每秒写入记录3000多次.. 建议分服务器存储.
数据量积累到一定量就 搬到其他服务器上去？ 这个究竟多大需要搬移？ 能否具体一点？谢谢[/quote] 请测试每秒写入3000多次情况下,服务器的磁盘IO能力是否能应付. 如果不能,则需拆分为多台服务器分担IO压力,例如每10个传感器的数据存在一个服务器上,这样需要6台服务器. [/quote] 是的，这个需要测试一下。谢谢指导。 不过条件不允许，只有一台服务器。 如果这条路走不下去，就有点困难啊
加载更多回复（7）
疑难问题

22,210

社区成员

121,729

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章