执行计划

从我们编写的SQL有时并不能看出实际底层运行的逻辑，而要性能优化的话，阅读执行计划可以帮助理解运行逻辑，从而提供优化思路。

另一方面，ClickHouse会对书写的SQL做一定的优化，可以通过查看优化执行计划帮助我们改进SQL语句

需要注意的是ClickHouse在20.6版本起才支持查看执行计划，之前的版本只能通过将日志级别设置为TRACE，查看执行日志了解运行过程

执行计划基本语法：

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

查看执行计划一共有四种运行模式：

1. PLAN：默认值，当不指定模式时默认为PLAN默认
   
   整体逻辑与Hive执行计划类似，不过内容更加简洁易读，可以配置参数：
   header 打印计划中各个步骤的 head 说明，默认关闭，默认值 0
   description 打印计划中各个步骤的描述，默认开启，默认值 1
   actions 打印计划中各个步骤的详细信息，默认关闭，默认值 0
2. AST：用于查看语法树，较少用到
3. SYNTAX：查看优化语法
   与其他几个不同，返回的是优化之后的SQL语句，开发中可以用于查看自己书写的SQL是否还有需要优化的地方，但是有些优化设置需要手动开启，例如optimize_if_chain_to_multiif配置，默认为关闭状态（0），当打开（1）后，会自动将多层嵌套的if判断修改为multiif函数
4. PIPELINE：查看PIPELINE计划
   可以配置参数：
   header 打印计划中各个步骤的 head 说明，默认关闭;
   graph 用 DOT 图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz 查看；
   actions 如果开启了 graph，紧凑打印打，默认开启。

数据类型

时间类型

在Hive中经常使用String类型来存储日期和时间，但是ClickHouse中应该注意，日期和时间应该使用专用的Date和DateTime存储，也不要使用Long类型时间戳，因为因为 DateTime 不需要经过函数转换处理，执行效率高、可读性好。

例如：

