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

[Zaden]

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

目前方案是我将 id 和 push_time 排序然后加序号建了一张临时表，然后自连接，用 t1.序号+1=t2.序号的条件计算超过 24h 的记录，然后过滤 id 不相同记录

[Moyyyyyyyyyyye]

脚本任务跑一天也没关系吧

[gwy15]

每次推送的时候 redis 标记一下，SET EX GET 。如果不存在，根据你的需求判断是第一次推送还是上次推送超过 24h 。

你没说你的 point_id 的数量级和每个 point_id 的推送频率，但是每次推送你都写 mysql ，那估计数量级也不大，直接 redis 存一下是比较简单的

[akira]

按 point_id 和 push_time 做 一下时分 汇总统计， 然后再二次处理就简单了

[dingyaguang117]

再加个表记吧

[Zaden]

@Moyyyyyyyyyyye 限时任务，有 20 多张表，按现在效率估计要跑一两周

[Zaden]

@gwy15 谢谢，我是做数据分析的，存储过程不需要参与

[Zaden]

@akira 请教一下，啥叫时分汇总统计

[Zaden]

@dingyaguang117 啥叫表记，我自学的 SQL ，有些简称看不懂

[dode]

SSD 硬盘

[ys1992]

如果是数据已经写好，不想改动业务表的情况下，要计算相邻推送的时间间隔这个就是纯读取相邻两条数据，然后计算推送时间间隔，这种必然要扫描全表的，那感觉可以按照时间顺序使用流式的方式读一遍全表，然后用程序计算好了，回写一张独立的推送间隔表，如果有增量数据那就记录一下末次处理的业务表 id ，然后基于记录的业务 id 定时扫表，或者监听 binlog 转 kafka 这种消息方式
如果业务表支持改造增加一个字段写入的时候就直接记录推送间隔就好了
有了推送间隔，后续基于此做分析可以使用纯 SQL 就能搞定

[Zaden]

@ys1992 目前方案是我将 id 和 push_time 排序然后加序号建了一张临时表，然后自连接，用 t1.num+1=t2.num 的条件计算超过 24h 的记录

[Zaden]

@dode 已经是 pcie3 ssd 了

[hellomsg]

这么多数据做数据分析还是换个存储吧。用 mysql 分析不影响服务吗？

[t3zb2xzvjm4yvmn]

不能理解 OP 目前的方案，排序+错位自连接？

窗口函数性能更好，而且可以用 point_id+push_time 加索引
max(push_time) - min(push_time) over(partition by point_id) 计算出来时长，再套子查询筛选出超过 24h 的即可

[Zaden]

@hellomsg #13 嗯，没在生产库里分析

[Zaden]

@t3zb2xzvjm4yvmn #14 谢谢，用了 lag 窗口函数，感觉性能也一般，我试试你的方案

[t3zb2xzvjm4yvmn]

@Zaden 不好意思，前面理解有误，确实要用 lag 窗口函数

[512357301]

用 clickhouse

[Zaden]

@512357301 #18 列式数据库对我是个新概念，我去学习下

[wenxueywx]

加个字段 last_push_time 记录上一次推送时间，insert 时可以通过触发器查询上一次该 point_id 的 push_time 来更新

[wxf666]

@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;
```

[Zaden]

为啥会这么快，2kw 数据，有索引，我执行最简单的 select point_id,max(push_time) from table group by point_id 都要好几分钟

[wxf666]

@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 秒截图

[ys1992]

@wxf666 #23 大佬动手能力真强呀，这个和之前 1brc( https://zhuanlan.zhihu.com/p/683955185)有异曲同工之处了，反正按照 po 主的意思，顺序扫一遍全表，然后内存计算推送时间过滤出超过 24 小时的，时间应该是快的，不过 sqllite 纯 SQL 还能这么快，还是挺让人震撼的(之前没怎么接触过 sqllite ，都是 pg 和 mysql 多一点)

[LiaoMatt]

@Zaden 有联合索引吗, 还得看看数据库的配置