详解 ClickHouse 的建表优化

一、explain 查看执行计划

explain 功能是从 20.6 版本才成为正式的功能，之前的版本需要到 log 日志中查看执行过程

1. 基本语法

explain [plan | ast | syntax | pipeline] [setting1=value1, setting2=value2,...] select ... [format ...];

• plan：默认查看模式，显示 sql 语句的执行计划
  • header 参数：打印计划中各个步骤的 head 说明，默认关闭，默认值 0
  • description 参数：打印计划中各个步骤的描述，默认开启，默认值 1
  • actions 参数：打印计划中各个步骤的详细信息，默认关闭，默认值 0
• ast：查看语法树模式，不常使用
• syntax：可查看优化语句，使用较多
• pipeline：查看 PIPELINE 计划
  • header 参数：打印计划中各个步骤的 head 说明，默认关闭，默认值 0
  • graph 参数：用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合 graphviz 查看
  • actions 参数：如果开启了 graph，紧凑打印打，默认开启

2. 案例实操

• 查看 plan

  explain [plan] select arrayJoin([1,2,3,null,null]);
  
  explain 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 plan header=1, actions=1,description=1 select number from system.numbers limit 10;
  

• 查看 ast

  explain AST SELECT number from system.numbers limit 10;
  

• 查看 syntax

  --先做一次查询
  SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);
  
  --第一次查看语法优化
  EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);
  
  --返回优化后的语句
  SELECT if(number = 1, 'hello', if(number = 2, 'world', 'xyz')) FROM numbers(10);
  
  --开启三元运算符优化
  SET optimize_if_chain_to_multiif = 1;
  
  --再次查看语法优化
  EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'xyz') FROM numbers(10);
  
  --返回优化后的语句
  SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'xyz') FROM numbers(10);
  
  

• 查看 pipeline

  EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY number % 20;
  
  --打开其他参数
  EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM numbers_mt(10000) GROUP BY number%20;
  

二、建表优化

1. 数据类型设置

1.1 时间类型字段

• Hive 中建表时日期、时间字段一般存储为 String 类型，但在 ClickHouse 中建表时不建议

• ClickHouse 中建表时日期、时间字段最好设置成对应的 Date 、Datetime 类型，避免后续需要经过函数转换处理，执行效率高、可读性好

  create table t_type2(
   id UInt32,
   sku_id String,
   total_amount Decimal(16,2) ,
   create_time Int32
  ) engine =ReplacingMergeTree(create_time)
   partition by toYYYYMMDD(toDate(create_time)) –-需要转换一次，否则报错
   primary key (id)
   order by (id, sku_id);
   
  create table t_type2(
   id UInt32,
   sku_id String,
   total_amount Decimal(16,2) ,
   create_time Datetime
  ) engine =ReplacingMergeTree(create_time)
   partition by toYYYYMMDD(create_time) –-不需要转换
   primary key (id)
   order by (id, sku_id);
  

1.2 空值类型字段

• ClickHouse 中使用 Nullable 来设置字段可以有空值，但是会拖累性能

• 存储 Nullable 列时需要创建一个额外的文件来存储 NULL 的标记，增加消耗；Nullable 列无法被索引

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

• 生产上建议：使用字段默认值或者在业务中无意义的值来表示空值

2. 分区和索引

• 建表时要考虑设置分区，避免全表扫描，一般选择按天分区，如果不按天分区，则建议以单表一亿数据为例，分区大小控制在 10-30 个为最佳
• 建表时必须指定 order by 字段，即索引、排序列；在指定多个索引列时将查询频率大的字段放最前面，然后基数特别大的字段不适合做索引列，如用户表的 userid 字段，通常筛选后的数据满足在百万以内为最佳

3. 表参数

• Index_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整
• 指定 TTL：对于不必须保存历史全量数据的表，建议指定对应的 TTL，可以免去手动过期历史数据的麻烦，TTL 也可以通过 alter table 语句随时修改

4. 写入和删除优化

• 尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力
• 不要一次写入太多分区，或数据写入太快，数据写入太快会导致 Merge 速度跟不上而报错，一般建议每秒钟发起 2-3 次写入操作，每次操作写入 2w~5w 条数据（依服务器性能而定）
• 写入过快报错(Too many parts 和 Memory limit)处理：
  • “Too many parts 处理”：使用 WAL 预写日志，提高写入性能，in_memory_parts_enable_wal 默认为 true
  • 在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现
  • 在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现

5. 常见配置

config.xml 配置项：https://clickhouse.tech/docs/en/operations/server-configuration-parameters/settings/
users.xml 配置项：https://clickhouse.tech/docs/en/operations/settings/settings/

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 核数

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，这样不管多大的分区表都可以删除

5.3 存储

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