Clickhouse性能优化

最新推荐文章于 2024-03-04 13:55:10 发布

weixin_39070689

最新推荐文章于 2024-03-04 13:55:10 发布

阅读量680

点赞数 1

分类专栏： Clickhouse 文章标签： clickhouse 性能优化数据库

本文链接：https://blog.csdn.net/weixin_39070689/article/details/128969531

版权

Clickhouse 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

硬件方面

尽量使用性能较好的磁盘，在查询方面，多数场景ssd硬盘性能比机械硬盘块2-3倍
CK的高性能与其对CPU的充分利用密不可分,所以尽可能给多的cpu资源可以大大提升查询效率

配置参数调优

参数	默认值	修改后的值	参数说明
max_memory_usage	0	80442450944	单个查询在服务器上可使用的最大内存，默认0无限制
max_memory_usage_for_user	0	140442450944	单台服务器上所有查询可使用最大内存，默认0无限制
use_uncompressed_cache	0	1	是否使用未压缩块的缓存,可以在处理大量短查询时显著减少延迟并提高吞吐量
max_partitions_per_insert_block	100	300	当个插入最大分区数，超过数值会抛出异常
max_execution_time	0	600	当次查询最大查询时间，0无限制，防止过多慢查询导致服务器雪崩
background_pool_size	16	32	后台线程池大小，建议改成cpu个数的2倍
max_concurrent_queries	100	200	最大并发处理的请求数
max_threads	48	32	设置单个查询能使用的最大cpu根据资源分配情况设置

建表优化

避免使用Nullable类型的，此类型需要额外文件存储，总是会拖累性能并且不能被索引，分区和主键中都无法使用
建表时跟数据的数据类型保持一致，不需要跟hive一样全建string类型的，如数值类型和时间类型，

不同类型有自己不同的函数且性能有优化
分区粒度根据业务特点决定，不宜过粗或过细，一般选择按天分区，或者有限集字段，总数最好控制在1000-10000之间
Order by中排序字段根据业务需求，将查询频率大的放在前面，ck使用稀疏索引，基数特别大的不适合做索引，如user字段这种数量有限且查询频率高的可以加在索引中
index_granularity 是用来控制索引粒度的默认是8192，官方建议如非必须不建议调整
如果表中不是必须保留全量历史数据，建议指定TTL，可以免去手动过期历史数据的麻烦。TTL也可以通过ALTER TABLE语句随时修改。

写入方面

尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来压力
建议每次写入不少于1000行的批量写入，或每秒不超过一个写入请求。当使用tab-separated格式将一份数据写入到MergeTree表中时，写入速度大约为50到200MB/s。如果写入的数据每行为1Kb，那么写入的速度为50，000到200，000行每秒。如果您的行更小，那么写入速度将更高。为了提高写入性能，您可以使用多个INSERT进行并行写入，这将带来线性的性能提升
不要同时写入太多个分区，分区数控制在阈值（max_partitions_per_insert_block）之下，否则写入会抛出异常
对于分布式表写入,建议写本地表,读分布式表,性能高,对zookeeper压力也比较小

查询优化

查询时充分使用分区和索引字段,可以大大减少io压力,提高查询效率

如根据唯一eventid查询某条数据的详情

扫描全表
SELECT * FROM A where eventid = '111'

添加分区条件查询(只扫描部分分区的数据)
SELECT * FROM A where eventid = '111' AND startTime='2021-10-13 00:00:00'

数据量太大时应避免使用select * 操作,大部分减少IO的操作比如列裁剪、分区裁剪，join前先过滤等都是通用的加速查询的方式，clickhouse中也适用。
join时遵循大表在前的原则,ClickHouse中无论是Left Join 、Right Join还是Inner Join永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表
对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免数据查询时重复计算的过程并且预聚合后可以大大提高查询效率；物化视图会在有新数据插入时进行更新。

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

使用in (效率更高)
select a.* from hits_v1 a where a.CounterID in (select CounterID from visits_v1); 

使用 join
select a.* from hits_v1 a left join visits_v1 b on a.CounterID=b.CounterID;

业务场景非强制要求100%准确的基数计量，应该用uniq()函数而不是uniqExact()函数或DISTINCT关键字。uniq()底层采用HyperLogLog实现，能够以低于1%的精度损失换来极大的性能提升.也可以使用uniqCombined函数比uniq性能低但是精确度高,底层使用了3种算法的组合
大规模数据集上的ORDER BY要加LIMIT限制
两张分布式表上的IN和JOIN之前必须加上GLOBAL关键字。如果不加GLOBAL关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询N2次（N是该分布式表的shard数量），这就是所谓的查询放大，会带来不小的overhead。加上GLOBAL关键字之后，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上

视图和物化视图

数据库中的 视图(View) 指的是通过一张或多张表查询出来的 「逻辑表」 ，本身只是一段 「SQL」 的封装并 「不存储数据」。

而 物化视图(Materialized View) 与普通视图不同的地方在于它是一个查询结果的数据库对象(持久化存储)，非常趋近于表；物化视图是数据库中的预计算逻辑+显式缓存，典型的空间换时间思路，所以用得好的话，它可以避免对基础表的频繁查询并复用结果，从而显著提升查询的性能。

场景

CREATE TABLE ailpha.ailpha_ueba_score
(
    `featureTime` DateTime,
    `userKey` String,
    `featureID` String,
    `riskScore` Nullable(Float64),
)
ENGINE = MergeTree
PARTITION BY featureID
ORDER BY (
 featureTime,
 userKey,
 featureID)
SETTINGS index_granularity = 8192

假设该表记录每个用户每分钟的风险得分情况.

问题: 查询每个用户在每个小时中的最高的分数

普通查询

SELECT toStartOfHour(featureTime,'Asia/Shanghai') AS featureTime,userKey, max(riskScore) as maxScore FROM ailpha.ailpha_ueba_score GROUP BY featureTime,userKey ORDER BY userKey,maxScore DESC  LIMIT 10

在这里插入图片描述

物化视图查询

先建物化视图
CREATE MATERIALIZED VIEW ailpha.host_score
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(featureTime)
ORDER BY (featureTime,
 userKey) AS
SELECT
    toStartOfHour(featureTime,
 'Asia/Shanghai') AS featureTime,
    userKey,
    maxState(riskScore) AS maxRiskScore
FROM ailpha.ailpha_ueba_score
GROUP BY
    featureTime,
    userKey
    
查询
SELECT   featureTime,userKey, maxMerge(maxRiskScore) as maxScore FROM ailpha.host_score GROUP BY featureTime,userKey ORDER BY userKey,maxScore DESC  LIMIT 10

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-64God2At-1676010387959)(C:\Users\think\AppData\Roaming\Typora\typora-user-images\image-20211012145226169.png)]$