ClickHouse 的执行计划以及优化策略(十六)

​ 本文来源： ( https://www.cnblogs.com/traditional/tag/ClickHouse：一款速度快到让人发指的列式存储数据库/ )

执行计划

如果要在 ClickHouse 20.6 版本之前查看 SQL 语句的执行计划，需要在 config.xml 里面将日志级别设置为 trace。

1
2
3
4
<!-- 新版本默认是 trace -->
<logger>
    <level>trace</level>
</logger>

然后还要真正执行相应的 SQL 语句，在执行日志里面查看，很明显这是非常不方便的。于是 ClickHouse 在 20.6 版本里面引入了原生的执行计划的语法（此时处于试用期阶段），并在 20.6.3 版本中正式转正。

我们当前系列都是基于 ClickHouse 的 21.7.3.14 版本。

然后我们来介绍如何查看执行计划，不过介绍之前我们先创建一张数据表，这次我们采用真实的数据。首先 ClickHouse 官方提供了两个数据集，其中数据行数和字段数都非常的大，不亚于一些公司生产环境上的数据，我们来下载一下。

1
2
3
4
5
# 下载数据集，这里的数据集不是 CSV、JSON，而是 .bin、.mrk 等物理文件
# 也就是说数据集本身就是符合 ClickHouse 物理存储的
curl -O https://datasets.clickhouse.tech/hits/partitions/hits_v1.tar
# 所以我们直接解压到拷贝到 /var/lib/clickhouse 目录下即可
tar -xvf hits_v1.tar -C /var/lib/clickhouse

然后我们就可以使用 hits_v1 这张表了，我们之前说过，必须要先创建表然后再导入数据，因为创建表的时候会生成一些元信息，存储在 /var/lib/clickhouse/metadata 目录下，而光有数据没有元信息是不行的。但对于当前而言则不用事先创建表，因为 ClickHouse 将元信息也准备好了，所以我们直接拷贝过去即可。压缩包解压之后，会有一个 data 目录和一个 metadata 目录，所以我们解压到 /var/lib/clickhouse 中，会自动将 data 目录里面的内容合并到 /var/lib/clickhouse 的 data 目录中，将 metadata 目录里面的内容合并到 /var/lib/clickhouse 的 metadata 目录中。

1
2
3
4
[root@satori data]# ls
datasets  default  system
[root@satori data]# ls datasets
hits_v1

我们看到里面多了一个 datasets 目录，datasets 目录下才是 hits_v1，显然我们后续需要使用 datasets.hits_v1 进行查询。当然数据集还有一份，我们按照相同的套路即可。

1
2
curl -O https://datasets.clickhouse.tech/visits/partitions/visits_v1.tar
tar -xvf visits_v1.tar -C /var/lib/clickhouse

我们来确认一下：

1
2
[root@satori datasets]# ls
hits_v1  visits_v1

显然数据集已经准备完毕，不过我们当前使用的是 root 用户，还应该要确保 clickhouse 用户有相应的操作权限。

1
2
3
4
chown clickhouse:clickhouse /var/lib/clickhouse/data -R
# 然后重启 ClickHouse，因为我们不是通过 CREATE TABLE 创建的表
# 因此要重启，不然 ClickHouse 是不知道我们通过拷贝文件的方式新增了两张表
clickhouse restart

重启之后，执行 SQL 语句进行查看：

img

数据量还是不少的，datasets.hits_v1 有将近 900 万条数据、字段数 130 多个，datasets.visit_v1 有 160 多万条数据、字段数为 180 多个，还是很大的。

具体都有哪些字段，可以通过 /var/lib/clickhouse/metadata/datasets 下的 .sql 文件进行查看。

有了数据集，我们就可以介绍查询计划了。当然使用这种规模的数据集有些小题大做，不过既然 ClickHouse 是为大数据准备的，那么使用大一点的数据集也无妨，而且我们后面也会经常使用这些数据集。

基本语法

在 MySQL 中查看执行计划使用的语法是什么呢？没错，EXPLAIN，在 ClickHouse 中也是如此，只不过 ClickHouse 将 EXPLAIN 变得更加丰富。

1
2
EXPLAN [AST | SYNTAX | PLAN | PIPELINE] [SETTINGS = value, ...]
SELECT ... [FORMAT ...]

我们看一下第一个中括号里面的内容，ClickHouse 除了可以让我们查看执行计划之外，还可以查看很多其它内容。

1）AST：查看编译之后的语法树，这个不是很常用

1
2
3
EXPLAIN AST
SELECT UserID, count() FROM datasets.hits_v1 
WHERE EventDate < toDate('2014-03-17') GROUP BY UserID;

执行一下，查看生成的语法树：

img

2）SYNTAX：用于优化语法，有时我们指定的查询语句未必是最优的，那么 ClickHouse 在底层会进行优化，EXPLAIN SYNTAX 可以返回对一条 SQL 语句进行优化后的结果。通过对比优化前和优化后的 SQL 语句，可以有助于我们理解 ClickHouse 的优化机制

1
2
3
EXPLAIN SYNTAX
SELECT UserID, count() count FROM datasets.hits_v1 
WHERE EventDate < toDate('2014-03-17') GROUP BY UserID ORDER BY count LIMIT 10;

img

我们看到仅仅是做了一些格式上调整，但优化前和优化后的语句本质上没差别，证明对于当前查询而言，我们写的 SQL 语句就是最优的。因为这条语句太简单了，ClickHouse 没有什么可优化的。

然后我们来写几个非常规的语句，比如三元表达式：

1
2
3
-- 这是嵌套的三元表达式，那么 ClickHouse 会怎么优化呢?
EXPLAIN SYNTAX
SELECT number < 5 ? '小于 5' : (number = 5 ? '等于 5' : '大于 5') FROM numbers(10);

img

注意：这里并没有开启优化，只不过是将三元表达式使用 if 语句替换了，因为没有嵌套的三元表达式在底层就是对应 if 函数的一个调用。只不过 ClickHouse 将一些比较特殊的函数调用，抽象成了一些语法糖，但本质上是没有变化的，所以当前的 SQL 语句并没有得到优化。

事实上，ClickHouse 对三元表达式的优化默认是关闭的，我们可以将其打开。

1
2
-- 见名知意，就是当出现 if 的嵌套时，优化成 multiIf
SET optimize_if_chain_to_multiif = 1;

img

SYNTAX 还是很常用的，我们写完一条 SQL 语句之后，可以直接 EXPLAIN SYNTAX 一下，然后将返回的结果替换掉我们原来的 SQL 语句。

PLAN：查看执行计划，默认选项

1
2
3
EXPLAIN
SELECT UserID, count() count FROM datasets.hits_v1 
WHERE EventDate < toDate('2014-03-17') GROUP BY UserID ORDER BY count LIMIT 10;

img

我们看到图中的 EXPLAIN 后面并没有带上 PLAN，说明 PLAN 是默认选项，然后查看执行计划时还可以设置一些额外的参数：

  • header：打印计划中各个步骤的 head 说明，默认值为 0 表示关闭，如果开启，设置为 1
  • description：打印计划中各个步骤的描述，就是图中括号里面的部分，默认值为 1 表示开启，如果关闭，设置为 0
  • actions：打印计划中各个步骤的详细信息，默认值为 0 表示关闭，如果开启，设置为 1
1
2
3
EXPLAIN header = 1, actions = 1
SELECT UserID, count() count FROM datasets.hits_v1 
WHERE EventDate < toDate('2014-03-17') GROUP BY UserID ORDER BY count LIMIT 10;

输出的内容非常多，可以测试一下。

PIPELINE：查看 PIPELINE 计划，类似于 PLAN

1
EXPLAIN PIPELINE SELECT sum(number) FROM numbers(100000) GROUP BY number % 20;

img

类似于 PLAN，查看 PIPELINE 计划时还可以设置一些额外的参数：

  • header：打印计划中各个步骤的 head 说明，默认值为 0 表示关闭，如果开启，设置为 1
  • graph：用 DOT 图形语言描述管道图，默认关闭，
  • actions：表示当开启 graph 之后是否紧凑打印，默认开启
1
2
EXPLAIN PIPELINE header = 1, graph = 1 
SELECT sum(number) FROM numbers(100000) GROUP BY number % 20;

可以自己查看一下输出。

建表优化

我们在创建表的时候，需要指定的内容比较多，比如 ORDER BY、表引擎、表参数、分区字段等等，这些对后续数据的查询效率都是有影响的，当然指定合适的数据类型也是非常重要的。下面就来介绍一下常见的优化手段。

数据类型

在建表的时候能用数值类型和日期时间类型表示的字段就不要使用字符串，虽然字符串类型在以 Hive 为中心的数仓建设中非常常见，但 ClickHouse 却并非如此。我们知道在 Hive 中，日期一般都用字符串，不会特意使用 Date 类型。但在 ClickHouse 中，能不要 String 就不要用，因为后期还要转换。

对于 DateTime，ClickHouse 底层会转成时间戳进行存储，但我们不要显式地使用 UInt64 类型来存储。因为 DateTime 不需要经过函数转换处理，执行效率高，可读性好。

1
2
3
4
5
6
7
8
9
CREATE TABLE test_t (
    id UInt32,
    product String,
    amount Decimal(16, 2),
    create_time UInt32  -- 这里使用了整数存储时间
) ENGINE = ReplacingMergeTree(create_time)
PARTITION BY toYYYYMMDD(toDate(create_time))  -- 需要转换一次，否则报错
PRIMARY KEY id
ORDER BY id

除了日期类型和数值类型不用字符串表示之外，Null 也是拖累性能的一个罪魁祸首，因为官方已经指出 Null 会影响性能了。因为存储 Nullable 类型的列时，需要创建一个额外的文件来存储 Null 标记，并且 Nullable 类型的列无法被索引。因此除了极特殊的情况，否则不要将列设置为 Nullable，可以用一个不可能出现的默认值、或者在业务中无意义的来代指空，例如将 id 设置为 -1 表示该商品没有 id，而不是使用 Null。

分区和索引

分区粒度根据业务特点决定，但不宜过粗或者过细，如果数据之间是严格按照时间来划分，比如经常要按天、按月或者按年汇总处理，那么不妨选择按天分区或者按月分区；如果数据按照地区来划分，比如经常针对不同的地区单独汇总，那么不妨按照地区分区。那么分区到底要分多少个区呢？以单表一亿条数据为例，分区大小控制在 10 到 30 个最好。所以如果按照时间分区，那么我们一般都会按天、按月分区，至于按分钟分区则 dark不必，因为这样分区目录就太多了。

还有指定索引列，默认通过 ORDER BY 指定。ORDER BY 在 ClickHouse 中是最重要的，因为分区内的排序通过 ORDER BY 指定，主键（索引）默认也是由 ORDER BY 指定，即使我们显式地使用 PRIMARY KEY 不使用 ORDER BY，那么主键也必须是排序键的前缀。当然这里的 ORDER BY 指的是建表时的 ORDER BY，不是查询语句中的 ORDER BY。

然后我们在通过 ORDER BY 指定索引列的时候，应该指定查询中经常被用来充当筛选条件的列，可以是单一维度，也可以是组合维度，如果是组合维度，那么索引列要满足查询频率大的在前原则。还有基数特别大的不适合做索引列，基数大指的就是那些重复数据非常少的列。

表参数

index_granularity 是用来控制索引粒度的，默认是 8192，如非必须不建议调整。另外，如果一张表不是必须要保留全量历史数据，则建议指定 TTL，可以免去手动清理过期历史数据的麻烦，TTL 也可以通过 ALTER TABLE 语句随时修改。

写入和删除优化

尽量不要执行单条或小批量删除、插入操作，这样会产生小分区文件，给后台 Merge 任务带来巨大压力。

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

常见配置

我们知道配置文件位于 /etc/clickhouse-server 目录下，里面有 config.xml 和 users.xml，我们之前一直说 config.xml，但其实 users.xml 也非常重要。它们都表示服务端的配置，而区别主要在于 config.xml 里面的配置是无法覆盖的，我们在命令行经常会使用 set 命令将某个参数进行修改，这些参数则是放在 users.xml 中。当然一个设置即可以在 users.xml 中出现，也可以在 config.xml 中出现，服务端首先会从 config.xml 中找，找不到再去 config.xml 中找。

而我们修改配置主要是为了调整 CPU、内存、IO，瓶颈主要在这里。因为 ClickHouse 会有后台线程 Merge 数据，所以非常的吃 CPU；当然加载数据，对内存也是一个考量；同理还有 IO，因为要从磁盘上读取大量数据。

下面来介绍与这三个配置有关的参数。

1）CPU

background_pool_size：位于 users.xml 中，非常重要的一个参数，表示后台线程池内的线程数量，Merge 线程就是在该线程池中执行，该线程池不仅仅是给 Merge 线程用的。默认值为 16，允许的前提下建议改成 CPU 个数的二倍。所以 ClickHouse 不建议和 HDFS、Yarn 等一起部署，因为 ClickHouse 太吃资源了，不然也达不到如此可观的速度

background_schedule_pool_size：位于 users.xml 中，表示执行后台任务的线程数，默认值为 128，允许的前提下建议改成 CPU 个数的二倍

background_distributed_schedule_pool_size：位于 users.xml 中，表示分布式发送执行后台任务的线程数，默认值为 16，允许的前提下建议改成 CPU 个数的二倍

max_concurrent_queries：位于 config.xml 中，表示最大并发处理的请求数（包含 SELECT、INSERT 等等），默认值为 100，推荐 150 ~ 300，不够再加

max_threads：位于 users.xml 中，表示单个查询所能使用的最大 CPU 个数，默认是 CPU 核数

**以上是关于 CPU 相关的设置，如果发现机器吃不消了，那么不妨减少一下线程数。 **

2）Memory

max_memory_usage：位于 users.xml 中，表示单次 Query 占用内存的最大值，该值可以设置的大一些，这样可以提高集群查询的上限。当然也要保留一些给 OS，比如 128G 的内存，设置为 100G 即可

img

max_bytes_before_external_group_by：表示 GROUP BY 使用的内存的最大值，一旦超过这个最大值，那么会刷新到磁盘进行 GROUP BY，一般按照 max_memory_usage 的一半设置即可。因为 ClickHouse 聚合分两个阶段，查询并建立中间数据、合并中间数据

max_bytes_before_external_sort：表示 ORDER BY 使用的内存的最大值，一旦超过这个最大值，那么会刷新到磁盘进行 ORDER BY。如果不设置该值，那么当内存不够的时候直接报错，设置了该值，ORDER BY 在内存不够的时候可以基于磁盘完成，但是速度相对就慢了（实际测试发现慢得多，甚至无法接受）。该参数和上一个参数都在 users.xml 中设置。

max_table_size_to_drop：位于 config.xml 中，应用于需要删除表或分区的情况，默认是 50 GB，意思是如果删除 50 GB 以上的数据会失败。建议设置为 0，表示无论分区表 多大都可以删除

3）IO

和 HDFS 不同，ClickHouse 不支持设置多数据目录，为了提升 IO 性能，可以挂载虚拟券组（将多块磁盘虚拟成一块磁盘），通过一个券组绑定多块物理磁盘提升读写性能。或者使用 SSD，但是成本就比较高了。

ClickHouse 语法优化规则

很多数据库底层都内置了优化器，定义好了许多的优化规则，用于给我们的 SQL 语句进行优化，比如大小表 JOIN、谓词下推等等，就是为了避免开发人员执行慢查询。

那么 ClickHouse 会对哪些查询进行优化呢？我们来看一下。

COUNT 优化

我们说如果统计一张表有多少行，那么使用 count() 或者 count(*) 即可，此时会直接读取 count.txt。还记得这个 count.txt 文件吗？我们在介绍 MergeTree 的时候说过，该文件里面存储了表的行数，当使用 count() 或者 count(*) 的时候，直接读取该文件即可，此时是不需要全表扫描的。类似于关系型数据库也是如此，MySQL 在使用 count() 的时候也是直接计算的 B+ 树的叶子结点个数。

但当我们 count 一个字段的时候，那么就必须要全表扫描了，而且我们说过 count 字段的时候统计的是该字段中非空的值的个数。如果该字段中没有空值，count(字段) 的结果和 count()、count(*) 是相等的。

img

对比输出信息的话，我们看到 count(字段) 进行了全表扫描。

再比如 count(1)，我们看看它会不会被优化：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
satori :) EXPLAIN SYNTAX SELECT count(1) FROM datasets.hits_v1;

