一个简单的查询优化指南

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

理解查询性能

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

但是我们要诚实地说，预测你的数据会增长多少或将执行哪些类型的查询是很困难的。

如果你有一个现有的部署，想要改善几个查询，第一步是理解这些查询的性能以及为什么有些查询执行只需几毫秒而其他的则需要更长时间。

ClickHouse 拥有丰富的工具来帮助你理解查询是如何执行的，以及执行过程中消耗了多少资源。

在本节中，我们将探讨这些工具及其用法。

一般考量

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

以下部分故意简化并采取了一些捷径；这里的目的是不让你淹没在细节中，而是让你迅速掌握基本概念。有关更多信息，你可以阅读 查询分析器。

从一个很高的层面来看，当 ClickHouse 执行查询时，会发生以下几个步骤：

• 查询解析与分析

查询被解析和分析，生成一个通用的查询执行计划。

• 查询优化

查询执行计划被优化，不必要的数据被修剪，并从查询计划中构建查询管道。

• 查询管道执行

数据被并行读取和处理。这是 ClickHouse 实际执行查询操作（如过滤、聚合和排序）的阶段。

• 最终处理

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

实际上，许多 优化 正在进行中，我们将在本指南中进一步讨论它们，但现在，这些主要概念让我们对 ClickHouse 执行查询时幕后发生的事情有了很好的理解。

通过这样的高层理解，让我们来审视 ClickHouse 提供的工具，以及如何使用这些工具跟踪影响查询性能的指标。

数据集

我们将使用一个实际示例来说明我们如何处理查询性能。

我们使用 NYC 出租车数据集，该数据集包含纽约市的出租车行程数据。首先，我们开始导入 NYC 出租车数据集，而不进行任何优化。

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

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

找出慢查询

查询日志

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

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

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

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

字段 query_duration_ms 指示特定查询执行所花费的时间。从查询日志的结果中，我们可以看到第一个查询花费了 2967 毫秒来运行，这可以得到改善。

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

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

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

总结成表格以便于阅读。

名称	消耗时间	处理行数	峰值内存
查询 1	1.699 sec	329.04 million	440.24 MiB
查询 2	1.419 sec	329.04 million	546.75 MiB
查询 3	1.414 sec	329.04 million	451.53 MiB

让我们更好地理解一下这些查询的作用。

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

这些查询都没有进行非常复杂的处理，除了第一个查询在每次查询执行时都要动态计算行程时间。然而，以上这些查询都需要超过一秒的时间执行，这在 ClickHouse 的世界中是非常长的。我们还注意到这些查询的内存使用情况；每个查询约 400 MB 的内存相当可观。此外，每个查询似乎读取的行数都是相同的（即 329.04 million）。我们快速确认一下这个表中有多少行。

该表包含 329.04 million 行，因此每个查询都在对表进行全面扫描。

解释语句

现在我们有了一些长时间运行的查询，让我们了解它们是如何执行的。为此，ClickHouse 支持 EXPLAIN语句命令。这是一个非常有用的工具，提供了查询执行的所有阶段的详细视图，而不实际运行查询。虽然对于非 ClickHouse 专家来说，这可能看起来很复杂，但它依然是深入了解查询如何执行的重要工具。

文档提供了详细的 指南，介绍了 EXPLAIN 语句是什么，以及如何使用它来分析你的查询执行。我们不想重复这份指南的内容，而是将重点放在一些可以帮助我们找到查询执行性能瓶颈的命令上。

解释索引 = 1

让我们以 EXPLAIN indexes = 1 开始，检查查询计划。查询计划是一个树状图，展示了查询将如何执行。在这里，你可以看到查询中的子句将以何种顺序执行。由 EXPLAIN 语句返回的查询计划可以从下到上进行阅读。

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

输出内容很直观。查询首先从 nyc_taxi.trips_small_inferred 表中读取数据。然后，应用 WHERE 子句以基于计算值过滤行。过滤后的数据准备进行聚合，计算分位数。最后，结果被排序并输出。

