定位查询瓶颈

每次只修改查询的一部分，并将结果与稳定的基线进行比较，可使查询优化更容易。本指南介绍如何逐步简化查询，并通过比较各次运行结果的差异，识别对其耗时影响最大的操作。随后，您可以在选择优化方案前验证疑似瓶颈。

首先，确定一个要调查的反复出现的慢查询模式。如果尚未确定，请参阅诊断慢查询，其中详细说明了该流程。

若要按原文运行本指南中的示例，如果尚未创建并加载 nyc_taxi.trips_small_inferred 表，请先创建并加载该表：

CREATE DATABASE IF NOT EXISTS nyc_taxi;
USE nyc_taxi;

CREATE TABLE nyc_taxi.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',
    NOSIGN,
    Parquet
);

INSERT INTO nyc_taxi.trips_small_inferred
SELECT *
FROM s3(
    'https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/clickhouse-academy/nyc_taxi_2009-2010.parquet',
    NOSIGN,
    Parquet
);

示例表使用 ORDER BY ()，因此其日期过滤器无法利用排序键在读取期间跳过数据。请使用该示例练习比较方法，而不要将其视为性能目标。

逐步简化查询，可以比较移除某个处理阶段前后的耗时。这些差异有助于判断是否需要进一步分析扫描和筛选、分组、聚合计算，或排序和输出格式化等后续操作：

1. 运行原始查询，确定基线测量值。
2. 保留 GROUP BY，将查询中的聚合计算替换为 count，并移除排序和输出格式化等后续操作。
3. 移除分组并运行不分组的 count，以估算扫描、筛选和任何 join 所保留的工作量。

这些步骤可直接用于常规的分组聚合查询。对于更复杂的查询，请一次对一个 SELECT 块应用相同原则：保留等效的数据源和过滤器，每次移除一项操作，并在每次更改后验证执行计划。

采用以下做法，确保测量结果具有可比性：

• 保持 FROM、JOIN、PREWHERE 和 WHERE 子句不变，确保每次比较使用相同的数据和时间范围。
• 在相近的系统负载下，多次运行每个版本的查询。
• 保持缓存条件一致。要么在记录测量结果前运行每个版本的查询，要么禁用下列缓存。不要比较已缓存和未缓存的运行结果。
• 记录具有代表性的耗时，例如在完成预热运行后，取多次重复运行结果的中位数，而不要采用最快或最慢的结果。
• 每次只更改一个变量，以便将性能差异归因于某项具体更改。

对于未缓存的诊断比较，请禁用 ClickHouse 针对远程数据的文件系统缓存、查询缓存和查询条件缓存。同时也要禁用隐式投影，确保运行 C 中的 count 不会使用优化后的执行计划，从而绕过你要比较的扫描操作。

SET enable_filesystem_cache = 0;
SET use_query_cache = 0;
SET use_query_condition_cache = 0;
SET optimize_use_implicit_projections = 0;

此工作流将受控执行查询与查询日志中的测量数据结合使用：

按以下方式收集每次运行的测量数据：

1. 为每次运行指定唯一的查询 ID，或记录查询界面生成的 ID。例如，将重复运行标识为 bottleneck-a-1、bottleneck-a-2 和 bottleneck-a-3。使用 clickhouse-client 时，请在执行查询时传入 --query_id your-query-id。

2. 在相同条件下多次执行每个对比查询。将预热运行与用于测量的运行分开。

3. 在查找最近完成的查询前刷新查询日志：

   SYSTEM FLUSH LOGS;

   如果无法运行 SYSTEM FLUSH LOGS，请等待查询日志自动刷新，然后重试查找。如果记录始终未出现，请确认查询日志已启用、你有权限读取 system.query_log，并且查询的是执行该查询的节点。

