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

[dreamramon]

下面的 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
      }
    }
  }
]

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

[yule111222]

用时序数据库

[suxixi]

emm mysql 的话放弃吧

[lambdaq]

考虑把 timestamp 做成小时级别，然后给 timestamp_hour + tb_groupchat.chat_id 之类的做复合索引。这样避免扫表

[Shamiko]

@lambdaq 没用的，这个数据如果对实时性要求不高可以考虑物化视图

[opengps]

换个思路试试，比如牺牲点 io 量，第一次所有要筛选的数据，统计部分放倒内存二次计算

[xsir2020]

按时间进行表分区，
然后预计算吧

[xyjincan]

按小时算好，存起来

[matrix1010]

首先必须要精确 count 吗，比如超过 99 可以显示个 99+

[masterclock]

timescaledb ，应该可以

[Maboroshii]

按分钟或者小时算好，提前存起来是个好办法。按分钟的话，一天也就 1000 多条数据，精度不错速度也不慢。

这个查询的问题还是范围太大，数据太多。

[encro]

数据库没有建立好，用我这个方案，不用 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]

你这个统计信息，又不是需要实时更新的，不需要性能很高，做缓存就行。

count ，order by 达到一定数据后就是慢，所以应该尽量避免。

[lingalonely]

你这是实时需求还是报表需求，看数据一天在 100 万，一次性查一天一定会慢的，暴力解决就加内存，加缓冲区，一劳永逸就是换 OLAP 类的数据库

[lingalonely]

另外问下，单纯 count("public"."tb_groupchat"."id") 会慢吗

[RainCats]

精确度不高的话为啥不能跑结果表呢，然后统计的时候统计结果表就完事了

[hhjswf]

@lingalonely #14 count 要全盘扫描怎么整都慢

[aloxaf]

点进来前还以为是来分享调优经验的（

[MoYi123]

精确的 count distinct 是没救的, 试试 hyperloglog 插件吧.

[Still4]

数据双写到 clickhouse ，用时间戳分区
clickhouse 也支持 mysql 引擎，这个没测过不清楚性能

[securityCoding]

233 一般来说这种数据不需要业务层 db 来做,数据上报已经做完了