create table t_type2(
id UInt32,
sku_id String,
total_amount Decimal(16,2) ,
create_time Int32 -- 这里是Long或者String类型都会报错
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(toDate(create_time)) -- 需要转换一次，否则报错
primary key (id)
order by (id, sku_id);

空值存储

官方已经指出 Nullable 类型几乎总是会拖累性能，因为存储 Nullable 列时需要创建一个
额外的文件来存储 NULL 的标记
这里我们创建一个样例表查看数据存储情况：

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

在来到数据存储路径下查看：

可以看到除了建表时指定的两列单独存储外，还有一个y.null.bin文件，专门存储y字段中的空值，ClickHouse是列式存储的，当出现null值时必然会影响存储速度，同时读取速度也会受到影响(读取到该列时需要从两个文件读取数据并按顺序排序)，且Nullable的列无法被索引

基于以上情况，在实际使用过程中，一般根据业务指定无意义的值代替空值（字符串中的空字符串，整型中的0或者-1，浮点型中的极大或极小值）

分区和索引

分区的目的和Hive相同，可以避免全表扫描，一般选择按天分区，也可以指定为Tuple()，即多级分区，分区粒度根据业务特点决定，不宜过粗或过细。以单表一亿数据为例，分区大小控制在 10-30 个为最佳。

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

索引粒度

这里的索引粒度指的就是建表语句最后的Index_granularity，默认值是 8192，含义是索引列排序后，每隔8192条建立一个索引值，这个值是经过测试的，如非必须不建议调整。

索引粒度和被索引列的重复程度相关，如果被索引列重复程度过高，那么很多索引值就是无用的，此时可以适当增大索引粒度。而索引粒度减小意味着索引值增多，过多的索引值反而会减慢查询速度。因此实际使用过程中一般取默认值

数据写入

前篇介绍过ClickHouse适用于批量的写入操作，所以尽量不要执行单条或小批量删除和插入操作，这样会产生小分区文件，给后台Merge 任务带来巨大压力

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

参数配置

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

内存配置

其他更多参数可以查阅官方文档：config.xml配置和users.xml配置
存储
ClickHouse 不支持设置多数据目录，为了提升数据 io 性能，可以挂载虚拟券组，一个券组绑定多块物理磁盘提升读写性能，多数据查询场景 SSD 会比普通机械硬盘快 2-3 倍。

SQL语法优化

这里很多优化都是ClickHouse底层默认自动优化的，即便输入的SQL逻辑效率不够高，ClickHouse也会适当修改输入的SQL提高执行效率，对于开发来说，仍然需要了解这些优化点，因为ClickHouse的自动优化并非适用于所有场景，且复杂逻辑场景下的优化可能还不够好，这是就需要手动调整SQL逻辑进行优化

谓词下推

当 group by 有 having 子句，但是没有 with cube、 with rollup 或者 with totals 修饰的时
候， having 过滤会下推到 where 提前过滤。例如下面的查询， HAVING name 变成了 WHERE name，在 group by 之前过滤：

EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID HAVING UserID ='8585742290196126178';

子查询场景下也可以优化

EXPLAIN SYNTAX
SELECT *
FROM (SELECT UserID FROM visits_v1)
WHERE UserID = '8585742290196126178'

稍微复杂一点的场景：

EXPLAIN SYNTAX
SELECT * FROM (
SELECT *
FROM (SELECT UserID FROM visits_v1)
UNION ALL
SELECT *
FROM (SELECT UserID FROM visits_v1)
)
WHERE UserID = '8585742290196126178'

谓词下推的目的就是在数据处理的多个步骤中，将过滤操作尽可能地提前，减少后续操作的计算压力以及内存和IO的压力，后续很多操作也是类似的思路

聚合计算外推

聚合外推指在聚合操作中对每一列进行的同一个操作，如果对于其聚合后的结果执行一遍就可以达到同样的效果，那么这样就减少了很多计算的操作

EXPLAIN SYNTAX
SELECT sum(UserID * 2)
FROM visits_v1

prewhere替代where

Prewhere和where语句的作用相同，用来过滤数据。不同之处在于prewhere只支持MergeTree系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤
之后再读取select声明的列字段来补全其余属性。
(对比普通的where过滤逻辑，对于行式存储会首先过滤得到符合条件的所有行的所有列的数据，然后根据select指定的字段从中选取指定的列，而prewhere只读取过滤列的指定行，首次读取的数据量更少)

当查询列明显多于筛选列时使用Prewhere可十倍提升查询性能，Prewhere会自动优化
执行过滤阶段的数据读取方式，降低io操作。在某些场合下，prewhere语句比where语句处理的数据量更少性能更高。

自动将where优化为prewhere的配置：set optimize_move_to_prewhere=1;默认值为1，开启状态

有些场景下，where不会被优化为prewhere：

• 使用常量表达式
• 使用默认值为 alias 类型的字段
• 包含了 arrayJOIN， globalIn， globalNotIn 或者 indexHint 的查询
• select 查询的列字段和 where 的谓词相同
• 使用了主键字段

采样

采样由于结果并不稳定，所以实际在处理数据时并不经常使用，但是在观察数据分布时十分常用，Hive也同样支持采样操作(分桶和sample函数)，这里记录下ClickHouse的采样语法：

SELECT Title,count(*) AS PageViews
FROM hits_v1
SAMPLE 0.1 #代表采样 10%的数据,也可以是具体的条数
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000

列裁剪和分区裁剪

两个裁剪的思路同样是尽早地减少无用的数据，列裁剪和分区裁剪都很容易理解，在建表时指定了partition by字段，数据会根据这个分区字段存储在不同位置，我们在开始抽取数据时通过where指定分区，就只指定需要地分区，这样就可以减少扫描的数据量。

而列裁剪对应的是ClickHouse的列式存储，在select分支选择字段式，避免直接写作select *，只抽取实际需要的字段，这样ClickHouse就不会去扫描不需要字段的存储文件(列名.bin)，字段越少，消耗的 io 资源越少，性能就会越高。

避免构建虚拟列

这里的虚拟列指的是没有出现在表中的字段，而是通过字段之间的计算得出的列值，因为虚拟列的数值每行都需要独立进行计算，所以会对处理速度造成一定影响。

虚拟列可以的话尽量避免，在得到最后结果后通过其他手段得出结果(例如报表展示时可以使用FineReport得到结果)，当然实际工作中不可能完全避免虚拟列的使用，此时只能考虑计算处理的各个阶段，在数据量尽可能少的阶段进行虚拟列数据的运算

小表在右

在MySQL和Hive的关联中，习惯上小表在前大表在后，因为关联是会将左表作为驱动表加载到内存与右表关联，而在ClickHouse中这点相反，ClickHouse中两表关联时（无论内外左右关联），例如A Join B，会把B表全部数据加载到内存，再逐条扫描A表与内存中的数据进行关联

因此ClickHouse中表关联Join时小表要放在右边；

使用Global

ClickHouse中有普通Join和Global Join两种，这里首先了解下分布式多表Join原理：
当分布式表关联时，ClickHouse会将左表的分布式表转化为本地表，并将SQL分发到集群的各个节点上，然后各个节点执行关联任务；
此时如果被关联的右表也是分布式表的话，那么每个节点在执行任务时就会同样在多个节点上执行查询任务，即当集群有N个节点，左表是分布式表的情况下，如果右表是本地表，那么关联查询总共需要执行N次，而当右表也是分布式表时，就需要执行N^2次；

在上面的查询模式下，虽然得到了查询结果，但是查询过程实际上被放大了。此时应该改用Global Join，Global Join的原理和普通Join类似，区别在于当右表为分布式表时，只有(左表分布的)首个 节点会到右表分布的各个节点查询，得到查询结果后会将结果分发到(左表分布的)其余节点上，这样就省去了(左表分布的)其他节点到右表所分布的节点逐个查询数据的过程；

-- 使用的语法和普通Join相同
SELECT a_.i, a_.s, b_.t
FROM a_all as a_
GLOBAL JOIN
b_all AS b_ ON a_.i = b_.i

使用In替代Join

由于ClickHouse的关联查询效率较低，当多表联查时， 查询的数据仅从其中一张表出时， 可考虑用 IN 操作而不是 JOIN，这里通过实际测试说明效果；

使用in查询：

使用Join查询：

可以从查询时间上看出，两者效率差距还是比较明显的

其他注意事项

（1）查询熔断
为了避免因个别慢查询引起的服务雪崩的问题，除了可以为单个查询设置超时以外，还可以配置周期熔断，在一个查询周期内，如果用户频繁进行慢查询操作超出规定阈值后将无法继续进行查询操作。

（2）关闭虚拟内存
物理内存和虚拟内存的数据交换，会导致查询变慢，资源允许的情况下关闭虚拟内存。

（3）配置join_use_nulls
为每一个账户添加join_use_nulls配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准SQL中的Null值，这项配置需要谨慎，在确保数据正确性的前提下配置。

（4）批量写入时先排序
批量写入数据时，必须控制每个批次的数据中涉及到的分区的数量，在写入之前最好对需要导入的数据进行排序。无序的数据或者涉及的分区太多，会导致ClickHouse无法及时对新导入的数据进行合并，从而影响查询性能。

（5）关注CPU
cpu一般在50%左右会出现查询波动，达到70%会出现大范围的查询超时，cpu是最关键的指标，要非常关注。