博客 / 工程

ClickHouse 查询优化简易指南：第一部分

Lionel Palacin

2024 年 12 月 11 日 - 24 分钟阅读

几个月前，我开始在 ClickHouse 的产品营销工程团队工作。我之前在 Elastic 工作，专注于搜索解决方案，因此有很多关于 ClickHouse 和 OLAP 数据库的知识需要学习。

我在 ClickHouse 的首批项目之一是帮助推出新的 ClickHouse Playground。Playground 包含许多不同的数据集，这对于我学习 ClickHouse 来说是一个完美的地方。

当我们发布 ClickHouse Playground 时，我很好奇用户体验，以及我们提供的一些示例查询是否可以改进。得益于广泛可用的学习资料，例如按需培训、视频、文档和博客，我学到了很多关于优化 ClickHouse 查询的知识。这是分为两部分的系列博客中的第一篇，我将在其中分享一些优化技巧。

在第一部分中，我们将介绍 ClickHouse 支持的用于调查慢查询的工具。然后，我们将讨论基本优化和主键的重要性。在下一部分中，我们将介绍更高级的查询优化技术，例如投影、物化视图和数据跳过索引。

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

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

因此，如果您尚未开始您的 ClickHouse 之旅，也许您想跳过这部分，直接进入下一节，了解基本优化和主键的重要性。

但是，如果您已经部署了 ClickHouse，并且有一些想要改进的查询，那么第一步是了解这些查询的执行情况以及为什么有些查询在几毫秒内执行，而另一些查询则需要更长的时间。

ClickHouse 拥有一套丰富的工具，可帮助您了解查询的执行方式以及执行所消耗的资源。

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

为了了解查询性能，让我们看看在 ClickHouse 中执行查询时会发生什么。

以下部分经过刻意简化并采用了一些快捷方式；这里的想法不是用细节淹没您，而是让您快速掌握基本概念。有关更多信息，您可以查阅查询分析器文档。

从非常高的层面来看，当 ClickHouse 执行查询时，会发生以下情况：

查询解析和分析

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

查询优化

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

查询管道执行

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

最终处理

在将结果发送到客户端之前，将结果合并、排序并格式化为最终结果。

实际上，正在进行许多优化，我们将在本指南中进行更多讨论，但就目前而言，这些主要概念让我们很好地了解了 ClickHouse 执行查询时幕后发生的事情。

有了这种高层次的理解，让我们检查一下 ClickHouse 提供的工具，以及我们如何使用它来跟踪影响查询性能的指标。

正如引言中所讨论的，我们最近部署了 ClickHouse Playground 演示环境，供任何人使用 ClickHouse 玩转不同的数据集。此环境在 ClickHouse Cloud 上运行，并被数百名用户使用。

我将使用此环境来说明我优化查询性能的方法。

ClickHouse Playground 上可用的数据集之一是 NYC Taxi 数据集，其中包含纽约市的出租车行程数据。我们摄取了 NYC 出租车数据集，没有进行任何优化。

下面是创建表并从 S3 存储桶插入数据的命令。请注意，我们自愿从数据中推断模式。

  
1-- Create table with inferred schema
2CREATE TABLE trips_small_inferred
3ORDER BY () EMPTY
4AS SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet');
5
6-- Insert data into table with inferred schema
7INSERT INTO trips_small_inferred
8SELECT * 
9FROM s3Cluster
10('default','https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet');

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

  
1--- Display inferred table schema
2SHOW CREATE TABLE trips_small_inferred
3
4Query id: d97361fd-c050-478e-b831-369469f0784d
5
6CREATE TABLE nyc_taxi.trips_small_inferred
7(
8    `vendor_id` Nullable(String),
9    `pickup_datetime` Nullable(DateTime64(6, 'UTC')),
10    `dropoff_datetime` Nullable(DateTime64(6, 'UTC')),
11    `passenger_count` Nullable(Int64),
12    `trip_distance` Nullable(Float64),
13    `ratecode_id` Nullable(String),
14    `pickup_location_id` Nullable(String),
15    `dropoff_location_id` Nullable(String),
16    `payment_type` Nullable(Int64),
17    `fare_amount` Nullable(Float64),
18    `extra` Nullable(Float64),
19    `mta_tax` Nullable(Float64),
20    `tip_amount` Nullable(Float64),
21    `tolls_amount` Nullable(Float64),
22    `total_amount` Nullable(Float64)
23)
24ORDER BY tuple()

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

