室内温度监控的 mysql 数据库设计

[kayseen]

需求:
室内温度实时监控

1.展示近 30 分钟内的实时室内温度(比如 11:30, 11:31 的温度分别是多少)
2.展示同一时刻的近 7 天的温度变化(比如现在是 11:30,需要展示的是往前共计 6 天的 11:30 的温度)


请教下这种需求的数据库应该怎么设计啊?
要展示近七天历史的每分钟的室内温度,总不能在数据库给每分钟都建立一个字段吧....请大家赐教

[OctopusGO]

这个记录分钟不可以么？

[mjVtb96d2bap2u3Z]

id|create_date|temp 不就完事了？

[ycz0926]

nosql 不就行了

[lolizeppelin]

直接用 pg 的 timescaledb 啊,直接 time bulk 处理
这种最适合时序数据库了,不要坚持 mysql 了,不行的

[chendy]

时序数据
模仿时序数据库的思路做就行了

[kayseen]

@OctopusGO 记录分钟的话,每小时 60 分钟,每天 24 小时,这字段有点多吧......

[gavindexu]

@kayseen 一个 datetime 字段就行了啊，
自增 id，时间字段，温度，［温度记录仪放置位置］，再加一个 timestamp 的录库时间👀

[crayygy]

id; datetime; temperature

总感觉是不是对数据库的表结构有什么误解，日期和分钟只是一个时间数据，为什么需要每个分钟都建一个字段，难道我建立一个学生表还需要把每个学生的名字建一个字段不成？

[newtype0092]

你开个字段记时间戳就好了啊，为什么需要每分钟都建立一个字段？
看你应该是不太懂时间戳，一般这种记录都要带时间戳的，以后取的时候根据规则过滤就好了，mysql 用 from_unixtime 和 unix_timestamp 之类的函数可以很方便的转换。

[whatever93]

Influxdb 时序数据库

[Raymon111111]

其实是时序需求, 有专门的存储搞这个的

但是够简单, 数据足够小, mysql 一样可以

字段就是 id, time, temp

两个取的需求, 一个是 time 三十分钟内

第二个简单做直接 in 就行 time in ( now, now-secOneDay, now-secOneDay*2....)

[kayseen]

@Raymon111111

@newtype0092
@crayygy

@crayygy
这样建字段的话，就是一个房间，每分钟记录一条数据，每天记录 1440 条数据，如果房间在 20 左右的话，MySQL 压力可以吗？

[loading]

时分秒各一列，这样你写 sql 也简单。

[snappyone]

@kayseen 一天 3 万条能有啥压力

[opengps]

看我博客，最近重新提过，用关系型数据库模拟时序数据库效果，只有 2 列就可以，timestamp 列和数值列即可

[xduanx]

参考 zabbix 的数据库设计

[xduanx]

zabbix 里的 history_uint 表里就是每个几秒记一条记录

[gainsurier]

time tag_name value status

[Huelse]

一天 1440 分钟，一年也就 525,600 次记录，这点数据完全受的住

[kayseen]

@opengps 可以提供下博客地址吗？我在 CSDN 上没看到你的这篇文章。。谢谢

[newtype0092]

@kayseen 你这点数据谈不上压力

[KomeijiSatori]

@kayseen MySQL 什么时候连这点数据都顶不住了

[opengps]

