跳到主要内容
跳到主要内容

查询优化的简单指南

本节旨在通过常见场景来说明如何使用不同的性能和优化技术，如 analyzer、查询分析 或 避免使用 Nullable 列，来提高 ClickHouse 查询的性能。

了解查询性能​

考虑性能优化的最佳时机是当你在将数据首次导入 ClickHouse 之前设置 数据模式 的时候。

但说实话；很难预测你的数据将增长多少或将执行什么类型的查询。

如果你有一个现有的部署，想要改进其中的一些查询，第一步是理解这些查询的执行情况，以及为什么某些查询在几毫秒内执行而其他查询则需要更长时间。

ClickHouse 提供了一整套工具来帮助你理解查询是如何被执行的，以及执行过程中消耗了哪些资源。

在本节中，我们将探讨这些工具以及如何使用它们。

一般考虑事项​

要理解查询性能，我们需要了解 ClickHouse 在执行查询时发生了什么。

以下部分是经过简化的，采取了一些捷径；这里的目的是不是让你淹没于细节中，而是让你迅速了解基本概念。如需更多信息，可以阅读 查询分析器。

从一个非常高层的角度来看，当 ClickHouse 执行查询时，会发生以下事情：

  • 查询解析与分析

查询被解析和分析，并创建一个通用的查询执行计划。

  • 查询优化

查询执行计划被优化，不需要的数据被剪枝，并从查询计划构建出一个查询管道。

  • 查询管道执行

数据被并行读取和处理。在此阶段，ClickHouse 实际上执行查询操作，如过滤、聚合和排序。

  • 最终处理

结果被合并、排序，并格式化为最终结果，然后发送给客户端。

实际上，许多 优化 正在进行中，我们将在本指南中详细讨论，但目前，这些主要概念给我们提供了一个良好的理解，了解在 ClickHouse 执行查询时发生了什么。

通过这种高层次的理解，让我们审视 ClickHouse 提供的工具及其如何用于追踪影响查询性能的指标。

数据集​

我们将使用一个真实的例子来说明我们如何接近查询性能。

让我们使用 NYC Taxi 数据集，其中包含纽约市的出租车行程数据。首先，我们从未优化的状态开始导入 NYC 出租车数据集。

以下是创建表和从 S3 存储桶插入数据的命令。请注意，我们自愿从数据中推断模式，这并没有经过优化。

让我们看看从数据中自动推断出的表模式。

找出慢查询​

查询日志​

默认情况下，ClickHouse 会收集和记录关于每个执行查询的信息，在 查询日志 中。这些数据存储在表 system.query_log 中。

对于每个执行的查询，ClickHouse 会记录统计信息，如查询执行时间、读取的行数，以及资源使用情况（如 CPU、内存使用或文件系统缓存命中）。

因此，查询日志是调查慢查询的好地方。你可以轻松找到执行时间较长的查询，并显示每个查询的资源使用信息。

让我们找出我们 NYC 出租车数据集中前五个运行时间较长的查询。

字段 query_duration_ms 指示该特定查询的执行时间。根据来自查询日志的结果，我们可以看到第一个查询的运行时间为 2967ms，这可以得到改进。

你可能还想知道哪些查询给系统带来了压力，可以通过检查消耗最多内存或 CPU 的查询来了解。

让我们隔离这些长时间运行的查询，重新运行几次以了解响应时间。

此时，关闭文件系统缓存是至关重要的，办法是将 enable_filesystem_cache 设置为 0，以提高可重复性。

将其汇总在一个表中，以便于阅读。

名称耗时处理的行数峰值内存
查询 11.699 秒3.2904 亿440.24 MiB
查询 21.419 秒3.2904 亿546.75 MiB
查询 31.414 秒3.2904 亿451.53 MiB

让我们更好地理解查询所实现的效果。

  • 查询 1 计算平均时速超过 30 英里每小时的行程的距离分布。
  • 查询 2 查找每周的行程数量和平均费用。
  • 查询 3 计算数据集中每次行程的平均时间。

这些查询都没有进行复杂的处理，除了第一个查询每次执行时动态计算行程时间。但是，这些查询的执行时间超过了一秒，在 ClickHouse 的世界里，这是一个非常长的时间。我们还可以注意到这些查询的内存使用；每个查询差不多 400 MB 的内存相当可观。此外，每个查询读取的行数似乎都是相同的（即 3.2904 亿）。让我们迅速确认一下这个表有多少行。

该表包含 3.2904 亿行，因此每个查询都是对表的全表扫描。

解释语句​

