一、建表优化
- 1.1 数据类型
- - 1.1.1 时间字段
  - 1.1.2 空值存储类型
- 1.2 分区和索引
- 1.3 表参数
- 1.4 写入和删除优化
- 1.5 举例
- 1.6 常见配置
- - 1.6.1CPU资源
  - 1.6.2 内存资源
  - 1.6.3 存储
二、语法优化规则
- 2.1 count优化
- 2.2 消除子查询重复字段
- 2.3 谓词下推
- - 2.3.1 having
  - 2.3.2 子查询
- 2.4 聚合计算外推
- 2.5 聚合函数消除
- 2.6 删除重复
- - 2.6.1 删除重复的order by key
  - 2.6.2 删除重复的limit by key
  - 2.6.3 删除重复的USING Key
- 2.7 标量替换
- 2.8 三元运算优化
三、查询优化
- 3.1 单表查询
- - 3.1.1 prewhere代替where
  - 3.1.2 数据采样
  - 3.1.3 列裁剪与分区裁剪
  - 3.1.4 orderby结合where, limit
  - 3.1.5 避免构建虚拟列
  - 3.1.6 uniqCombined替代distinct
  - 3.1.7 其他注意事项
- 3.2 多表关联
- - 3.2.1 准备表和数据
  - 3.2.2 join优化
  - - 3.2.2.1 常规join方式
    - 3.2.2.2 大小表join
    - 3.2.2.3 用in代替join
    - 3.2.2.4 谓词下推
    - 3.2.2.5 分布式表使用global
    - 3.2.2.6 使用字典表
    - 3.2.2.7 提前过滤
- 3.3 总结

一、建表优化

1.1 数据类型

1.1.1 时间字段

时间字段建议存储为DateTime类型，不需要经过函数转换处理，执行效率高，可读性好。

1.1.2 空值存储类型

Nullable类型几乎总是会拖累性能，因为存储Nullable列时需要创建一个额外的文件来存储NULL的标记，并且Nullable列无法被索引。

参考：nullable列，clickhouse中Nullable与非空字段的建表与类型互转

1.2 分区和索引

分区粒度一般按天分区，也可以指定Tuple()，也就是不分区。常规来说，1亿条数据分30个分区比较合适。
必须指定索引列，clickhouse中的索引列即排序列，通过order by指定。通常是查询频率大的在前，高级列在前原则。基数大的不适合做索引列（即重复数据特别少的列）。

1.3 表参数

index_granularity控制索引粒度，默认8192。不保存全量历史数据的可以用TTL。

1.4 写入和删除优化

尽量不要执行单条或小批量删除和插入操作，因为会产生小分区文件，给后台Merge任务带来巨大压力。
不要一次写入太多分区，或数据写入太快，数据写入太快会导致Merge速度跟不上报错。一般建议每秒发起2-3次写入操作，每次操作写入2w-5w条数据。

1.5 举例

参考：ClickHouse元数据
以下例子主要测试两部分内容：一是删除数据时，查看系统表分区情况会发现删除的数据并没有马上删掉；二是使用TTL的效果。

CREATE TABLE test.t_null(
    id String,
    name Nullable(String),
    create_time DateTime default now()
) 
ENGINE = MergeTree 
PARTITION BY id 
ORDER BY id 
TTL create_time + INTERVAL 10 minute ;           --默认是delete

-- 注意8小时时差
insert into test.t_null values
('1', 'alice', '2021-12-25 09:41:00'),
('1', NULL, '2021-12-25 09:41:00'),
('2', 'bob', '2021-12-02 06:41:00'),
('3', 'carolin', '2021-12-02 06:41:00');

--查询结果
select * from test.t_null;

--删除某个分区
alter table test.t_null delete where id = '3';

--系统存储情况，注意观察 name列，删除前后生成的小分区
select 
	partition, 
	name,
	part_type,
	active,
	marks,
	rows,
	--bytes_on_disk,
	--data_compressed_bytes,
	--data_uncompressed_bytes,
	remove_time,
	--partition_id,
	level,
	database,
	table,
	disk_name,
	--path,
	column,
	type,
	column_position
from system.parts_columns
where database='test' and table='t_null';

--合并数据
optimize table test.t_null final;

1.6 常见配置

1.6.1CPU资源

config.xml配置项
users.xml配置项

background_pool_size：后台线程池大小，merge线程就是在该线程池执行，默认16，允许的前提下建议改成cpu个数的2倍(线程数)。

background_schedule_pool_size：执行后台任务的线程数，默认128，建议改成cpu个数的2倍(线程数)。

background_distributed_schedule_pool_size：设置为分布式发送执行后台任务的线程数，默认16，建议改成cpu个数的2倍(线程数)。

max_concurrent_queries：最大并发处理的请求数(包含select, insert等)，默认值100，推荐150-300。

max_threads：单个查询所能使用的cpu个数，默认是cpu核数。

1.6.2 内存资源

max_memory_usage：在users.xml中，表示单次Query占用内存最大值，可以设置稍大。比如128G的内存设置为100G。