在这里，我们可以注意到没有使用主键，这也是可以理解的，因为在创建表时我们并未定义任何主键。因此，ClickHouse 在执行查询时正在对表进行全面扫描。

解释管道

EXPLAIN PIPELINE 显示查询的具体执行策略。在这里，你可以看到 ClickHouse 实际执行我们之前查看的通用查询计划的方法。

在这里，我们注意到执行查询所使用的线程数量为 59，表明高度并行化。这加快了查询的速度，而在较小的机器上则需要更长的执行时间。并行运行的线程数量可以解释查询使用的高内存量。

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

方法论

在生产部署中识别问题查询可能很困难，因为在你的 ClickHouse 部署中，随时可能会执行大量查询。

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

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

查询优化需要结构。我不是在谈论高级基准测试，而是说建立一个简单的过程来理解你的更改如何影响查询性能将发挥很大作用。

首先通过查询日志识别慢查询，然后在隔离中调查潜在的改进。在测试查询时，确保禁用文件系统缓存。

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

一旦你识别出潜在的优化，建议逐一实施它们，以便更好地跟踪它们对性能的影响。下面是描述一般方法的图示。

最后，小心异常值；查询缓慢的情况非常普遍，可能是因为用户尝试了临时的昂贵查询或系统因其他原因受到压力。你可以根据字段 normalized_query_hash 分组，以识别定期执行的昂贵查询。这些查询可能是你想调查的对象。

基本优化

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

开始的最佳地方是查看数据如何存储。对于任何数据库，读取的数据越少，查询的执行就越快。

根据你如何导入数据，你可能利用了 ClickHouse 功能 从导入数据中推断表模式。虽然这对于入门非常实用，但如果你想优化查询性能，你需要审查数据模式以最好地适应你的用例。

Nullable

如 最佳实践文档 所述，尽可能避免 nullable 列。对它们的使用虽然诱人，因为它们使数据导入机制更加灵活，但它们会在每次处理时负面影响性能。

运行一个 SQL 查询以计算 NULL 值的行，可以轻松揭示出表中哪些列实际上需要 nullable 值。

我们只有两列存在 null 值：mta_tax 和 payment_type。其他字段应该不使用 Nullable 列。

低基数

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

确定哪些列适合低基数的简单规则是，任何唯一值少于 10,000 的列都是完美的候选。

您可以使用以下 SQL 查询查找唯一值较少的列。

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

优化数据类型

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

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

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

应用优化

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

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

名称	运行 1 - 消耗时间	消耗时间	处理行数	峰值内存
查询 1	1.699 sec	1.353 sec	329.04 million	337.12 MiB
查询 2	1.419 sec	1.171 sec	329.04 million	531.09 MiB
查询 3	1.414 sec	1.188 sec	329.04 million	265.05 MiB

我们注意到查询时间和内存使用量都有了一些改进。通过对数据模式的优化，我们减少了表示数据的总数据量，导致内存消耗得到改善，处理时间降低。

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

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

主键的重要性

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

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

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

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

ClickHouse 支持的其他选项，比如 Projections 或 Materialized views，允许你在相同的数据上使用不同的主键集。该系列博客的第二部分将对此进行更详细的探讨。

选择主键

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

目前，我们将遵循以下简单的实践：

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

在我们的案例中，我们将尝试以下主键： 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 MiB	337.12 MiB	444.19 MiB

查询 2
	运行 1	运行 2	运行 3
消耗时间	1.419 秒	1.171 秒	0.248 秒
处理行数	3.2904 亿	3.2904 亿	4146 万
峰值内存	546.75 MiB	531.09 MiB	173.50 MiB

查询 3
	运行 1	运行 2	运行 3
消耗时间	1.414 秒	1.188 秒	0.431 秒
处理行数	3.2904 亿	3.2904 亿	2.7699 亿
峰值内存	451.53 MiB	265.05 MiB	197.38 MiB

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

查询 2 从主键中获益最多。让我们看看生成的查询计划与之前有何不同。

感谢主键，只有一部分表粒度被选中。这本身大大提高了查询性能，因为 ClickHouse 处理的数据量显著减少。

下一步

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

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