京东200亿数据基于clickhouse的秒级预估实现

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

业务背景是一个典型的电商场景，基于用户画像和用户行为的人群圈选，后续就可以进行人群的分析及打标等。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、表设计

我们要查询的结果是去重后的用户ID，用户画像数据大概几亿条，用户行为数据，考虑数据量级问题，我们只统计了30天的PV数据，90天的购物车数据，180天的购买数据以及30天的搜索数据（用户的搜索词按照三级品类维度聚合，放到一起），用户行为按照时间维度又分为1天、3天、7天、30天、60天、90天、180天，因此数据需要每天生成，并导入到clickhouse，一天的数据量大概在200亿。表引擎使用的是ReplicatedMergeTree + Distributed，ReplicatedMergeTree的ORDER BY是基于京东的一二三级品类。

1.画像表和行为表分离

这种场景，当用户同时选择画像标签和行为标签时，因为是and关系，需要对两张表的数据进行连接，如果两边都是千万级数据，是无法做到秒级返回的。

2.画像&行为大宽表

这张场景下，如果用户只选择少量的画像标签，而不选择行为标签，会导致画像标签的数据量级过大，从而导致需要对几十亿条数据进行去重计算，虽然有近似去重统计函数uniq，但依然无法做到秒级返回。

3.画像&行为大宽表 + 画像表

这张场景下，如果用户只选择少量的画像标签，则直接查询画像表，如果查询行为+画像，因为大宽表的ORDER BY是基于品类的，所以不会出现全表扫描的情况，依然可以达到秒级返回预估数。

---

二、数据库优化

1.推送优化

在之前的文章里，我有测试过顺序和无序两种数据的推送性能的差，当初测试的场景是比较极端的，ORDER BY的选择是区分度极高的字段，导致顺序插入和无需插入相差10倍，但随着对ck索引机制的了解，ORDER BY是需要选择区分度适中的字段，且要在where中一定存在的，order by多个字段时，区分度要由差到好，区分度是否合适，还和GRANULARITY的粒度有关，这块可以多进行测试来评估。
我们在hive中的任务在推送前对数据按照ORDER BY的子弹进行排序，但推送效果确实很好，50个字段，可以达到120万/秒（4分区1副本*16C32G）。但因为增加了排序环节，导致前边的数据计算额外增加了5个小时，之后将任务改为基于二级品类的多线程推送，不排序，发现基本也能达到相同的效果，这也和最细化的三级品类维度（7千多）区分度比较稀疏有关，但其他条件都是非必选，个人觉得加入到ORDER BY也不是个好选择。按照二级品类推送且不排序后，缩短了5小时的数据准备时间。
关于推送前数据是否要排序，还是要综合评估成本，不能教条主义。

---

2.数据推送

200亿数据是一个持续几小时的推送，因此增加推送标记表，数据推送完成后写入时间ÿ