本文是对《从零到大数据专家的学习指南(全面升级版)》的ClickHouse部分补充。
1 单表查询
1.1 Prewhere 替代 where
Prewhere 和 where 语句的作用相同,用来过滤数据。不同之处在于 prewhere 只支持MergeTree 族系列引擎的表,首先会读取指定的列数据,来判断数据过滤,等待数据过滤之后再读取 select 声明的列字段来补全其余属性。
采样修饰符只有在 MergeTree engine 表中才有效,且在创建表时需要指定采样策略。
1.3 列裁剪与分区裁剪
数据量太大时应避免使用 select * 操作,查询的性能会与查询的字段大小和数量成线性表换,字段越少,消耗的 io 资源越少,性能就会越高。
分区裁剪就是只读取需要的分区,在过滤条件中指定。
1.4 orderby 结合 where、limit
千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用。
1.5 避免构建虚拟列
如非必须,不要在结果集上构建虚拟列,虚拟列非常消耗资源浪费性能,可以考虑在前端进行处理,或者在表中构造实际字段进行额外存储。
1.6 uniqCombined 替代 distinct
性能可提升 10 倍以上,uniqCombined 底层采用类似 HyperLogLog 算法实现,能接收 2%左右的数据误差,可直接使用这种去重方式提升查询性能。Count(distinct )会使用 uniqExact精确去重。
不建议在千万级不同数据上执行 distinct 去重查询,改为近似去重 uniqCombined
1.7 使用物化视图
参考第 6 章。
1.8 其他注意事项
(1)查询熔断
为了避免因个别慢查询引起的服务雪崩的问题,除了可以为单个查询设置超时以外,还可以配置周期熔断,在一个查询周期内,如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。
(2)关闭虚拟内存
物理内存和虚拟内存的数据交换,会导致查询变慢,资源允许的情况下关闭虚拟内存。
(3)配置 join_use_nulls
为每一个账户添加 join_use_nulls 配置,左表中的一条记录在右表中不存在,右表的相应字段会返回该字段相应数据类型的默认值,而不是标准 SQL 中的 Null 值。
(4)批量写入时先排序
批量写入数据时,必须控制每个批次的数据中涉及到的分区的数量,在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多,会导致 ClickHouse 无法及时对新导入的数据进行合并,从而影响查询性能。
(5)关注 CPU
cpu 一般在 50%左右会出现查询波动,达到 70%会出现大范围的查询超时,cpu 是最关键的指标,要非常关注。
2 多表关联
2.1 准备表和数据
复制
2.2 用 IN 代替 JOIN
当多表联查时,查询的数据仅从其中一张表出时,可考虑用 IN 操作而不是 JOIN
2.3 大小表 JOIN
多表 join 时要满足小表在右的原则,右表关联时被加载到内存中与左表进行比较,ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在,所以右表必须是小表。
(1)小表在右
(2)大表在右
2.4 注意谓词下推(版本差异)
ClickHouse 在 join 查询时不会主动发起谓词下推的操作,需要每个子查询提前完成过滤操作,需要注意的是,是否执行谓词下推,对性能影响差别很大(新版本中已经不存在此问题,但是需要注意谓词的位置的不同依然有性能的差异)
2.5 分布式表使用 GLOBAL
两张分布式表上的 IN 和 JOIN 之前必须加上 GLOBAL 关键字,右表只会在接收查询请求的那个节点查询一次,并将其分发到其他节点上。如果不加 GLOBAL 关键字的话,每个节点都会单独发起一次对右表的查询,而右表又是分布式表,就导致右表一共会被查询 N²次(N是该分布式表的分片数量),这就是查询放大,会带来很大开销。
2.6 使用字典表
将一些需要关联分析的业务创建成字典表进行 join 操作,前提是字典表不宜太大,因为字典表会常驻内存
2.7 提前过滤
通过增加逻辑过滤可以减少数据扫描,达到提高执行速度及降低内存消耗的目的