一些经验

1，查询强烈要求带上分区键过滤和主键过滤，如 where day = today() and itime = now()。

2，建表的时候，选择合适的分区键和排序键是优化的关键。

3，如果不允许重复主键(且不要求去重时效性)，建议使用表类型：ReplicatedReplacingMergeTree 建表语句可参考https://clickhouse.yandex/docs/en/operations/table_engines/replacingmergetree/ ,注意只能保证单节点的数据不重复，无法保证集群的。

4，如果要对某一列过滤，且该列非partition key和orderby key, 且该列过滤前后数据量差异较大，建议使用prewhere clause过滤。参考：https://clickhouse.yandex/docs/en/query_language/select/#prewhere-clause。

5，日期和时间使用Date, DateTime类型，不要用String类型。

6，建表时，强烈建议低基数(基数小于10000)且类型为String的列，使用LowCardinality特性，例如国家(country)，操作系统(os)皆可用LowCardinality。查询效益提高可以40~50%，具体参考https://altinity.com/blog/2019/3/27/low-cardinality。

7，为了使复杂查询尽量本地完成，提前减小数据量和网络传输，加快查询速度，创建分布式表时，尽量按照主键hash分shard。例如欲加快select count(distinct uid) from table_all group by country, os的查询速度. 创建分布式表table_all时，shard key为cityHash64(country, os)，hash函数参考https://clickhouse.tech/docs/en/sql-reference/functions/hash-functions/。

8，计算不同维度组合的指标值时，用with rollup或with cube替代union all子句。

9，建表时，请遵守命名规范：分布式表名 = 本地表名 + 后缀"_all"。 select请直接操作分布式表。

10，官方已经指出Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1表示没有商品ID）

11，稀疏索引不同于mysql的B+树，不存在最左的原则，所以在ck查询的时候，where条件中，基数较大的列（即区分度较高的列）在前，基数较小的列（区分度较低的列）在后。

12，多表Join时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较

13，多维分析, 查询列不宜过多, 过滤条件带上分区筛选 (select dim1, dim2, agg1(xxx), agg2(xxx) from table where xxxx group by dim1, dim2 )

14，禁止SELECT *, 不能拉取原始数据!!!! (clickhouse不是数据仓库, 纯粹是拉原始表数据的查询应该禁止,如 select a, b, c, f, e, country from xxx )

分区键和排序键

分区键和排序键理论上不能修改，在建表建库的时候尽量考虑清楚。

0，事实表必须分区，分区粒度根据业务特点决定，不宜过粗或过细。我们当前都是按天分区，按小时、周、月分区也比较常见（系统表中的query_log、trace_log表默认就是按月分区的）。

1，分区键能过滤大量数据，分区键建议使用toYYYYMMDD()按天分区，如果数据量很少，100w左右，建议使用toYYYYMM()按月分区，过多的分区会占用大量的资源，会对集群的稳定性造成很大的影响。

2，分区键必须使用date和datetime字段，避免string类型的分区键

3，每个sql必须要用分区键，否则会导致大量的数据被读取，到了集群的内存限制直接拒绝

4，排序键也是一个非常重要的过滤条件，考虑到ck是OLAP 库，排序键默认也是ck的主键，loap库建议分区键要使用基数比较少的字段，比如country就比timestramp要好。

5，不要使用过长的分区键，主键 。

6，CK的索引非MySQL的B树索引，而是类似Kafka log风格的稀疏索引，故不用考虑最左原则，但是建议基数较大的列（即区分度较高的列）在前，基数较小的列（区分度较低的列）在后。另外，基数特别大的列（如订单ID等）不建议直接用作索引。

分区数

分区数过多会导致一些致命的集群问题。不建议分区数粒度过细，不建议分区数过多，经验来看，10亿数据建议1-10个分区差不多了，当然需要参考你的硬件资源如何。

1，select 查询性能降低，分区数过多会导致打开大量文件句柄，影响集群。

2，分区数过多会导致集群重启变慢，zk压力变大，insert变慢等问题。

https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/custom-partitioning-key/

image2021-7-13_9-26-37.png