目录
4.1.6 uniqCombined 替代 distinct
1、Explain 查看执行计划
在 clickhouse 20.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图形语言描述管道图,默认关闭,需要查看相关的图形需要配合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
|
|