ClickHouse 查询优化实用指南：第一部分

本文字数：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