EXPLAIN SYNTAX
SELECT count(1)
FROM datasets.hits_v1

Query id: f59337ec-58e3-4a37-b00d-eaa796f54f65

┌─explain───────────────┐
│ SELECT count()        │
│ FROM datasets.hits_v1 │
└───────────────────────┘

2 rows in set. Elapsed: 0.004 sec.

因为 1 是一个整型，没有什么实际意义，所以直接变成了 count()。

谓词下推

在 SQL 中，谓词就是返回 boolean 值的函数，或隐式转换为 bool 的函数，说白了你就简单理解为 WHERE 语句即可。而谓词下推指的是将过滤表达式尽可能移动至靠近数据源的位置，从事后过滤变成事前过滤。

举个最简单的栗子就是 WHERE 和 HAVING，我们知道 WHERE 是发生在 GROUP BY 之前的，HAVAING 发生在 GROUP BY 之后。

1
2
3
4
5
6
7
SELECT UserID, count() FROM datasets.hits_v1
GROUP BY UserID HAVING UserID = 1785640464950496314;
/*
┌──────────────UserID─┬─count()─┐
│ 1785640464950496314 │     105 │
└─────────────────────┴─────────┘
*/

上面这行 SQL 语句执行的时候虽然没有任何问题，但很明显这是一个糟糕的 SQL 语句，因为要先对将近 900 万的数据进行聚合，然后选择 UserID 为 1785640464950496314 的记录。既然如此，那我们为什么不能先把 UserID 为 1785640464950496314 的记录选出来，然后再单独进行聚合呢？这样的话数据量会少很多。

