本文字数：18076；估计阅读时间：46分钟
作者： Lionel Palacin
本文在公众号【ClickHouseInc】首发
几个月前，我加入了 ClickHouse 的产品营销工程团队。在此之前，我在 Elastic 专注于搜索解决方案。转岗后，我需要快速熟悉 ClickHouse 和 OLAP 数据库的核心知识。
我的第一个项目是参与推出全新的 ClickHouse Playground。这是一个包含丰富数据集的平台，非常适合我学习和实践 ClickHouse 的强大功能。
在 ClickHouse Playground 发布后，我对用户体验产生了浓厚的兴趣，特别是想知道我们提供的一些示例查询是否还可以进一步优化。通过按需培训、视频、文档和博客等学习资源，我积累了许多优化 ClickHouse 查询的技巧。本文是一个两部分系列博客的第一篇，旨在分享一些实用的优化方法。
在本篇中，我们将介绍 ClickHouse 提供的工具，帮助您分析和排查慢查询。接着，我们会讨论基础优化和主键设计的重要性。在下一篇中，我们将探讨更高级的优化技术，例如投影 (projections)、物化视图 (materialized views) 和跳过索引 (data-skipping indexes)。
如何分析查询性能
性能优化的最佳时机是在您首次向 ClickHouse 导入数据之前，设计数据模式时。
然而，现实中很难准确预测数据的增长规模或未来会执行的查询类型。
如果您刚刚开始使用 ClickHouse，可以直接跳过这一部分，前往下一节，学习基础优化和主键设计的关键点。
但如果您已经有了一个运行中的部署，并希望改进某些查询的性能，那么第一步是了解这些查询的执行情况。具体来说，分析为什么某些查询只需几毫秒，而另一些却耗时更久。
ClickHouse 提供了一系列强大的工具，帮助您深入了解查询的执行方式以及资源消耗。
接下来的内容中，我们将详细介绍这些工具及其使用方法。
理解查询性能的核心要点
要理解查询性能，首先让我们简单了解一下 ClickHouse 在执行查询时的主要流程。
以下内容经过简化，目的是帮助您快速掌握基本概念，而不是陷入繁琐的细节之中。如果您希望深入了解，可以参考查询分析器的文档。
从一个高层次的角度来看，当 ClickHouse 执行查询时，主要经历以下几个阶段：
1. 查询解析与分析
首先，查询会被解析和分析，生成一个初步的查询执行计划。
2. 查询优化
接下来，查询执行计划会经过优化，包括剪枝不必要的数据，并根据查询计划构建查询管道。
3. 查询管道的执行
在这个阶段，ClickHouse 会以并行方式读取和处理数据。这是实际执行查询操作的过程，包括过滤、聚合和排序等。
4. 最终处理
最后，查询结果会被合并、排序，并格式化为最终的输出结果，然后发送给客户端。
实际上，查询执行过程中包含了许多额外的优化步骤，我们将在本指南后续部分深入讨论。不过，目前这些核心概念足以帮助您理解 ClickHouse 查询执行背后的基本原理。
现在，基于这个高层次的理解，我们来看一看 ClickHouse 提供的工具，以及如何利用它们追踪影响查询性能的关键指标。
探索查询优化的演示环境
正如我们在介绍中提到的，我们最近上线了 ClickHouse Playground 演示环境，用户可以在这里用 ClickHouse 测试不同的数据集。这个环境运行在 ClickHouse Cloud 上，已经吸引了数百名用户的参与。
在本文中，我将利用这个环境展示如何优化查询性能。
数据集介绍
ClickHouse Playground 中的一个数据集是 NYC Taxi 数据集，其中包含纽约市出租车行程的详细数据。在没有进行任何优化的情况下，我们已经将这个数据集导入。
以下是创建表并从 S3 存储桶导入数据的命令。为了方便操作，我们直接从数据中推断表结构。
-- Create table with inferred schema
CREATE TABLE trips_small_inferred
ORDER BY () EMPTY
AS SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet');
-- Insert data into table with inferred schema
INSERT INTO trips_small_inferred
SELECT *
FROM s3Cluster
('default','https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet');
接下来，我们先看一下 ClickHouse 自动生成的表结构。
--- Display inferred table schema
SHOW CREATE TABLE trips_small_inferred
Query id: d97361fd-c050-478e-b831-369469f0784d
CREATE TABLE nyc_taxi.trips_small_inferred
(
`vendor_id` Nullable(String),
`pickup_datetime` Nullable(DateTime64(6, 'UTC')),
`dropoff_datetime` Nullable(DateTime64(6, 'UTC')),
`passenger_count` Nullable(Int64),
`trip_distance` Nullable(Float64),
`ratecode_id` Nullable(String),
`pickup_location_id` Nullable(String),
`dropoff_location_id` Nullable(String),
`payment_type` Nullable(Int64),
`fare_amount` Nullable(Float64),
`extra` Nullable(Float64),
`mta_tax` Nullable(Float64),
`tip_amount` Nullable(Float64),
`tolls_amount` Nullable(Float64),
`total_amount` Nullable(Float64)
)
ORDER BY tuple()
识别慢查询
查询日志的使用
ClickHouse 会默认记录所有查询的执行信息，并存储在 system.query_log 表中。这些日志包含查询执行时间、读取行数以及资源使用情况（如 CPU 和内存消耗、文件系统缓存命中次数）。
如果您使用的是 ClickHouse Cloud，由于 query_log 表分布在多个节点上，需要使用 clusterAllReplicas(default, system.query_log)。而在本地运行时，只需替换为 FROM system.query_log。
ClickHouse 会为每个执行的查询记录统计数据，例如查询执行时间、读取的行数以及资源使用情况，包括 CPU、内存使用量和文件系统缓存命中率。
查询日志是定位慢查询的理想起点。通过这些日志，您可以快速找到运行时间较长的查询，并查看它们的资源消耗情况。
长时间运行查询分析
让我们筛选 NYC Taxi 数据集中运行时间最长的前五个查询。
-- Find top 5 long running queries from nyc_taxi database in the last 1 hour
SELECT
type,
event_time,
query_duration_ms,
query,
read_rows,
tables
FROM clusterAllReplicas(default, system.query_log)
WHERE has(databases, 'nyc_taxi') AND (event_time >= (now() - toIntervalMinute(60))) AND type='QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 5
FORMAT VERTICAL
Query id: e3d48c9f-32bb-49a4-8303-080f59ed1835
Row 1:
──────
type: QueryFinish
event_time: 2024-11-27 11:12:36
query_duration_ms: 2967
query: WITH
dateDiff('s', pickup_datetime, dropoff_datetime) as trip_time,
trip_distance / trip_time * 3600 AS speed_mph
SELECT
quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
FROM
nyc_taxi.trips_small_inferred
WHERE
speed_mph > 30
FORMAT JSON
read_rows: 329044175
tables: ['nyc_taxi.trips_small_inferred']
Row 2:
──────
type: QueryFinish
event_time: 2024-11-27 11:11:33
query_duration_ms: 2026
query: SELECT
payment_type,
COUNT() AS trip_count,
formatReadableQuantity(SUM(trip_distance)) AS total_distance,
AVG(total_amount) AS total_amount_avg,
AVG(tip_amount) AS tip_amount_avg
FROM
nyc_taxi.trips_small_inferred
WHERE
pickup_datetime >= '2009-01-01' AND pickup_datetime < '2009-04-01'
GROUP BY
payment_type
ORDER BY
trip_count DESC;
read_rows: 329044175
tables: ['nyc_taxi.trips_small_inferred']
Row 3:
──────
type: QueryFinish
event_time: 2024-11-27 11:12:17
query_duration_ms: 1860
query: SELECT
avg(dateDiff('s', pickup_datetime, dropoff_datetime))
FROM nyc_taxi.trips_small_inferred
WHERE passenger_count = 1 or passenger_count = 2
FORMAT JSON
read_rows: 329044175
tables: ['nyc_taxi.trips_small_inferred']
Row 4:
──────
type: QueryFinish
event_time: 2024-11-27 11:12:31
query_duration_ms: 690
query: SELECT avg(total_amount) FROM nyc_taxi.trips_small_inferred WHERE trip_distance > 5
FORMAT JSON
read_rows: 329044175
tables: ['nyc_taxi.trips_small_inferred']
Row 5:
──────
type: QueryFinish
event_time: 2024-11-27 11:12:44
query_duration_ms: 634
query: SELECT
vendor_id,
avg(total_amount),
avg(trip_distance),
FROM
nyc_taxi.trips_small_inferred
GROUP BY vendor_id
ORDER BY 1 DESC
FORMAT JSON
read_rows: 329044175
tables: ['nyc_taxi.trips_small_inferred']
通过字段 query_duration_ms，我们可以看到第一个查询的执行时间为 2967 毫秒，显然有优化的空间。
除此之外，您还可以检查内存和 CPU 消耗最多的查询，以找到对系统负载最大的操作。
-- Top queries by memory usage
SELECT
type,
event_time,
query_id,
formatReadableSize(memory_usage) AS memory,
ProfileEvents.Values[indexOf(ProfileEvents.Names, 'UserTimeMicroseconds')] AS userCPU,
ProfileEvents.Values[indexOf(ProfileEvents.Names, 'SystemTimeMicroseconds')] AS systemCPU,
(ProfileEvents['CachedReadBufferReadFromCacheMicroseconds']) / 1000000 AS FromCacheSeconds,
(ProfileEvents['CachedReadBufferReadFromSourceMicroseconds']) / 1000000 AS FromSourceSeconds,
normalized_query_hash
FROM clusterAllReplicas(default, system.query_log)
WHERE has(databases, 'nyc_taxi') AND (type='QueryFinish') AND ((event_time >= (now() - toIntervalDay(2))) AND (event_time <= now())) AND (user NOT ILIKE '%internal%')
ORDER BY memory_usage DESC
LIMIT 30
接下来，我们将定位这些慢查询并多次重新运行，以便更好地了解它们的响应时间。
同时，为了确保结果的可重复性，我们需要将 enable_filesystem_cache 设置为 0，关闭文件系统缓存。
-- Disable filesystem cache
set enable_filesystem_cache = 0;
-- Run query 1
WITH
dateDiff('s', pickup_datetime, dropoff_datetime) as trip_time,
trip_distance / trip_time * 3600 AS speed_mph
SELECT
quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
FROM
nyc_taxi.trips_small_inferred
WHERE
speed_mph > 30
FORMAT JSON
----
1 row in set. Elapsed: 1.699 sec. Processed 329.04 million rows, 8.88 GB (193.72 million rows/s., 5.23 GB/s.)
Peak memory usage: 440.24 MiB.
-- Run query 2
SELECT
payment_type,
COUNT() AS trip_count,
formatReadableQuantity(SUM(trip_distance)) AS total_distance,
AVG(total_amount) AS total_amount_avg,
AVG(tip_amount) AS tip_amount_avg
FROM
nyc_taxi.trips_small_inferred
WHERE
pickup_datetime >= '2009-01-01' AND pickup_datetime < '2009-04-01'
GROUP BY
payment_type
ORDER BY
trip_count DESC;
---
4 rows in set. Elapsed: 1.419 sec. Processed 329.04 million rows, 5.72 GB (231.86 million rows/s., 4.03 GB/s.)
Peak memory usage: 546.75 MiB.
-- Run query 3
SELECT
avg(dateDiff('s', pickup_datetime, dropoff_datetime))
FROM nyc_taxi.trips_small_inferred
WHERE passenger_count = 1 or passenger_count = 2
FORMAT JSON
---
1 row in set. Elapsed: 1.414 sec. Processed 329.04 million rows, 8.88 GB (232.63 million rows/s., 6.28 GB/s.)
Peak memory usage: 451.53 MiB.
查询性能与资源分析
|
Name | Elapsed | Rows processed | Peak memory |
| Query 1 | 1.699 sec | 329.04 million | 440.24 MiB |
| Query 2 | 1.419 sec | 329.04 million | 546.75 MiB |
| Query 3 | 1.414 sec | 329.04 million | 451.53 MiB |
通过日志分析，我们发现：
1. 数据表包含 3.2904 亿行记录，每个查询都对表进行了全表扫描。
2. 查询 1：计算平均速度超过 30 英里/小时行程的距离分布。
3. 查询 2：统计每周行程数量和平均费用。
4. 查询 3：计算每次行程的平均时间。
尽管这些查询本身并不复杂，但第一个查询需要动态计算行程时间，这使其负载较高。每个查询的执行时间都超过 1 秒，而在 ClickHouse 环境中，这已经属于较长时间。同时，每个查询大约消耗 400 MB 内存，并且读取了相同数量的行数（3.2904 亿行）。接下来，我们将快速确认表的实际行数。
-- Count number of rows in table
SELECT count()
FROM nyc_taxi.trips_small_inferred
Query id: 733372c5-deaf-4719-94e3-261540933b23
┌───count()─┐
1. │ 329044175 │ -- 329.04 million
└───────────┘
该表共有 3.29 亿行，因此每次查询都会对表进行全表扫描。
此外，ClickHouse Cloud 提供了一个名为 Query insight 的功能丰富的 UI，可以通过各种可视化和表格展示查询日志。
深入分析慢查询：EXPLAIN 工具的使用
在分析慢查询时，ClickHouse 提供了一项强大的工具——EXPLAIN 语句。它可以详细展示查询执行的每个阶段，而无需实际运行查询。这对于深入了解查询的执行逻辑非常重要。虽然这个工具的输出可能显得复杂，但只需掌握一些关键命令，就可以快速定位性能瓶颈。
接下来，我们将重点介绍两个常用的 EXPLAIN 命令，并通过案例展示如何分析查询。
EXPLAIN indexes = 1：查询计划分析
使用 EXPLAIN indexes = 1 命令可以检查查询计划。查询计划以树状结构展示查询的执行顺序，帮助我们直观了解 ClickHouse 如何处理每个子句。需要注意的是，查询计划的输出应从下往上阅读。
现在，我们尝试运行第一个长时间运行的查询。
EXPLAIN indexes = 1
WITH
dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,
(trip_distance / trip_time) * 3600 AS speed_mph
SELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
FROM nyc_taxi.trips_small_inferred
WHERE speed_mph > 30
Query id: f35c412a-edda-4089-914b-fa1622d69868
┌─explain─────────────────────────────────────────────┐
1. │ Expression ((Projection + Before ORDER BY)) │
2. │ Aggregating │
3. │ Expression (Before GROUP BY) │
4. │ Filter (WHERE) │
5. │ ReadFromMergeTree (nyc_taxi.trips_small_inferred) │
└─────────────────────────────────────────────────────┘
以我们发现的其中一个长时间运行的查询为例，使用该命令后，我们可以清楚地看到：
1. 查询从表 nyc_taxi.trips_small_inferred 读取数据。
2. 然后应用 WHERE 子句，过滤符合条件的行。
3. 接着对过滤后的数据进行聚合操作并计算分位数。
4. 最后，对结果进行排序并输出。
我们还注意到，由于创建表时未定义主键，ClickHouse 只能对整张表进行全表扫描，这也是查询较慢的原因之一。
EXPLAIN Pipeline：执行策略分析
除了查询计划，`EXPLAIN Pipeline` 提供了查询的具体执行策略。通过该命令，我们可以深入了解查询实际的执行方式。以同一查询为例，Pipeline 输出显示：
EXPLAIN PIPELINE
WITH
dateDiff('s', pickup_datetime, dropoff_datetime) AS trip_time,
(trip_distance / trip_time) * 3600 AS speed_mph
SELECT quantiles(0.5, 0.75, 0.9, 0.99)(trip_distance)
FROM nyc_taxi.trips_small_inferred
WHERE speed_mph > 30
Query id: c7e11e7b-d970-4e35-936c-ecfc24e3b879
┌─explain─────────────────────────────────────────────────────────────────────────────┐
1. │ (Expression) │
2. │ ExpressionTransform × 59 │
3. │ (Aggregating) │
4. │ Resize 59 → 59 │
5. │ AggregatingTransform × 59 │
6. │ StrictResize 59 → 59 │
7. │ (Expression) │
8. │ ExpressionTransform × 59 │
9. │ (Filter) │
10. │ FilterTransform × 59 │
11. │ (ReadFromMergeTree) │
12. │ MergeTreeSelect(pool: PrefetchedReadPool, algorithm: Thread) × 59 0 → 1 │
查询使用了 59 个线程执行，这表明 ClickHouse 进行了高度并行化处理。 并行线程的增加显著缩短了查询时间，但同时也导致内存使用量较高。
通过分析这些信息，我们能够更好地理解查询的资源消耗及性能瓶颈。
优化建议
对于每一个慢查询，您都可以采用类似的方法，通过查询日志和 EXPLAIN 工具，了解查询读取的行数、资源消耗及执行过程。这样，您可以找出不必要的复杂查询计划，优化查询性能，为后续操作奠定基础。
优化查询的方法与实践
在生产环境中定位问题查询可能是一项挑战，因为 ClickHouse 部署中的任何时刻都可能有大量查询在执行。
如果您已经知道某个用户、数据库或表存在性能问题，可以利用system.query_log 表中的字段 user、tables 或 databases 来缩小搜索范围，快速锁定目标。
一旦确定了需要优化的查询，接下来的重点是以结构化的方式逐步优化。然而，很多开发人员在这个阶段常犯的一个错误是一次性修改多个参数，随意测试结果。这种方法通常会产生混杂的效果，更重要的是，难以明确究竟是哪些改动提升了查询性能。
查询优化需要一个清晰的流程。您不需要复杂的基准测试，但一个简单的、有条理的优化方法可以帮助您更好地理解修改对性能的具体影响。
逐步优化的流程
识别慢查询：从查询日志入手，定位运行时间较长的查询。
逐一分析改进措施：对潜在的优化点进行隔离测试。在测试过程中，确保禁用文件系统缓存 (enable_filesystem_cache=0)。
ClickHouse 在多个阶段使用缓存来加速查询性能。虽然这对实际运行很有帮助，但在排查性能问题时，缓存可能会掩盖潜在的 I/O 瓶颈或表设计缺陷。因此，关闭缓存能够更真实地反映查询性能。
逐步实施优化：对于识别出的优化措施，建议一次只应用一个更改，以便清晰地跟踪每次调整对查询性能的具体影响。
下面的图表展示了这个通用优化方法的关键步骤：
注意异常查询
在排查慢查询时，还需要小心偶然的异常情况。例如，某些查询可能因用户临时执行了代价高昂的查询而变慢，或者由于系统其他部分的压力导致性能下降。为此，您可以使用字段 normalized_query_hash 对查询分组，识别那些经常执行且代价昂贵的查询。这些查询通常是最值得深入优化的目标。
从基础入手优化 ClickHouse 查询
现在，我们已经搭建好了测试框架，可以开始实际的优化工作。
从存储方式着手
查询优化的第一步是检查数据的存储方式。对于任何数据库而言，读取的数据越少，查询速度就越快。
在数据导入时，您可能使用了 ClickHouse 自动推断表模式的功能。这种方法适合快速启动，但如果您希望进一步优化查询性能，就需要根据实际场景重新设计数据模式，以更好地适配您的需求。
减少 Nullable 列的使用
根据最佳实践，尽量避免使用 Nullable 列。虽然 Nullable 列使数据导入更灵活，但会增加额外的性能开销，因为需要处理额外的列。
运行一个简单的 SQL 查询，统计哪些列存在 NULL 值，可以快速识别哪些列真正需要使用 Nullable 类型。
-- Find non-null values columns
SELECT
countIf(vendor_id IS NULL) AS vendor_id_nulls,
countIf(pickup_datetime IS NULL) AS pickup_datetime_nulls,
countIf(dropoff_datetime IS NULL) AS dropoff_datetime_nulls,
countIf(passenger_count IS NULL) AS passenger_count_nulls,
countIf(trip_distance IS NULL) AS trip_distance_nulls,
countIf(fare_amount IS NULL) AS fare_amount_nulls,
countIf(mta_tax IS NULL) AS mta_tax_nulls,
countIf(tip_amount IS NULL) AS tip_amount_nulls,
countIf(tolls_amount IS NULL) AS tolls_amount_nulls,
countIf(total_amount IS NULL) AS total_amount_nulls,
countIf(payment_type IS NULL) AS payment_type_nulls,
countIf(pickup_location_id IS NULL) AS pickup_location_id_nulls,
countIf(dropoff_location_id IS NULL) AS dropoff_location_id_nulls
FROM trips_small_inferred
FORMAT VERTICAL
Query id: 4a70fc5b-2501-41c8-813c-45ce241d85ae
Row 1:
──────
vendor_id_nulls: 0
pickup_datetime_nulls: 0
dropoff_datetime_nulls: 0
passenger_count_nulls: 0
trip_distance_nulls: 0
fare_amount_nulls: 0
mta_tax_nulls: 137946731
tip_amount_nulls: 0
tolls_amount_nulls: 0
total_amount_nulls: 0
payment_type_nulls: 69305
pickup_location_id_nulls: 0
dropoff_location_id_nulls: 0
在我们的数据集中，仅有 mta_tax 和 payment_type 两列包含 NULL 值，其余字段可以移除 Nullable 类型。
利用 LowCardinality 提升性能
对于字符串类型数据，LowCardinality 是一个极具价值的优化工具。ClickHouse 使用字典编码处理 LowCardinality 列，大幅提升查询效率。
判断哪些列适合 LowCardinality 的一个简单经验是：如果某列的唯一值少于 10,000 个，就非常适合使用该类型。
您可以使用以下 SQL 查询来查找唯一值较少的列。
-- Identify low cardinality columns
SELECT
uniq(ratecode_id),
uniq(pickup_location_id),
uniq(dropoff_location_id),
uniq(vendor_id)
FROM trips_small_inferred
FORMAT VERTICAL
Query id: d502c6a1-c9bc-4415-9d86-5de74dd6d932
Row 1:
──────
uniq(ratecode_id): 6
uniq(pickup_location_id): 260
uniq(dropoff_location_id): 260
uniq(vendor_id): 3
在我们的数据集中，ratecode_id、pickup_location_id、dropoff_location_id 和 vendor_id 是理想的 LowCardinality 列。
选择合适的数据类型
ClickHouse 支持多种数据类型。在设计表模式时，选择最小的数据类型可以优化查询性能并减少磁盘存储空间。
数字类型：检查数据的最小值和最大值，确保当前的数据精度符合实际需求。
-- Find min/max values for the payment_type field
SELECT
min(payment_type),max(payment_type),
min(passenger_count), max(passenger_count)
FROM trips_small_inferred
Query id: 4306a8e1-2a9c-4b06-97b4-4d902d2233eb
┌─min(payment_type)─┬─max(payment_type)─┐
1. │ 1 │ 4 │
└───────────────────┴───────────────────┘
日期类型：根据数据集和查询需求，选择适合的日期精度，避免不必要的存储开销。
应用优化并评估效果
我们通过重新设计表模式，创建一个优化后的新表并重新导入数据。
-- Create table with optimized data
CREATE TABLE trips_small_no_pk
(
`vendor_id` LowCardinality(String),
`pickup_datetime` DateTime,
`dropoff_datetime` DateTime,
`passenger_count` UInt8,
`trip_distance` Float32,
`ratecode_id` LowCardinality(String),
`pickup_location_id` LowCardinality(String),
`dropoff_location_id` LowCardinality(String),
`payment_type` Nullable(UInt8),
`fare_amount` Decimal32(2),
`extra` Decimal32(2),
`mta_tax` Nullable(Decimal32(2)),
`tip_amount` Decimal32(2),
`tolls_amount` Decimal32(2),
`total_amount` Decimal32(2)
)
ORDER BY tuple();
-- Insert the data
INSERT INTO trips_small_no_pk SELECT * FROM trips_small_inferred
我们使用新表重新运行查询，以评估改进效果。
| Name | Run 1 - Elapsed | Elapsed | Rows processed | Peak memory |
| Query 1 | 1.699 sec | 1.353 sec | 329.04 million | 337.12 MiB |
| Query 2 | 1.419 sec | 1.171 sec | 329.04 million | 531.09 MiB |
| Query 3 | 1.414 sec | 1.188 sec | 329.04 million | 265.05 MiB |
重新运行查询后，我们观察到查询时间和内存使用均有显著改进。优化后的模式减少了存储的数据量，从而降低了内存开销和查询处理时间。
存储空间的显著缩减
让我们比较一下优化前后表的大小：
SELECT
`table`,
formatReadableSize(sum(data_compressed_bytes) AS size) AS compressed,
formatReadableSize(sum(data_uncompressed_bytes) AS usize) AS uncompressed,
sum(rows) AS rows
FROM system.parts
WHERE (active = 1) AND ((`table` = 'trips_small_no_pk') OR (`table` = 'trips_small_inferred'))
GROUP BY
database,
`table`
ORDER BY size DESC
Query id: 72b5eb1c-ff33-4fdb-9d29-dd076ac6f532
┌─table────────────────┬─compressed─┬─uncompressed─┬──────rows─┐
1. │ trips_small_inferred │ 7.38 GiB │ 37.41 GiB │ 329044175 │
2. │ trips_small_no_pk │ 4.89 GiB │ 15.31 GiB │ 329044175 │
└──────────────────────┴────────────┴──────────────┴───────────┘
新表的磁盘占用减少了约 34%（从 7.38 GiB 降至 4.89 GiB）。这一优化不仅节省了存储空间，还对查询性能的提升起到了关键作用。
理解 ClickHouse 主键的独特性
在大多数传统数据库中，主键的主要作用是强制数据的唯一性和完整性。任何试图插入重复主键值的操作都会被拒绝，并且通常会创建基于 B 树或哈希的索引来加速查找操作。
然而，在 ClickHouse 中，主键的作用完全不同。它并不负责确保数据的唯一性或完整性，而是专为优化查询性能而设计。主键定义了数据在磁盘上的存储顺序，并通过稀疏索引实现，该索引记录了指向每个 granule 第一行的指针。
什么是 granule？
在 ClickHouse 中，granule 是查询执行时读取的最小数据单元。一个 granule 包含固定数量的行，其行数由 index_granularity 参数决定，默认值为 8192 行。granule 数据按主键排序并连续存储在磁盘上。
为何主键选择至关重要？
为表选择一组合适的主键对性能优化至关重要。常见的实践是，将相同的数据存储在不同的表中，为每张表设计不同的主键，以加速特定的查询场景。例如，针对时间范围的查询可以使用时间戳作为主键，而针对地理位置的查询则可以选择地理位置字段作为主键。
其他可选方案
ClickHouse 提供了一些额外的功能，例如投影 (Projection) 和物化视图 (Materialized View)，可以让您在同一数据集上使用不同的主键，以满足多种查询需求。本系列博客的第二部分将深入探讨这些高级优化技术，敬请期待。
如何通过主键优化查询性能
选择适合的主键是优化 ClickHouse 查询性能的关键一步。尽管这一过程可能涉及权衡和多次实验，但遵循一些基本原则可以帮助您快速入门：
优先选择常用于过滤条件的字段：查询中频繁用作筛选的字段是主键的首选。
从低基数字段入手：低基数的列（如值较少的字段）通常能显著提升查询效率。
引入时间字段：时间戳字段是许多数据集中的核心过滤条件，在主键中加入它们可以极大优化基于时间的查询。
在我们的案例中，我们尝试使用以下主键组合：passenger_count、pickup_datetime 和 dropoff_datetime。
passenger_count 只有 24 个唯一值，基数很低，并且在慢查询中经常使用。 pickup_datetime 和 dropoff_datetime 是时间戳字段，可以作为常见的过滤条件。
我们基于新的主键组合创建了一张表，并重新导入数据。
CREATE TABLE trips_small_pk
(
`vendor_id` UInt8,
`pickup_datetime` DateTime,
`dropoff_datetime` DateTime,
`passenger_count` UInt8,
`trip_distance` Float32,
`ratecode_id` LowCardinality(String),
`pickup_location_id` UInt16,
`dropoff_location_id` UInt16,
`payment_type` Nullable(UInt8),
`fare_amount` Decimal32(2),
`extra` Decimal32(2),
`mta_tax` Nullable(Decimal32(2)),
`tip_amount` Decimal32(2),
`tolls_amount` Decimal32(2),
`total_amount` Decimal32(2)
)
PRIMARY KEY (passenger_count, pickup_datetime, dropoff_datetime);
-- Insert the data
INSERT INTO trips_small_pk SELECT * FROM trips_small_inferred
性能优化效果
重新运行查询后，我们对比了三次实验的结果，观察查询耗时、处理行数和内存使用的变化。
| Query 1 | |||
| Run 1 | Run 2 | Run 3 | |
| Elapsed | 1.699 sec | 1.353 sec | 0.765 sec |
| Rows processed | 329.04 million | 329.04 million | 329.04 million |
| Peak memory | 440.24 MiB | 337.12 MiB | 444.19 MiB |
| Query 2 | |||
| Run 1 | Run 2 | Run 3 | |
| Elapsed | 1.419 sec | 1.171 sec | 0.248 sec |
| Rows processed | 329.04 million | 329.04 million | 41.46 million |
| Peak memory | 546.75 MiB | 531.09 MiB | 173.50 MiB |
| Query 3 | |||
| Run 1 | Run 2 | Run 3 | |
| Elapsed | 1.414 sec | 1.188 sec | 0.431 sec |
| Rows processed | 329.04 million | 329.04 million | 276.99 million |
| Peak memory | 451.53 MiB | 265.05 MiB | 197.38 MiB |
实验结果显示，执行时间和内存消耗方面都有显著改善。
查询 2 从主键优化中获益最大。主键的引入让查询计划发生了明显变化。
EXPLAIN indexes = 1
SELECT
payment_type,
COUNT() AS trip_count,
formatReadableQuantity(SUM(trip_distance)) AS total_distance,
AVG(total_amount) AS total_amount_avg,
AVG(tip_amount) AS tip_amount_avg
FROM nyc_taxi.trips_small_pk
WHERE (pickup_datetime >= '2009-01-01') AND (pickup_datetime < '2009-04-01')
GROUP BY payment_type
ORDER BY trip_count DESC
Query id: 30116a77-ba86-4e9f-a9a2-a01670ad2e15
┌─explain──────────────────────────────────────────────────────────────────────────────────────────────────────────┐
1. │ Expression ((Projection + Before ORDER BY [lifted up part])) │
2. │ Sorting (Sorting for ORDER BY) │
3. │ Expression (Before ORDER BY) │
4. │ Aggregating │
5. │ Expression (Before GROUP BY) │
6. │ Expression │
7. │ ReadFromMergeTree (nyc_taxi.trips_small_pk) │
8. │ Indexes: │
9. │ PrimaryKey │
10. │ Keys: │
11. │ pickup_datetime │
12. │ Condition: and((pickup_datetime in (-Inf, 1238543999]), (pickup_datetime in [1230768000, +Inf))) │
13. │ Parts: 9/9 │
14. │ Granules: 5061/40167 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
仅一部分 granule 被选中供查询处理，而非全表扫描。由于 ClickHouse 需要处理的数据量大幅减少，查询性能得到了显著提升。
总结与展望
ClickHouse 是一款极具性能优势的分析型数据库，其核心设计中包含丰富的优化机制。但要真正释放其性能潜力，了解数据库的工作方式以及如何设计最佳数据模式至关重要。
通过本文的内容，您可以学会如何识别低效查询，并通过对数据模式进行简单却高效的优化，获得显著的性能提升。这是初学者优化 ClickHouse 的理想起点。
如果您已经是一名资深用户，可能对本文讨论的内容感到熟悉。在下一篇博客中，我们将深入探索更高级的优化技术，例如投影 (Projection)、物化视图 (Materialized Views) 和跳过索引 (Data Skipping Index)。敬请关注！
征稿启示
面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com
扫一扫
2019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
