ClickHouse调优（一）基本调优

1、执行计划调优

        在clickhuse20.6版本之前要查看SQL语句的执行计划需要设置日志级别为trace才能可以看到并且只能真正执行sql，在执行日志里面查看。在20.6版本引入了原生的执行计划的语法。在20.6.3版本成为正式版本的功能。

1.1、基本语法

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] 
select ...[FORMAT ...]

        PLAN：用于查看执行计划，默认值。

• header：打印计划中各个步骤的head说明，默认关闭，默认值为 0；
• description：打印计划中各个步骤的描述，默认开启，默认值为1；
• actions：打印计划中各个步骤的详细信息，默认关闭，默认值为0。

        AST：用于查看语法树；

        SYNTAX：用于优化语法；

        PIPELINE：用于查看PIPELINE计划。

• header：打印计划中各个步骤的head说明，默认关闭；
• graph：用DOT图形语言描述管道图，默认关闭，需要查看相关的图形需要配置grapgviz查看；
• actions：如果开启了graph，默认开启。

        注：PLAN和PIPELINE还可以进行额外的显示设置，如上setting。

1.2、案例实操

1.2.1、新版本使用EXPLAIN

        可以使用20.6以上版本，例如我现使用的为21.7.3.14。或者直接在官网的在线demo，选择高版本进行测试。

        官网在线测试链接：ClickHouse Playground

        1、查看PLAN

-- plan 是默认的，可省略
explain plan select database,table,count(1) cnt from system.parts where database in ('datasets','system') group by database,
table order by database,cnt desc limit 2 by database;

explain header=1,actions=1,description=1 select database,table,count(1) cnt from system.parts where database in ('datasets','system') group by database,
table order by database,cnt desc limit 2 by database;

         2、AST语法树（更深究才会使用到）

explain AST SELECT number from system.numbers limit 10;

         3、SYNTAX语法优化

-- 初次查询
select number =1? 'hello':(number=2?'world':'abcdef') from numbers(10);

-- 查看语法优化
EXPLAIN SYNTAX SELECT number =1? 'hello':(number=2?'world':'abcdef') from numbers(10);

-- 开启三元运算符优化
set optimize_if_chain_to_multiif = 1;

-- 再次查看语法优化
EXPLAIN SYNTAX SELECT number =1? 'hello':(number=2?'world':'abcdef') from numbers(10);

-- 返回优化后的语句
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'abcdef') FROM numbers(10)

         4、PIPELINE

        可以查看每个步骤使用的并行度：

EXPLAIN PIPELINE SELECT sum(number) from numbers_mt(10000) group by number % 20;

EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) from numbers_mt(10000) group by number % 20;

         在这里最常用的是SYNTAX和PIPELINE，效果显而易见。

1.2.2、老版本查看执行计划

clickhouse-client -h 主机名 --send_logs_level=trace <<< "sql" > /dev/null

        其中，send_logs_level参数指定日志等级为trace，<<<将SQL语句重定向至clickhouse-client进行查询，>/dev/numm将查询结果重定向到空设备吞掉，以便观察日志。

        注意：

1. 通过将ClickHouse的服务日志，设置到DEBUG或者TRACE级别，才可以变相实现EXPLAIN查询的作用。
2. 需要真正的执行SQL查询，CH才能打印计划日志，所以如果表的数据量很大， 最好借助LIMIT子句，减小查询返回的数据量。

2、建表优化

2.1、数据类型

2.1.1、时间字段的类型

        建表时能用数值型或日期时间型表示的字段能不用字符串就不要用字符串，全String类型在以Hive为中心的数仓建设中常见，但ClickHouse环境不应受此影响。

        虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高、可读性好。

2.1.2、控制存储类型

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

        例如：

CREATE TABLE test_null(x Int8,y Nullable(Int8)) ENGINE TinyLog;
INSERT INTO test_null VALUES(1,NULL),(2,3);
SELECT x+y FROM test_null;

        查看存储的文件：

2.2、分区和索引

        分区粒度根据业务特点决定，不宜过粗或过细。一般选择按天分区，也可以指定为Tuple()，以单表一亿数据为例，分区大小控制在10-30个为最佳。

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

2.3、表参数

        Index_granularity是用来通知索引粒度的，默认是8192，如非必须不建议调整。

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

2.4、写入和删除优化

        1、尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge任务带来巨大压力。

        2、不要一次写入太多分区，或数据写入太快，数据写入太快会导致Merge速度跟不上而报错，一般建议每秒钟发起2-3次写入操作（例如flink的并行度），每次操作写入2W~5W条数据（依服务器性能而定）。

2.5、常见配置

        配置项主要在config.xml（对应服务端的配置）或users.xml（用户使用的配置）中，基本上都在users.xml里。目录在/etc/clickhouse-server。

        1、config.xml的配置项（不能被覆盖，即在客户端设置的参数无法覆盖）：

        Server Settings | ClickHouse Documentation

        2、users.xml的配置项：

        Introduction | ClickHouse Documentation

        

2.5.1、CPU资源

配置	描述
background_pool_size	后台线程池的大小，merge线程就是在该线程池中执行，该线程池不仅仅是给merge线程用的，默认值16，允许的前提下建议改成cpu个数的2倍（线程数）。
background_schedule_pool_size	执行后台任务（复制表、Kafka流、DNS缓存更新）的线程数。默认128，建议改成cpu个数的2倍（线程数）。
background_distributed_schedule_pool_size	设置为分布式发送执行后台任务的线程数，默认16，建议改成CPU个数的2倍（线程数）
max_concurrent_queries	最大并发处理的请求数（包含select,insert等），默认值100，推荐150（不够再加）~300。
max_threads	设置单个查询所能使用的最大cpu个数，默认是cpu核数

2.5.2、内存资源

配置	描述
max_memory_usage	此参数在users.xml中,表示单次Query占用内存最大值，该值可以设置的比较大，这样可以提升集群查询的上限。保留一点给OS，比如128G内存的机器，设置为100GB。
max_bytes_before_external_group_by	一般按照max_memory_usage的一半设置内存，当group使用内存超过阈值后会刷新到磁盘进行。因为clickhouse聚合分两个阶段：查询并及建立中间数据、合并中间数据，结合上一项，建议50GB。
max_bytes_before_external_sort	当order by已使用max_bytes_before_external_sort内存就进行溢写磁盘(基于磁盘排序)，如果不设置该值，那么当内存不够时直接抛错，设置了该值order by可以正常完成，但是速度相对存内存来说肯定要慢点(实测慢的非常多，无法接受)。
max_table_size_to_drop	此参数在config.xml中，应用于需要删除表或分区的情况，默认是50GB，意思是如果删除50GB以上的分区表会失败。建议修改为0，这样不管多大的分区表都可以删除。

2.5.3、存储

        ClickHouse不支持设置多数据目录，为了提升数据io性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景SSD会比普通机械硬盘快2-3倍。