本节中介绍的查询已在 ClickHouse Cloud 上执行。FROM 部分使用 clusterAllReplicas(default, system.query_log)，因为 query_log 表分布在 ClickHouse Cloud 中的多个节点上。在本地运行，您可以将其替换为 FROM system.query_log。

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

因此，查询日志是调查慢查询的一个良好起点。您可以轻松发现执行时间长的查询，并显示每个查询的资源使用情况信息。

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

 
1-- Find top 5 long running queries from nyc_taxi database in the last 1 hour
2SELECT
3    type,
4    event_time,
5    query_duration_ms,
6    query,
7    read_rows,
8    tables
9FROM clusterAllReplicas(default, system.query_log)
10WHERE has(databases, 'nyc_taxi') AND (event_time >= (now() - toIntervalMinute(60))) AND type='QueryFinish'
11ORDER BY query_duration_ms DESC
12LIMIT 5
13FORMAT VERTICAL
14
15Query id: e3d48c9f-32bb-49a4-8303-080f59ed1835
16
17Row 1:
18──────
19type:              QueryFinish
20event_time:        2024-11-27 11:12:36
21query_duration_ms: 2967
22query:             WITH
23  dateDiff('s', pickup_datetime, dropoff_datetime) as trip_time,
24  trip_distance / trip_time * 3600 AS speed_mph
25SELECT
26  quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
27FROM
28  nyc_taxi.trips_small_inferred
29WHERE
30  speed_mph > 30
31FORMAT JSON
32read_rows:         329044175
33tables:            ['nyc_taxi.trips_small_inferred']
34
35Row 2:
36──────
37type:              QueryFinish
38event_time:        2024-11-27 11:11:33
39query_duration_ms: 2026
40query:             SELECT 
41    payment_type,
42    COUNT() AS trip_count,
43    formatReadableQuantity(SUM(trip_distance)) AS total_distance,
44    AVG(total_amount) AS total_amount_avg,
45    AVG(tip_amount) AS tip_amount_avg
46FROM 
47    nyc_taxi.trips_small_inferred
48WHERE 
49    pickup_datetime >= '2009-01-01' AND pickup_datetime < '2009-04-01'
50GROUP BY 
51    payment_type
52ORDER BY 
53    trip_count DESC;
54
55read_rows:         329044175
56tables:            ['nyc_taxi.trips_small_inferred']
57
58Row 3:
59──────
60type:              QueryFinish
61event_time:        2024-11-27 11:12:17
62query_duration_ms: 1860
63query:             SELECT
64  avg(dateDiff('s', pickup_datetime, dropoff_datetime))
65FROM nyc_taxi.trips_small_inferred
66WHERE passenger_count = 1 or passenger_count = 2
67FORMAT JSON
68read_rows:         329044175
69tables:            ['nyc_taxi.trips_small_inferred']
70
71Row 4:
72──────
73type:              QueryFinish
74event_time:        2024-11-27 11:12:31
75query_duration_ms: 690
76query:             SELECT avg(total_amount) FROM nyc_taxi.trips_small_inferred WHERE trip_distance > 5
77FORMAT JSON
78read_rows:         329044175
79tables:            ['nyc_taxi.trips_small_inferred']
80
81Row 5:
82──────
83type:              QueryFinish
84event_time:        2024-11-27 11:12:44
85query_duration_ms: 634
86query:             SELECT
87vendor_id,
88avg(total_amount),
89avg(trip_distance),
90FROM
91nyc_taxi.trips_small_inferred
92GROUP BY vendor_id
93ORDER BY 1 DESC
94FORMAT JSON
95read_rows:         329044175
96tables:            ['nyc_taxi.trips_small_inferred']