img

我们看到在优化之后的 SQL 语句将条件从 HAVING 移到了 WHERE，所以将过滤表达式尽可能移动至靠近数据源的位置，在计算之前先将无用数据过滤掉，这个过程就是谓词下推。

当然谓词下推不仅仅是这里的 HAVING，子查询也支持，举个栗子，我们要根据 UserID 从 hits_v1 表中查询几个用户的记录，但是这些值必须存在于 visits_v1 的 UserID 字段中。

1
2
SELECT UserID, URL FROM datasets.hits_v1
WHERE UserID IN (329024891984319329, 3341630990649416532, 3444082748272603552);

显然这是非常简单的，但如果我们规定 UserID 还必须要出现在 visits_v1 表的 UserID 字段中，那么要怎么做呢？最简单的做法就是一个条件即可。

1
2
3
4
SELECT UserID, URL
FROM datasets.hits_v1
WHERE UserID IN (329024891984319329, 3341630990649416532, 3444082748272603552)
  AND UserID IN (SELECT UserID FROM datasets.visits_v1);

但很明显这条语句就不是最优解，因为子查询会扫描全表，也就是 visits_v1 会全量读取。既然 UserID 要在两个表中都出现，那么就应该优先把过滤条件放在子查询里面。

1
2
3
4
5
6
SELECT UserID, URL
FROM datasets.hits_v1
WHERE UserID IN
      (SELECT UserID  -- 数据量大的话，还可以进行去重
       FROM datasets.visits_v1
       WHERE UserID IN (329024891984319329, 3341630990649416532, 3444082748272603552));

