clickhouse优化（持续更新...）

表结构优化

表引擎

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';可以查看到更详细的日志信息。