字段 query_duration_ms 指示特定查询的执行时间。查看查询日志的结果，我们可以看到第一个查询需要 2967 毫秒才能运行，这可以改进。

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

 
1-- Top queries by memory usage 
2SELECT
3    type,
4    event_time,
5    query_id,
6    formatReadableSize(memory_usage) AS memory,
7    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')] AS userCPU,
8    ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')] AS systemCPU,
9    (ProfileEvents['CachedReadBufferReadFromCacheMicroseconds']) / 1000000 AS FromCacheSeconds,
10    (ProfileEvents['CachedReadBufferReadFromSourceMicroseconds']) / 1000000 AS FromSourceSeconds,
11    normalized_query_hash
12FROM clusterAllReplicas(default, system.query_log)
13WHERE has(databases, 'nyc_taxi') AND (type='QueryFinish') AND ((event_time >= (now() - toIntervalDay(2))) AND (event_time <= now())) AND (user NOT ILIKE '%internal%')
14ORDER BY memory_usage DESC
15LIMIT 30

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

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

 
1-- Disable filesystem cache
2set enable_filesystem_cache = 0;
3
4-- Run query 1
5WITH
6  dateDiff('s', pickup_datetime, dropoff_datetime) as trip_time,
7  trip_distance / trip_time * 3600 AS speed_mph
8SELECT
9  quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
10FROM
11  nyc_taxi.trips_small_inferred
12WHERE
13  speed_mph > 30
14FORMAT JSON
15
16----
171 row in set. Elapsed: 1.699 sec. Processed 329.04 million rows, 8.88 GB (193.72 million rows/s., 5.23 GB/s.)
18Peak memory usage: 440.24 MiB.
19
20-- Run query 2
21SELECT 
22    payment_type,
23    COUNT() AS trip_count,
24    formatReadableQuantity(SUM(trip_distance)) AS total_distance,
25    AVG(total_amount) AS total_amount_avg,
26    AVG(tip_amount) AS tip_amount_avg
27FROM 
28    nyc_taxi.trips_small_inferred
29WHERE 
30    pickup_datetime >= '2009-01-01' AND pickup_datetime < '2009-04-01'
31GROUP BY 
32    payment_type
33ORDER BY 
34    trip_count DESC;
35
36--- 
374 rows in set. Elapsed: 1.419 sec. Processed 329.04 million rows, 5.72 GB (231.86 million rows/s., 4.03 GB/s.)
38Peak memory usage: 546.75 MiB.
39
40-- Run query 3
41SELECT
42  avg(dateDiff('s', pickup_datetime, dropoff_datetime))
43FROM nyc_taxi.trips_small_inferred
44WHERE passenger_count = 1 or passenger_count = 2
45FORMAT JSON
46
47---
481 row in set. Elapsed: 1.414 sec. Processed 329.04 million rows, 8.88 GB (232.63 million rows/s., 6.28 GB/s.)
49Peak memory usage: 451.53 MiB.

在表中总结以便于阅读。

名称	经过时间	处理的行数	峰值内存
查询 1	1.699 秒	3.2904 亿	440.24 MiB
查询 2	1.419 秒	3.2904 亿	546.75 MiB
查询 3	1.414 秒	3.2904 亿	451.53 MiB

让我们更好地了解查询实现了什么。

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

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

 
1-- Count number of rows in table 
2SELECT count()
3FROM nyc_taxi.trips_small_inferred
4
5Query id: 733372c5-deaf-4719-94e3-261540933b23
6
7   ┌───count()─┐
81. │ 329044175 │ -- 329.04 million
9   └───────────┘

该表包含 3.2904 亿行，因此每个查询都在对表进行完整扫描。

或者，ClickHouse Cloud 还提供了丰富的 UI，称为 Query insight，可通过各种可视化和表格显示查询日志。

现在我们有一些运行时间长的查询，让我们了解它们的执行方式。为此，ClickHouse 支持 EXPLAIN 语句命令。它是一个非常有用的工具，可以非常详细地查看所有查询执行阶段，而无需实际运行查询。虽然对于非 ClickHouse 专家来说，查看它可能会让人感到不知所措，但它仍然是深入了解查询执行方式的重要工具。

文档提供了关于 EXPLAIN 语句是什么以及如何使用它来分析查询执行的详细指南。与其重复本指南中的内容，不如让我们关注一些命令，这些命令将帮助我们找到查询执行性能的瓶颈。

EXPLAIN indexes = 1

让我们从 EXPLAIN indexes = 1 开始检查查询计划。查询计划是一棵树，显示了查询将如何执行。在那里，您可以看到查询中的子句将按什么顺序执行。EXPLAIN 语句返回的查询计划可以从下往上读取。

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

 
1EXPLAIN indexes = 1
2WITH
3    dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,
4    (trip_distance / trip_time) * 3600 AS speed_mph
5SELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
6FROM nyc_taxi.trips_small_inferred
7WHERE speed_mph > 30
8
9Query id: f35c412a-edda-4089-914b-fa1622d69868
10
11   ┌─explain─────────────────────────────────────────────┐
121. │ Expression ((Projection + Before ORDER BY))         │
132. │   Aggregating                                       │
143. │     Expression (Before GROUP BY)                    │
154. │       Filter (WHERE)                                │
165. │         ReadFromMergeTree (nyc_taxi.trips_small_inferred) │
17   └─────────────────────────────────────────────────────┘