这种做法显然更优，因为 visits_v1 不需要全量读取，但 ClickHouse 目前还做不了这种优化，ClickHouse 所能做的子查询谓词下推还是很有限的。当然不光是子查询，相比 Hive，ClickHouse 所做的优化非常有限，不同的 SQL 语句效率相差十倍以上都是很正常的，因为我们写 SQL 就不可以肆无忌惮。

作为表进行 JOIN 的子查询会消除重复字段

如果子查询中重复选择了某个字段，那么当它作为表进行 JOIN 的时候，会去除重复字段。

1
2
3
4
5
6
7
EXPLAIN SYNTAX
SELECT a.UserID, b.VisitID, a.URL
FROM datasets.hits_v1 a LEFT JOIN (
    SELECT UserID, UserID, VisitID
    FROM datasets.visits_v1
) b
USING(UserID) LIMIT 3

这里进行 JOIN 的右表是一个子查询，而在这个子查询里面我们选择了两次 UserID，那么 ClickHouse 会进行优化，变成只选择一次。

img

可能有人好奇，如果我给第二个 UserID 起一个别名会怎么样呢？答案是即使起了别名，仍然只会选择一次。

注意：这里的子查询在 JOIN 的时候会删除重复字段，但如果不是在 JOIN 的时候就不一样了。

