V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
dreamramon
V2EX  ›  PostgreSQL

[pg15.2]一个亿级聊天记录表的调优

  •  
  •   dreamramon · 2023-05-31 11:16:18 +08:00 · 5247 次点击
    这是一个创建于 557 天前的主题,其中的信息可能已经有所发展或是发生改变。

    下面的 sql 查询非常慢。。。

    select count(distinct "public"."tb_groupchat"."chat_id"), count("public"."tb_groupchat"."id"), count(distinct "public"."tb_groupchat"."sender_id") from "public"."tb_groupchat" where ("public"."tb_groupchat"."timestamp" >= $1 and "public"."tb_groupchat"."timestamp" < $2)
    

    表结构:

      "id"                serial8 PRIMARY KEY NOT NULL,
      "chat_id"           int8                NOT NULL,
      "sender_id"         int8                NOT NULL,
      "timestamp"         int8                NOT NULL
    
    

    explain 的结果

    [
      {
        "Plan": {
          "Node Type": "Aggregate",
          "Strategy": "Plain",
          "Partial Mode": "Simple",
          "Parallel Aware": false,
          "Async Capable": false,
          "Startup Cost": 181904.15,
          "Total Cost": 181904.16,
          "Plan Rows": 1,
          "Plan Width": 24,
          "Output": ["count(DISTINCT chat_id)", "count(id)", "count(DISTINCT sender_id)"],
          "Plans": [
            {
              "Node Type": "Index Scan",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Async Capable": false,
              "Scan Direction": "Forward",
              "Index Name": "idx_timestamp",
              "Relation Name": "tb_groupchat",
              "Schema": "public",
              "Alias": "tb_groupchat",
              "Startup Cost": 0.43,
              "Total Cost": 172205.39,
              "Plan Rows": 1293168,
              "Plan Width": 24,
              "Output": ["id",  "chat_id", "sender_id", "content", "\"timestamp\""],
              "Index Cond": "((tb_groupchat.\"timestamp\" >= '1684944000000'::bigint) AND (tb_groupchat.\"timestamp\" < '1685030400000'::bigint))"
            }
          ]
        },
        "Query Identifier": 6892608323288585066,
        "JIT": {
          "Functions": 5,
          "Options": {
            "Inlining": false,
            "Optimization": false,
            "Expressions": true,
            "Deforming": true
          }
        }
      }
    ]
    

    不知道各位同学有没有啥高招。。。

    24 条回复    2023-06-02 14:55:06 +08:00
    yule111222
        1
    yule111222  
       2023-05-31 11:19:52 +08:00
    用时序数据库
    suxixi
        2
    suxixi  
       2023-05-31 11:23:00 +08:00
    emm mysql 的话放弃吧
    lambdaq
        3
    lambdaq  
       2023-05-31 11:30:18 +08:00
    考虑把 timestamp 做成小时级别,然后给 timestamp_hour + tb_groupchat.chat_id 之类的做复合索引。这样避免扫表
    Shamiko
        4
    Shamiko  
       2023-05-31 13:07:15 +08:00
    @lambdaq 没用的,这个数据如果对实时性要求不高可以考虑物化视图
    opengps
        5
    opengps  
       2023-05-31 13:12:27 +08:00
    换个思路试试,比如牺牲点 io 量,第一次所有要筛选的数据,统计部分放倒内存二次计算
    xsir2020
        6
    xsir2020  
       2023-05-31 13:20:27 +08:00
    按时间进行表分区,
    然后预计算吧
    xyjincan
        7
    xyjincan  
       2023-05-31 13:21:24 +08:00
    按小时算好,存起来
    matrix1010
        8
    matrix1010  
       2023-05-31 13:27:39 +08:00 via iPhone
    首先必须要精确 count 吗,比如超过 99 可以显示个 99+
    masterclock
        9
    masterclock  
       2023-05-31 13:30:07 +08:00
    timescaledb ,应该可以
    Maboroshii
        10
    Maboroshii  
       2023-05-31 13:47:47 +08:00 via Android
    按分钟或者小时算好,提前存起来是个好办法。按分钟的话,一天也就 1000 多条数据,精度不错速度也不慢。

    这个查询的问题还是范围太大,数据太多。
    encro
        11
    encro  
       2023-05-31 14:02:30 +08:00   ❤️ 1
    数据库没有建立好,用我这个方案,不用 count 。。。

    user:
    id,
    unread_msg

    msg:
    id,
    content,
    from_id,
    created_at


    chatgroup:
    id,
    title,
    created_at


    chatgroup_user:
    chatgroup_id,
    user_id,
    unread_msg ,
    last_read_id,
    created_at

    chatgroup_msg:
    msg_id,
    chatgroup_id,
    created_at
    encro
        12
    encro  
       2023-05-31 14:06:35 +08:00
    你这个统计信息,又不是需要实时更新的,不需要性能很高,做缓存就行。

    count ,order by 达到一定数据后就是慢,所以应该尽量避免。
    lingalonely
        13
    lingalonely  
       2023-05-31 14:17:55 +08:00
    你这是实时需求还是报表需求,看数据一天在 100 万,一次性查一天一定会慢的,暴力解决就加内存,加缓冲区,一劳永逸就是换 OLAP 类的数据库
    lingalonely
        14
    lingalonely  
       2023-05-31 14:21:26 +08:00
    另外问下,单纯 count("public"."tb_groupchat"."id") 会慢吗
    RainCats
        15
    RainCats  
       2023-05-31 14:48:09 +08:00
    精确度不高的话为啥不能跑结果表呢,然后统计的时候统计结果表就完事了
    hhjswf
        16
    hhjswf  
       2023-05-31 14:50:48 +08:00
    @lingalonely #14 count 要全盘扫描怎么整都慢
    aloxaf
        17
    aloxaf  
       2023-05-31 15:02:19 +08:00   ❤️ 2
    点进来前还以为是来分享调优经验的(
    MoYi123
        18
    MoYi123  
       2023-05-31 15:55:39 +08:00
    精确的 count distinct 是没救的, 试试 hyperloglog 插件吧.
    Still4
        19
    Still4  
       2023-05-31 19:24:55 +08:00
    数据双写到 clickhouse ,用时间戳分区
    clickhouse 也支持 mysql 引擎,这个没测过不清楚性能
    securityCoding
        20
    securityCoding  
       2023-05-31 19:31:17 +08:00
    233 一般来说这种数据不需要业务层 db 来做,数据上报已经做完了
    urnoob
        21
    urnoob  
       2023-05-31 19:51:00 +08:00 via Android
    时间一大 传统 db 怎么都快不起来。
    这需求就和记 metrics 一模一样。那种怎么做你参考着来就好了
    jerry1zeng
        22
    jerry1zeng  
       2023-05-31 20:12:09 +08:00
    timestamp 加一个 brin 索引试试?
    superares
        23
    superares  
       2023-06-01 08:33:55 +08:00
    来源 ChatGPT 4 ,听上去有点道理:
    ```
    你的查询慢可能是由于几个原因:

    1. 你正在计算三个不同的计数,其中两个使用 `DISTINCT`。这将要求数据库执行额外的工作来确认值的唯一性。在大数据集中,这可能会引发性能问题。

    2. 你在查询中使用了 `WHERE` 子句,这将对表的所有行进行扫描,以确定哪些行满足条件。这可能需要大量时间,特别是当表的行数非常大时。

    考虑到这些问题,我有以下建议:

    1. **索引:**根据 `EXPLAIN` 的结果,查询正在使用一个名为 `idx_timestamp` 的索引,但由于你的查询中涉及到 `chat_id` 和 `sender_id` 的 DISTINCT 计数,你可能需要为 `chat_id` 和 `sender_id` 创建索引,以加速这部分的计算。

    2. **预计算:**如果可能,你可以考虑预计算结果。例如,你可以创建一个触发器,在插入、更新或删除 `tb_groupchat` 表的行时更新计数。这样,你可以直接从预计算的值中读取计数,而不是在每次查询时计算它们。

    3. **优化 DISTINCT 计数:**对于 DISTINCT 计数,如果你能容忍一些误差,你可以使用一些近似 DISTINCT 计数的方法,例如使用 HyperLogLog 算法,它可以以较小的内存开销得到近似的结果。注意,这需要 PostgreSQL 9.4 或更高版本。

    4. **查询分解:**你可以考虑将查询分解为几个独立的查询,每个查询只计算一个计数。这可能会减少数据库的工作量,尽管它会增加应用程序的复杂性。

    5. **硬件升级:**如果上述方法都不能满足你的需要,可能需要考虑升级硬件,例如增加内存、使用更快的存储设备,或者增加处理器核心数。

    希望这些建议对你有所帮助。如果你能提供更多关于你的应用场景、数据量和性能要求的信息,我可能能提供更具体的建议。
    ```
    dreamramon
        24
    dreamramon  
    OP
       2023-06-02 14:55:06 +08:00
    统一回复,最后通过写聊天记录到 clickhouse 暂时解决了。。。
    clickhouse 的统计非常快。。。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2287 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 05:43 · PVG 13:43 · LAX 21:43 · JFK 00:43
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.