输出很简单。查询首先从 nyc_taxi.trips_small_inferred 表中读取数据。然后，应用 WHERE 子句来根据计算值过滤行。过滤后的数据将准备用于聚合，并计算分位数。最后，对结果进行排序并输出。

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

EXPLAIN Pipeline

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

 
1EXPLAIN PIPELINE
2WITH
3    dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,
4    (trip_distance / trip_time) * 3600 AS speed_mph
5SELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
6FROM nyc_taxi.trips_small_inferred
7WHERE speed_mph > 30
8
9Query id: c7e11e7b-d970-4e35-936c-ecfc24e3b879
10
11    ┌─explain─────────────────────────────────────────────────────────────────────────────┐
12 1. │ (Expression)                                                                        │
13 2. │ ExpressionTransform × 59                                                            │
14 3. │   (Aggregating)                                                                     │
15 4. │   Resize 59 → 59                                                                    │
16 5. │     AggregatingTransform × 59                                                       │
17 6. │       StrictResize 59 → 59                                                          │
18 7. │         (Expression)                                                                │
19 8. │         ExpressionTransform × 59                                                    │
20 9. │           (Filter)                                                                  │
2110. │           FilterTransform × 59                                                      │
2211. │             (ReadFromMergeTree)                                                     │
2312. │             MergeTreeSelect(pool: PrefetchedReadPool, algorithm: Thread) × 59 0 → 1 │

在这里，我们可以注意到用于执行查询的线程数：59 个线程，这表明高度并行化。这加快了查询速度，在较小的机器上执行查询将花费更长的时间。并行运行的线程数可以解释查询使用的大量内存。

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

在生产部署中识别有问题的查询可能很困难，因为在任何给定时间，您的 ClickHouse 部署上都可能执行大量查询。

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

一旦确定了要优化的查询，就可以开始优化它们。开发人员在此阶段常犯的一个错误是同时更改多个内容、运行临时实验，并且通常最终得到的结果好坏参半，但更重要的是，没有很好地理解是什么使查询速度更快。

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

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

ClickHouse 利用缓存来加快不同阶段的查询性能。这对查询性能有利，但在故障排除期间，它可能会隐藏潜在的 I/O 瓶颈或糟糕的表模式。因此，我建议关闭文件系统缓存。

一旦确定了潜在的优化，建议您逐个实施它们，以便更好地跟踪它们如何影响性能。下面是一个描述一般方法的图表。

最后，请注意异常值；查询运行缓慢是很常见的，可能是因为用户尝试了临时的开销大的查询，或者系统因其他原因而承受压力。您可以按字段 normalized_query_hash 分组，以识别定期执行的开销大的查询。这些可能是您想要调查的查询。

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

最好的起点是查看数据的存储方式。与任何数据库一样，我们读取的数据越少，查询执行速度就越快。

根据您摄取数据的方式，您可能已经利用 ClickHouse 功能根据摄取的数据推断表模式。虽然这对于入门非常实用，但如果您想优化查询性能，则需要查看数据模式以最适合您的用例。

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

运行 SQL 查询来计算具有 NULL 值的行数可以轻松揭示表中实际需要 Nullable 值的列。

 
1-- Find non-null values columns 
2SELECT
3    countIf(vendor_id IS NULL) AS vendor_id_nulls,
4    countIf(pickup_datetime IS NULL) AS pickup_datetime_nulls,
5    countIf(dropoff_datetime IS NULL) AS dropoff_datetime_nulls,
6    countIf(passenger_count IS NULL) AS passenger_count_nulls,
7    countIf(trip_distance IS NULL) AS trip_distance_nulls,
8    countIf(fare_amount IS NULL) AS fare_amount_nulls,
9    countIf(mta_tax IS NULL) AS mta_tax_nulls,
10    countIf(tip_amount IS NULL) AS tip_amount_nulls,
11    countIf(tolls_amount IS NULL) AS tolls_amount_nulls,
12    countIf(total_amount IS NULL) AS total_amount_nulls,
13    countIf(payment_type IS NULL) AS payment_type_nulls,
14    countIf(pickup_location_id IS NULL) AS pickup_location_id_nulls,
15    countIf(dropoff_location_id IS NULL) AS dropoff_location_id_nulls
16FROM trips_small_inferred
17FORMAT VERTICAL
18
19Query id: 4a70fc5b-2501-41c8-813c-45ce241d85ae
20
21Row 1:
22──────
23vendor_id_nulls:           0
24pickup_datetime_nulls:     0
25dropoff_datetime_nulls:    0
26passenger_count_nulls:     0
27trip_distance_nulls:       0
28fare_amount_nulls:         0
29mta_tax_nulls:             137946731
30tip_amount_nulls:          0
31tolls_amount_nulls:        0
32total_amount_nulls:        0
33payment_type_nulls:        69305
34pickup_location_id_nulls:  0
35dropoff_location_id_nulls: 0

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