1
2
3
4
5
6
EXPLAIN SYNTAX
SELECT UserID, URL
FROM (
    SELECT UserID, UserID, URL
    FROM datasets.hits_v1
) LIMIT 3

这里的子查询当中我们选择了两个 UserID，那么 ClickHouse 会不会变成一个呢？

img

我们看到并没有优化掉，所以 ClickHouse 所做的优化还是比较有限的。

聚合计算外推

什么是聚合计算外推呢？举个栗子：

1
SELECT sum(RequestNum * 2) FROM datasets.hits_v1;

你觉得上面的 SQL 有能够优化的地方吗？我们看看 ClickHouse 是如何做的。

img

没优化的时候，相当于是在 sum 之前先给每一条数据做一次乘法运算，然后进行 sum；优化之后则是先进性 sum，最后只对总和进行一次乘法运算，显然后者更优。

聚合函数消除

我们在使用 GROUP BY 的时候有一个限制，那就是 SELECT 中没有使用聚合函数的字段必须出现在分组字段中。举几个栗子：

1
2
3
4
5
6
7
8
9
10
11
-- 字段 b 出现在了 SELECT 中，并且没有使用聚合函数，所以它一定要出现在分组字段（GROUP BY）中
-- 但没有出现，所以报错
SELECT a, b, count(c) FROM t GROUP BY a;