4. 查找每个查询 ID 对应的完成记录。对于已完成的查询，system.query_log 会同时记录 QueryStart 和 QueryFinish 事件。筛选 QueryFinish，其中包含最终耗时、读取的行数和字节数，以及峰值内存占用：

   SELECT
       query_id,
       query_duration_ms,
       read_rows,
       read_bytes,
       memory_usage
   FROM system.query_log
   WHERE type = 'QueryFinish'
     AND query_id = 'your-query-id'
   ORDER BY event_time_microseconds DESC
   LIMIT 1;

5. 对于查询的每个版本，使用测量运行的耗时中位数。记录最接近该中位数的那次运行的 read_rows、read_bytes 和峰值内存占用，以确保测量数据对应实际运行。

使用下表整理具有代表性的测量数据。有关字段和配置的更多信息，请参阅 system.query_log。

Run,Query version,Representative duration,read_rows,read_bytes,Peak memory
A,Original query,,,,
B,Grouped count,,,,
C,Ungrouped count,,,,

为演示这三种比较，示例使用了分组的日期范围工作负载。您也可以将此方法应用于其他查询，无需遵循该完整示例。如果查询不包含 GROUP BY，请按下文说明跳过运行 B。

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;

SELECT
    payment_type,
    count() AS trip_count
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01'
GROUP BY payment_type;

SELECT count()
FROM nyc_taxi.trips_small_inferred
WHERE pickup_datetime >= '2009-01-01'
  AND pickup_datetime < '2009-04-01';

应比较多次运行中具有代表性的耗时，而非将两次单独的计时结果相减。显著且持续存在的差异表明下一步应从何处着手调查：

将运行 C 的 read_rows 与其 count 返回的值进行比较。例如，如果 read_rows 为 1 亿，而 count 返回 100 万，则 ClickHouse 每统计一行，大约扫描了 100 行源数据。这表明过滤器排除了从表中读取的大多数行，但无法说明具体原因。此比率适用于简单的单表扫描。对于涉及多个数据源或投影的查询，应结合执行计划来解读 read_rows。

对于 ClickHouse 25.9 及更高版本，在检查索引使用情况之前，请禁用查询条件缓存以及跳过索引的动态应用：

SET use_query_condition_cache = 0;
SET use_skip_indexes_on_data_read = 0;

然后使用 EXPLAIN indexes = 1 查看 ClickHouse 使用了哪些索引，以及每个索引跳过了多少 parts 和粒度。如果 ClickHouse 选中的粒度多于预期，请检查过滤器是否与表的排序键匹配，以及是否可通过分区剪枝或数据跳过索引跳过更多粒度。如果执行计划中没有 Indexes 部分，说明 EXPLAIN 未显示该查询的索引剪枝信息。相比之下，全表分析查询通常会读取表中的大部分数据。

比较结果指向可能的瓶颈后，请先进行验证，再修改 schema 或查询。应根据疑似的延迟来源收集相应证据：

• 对于扫描或筛选瓶颈，请结合上述设置使用 EXPLAIN indexes = 1，查看 ClickHouse 使用了哪些索引，以及每个索引排除了多少 parts 和粒度。检查执行计划是否使用了隐式投影，而不是预期的扫描。
• 对于分组或聚合瓶颈，请检查相关的查询 profile events 和峰值内存占用。
• 如果运行 C 仍然较慢且包含 joins，请将其与每次移除一个 join 的诊断查询进行比较。耗时大幅降低表明被移除的 join 会带来大量工作。由于移除 join 会改变查询含义，因此此比较仅用于定位耗时；行数变化应单独解读。
• 对于运行 C 中保留的其他操作所造成的瓶颈，请检查执行计划和相关的查询 profile events。

有关 EXPLAIN 返回的索引信息，请参阅慢查询诊断指南。进行一项有针对性的更改后，在相同条件下重复运行 A、B 和 C。确认该更改减少了预期的工作量，且未将瓶颈转移到其他位置。

继续阅读优化方法，针对疑似性能瓶颈采取一项或多项相应的优化措施。