max_bytes_before_external_group_by：一般按照max_memory_usage的一般设置，当group使用内存超过阈值后会刷到磁盘运行。建议50G。

max_bytes_before_external_sort：超过时进行溢写磁盘。

max_table_size_to_drop：在config.xml中，默认是50GB，建议修改为0，不限制。

1.6.3 存储

不支持设置多数级目录，为了提升数据io性能，看挂载虚拟券组。

二、语法优化规则

基于RBO(Rule Based Optimization)，参考：
RBO优化器

下面通过explain语句查看执行计划，来看看不同的查询语句执行效果，其中部分是clickhouse自动帮我们进行优化的，但另一部分是在查看优化的执行语句之后，我们需要手动对查询语句进行修改。

2.1 count优化

-- 只要加了具体字段就会导致扫描全表
explain plan select count(UserID) from hits_v1;

-- count(*), count(1)都会优化直接获取count文件
explain plan select count() from hits_v1;

2.2 消除子查询重复字段

explain syntax select UserID, UserID, VisitID from visits_v1;
explain syntax select UserID, UserID as UserID2, VisitID from visits_v1;

2.3 谓词下推

2.3.1 having

当group by有having子句，但是没有with cube, with rollup 或者 with totals修饰的时候，having过滤会下推到where提前过滤。

explain syntax select UserID from hits_v1 group by UserID having UserID = '8585742290196126178';

--优化后
SELECT UserID FROM hits_v1 WHERE UserID = '8585742290196126178' GROUP BY UserID;

2.3.2 子查询

优化后过滤条件被移至子查询中。

explain syntax select * from (select UserID from visits_v1) where UserID = '8585742290196126178';

--优化后
SELECT UserID
FROM	
(SELECT UserID FROM visits_v1 WHERE UserID = '8585742290196126178') 
WHERE UserID = '8585742290196126178'

2.4 聚合计算外推

explain syntax select sum(UserID * 2) from visits_v1

--优化结果
select sum(UserID) * 2 from visits_v1

explain syntax select sum(exp_cnt * 2) from test.test1;

--优化结果
SELECT sum(exp_cnt) * 2 FROM test.test1;

需要注意的是clickhouse并不会自动帮我们优化，可以执行两种查询语句查看执行时间：

--明显第二种方式结果更快
select sum(exp_cnt * 2) from test.test1;
SELECT sum(exp_cnt) * 2 FROM test.test1;

2.5 聚合函数消除

explain syntax
select
	sum(UserID * 2),
	max(VisitID),
	max(UserID)
from visits_v1
group by UserID

--优化后的语句
SELECT
    sum(UserID) * 2,
    max(VisitID),	
    UserID  --去掉无意义的函数
FROM visits_v1	
GROUP BY UserID

2.6 删除重复

2.6.1 删除重复的order by key

explain syntax
select UserID, VisitID
from visits_v1
order by
	UserID ASC,
	UserID DESC,
	VisitID ASC,
	VisitID ASC

--优化后语句
explain syntax
select UserID, VisitID
from visits_v1
order by
	UserID ASC,
	VisitID ASC

2.6.2 删除重复的limit by key

参考：LIMIT BY

explain syntax
select *
from visits_v1
order by CounterID
limit 2 by CounterID, CounterID  --每个单独的CounterID保留两条数据
LIMIT 10

--优化后的语句
select *
from visits_v1
order by CounterID
limit 2 by CounterID
LIMIT 10

2.6.3 删除重复的USING Key

explain syntax
select 
	a.UserID,
	b.VisitID,
	a.URL,
	b.UserID
from hits_v1 AS a
left join visits_v1 as b using (UserID, UserID) --on a.UserID=b.UserID

--返回优化后的语句
select 
	UserID,
	VisitID,
	URL,
	b.UserID
from hits_v1 AS a
left join visits_v1 as b using (UserID)

2.7 标量替换