现在我们有了一些长时间运行的查询，让我们理解它们是如何执行的。为此，ClickHouse 支持 EXPLAIN 语句命令。它是一个非常有用的工具，提供查询执行阶段的详细视图，而不实际运行查询。尽管对非 ClickHouse 专家而言，查看输出可能会感到不知所措，但它仍然是深入了解查询如何执行的重要工具。

文档提供了关于 EXPLAIN 语句是什么以及如何使用它分析查询执行的详细 指南。我们不重复该指南中的内容，而是专注于几个命令，帮助我们找到查询执行性能瓶颈。

Explain indexes = 1

我们用 EXPLAIN indexes = 1 来检查查询计划。查询计划是一个树状结构，显示查询将如何执行。在那里，你可以看到查询中的子句将以什么顺序执行。EXPLAIN 语句返回的查询计划可以从下到上进行阅读。

我们尝试使用我们第一个长时间运行的查询。

输出是直接的。查询开始时将数据从 nyc_taxi.trips_small_inferred 表中读取。然后，应用 WHERE 子句来根据计算的值过滤行。过滤后的数据准备进行聚合，并计算分位数。最后，结果被排序并输出。

在这里，我们可以注意到没有使用主键，这很合理，因为我们在创建该表时没有定义任何主键。因此，ClickHouse 在执行查询时对整张表进行了全表扫描。

Explain Pipeline

EXPLAIN Pipeline 展示了查询的具体执行策略。在这里，你可以看到 ClickHouse 实际上是如何执行我们之前查看的通用查询计划的。

在这里，我们可以注意到用于执行查询的线程数量：59 个线程，表示出色的并行化。这加快了查询，需要在较小机器上执行的时间会更长。并行运行的线程数量可以解释查询使用的高内存容量。

理想情况下，你应该以相同的方式调查所有慢查询，以识别不必要的复杂查询计划，并了解每个查询读取的行数以及消耗的资源。

方法论​

在生产环境中识别问题查询可能很困难，因为在你的 ClickHouse 部署上，可能会有大量查询在任何给定时刻被执行。

如果你知道哪个用户、数据库或表存在问题，可以使用 system.query_logs 中的 user、tables 或 databases 字段来缩小搜索范围。

一旦识别出想要优化的查询，你可以着手进行优化。在这个阶段，开发者常犯的一个错误是同时更改多个内容，进行临时实验，最终通常会得出混合结果，但更重要的是缺乏对使查询更快的因素的良好理解。

查询优化需要结构。我不是在谈论高级基准测试，而是在设置一个简单的流程，以了解你的更改如何影响查询性能，这可以带来很大帮助。

首先从查询日志中识别出你的慢查询，然后在隔离状态下调查潜在的改善。当测试查询时，确保禁用文件系统缓存。

ClickHouse 利用 缓存 在不同阶段加速查询性能。这对于查询性能有好处，但在故障排除期间，可能会隐藏潜在的 I/O 瓶颈或不良的表模式。因此，我建议在测试期间关闭文件系统缓存。在生产环境中确保开启。

一旦识别出潜在的优化，建议逐个实施这些优化，以更好地跟踪它们如何影响性能。下面是描述一般方法的图表。

最后，注意异常值；查询可能会变慢，这是比较常见的，可能是由于用户尝试了临时的开销查询或者系统因其他原因面临压力。你可以通过字段 normalized_query_hash 分组，识别定期执行的开销查询。这些查询可能就是你想要调查的。

基本优化​

现在我们有了测试的框架，可以开始优化。

开始时最好的地方是看看数据是如何存储的。就像任何数据库一样，我们读取的数据越少，查询执行的速度越快。

根据你摄取数据的方式，你可能利用了 ClickHouse 能力 根据摄取的数据推断表模式。虽然这在开始时非常方便，但如果你想优化查询性能，你需要审查数据模式，以最适合你的用例。

Nullable​

如 最佳实践文档 所述，尽可能避免使用 nullable 列。虽然经常使用 nullable 列会使数据摄取机制更灵活，但它们会对性能产生负面影响，因为每次都必须处理额外的列。

运行一个计算 NULL 值行数的 SQL 查询，可以很容易揭示出你表中实际需要 使用 Nullable 值的列。

我们只有两个列有 NULL 值：mta_tax 和 payment_type。其余字段不应该使用 Nullable 列。

低基数​

对字符串应用的一个简单优化是充分利用 LowCardinality 数据类型。如 低基数文档 中所述，ClickHouse 对 LowCardinality 列应用字典编码，这显著提高了查询性能。

确定哪些列是 LowCardinality 的良好候选列的一个简单经验法则是，任何唯一值少于 10,000 的列都是完美候选列。

你可以使用以下 SQL 查询查找具有低唯一值数量的列。

