目录
- 一、建表优化
- 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实现