应用于字符串的简单优化是充分利用 LowCardinality 数据类型。正如 low cardinality 文档中所述，ClickHouse 将字典编码应用于 LowCardinality 列，这显着提高了查询性能。

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

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

 
1-- Identify low cardinality columns
2SELECT
3    uniq(ratecode_id),
4    uniq(pickup_location_id),
5    uniq(dropoff_location_id),
6    uniq(vendor_id)
7FROM trips_small_inferred
8FORMAT VERTICAL
9
10Query id: d502c6a1-c9bc-4415-9d86-5de74dd6d932
11
12Row 1:
13──────
14uniq(ratecode_id):         6
15uniq(pickup_location_id):  260
16uniq(dropoff_location_id): 260
17uniq(vendor_id):           3

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

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

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

 
1-- Find min/max values for the payment_type field
2SELECT
3    min(payment_type),max(payment_type),
4    min(passenger_count), max(passenger_count)
5FROM trips_small_inferred
6
7Query id: 4306a8e1-2a9c-4b06-97b4-4d902d2233eb
8
9   ┌─min(payment_type)─┬─max(payment_type)─┐
101. │                 1 │                 4 │
11   └───────────────────┴───────────────────┘

对于日期，您应该选择与您的数据集匹配并且最适合回答您计划运行的查询的精度。

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

 
1-- Create table with optimized data 
2CREATE TABLE trips_small_no_pk
3(
4    `vendor_id` LowCardinality(String),
5    `pickup_datetime` DateTime,
6    `dropoff_datetime` DateTime,
7    `passenger_count` UInt8,
8    `trip_distance` Float32,
9    `ratecode_id` LowCardinality(String),
10    `pickup_location_id` LowCardinality(String),
11    `dropoff_location_id` LowCardinality(String),
12    `payment_type` Nullable(UInt8),
13    `fare_amount` Decimal32(2),
14    `extra` Decimal32(2),
15    `mta_tax` Nullable(Decimal32(2)),
16    `tip_amount` Decimal32(2),
17    `tolls_amount` Decimal32(2),
18    `total_amount` Decimal32(2)
19)
20ORDER BY tuple();
21
22-- Insert the data 
23INSERT INTO trips_small_no_pk SELECT * FROM trips_small_inferred

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

名称	运行 1 - 经过时间	经过时间	处理的行数	峰值内存
查询 1	1.699 秒	1.353 秒	3.2904 亿	337.12 MiB
查询 2	1.419 秒	1.171 秒	3.2904 亿	531.09 MiB
查询 3	1.414 秒	1.188 秒	3.2904 亿	265.05 MiB

我们注意到查询时间和内存使用量都有所改进。由于数据模式的优化，我们减少了表示数据的总数据量，从而提高了内存消耗并缩短了处理时间。

让我们检查一下表的大小，看看差异。

 
1SELECT
2    `table`,
3    formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
4    formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
5    sum(rows) AS rows
6FROM system.parts
7WHERE (active = 1) AND ((`table` = 'trips_small_no_pk') OR (`table` = 'trips_small_inferred'))
8GROUP BY
9    database,
10    `table`
11ORDER BY size DESC
12
13Query id: 72b5eb1c-ff33-4fdb-9d29-dd076ac6f532
14
15   ┌─table────────────────┬─compressed─┬─uncompressed─┬──────rows─┐
161. │ trips_small_inferred │ 7.38 GiB   │ 37.41 GiB    │ 329044175 │
172. │ trips_small_no_pk    │ 4.89 GiB   │ 15.31 GiB    │ 329044175 │
18   └──────────────────────┴────────────┴──────────────┴───────────┘

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

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

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

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

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

