实战攻略 | ClickHouse优化之FINAL查询加速

【本文作者：擎创科技资深研发禹鼎侯】

查询时为什么要加FINAL

我们在使用ClickHouse存储数据时，通常会有一些去重的需求，这时候我们可以使用ReplacingMergeTree引擎。这个引擎允许你存储重复数据，但是在merge的时候会根据order by的字段进行去重。

它的去重逻辑是使用新数据覆盖旧数据。

但是很多时候，merge并不是实时的，他可能会在数据插入后几分钟甚至十几分钟后才会进行，而我们对数据的查询却往往却是实时的。这个时候就必然出现一个问题：

那些还没有来得及merge的数据，查询出来会有重复。这与我们所期望的效果是背道而驰的。

事实上，即便是merge发生了，我们也不能保证数据一定没有重复。我们举个简单的例子：

如上图所示：p1~p4是四个原始part，发生合并之后数据进行了去重，最终合并成了p1_4，对id去重之后有四个值，分别为1，2，3，4，假设此时又有一个新part p5插入，此时去查询仍然能搜到两条id为2和3数据。

因此，即使发生了合并，我们也不能保证数据就一定是唯一的。

ClickHouse为了解决这个问题，提供了FINAL语法，从字面意义上理解，就是返回merge最终态的数据结果。它的效果与OPTIMIZE FINAL 是一致的。

不过，SELECT FINAL仅是在读时合并，并不会实际将底层数据合并，而OPTIMIZE FINAL则是实实在在的发生合并，这是二者的本质的区别。

FINAL查询会有什么问题

那么大家肯定也已经发现了。这种加了FINAL的查询，性能会有很大的问题！

因为FINAL相当于在查询时执行一次OPTIMIZE FINAL，而这个操作本质上是将同一个partition内的数据合并成一个part。在数据量不大的情况下还好，这个操作还能很快返回，如果数据集比较大，比如单个分区的数据已经达到了上千万级，甚至上亿级，那么一次FINAL可真是要了亲命了。

为了让大家直观的感受到这种性能差距，我们来举个例子。

我们使用ClickHouse官方提供的压测数据来进行举例：

https://clickhouse.com/docs/en/getting-started/example-datasets/opensky#validate-data

建表语句如下：

CREATE TABLE opensky(    `callsign` String,    `number` String,    `icao24` String,    `registration` String,    `typecode` String,    `origin` String,    `destination` String,    `firstseen` DateTime,    `lastseen` DateTime,    `day` DateTime,    `latitude_1` Float64,    `longitude_1` Float64,    `altitude_1` Float64,    `latitude_2` Float64,    `longitude_2` Float64,    `altitude_2` Float64)ENGINE = ReplacingMergeTreePARTITION BY toYYYYMMDD(day)ORDER BY (origin, destination, callsign)

我们将数据导入两遍，这样就一定会得到重复的数据：

ck94 :) select count() from opensky;
SELECT count()FROM opensky
Query id: 0d65affc-873e-4847-9ee0-ae749b091bd1
┌───count()─┐│ 127132244 │└───────────┘
1 row in set. Elapsed: 0.007 sec.

接下来我们来执行一个查询语句：

ck94 :) select avg(altitude_1) from opensky where day >= '2020-10-01 00:00:00' and typecode = 'B737';
SELECT avg(altitude_1)FROM openskyWHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')
Query id: bcb16bd6-91fc-4993-a4de-87e1731d7760
┌────avg(altitude_1)─┐│ 1458.7475907858743 │└────────────────────┘
1 row in set. Elapsed: 0.186 sec. Processed 34.74 million rows, 812.04 MB (186.88 million rows/s., 4.37 GB/s.)Peak memory usage: 14.39 MiB.

可以看到，当我们不使用final时，上面这个sql仅仅使用了0.186秒就返回了结果。

接下来我们看看加了final的效果：

ck94 :) select avg(altitude_1) from opensky final where day >= '2020-10-01 00:00:00' and typecode = 'B737';
SELECT avg(altitude_1)FROM openskyFINALWHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')
Query id: 63f760a6-9bca-4a40-a7b3-237eacae356a
┌────avg(altitude_1)─┐│ 1336.4280164372838 │└────────────────────┘
1 row in set. Elapsed: 12.784 sec. Processed 48.60 million rows, 3.06 GB (3.80 million rows/s., 239.37 MB/s.)Peak memory usage: 4.16 GiB.

上面这个SQL足足耗费了12.784秒！这可是近70倍的差距，我们当前的数据集还不算大，如果数据集再大一点，那么这个查询性能慢的问题将无限扩大化，最终影响到生产使用。

这让老夫如何是好

那么，这个问题如此明显，有没有什么好的优化手段呢？

自然是有的。

接下来就来介绍两种方法来做final查询的优化。

优化手段1：使用PREWHERE

所谓PREWHERE，就是在查询之前，先将数据过滤掉一部分，这样，目标数据集的规模小了，执行FINAL自然会快了。

我们使用EXPLAIN SYNTAX 执行计划来看一下为啥第一条不加FINAL的SQL快得离谱：

EXPLAIN SYNTAXSELECT avg(altitude_1)FROM openskyWHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')
Query id: 046a721e-7690-4d0b-9457-20e825d0e152
┌─explain─────────────────────────────────────────────────────────┐│ SELECT avg(altitude_1)                                          ││ FROM opensky                                                    ││ PREWHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737') │└─────────────────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.003 sec.

可以看到，上面这条SQL，实际上clickhouse优化器已经对其进行改写成了PREWHERE。

