7.ClickHouse系列之查询优化

1. Explain查询计划查看

// 查看执行计划，默认值
EXPLAIN PLAN SELECT arrayJoin([6,6,7])
// AST语法树查看
EXPLAIN AST SELECT numbers FROM system.numbers LIMIT 10;
// 用于优化语法
EXPLAIN SYNTAX SELECT arrayJoin([6,6,7])
// 查看PIPELINE计划
EXPLAIN PIPELINE SELECT arrayJoin([6,6,7])

2. 建表优化

2.1 数据类型

• 时间字段类型：建表时能用数值型或日期时间表示的字段就不要用字符串。虽然ClickHouse底层将DateTime存储为时间戳Long类型，但不建议存储Long类型，因为DateTime不需要经过函数转换处理，执行效率高，可读性好
• 空值存储类型：官方指出Nullable类型几乎总是会拖累性能。因为存储Nullable列时需要创建一个额外的文件来存储NULL的标识(具体var/lib/clickhouse/data/table/column.null.bin),并且Nullable列无法被索引。应直接使用字段默认值表示空，或者自行指定一个在业务中无意义的值

2.2 分区与索引

分区粒度根据根据业务特点决定，不宜过粗过细。一般选择按天分区。必须指定索引列，ClickHouse中索引列即排序列，通过order by指定。组合索引需满足查询频率大在前。基数特别大的列不适宜做索引列

3 ClickHouse内部语法优化规则

3.1 Count优化

如果使用的是count()或count(*)，只要没指定具体字段，且没有where条件，则会直接使用system.tables的total_rows：

SELECT COUNT() FROM datasets.hits_vl;

3.2 子查询\ORDER BY\LIMIT BY\USING KEY重复字段会自动消除

3.3 谓词下推

EXPLAIN SYNTAX SELECT UserID FROM hits_v1 GROUP BY UserID Having UserID='111';
会被自动优化为
EXPLAIN SYNTAX SELECT UserID FROM hits_v1 WHERE UserID='111' GROUP BY UserID;

3.4 聚合函数外推

EXPLAIN SYNTAX SELECT SUM(UserID*2) FROM visits_v1;
会被自动优化为
SELECT SUM(UserID) * 2 FROM visits_v1;

3.5 聚合函数消除

EXPLAIN SYNTAX SELECT SUM(UserID*2),max(VisitID), max(UserID) FROM visits_v1 GROUP BY UserID;
会被自动优化为
EXPLAIN SYNTAX SELECT SUM(UserID) * 2, max(VisitID), UserID FROM visits_v1 GROUP BY UserID;

3.6 三元运算优化

EXPLAIN SYNTAX SELECT number=1?'hello':(number=2?'world':'china') FROM numbers(10) SETTINGS optimize_if_chain_to_multiif=1;
返回优化后的语句：
─explain───────────────────────────────────────────────────────────┐
│ SELECT multiIf(number = 1, 'hello', number = 2, 'world', 'china') │
│ FROM numbers(10)                                                  │
│ SETTINGS optimize_if_chain_to_multiif = 1                         │
└───────────────────────────────────────────────────────────────────┘

4 单表查询优化

4.1 prewhere代替where

prewhere和where语句的作用相同，用来过滤数据。不同之处在于prewhere只支持*MergeTree族系列引擎的表，首先会读取指定的列数据，来判断数据过滤，等待数据过滤之后在读取select声明的列字段来补全其余属性。默认情况下，where条件会自动优化成prewhere。

4.2 数据采样

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

SELECT Title, count(*) AS PageViews FROM hits_v1 SAMPLE 0.1 WHERE CounterID=57 GROUP BY Title;

4.3 列裁剪与分区裁剪

列裁剪即指定所需要的列，而非全量*，分区裁剪就是只读取需要的分区，在过滤条件中指定

4.4 order by结合where limit

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

4.5 避免构建虚拟列

不要在结果集上构建虚拟列，非常消耗资源降低性能。

// 反例
SELECT Income, Age, Income/Age AS Ia FROM datasets.hits_v1;

4.6 uniqCombined替代distinct

性能可提升10倍以上，uniqCombined底层采用类似HyperLogLog算法实现。不建议在千万级数据上执行distinct去重查询，改为近似去重uniqCombined。

4.7 考虑使用物化视图

ClickHouse的物化视图是一种查询结果的持久化。物化视图不会随着基础表的变换而变化，被称为’快照’,
如下表而言，当发布日期大于10月1日，会在物化视图的存储表.inner_id.xxxx中插入数据，而当清空基础表house时，.inner_id.xxxx中的数据并不会丢失。

// 创建物化视图
CREATE MATERIALIZED VIEW study.house_new_mv ENGINE ReplacingMergeTree PARTITION BY toYYYYMMDD(publish_date) ORDER BY(id, city, region, name)
AS SELECT id, city, region, name, publish_date FROM study.house WHERE publish_date > toDate('2022-10-01');
// 插入数据
INSERT INTO study.house VALUES (2, '上海', '静安', '彭一小区', 70000, toDateTime('2022-05-06'));
INSERT INTO study.house VALUES (2, '上海', '静安', '彭一小区', 70000, toDateTime('2022-10-06'));
TRUNCATE TABLE study.house;
SELECT * FROM study.house_new_mv;

5. 用IN代替JOIN

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

SELECT a.* FROM hits_v1 a WHERE a.CounterID in (SELECT CounterID FROM visits_v1);

6. 多表JOIN小表在右

右表关联时被加载到内存与左表进行对比，所以多表JOIN时右表必须是小表。

7. 注意谓词下推

ClickHouse在join查询时不会主动发起谓词下推的操作，需要每个子查询提前完成过滤操作。新版本不存在此问题，可以在查询时EXPLAIN SYNTAX

8. 分布式表使用gobal

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

欢迎关注公众号算法小生或沈健的技术博客shenjian.online