V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
kayseen
V2EX  ›  Python

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

  •  
  •   kayseen · 2019-09-08 14:55:48 +08:00 · 8916 次点击
    这是一个创建于 1663 天前的主题,其中的信息可能已经有所发展或是发生改变。
    需求:
    室内温度实时监控
    
    1.展示近 30 分钟内的实时室内温度(比如 11:30, 11:31 的温度分别是多少)
    2.展示同一时刻的近 7 天的温度变化(比如现在是 11:30,需要展示的是往前共计 6 天的 11:30 的温度)
    
    
    请教下这种需求的数据库应该怎么设计啊?
    要展示近七天历史的每分钟的室内温度,总不能在数据库给每分钟都建立一个字段吧....请大家赐教
    
    74 条回复    2019-09-12 11:54:25 +08:00
    OctopusGO
        1
    OctopusGO  
       2019-09-08 14:59:00 +08:00 via Android
    这个记录分钟不可以么?
    ali727
        2
    ali727  
       2019-09-08 14:59:28 +08:00
    id|create_date|temp 不就完事了?
    ycz0926
        3
    ycz0926  
       2019-09-08 14:59:54 +08:00
    nosql 不就行了
    lolizeppelin
        4
    lolizeppelin  
       2019-09-08 15:02:32 +08:00   ❤️ 5
    直接用 pg 的 timescaledb 啊,直接 time bulk 处理
    这种最适合时序数据库了,不要坚持 mysql 了,不行的
    chendy
        5
    chendy  
       2019-09-08 15:02:34 +08:00
    时序数据
    模仿时序数据库的思路做就行了
    kayseen
        6
    kayseen  
    OP
       2019-09-08 15:02:52 +08:00
    @OctopusGO 记录分钟的话,每小时 60 分钟,每天 24 小时,这字段有点多吧......
    gavindexu
        7
    gavindexu  
       2019-09-08 15:09:53 +08:00 via iPhone
    @kayseen 一个 datetime 字段就行了啊,
    自增 id,时间字段,温度,[温度记录仪放置位置],再加一个 timestamp 的录库时间👀
    crayygy
        8
    crayygy  
       2019-09-08 15:11:28 +08:00 via Android
    id; datetime; temperature

    总感觉是不是对数据库的表结构有什么误解,日期和分钟只是一个时间数据,为什么需要每个分钟都建一个字段,难道我建立一个学生表还需要把每个学生的名字建一个字段不成?
    newtype0092
        9
    newtype0092  
       2019-09-08 15:15:08 +08:00
    你开个字段记时间戳就好了啊,为什么需要每分钟都建立一个字段?
    看你应该是不太懂时间戳,一般这种记录都要带时间戳的,以后取的时候根据规则过滤就好了,mysql 用 from_unixtime 和 unix_timestamp 之类的函数可以很方便的转换。
    whatever93
        10
    whatever93  
       2019-09-08 15:21:24 +08:00 via Android
    Influxdb 时序数据库
    Raymon111111
        11
    Raymon111111  
       2019-09-08 15:29:10 +08:00   ❤️ 1
    其实是时序需求, 有专门的存储搞这个的

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

    字段就是 id, time, temp

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

    第二个简单做直接 in 就行 time in ( now, now-secOneDay, now-secOneDay*2....)
    kayseen
        12
    kayseen  
    OP
       2019-09-08 15:37:03 +08:00 via Android
    @Raymon111111

    @newtype0092
    @crayygy

    @crayygy
    这样建字段的话,就是一个房间,每分钟记录一条数据,每天记录 1440 条数据,如果房间在 20 左右的话,MySQL 压力可以吗?
    loading
        13
    loading  
       2019-09-08 15:42:00 +08:00
    时分秒各一列,这样你写 sql 也简单。
    snappyone
        14
    snappyone  
       2019-09-08 15:50:21 +08:00 via Android
    @kayseen 一天 3 万条能有啥压力
    opengps
        15
    opengps  
       2019-09-08 16:02:03 +08:00 via Android
    看我博客,最近重新提过,用关系型数据库模拟时序数据库效果,只有 2 列就可以,timestamp 列和数值列即可
    xduanx
        16
    xduanx  
       2019-09-08 16:02:08 +08:00 via iPhone
    参考 zabbix 的数据库设计
    xduanx
        17
    xduanx  
       2019-09-08 16:04:21 +08:00 via iPhone
    zabbix 里的 history_uint 表里就是每个几秒记一条记录
    gainsurier
        18
    gainsurier  
       2019-09-08 16:08:08 +08:00
    time tag_name value status
    Huelse
        19
    Huelse  
       2019-09-08 16:29:46 +08:00
    一天 1440 分钟,一年也就 525,600 次记录,这点数据完全受的住
    kayseen
        20
    kayseen  
    OP
       2019-09-08 16:41:45 +08:00 via Android
    @opengps 可以提供下博客地址吗?我在 CSDN 上没看到你的这篇文章。。谢谢
    newtype0092
        21
    newtype0092  
       2019-09-08 16:58:02 +08:00
    @kayseen 你这点数据谈不上压力
    KomeijiSatori
        22
    KomeijiSatori  
       2019-09-08 17:38:35 +08:00
    @kayseen MySQL 什么时候连这点数据都顶不住了
    opengps
        23
    opengps  
       2019-09-08 17:41:54 +08:00   ❤️ 1
    @kayseen
    [分享一个我用过的压测 SqlServer 写入能力的方法代码]( https://www.opengps.cn/Blog/View.aspx?id=422)
    [mysql,sqlserver 数据库单表数据过大的处理方式]( https://www.opengps.cn/Blog/View.aspx?id=284)
    unixeno
        24
    unixeno  
       2019-09-08 17:42:05 +08:00 via Android
    @kayseen 这点数据量根本谈不上压力,再加 2 个 0 都没问题
    而且你这个也没有啥查询压力,对时间戳做个索引就可以了
    vincenttone
        25
    vincenttone  
       2019-09-08 18:17:12 +08:00
    每分钟记录一次没什么问题,60 * 24 * 7 = 10080 一周才一万条记录,就算你记上一年闰年也才 524040,50w 条。
    如果你磁盘实在是小,那你起个脚本定时清理一下一周前的记录也就完事了,一直保持 10080 条就可以了。
    lolizeppelin
        26
    lolizeppelin  
       2019-09-08 19:13:31 +08:00
    不想改代码就用 maxwell 把数据实时转发就是,不影响线上记录相关代码
    跳出来海阔天空啊!
    sayhier
        27
    sayhier  
       2019-09-08 20:45:00 +08:00 via iPhone
    我再把问题延伸一下啊,其实这是个物联网问题,假设数据不止温度一个,也不止从一个地方发来数据,应该如何设计
    hjmeteor
        28
    hjmeteor  
       2019-09-08 21:07:55 +08:00 via Android
    为什么不参考下气象探测(采集)呢?每一条记录固定格式,把字节分配下,比如时间(举例 YYYYMMDDHHMM )、房间号、要素值,使用时读取、适当的转换即可。
    Huelse
        29
    Huelse  
       2019-09-08 22:37:07 +08:00
    其实也可以考虑 nginx 那样的写日志文件
    GreyYang
        30
    GreyYang  
       2019-09-08 22:47:43 +08:00
    建议 influxdb. 这个需求比较合适.
    starsriver
        31
    starsriver  
       2019-09-08 22:48:07 +08:00 via Android
    为什么要 mysql。

    这么点数据 直接文件存
    Sasasu
        32
    Sasasu  
       2019-09-08 23:16:38 +08:00
    时序数据库都有很强的压缩,比直接用关系数据库能省至少 75% 的硬盘
    kayseen
        33
    kayseen  
    OP
       2019-09-08 23:46:42 +08:00 via Android
    @GreyYang 这个数据库资料好少啊,请问在 python 中操作方便吗?或者在 flask 中操作方便吗?
    kayseen
        34
    kayseen  
    OP
       2019-09-08 23:47:20 +08:00 via Android
    @sayhier 是的,物联网,除此之外会有什么温度湿度降水量之类的很多数据的。。
    GreyYang
        35
    GreyYang  
       2019-09-09 00:03:41 +08:00
    @kayseen python 操作方便, 参考: https://github.com/influxdata/influxdb-python ; 可视化可以使用原生的 chronograf https://www.influxdata.com/time-series-platform/chronograf/ ; 整套解决方案可以使用 tick 技术栈: telegraf + influxdb + chronograf + kapacitor, 满足数据采集, 存储, 时序数据分析, 报警, 可视化整套需求. 缺点: 集群收费...很贵. 不过单机性能已经很强了, 一般数据量不是特别大应该 ok.
    autogen
        36
    autogen  
       2019-09-09 00:22:50 +08:00
    我觉得 excel 都可以。。。
    AlphaTr
        37
    AlphaTr  
       2019-09-09 01:03:22 +08:00 via iPhone
    @sayhier #27 time、group,type、value ;时间、分组对应地方等、type 对应类型(温度,湿度)、值:类型数据
    Pythondr
        38
    Pythondr  
       2019-09-09 01:21:57 +08:00 via Android
    时序数据库,influxDB
    ninjachen
        39
    ninjachen  
       2019-09-09 03:40:13 +08:00 via Android
    其实他除了原始数据还有个统计需求。
    你先记录原始数据,然后每分钟跑个 cron 统计上一分钟的数据,记在新表里,他们业务是不一样的,用 rdbms 的话不能存一起。
    daozhihun
        40
    daozhihun  
       2019-09-09 07:01:50 +08:00 via Android
    如果你的项目要用到 MySQL,不建议楼上有些人说的那样另搞一个时序数据库,反而增加复杂度。这点数据量没什么压力,顶多一年一次给旧数据挪窝就行。
    如果没有用到 MySQL,倒是可以考虑用类时序数据库来存储。
    deepdark
        41
    deepdark  
       2019-09-09 07:47:44 +08:00 via Android
    这么点数据,txt 都存了
    code2019
        42
    code2019  
       2019-09-09 07:50:16 +08:00 via iPhone
    influxdb 时序数据库了解一下
    silvernoo
        43
    silvernoo  
       2019-09-09 08:11:03 +08:00
    rrdtool
    CallMeReznov
        44
    CallMeReznov  
       2019-09-09 08:47:04 +08:00
    @opengps #23 好东西啊,兄弟
    himesens
        45
    himesens  
       2019-09-09 09:19:14 +08:00
    一分钟存一行,至于怎么取其中某个站点最新 30 行,和隔 1440 行取一条,后台处理,一分钟刷一次,表字段越少越好。你甚至可以一次查完七天近万条数据循环+1 解决,整个处理时间都不会超过 1 秒。
    leafre
        46
    leafre  
       2019-09-09 09:30:59 +08:00
    有难点吗?
    Aresxue
        47
    Aresxue  
       2019-09-09 09:41:25 +08:00
    当前没必要引入时序数据库,less is more。
    mysql 的话就按一分钟一条数据存储好了,如果后期数据量真的大起来,那就把近来七天的数据作为热点数据放到缓存里,过期时间设为七天,性能这方面完全不会有啥问题。
    EricInBj
        48
    EricInBj  
       2019-09-09 09:43:08 +08:00
    用 influxdb
    keakon
        49
    keakon  
       2019-09-09 09:56:54 +08:00
    clickhouse
    zjyl1994
        50
    zjyl1994  
       2019-09-09 10:33:34 +08:00
    正常存 datetime 就行了,select 的时候提前算好了对应的时间点进去 wherein 就能提出来对应的数据
    GTim
        51
    GTim  
       2019-09-09 11:11:09 +08:00
    楼上各位推荐 influxdb 是认真的吗??????

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

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

    如果不止一个设备,再加一个 did 设备编号,8 + 4 + 4 字节 = 16 字节
    GTim
        52
    GTim  
       2019-09-09 11:20:57 +08:00
    @Aresxue 数据量根本就不大,这个表最多只有 5 个字段,自增 id + 时间戳 + 温度 + date(ymdhi) + 设备 id 只需要 24 字节,1g 理论上能存 4kw 数据
    Aresxue
        53
    Aresxue  
       2019-09-09 11:24:37 +08:00
    @GTim 这里只是假设,比如说忽然要求细粒度到 ms,或者后续可能远远不止温度,像物联网有烟感、雾感等生活上方方面面的指标。
    GTim
        54
    GTim  
       2019-09-09 11:30:41 +08:00
    @Aresxue 细腻到 ms 或 ns 也就是 bigint 的问题,时间戳 * 1000 或者 1000000 就可以了,至于烟感、雾感,不能放这个表,不然插入太频繁会掉性能。
    w516322644
        55
    w516322644  
       2019-09-09 11:33:38 +08:00
    @crayygy 这有啥误解的,数据多的时候,你时间戳还得处理下,有分钟字段的话,可以直接查。
    JosephHan
        56
    JosephHan  
       2019-09-09 11:35:04 +08:00
    很简单的需求, 不就是一个表记录 id, room_id, datetime, temperature 吗? 每分钟一次的记录, mysql 哪里承受不了这点数据了? 我自己用的还记录了湿度和气压呢, 完全没问题.
    ZXCDFGTYU
        57
    ZXCDFGTYU  
       2019-09-09 12:49:51 +08:00
    时序数据库足够了,mysql 的话就按楼上说的就 ok
    Aresxue
        58
    Aresxue  
       2019-09-09 14:09:33 +08:00
    @GTim 我说的 ms、ns 是针对你回复我数据量不大的,改成 ms、ns 主要是数据量的上升,比如 ms 就是 60*100 倍的数据量了,这时候数据量就很可观了,要考虑下性能的问题了。烟感雾感也只是举例子后续可能的扩展性问题,当然不可能直接加进去,但是你一张表只放一类数据对于 mysql 来说是很浪费的。
    ai88030669
        59
    ai88030669  
       2019-09-09 16:21:30 +08:00
    我觉得 你问的东西 有问题 我想你问的是 sql 怎么写吧...
    skyqqcc
        60
    skyqqcc  
       2019-09-09 17:23:54 +08:00 via Android
    感谢回复者 Reply 56
    JosephHan 5 小时 44 分钟前
    很简单的需求, 不就是一个表记录 id, room_id, datetime, temperature 吗? 每分钟一次的记录, mysql 哪里承受不了这点数据了? 我自己用的还记录了湿度和气压呢, 完全没问题.


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

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

    以此类推(还有更复杂,更简单的写法吧,只是哥哥不会。。)
    kayseen
        61
    kayseen  
    OP
       2019-09-09 17:29:31 +08:00
    @deepdark
    @starsriver
    请问,如果直接存 txt 文件的话,我也给每一行数据分配了 datetime 字段,
    把每天的数据存为一个文件,那么怎么筛选出 txt 文件中的昨天和前天的同一时刻的温度呢?
    文件操作可以筛选时间吗?
    quadrapop
        62
    quadrapop  
       2019-09-09 17:45:16 +08:00
    5 秒钟一条数据,现在表已经 2 亿数据了。。 也是 mysql
    www5070504
        63
    www5070504  
       2019-09-09 17:47:02 +08:00
    推荐一波 influxdb 时序的
    starsriver
        64
    starsriver  
       2019-09-09 18:38:41 +08:00 via Android
    使用 xml 或 json 结构化的文档存储信息。不是说直接一行一条数据。
    liang2u
        65
    liang2u  
       2019-09-09 18:45:23 +08:00 via Android
    @kayseen 没说全存一个文件里面,按天存或者按小时存,自己定规则,自己写逻辑去读,时间点对应的行号都可以根据自己的规则去推导,程序员的乐趣不就是折腾。
    phantomzz
        66
    phantomzz  
       2019-09-09 20:09:35 +08:00 via Android
    Influxdb 或者 prometheus
    MonoLogueChi
        67
    MonoLogueChi  
       2019-09-09 20:46:59 +08:00 via Android
    一分钟记录一条,怕机器顶不住就半年分一次表
    a852695
        68
    a852695  
       2019-09-09 21:08:07 +08:00
    我觉得这个问题是很简单,忍不住了,还是自己看看什么是数据库?
    leavic
        69
    leavic  
       2019-09-09 21:49:17 +08:00
    感觉楼主是来骗毕业设计的
    cshlxm
        70
    cshlxm  
       2019-09-09 22:12:52 +08:00
    试试 tdengine 国产的开源时序数据库,看上去很厉害
    irvinghua
        71
    irvinghua  
       2019-09-10 09:21:04 +08:00
    你们楼上,都无视部署、开发成本啊,动不动就推荐重量级的大型数据库。10 年累计下来也只要: 60*24*365*10=525.6W 条,就这点小数据量 MySQL 分表都不用,毫无压力
    irvinghua
        72
    irvinghua  
       2019-09-10 09:23:37 +08:00
    人家自己想动手 DIY 做个小玩具,你叫别人买七轴联动的高精尖机床加工的既视感
    Breadykid
        73
    Breadykid  
       2019-09-10 22:31:18 +08:00
    完全不用关系型数据库,nosql 或者缓存都行
    evlos
        74
    evlos  
       2019-09-12 11:54:25 +08:00
    用 influxdb 吧
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5417 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 39ms · UTC 08:43 · PVG 16:43 · LAX 01:43 · JFK 04:43
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.