1. 单表查询

1.1 prewhere替代where

prewhere 和 where 语句的作用相同，用来过滤数据。不同之处在于 prewhere 只支持*MergeTree族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取 Select 声明的列字段来补全其余属性。
当查询列明显多于筛选列时使用 prewhere 可十倍提升查询性能，prewhere 会自动优化执行过滤阶段的数据读取方式，降低 IO 操作。
在某些场合下，prewhere 语句比 where 语句处理的数据量更少性能更高。

# 关闭where自动转prewhere(默认情况下， where条件会自动优化成prewhere)
set optimize_move_to_prewhere=0; 
# 使用where
select
	WatchID, 
	JavaEnable, 
	Title, 
	GoodEvent, 
	EventTime, 
	EventDate, 
	CounterID, 
	ClientIP, 
	ClientIP6, 
	RegionID, 
	UserID, 
	CounterClass, 
	OS, 
	UserAgent, 
	URL, 
	Referer, 
	URLDomain, 
	RefererDomain, 
	Refresh, 
	IsRobot, 
	RefererCategories, 
	URLCategories, 
	URLRegions, 
	RefererRegions, 
	ResolutionWidth, 
	ResolutionHeight, 
	ResolutionDepth, 
	FlashMajor, 
	FlashMinor, 
	FlashMinor2
from
	datasets.hits_v1
where
	UserID='3198390223272470366';

# 使用prewhere关键字
select
	WatchID, 
	JavaEnable,
	Title, 
	GoodEvent, 
	EventTime, 
	EventDate, 
	CounterID, 
	ClientIP, 
	ClientIP6, 
	RegionID, 
	UserID, 
	CounterClass, 
	OS, 
	UserAgent, 
	URL, 
	Referer, 
	URLDomain, 
	RefererDomain, 
	Refresh, 
	IsRobot, 
	RefererCategories, 
	URLCategories, 
	URLRegions, 
	RefererRegions, 
	ResolutionWidth, 
	ResolutionHeight, 
	ResolutionDepth, 
	FlashMajor, 
	FlashMinor, 
	FlashMinor2
from
	datasets.hits_v1
prewhere
	UserID='3198390223272470366';

默认情况，我们肯定不会关闭 where 自动优化成 prewhere，但是某些场景即使开启优化，也不会自动转换成 prewhere，需要手动指定 prewhere：

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

1.2 数据采样

通过采样运算可极大提升数据分析的性能：

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

采样修饰符只有在 MergeTree engine 表中才有效，且在创建表时需要指定采样策略。

1.3 列裁剪与分区裁剪

数据量太大时应避免使用select *操作，查询的性能会与查询的字段大小和数量成反比，字段越少，消耗的 IO 资源越少，性能就会越高。

# 反例：
select * from datasets.hits_v1;
# 正例：
select
	WatchID, 
	JavaEnable, 
	Title, 
	GoodEvent, 
	EventTime, 
	EventDate, 
	CounterID, 
	ClientIP, 
	ClientIP6, 
	RegionID, 
	UserID
from datasets.hits_v1;

分区裁剪就是只读取需要的分区，在过滤条件中指定：

select WatchID, 
	JavaEnable, 
	Title, 
	GoodEvent, 
	EventTime, 
	EventDate, 
	CounterID, 
	ClientIP, 
	ClientIP6, 
	RegionID, 
	UserID
from datasets.hits_v1
where EventDate='2022-04-02';

1.4 order by结合where、limit

千万以上数据集进行 order by 查询时需要搭配 where 条件和 limit 语句一起使用：

# 正例：
SELECT
	UserID,
	Age
FROM hits_v1 
WHERE CounterID=57
ORDER BY Age DESC LIMIT 1000

# 反例：
SELECT
	UserID,
	Age
FROM hits_v1 
ORDER BY Age DESC

1.5 避免构建虚拟列

如非必须，不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前端进行处理，或者在表中构造实际字段进行额外存储。

# 反例：
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;

# 正例：拿到Income和Age后，考虑在前端进行处理，或者在表中构造实际字段进行额外存储
SELECT Income,Age FROM datasets.hits_v1;

1.6 uniqCombined替代distinct

