请教一个系统设计题

[zdking08135]

一个系统，客户端会上报统计点，上报本身不去重，报一次记一次：
uid ，地域（城市省份国家），打点时间（时间戳）。

这里定位服务保证地域唯一。

要求实现如下能力：

- 统计指定地域（最小城市维度，省份，国家也可以）与时间范围（天维度）内的单 uid 单日平均打点数
（结果是按天输出，每天一个指定地域平均数）。

- 查询支持实时 & 支持输入。

- 时间范围，最长 1 年。

- 地域支持多地域 or 查询，但是多地域出现的 uid 需要合并统计。（比如，用户同一天在 A,B 两地上报，如果查询条件为 A or B ，那么这个用户的打点数要合并计算）

指标：用户量 10 亿，单 uid 单日打点数 10~50 之间，城市范围覆盖到全球。

这里细化一点，假设城市、省份、国家就用 id 表示（城市不需要模糊查询），并且范围包括全世界。

希望给出更多的细节，表怎么组织，数据怎么组织，怎么做查询。

[wei2629]

找个时序数据库

[lsk569937453]

## 假设
- 存储每天 10 亿*50(次)*100byte=5TB(存储量太大，上 hbase 吧)
- 打点接口最多每秒访问次数:10 亿次
- 打点接口最多每秒占用的网络带宽:10 亿*100byte=100GB(万兆网卡可能不够用了)
- 单中心情况下网络延迟:地球上两点间最长距离为 20000 公里/光速(299792458m/s)=0.066s ，即单次请求的网络延时为 0.066*2=1.3(s)

## 系统设计
- 打点接口收到数据直接异步写入 kafka 集群,假设接口单次处理时间为 0.5ms,则单机 QPS 为 2000,处理 10 亿条数据需要的机器数量为:10 亿/2000=5w 台。
- 同时我们开线程从 kafka 集群读取数据,格式化后写入 HBase 集群。

### 数据库设计


Hbase 的 rowkey 设计为：地域+时间戳+uuid
- 统计指定地域:直接地域+时间范围全部查出来即可
- 地域支持多地域 or 查询:根据查询条件查询出来，将所有的数据写入到 kafka ，然后由 storm/spark/flink 做实时的统计，然后将结果写入到数据库中。

[llsquaer]

给你们老板说下，别来不来就 10 亿，先 10 万的开始

[thedinosaurmail]

clickhouse , 10 亿还好 ，按天分区就行

[zdking08135]

@lsk569937453
感谢老哥，这里不是每秒 10 亿次，保证一天能抗住 10 亿 * 50 次上报就行了，大约是 60w 的 qps ，这个不是重点。
重点是怎么支持查询。

--------------------

"根据查询条件查询出来，将所有的数据写入到 kafka ，然后由 storm/spark/flink 做实时的统计，然后将结果写入到数据库中。"

这里，如果想查比如上海+苏州范围，两地一共 2kw 用户，10 亿条记录
需要把上海和苏州的用户记录数据全部读出来，再写 Kakfa 做统计？

[zdking08135]

@llsquaer 系统设计题啦，不是实际业务，实际肯定会取舍。

[yjhatfdu2]

这个量，clickhouse 集群，做好表的设计，选好排序键、字段编码和压缩，按天分区，这个量写入和查询问题应该都不是很大。顺便，兄弟你这是在做天网么

[zdking08135]

@yjhatfdu2
@thedinosaurmail

用 clickhouse 的话，具体一点呢？表怎么设计？有哪些字段？
查询要怎么写？效率如何，可以实时吗？

[thedinosaurmail]

直接写 clickhouse 就行 ，不需要怎么设计设计
uid ，country ，province ，create_at

主要是要判断好按什么排序就行

[thedinosaurmail]

在使用 ClickHouse 进行表的设计时，针对您的需求，我们需要考虑如何优化存储和查询效率，尤其是面对大规模数据和复杂查询（如跨地域合并统计）。以下是一个基于您需求的示例表结构，包括了用户 ID 、打点时间、地域信息和打点数。

首先，考虑到数据量和查询需求，建议使用 MergeTree 系列引擎，它适用于大数据量的存储和分析，支持高效的数据插入和实时查询。

表结构设计
sql
Copy code
CREATE TABLE user_events
(
`event_date` Date,
`user_id` UInt64,
`city_id` UInt32,
`country_id` UInt32,
`event_count` UInt32,
`event_datetime` DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, country_id, city_id, user_id)
SAMPLE BY user_id
SETTINGS index_granularity = 8192;
字段解释：
event_date: 打点发生的日期，用于分区和快速过滤。
user_id: 用户的唯一标识符。
city_id: 城市的唯一标识符，需要有一个额外的映射表来解释每个城市 ID 对应的实际城市。
country_id: 国家的唯一标识符，同样需要一个映射表来详细说明。
event_count: 该用户在该日的打点数，考虑到您的业务场景，可能需要在数据插入前进行聚合计算。
event_datetime: 打点的具体时间点，支持精确到秒的时间戳，可用于进一步的时间序分析。
注意事项：
分区策略：根据 event_date 进行分区，可以有效地管理数据的存储和查询，尤其是对历史数据的分析。
排序键：通过(event_date, country_id, city_id, user_id)进行排序，优化查询性能，特别是当进行地域和时间范围的查询时。
采样：通过 SAMPLE BY user_id 支持对数据进行采样查询，适用于需要估算或快速分析的场景。
索引粒度：index_granularity 设置为 8192 ，这是一个平衡查询速度和存储效率的配置。根据实际数据量和查询模式，这个值可能需要调整。
多地域查询设计思路：
对于跨地域的统计分析，可以在查询时通过 GROUP BY 语句实现。例如，如果需要合并计算用户在同一天内不同城市（或国家）的打点数，可以通过将 user_id 和 event_date 作为聚合的关键字，然后对 event_count 求和。

