本节旨在通过常见场景说明如何使用不同的性能和优化技术，例如 分析器、查询分析 或 避免可空列，以提高 ClickHouse 查询性能。
思考性能优化的最佳时机是在设置 数据模式 并在首次将数据导入 ClickHouse 之前。
但说实话，很难预测数据的增长量或将执行的查询类型。
如果您有一个现有的部署，并且想要改进几个查询，那么第一步是了解这些查询的性能以及为什么有些查询在几毫秒内执行完毕，而另一些查询则需要更长时间。
ClickHouse 拥有一套丰富的工具来帮助您了解查询的执行方式以及执行查询所消耗的资源。
在本节中，我们将了解这些工具以及如何使用它们。
通用注意事项
为了理解查询性能，让我们看看当 ClickHouse 执行查询时会发生什么。
以下内容经过了刻意简化，并进行了一些捷径；这里的想法不是用细节淹没您，而是让您快速了解基本概念。有关更多信息，您可以阅读关于 查询分析器 的内容。
从非常高级别的角度来看，当 ClickHouse 执行查询时，会发生以下情况：
查询将被解析和分析，并创建一个通用的查询执行计划。
查询执行计划将被优化，不必要的数据将被修剪，并从查询计划构建一个查询管道。
数据将并行读取和处理。这是 ClickHouse 实际执行查询操作（例如过滤、聚合和排序）的阶段。
结果将被合并、排序并格式化为最终结果，然后发送到客户端。
实际上，许多 优化 正在进行中，我们将在本指南中进一步讨论它们，但现在，这些主要概念使我们很好地了解了 ClickHouse 执行查询时幕后发生的事情。
有了这种高级别的理解，让我们检查 ClickHouse 提供的工具以及如何使用它们来跟踪影响查询性能的指标。
数据集
我们将使用一个真实示例来说明我们如何处理查询性能。
让我们使用纽约出租车数据集，其中包含纽约出租车行程数据。首先，我们从不进行优化的状态开始导入纽约出租车数据集。
以下是从 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');
让我们看一下从数据自动推断的表模式。
--- 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 表中。
对于每个执行的查询，ClickHouse 会记录诸如查询执行时间、读取的行数以及资源使用情况（例如 CPU、内存使用情况或文件系统缓存命中）等统计信息。
因此，查询日志是调查慢查询的好地方。您可以轻松地发现花费很长时间执行的查询，并显示每个查询的资源使用信息。
让我们找到纽约出租车数据集上运行时间最长的五个查询。
-- 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.
在表格中总结以便于阅读。
| 名称 | 经过时间 | 处理的行数 | 峰值内存 |
|---|
| 查询 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 亿）。让我们快速确认此表中有多少行。
-- 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.2904 亿行，因此每个查询都在对表进行完全扫描。
解释语句
现在我们有一些运行时间长的查询，让我们了解它们是如何执行的。为此，ClickHouse 支持 EXPLAIN 语句命令。这是一个非常有用的工具，可以在不实际运行查询的情况下提供所有查询执行阶段的详细视图。虽然对于非 ClickHouse 专家来说，查看它可能会让人不知所措，但它仍然是深入了解查询执行方式的重要工具。
文档提供了关于 EXPLAIN 语句是什么以及如何使用它来分析查询执行 的详细指南。为了不重复本指南中的内容，让我们专注于一些可以帮助我们找到查询执行性能瓶颈的命令。
解释索引 = 1
让我们从 EXPLAIN indexes = 1 开始检查查询计划。查询计划是一个树，显示查询将如何执行。在那里，您可以查看查询中的哪些子句将按什么顺序执行。EXPLAIN 语句返回的查询计划可以从底部到顶部读取。
让我们尝试使用我们的第一个运行时间长的查询。
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) │
└─────────────────────────────────────────────────────┘
输出很简单。查询从读取 nyc_taxi.trips_small_inferred 表中的数据开始。然后，应用 WHERE 子句以根据计算值过滤行。过滤后的数据为聚合做准备，并计算分位数。最后，结果被排序和输出。
在这里，我们可以注意到没有使用主键，这很有意义，因为我们在创建表时没有定义任何主键。因此，ClickHouse 正在对表进行完全扫描。
解释管道
EXPLAIN Pipeline 显示查询的具体执行策略。在那里，您可以查看 ClickHouse 如何实际执行我们之前查看的通用查询计划。
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 部署中，任何给定时间可能都有大量查询正在执行。
如果您知道哪个用户、数据库或表存在问题，则可以使用 system.query_logs 中的字段 user、tables 或 databases 来缩小搜索范围。
一旦您确定了要优化的查询，就可以开始对其进行处理。开发人员在此阶段常犯的一个错误是同时更改多个内容，运行临时实验，通常会得到混合结果，但更重要的是，无法很好地理解是什么使查询更快。
查询优化需要结构。我不是指高级基准测试，而是建立一个简单的流程来了解您的更改如何影响查询性能，可以大有帮助。
首先从查询日志中识别您的慢查询，然后隔离潜在的改进进行调查。测试查询时，请确保禁用文件系统缓存。
ClickHouse 利用 缓存 在不同阶段加速查询性能。这对于查询性能来说是好的，但在故障排除期间，它可能会隐藏潜在的 I/O 瓶颈或较差的表模式。因此，我建议在测试期间关闭文件系统缓存。确保在生产设置中启用它。
一旦您确定了潜在的优化，建议逐一实现它们，以便更好地跟踪它们如何影响性能。下图描述了通用方法。
最后，请注意异常值；通常，查询可能会运行缓慢，要么是因为用户尝试了临时的高成本查询，要么是因为系统正处于压力之下。您可以按 normalized_query_hash 字段分组，以识别定期执行的成本高昂的查询。这些可能是您想要调查的查询。
基本优化
现在我们有了测试框架，就可以开始优化了。
最佳的起点是查看数据的存储方式。与任何数据库一样，我们读取的数据越少，查询执行的速度就越快。
根据您导入数据的方式，您可能利用了 ClickHouse 功能 根据导入的数据推断表模式。虽然这对于入门非常实用，但如果您想优化查询性能，则需要查看数据模式以最好地适应您的用例。
如最佳实践文档中所述，尽可能避免使用可为空的列。虽然它们使数据摄取机制更灵活，但它们会降低性能，因为每次都需要处理额外的列。
运行一个 SQL 查询来统计具有 NULL 值的行数，可以轻松地发现您的表中实际需要可为空值的列。
-- 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。其余字段不应使用Nullable列。
低基数
对字符串进行的一个简单优化是充分利用 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
我们再次运行查询，使用新表来检查改进情况。
| 名称 | 运行 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 |
我们注意到查询时间和内存使用方面都有一些改进。由于数据模式的优化，我们减少了代表我们数据的总数据量，从而提高了内存消耗并减少了处理时间。
让我们检查表的大小，看看差异。
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 的第一行的指针。
在 ClickHouse 中，granule 是查询执行期间读取数据的最小单位。它们包含最多固定数量的行，由 index_granularity 确定，默认值为 8192 行。Granule 连续存储并按主键排序。
选择一组好的主键对于性能很重要，并且实际上通常将相同的数据存储在不同的表中，并使用不同的主键集来加速特定查询集。
ClickHouse 支持的其他选项，例如 Projection 或 Materialized view，允许您在相同的数据上使用不同的主键集。本博客系列的第二部分将更详细地介绍这一点。
选择主键
选择正确的主键集是一个复杂的话题，可能需要权衡和实验才能找到最佳组合。
现在，我们将遵循以下简单实践：
- 使用在大多数查询中用于过滤的字段
- 首先选择基数较低的列
- 考虑在主键中使用基于时间的分量，因为在时间戳数据集上按时间过滤非常常见。
在我们的例子中，我们将尝试以下主键：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
然后我们重新运行我们的查询。我们将从三个实验中编译结果，以查看耗时、处理的行数和内存消耗方面的改进。
| 查询 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 亿 | 27699 万 |
| 峰值内存 | 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 如何执行您的查询。
随着您对 ClickHouse 的具体性越来越熟悉，我建议您阅读关于分区键和数据跳过索引的信息，以了解更多您可以用来加速查询的高级技术。
