ClickHouse学习笔记（二）：执行计划、建表优化、语法优化规则、查询优化、数据一致性

一、Explain查看执行计划

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

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

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

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

2）AST：用于查看语法树

3）SYNTAX：用于优化语法

4）PIPELINE：用于查看PIPELINE计划

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

2、案例
1）查看PLAN

1. 简单查询

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', 'atguigu') 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;

二、建表优化

1、数据类型

1）、时间字段的类型

建表时能用数值型或日期时间型表示的字段就不要用字符串

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

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;

1 + NULL = NULL：

 单独的文件来存储NULL值：

[root@aliyun ~]# cd /var/lib/clickhouse/data/default/t_null/
[root@aliyun t_null]# ll
total 16
-rw-r----- 1 clickhouse clickhouse 91 Sep 21 08:28 sizes.json
-rw-r----- 1 clickhouse clickhouse 28 Sep 21 08:28 x.bin
-rw-r----- 1 clickhouse clickhouse 28 Sep 21 08:28 y.bin
-rw-r----- 1 clickhouse clickhouse 28 Sep 21 08:28 y.null.bin

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

必须指定索引列，ClickHouse中的索引列即排序列，通过order by指定，一般在查询条件中经常被用来充当筛选条件的属性被纳入进来；可以是单一维度，也可以是组合维