【clickhouse踩坑记录】ClickHouse查询性能优化（⼊门级）
背景
⽤了⼀年多的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：
1. 使⽤常量表达式
2. 使⽤默认值为aliaas类型的字段
3. 包含了arrayJOIN，globalln，globalNothln或者indexHint的查询。
4. select 查的列字段和where的谓词相同。（当且仅当查询跟where相同）
5. 使⽤了主键字段
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
0rows in set. Elapsed: 0.300 sec. Processed 5.01 million rows,40.08 MB (16.68 million rows/s.,133.46 MB/s.)
-- ⼩表关联⼤表
join和in哪个查询更快insert into table numbers_v3
select a.number from numbers_v1 a join numbers_v2 b on a.number = b.number
0rows 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，运维那块也只⼀知半解。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。