[dlmy]

Log -> Kafka -> Flink ↓
--> ODS -> DWD -> DWM -> DWS -> ADS ↓
--> ClickHouse -> API ↓
--> Visualization Panel

看得懂这个，你就知道怎么做了

[yjhatfdu2]

clickhouse 造一天数据试试看，单机 64 核 epyc 256G ram
建表，目前试下来效率最高的表结构
create table test4
(
time datetime CODEC (DoubleDelta, LZ4),
country UInt8 CODEC (DoubleDelta, LZ4),
province UInt8 CODEC (DoubleDelta, LZ4),
city UInt16 CODEC (DoubleDelta, LZ4),
uid UInt32
) engine = MergeTree() partition by toYYYYMMDD(time)
order by (time, country, province, city) settings index_granularity = 65536;

先造 10 亿数据，分布在一天内
insert into test4
select dateAdd(second, number / 1000000000, toDateTime('2024-04-02'))
, rand32() % 200
, rand32(1) % 250
, rand32(2) % 100
, number + 1
from numbers(1000000000);
-- 然后扩增到 32 倍
insert into test4 select * from test4;
insert into test4 select * from test4;
insert into test4 select * from test4;
insert into test4 select * from test4;
insert into test4 select * from test4;

SELECT count(*)
FROM test4

Query id: a4a01197-a22b-4a0d-9747-26555229ff58

┌─────count()─┐
│ 32000000000 │
└─────────────┘

1 row in set. Elapsed: 0.004 sec.
一共 320 亿
等后台 merge 完才 14.28 GiB 磁盘占用
楼主要的查询
WITH r AS
(
SELECT count() AS c
FROM test4
WHERE country = 100
GROUP BY uid
)
SELECT avg(c)
FROM r

Query id: c634e7a7-13fa-4d40-9f30-e6e43105ffe9

┌─avg(c)─┐
│ 32 │
└────────┘

1 row in set. Elapsed: 0.168 sec. Processed 160.30 million rows, 801.18 MB (954.12 million rows/s., 4.77 GB/s.)
0.168 秒完成

这样看起来，一年的数据单机也问题不大
注意，不同的建表语句尤其是 CODEC 非常影响存储空间和性能

[siaronwang]

apache drios

[MoYi123]

只要想办法把 Euler Tour Tree 存数据库里就行了.

[hefish]

现在就要准备毕业设计啦。。。这么早啊。。。

[1018ji]

有时间范围只能现算，又不能预聚合，ck doris 之类试试吧

[wu00]

牛批，学习一下

[zdking08135]

@yjhatfdu2

NB 了，感谢，看来要多研究一下这个软件。
话说，可以顺便尝试复杂查询？

比如(city = 100 or city = 101) and date < '2024-04-02' and date > '2024-03-31'

[yjhatfdu2]

@zdking08135 当然可以，不过按照这个编码形式，肯定要指定 country

[zzmark06]

就这么点数据，想这么多，又这又那的
这点量都摸不到 doris/ck 单机瓶颈

拿 ck 来说，上面给出 ck 表结构的兄弟，@yjhatfdu2 表排序键有问题，排序优先遵循业务必选条件，再根据基数由低到高。建议调整排序顺序为 country,province,city,time 编码方式里，时间去掉 doubledelta ，追求压缩率平衡不用 lz4 ，改用 zstd(1)差不多就这样了。
你这第一个就是高基数，压缩比会很低，速度上不来

对列存来说，整分区 count 都是 O(1)消耗的元数据查询，看不出性能

至于表分区键选用按日还是按月，需要考虑业务平常查询到底按什么的多些。经常跨度大的就改为按月，反之按日。若是业务有按国家为租户的习惯，那分区把国家带上再按月也合理。
若是还有一些大范围时间内区域统计需求，上 projection 来预计算

[zzmark06]

按题目数据量级，大概算下来，一年大概 18 万亿行，磁盘空间应该在 1t 到 2t 之间，写入带宽都喂不满一个单点 ck 配几块机械盘
不过列存嘛，整体结构参考上面 @dlmy 兄弟的描述

[xueling]

兄弟，用我的开源软件，不能帮你实现所有需求，但是可以帮你实现很多需求，可以实现统计 1 天内、小时级、分钟级的 uv,和各地区的 uv ，支持高并发查询结果。至于地区模糊查询和超过 1 天以上日期的查询可以借助 clickhouse 或离线统计来实现。为什么不建议全部使用 ck ，因为 ck 每次查询都是全量计算，并发查询效率比较低。我的开源项目： https://github.com/xl-xueling/xl-lighthouse.git ，有问题找我~