我们再看看加了FINAL的执行计划：

EXPLAIN SYNTAXSELECT avg(altitude_1)FROM openskyFINALWHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')
Query id: c4ae0a63-7a55-4216-815a-25ff44ee538e
┌─explain──────────────────────────────────────────────────────┐│ SELECT avg(altitude_1)                                       ││ FROM opensky                                                 ││ FINAL                                                        ││ WHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737') │└──────────────────────────────────────────────────────────────┘

那就离了个大谱了！

为神马就加了个FINAL，他就不能使用PREWHERE优化了？

按照官方的说法：如果查询条件里带有主键字段，clickhouse会默认会使用PREWHERE进行优化，可以提前减少数据集的大小，一来避免过多内存造成OOM，二来自然是可以加速查询了。

然而十分悲催的是，这个优化对加了FINAL的查询不会生效。

那么我们怎么能让它使用到PREWHERE优化呢？

这里提供两种方法：

一种是显式指定。

ck94 :) select avg(altitude_1) from opensky final prewhere day >= '2020-10-01 00:00:00' and typecode = 'B737';
SELECT avg(altitude_1)FROM openskyFINALPREWHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')
Query id: ce72c7a9-38a1-4ae8-957e-d951e0dd9d0b
┌────avg(altitude_1)─┐│ 1424.1051732278174 │└────────────────────┘
1 row in set. Elapsed: 1.429 sec. Processed 48.60 million rows, 3.06 GB (34.01 million rows/s., 2.14 GB/s.)Peak memory usage: 1.19 GiB.

可以看到，我们改成显式使用PREWHERE之后，查询性能立马减少到了1.429秒，差不多有9倍左右的提升，可见这个提升是巨大的。

第二是利用子查询命中PREWHERE。

那么，有木有什么办法不显式指定PREWHERE，但是让其有这个优化呢？而且我们并不能保证所有的条件都会带上主键索引。

前面介绍过，当查询条件带主键，且不加final的时候，clickhouse会默认使用PREWHERE进行优化，那么我们是不是可以先用子查询命中PREWHERE，然后再final呢？

我们将SQL改成如下样子：

ck94 :) select avg(altitude_1) from opensky where (origin, destination, callsign) in (select origin, destination, callsign from opensky where day >= '2020-10-01 00:00:00' and typecode = 'B737') and day >= '2020-10-01 00:00:00' and typecode = 'B737';
SELECT avg(altitude_1)FROM openskyWHERE ((origin, destination, callsign) IN (    SELECT        origin,        destination,        callsign    FROM opensky    WHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737'))) AND (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')
Query id: 61ae0590-de1a-44b1-866f-0c54a919c762
┌────avg(altitude_1)─┐│ 1458.7475907858748 │└────────────────────┘
1 row in set. Elapsed: 0.768 sec. Processed 69.47 million rows, 4.09 GB (90.43 million rows/s., 5.32 GB/s.)Peak memory usage: 69.15 MiB.

耗时降到了0.768秒，这已经非常接近不加final的裸查询了。牛逼class！

优化手段2：禁用final查询跨分区merge

ClickHouse本身是允许跨分区进行数据替换的，这无疑复杂化了FINAL查询的逻辑。但事实上，只要我们的数据分区合理，这种情况完全可以规避掉。这时候我们可以使用do_not_merge_across_partitions_select_final=1这个配置来禁用跨final查询时分区合并。这意味着clickhouse仅在本分区内进行merge去重。

我们来看看效果：

ck94 :) select avg(altitude_1) from opensky final where day >= '2020-10-01 00:00:00' and typecode = 'B737' SETTINGS do_not_merge_across_partitions_select_final=1;
SELECT avg(altitude_1)FROM openskyFINALWHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')SETTINGS do_not_merge_across_partitions_select_final = 1
Query id: 41aa4fc9-5933-43e1-86bf-a374eab15dd9
┌────avg(altitude_1)─┐│ 1458.6043678101414 │└────────────────────┘
1 row in set. Elapsed: 0.732 sec. Processed 34.74 million rows, 2.18 GB (47.44 million rows/s., 2.98 GB/s.)Peak memory usage: 2.86 GiB.

仅耗时0.732秒，同样牛的一批。

事实上，我们建议在部署集群时，将这个配置作为默认配置进行设置，在知名项目clickhouse-operator 中，这个配置就是默认打开的。

那么，综合上面两种优化手段，这就是最终形态了：

ck94 :) select avg(altitude_1) from opensky where (origin, destination, callsign) in (select origin, destination, callsign from opensky where day >= '2020-10-01 00:00:00' and typecode = 'B737') and day >= '2020-10-01 00:00:00' and typecode = 'B737' SETTINGS do_not_merge_across_partitions_select_final = 1;
SELECT avg(altitude_1)FROM openskyWHERE ((origin, destination, callsign) IN (    SELECT        origin,        destination,        callsign    FROM opensky    WHERE (day >= '2020-10-01 00:00:00') AND (typecode = 'B737'))) AND (day >= '2020-10-01 00:00:00') AND (typecode = 'B737')SETTINGS do_not_merge_across_partitions_select_final = 1
Query id: 298c3d92-67da-4379-8e1d-43f85c99cc2c
┌────avg(altitude_1)─┐│ 1458.7475907858745 │└────────────────────┘
1 row in set. Elapsed: 0.678 sec. Processed 69.47 million rows, 4.09 GB (102.45 million rows/s., 6.03 GB/s.)Peak memory usage: 69.89 MiB.