时间序列查询性能
在优化存储后,下一步是提高查询性能。
本节探讨了两种关键技术:优化 ORDER BY
键和使用物化视图。
我们将看到这些方法如何将查询时间从秒减少到毫秒。
优化 ORDER BY 键
在尝试其他优化之前,您应该优化排序键,以确保 ClickHouse 生成尽可能快速的结果。
正确选择键很大程度上取决于您要运行的查询。假设我们的查询大多按 project
和 subproject
列进行过滤。
在这种情况下,将它们添加到排序键中是个好主意——同时也要包含时间列,因为我们也按时间查询:
让我们创建一个版本的表,该表的列类型与 wikistat
相同,但按 (project, subproject, time)
排序。
现在让我们比较多个查询,以了解排序键表达式对性能的影响。请注意,我们尚未应用之前的数据类型和编解码优化,因此查询性能的差异仅基于排序顺序。
查询 | (time) | (project, subproject, time) |
---|---|---|
2.381 秒 | 1.660 秒 | |
2.148 秒 | 0.058 秒 | |
2.192 秒 | 0.012 秒 | |
2.968 秒 | 0.010 秒 |
物化视图
另一个选项是使用物化视图来聚合和存储流行查询的结果。可以查询这些结果,而不是原始表。假设在我们的情况下,以下查询经常执行:
创建物化视图
我们可以创建以下物化视图:
回填目标表
此目标表只会在向 wikistat
表插入新记录时填充,因此我们需要进行一些 回填。
最简单的方法是使用 INSERT INTO SELECT
语句直接插入到物化视图的目标表 使用 视图的 SELECT 查询(转化):
根据原始数据集的基数(我们有 10 亿行!),这可能是一个内存密集型的方法。或者,您可以使用一个需要最少内存的变体:
- 创建一个 Null 表引擎的临时表
- 将通常使用的物化视图的副本连接到该临时表
- 使用 INSERT INTO SELECT 查询,将原始数据集的所有数据复制到该临时表
- 删除临时表和临时物化视图。
通过这种方法,原始数据集的行按块复制到临时表(该表不存储任何这些行),并为每个块的行计算部分状态并写入目标表,这些状态会在后台逐步合并。
接下来,我们将创建一个物化视图,从 wikistat_backfill
中读取并写入 wikistat_top
最后,我们将从初始的 wikistat
表填充 wikistat_backfill
:
一旦该查询完成,我们可以删除回填表和物化视图:
现在我们可以查询物化视图,而不是原始表:
我们的性能提升十分显著。 之前计算此查询的答案需要超过 2 秒,而现在只需 4 毫秒。