-- 此时没有问题，a 和 b 都出现在 GROUP BY 中
-- 至于字段 c，它是以 count(c) 的形式出现的，使用了聚合函数，所以没问题
SELECT a, b, count(c) FROM t GROUP BY a, b;

-- 这条语句也是非法的，因为 b 没有出现在 GROUP BY 中，至于 count(b) 和 b 无关
-- 既然 SELECT 中出现了没有使用聚合函数的字段 b，那么它就必须要出现在 GROUP BY 中
SELECT a, b, count(b), count(c) FROM t GROUP BY a;

当然这些属于基础内容了，我主要想表达的是，我们可不可以对分组字段使用聚合函数呢？比如说：

1
SELECT max(UserID), max(Age), min(Age), sum(Age) FROM datasets.hits_v1 GROUP BY Age

很明显是可以的，但是这么做没有任何意义，因为分组就是把分组字段对应的值相同的归为一组，比如这里的 age，所以每一组的 age 的值都是一样的。既然都一样，那么做聚合就没有太大意义，因此 ClickHouse 会那些对分组字段使用 min、max、any 的聚合函数给删掉。因为每一组的所有值都是一样的，最小值、最大值、第一行的值，三者之间没差别。

img

我们看到聚合函数 min、max 被剥掉了，只留下了 Age 字段，因为分组字段的值都是一样的，min、max、any 没有意义。但聚合函数仅限于 max、min、any，如果是 sum 就不会了，虽然从业务的角度上来说也没有太大意义，但毕竟 sum 涉及到加法运算，所以它不会被剥掉。

删除重复的 ORDER BY KEY

类似于消除重复字段，如果指定了多个相同的排序字段，那么只会保留一个。

1
2
EXPLAIN SYNTAX SELECT UserID FROM datasets.hits_v1 
ORDER BY Age, Age, Age DESC, Age DESC;

img

我们看到即使不同的排序，也会只保留相同排序字段的第一个，因为同一个字段即升序又降序本身就很奇怪。

删除重复的 LIMIT BY KEY

还记得 LIMIT BY 吗？”LIMIT N BY 字段” 表示按照字段进行分组，然后选出每组的前 N 条数据。如果 BY 后面的字段重复了，那么也会删除掉。

1
2
EXPLAIN SYNTAX SELECT UserID, URL FROM datasets.hits_v1 
LIMIT 3 BY UserID, UserID

img

删除重复的 USING KEY

USING 也是如此，直接看例子吧。

1
2
3
EXPLAIN SYNTAX
SELECT a.UserID, a.UserID, b.VisitID, a.URL, b.UserID
FROM datasets.hits_v1 a LEFT JOIN datasets.visits_v1 b USING(UserID, UserID);

USING 里面指定了两个 UserID，那么会变成一个。

img

USING 里面指定了两个 UserID，那么会变成一个，即使我们指定了前缀，比如 USING(a.UserID, b.UserID)，那么依旧会被优化成一个 UserID。

但是说实话，删除重复的 ORDER BY KEY、LIMIT BY KEY、USING KEY，正常情况下很难出现，因为谁会没事故意将一个字段重复写两遍啊。当然字段多了倒是有可能发生，因为字段一多就可能忘记某个字段已经写过一遍了，但是字段少的情况下几乎不可能发生。

所以这个 ClickHouse 的优化机制有点把人当傻子，大概感觉就是当你写了一个 1 + 1 = 3，那么它能帮你改成 1 + 1 = 2，然而查询一旦复杂，它就无法优化了。所以一切还需要我们来保证，当然后续 ClickHouse 的优化机制会变得越来越完善。

标量替换

这个主要体现在 WITH 子句上面，我们最开始介绍 WITH 子句的时候说过，WITH 子句可以给一个普通的表达式赋值，也可以给一个查询赋值，但查询只能返回一行数组。最终会将其作为一个标量，后续查询时直接用这个标量进行替换即可。这背后也是 ClickHouse 给我们做的优化，举个例子：

1
2
3
4
5
6
WITH (SELECT sum(data_uncompressed_bytes) FROM system.columns) AS total_bytes
SELECT database, 
       (sum(data_uncompressed_bytes) / total_bytes) * 100 AS database_disk_usage
FROM system.columns
GROUP BY database
ORDER BY database_disk_usage DESC

注意 WITH 语句中的 total_bytes，它是对 data_uncompressed_bytes 进行 sum 所得到的结果，如果在查询中多次使用 total_bytes，那么难道每次都要计算一遍吗？显然不是的，这个值是提前算好的，是一个标量，因为只有一行数据，如果都多列就是一个元组。后续使用的都是已经算好的值。

