背景

用了一年多的ClickHouse，但好像都没系统地去学一遍，趁着最近有点时间，相对全面地去看了一圈ClickHouse的内容。发现ClickHouse虽然性能查询本身快，但如果使用不恰当，性能会被降一个级别。下面主要简单介绍一下，ClickHouse的查询可以从哪些方面做优化。可重点关注标题加粗部分！！

优化方法

以下，主要从表级别、语法、查询这三方面简要介绍。

表级别优化

1. 填充有空值的字段

   对于一些表字段，若存在空值，则可以考虑使用无业务场景意义的字符进行填充。因为ClickHouse对于空值，在底层存储是用了单独的文件存储。相对于没有空值的情况，存在空值会稍微影响查询性能。

2. 分区存储与索引

   这部分属于相对常规的操作啦。跟hive一样，表分区后，底层也会有相关的分区目录，筛选的时候添加分区过滤，提升查询性能。
   另一部分是索引，即建表语句中的order by，对于涉及到筛选的字段，按由粗到细的粒度对表进行排序，查询性能也可以有一定的提升。

3. 索引粒度

   index_granularity：这个参数是稀疏索引的粒度，默认是8192。正常情况是不用修改，官方也不建议。除非遇到大量的数据（上亿级别）且分布不均的时候，可以调整此参数。

语法优化

1. count优化

   因为clickhouse对每个表的数据量，在底层文件中提供了预数据。所以能直接使用count()则避免使用count(col_name)。

2. 谓词下推

   常规操作啦，也就是存储在子查询的话，将where条件挪到子查询中，提前过滤，避免过多的数据加载到内存中。

3. 聚合外推

   举个例子：如将sum(money * 2) 变成 sum(money) * 2。对数据库来说，后者的计算量明显少一点。

4. 高级函数

   ClickHouse中有很多很好用的函数。如：使用multiIf()替代多重case when，对于版本数据的获取使用argMax()函数，而非用子查询关联取最大值。

查询优化

1. prewhere代替where

   rewhere只支持*MergeTree族系列引擎表，原理是首先会读取制定的数据列，来判断数据过滤，等待数据过滤之后再读取select声明的列字段来补全其余属性。
   当查询列明显多于筛选列时使用prewhere可十倍提升查询性能，prewhere会自动优化执行过滤阶段的数据读取方式，降低io操作。
   不过新版本中，clickhouse如果能将where转化为prewhere，clickhouse会自动帮你做优化。
   附：如下场景不会使用prewhere：

   • 使用常量表达式
   • 使用默认值为aliaas类型的字段
   • 包含了arrayJOIN，globalln，globalNothln或者indexHint的查询。
   • select 查的列字段和where的谓词相同。（当且仅当查询跟where相同）
   • 使用了主键字段

2. 列裁剪与分区裁剪

   常规操作，如无必要，提前过滤，并且仅选择自己需要的列。

3. 避免构建虚拟列

   能少用虚拟列，则少用虚拟列。如下例子：

   -- 少用（如果虚拟列能不在clickhouse产生，转移到其他地方操作（如后端），则少用）
   select col_a, col_b,col_a + col_b from table
   -- 多用
   select col_a, col_b from table
   

4. 用IN 代替JOIN

   使用in过滤数据优于使用join过滤数据。因为A join B的话，需要把B表都加载到内存中，B表过大的话，性能很差，而用in的话，则可以避免部分数据过滤场景。

5. 大join小表而非小表join大表

   上面也提到，clickhouse中对A join B的实现，是将B表加载到内存中，如果B表过大，加载到内存后，其实很影响性能的。所以选择小表放右边可以很大程度上提升性能。
   做了个测试，A表1w，B表500w，性能表现如下，把我惊到了。
   另外如果生产上，A、B表数据量很大的话，尽可能地在子查询中增加过滤条件，博主在生产上使用此优化后，查询速度降低到原来的1/4。

   -- 大表关联小表
   insert into table numbers_v3
   select a.number from numbers_v2 a join numbers_v1 b on a.number = b.number
   
   0 rows in set. Elapsed: 0.300 sec. Processed 5.01 million rows, 40.08 MB (16.68 million rows/s., 133.46 MB/s.)
   
   -- 小表关联大表
   insert into table numbers_v3
   select a.number from numbers_v1 a join numbers_v2 b on a.number = b.number
   
   0 rows in set. Elapsed: 3.353 sec. Processed 5.01 million rows, 40.08 MB (1.49 million rows/s., 11.95 MB/s.)
   

附还有几点其他小技巧

• clickhouse的20.6版本之后，支持查看执行计划，可以通过执行计划分析，从而优化SQL。
• trace分析，如下方法，可对执行过程进行更加深入的分析。

clickhouse-client -u xxxx --password xxxxxx --send_logs_level=trace <<< 'your query sql' > /dev/null

总结

以上，即为入门级别，博主对ClickHouse理解后，总结下来作为SQL Boy具有可操作性极强的性能优化。对于ClickHouse的优化，在运维层面也很重要，比如：比如对于CPU资源、内存资源、IO的管理等等，这里不好多讲，毕竟博主也算是个SQL Boy，运维那块也只一知半解。