如果子查询只返回一行数据，在被引用的时候用标量替换。在本例中，指的是``with (select sum(bytes) from system.parts where active) as total_disk_usage`会被优化为用一个数值代替。

explain syntax
with (select sum(bytes) from system.parts where active) as total_disk_usage
select 
	table, (sum(bytes) / total_disk_usage) * 100 as table_disk_usage, total_disk_usage
from system.parts
group by table
order by table_disk_usage desc
limit 10;

--优化结果，WITH语句内容似乎是与查询语句绑定的，不能单独执行，不然结果是不对的
WITH identity(CAST(0, 'UInt64')) AS total_disk_usage
SELECT 
    table,
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    total_disk_usage
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

2.8 三元运算优化

开启参数 optimize_if_chain_to_multiif，则三元运算符会被替换成multiIf函数

explain syntax
select number = 1 ? 'hello' : (number = 2 ? 'world' : 'atguigu')
from numbers(10)
settings optimize_if_chain_to_multiif = 1;

--优化结果
SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'atguigu')
FROM numbers(10)
SETTINGS optimize_if_chain_to_multiif = 1;

三、查询优化

3.1 单表查询

3.1.1 prewhere代替where

where是先过滤出满足条件的行，然后再选择需要的列。prewhere只支持MergeTreee族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后再读取select声明的列字段来补全其余属性，这样可以避免整行扫描。

当查询列明显多于筛选列时，prewhere可十倍提升查询性能，自动优化执行过滤阶段的数据读取方式，降低io操作。默认情况下，where条件会自动优化成prewhere。
关闭自动优化：set optimize_move_to_prewhere=0;

explain syntax
select 
	WatchID, EventTime, UserID, URLDomain, RefererDomain
from datasets.hits_v1
where UserID = '8585742290196126178'

--优化结果
SELECT WatchID, EventTime, UserID, URLDomain, RefererDomain
FROM datasets.hits_v1
PREWHERE UserID = '858574229019612617

不能自动优化的情况：

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

explain syntax
select UserID
from datasets.hits_v1
where UserID = '8585742290196126178';
--结果没有优化
SELECT UserID
FROM datasets.hits_v1
WHERE UserID = '8585742290196126178';

3.1.2 数据采样

采样可以极大提升数据分析的性能

select Title, count(*) as PageViews
from datasets.hits_v1
sample 0.1
where CounterID = 57
group by Title
order by PageViews desc limit 1000

3.1.3 列裁剪与分区裁剪

数据量太大时应避免使用select * 操作，尽量只取用到的列，必要时可对分区进行过滤

-- 反例
select * from datasets.hits_v1;

-- 正例
select WatchID, JavaEnable, Title, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID
from datasets.hits_v1;

-- 分区裁剪
select WatchID, JavaEnable, Title, EventTime, EventDate, CounterID, ClientIP, ClientIP6, RegionID, UserID
from datasets.hits_v1
where EventDate = '2014-03-23';

3.1.4 orderby结合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;

3.1.5 避免构建虚拟列

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

-- 反例
select Income, Age, Income/Age as IncRate from datasets.hits_v1;
-- 正例
select Income, Age from datasets.hits_v1;

3.1.6 uniqCombined替代distinct

性能可提升10倍以上，uniqCombined底层采用类似HyperLogLog算法实现，能接受数据误差，可直接使用这种去重方式提升查询性能。

-- 反例
select count(distinct UserID) from datasets.hits_v1;
-- SELECT uniqExact(UserID) FROM datasets.hits_v1

-- 正例
select uniqCombined(UserID) from datasets.hits_v1;

3.1.7 其他注意事项

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

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

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

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

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

3.2 多表关联

3.2.1 准备表和数据

# 创建小表
create table visits_v2
engine = CollapsingMergeTree(Sign)
partition by toYYYYMM(StartDte)
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, StartDate, intHash32(UserID))
sample by intHash32(UserID)
settings index_granularity = 8192
as select * from hits_v1 where 1=0;

3.2.2 join优化

3.2.2.1 常规join方式

表A和表B进行join时，先将B加载到内存中，然后A每一条数据都去内存中查询表B进行匹配。

3.2.2.2 大小表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;

大表在右

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

3.2.2.3 用in代替join

多表联查时，查询的数据仅从其中一张表出时，可以考虑用in操作而不是join

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

3.2.2.4 谓词下推

谓词下推的基本思想即：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。

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

explain syntax
select a.* from hits_v1 a left join visits_v2 b on a.CounterID = b.CounterID
having a.EventDate = '2014-03-17';
# 最后一行变成了PREWHERE EventDate = '2014-03-17'

explain syntax
select a.* from hits_v1 a left join visits_v2 b on a.CounterID = b.CounterID
having b.StartDate = '2014-03-17';
# 最后一行变成了WHERE StartDate = '2014-03-17'

尽量在join前进行where过滤，提升查询效率

explain syntax
select a.* from (
    select * from hits_v1 
    where EventDate = '2014-03-17'
)
a left join visits_v1 b on a.CounterID = b.CounterID;
# PREWHERE EventDate = '2014-03-17'

on关联条件中不可有过滤条件

# 报错，关联条件不可用来做过滤
explain syntax
select a.* from hits_v1 a left join visits_v2 b on a.CounterID = b.CounterID
and a.EventDate = '2014-03-17';

3.2.2.5 分布式表使用global

两张分布式表上的in和join之前必须加上global关键字，右表只会在接收查询请求的那个节点查询一次，并将其分发到其他节点上。如果不加global关键字的话，每个节点都会单独发起一次对右表的查询，而右表又是分布式表，就导致右表一共会被查询N的2次方(N是分布式表的分片数量)，这就是查询放大，会带来很大开销。可能会减小为N次。

3.2.2.6 使用字典表

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

3.2.2.7 提前过滤

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

3.3 总结

尽量不要用join
能过滤先过滤，尤其是右表
右边放小表
特殊场景可以考虑使用字典表
可以替换的话，尽量不要用join，比如用In实现

参考：
https://play.clickhouse.com/
http://nicethemes.cn/news/txtlist_i169603v.html

目录