mysql 如何高效获取两条相邻推送时间间隔

277 天前
Zaden  Zaden

我有个 mysql8 库,里面有若干张物联网推送数据表,每张表有上千万至上亿条数据,这次涉及表里两个字段 point_id 和 push_time ,想检索历史上有哪些 point_id 断线时间(即期间无 push_time )超过 24h ,我试过用 mysql8 的窗口函数、python 游标读写表感觉效率都不太高,想请教大佬有无高效的办法

2199 次点击
所在节点   MySQL  MySQL
25 条回复
Moyyyyyyyyyyye
Moyyyyyyyyyyye
277 天前
脚本任务跑一天也没关系吧
gwy15
gwy15
277 天前
每次推送的时候 redis 标记一下,SET EX GET 。如果不存在,根据你的需求判断是第一次推送还是上次推送超过 24h 。

你没说你的 point_id 的数量级和每个 point_id 的推送频率,但是每次推送你都写 mysql ,那估计数量级也不大,直接 redis 存一下是比较简单的
akira
akira
277 天前
按 point_id 和 push_time 做 一下时分 汇总统计, 然后再二次处理就简单了
dingyaguang117
dingyaguang117
277 天前
再加个表记吧
Zaden
Zaden
276 天前
@Moyyyyyyyyyyye 限时任务,有 20 多张表,按现在效率估计要跑一两周
Zaden
Zaden
276 天前
@gwy15 谢谢,我是做数据分析的,存储过程不需要参与
Zaden
Zaden
276 天前
@akira 请教一下,啥叫时分汇总统计
Zaden
Zaden
276 天前
@dingyaguang117 啥叫表记,我自学的 SQL ,有些简称看不懂
dode
dode
276 天前
SSD 硬盘
ys1992
ys1992
276 天前
如果是数据已经写好,不想改动业务表的情况下,要计算相邻推送的时间间隔这个就是纯读取相邻两条数据,然后计算推送时间间隔,这种必然要扫描全表的,那感觉可以按照时间顺序使用流式的方式读一遍全表,然后用程序计算好了,回写一张独立的推送间隔表,如果有增量数据那就记录一下末次处理的业务表 id ,然后基于记录的业务 id 定时扫表,或者监听 binlog 转 kafka 这种消息方式
如果业务表支持改造增加一个字段写入的时候就直接记录推送间隔就好了
有了推送间隔,后续基于此做分析可以使用纯 SQL 就能搞定
Zaden
Zaden
276 天前
@ys1992 目前方案是我将 id 和 push_time 排序然后加序号建了一张临时表,然后自连接,用 t1.num+1=t2.num 的条件计算超过 24h 的记录
Zaden
Zaden
276 天前
@dode 已经是 pcie3 ssd 了
hellomsg
hellomsg
276 天前
这么多数据做数据分析还是换个存储吧。用 mysql 分析不影响服务吗?
t3zb2xzvjm4yvmn
t3zb2xzvjm4yvmn
276 天前
不能理解 OP 目前的方案,排序+错位自连接?

窗口函数性能更好,而且可以用 point_id+push_time 加索引
max(push_time) - min(push_time) over(partition by point_id) 计算出来时长,再套子查询筛选出超过 24h 的即可
Zaden
Zaden
276 天前
@hellomsg #13 嗯,没在生产库里分析
Zaden
Zaden
276 天前
@t3zb2xzvjm4yvmn #14 谢谢,用了 lag 窗口函数,感觉性能也一般,我试试你的方案
t3zb2xzvjm4yvmn
t3zb2xzvjm4yvmn
276 天前
@Zaden 不好意思,前面理解有误,确实要用 lag 窗口函数
512357301
512357301
275 天前
用 clickhouse
Zaden
Zaden
275 天前
@512357301 #18 列式数据库对我是个新概念,我去学习下
wenxueywx
wenxueywx
275 天前
加个字段 last_push_time 记录上一次推送时间,insert 时可以通过触发器查询上一次该 point_id 的 push_time 来更新

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/1041992

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX