Clickhouse性能优化

Clickhouse性能优化

视图

ClickHouse中分为:普通视图和物化视图

  1. 普通视图: 不会存储数据,只保存了一个query,一般用作子查询,当base表删除后不可用
  2. 物化视图: 存储执行query后得到的数据,创建视图时必须制定engine

物化视图

通常物化视图,会做两个层面的物化视图,一个是维度层面的物化,一个是时序层面的物化。

什么是物化视图,假设一个数据源的原始维度有十个列,通过分析查询请求发现,group1 中的三个维度和 group2 中的三个维度分别经常同时出现,剩余的四个维度可能查询频率很低。更加严重的是,没有被查询的维度列里面有一个是高基维,就是 count district 值很大的维度,比如说像 User id 这种。这种情况下会存在很大的查询性能问题,因为高基维度会影响 Druid 的数据预聚合效果,聚合效果差就会导致索引文件 Size 变大,进而导致查询时的读 IO 变大,整体查询性能变差。针对这种 case 的优化,我们会将 group1 和 group2 这种维度分别建一个预聚合索引,然后当收到新的查询请求,系统会先分析请求里要查询维度集合,如果要查询的维度集合是刚才新建的专用的索引维度集合的一个子集,则直接访问刚才新建的索引就可以,不需要去访问原始的聚合索引,查询的性能会有一个比较明显的改善,这就是物化视图的一个设计思路,也是一个典型的用空间换时间的方案。

时序物化视图:除了刚才提到的查询场景外,还有一种查询 Case,Druid 也不能很好满足。比如大跨度时间范围的查询,假设一个数据源的聚合力度是分钟级别,但需要查询最近三个月的数据就比较麻烦,因为需要把过去三个月的所有分钟级别的索引文件全部扫描一遍,然后再做一次聚合的计算。

为了解决这个问题,我们在数据源分钟级别的索引上再新建一个小时级别甚至级别的物化索引,这种情况下聚合效果就会更好,索引整体的 size 也会比较小。当收到一个新的查询请求时,如果查询要统计的粒度是天级别或者是更高级别的查询粒度,会把查询请求自动路由到天级别物化索引上,这样查询性能也会有一个比较明显的改善。

ck的sql慢的优化方向

  1. 分区,原则是尽量把经常一起用到的数据放到相同区(也可以根据where条件来分区),如果一个区太大再放到多个区,
  2. 主键(索引,即排序)order by字段选择:就是把where 里面肯定有的字段加到里面,where 中一定有的字段放到第一位,注意字段的区分度适中即可 区分度太大太小都不好,因为ck的索引时稀疏索引,采用的是按照固定的粒度抽样作为实际的索引值,不是mysql的二叉树,所以不建议使用区分度特别高的字段。
    两种主键,第一种ORDER BY (industry, l1_name, l2_name, l3_name, job_city, job_area, row_id),第二种不包含row_id字段,即ORDER BY (industry, l1_name, l2_name, l3_name, job_city, job_area),其中row_id 是唯一的,在where条件中使用row_id来查询时,你会发现第二种会性能更好,即将row_id从主键中移除,查询效果更好