clickhouse常规的优化方法_clickhouse 优化，2024年最新Linux运维开发全套学习

在ClickHouse表中数据存储时，对于一些列尽量不使用Nullable类型存储，因为此类型需要单独创建额外的文件来存储NULL的标记并且Nullable类型列无法被索引，会拖累性能，在数据存储时如果有空值时，我们可以选择在业务中没有意义的值来替代NULL值。
比如：用户ID或商品ID，用-1表示没有ID

1.3 分区和索引

ClickHouse中一般选择按天分区，可以指定tuple()指定多个列为组合分区。如果不按天分区，每个分区数据量控制在800~1000万为宜。
建表时通过order by 指定索引列，可以指定tuple(),指定多个列为索引列，指定索引列时最好满足高基列在前、查询频率大的列在前的原则。基数过大的列不适合作为索引列，因为如果某列基数特别大，这种情况有索引和没索引效果一样。如用户表的userid 字段；通常筛选后的数据满足在百万以内为最佳。

在这些分区索引的作用下，进行数据查询时能够快速跳过不必要的数据分区目录，从而减少最终需要扫描的数据范围。

比如官方案例的hits_v1 表：

PARTITION BY toYYYYMM(EventDate) 
ORDER BY (CounterID, EventDate, intHash32(UserID)) 

visits_v1 表：

PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) 

现场查询按月查询的情况比较多，可以按月进行分区

1.4 表参数

l Index_granularity 是用来控制索引粒度的，默认是8192，如非必须不建议调整。
l 如果表中不是必须保留全量历史数据，建议指定TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过alter table 语句随时修改。

二、写入查询优化

（1）尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力
（2）不要一次写入太多分区，或数据写入太快，数据写入太快会导致Merge 速度跟不上而报错，一般建议每秒钟发起2-3 次写入操作，每次操作写入2w~5w 条数据（依服务器性能而定）

配置项主要在config.xml 或users.xml 中，基本上都在users.xml 里
➢ config.xml 的配置项
https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/
➢ users.xml 的配置项
https://clickhouse.tech/docs/en/operations/settings/settings/

CPU 资源

内存资源

三、查询优化

3.1 单表查询

3.1.1 Prewhere 替代 where

Prewhere 和where 语句的作用相同，用来过滤数据。不同之处在于prewhere 只支持MergeTree 族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select 声明的列字段来补全其余属性。
当查询列明显多于筛选列时使用Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低io 操作。
在某些场合下，prewhere 语句比where 语句处理的数据量更少性能更高。
默认情况，我们肯定不会关闭where 自动优化成prewhere，但是某些场景即使开启优化，也不会自动转换prewhere，需要手动指定prewhere：
⚫ 使用常量表达式
⚫ 使用默认值为alias 类型的字段
⚫ 包含了arrayJOIN，globalIn，globalNotIn 或者indexHint 的查询
⚫ select 查询的列字段和where 的谓词相同
⚫ 使用了主键字段

3.1.2 列裁剪与分区裁剪

数据量太大时应避免使用select * 操作，查询的性能会与查询的字段大小和数量成线性表换，字段越少，消耗的io 资源越少，性能就会越高。分区裁剪就是只读取需要的分区，在过滤条件中指定。

3.1.3 orderby 结合 where、limit

千万以上数据集进行order by 查询时需要搭配where 条件和limit 语句一起使用。

3.1.4 避免构建虚拟列

如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

3.1.5 uniqCombined 替代 distinct

性能可提升10 倍以上，uniqCombined 底层采用类似HyperLogLog 算法实现，能接收2% 左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct )会使用uniqExact 精确去重。
不建议在千万级不同数据上执行distinct 去重查询，改为近似去重uniqCombined。

3.2 多表关联

3.2.1 用 IN 代替JOIN

当多表联查时，查询的数据仅从其中一张表出时，可考虑用IN 操作而不是JOIN

3.2.2 大小表 JOIN

多表join 时要满足小表在右的原则。
右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

3.2.3 谓词下推（版本差异）

ClickHouse 在join 查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问题，但是需要注意谓词的位置的不同依然有性能的差异）

3.2.4 分布式表使用 GLOBAL

两张分布式表上的IN 和JOIN 之前必须加上GLOBAL 关键字，右表只会在接收查询请求
的那个节点查询一次，并将其分发到其他节点上。
如果不加GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询N²次（N 是该分布式表的分片数量），这就是查询放大，会带来很大开销。

示例：
对分布式表使用join 或者 in时，ClickHouse会将当前SQL分发到各个ClickHouse节点上执行，例如有如下SQL:

select a.id,a.name,b.score from a join b on a.id = b.id

如果以上a表和b表都是分布式表，ClickHouse集群有3个节点，那么上面SQL会分发到ClickHouse所有节点执行，b表会在每个节点上收集其他节点对应b表数据并放在内存，这样的话，每个ClickHouse节点都会从对应的3台节点上将b表数据进行汇集。
如果使用global关键字，执行如下SQL：

**自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。**

**深知大多数Linux运维工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！**

**因此收集整理了一份《2024年Linux运维全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。**
![img](https://img-blog.csdnimg.cn/img_convert/3d9c61e3d2df26d7104aa34a0ab0e14c.png)
![img](https://img-blog.csdnimg.cn/img_convert/0d0af29ac321eaa993b6da1de6822db6.png)
![img](https://img-blog.csdnimg.cn/img_convert/75dca412a731444a607261c8150491b5.png)
![img](https://img-blog.csdnimg.cn/img_convert/38a74bc60da104ce4f0fe0d3cac527d9.png)
![img](https://img-blog.csdnimg.cn/img_convert/cab94f29175543b14f8994cba93ba3e4.png)

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Linux运维知识点，真正体系化！**

**由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新**

**如果你觉得这些内容对你有帮助，可以添加VX：vip1024b （备注Linux运维获取）**
![img](https://img-blog.csdnimg.cn/img_convert/6b8957bc45f1c3f435b2a797a15ebb6e.jpeg)

系化！**

**由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新**

**如果你觉得这些内容对你有帮助，可以添加VX：vip1024b （备注Linux运维获取）**
[外链图片转存中...(img-FNKYVuNk-1712944873576)]