[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 的话放弃吧

[tf2]

考虑把 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 来做,数据上报已经做完了

[urnoob]

时间一大 传统 db 怎么都快不起来。
这需求就和记 metrics 一模一样。那种怎么做你参考着来就好了

[spike01]

timestamp 加一个 brin 索引试试？

[superares]

来源 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]

统一回复，最后通过写聊天记录到 clickhouse 暂时解决了。。。
clickhouse 的统计非常快。。。