背景
用了一年多的ClickHouse,但好像都没系统地去学一遍,趁着最近有点时间,相对全面地去看了一圈ClickHouse的内容。发现ClickHouse虽然性能查询本身快,但如果使用不恰当,性能会被降一个级别。下面主要简单介绍一下,ClickHouse的查询可以从哪些方面做优化。可重点关注标题加粗部分!!
优化方法
以下,主要从表级别、语法、查询这三方面简要介绍。
表级别优化
- 填充有空值的字段
对于一些表字段,若存在空值,则可以考虑使用无业务场景意义的字符进行填充。因为ClickHouse对于空值,在底层存储是用了单独的文件存储。相对于没有空值的情况,存在空值会稍微影响查询性能。
- 分区存储与索引
这部分属于相对常规的操作啦。跟hive一样,表分区后,底层也会有相关的分区目录,筛选的时候添加分区过滤,提升查询性能。
另一部分是索引,即建表语句中的order by,对于涉及到筛选的字段,按由粗到细的粒度对表进行排序,查询性能也可以有一定的提升。 - 索引粒度
index_granularity:这个参数是稀疏索引的粒度,默认是8192。正常情况是不用修改,官方也不建议。除非遇到大量的数据(上亿级别)且分布不均的时候,可以调整此参数。
语法优化
- count优化
因为clickhouse对每个表的数据量,在底层文件中提供了预数据。所以能直接使用count()则避免使用count(col_name)。
- 谓词下推
常规操作啦,也就是存储在子查询的话,将where条件挪到子查询中,提前过滤,避免过多的数据加载到内存中。
- 聚合外推
举个例子:如将sum(money * 2) 变成 sum(money) * 2。对数据库来说,后者的计算量明显少一点。
- 高级函数
ClickHouse中有很多很好用的函数。如:使用multiIf()替代多重case when,对于版本数据的获取使用argMax()函数,而非用子查询关联取最大值。
查询优化
- prewhere代替where
rewhere只支持*MergeTree族系列引擎表,原理是首先会读取制定的数据列,来判断数据过滤,等待数据过滤之后再读取select声明的列字段来补全其余属性。
当查询列明显多于筛选列时使用prewhere可十倍提升查询性能,prewhere会自动优化执行过滤阶段的数据读取方式,降低io操作。
不过新版本中,clickhouse如果能将where转化为prewhere,clickhouse会自动帮你做优化。
附:如下场景不会使用prewhere:- 使用常量表达式
- 使用默认值为aliaas类型的字段
- 包含了arrayJOIN,globalln,globalNothln或者indexHint的查询。
- select 查的列字段和where的谓词相同。(当且仅当查询跟where相同)
- 使用了主键字段
- 列裁剪与分区裁剪
常规操作,如无必要,提前过滤,并且仅选择自己需要的列。
- 避免构建虚拟列
能少用虚拟列,则少用虚拟列。如下例子:
-- 少用(如果虚拟列能不在clickhouse产生,转移到其他地方操作(如后端),则少用) select col_a, col_b,col_a + col_b from table -- 多用 select col_a, col_b from table
- 用IN 代替JOIN
使用in过滤数据优于使用join过滤数据。因为A join B的话,需要把B表都加载到内存中,B表过大的话,性能很差,而用in的话,则可以避免部分数据过滤场景。
- 大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,运维那块也只一知半解。