ClickHouse 高阶详解_session is locked by a concurrent client-CSDN博客

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图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看;
    ◼ actions 如果开启了 graph，紧凑打印打，默认开启。 注:PLAN 和 PIPELINE 还可以进行额外的显示设置，如上参数所示。

1.2 案例实操

1.2.1 新版本使用 EXPLAIN

可以安装一个 20.6 以上版本，或者直接在官网的在线 demo，选择高版本进行测试。官网在线测试链接: ClickHouse Playground

（1）查看 PLAIN

简单查询
    explain plan select arrayJoin([1,2,3,null,null]);
复杂 SQL 的执行计划
    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 header=1, actions=1,description=1 SELECT number from
    system.numbers limit 10;

（2）AST 语法树

EXPLAIN AST SELECT number from system.numbers limit 10;

（3）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)

（4）查看 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;

2、建表优化

2.1 数据类型

2.1.1 时间字段的类型

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

虽然 ClickHouse 底层将 DateTime 存储为时间戳 Long 类型，但不建议存储 Long 类型，因为 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);

2.1.2 空值存储类型

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

官网说明: 可为空（类型名称) | ClickHouse文档

2.2 分区和索引

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

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

比如官方案例的 hits_v1 表:

......
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID)) ......

visits_v1 表:

......
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID) ......

2.3 表参数

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

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

2.4 写入和删除优化

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

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

写入过快报错，报错信息:

1. Code: 252, e.displayText() = DB::Exception: Too many parts(304).
Merges are processing significantly slower than inserts
2. Code: 241, e.displayText() = DB::Exception: Memory limit (for query)
exceeded:would use 9.37 GiB (attempt to allocate chunk of 301989888
bytes), 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 参数来实现。

2.5 常见配置

配置项主要在 config.xml 或 users.xml 中，基本上都在 users.xml 里

➢ config.xml的配置项： Server Settings | ClickHouse Documentation

➢ users.xml的配置项： Settings | 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