三元运算符优化

如果开启了 optimize_if_chain_to_multiif 参数，那么三元运算符会被替换成 ，multiIf 函数，之前说过，这里就不再赘述了。

查询优化

介绍了一些 ClickHouse 的优化规则，我们来看一下在编写 SQL 时如何手动进行优化，或者说有哪些可以优化的点。因为 ClickHouse 的优化规则（或者说内置的优化器）实在太简单了，但凡有点关系型数据库经验的人都不会那么写，因此在编写 SQL 语句的时候只能靠我们保证质量。那么来看看都有哪些注意的点。

PREWHERE 替代 WHERE

PREWHERE 和 WHERE 语句的作用相同，用来过滤数据，但是 PREWHERE 只支持 MergeTree 系列的引擎。WHERE 语句是读取所有的字段，然后进行数据过滤，而 PREWHERE 则是指定了哪些字段就读取哪些字段。比如 age > 18 and length > 160，WHERE 的话会读取全部字段，然后进行数据过滤，再根据 SELECT 中指定的字段进行丢弃。PREWHERE 则是只读取 age 和 length 两个字段，因为过滤条件只有这两个字段，而将数据过滤之后，再根据 SELECT 中指定的字段进行补全（或丢弃）。

所以两者的区别在于读取的数据量不同，当查询列明显多于筛选列时，使用 PREWHERE 可以十倍提升性能。当然这些我们之前在介绍子句的时候说过了，并且我们说过 ClickHouse 会自动将 WHERE 优化成 PREWHERE，因此我们直接用 WHERE 就好。当然我们还说了有几种情况，ClickHouse 不会自动优化，因为在这几种情况下，优化带来的性能提升不大，具体可以回去看看。

1
EXPLAIN SELECT * FROM datasets.hits_v1 WHERE UserID = 610708775678702928

img

在使用 * 的时候，ClickHouse 会自动展开成所有字段，然后重点是我们看到 WHERE 被替换成了 PREWHERE，证明确实会自动优化，当然我们说过可以通过设置 optimize_move_to_prewhere 为 1、0 进行开启、关闭，默认是开启的。另外这个配置可以通过 set 设置，那么它位于哪里呢？没错，显然是 users.xml 中。

数据采样

当我们要求数据的实时性高于数据的准确性时，数据采样就很有用了。记得在大四实习的时候，当时负责给各大上市公司做审计，由于数据量非常庞大，算一次要花上好几个小时。所以每次都先采样，只算百分之 10 到百分之 20 的数据，如果得出来的结果符合正常预期，那么再跑全量数据，这样会稳妥一些。如果上来就跑全量数据，最后发现结果算的不对就尴尬了。

当然我当时选择采样只是简单的对程序的准确性进行一些检测，但实际生产中的程序基本上都是准确的，这个时候如果用户执行了一个查询，那么为了很快的给出结果，选择随机采样是最合适的方式。以我之前的经验，如果数据倾斜不严重的话，那么采样 10% 的数据和全量数据计算出来的结果差别很小，当然具体怎么做还是要取决于你的业务。

1
2
SELECT ... FROM ... SAMPLE 0.1
WHERE ...

SAMPLE 放在 FROM 之后、WHERE 之前，至于具体用法之前已经说过了，可以回头看一下。

列裁剪与分区裁剪

ClickHouse 非常适合存储大数据量的宽表，因此我们应该避免使用 SELECT * 操作，这是一个非常影响的操作。应当对列进行裁剪，只选择你需要的列，因为字段越少，消耗的 IO 资源就越少，从而性能就越高。

而分区裁剪就是只读取需要分区，在过滤条件中指定，所以设计一个合适的分区表对后期查询是非常有帮助的。。

ORDER BY 应当于 WHERE、LIMIT 一起使用

对千万级以上的数据集进行排序的时候一定要搭配 WHERE 或 LIMIT 使用，可能有人觉得我只是排个序而已，为啥还要有这么多限制。因为事实上我们很少会对数据进行全局排序，而且数据量一大，全局排序的话内存很容易爆掉；如果设置了 max_bytes_before_external_sort，那么全局排序会在磁盘上进行，此时速度又是一个难以忍受的地方。因此在使用 ORDER BY 的时候，需要搭配 WHERE、LIMIT。

避免构建虚拟列

虚拟列指的就是我们自己构造出来的字段，而在原表中是没有的，举个栗子：