ClickHouse 支持的其他选项（例如 Projection 或 Materialized view）允许您在相同的数据上使用不同的主键集。本博客系列的第二部分将更详细地介绍这一点。

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

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

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

在我们的例子中，我们将使用以下主键进行实验：passenger_count、pickup_datetime 和 dropoff_datetime。

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

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

 
1CREATE TABLE trips_small_pk
2(
3    `vendor_id` UInt8,
4    `pickup_datetime` DateTime,
5    `dropoff_datetime` DateTime,
6    `passenger_count` UInt8,
7    `trip_distance` Float32,
8    `ratecode_id` LowCardinality(String),
9    `pickup_location_id` UInt16,
10    `dropoff_location_id` UInt16,
11    `payment_type` Nullable(UInt8),
12    `fare_amount` Decimal32(2),
13    `extra` Decimal32(2),
14    `mta_tax` Nullable(Decimal32(2)),
15    `tip_amount` Decimal32(2),
16    `tolls_amount` Decimal32(2),
17    `total_amount` Decimal32(2)
18)
19PRIMARY KEY (passenger_count, pickup_datetime, dropoff_datetime);
20
21-- Insert the data 
22INSERT INTO trips_small_pk SELECT * FROM trips_small_inferred

然后我们重新运行我们的查询。我们编译来自三个实验的结果，以查看在经过时间、处理的行数和内存消耗方面的改进。

查询 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 从主键中获益最多。让我们看看生成的查询计划与之前有何不同。

 
1EXPLAIN indexes = 1
2SELECT
3    payment_type,
4    COUNT() AS trip_count,
5    formatReadableQuantity(SUM(trip_distance)) AS total_distance,
6    AVG(total_amount) AS total_amount_avg,
7    AVG(tip_amount) AS tip_amount_avg
8FROM nyc_taxi.trips_small_pk
9WHERE (pickup_datetime >= '2009-01-01') AND (pickup_datetime < '2009-04-01')
10GROUP BY payment_type
11ORDER BY trip_count DESC
12
13Query id: 30116a77-ba86-4e9f-a9a2-a01670ad2e15
14
15    ┌─explain──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
16 1. │ Expression ((Projection + Before ORDER BY [lifted up part]))                                                     │
17 2. │   Sorting (Sorting for ORDER BY)                                                                                 │
18 3. │     Expression (Before ORDER BY)                                                                                 │
19 4. │       Aggregating                                                                                                │
20 5. │         Expression (Before GROUP BY)                                                                             │
21 6. │           Expression                                                                                             │
22 7. │             ReadFromMergeTree (nyc_taxi.trips_small_pk)                                                          │
23 8. │             Indexes:                                                                                             │
24 9. │               PrimaryKey                                                                                         │
2510. │                 Keys:                                                                                            │
2611. │                   pickup_datetime                                                                                │
2712. │                 Condition: and((pickup_datetime in (-Inf, 1238543999]), (pickup_datetime in [1230768000, +Inf))) │
2813. │                 Parts: 9/9                                                                                       │
2914. │                 Granules: 5061/40167                                                                             │
30    └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

由于主键，仅选择了表 granule 的子集。仅此一项就大大提高了查询性能，因为 ClickHouse 必须处理的数据明显减少。

ClickHouse 是一个性能非常出色的分析数据库，并实施了大量的性能优化来实现这一点。但是，要释放 ClickHouse 性能的全部潜力，有必要了解数据库的工作原理以及如何最好地利用它。通过利用您在本博客中学到的知识，例如识别性能较差的查询并了解如何通过对数据模式应用基本但功能强大的更改来优化它们，您将看到查询性能的显着改进。

如果您不熟悉 ClickHouse，这是一个很好的起点。但是，如果您是经验丰富的 ClickHouse 用户，那么本博客文章中讨论的一些主题对您来说可能不是新闻。在我们的下一篇博客中，我们将介绍更高级的主题，例如投影、物化视图和数据跳过索引。敬请关注。

立即开始使用 ClickHouse Cloud，并获得 300 美元的信用额度。在 30 天试用期结束时，继续使用按需付费计划，或联系我们以了解有关我们基于数量的折扣的更多信息。访问我们的定价页面了解详情。

分享此文章