【场景实战】 ClickHouse 查询缓慢的问题分析排查

必需] 从开始：找到慢查询，记录其read_rowsread_bytes。[必需] 检查 SQL 的 WHERE 条件：是否有效利用了主键？是否避免了函数和低效操作（LIKE, IN）？[推荐] 使用EXPLAIN查看执行计划和可以帮助理解查询如何利用主键和分区。[检查] 表结构：主键/排序键设置是否合理？分区数量是否健康？[监控] 系统资源：在查询运行时，快速查看 CPU、内存、磁盘 I/O 的

lanbing

1091人浏览 · 2025-09-16 20:19:10

lanbing · 2025-09-16 20:19:10 发布

今天我们遵循一个从外到内、由浅入深的系统性排查流程来分析解决一个“ClickHouse 查询缓慢”的实战问题。下面我将为你梳理出一套详细的排查、定位和解决方法。

一、核心排查思路：从 SQL 到硬件，层层递进

定位问题查询：首先找到是哪个（哪些）查询慢。
分析查询本身：检查 SQL 写法、数据访问模式是否合理。
检查表引擎与结构：表的设计是性能的基石。
审视系统资源：CPU、内存、磁盘 I/O 是否成为瓶颈。
考察集群与副本：分布式查询和复制是否正常工作。

二、详细定位过程

第一步：定位问题查询

识别慢查询：
- 查询 system.query_log 表。这是最强大的工具，它记录了所有查询的详细信息。
```
-- 查看最近10条执行时间超过2秒的查询
SELECT
    query_id,
    query,
    event_time,
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage,
    exception
FROM system.query_log
WHERE type = 'QueryFinish' -- 只查完成的分析
AND query_duration_ms > 2000
ORDER BY event_time DESC
LIMIT 10;
```
- 关键字段：
  - query_duration_ms：查询执行时间。
  - read_rows/read_bytes：读取的数据量和字节数。巨大的数值通常意味着全表扫描。
  - memory_usage：内存使用情况，排查是否因内存不足导致溢出到磁盘。
  - exception：是否有错误信息。

实时监控：

使用 clickhouse-client 时，可以开启性能分析：

SET send_logs_level = 'trace';
-- 然后运行你的慢查询，客户端会输出详细的执行日志，包括每个步骤的时间。

第二步：分析查询语句 (SQL)

很多性能问题源于 SQL 写法。

避免全表扫描：
- 确认 WHERE 子句是否有效使用了主键/分区键。ClickHouse 的主键索引是稀疏的，最适合范围查询。检查你的查询条件是否匹配主键的前缀。
- 示例：表的主键是 (Date, UserID)。
  - WHERE Date = today() AND UserID = 123 (高效)
  - WHERE UserID = 123 (低效，因为跳过了 Date 这个主键第一列)
谨慎使用 JOIN：
- ClickHouse 的 JOIN 性能通常不如传统OLTP数据库。优先考虑 denormalization（反范式化），即用大宽表代替多表关联。
- 如果必须用 JOIN：
  - 确保右表是小的维度表。
  - 使用 JOIN 子句而不是 WHERE ... IN (SELECT ...)（旧版本）。
  - 考虑使用字典来代替小表 JOIN。
慎用非确定性函数和 LIKE：
- 函数如 now()、rand() 或在 WHERE 条件中对字段使用函数（如 WHERE toDate(timestamp) = '2023-01-01'）会阻止索引使用。应改为 WHERE timestamp >= '2023-01-01 00:00:00' AND timestamp < '2023-01-02 00:00:00'。
- LIKE '%pattern% 这种前缀模糊匹配无法使用索引。
检查 GROUP BY 和 ORDER BY：
- 这些操作非常消耗内存和CPU。确保你确实需要所有细节数据，是否可以增加聚合粒度或使用近似计算（如 uniqCombined 代替 uniqExact）。
- 优化 GROUP BY 的顺序，将高基数列放在后面可能有助于降低内存消耗。

