我有个 mysql8 库,里面有若干张物联网推送数据表,每张表有上千万至上亿条数据,这次涉及表里两个字段 point_id 和 push_time ,想检索历史上有哪些 point_id 断线时间(即期间无 push_time )超过 24h ,我试过用 mysql8 的窗口函数、python 游标读写表感觉效率都不太高,想请教大佬有无高效的办法
1
Moyyyyyyyyyyye 170 天前
脚本任务跑一天也没关系吧
|
2
gwy15 170 天前 1
每次推送的时候 redis 标记一下,SET EX GET 。如果不存在,根据你的需求判断是第一次推送还是上次推送超过 24h 。
你没说你的 point_id 的数量级和每个 point_id 的推送频率,但是每次推送你都写 mysql ,那估计数量级也不大,直接 redis 存一下是比较简单的 |
3
akira 170 天前
按 point_id 和 push_time 做 一下时分 汇总统计, 然后再二次处理就简单了
|
4
dingyaguang117 170 天前 via iPhone
再加个表记吧
|
5
Zaden OP @Moyyyyyyyyyyye 限时任务,有 20 多张表,按现在效率估计要跑一两周
|
8
Zaden OP @dingyaguang117 啥叫表记,我自学的 SQL ,有些简称看不懂
|
9
dode 170 天前
SSD 硬盘
|
10
ys1992 170 天前 1
如果是数据已经写好,不想改动业务表的情况下,要计算相邻推送的时间间隔这个就是纯读取相邻两条数据,然后计算推送时间间隔,这种必然要扫描全表的,那感觉可以按照时间顺序使用流式的方式读一遍全表,然后用程序计算好了,回写一张独立的推送间隔表,如果有增量数据那就记录一下末次处理的业务表 id ,然后基于记录的业务 id 定时扫表,或者监听 binlog 转 kafka 这种消息方式
如果业务表支持改造增加一个字段写入的时候就直接记录推送间隔就好了 有了推送间隔,后续基于此做分析可以使用纯 SQL 就能搞定 |
11
Zaden OP @ys1992 目前方案是我将 id 和 push_time 排序然后加序号建了一张临时表,然后自连接,用 t1.num+1=t2.num 的条件计算超过 24h 的记录
|
13
hellomsg 169 天前
这么多数据做数据分析还是换个存储吧。用 mysql 分析不影响服务吗?
|
14
t3zb2xzvjm4yvmn 169 天前 1
不能理解 OP 目前的方案,排序+错位自连接?
窗口函数性能更好,而且可以用 point_id+push_time 加索引 max(push_time) - min(push_time) over(partition by point_id) 计算出来时长,再套子查询筛选出超过 24h 的即可 |
16
Zaden OP @t3zb2xzvjm4yvmn #14 谢谢,用了 lag 窗口函数,感觉性能也一般,我试试你的方案
|
17
t3zb2xzvjm4yvmn 169 天前
@Zaden 不好意思,前面理解有误,确实要用 lag 窗口函数
|
18
512357301 168 天前 via Android 1
用 clickhouse
|
20
wenxueywx 168 天前
加个字段 last_push_time 记录上一次推送时间,insert 时可以通过触发器查询上一次该 point_id 的 push_time 来更新
|
21
wxf666 165 天前
@ys1992 #10 不一定要扫全表吧。。
不断根据索引,查最接近 24 小时前的推送时间,应该只需要检查很少数据量,就能算出来了? @Zaden 我用最垃圾的 SQLite ,在七年前的 i5-8250U 轻薄本上,效率一般的浏览器 wasm 环境里,试了下, 100 设备、一亿数据(每分钟推送、持续两年),每设备断线十次,每次 1~2 天, 只需 7 秒,就能全找出来了? ## 截图 ## SQL 测试代码 ```sql -- V 站吞空格,缩进改成全角空格了 -- 建表,当 (point_id, push_time) 索引用 DROP TABLE IF EXISTS data; CREATE TABLE data ( point_id INT, push_time INT, PRIMARY KEY (point_id, push_time) ) WITHOUT ROWID; -- 添加一亿条数据( 100 设备、每分钟推送、持续两年) INSERT INTO data SELECT point.value, time.value FROM generate_series(1, 100) point JOIN generate_series( unixepoch('2024-05-24', '-2 year'), unixepoch('2024-05-24'), 60) time; -- 删掉断线数据( 100 设备,每台断线 10 次,第 N 次是 id*N 天前,持续 1, 1.1, ..., 1.9 天) DELETE FROM data WHERE (point_id, push_time) IN ( SELECT point_id, push_time FROM generate_series(1, 100) point JOIN generate_series(1, 10) nth CROSS JOIN data WHERE point_id = point.value AND push_time >= unixepoch('2024-05-24', format('-%f day', nth.value * (point.value + 0.1) - 0.1)) AND push_time < unixepoch('2024-05-24', format('-%f day', nth.value * point.value - 1)) ); -- 循环每个设备,从今天开始,不断往前找,最接近 24 小时前的推送时间 -- 若俩时间 >= 24 小时,则属于断线过久 WITH RECURSIVE t(id, a, b) AS ( SELECT point_id, unixepoch('2024-05-24'), NULL FROM data GROUP BY point_id UNION ALL SELECT id, ifnull(( SELECT min(push_time) FROM data WHERE point_id = t.id AND push_time > t.a - 86400 AND push_time < t.a ), ( SELECT max(push_time) FROM data WHERE point_id = t.id AND push_time < t.a - 86400 )), a FROM t WHERE a ) SELECT id "设备 ID", datetime(a, 'auto') "最后在线", format('%d 天 %d 小时 %d 分钟', (b-a)/86400, (b-a)%86400/3600, (b-a)%3600/60) "断线时长" FROM t WHERE b - a >= 86400 ORDER BY id IN (1, 2, 73) DESC, id, a DESC; ``` |
22
Zaden OP 为啥会这么快,2kw 数据,有索引,我执行最简单的 select point_id,max(push_time) from table group by point_id 都要好几分钟
|
23
wxf666 164 天前 1
@ys1992 #10
我又改了改,速度提升到,仅 0.2 秒了。。 原来大部分时间,都在遍历这张一亿数据的表,查有哪些独立的 point_id 了。。 如果有 point 表,直接从里面抽出所有 point_id 即可。我是手动用 generate_series(1, 100) 模拟的。 @Zaden #22: 可能你和我原因一样,想分组 point_id ,查最后时间,数据库居然扫全索引去了。。 其他快的原因,我觉得就是,不断跳来跳去,直接查最接近 24 小时前的时间,而不是每两条时间一一对比。 这需要高度依赖 4K 随机读取。比如,浏览器里运行数据库时,会将数据留存在内存里,自然快一些。 - 本地上测试,同样缓存在内存里时,只需 0.1 秒 - 7 年前垃圾固态上(顺序读 420 MB/s ,4K 随机读 25 MB/s ),且清除系统对文件缓存后,需 10 秒 - 10 年前硬盘上(顺序读 150 MB/s ,4K 随机读 0.65 MB/s ),也清除系统对文件缓存后,需 80 秒。。 我也不知道,为啥硬盘 4K 随机读,比固态差近 40 倍,但耗时才慢 8 倍。。 可能夹杂着一些顺序读取(比如有时跳到相邻页上了?),使得差距没这么大吧。。 总之,我浏览器里都能缓存一亿数据(约 1.3 GB ),对你来说应该也不是啥难事的。 ## 0.2 秒截图 |
24
ys1992 162 天前
@wxf666 #23 大佬动手能力真强呀,这个和之前 1brc( https://zhuanlan.zhihu.com/p/683955185)有异曲同工之处了,反正按照 po 主的意思,顺序扫一遍全表,然后内存计算推送时间过滤出超过 24 小时的,时间应该是快的,不过 sqllite 纯 SQL 还能这么快,还是挺让人震撼的(之前没怎么接触过 sqllite ,都是 pg 和 mysql 多一点)
|