设计： 一个统计设备上下线的方案

[lanqing]

假设有 10000 台设备，统计任意 A 天到 B 天的某设备 SN 的在线时长
我能收到每台设备的上线下线消息。
如何存储，如何查询，大佬们有啥方案么？

本人不才，设计到 mysql 存储每台设备每天的上下线消息，但是不管是查询还是设计，都感觉丑陋无比，有啥更好的方案么？

还要能查询出最近一次 在线时长，离线时长

[wangyuhang]

你现在有哪几个方案，说下

[dapang1221]

能保证一个上线消息必定会受到一个下线消息吗？能的话一条记录两个字段，上线时间、下线时间，用这俩一减。不能保证的话，做心跳包机制，收到消息的数量 * 心跳包间隔就是在线时间

[vcfghtyjc]

感觉问题描述的不太清楚呀。是要设计数据库，还是设计整个系统架构？

[opengps]

我也有过类似的设计，设计出来总感觉不完美，似乎关系型数据库用起来不匹配

[IvanLi127]

今日每台设备每次上下线消息存 redis 或者数据库，次日定时统计。redis 用 list 存上线时间 下线时间，list key 日期+设备编号，应该差不多了

[oott123]

首先做个数据库，redis 还是 mysql 不重要，把设备 ID 存进去，在线状态设为不在线
收到上线消息，去数据库里改成在线；下线消息同理

然后配一个 Promethues，监控项是每台设备的当前在线情况

搞完了，然后你想用什么时序数据库存想怎么查都可以

[xcstream]

定义任务 每天计算一下所有设备的在线时长

[lanqing]

@dapang1221 上线下线存一条消息，我担心 10000 台*100 天*10 次上下线 = 1000W 条数据，mysql 查询会不会有点性能问题

[lanqing]

@vcfghtyjc 能够存储，能够查询就行了

[lanqing]

@oott123 时序数据库能解决天到天的查询是么？我去看下

[scofieldpeng]

要统计在线时长，首先定义怎么判定在线这个过程，如果是设备会自动上报，

你只有 1w 各设备，mysql 一张表就能解决，

sn,first_report_time,last_report_time 这 3 个字段就可以完成你说的时长+统计，每个设备每天一条记录，当收到上报信息的时候，看下这张表 sn 有没有今日的记录，没有的话 insert,first_report_time,last_report_time 写入上报时间，有的话就只需要更新 last_report_time 就好了

楼上说的普罗米修斯啥的，真没必要

[scofieldpeng]

一天 1w 条记录，就算跑一年也才 365w 条记录，这点量，太小了，随便玩

[Slartibartfast]

你这个用 MySQL 足够了，如果太大，就按照时间或者设备 id 分库分表。

[4kingRAS]

id 一个表，device 一个表包括上线 timestamp 。下线 timestamp， 上下线的 log 一个表，记录 id，时间，动作。
统计用 log 表在 Java 里统计。

[silencegg]

CREATE TABLE `heart_beat` (
`sn` varchar(50) DEFAULT NULL COMMENT 'sn',
`start_time` varchar(50) DEFAULT NULL COMMENT '心态开始时间',
`end_time` datetime DEFAULT NULL COMMENT '心跳结束时间',
`online_time` bigint(20) DEFAULT NULL COMMENT '在线时长',
`creat_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`reason` varchar(50) DEFAULT NULL COMMENT '原因'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='心跳'

[silencegg]

这个是之前我用 EMQ 监听设备上下线的表来统计设备在线时长

[4kingRAS]

原来是担心一天内上下线次数太多啊，那就做个 trigger，log 表限制为只记录当日，0 点统计时长存入一个专门统计时间的表：id ,日期，第一次上线，最后一次下线，在线时长。虽然多了一个表，但是比较灵活。

[q149072205]

还有一个问题，如果第一天上线一直没下线 ，到第二天下线你要怎么统计

[Lee2019]

用 influxdb 之类的 tsdb 呢？
甚至直接写到 prometheus 里面？
{sn="xxx", active=1/0}这种？

[LeeReamond]

以天为单位展示就以天为单位储存，每天登录多长时间通过业务维护，整个系统很简单

[Kinnice]

下线时记录 时长

[zardly666]

我目前做的是以某台设备某天为一条数据存储。限定到某台机器，某一天的上下线信息都只更新这一条数据。
{
"date": "2021-03-26",
"online_sum": 40044000,
"device_sn": "xxxxxxxxxx",
"last_online_time": "2021-03-26 12:52:36",
"last_status": "online",
"merchant": "xxx"
}

[ch2]

用 mongo 正好，数据类型简单的话存再多也能 cache 的住无压力

[lanqing]

@zardly666 如果设备连续 3 天在线，这 3 天怎么统计的？

[lanqing]

@Lee2019 后续优化会去考虑 tsdb，目前公司业务没有用到这个 db，而且赶工期，所以不尝试，哭

[lanqing]

@silencegg 你这个能保证下线消息一定能够收到，或者能够处理成功么？如果不能，心跳结束时间永远不会更新

[zardly666]

@lanqing 忘记说了。每天零点有一个调度任务：
①把零点时前一天最后状态仍为在线的，用零点的时间戳减去前一天最后一次上线时间的时间戳，加和进前一天这条记录的在线时长。
②插入一条这台机器，时间为今天的新数据，以今天零点为上次在线时长戳，在线时长为 0.

[zardly666]

@lanqing 这样涉及有个弊端，就是想查看今天的在线时长，从记录查不出来，需要计算。

比如机器从昨天一直开机，在今天早上零点产生了一条数据，今天零点为上次在线时长戳，在线时长为 0.

需要用当前时间戳-今天凌晨的时间戳。

[redtea]

存 MongoDB，每隔几秒向服务器发送一次消息，超过时间视为离线。

[Maboroshii]

心跳吧, 频率越高越精准 。 丢失心跳视为下线

[dapang1221]

@lanqing 才 1kw 条数据，太小看 mysql 了。。实在不行就分表，1w 台按照 id 平均分到 10~100 个表里，而且冷数据也能定期清掉或转移

[securityCoding]

往高大上的方向整就对了

[joesonw]

influxdb 每在线 5 分钟, 插一条. 随便怎么统计.

[owenliang]

spark 处理一下就好

[useben]

心跳统计

[uselessVisitor]

我们是冗余了一张最新设备信息表

[LeeReamond]

@dapang1221 1kw 慢明显是没优化，不用嘲讽楼主了。。不过话说回来 mysql 确实跟同类产品比性能不行啊，pg 和 oracle 大概 1kw 数据没优化硬顶也能顶住吧

[Lee2019]

@lanqing 不过感觉你们的场景天然就该用 tsdb

[SHSF]

没人提 MQTT 吗