翻译原文：https://altinity.com/blog/clickhouse-aggregation-fun-part-2-exploring-and-fixing-performance
前一篇文章我们已经解释了Clickhouse数据库是如何用并行处理的方式收集聚合结果，然后用归并的方式得到最终的结果的。同时，文章还介绍了system.query_log以及服务端的trace和debug日志。这些工具对于理解聚合函数的行为都十分重要。
在这篇文章中我们将用我们学到的知识来探索真实查询的性能，从而帮助我们理解在耗时和资源占用上的一些取舍。在文章的最后我们将总结一个能让聚合操作更快、占用资源更少的实践经验清单。
探索聚合函数性能
我们之前的查询例子中GROUP BY之后只有几十个值，回想一下我们扫描之后存储结果的哈希表，意味着这样的哈希表里只有少量的键值。倘若我们使用同样的查询，想办法让GROUP BY之后产生更多的值，是不是键值数越多，在资源占用和聚合速度上就会有很大的差距呢？
这个很容易验证。首先，我们用不同的GROUP BY 跑另外一组测试。
SET max_untracked_memory = 1
-- #1
SELECT Carrier, avg(DepDelay) AS Delay
FROM ontime
GROUP BY Carrier
ORDER BY Delay DESC LIMIT 3
;
-- #2
SELECT Carrier, FlightDate, avg(DepDelay) AS Delay
FROM ontime
GROUP BY Carrier, FlightDate
ORDER BY Delay DESC LIMIT 314如果我们用system.query_log检查一下这个查询的速度和内存占用，就会得到如下结果：
┌──────────event_time─┬──secs─┬─memory────┬─threads─┬─query───────────────┐
│ 2022-03-15 11:09:34 │ 0.725 │ 33.96 MiB │ 4 │ -- #2SELECT Carrier │
│ 2022-03-15 11:09:33 │ 0.831 │ 10.46 MiB │ 4 │ -- #1SELECT Carrier │
└─────────────────────┴───────┴───────────┴─────────┴─────────────────────┘第二个查询的内存占用显著增长。至少在这个例子中，更多的键值意味着占用更多的内存。造成这种现象的键值数量的差距有多大呢？我们可以看看下面这个查询，uniqExact()函数可以用来统计值出现的次数。
SELECT
uniqExact(Carrier),
uniqExact(Carrier, FlightDate)
FROM ontime
FORMAT Vertical
Row 1:
──────
uniqExact(Carrier): 35
uniqExact(Carrier, FlightDate): 169368第一个查询GROUP BY的键值有35个，第二个则有169368个。
在这个例子中，我们还应该注意到第二个查询比第一个还快了15%。这看起来比较奇怪，因为通常我们会认为GROUP BY的键值越多需要做更多的工作。但是正如我们之前解释过的，Clickhouse会根据GROUP BY的键值数量和类型的不同选取不同的聚合方法，以及不同的哈希表配置。它们在性能上大不相同。
那我们怎么看出来的呢？
我们可以通过clickhouse-client --send_log_message='trace'选项来打开trace日志的开关，然后仔细的查看日志。它会显示扫描线程结束的时间以及使用的聚合方法。下面是我们从第二个查询的日志中挑出来的一些典型信息。
[chi-clickhouse101-clickhouse101-0-0-0] 2022.03.28 14:23:15.311284 [ 1887 ] {92d8506c-ae7c-4d01-84b6-3c927ed1d6a5} <Trace> Aggregator: Aggregation method: keys32
…
[chi-clickhouse101-clickhouse101-0-0-0] 2022.03.28 14:23:16.007341 [ 1887 ] {92d8506c-ae7c-4d01-84b6-3c927ed1d6a5} <Debug> AggregatingTransform: Aggregated. 48020348 to 42056 rows (from 366.37 MiB) in 0.698374248 sec. (68760192.887 rows/sec., 524.61 MiB/sec.)第二个例子的扫描线程运行的更快即有可能说明选取的聚合方法或者哈希表的实现方式更优。如果我们想从代码层面了解原因的话，可以去看源码src/Interpreters/Aggregator.h。这里面我们会学到keys32和其他一些聚合方法。这一部分内容我们会另写博客进行深入介绍。
不同聚合函数的影响
avg()聚合函数相对来说比较简单，只用了很少的内存存储每一个子聚合产生的若干个整数。但如果我们使用的是稍微复杂的聚合函数呢？
uniqExact()函数就是一个很好的例子。该函数需要存储一张哈希表，包含着每个block里的遇到的值，然后归并得到最终的结果。 我们可以假设这个哈希表占用更多的内存，运行的也更慢一些。我们实际试一下吧！我们运行如下的查询，分别使用较少和较多的GROUP BY键值。
SET max_untracked_memory = 1
SELECT Carrier, avg(DepDelay) AS Delay, uniqExact(TailNum) AS Aircraft
FROM ontime
GROUP BY Carrier ORDER BY Delay DESC
LIMIT 3
-- #2
SELECT Carrier, FlightDate, avg(DepDelay) AS Delay,
uniqExact(TailNum) AS Aircraft
FROM ontime
GROUP BY Carrier, FlightDate ORDER BY Delay DESC
LIMIT 3然后还是通过system.query_log来查看查询性能。
┌──────────event_time─┬──secs─┬─memory────┬─threads─┬─query────────────────┐
│ 2022-03-15 12:19:08 │ 3.324 │ 2.41 GiB │ 4 │ -- #2SELECT Carrier │
│ 2022-03-15 12:19:04 │ 2.657 │ 21.57 MiB │ 4 │ -- #1SELECT Carrier │添加了该聚合函数以后执行时间明显变长了，但是这并不奇怪。有意思的现象是内存的增长，对于键值比较少的情况，内存由之前的10.46Mib增加到21.57Mib，看起来并不多。然而，随着GROUP BY键值的增加，内存占用爆炸式增长。如果你仔细想想这个查询的话你就能够明白，这个查询会请求每个分组里的所有航班号码。把航班日期FlightDate加到GROUP BY里以后意味着每一个航班都要计算多次，而它们都需要在哈希表里保存下来指导Clickhouse做最后的聚合操作得到最终结果。
因为我们看到了爆炸式的增长，我们再来看一个查询。groupArray函数是一个强大的聚合去重函数，能够将某一列的值聚合后形成一个数组。我们假设它比uniqExact函数使用更多的内存，因为它需要保存所有的值而不仅仅是计数。下面是这个查询的统计结果：
SELECT Carrier, FlightDate, avg(DepDelay) AS Delay,
length(groupArray(TailNum)) AS TailArrayLen
FROM ontime
GROUP BY Carrier, FlightDate ORDER BY Delay DESC
LIMIT 3
┌──────────event_time─┬──secs─┬─memory───┬─threads─┬─query───────────────┐
│ 2022-03-15 21:28:51 │ 2.957 │ 5.66 GiB │ 4 │ -- #3SELECT Carrier │
└─────────────────────┴───────┴──────────┴─────────┴─────────────────────┘正如我们预期的一样，它比uniqExact函数消耗了更多的内存：5.66Gib。Clickhouse默认会终止内存占用超过10GiB的查询，因此这已经很接近这一临界值了。然而，即使这样，所以的这些查询在我们扫描差不多2亿条数据的情况下依然很快。正如之前所说，由于clickhouse针对不同的场景使用了不同的聚合方法和哈希实现，因此性能上会有区别。
执行线程数的影响
我们最后来看看线程数对性能的影响吧。我们可以通过控制max_threads参数来控制扫描线程的数量。我们用下面同一个查询执行三次：
SET max_untracked_memory = 1
SET max_threads = 1
SELECT Origin, FlightDate, avg(DepDelay) AS Delay,
uniqExact(TailNum) AS Aircraft
FROM ontime
WHERE Carrier='WN'
GROUP BY Origin, FlightDate ORDER BY Delay DESC
LIMIT 3
SET max_threads = 2
(same query)
SET max_threads = 4
(same query)OK，让我们看看内存使用情况，为了看起来更容易区分我加了一些注释。
┌──────────event_time─┬──secs─┬─memory───┬─threads─┬─query───────────────┐
│ 2022-03-15 22:12:02 │ 0.86 │ 1.71 GiB │ 8 │ -- #8SELECT Origin, │
│ 2022-03-15 22:12:01 │ 1.285 │ 1.72 GiB │ 4 │ -- #4SELECT Origin, │
│ 2022-03-15 22:11:59 │ 2.325 │ 1.75 GiB │ 2 │ -- #2SELECT Origin, │
│ 2022-03-15 22:11:56 │ 4.408 │ 1.91 GiB │ 1 │ -- #1SELECT Origin, │
. . .我们把结果画一张图更容易理解了。
增加线程数会显著提升查询速度，直到到达一个临界点，本例中是4个线程。到达该临界点以后，随着线程数的增加，查询性能不再线性增长。有意思的是，Clickhouse会在扫描结束之后并行进行合并操作。如果仔细查看debug日志的话，会在其中看到这部分查询执行更快了。
添加更多的线程会影响到内存使用，但是也许与你想的不太一样。在此例中我们看到随着线程数的增加，内存占用反而略有下降。
提升聚合性能的实用技巧
我们已经详细探究了聚合函数的响应时间和内存使用情况，这两个是clickhouse在生产环境使用时主要考虑的因素。在最后一部分我们会总结一下提升这两方面性能使用技巧。
让聚合更快
有很多方式可以让聚合查询变的更快，下面是一些主要的建议。
1. 去掉或替换复杂的聚合函数
还在用uniqExact来计算出现次数？试着用uniq替代吧，它通过牺牲一下结果的精度换来更快的速度。对于比较耗时的函数，clickhouse都提供了专门的替代函数。如果你正在基于网站访问用户数来计算转化率，那么可以试试窗口函数。
2. 减少GROUP BY的值
处理大量的GROUP BY键值会增加聚合操作的时间，尽可能的减少Group by的项。
3. 通过max_threads增加并行度
增加max_threads的值可以带来接近线性的性能提升，直到到达某一个值以后不再增长。超过了这个点以后再增加线程数量就有点儿得不偿失了。
4. 通过推迟join减少初始扫描的数据
如果你正在join数据，那么最好先等聚合完了再join。下面就是一个例子，Clickhouse先进行了子查询，然后用airports表来做join操作。
SELECT Dest, airports.Name Name, c Flights, ad Delay
FROM
(
SELECT Dest, count(*) c, avg(ArrDelayMinutes) ad
FROM ontime
GROUP BY Dest HAVING c > 100000
ORDER BY ad DESC LIMIT 10
) a
LEFT JOIN airports ON IATA = Dest如果我们直接join的话，Clickhouse会在主进程中join数据，会需要额外的CPU操作。相反，如果我们等子查询结束以后再操作，则只需要join十行数据。这极大的减少了需要的处理时间。
这种方法同样可以节省内存但是效果不是很明显，除非你在结果里加了很多列。这个例子给每一个GROUP BY的键值增加了一个值，相对整个大的计划而言就比较少。
5. 只保留一些必要的数据
从前面的分析来看，任何查询只要处理的数据更少，速度必然会更快。因此把不必要的行和列都去掉。
6. 使用物化视图
通过物化视图对数据进行预聚合是通用的做法。在最佳场景下这种方式可以节省1000倍甚至更多的响应时间，只是通过简单的减少你每次需要扫描的数据。
让聚合更省内存
1. 还是使用物化视图
2. 替换掉或去掉耗内存多的函数
3. 减少GROUP BY的键值数量
4. 放弃吧（没有办法的办法）
有些查询本身就会消耗很多内存。增加阈值限制可以让查询正常运行。clickhouse通过下面这三个参数控制内存使用，你可以在服务端或者用户配置里修改这些参数。
- max_memory_usage 单个查询可以使用的最大内存，单位字节。默认10GiB，大部分时候是够用了。
- max_memory_usage_for_user 一个用户在一个时刻对所有查询可以使用的最大内存，默认是无限的。
- max_server_memory_usage 整个clickhouse server使用的最大内存，默认是可用内存的90%。
如果这些还不奏效，那么你可以试试把部分汇聚的结果写到磁盘上。通过设置参数max_bytes_before_external_group_by。一般的推荐是max_memory_usage参数的50%。这可以确保你不会在merge阶段用尽内存。
不要想当然
最好都测试一下！聚合操作看似复杂，但是你是可以弄明白的。通过简单的例子，大量的数据集来进行测试。根据我们的经验，通常是可以做到加速查询的，而且是显著的。大胆假设，小心求证。你也许会碰到一些意想之外的事情，但通常是好的一方面。
小结
聚合操作是数据库中的一个基本操作，用于从大量的数据中提取价值。Clickhouse聚合函数就像一台高性能的跑车，很快，但你需要训练之后才可以用它赢得比赛。用对了的话你也许可以在不到1秒的时间就得到你想要的的结果，用错了的话你的查询就会特别慢而且还占内存。
