制作简单的个人网站,网站后台的东西都是要上传到在虚拟主机上面吗,四川省住房和城乡建设厅官网证件查询,网络虚拟号【本文作者#xff1a;擎创科技资深研发 禹鼎侯】 查询时为什么要加FINAL
我们在使用ClickHouse存储数据时#xff0c;通常会有一些去重的需求#xff0c;这时候我们可以使用ReplacingMergeTree引擎。这个引擎允许你存储重复数据#xff0c;但是在merge的时候会根据order …【本文作者擎创科技资深研发 禹鼎侯】 查询时为什么要加FINAL
我们在使用ClickHouse存储数据时通常会有一些去重的需求这时候我们可以使用ReplacingMergeTree引擎。这个引擎允许你存储重复数据但是在merge的时候会根据order by的字段进行去重。
它的去重逻辑是使用新数据覆盖旧数据。
但是很多时候merge并不是实时的他可能会在数据插入后几分钟甚至十几分钟后才会进行而我们对数据的查询却往往却是实时的。这个时候就必然出现一个问题
那些还没有来得及merge的数据查询出来会有重复。这与我们所期望的效果是背道而驰的。
事实上即便是merge发生了我们也不能保证数据一定没有重复。我们举个简单的例子 如上图所示p1~p4是四个原始part发生合并之后数据进行了去重最终合并成了p1_4 对id去重之后有四个值分别为1234 假设此时又有一个新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_final1这个配置来禁用跨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_final1;
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.
虽然还是比不加final的要慢一丢丢但总体上已经到了可以接受的程度了。
———— THE END ————