第三步：检查表引擎与结构

表结构设计是 ClickHouse 性能的核心。

分区键 (PARTITION BY)：
- 分区用于数据管理（删除旧数据），而不是为了查询性能。
- 分区粒度不宜过细（例如不要按分钟分区），否则会导致分区数量爆炸，大量小文件，严重影响性能。
- 理想的查询应该只触及1到2个分区。检查 system.parts 表，看分区数量是否过多。
主键/排序键 (ORDER BY)：
- 这是影响查询性能最重要的因素。数据在磁盘上按排序键存储。
- 将查询过滤中最常用、高基数的列放在前面。
- 使用 EXPLAIN PIPELINE 来查看查询是否有效地利用了主键进行数据裁剪。
```
EXPLAIN PIPELINE
SELECT * FROM your_table WHERE your_indexed_column = 'value';
```
跳数索引 (Data Skipping Indexes)：
- 如果无法将某列加入主键，但又需要频繁过滤，可以为它创建跳数索引（如 minmax, set, bloom_filter）。
- 注意：跳数索引不是银弹，它会增加磁盘开销和写入时间，需要测试其有效性。
表属性：
- TTL：确认 TTL 设置是否合理，避免后台合并操作过于频繁。

第四步：审视系统资源

如果查询和表结构都OK，那么看硬件资源。

CPU：
- 使用 top 或 htop 命令查看 ClickHouse 进程的 CPU 使用率。
- 查询时CPU是否跑满？如果是，可能是计算密集型查询（复杂聚合、排序），考虑升级CPU或优化查询。
- 使用 system.metrics 表查看 Query 线程数是否饱和。
内存 (Memory)：
- ClickHouse 会尽力使用内存。关注是否因内存不足导致 “溢出到磁盘”。
- 检查 system.query_log 中的 memory_usage 和 ProfileEvents 中的 ExternalSortExternalPartsMerge 等指标。大量的磁盘溢出会极大降低性能。
- 调整 max_memory_usage 等设置，但更要优化查询减少内存消耗。
磁盘 I/O：
- 使用 iostat -x 1 命令监控磁盘使用情况。
- 关注 %util (利用率) 和 await (响应时间)。如果持续很高，说明磁盘是瓶颈。
- 原因：
  - 数据未缓存，需要从磁盘读取。
  - 查询需要读取大量数据（全表扫描）。
  - 后台的 Merge 进程正在合并数据块，与查询争抢I/O资源。
- 解决方案：使用更快的存储（如 NVMe SSD）、增加内存（让更多数据被缓存）、优化查询减少数据扫描量。

第五步：考察集群与副本（如果适用）

分布式查询：
- 对于分布式表（Distributed table），查询会发往所有分片。
- 检查是否存在数据倾斜？某个分片的数据量远大于其他分片，导致它成为慢节点。
- 使用 explain estimates = 1 或查看 query_log 的 read_rows 是否在所有分片上均匀。
复制表：
- 检查副本同步是否延迟。延迟的副本可能提供过时的数据，或者导致查询需要等待。

三、总结与行动清单

当你遇到慢查询时，可以按以下清单快速排查：

[必需] 从 system.query_log 开始：找到慢查询，记录其 read_rows, read_bytes, memory_usage。
[必需] 检查 SQL 的 WHERE 条件：是否有效利用了主键？是否避免了函数和低效操作（LIKE, IN）？
[推荐] 使用 EXPLAIN 查看执行计划：EXPLAIN PIPELINE 和 EXPLAIN ESTIMATES 可以帮助理解查询如何利用主键和分区。
[检查] 表结构：主键/排序键设置是否合理？分区数量是否健康？
[监控] 系统资源：在查询运行时，快速查看 CPU、内存、磁盘 I/O 的使用情况，定位硬件瓶颈。
[高级] 考虑索引：是否为频繁过滤的非主键列创建了合适的跳数索引？