uniqCombined 相比 distinct 性能可提升 10 倍以上，uniqCombined 底层采用类似 HyperLogLog 算法实现，能接收 2% 左右的数据误差，可直接使用这种去重方式提升查询性能。Count(distinct) 会使用 uniqExact 精确去重。
不建议在千万级不同数据上执行 distinct 去重查询，改为近似去重 uniqCombined。

# 反例：
select count(distinct rand()) from hits_v1;
# 正例：
SELECT uniqCombined(rand()) from datasets.hits_v1;

1.7 使用物化视图

这个后面再单开一篇博客说吧，东西比较多。。。

1.8 其他注意事项

查询熔断

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

关闭虚拟内存

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

配置 join_use_nulls

为每一个账户添加 join_use_nulls 配置，左表中的一条记录在右表中不存在，右表的相应字段会返回该字段相应数据类型的默认值，而不是标准 SQL 中的 Null 值。

批量写入时先排序

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

关注CPU

CPU 一般在 50% 左右会出现查询波动，达到 70% 会出现大范围的查询超时，CPU 是最关键的指标，要非常关注，这一点在《ClickHouse实时分析（九）- ClickHouse常规优化方案》也重点提过了。

数据变更

对 ClickHouse 数据的增删改操作都会产生新的临时分区，会给 MergeTree 带来额外的合并任务。因此，数据变更操作不宜太频繁，这样会产生非常多的临时分区。一次操作的数据也不能太快。临时分区写入过快会导致 Merge 速度跟不上而报错。
官方一般建议一秒钟发起一次左右的写入操作，每次操作写入的数据量保持在 2W~5W 之间，具体根据服务器性能而定（这一点在常规优化方案中也提到过）。

2. 多表关联

2.1 准备表和数据

# 创建小表
CREATE TABLE visits_v2 
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from visits_v1 limit 10000;

# 创建 join 结果表，避免控制台疯狂打印数据
CREATE TABLE hits_v2 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from hits_v1 where 1=0;

2.2 用IN代替JOIN

当多表联查时，查询的数据仅从其中一张表出时，可考虑用 IN 操作而不是 JOIN：

insert into hits_v2
select
	a.*
from
	hits_v1 a
where
	a. CounterID in (select CounterID from visits_v1);

2.3 大小表JOIN

多表 join 时要满足小表在右的原则，右表关联时被加载到内存中与左表进行比较，ClickHouse 中无论是 Left join 、Right join 还是 Inner join 永远都是拿着右表中的每一条记录到左表中查找该记录是否存在，所以右表必须是小表。

insert into table hits_v2
select
	a.*
from
	hits_v1 a
left join
	visits_v2 b
on a.CounterID=b.CounterID;

2.4 注意谓词下推

ClickHouse 在 join 查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作，需要注意的是，是否执行谓词下推，对性能影响差别很大（新版本中已经不存在此问题，但是需要注意谓词的位置的不同依然有性能的差异）。

Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a.CounterID=b.CounterID
having a.EventDate = '2022-04-02';

Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a.CounterID=b.CounterID
having b.StartDate = '2022-04-02';

insert into hits_v2
select a.* from hits_v1 a left join visits_v2 b on a.CounterID=b.CounterID
where a.EventDate = '2022-04-02';

insert into hits_v2
select a.* from (
	select * from 
	hits_v1 
	where EventDate = '2022-04-02'
) a left join visits_v2 b on a.CounterID=b.CounterID;

2.5 分布式表使用GLOBAL

两张分布式表上的 IN 和 JOIN 之前必须加上GLOBAL关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加 GLOBAL 关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询 $N^2$ 次（ $N$ 是该分布式表的分片数量），这就是查询放大，会带来很大开销。

2.6 使用字典表

将一些需要关联分析的业务创建成字典表进行 join 操作，前提是字典表不宜太大，因为字典表会常驻内存。

2.7 提前过滤

通过增加逻辑过滤可以减少数据扫描，达到提高执行速度及降低内存消耗的目的。

参考文献

【1】https://clickhouse.com/docs/zh/
【2】https://www.bilibili.com/video/BV1Yh411z7os?from=search&seid=4579023877699743987&spm_id_from=333.337.0.0
【3】https://clickhouse.com/docs/zh/