对于低基数，这四列 ratecode_id、pickup_location_id、dropoff_location_id 和 vendor_id 是 LowCardinality 字段类型的良好候选列。

优化数据类型​

Clickhouse 支持大量数据类型。确保选择符合你的用例的最小数据类型，以优化性能并减少磁盘上的数据存储空间。

对于数字，你可以检查数据集中最小/最大值，以检查当前精度值是否与数据集的实际情况匹配。

对于日期，你应选择与数据集相匹配、最适合于回答你打算运行的查询的精度。

应用优化​

让我们创建一个新表以使用优化过的模式，并重新导入数据。

我们使用新表再次运行查询以检查改进情况。

名称运行 1 - 耗时耗时处理的行数峰值内存
查询 11.699 秒1.353 秒3.2904 亿337.12 MiB
查询 21.419 秒1.171 秒3.2904 亿531.09 MiB
查询 31.414 秒1.188 秒3.2904 亿265.05 MiB

我们注意到查询时间和内存使用方面都有一些改善。由于数据模式的优化，我们减少了所代表数据的总数据量，导致内存消耗降低和处理时间缩短。

让我们检查表的大小以查看差异。

新表比之前的表小得多。我们看到该表的磁盘空间减少了约 34%（7.38 GiB 对比 4.89 GiB）。

主键的重要性​

ClickHouse 中的主键与大多数传统数据库系统中的工作方式有所不同。在这些系统中，主键强制执行唯一性和数据完整性。任何试图插入重复主键值的尝试都会被拒绝，并且通常会为快速查找创建 B 树或基于哈希的索引。

在 ClickHouse 中，主键的 目标 是不同的；它不强制执行唯一性或帮助确保数据完整性。相反，它旨在优化查询性能。主键定义了数据在磁盘上的存储顺序，并作为稀疏索引实现，存储指向每个 granule 第一行的指针。

在 ClickHouse 中，granules 是在查询执行期间读取的最小数据单位。它们包含最多固定数量的行，由 index_granularity 决定，默认值为 8192 行。granules 是连续存储并按主键排序的。

选择一组好的主键对于性能很重要，实际上，通常会在不同的表中存储相同的数据，并使用不同的主键集合来加速特定的查询集合。

ClickHouse 支持的其他选项，如投影或物化视图，允许你对相同数据使用不同的主键集。该博客系列的第二部分将对此进行更深入的讲解。

选择主键​

选择正确的主键集合是一个复杂的话题，可能需要权衡和实验以找到最佳组合。

目前，我们将遵循以下简单做法：

  • 使用在大多数查询中用于过滤的字段
  • 首先选择基数较低的列
  • 在你的主键中考虑基于时间的组件，因为在时间戳数据集上按时间过滤是很常见的。

在我们的案例中，我们将尝试以下主键：passenger_count、pickup_datetime 和 dropoff_datetime。

passenger_count 的基数很小（24 个唯一值），并且使用于我们的慢查询中。我们还添加了时间戳字段（pickup_datetime 和 dropoff_datetime），因为它们经常被过滤。

创建一个带有主键的新表并重新导入数据。

然后我们重新运行我们的查询。我们汇总了三次实验的结果，以查看在耗时、处理的行数和内存消耗方面的改进。

查询 1
运行 1运行 2运行 3
耗时1.699 秒1.353 秒0.765 秒
处理的行数3.2904 亿3.2904 亿3.2904 亿
峰值内存440.24 MiB337.12 MiB444.19 MiB
查询 2
运行 1运行 2运行 3
耗时1.419 秒1.171 秒0.248 秒
处理的行数3.2904 亿3.2904 亿41.46 百万
峰值内存546.75 MiB531.09 MiB173.50 MiB
查询 3
运行 1运行 2运行 3
耗时1.414 秒1.188 秒0.431 秒
处理的行数3.2904 亿3.2904 亿276.99 百万
峰值内存451.53 MiB265.05 MiB197.38 MiB

我们可以看到在执行时间和内存使用方面都有显著的改善。

查询 2 从主键中受益最大。让我们来看一下生成的查询计划与之前的不同之处。

由于主键，只选择了表 granules 的一个子集。这大大提高了查询性能，因为 ClickHouse 需要处理的数据量显著减少。

后续步骤​

希望本指南能让你很好地理解如何使用 ClickHouse 调查慢查询以及如何使它们更快。要进一步探索这个主题，你可以阅读更多关于 查询分析器 和 分析 的内容，以更好地理解 ClickHouse 是如何执行查询的。

随着你对 ClickHouse 特性的熟悉，我建议你阅读有关 分区键 和 数据跳过索引 的内容，以了解更多可以加速查询的高级技术。