1
SELECT A, A + 1 FROM table;

A 是表中的字段，但 A + 1 明显不是，它是我们构造出来的，所以叫虚拟列。但如果非必须的话，最好不要再结果集上构造虚拟机列，因为虚拟列非常消耗资源性能，可以考虑在拿到数据之后由前后端进行处理。

uniqCombined 替代 count(DISTINCT)

我们之前说过 ClickHouse 提供了一些语法糖，例如这里的 count(DISTINCT column) 实际上就是 countDistinct(column)，只不过 ClickHouse 提供了类似于关系型数据库中 count(DISTINCT) 语法。并且在具体执行的时候，底层都对应 uniqExact 函数，举个例子：

1
2
SELECT count(DISTINCT UserID), countDistinct(UserID), uniqExact(UserID) 
FROM datasets.hits_v1;

img

但还是建议使用 count(DISTINCT)，因为这几个都是等价的，那么自然选一个看起来最熟悉的。那么这个我们说的 uniqCombined 有什么关系呢？原因是 uniqExact 是精确去重并统计数量，如果我们在数量上对统计的数据的误差有一定的容忍性，那么可以使用 uniqCombined，该函数使用类似 HyperLogLog 的算法，在速度上可以提升 10 倍以上，但牺牲了一些准确率。

img

JOIN 操作

在介绍 JOIN 之前我先说两句，很多公司在设计关系型数据库的表结构时，都会遵循相应的范式，但对于数据仓库而言是完全不需要的，数仓的重点在于分层。对于 OLAP 型的列式存储数据库而言，尤其是 ClickHouse，能不用 JOIN 就不用 JOIN，最好是单表操作，因此这就需要我们保证数据有冗余度，但这在数仓建设中完全 OK 。并且对于 ClickHouse 而言，它的 JOIN 也是比较奇葩的，那么它是怎么做的呢？

首先不管是 LEFT 还是 RIGHT，当 A 表和 B 表进行 JOIN 的时候，ClickHouse 都会将 B 表加载到内存，然后遍历 A 表数据，查询 B 表中有没有能与之关联上的数据，因此这就引出了第一个优化的原则：当大小表 JOIN 的时候，要保证小表在右侧。

1
2
3
4
5
6
7
8
9
10
-- hits_v1 的数据量要远大于 visits_v1，然后我们来 JOIN 试一下
-- 这里为了避免输出大量信息，我们使用 count(*) 代替
SELECT count(*) FROM datasets.hits_v1 a
LEFT JOIN datasets.visits_v1 b
USING(CounterID);

-- 上面是 hits_v1 作为左表进行左关联，等价于如下：将 hits_v1 作为右表进行右关联
SELECT count(*) FROM datasets.visits_v1 b
RIGHT JOIN datasets.hits_v1 a
USING(CounterID);

img

我们看到效率确实有差异，而 JOIN 之后的数据量比 hits_v1 表还要多，说明中间产生了笛卡尔积。如果不想产生笛卡尔积，那么只需要在 LEFT JOIN 和 RIGHT JOIN 的前面加上 ANY 即可，默认是 ALL。

但还是上面那句话，能不用 JOIN 就不要用 JOIN，当涉及到两张表的时候，看看是否可以用子查询来替代。

然后是谓词下推，我们举个栗子：

1
2
3
4
SELECT a.UserID, a.Age, b.CounterID 
FROM datasets.hits_v1 a
LEFT JOIN datasets.visits_v1 b
USING(CounterID) WHERE a.EventDate = '2020-04-17'

上面会先对两张表进行 JOIN，完事之后再进行过滤，既然如此的话，那么为什么不能先过滤然后再进行 JOIN 呢？

1
2
3
4
SELECT a.*, b.CounterID 
FROM (SELECT UserID, Age FROM datasets.hits_v1 WHERE EventDate = '2020-04-17') a
LEFT JOIN datasets.visits_v1 b
USING(CounterID)

下面的做法会比上面要快，因为在 JOIN 之前就将数据过滤掉了一部分，不要小看这一点，有时对性能的影响很大。并且 ClickHouse 不会主动帮我们发起谓词下推的操作，需要我们自己手动完成。可能有人好奇，如果将过滤条件放在 ON 子句后面会怎么样，答案是会报错，因为 ClickHouse 不允许 ON 子句中出现过滤条件。

小结

以上就是 ClickHouse 关于优化方面的内容，说实话都比较简单，总之重点就是我们在写 SQL 的时候一定要注意，不能够随心所欲，因为 ClickHouse 所能做到的优化是非常有限的。