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

---

前言

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

---

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

一、表设计

我们要查询的结果是去重后的用户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亿数据是一个持续几小时的推送，因此增加推送标记表，数据推送完成后写入时间，该时间和当日所有的数据的create_time保持一致，同时增加env字段，可以用来对推送任务的SQL语句调试时，用来测试。代码中基于AOP对create_time进行自动添加，相关细节很简单，不再赘述。
clickhouse也是有缓存机制的，仅仅是基于linux的page cache，所以每天的数据也是需要进行预热的，因此在代码中增加了Scheduled任务，每隔10秒检测一下是否存在更新的推送标记数据，如果存在则执行预先准备好的查询，完成预热。

---

3.二级索引

实际测试发现，clickhouse的二级索引作用有限，这块可以看下稀疏索引的介绍，创建稀疏索引并不能达到mysql中那种加上适合的索引，可以百倍千倍的加快性能，但使用得当，十倍的性能提升还是可以的，同时不同类型的二级索引以及选择不同GRANULARITY，也是有差别的，实际测试中，可以额外加快几倍到十几倍的性能，例如某个字段只存在有限的几个枚举值，则set会比minmax性能好，数据量很大的情况下，适度将GRANULARITY放大，效果会更好，minmax适合数值区分度高的情况，对于一些数值不需要很精确，可以考虑将数值进行集中，例如1-10000，可以划分为1，10，100，1000，10000类似这种；ngrambf_v1、tokenbf_v1、bloom_filter则适合字符串场景。

谨记：官方并不建议修改GRANULARITY，所以只有在进行充分测试时，才建议去修改，另外创建稀疏索引的原则和ORDER BY也是一样的，多个字段的组合索引时，一定要将稀疏的放在前边，紧密的放在后边，多个字段可选择时，选择更稀疏的

• minmax: 以index granularity为单位，存储指定表达式计算后的min、max值；在等值和范围查询中能够帮助快速跳过不满足要求的块，减少IO。
• set(max_rows)：以index granularity为单位，存储指定表达式的distinct value集合，用于快速判断等值查询是否命中该块，减少IO。如果不知道有多少种，可以设置为set(0)。
• ngrambf_v1(n, size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)：将string进行ngram分词后，构建bloom filter，能够优化等值、like、in等查询条件。
• tokenbf_v1(size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)： 与ngrambf_v1类似，区别是不使用ngram进行分词，而是通过标点符号进行词语分割。
• bloom_filter([false_positive])：对指定列构建bloom filter，用于加速等值、like、in等查询条件的执行。

ALTER TABLE  
db.t_test on cluster '{cluster}'
ADD INDEX  
idx_user(user_id)  
TYPE minmax
GRANULARITY 8192;

ALTER TABLE  
db.t_test on cluster '{cluster}'
ADD INDEX  
idx_profession(profession)  
TYPE set(0)
GRANULARITY 81920;

4.TTL

每日的全量数据更新，就一定要合理使用TTL，否则数据库的磁盘没几天就会被灌满。

列 TTL

创建表时指定 TTL

CREATE TABLE example_table
(
    d DateTime,
    a Int TTL d + INTERVAL 1 MONTH,
    b Int TTL d + INTERVAL 1 MONTH,
    c String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d;

为表中已存在的列字段添加 TTL

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 DAY;

修改列字段的 TTL

ALTER TABLE example_table
    MODIFY COLUMN
    c String TTL d + INTERVAL 1 MONTH;

表 TTL

创建时指定 TTL

CREATE TABLE example_table
(
    d DateTime,
    a Int
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(d)
ORDER BY d
TTL d + INTERVAL 1 MONTH [DELETE],
    d + INTERVAL 1 WEEK TO VOLUME 'aaa',
    d + INTERVAL 2 WEEK TO DISK 'bbb';

修改表的 TTL

ALTER TABLE example_table
    MODIFY TTL d + INTERVAL 1 DAY;

删除分区

ALTER TABLE innovation.user_portrait_behavior on cluster default DROP PARTITION '20211021091418';

---

三、遗留的问题

1. 预热：新数据导入后的首次查询确实很慢，目前的预热我并不能保证可以再所有场景下都能够起到应有的效果。
2. 目前是配置表一级的TTL，但可能会存在这种情况，就是数据侧的因为某种原因，持续没有推送成功，虽然会有报警，但没有在TTL的时间内处理，导致所有数据都过期被删除。这块我是建议不使用clickhouse的TTL，而是将逻辑放到业务中，同时定时任务扫描，使用删除分区的方式来完成。
3. 表的TTL会比执行drop partition会慢很多，我在测试中，对一张800亿的表进行alter增加ttl，大概会删除600亿的数据，我之前使用drop partition时立即返回，但配置了TTL，发现大概在几个小时中，磁盘的容量缓慢下降，这块后续有时间可以在具体了解下TTL的机制。