表结构优化
表引擎
clickhouse中有非常多的表引擎,用得最多的当属*MergeTree
表引擎,*MergeTree
是clickhouse中最为强大的表引擎(the most robust)。应根据应用场景选择不用的表引擎比如我们临时表用的表引擎为Memory
,这里主要谈谈MergeTree
中的一些优化。
官网中列出了MergeTree的四点特性:
- 数据根据主键排序存储;
- 可通过指定partitioning key确定分区;
- 支持数据备份;
- 支持数据抽样。
MergeTree表引擎的数据由多组part组成,每一个part的数据按照主键进行字典序排列,然后定期在后台进行数据合并。分区和数据合并详情可参考ClickHouse|MergeTree引擎之数据分区
关于MergeTree优化,主要有以下几点:
- 合理设置分区字段,分区粒度根据业务特点决定,以单表1亿数据为例,分区大小控制在10-30个为最佳;
- 合理设置主键(primary key),主键字段默认为排序字段,即建表时
ORDER BY
指定的字段。可通过PRIMARY KEY
关键字指定不同于排序字段的主键字段,但是必须为排序字段tuple的前缀:In this case the primary key expression tuple must be a prefix of the sorting key expression tuple.
。此外,主键的长度并没有限制,但是过长的主键对数据写入会有性能影响(查询没有影响)。 - 如果数据是有时效性的,可通过
TTL
设置数据过期时间,TTL可作用在表之上,也可以作用在具体的字段上,具体见:MergeTree中TTL章节。
字段类型
-
能使用数值型或者时间类型标识的字段,尽量不要用字符串;
-
字符串字段存储文本时,推荐使用UTF-8编码,这样在读取和写入数据是不需要做额外的转换;
-
虽然clickhouse底层将DateTime存储为时间戳Long类型,但不建议直接存储Long类型,因为DateTime不需要经过函数转换处理,执行效率高、可读性好;
-
尽量少将字段指定为Nullable类型,有空值的字段可将空值设定为默认值比如
-1,NULL字符串
。原因是clickhouse需要用额外的文件记录NULL标记,官网也明确表示Nullable对性能有消极的影响:Note
Using
Nullable
almost always negatively affects performance, keep this in mind when designing your databases.
查询优化
-
大部分减少IO的操作比如列裁剪、分区裁剪,join前先过滤等都是通用的加速查询的方式,clickhouse中也适用。尤其是join前先过滤,clickhouse中没有做谓词下推,需要手动将where放到join之前;
-
join时遵循大表在前的原则,这点和hive刚好相反;
-
控制join的表的数量,尽量保持在3个及以下。虽然高版本优化了多表join的效率,但是太多表join情况下对查询效率有巨大的消极影响,可以的话考虑生成大宽表后在单表中查询,正常查询都是毫秒级的;
-
常用的关联表可设置为字典表,字典表数据会存在内存中,注意数据量不要太大;
-
当查询字段出自单表时,可考虑将join改为in,clickhouse中的in查询不但支持单字段还支持tuple:
SELECT UserID IN (123, 456) FROM ... SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ... -- tuple来自子查询 SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...
-
用prewhere代替where(只在
*MergeTree
有效),当查询列明显多于筛选列时使用prewhere可十倍提升查询性能。其实默认情况下clickhouse就会将where中的部分转为prewhere,可根据数据情况手动指定进行prewhere的字段:# prewhere 会自动优化执行过滤阶段的数据读取方式,降低io操作 select * from work_basic_model where product='tracker_view' and ( id='eDf8fZky' or code='eDf8fZky' ) #替换where关键字 select * from work_basic_model prewhere product='tracker_view' and ( id='eDf8fZky' or code='eDf8fZky' )
-
精确统计使用
uniqExact
代替count(distinct)
,如果允许少量误差,可用uniq
和uniqCombined
代替,uniqCombined
比uniq
内存消耗小且精度更高,但是性能会比uniq
差。这三个函数性能上比count(disitnct)
都有10倍以上的性能提升; -
对于一些确定的数据模型,可将统计指标通过物化视图的方式进行构建,这样可避免数据查询时重复计算的过程;物化视图会在有新数据插入时进行更新。物化视图创建可参照CREATE VIEW中
Materialized
章节。
配置优化
config.xml
配置项 | 描述 |
---|---|
background_pool_size | 后台用来merge进程的大小,默认是16,建议改成cpu核数的2倍 |
log_queries | 默认值为0,修改为1,系统会自动创建system_query_log表,并记录每次查询的query信息 |
max_execution_time | 设置单次查询的最大耗时,单位是秒;默认无限制;需要注意的是客户端的超时设置会覆盖该参数 |
max_threads | 设置单个查询所能使用的最大cpu个数;默认是CPU核数 |
max_connections | 最大连接数,根据实际情况配置,默认4096 |
max_server_memory_usage | clickhouse最大使用内存,默认为0动态申请会压榨机器所有资源 |
max_concurrent_queries | 最大并发查询数 |
max_open_files | 默认值为maximum。可能会出现too many open files的错误,配合 ulimit -a设置,如果不生效确认下/etc/security/limits.d/clickhouse.conf 配置是否一致,以及服务是否重启了,不过这个值默认为262144已经很大了 |
users.xml
配置项 | 描述 |
---|---|
max_memory_usage | 单个查询可用的最大内存,默认为10G,根据数据和查询情况设置 |
use_uncompressed_cache | 启用未压缩块的缓存,提升短查询速度。禁用0或启用1。默认情况下0(禁用) |
max_rows_to_read | 查询时可以从表中读取的最大行数,如果查询结果超过改设置会抛异常 |
max_bytes_to_read | 查询时最大返回的数据量,如果超过改设置会跑异常 |
join_use_nulls | join的时候是否使用null,默认为0,在join时如果为空会对空列赋默认值,比如Int类型赋值0,String类型赋值'' |
其他优化
clickhouse在20.6.3之后提供了explain进行执行计划解析的功能,可通过对sql的执行计划解析发现sql中可能存在的影响性能的问题点。关于explain可查看Clickhouse 原生执行计划进行了解。
关于explain
有两点需要注意:
- 目前用工具(DBeaver7.2.2,clickhouse20.9.3.45)执行
explain
命令不会报错但并不能拿到解析结果,只能通过clickhouse-client查看; - 在clickhouse-client中执行
SET send_logs_level = 'trace';
可以查看到更详细的日志信息。