@kayseen
[分享一个我用过的压测 SqlServer 写入能力的方法代码]( https://www.opengps.cn/Blog/View.aspx?id=422)
[mysql，sqlserver 数据库单表数据过大的处理方式]( https://www.opengps.cn/Blog/View.aspx?id=284)

[unixeno]

@kayseen 这点数据量根本谈不上压力，再加 2 个 0 都没问题
而且你这个也没有啥查询压力，对时间戳做个索引就可以了

[vincenttone]

每分钟记录一次没什么问题，60 * 24 * 7 = 10080 一周才一万条记录，就算你记上一年闰年也才 524040，50w 条。
如果你磁盘实在是小，那你起个脚本定时清理一下一周前的记录也就完事了，一直保持 10080 条就可以了。

[lolizeppelin]

不想改代码就用 maxwell 把数据实时转发就是,不影响线上记录相关代码
跳出来海阔天空啊！

[sayhier]

我再把问题延伸一下啊，其实这是个物联网问题，假设数据不止温度一个，也不止从一个地方发来数据，应该如何设计

[hjmeteor]

为什么不参考下气象探测（采集）呢？每一条记录固定格式，把字节分配下，比如时间（举例 YYYYMMDDHHMM ）、房间号、要素值，使用时读取、适当的转换即可。

[Huelse]

其实也可以考虑 nginx 那样的写日志文件

[GreyYang]

建议 influxdb. 这个需求比较合适.

[starsriver]

为什么要 mysql。

这么点数据 直接文件存

[Sasasu]

时序数据库都有很强的压缩，比直接用关系数据库能省至少 75% 的硬盘

[kayseen]

@GreyYang 这个数据库资料好少啊，请问在 python 中操作方便吗？或者在 flask 中操作方便吗？

[kayseen]

@sayhier 是的，物联网，除此之外会有什么温度湿度降水量之类的很多数据的。。

[GreyYang]

@kayseen python 操作方便, 参考: https://github.com/influxdata/influxdb-python ; 可视化可以使用原生的 chronograf https://www.influxdata.com/time-series-platform/chronograf/ ; 整套解决方案可以使用 tick 技术栈: telegraf + influxdb + chronograf + kapacitor, 满足数据采集, 存储, 时序数据分析, 报警, 可视化整套需求. 缺点: 集群收费...很贵. 不过单机性能已经很强了, 一般数据量不是特别大应该 ok.

[autogen]

我觉得 excel 都可以。。。

[AlphaTr]

@sayhier #27 time、group，type、value ；时间、分组对应地方等、type 对应类型（温度，湿度）、值：类型数据

[Pythondr]

时序数据库，influxDB

[ninjachen]

其实他除了原始数据还有个统计需求。
你先记录原始数据，然后每分钟跑个 cron 统计上一分钟的数据，记在新表里，他们业务是不一样的，用 rdbms 的话不能存一起。

[daozhihun]

如果你的项目要用到 MySQL，不建议楼上有些人说的那样另搞一个时序数据库，反而增加复杂度。这点数据量没什么压力，顶多一年一次给旧数据挪窝就行。
如果没有用到 MySQL，倒是可以考虑用类时序数据库来存储。

[deepdark]

这么点数据，txt 都存了

[code2019]

influxdb 时序数据库了解一下

[silvernoo]

rrdtool

[CallMeReznov]

@opengps #23 好东西啊,兄弟

[himesens]

一分钟存一行，至于怎么取其中某个站点最新 30 行，和隔 1440 行取一条，后台处理，一分钟刷一次，表字段越少越好。你甚至可以一次查完七天近万条数据循环+1 解决，整个处理时间都不会超过 1 秒。

[leafre]

有难点吗？

[Aresxue]

当前没必要引入时序数据库，less is more。
mysql 的话就按一分钟一条数据存储好了，如果后期数据量真的大起来，那就把近来七天的数据作为热点数据放到缓存里，过期时间设为七天，性能这方面完全不会有啥问题。

[EricInBj]

用 influxdb

[keakon]

clickhouse

[zjyl1994]

正常存 datetime 就行了，select 的时候提前算好了对应的时间点进去 wherein 就能提出来对应的数据

[GTim]

楼上各位推荐 influxdb 是认真的吗？？？？？？

如果是精确到分，只有一个温度计，完全可以用 bigint(20) 来表示时间 1909091010

算上温度 float 或者 decimal 就算 1000w 条数据，空间也是小的可怜好吗

如果不止一个设备，再加一个 did 设备编号，8 + 4 + 4 字节 = 16 字节

[GTim]

@Aresxue 数据量根本就不大，这个表最多只有 5 个字段，自增 id + 时间戳 + 温度 + date(ymdhi) + 设备 id 只需要 24 字节，1g 理论上能存 4kw 数据

[Aresxue]

@GTim 这里只是假设，比如说忽然要求细粒度到 ms，或者后续可能远远不止温度，像物联网有烟感、雾感等生活上方方面面的指标。

[GTim]

@Aresxue 细腻到 ms 或 ns 也就是 bigint 的问题，时间戳 * 1000 或者 1000000 就可以了，至于烟感、雾感，不能放这个表，不然插入太频繁会掉性能。

[w516322644]

@crayygy 这有啥误解的，数据多的时候，你时间戳还得处理下，有分钟字段的话，可以直接查。

[JosephHan]

很简单的需求, 不就是一个表记录 id, room_id, datetime, temperature 吗? 每分钟一次的记录, mysql 哪里承受不了这点数据了? 我自己用的还记录了湿度和气压呢, 完全没问题.

[ZXCDFGTYU]

时序数据库足够了，mysql 的话就按楼上说的就 ok

[Aresxue]

@GTim 我说的 ms、ns 是针对你回复我数据量不大的，改成 ms、ns 主要是数据量的上升，比如 ms 就是 60*100 倍的数据量了，这时候数据量就很可观了，要考虑下性能的问题了。烟感雾感也只是举例子后续可能的扩展性问题，当然不可能直接加进去，但是你一张表只放一类数据对于 mysql 来说是很浪费的。

[ai88030669]

我觉得 你问的东西 有问题 我想你问的是 sql 怎么写吧...

[skyqqcc]

感谢回复者 Reply 56
JosephHan 5 小时 44 分钟前
很简单的需求, 不就是一个表记录 id, room_id, datetime, temperature 吗? 每分钟一次的记录, mysql 哪里承受不了这点数据了? 我自己用的还记录了湿度和气压呢, 完全没问题.


sql 怎么写？也很简单，要取一分钟的直接按时间来就行了。

去间隔 5 分钟的？当前分钟数%5=余数
where datetime%5=余数

以此类推（还有更复杂，更简单的写法吧，只是哥哥不会。。）

[kayseen]

@deepdark
@starsriver
请问,如果直接存 txt 文件的话,我也给每一行数据分配了 datetime 字段,
把每天的数据存为一个文件,那么怎么筛选出 txt 文件中的昨天和前天的同一时刻的温度呢?
文件操作可以筛选时间吗?

[quadrapop]

5 秒钟一条数据，现在表已经 2 亿数据了。。 也是 mysql

[www5070504]

推荐一波 influxdb 时序的

[starsriver]

使用 xml 或 json 结构化的文档存储信息。不是说直接一行一条数据。

[liang2u]

@kayseen 没说全存一个文件里面，按天存或者按小时存，自己定规则，自己写逻辑去读，时间点对应的行号都可以根据自己的规则去推导，程序员的乐趣不就是折腾。

[0NF09LJPS51k57uH]

Influxdb 或者 prometheus

[MonoLogueChi]

一分钟记录一条，怕机器顶不住就半年分一次表

[a852695]

我觉得这个问题是很简单，忍不住了，还是自己看看什么是数据库？

[leavic]

感觉楼主是来骗毕业设计的

[cshlxm]

试试 tdengine 国产的开源时序数据库，看上去很厉害

[irvinghua]

你们楼上，都无视部署、开发成本啊，动不动就推荐重量级的大型数据库。10 年累计下来也只要: 60*24*365*10=525.6W 条，就这点小数据量 MySQL 分表都不用，毫无压力

[irvinghua]

人家自己想动手 DIY 做个小玩具，你叫别人买七轴联动的高精尖机床加工的既视感

[Breadykid]

完全不用关系型数据库，nosql 或者缓存都行

[evlos]

用 influxdb 吧