clickhouse 21.x生产实践优化

架构师老狼

已于 2022-01-23 14:43:42 修改

阅读量1.7k

点赞数

分类专栏： OLAP 架构设计文章标签： clickhouse OLAP

于 2022-01-23 14:40:26 首次发布

本文链接：https://blog.csdn.net/wolfjson/article/details/122651226

版权

架构设计同时被 2 个专栏收录

17 篇文章 1 订阅

订阅专栏

OLAP

2 篇文章 0 订阅

订阅专栏

clickhouse

1 时间字段类型

建表时能用数值型或日期时间型表示的字段就不要用字符串，全String 类型在以Hive(hbase)为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。虽然 ClickHouse 底层将DateTime 存储为时间戳Long类型，但不建议存储Long 类型，因为DateTime 不需要经过函数转换处理，执行效率高、可读性好。

2 Nullable类型

官方已经指出Nullable 类型几乎总是会拖累性能，因为存储Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，并且 Nullable 列无法被索引。因此除非极特殊情况，应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值（例如用-1 表示没有商品ID）

3 分区与索引

一般选择按天分区，也可以指定为 Tuple()，以单表一亿数据为例，分区大小控制在 10-30 个为最佳。
必须指定索引列，ClickHouse 中的索引列即排序列，通过 order by 指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维度的索引；通常需要满足高级列在前、查询频率大的在前原则；还有基数特别大的不适合做索引列，如用户表的 userid 字段；通常筛选后的数据满足在百万以内为最佳。

4 数据TTL

如果表中不是必须保留全量历史数据，建议指定 TTL（生存时间值），可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改。

5 写入与删除优化

尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力
不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）
Too many parts错误：in_memory_parts_enable_wal 默认为 true（开启wal预写日志）
Memory limit错误：增加内存设置max_memory_usage ，或者内存不充裕建议将超出部分内容分配到系统硬盘上max_bytes_before_external_group_by、max_bytes_before_external_sort

6 谓词下推：(各个版本性能损失有差异)

当 group by 有 having 子句，但是没有 with cube、with rollup 或者 with totals 修饰的时候，having 过滤会下推到 where 提前过滤。例如下面的查询，HAVING name 变成了 WHERE name，在 group by 之前过滤

7 Prewhere替代where

当查询列明显多于筛选列时使用 Prewhere 可十倍提升查询性能，Prewhere 会自动优化执行过滤阶段的数据读取方式，降低 io 操作
默认：Prewhere自动打开，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：
1）使用常量表达式
2）使用默认值为 alias 类型的字段
3）包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查询
4）select 查询的列字段和 where 的谓词相同
5）使用了主键字段

8 类关系型数据库要求

千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用（mysql、oracle也有同样的要求）

9 避免构建虚拟列

其实就是mysql、oracle要求使用函数的列不会命中索引

SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;

10 uniqCombined 替代distinct

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

反例：select count(distinct rand()) from hits_v1;

正例：SELECT uniqCombined(rand()) from datasets.hits_v1

11 用IN代替JOIN

mysql、oracle exist轮训外表，子查询是大表；in用于子查询是小表
当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN

12 大小表JOIN（mysql、oracle通用要求）

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

13 分布式表使用GLOBAL

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

14 数据一致性

即便对数据一致性支持最好的 Mergetree，也只是保证最终一致性，ReplacingMergeTree去重时机不确定性
1）在查询语句后增加 FINAL 修饰符，这样在查询的过程中将会执行Merge 的特殊逻辑（例如数据去重，预聚合等）
2）在 v20.5.2.7-stable 版本及以后，FINAL 查询支持多线程执行，并且可以通过max_final_threads参数控制单个查询的线程数。

explain pipeline select * from visits_v1 final WHERE StartDate = '2014-03-17' limit 100 settings max_final_threads = 2;

(Expression)

ExpressionTransform × 2

(SettingQuotaAndLimits)

(Limit)

Limit 2 → 2

(ReadFromMergeTree)

ExpressionTransform × 2

CollapsingSortedTransform × 2

Copy 1 → 2

AddingSelector

ExpressionTransform

MergeTree 0 → 1

从 CollapsingSortedTransform 这一步开始已经是多线程执行，但是读取 part 部分的动作还是串行

15 物化视图：

1）定义：

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

2）优缺点

优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。
缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

3）物化视图实战

#建表语句

CREATE TABLE hits_test(

EventDate Date,

CounterID UInt32,

UserID UInt64,

URL String,

Income UInt8

)ENGINE = MergeTree()

PARTITION BY toYYYYMM(EventDate)

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

SAMPLE BY intHash32(UserID)

SETTINGS index_granularity = 8192

#导入数据

INSERT INTO hits_test

SELECT EventDate,CounterID,UserID,URL,Income FROM hits_v1 limit 10000;

#创建物化视图
CREATE MATERIALIZED VIEW hits_mv

ENGINE=SummingMergeTree

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

AS SELECT UserID,EventDate,count(URL) as ClickCount,sum(Income) AS IncomeSum FROM hits_test

WHERE EventDate >= '2014-03-20' #设置更新点,该时间点之前的数据可以另外通过insert into select …… 的方式进行插入 

GROUP BY UserID,EventDate;

#或者可以用下列语法，表 A 可以是一张 mergetree 表

CREATE MATERIALIZED VIEW 物化视图名 TO 表 A AS SELECT FROM 表 B;

#不建议添加 populate 关键字进行全量更新

#导入增量数据

INSERT INTO hits_test

SELECT EventDate,CounterID,UserID,URL,Income FROM hits_v1 WHERE EventDate >= '2014-03-23' limit 10;

#查询物化视图

SELECT * FROM hits_mv;

#导入历史数据

INSERT INTO hits_mv

SELECT UserID,EventDate,count(URL) as ClickCount, sum(Income) AS IncomeSum FROM hits_test

WHERE EventDate = '2014-03-19' GROUP BY UserID,EventDate

架构师老狼

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
clickhouse 21.x生产实践优化

1 时间字段类型建表时能用数值型或日期时间型表示的字段就不要用字符串，全String 类型在以Hive(hbase)为中心的数仓建设中常见，但 ClickHouse 环境不应受此影响。虽然 ClickHouse 底层将DateTime 存储为时间戳Long类型，但不建议存储Long 类型，因为DateTime 不需要经过函数转换处理，执行效率高、可读性好。2 Nullable类型官方已经指出Nullable 类型几乎总是会拖累性能，因为存储Nullable 列时需要创建一个额外的文件来存储 N.
复制链接

扫一扫