V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
Zaden
V2EX  ›  MySQL

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

  •  
  •   Zaden · 66 天前 · 1774 次点击
    这是一个创建于 66 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    第 1 条附言  ·  65 天前
    目前方案是我将 id 和 push_time 排序然后加序号建了一张临时表,然后自连接,用 t1.序号+1=t2.序号的条件计算超过 24h 的记录,然后过滤 id 不相同记录
    25 条回复    2024-05-28 10:44:04 +08:00
    Moyyyyyyyyyyye
        1
    Moyyyyyyyyyyye  
       66 天前
    脚本任务跑一天也没关系吧
    gwy15
        2
    gwy15  
       65 天前   ❤️ 1
    每次推送的时候 redis 标记一下,SET EX GET 。如果不存在,根据你的需求判断是第一次推送还是上次推送超过 24h 。

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

    窗口函数性能更好,而且可以用 point_id+push_time 加索引
    max(push_time) - min(push_time) over(partition by point_id) 计算出来时长,再套子查询筛选出超过 24h 的即可
    Zaden
        15
    Zaden  
    OP
       64 天前
    @hellomsg #13 嗯,没在生产库里分析
    Zaden
        16
    Zaden  
    OP
       64 天前
    @t3zb2xzvjm4yvmn #14 谢谢,用了 lag 窗口函数,感觉性能也一般,我试试你的方案
    t3zb2xzvjm4yvmn
        17
    t3zb2xzvjm4yvmn  
       64 天前
    @Zaden 不好意思,前面理解有误,确实要用 lag 窗口函数
    512357301
        18
    512357301  
       64 天前 via Android   ❤️ 1
    用 clickhouse
    Zaden
        19
    Zaden  
    OP
       64 天前
    @512357301 #18 列式数据库对我是个新概念,我去学习下
    wenxueywx
        20
    wenxueywx  
       63 天前
    加个字段 last_push_time 记录上一次推送时间,insert 时可以通过触发器查询上一次该 point_id 的 push_time 来更新
    wxf666
        21
    wxf666  
       61 天前
    @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
        22
    Zaden  
    OP
       60 天前
    为啥会这么快,2kw 数据,有索引,我执行最简单的 select point_id,max(push_time) from table group by point_id 都要好几分钟
    wxf666
        23
    wxf666  
       60 天前   ❤️ 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 秒截图

    ys1992
        24
    ys1992  
       57 天前
    @wxf666 #23 大佬动手能力真强呀,这个和之前 1brc( https://zhuanlan.zhihu.com/p/683955185)有异曲同工之处了,反正按照 po 主的意思,顺序扫一遍全表,然后内存计算推送时间过滤出超过 24 小时的,时间应该是快的,不过 sqllite 纯 SQL 还能这么快,还是挺让人震撼的(之前没怎么接触过 sqllite ,都是 pg 和 mysql 多一点)
    LiaoMatt
        25
    LiaoMatt  
       57 天前
    @Zaden 有联合索引吗, 还得看看数据库的配置
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4931 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 88ms · UTC 08:03 · PVG 16:03 · LAX 01:03 · JFK 04:03
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.