技术博客
目录

ClickHouse Explain及建表优化

本文及后续所有文章都以 21.7.3.14-2 做为版本讲解和入门学习

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

# EXPLAIN

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...] SELECT ... [FORMAT ...]
1
  • AST ：用于查看语法树；
  • SYNTAX：用于优化语法；
  • PLAN：用于查看执行计划，默认值。
    -> header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0;
    -> description 打印计划中各个步骤的描述，默认开启，默认值 1；
    -> actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0。
  • PIPELINE：用于查看 PIPELINE 计划。
    -> header 打印计划中各个步骤的 head 说明，默认关闭；
    -> graph 用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合 graphviz 查看；
    -> actions 如果开启了 graph，紧凑打印打，默认开启。

# PLAN 执行计划

执行一段复杂的查询语句，可以直接在 CK 中执行

# 执行
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
SELECT
    database,
    table,
    count(1) AS cnt
FROM system.parts
WHERE database IN ('datasets', 'system')
GROUP BY
    database,
    table
ORDER BY
    database ASC,
    cnt DESC
LIMIT 2 BY database
┌─explain─────────────────────────────────────────────────────────────────────────────────────┐
│ Expression (Projection)                                                                     │
│   LimitBy                                                                                   │
│     Expression (Before LIMIT BY)                                                            │
│       MergingSorted (Merge sorted streams for ORDER BY)                                     │
│         MergeSorting (Merge sorted blocks for ORDER BY)                                     │
│           PartialSorting (Sort each block for ORDER BY)                                     │
│             Expression (Before ORDER BY)                                                    │
│               Aggregating                                                                   │
│                 Expression (Before GROUP BY)                                                │
│                   Filter (WHERE)                                                            │
│                     SettingQuotaAndLimits (Set limits and quota after reading from storage) │
│                       ReadFromStorage (SystemParts)                                         │
└─────────────────────────────────────────────────────────────────────────────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# SYNTAX 返回优化的语法

# 先做一次查询
SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu') FROM
numbers(10);

# 查看语法优化
EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' :
'atguigu') FROM numbers(10);

# 开启三元运算符优化
SET optimize_if_chain_to_multiif = 1;

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

# 返回优化后的语句
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'xyz')
FROM numbers(10);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# PIPELINE 执行过程

EXPLAIN PIPELINE SELECT sum(number) FROM numbers_mt(100000) GROUP BY
number % 20;

# x 4 的意思是用4个线程执行
┌─explain─────────────────────────┐
│ (Expression)                    │
│ ExpressionTransform             │
│   (Aggregating)                 │
│   Resize 4 → 1                  │
│     AggregatingTransform × 4    │
│       (Expression)              │
│       ExpressionTransform × 4   │
│         (SettingQuotaAndLimits) │
│           (ReadFromStorage)     │
│           NumbersMt × 4 0 → 1   │
└─────────────────────────────────┘

# 打开其他参数
EXPLAIN PIPELINE header=1,graph=1 SELECT sum(number) FROM
numbers_mt(10000) GROUP BY number%20;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

在之前的版本想查看 sql 执行计划或过程

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

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

注意：

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

# 建表优化

# 数据类型

# 时间字段的类型

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

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

create table t_type2(
 id UInt32,
 sku_id String,
 total_amount Decimal(16,2) ,
 # 初始时 Int32 类似于 long
 create_time Int32
) engine =ReplacingMergeTree(create_time)
 # 需要转换一次，否则报错
 partition by toYYYYMMDD(toDate(create_time)) 
 primary key (id)
 order by (id, sku_id);
1
2
3
4
5
6
7
8
9
10
11

# 空值存储类型

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

# 分区和索引

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

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

# 表参数

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

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

# 写入和删除优化

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

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

写入过快报错，报错信息
1.DB::Exception: Too many parts (304).Merges are processing significantly slower than inserts
2.DB::Exception: Memory limit (for query)exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888bytes), maximum: 9.31 GiB

Too many parts 处理，使用 WAL 预写日志，提高写入性能。in_memory_parts_enable_wal 默认为 true ，在服务器内存充裕的情况下增加内存配额，一般通过 max_memory_usage 来实现。

在服务器内存不充裕的情况下，建议将超出部分内容分配到系统硬盘上，但会降低执行速度，一般通过 max_bytes_before_external_group_by、max_bytes_before_external_sort 参数来实现。

# 常见配置

配置项主要在 config.xml (opens new window) 或 users.xml (opens new window) 中，config.xml 为服务端的配置，users.xml 做一些优化配置及用户权限配置

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

# 内存资源

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

# 存储

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

上次更新: 6/11/2025, 4:10:30 PM
ClickHouse 分片与集群部署
ClickHouse 语法优化规则

← ClickHouse 语法优化规则→

Theme by Vdoing | Copyright © 2023-2025