上一篇【第38篇】ClickHouse SELECT高级用法——表达式函数与DISTINCT/UNION ALL
下一篇ClickHouse聚合函数体系详解（上）——基础聚合与组合聚合

摘要

本文是《Clickhouse从入门到精通》系列博客的第39篇文章，聚焦ClickHouse的SQL执行计划查看与查询优化基础。文章从EXPLAIN命令体系入手，详细讲解EXPLAIN PLAN、EXPLAIN TREE、EXPLAIN PIPELINE、EXPLAIN ESTIMATE和EXPLAIN SYNTAX等五种执行计划查看方式的语法与输出解读；随后系统阐述分区裁剪、索引选择、列裁剪、PREWHERE优化和GROUP BY优化等基础查询优化策略；最后通过实战案例展示如何分析慢查询执行计划并定位性能瓶颈。

关键词：EXPLAIN、执行计划、查询优化、PIPELINE、分区裁剪

1. 引言

在上一篇文章中，我们深入探讨了ClickHouse中SELECT语句的高级用法，包括列表达式、DISTINCT去重、UNION ALL合并查询和子查询等技术。掌握了这些查询写法后，下一步就是确保查询能高效执行。在实际生产环境中，面对TB甚至PB级别的数据量，一个不经优化的查询可能从毫秒级退化到分钟甚至小时级别。

ClickHouse提供了丰富的EXPLAIN命令来查看SQL执行计划，帮助我们理解查询的内部执行机制，从而精准定位性能瓶颈并制定优化策略。本文将从EXPLAIN命令体系入手，逐步展开查询优化的基础方法论。

2. EXPLAIN命令概述

ClickHouse从21.x版本开始引入了EXPLAIN命令族，用于查看查询的执行计划。通过不同类型的EXPLAIN命令，我们可以从不同角度审视查询的执行方式。

EXPLAIN命令类型总览

命令 用途 适用场景
EXPLAIN PLAN 查看逻辑执行计划 理解查询逻辑步骤
EXPLAIN TREE 查看树形执行计划 更直观地理解执行层次
EXPLAIN PIPELINE 查看查询管道 理解数据流和并行度
EXPLAIN ESTIMATE 查看估算信息 预估数据量和I/O
EXPLAIN SYNTAX 查看语法解析结果 理解SQL实际解析形态

基本语法格式：

EXPLAIN [PLAN | TREE | PIPELINE | ESTIMATE | SYNTAX] [SETTINGS ...]
SELECT ...;

3. EXPLAIN PLAN：传统执行计划输出

3.1 基本语法

EXPLAIN PLAN SELECT
    region,
    sum(amount) AS total
FROM orders
WHERE date = '2024-01-15'
GROUP BY region
ORDER BY total DESC
LIMIT 10;

3.2 输出格式解读

EXPLAIN PLAN输出的是逻辑执行计划的层次结构，每个节点代表一个执行步骤：

┌─explain─────────────────────────────────────────┐
│ Limit                                          │
│   Sorting                                      │
│     Aggregating                                │
│       Expression                               │
│         Filter                                 │
│           Expression                           │
│             ReadFromMergeTree                  │
└────────────────────────────────────────────────┘

从输出中可以看到执行步骤从底向上：先读取数据（ReadFromMergeTree），然后经过表达式计算（Expression）、过滤（Filter）、聚合（Aggregating）、排序（Sorting），最后取前N行（Limit）。

3.3 关键节点类型解读

节点类型 含义 常见场景
ReadFromMergeTree 从MergeTree表读取数据 所有查询的起始节点
ReadFromStorage 从其他存储引擎读取 非MergeTree表
Filter WHERE条件过滤 有WHERE子句
Expression 表达式计算/列投影 别名计算、类型转换
Aggregating 聚合计算 GROUP BY
AggregatingPartitions 分区级聚合 分布式聚合
Sorting 排序 ORDER BY
Limit 结果截断 LIMIT子句
Join 连接操作 JOIN子句
Distinct 去重 DISTINCT
Union 合并 UNION ALL
Subquery 子查询 FROM中的子查询

EXPLAIN PLAN的扩展选项

-- 查看更详细的信息：header、index、actions
EXPLAIN PLAN header = 1, indexes = 1, actions = 1
SELECT region, sum(amount) AS total
FROM orders
WHERE date = '2024-01-15'
GROUP BY region;
  • header = 1：显示每个节点输出的列信息
  • indexes = 1：显示索引使用情况
  • actions = 1：显示每个节点的详细操作

4. EXPLAIN TREE：树形执行计划

4.1 更直观的树状结构

EXPLAIN TREE以更详细的树形结构展示执行计划，每个节点包含更多上下文信息：

EXPLAIN TREE SELECT
    region,
    sum(amount) AS total
FROM orders
WHERE date = '2024-01-15' AND amount > 100
GROUP BY region
ORDER BY total DESC
LIMIT 10;

输出示例：

┌─explain──────────────────────────────────────────────┐
│ Limit (len 10)                                       │
│ └── Sorting (ORDER BY total DESC)                    │
│     └── Aggregating                                  │
│         └── Expression                               │
│             └── Filter (amount > 100)                │
│                 └── Expression                       │
│                     └── ReadFromMergeTree (default.orders) │
│                     Parts: 8                          │
│                     Granules: 120                     │
└──────────────────────────────────────────────────────┘

4.2 各节点类型深度解读

ReadFromMergeTree节点

这是最关键的节点之一，显示数据读取的详细信息：

EXPLAIN TREE SELECT count() FROM hits WHERE date = '2024-01-15';

输出中的关键信息包括：

  • Parts：需要扫描的数据分区数
  • Granules：需要读取的Mark数（Granule是ClickHouse的最小读取单位）
  • Partitions：涉及的分区信息

Aggregating节点

聚合节点显示聚合的方式（单阶段聚合或两阶段聚合）：

EXPLAIN TREE SELECT region, count() FROM orders GROUP BY region;

在分布式查询中，聚合通常分为两阶段：先在各分片本地聚合，再在协调节点合并聚合结果。EXPLAIN TREE可以清晰展示这一过程。

Join节点

Join节点显示连接类型和连接条件：

EXPLAIN TREE SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.user_id;

输出会显示Join的类型（HASH JOIN、MERGE JOIN等）、连接键和右表构建方式。

5. EXPLAIN PIPELINE：查询管道

5.1 QueryPipeline的可视化

EXPLAIN PIPELINE是ClickHouse最具特色的执行计划查看方式。ClickHouse的查询执行引擎基于Pipeline模型，数据在多个Processor之间以流式方式传递。EXPLAIN PIPELINE将这一过程可视化：

EXPLAIN PIPELINE SELECT
    region,
    sum(amount) AS total
FROM orders
WHERE date = '2024-01-15'
GROUP BY region;

输出示例：

┌─explain──────────────────────────────────────────────────┐
│ (MergeTreeThread)                                        │
│   (ExpressionTransform)                                  │
│   (FilterTransform)                                      │
│ (MergingAggregated)                                      │
│   (AggregatingTransform)                                 │
│     (ExpressionTransform)                                │
│       (MergeTreeThread)                                  │
│         (ExpressionTransform)                            │
│         (FilterTransform)                                │
│ (SortingTransform)                                        │
│   (MergingAggregated)                                     │
│     (AggregatingTransform)                               │
│ (LimitTransform)                                          │
└──────────────────────────────────────────────────────────┘

5.2 理解数据流向

Pipeline展示了数据的实际流动路径。每个Processor是一个数据处理单元，数据从数据源（如MergeTreeThread）经过一系列Transform流向最终输出。

关键概念

  • Processor：数据处理单元，包括Source（数据源）、Transform（数据变换）和Sink（数据输出）
  • Port：Processor之间的连接点，数据通过Port在Processor之间传递
  • Thread：执行线程，Pipeline中的Processor被分配到不同线程并行执行
-- 查看Pipeline的详细图
EXPLAIN PIPELINE graph = 1 SELECT count() FROM hits;

当graph = 1时，ClickHouse会输出Pipeline的DOT格式图描述，可以用于生成可视化图形。

5.3 并行度分析

EXPLAIN PIPELINE可以直观地展示查询的并行度：

EXPLAIN PIPELINE SELECT count() FROM large_table;

输出中的数字表示各阶段的并行线程数：

┌─explain──────────────────────────────┐
│ (MergeTreeThread × 8)               │  ← 8个线程并行读取
│ (ExpressionTransform × 8)           │  ← 8个线程并行转换
│ (AggregatingTransform × 8)          │  ← 8个线程并行聚合
│ (MergingAggregated × 1)             │  ← 1个线程合并结果
│ (ExpressionTransform × 1)           │  ← 1个线程输出
└──────────────────────────────────────┘

理解并行度有助于判断查询是否充分利用了CPU资源。如果某个阶段的并行度很低（例如最终合并阶段只有1个线程），这可能成为性能瓶颈。

6. EXPLAIN ESTIMATE：估算信息

6.1 行数和字节数估算

EXPLAIN ESTIMATE提供查询各阶段的行数和字节数估算：

EXPLAIN ESTIMATE SELECT
    region,
    sum(amount) AS total
FROM orders
WHERE date = '2024-01-15' AND status = 'completed'
GROUP BY region;

输出示例：

┌─database─┬─table──┬─parts─┬─rows─────┬─bytes───────┐
│ default  │ orders │ 4     │ 1500000  │ 120000000   │
└──────────┴────────┴───────┴──────────┴─────────────┘

各列含义：

列名 含义
database 数据库名
table 表名
parts 涉及的数据分区数
rows 预估读取的行数
bytes 预估读取的字节数

6.2 估算信息的用途

EXPLAIN ESTIMATE的估算信息对优化决策具有重要参考价值：

判断分区裁剪效果

-- 有分区裁剪
EXPLAIN ESTIMATE SELECT * FROM orders WHERE date = '2024-01-15';
-- parts: 1, rows: 500000

-- 无分区裁剪
EXPLAIN ESTIMATE SELECT * FROM orders WHERE amount > 1000;
-- parts: 90, rows: 45000000

通过对比parts和rows的数量，可以直观判断WHERE条件是否有效触发了分区裁剪。

预估查询资源消耗

rows和bytes的估算值可以帮助预估查询的资源消耗，避免在生产环境执行资源消耗过大的查询。

7. EXPLAIN SYNTAX：语法检查

7.1 查看SQL的实际解析结果

EXPLAIN SYNTAX展示ClickHouse对SQL的实际解析和重写结果：

EXPLAIN SYNTAX SELECT
    user_id AS uid,
    count() AS cnt
FROM events
WHERE uid > 1000
GROUP BY uid
ORDER BY cnt DESC;

输出示例：

SELECT
    user_id AS uid,
    count() AS cnt
FROM events
WHERE user_id > 1000   -- 注意：uid被展开为原始列名user_id
GROUP BY user_id        -- 同样展开为原始列名
ORDER BY cnt DESC;

7.2 别名展开与类型推导

EXPLAIN SYNTAX能帮助我们看到ClickHouse内部对SQL做了哪些优化和重写：

-- 原始SQL
EXPLAIN SYNTAX SELECT
    toDate(event_time) AS d,
    count() AS cnt
FROM events
WHERE d = '2024-01-15'
GROUP BY d;

-- 实际解析结果（别名被展开）
SELECT
    toDate(event_time) AS d,
    count() AS cnt
FROM events
WHERE toDate(event_time) = '2024-01-15'  -- d被展开
GROUP BY toDate(event_time);              -- d被展开

注意：当WHERE条件中的别名被展开后，分区裁剪可能失效！因为ClickHouse的分区裁剪依赖于对分区列的直接比较，如果分区列被函数包裹，则无法利用分区索引。

-- 好的写法：直接使用分区列
WHERE date = '2024-01-15'

-- 不好的写法：对分区列使用函数
WHERE toDate(event_time) = '2024-01-15'

7.3 优化器重写

ClickHouse的优化器会自动重写某些SQL模式：

-- 原始SQL
EXPLAIN SYNTAX SELECT * FROM (
    SELECT user_id, count() AS cnt FROM events GROUP BY user_id
) WHERE cnt > 10;

-- 可能的重写结果：谓词下推
SELECT user_id, count() AS cnt
FROM events
GROUP BY user_id
HAVING count() > 10;

8. 基础查询优化策略

8.1 分区裁剪优化

分区裁剪是ClickHouse最重要的优化手段之一。当查询的WHERE条件包含分区键时，ClickHouse可以跳过不相关的分区，大幅减少I/O。

原理

MergeTree引擎按PARTITION BY定义的分区键组织数据，每个分区是独立的目录。查询时，ClickHouse根据WHERE条件判断哪些分区需要扫描，跳过不满足条件的分区。

-- 按日期分区
CREATE TABLE orders (
    date Date,
    order_id UInt64,
    amount Decimal(18,2),
    region String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, order_id);

-- 有效分区裁剪
SELECT * FROM orders WHERE date = '2024-01-15';
-- 只扫描202401分区

-- 有效分区裁剪：范围查询
SELECT * FROM orders WHERE date BETWEEN '2024-01-01' AND '2024-01-31';
-- 只扫描202401分区

-- 无效分区裁剪
SELECT * FROM orders WHERE amount > 1000;
-- 扫描所有分区

分区裁剪验证

-- 使用EXPLAIN ESTIMATE验证分区裁剪效果
EXPLAIN ESTIMATE SELECT * FROM orders WHERE date = '2024-01-15';
-- parts: 1（只有一个分区被扫描）

EXPLAIN ESTIMATE SELECT * FROM orders WHERE amount > 1000;
-- parts: 24（所有分区都被扫描）

8.2 索引选择优化

ClickHouse的MergeTree引擎使用主键索引（PRIMARY KEY / ORDER BY）来加速数据查找。

主键索引原理

主键索引基于排序键构建，数据按主键排序存储。每个Mark（索引标记）对应一个Granule（8192行）的数据范围。

-- 合理的主键设计
CREATE TABLE user_events (
    date Date,
    user_id UInt64,
    event_type String,
    event_time DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (user_id, date);  -- 以user_id为首列，支持按用户查询

-- 不合理的主键设计
CREATE TABLE user_events_bad (
    date Date,
    user_id UInt64,
    event_type String,
    event_time DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (date, user_id);  -- 以date为首列，按用户查询无法利用索引

索引选择原则

原则 说明
高基数列优先 将区分度高的列放在主键前面
查询频率优先 将WHERE中频繁使用的列放在主键前面
范围查询列靠后 等值查询列在前，范围查询列在后
主键长度控制 主键列不宜过多，建议3-5列 
-- 查询模式1：按user_id精确查找
-- 最佳ORDER BY: (user_id, date)

-- 查询模式2：按date范围+user_id精确查找
-- 最佳ORDER BY: (date, user_id)

-- 查询模式3：按region+user_id查找
-- 最佳ORDER BY: (region, user_id, date)

8.3 列裁剪优化

列式存储的核心优势在于只读取需要的列。避免SELECT *是最基本的优化手段。

对比效果

-- 100列的宽表
-- 查询1：SELECT * — 读取所有100列
-- 查询2：SELECT col1, col2, col3 — 只读取3列
-- I/O差距：约33倍

-- 验证列裁剪效果
EXPLAIN ESTIMATE SELECT * FROM wide_table WHERE date = today();
-- bytes: 大

EXPLAIN ESTIMATE SELECT col1, col2 FROM wide_table WHERE date = today();
-- bytes: 小

8.4 PREWHERE优化

PREWHERE是ClickHouse特有的优化机制，专门用于MergeTree引擎。它允许在读取完整列数据之前，先读取过滤列的稀疏索引来跳过不满足条件的数据块。

PREWHERE vs WHERE

-- WHERE：先读取所有需要的列，再过滤
SELECT user_id, event_type, amount
FROM events
WHERE date = '2024-01-15' AND amount > 1000;

-- PREWHERE：先读取amount列判断是否满足条件，再决定是否读取其他列
SELECT user_id, event_type, amount
FROM events
PREWHERE amount > 1000
WHERE date = '2024-01-15';

使用场景

PREWHERE适合过滤性强、列体积小的条件：

条件特征 适合PREWHERE 适合WHERE
过滤性强（过滤掉90%+数据） 是 否
列体积小 是 否
过滤性弱（只过滤少量数据） 否 是
列体积大 否 是 
-- 自动PREWHERE优化
-- ClickHouse在大多数情况下会自动将WHERE条件提升为PREWHERE
-- 但对于复杂查询，手动指定PREWHERE可能更优

-- 手动PREWHERE示例
SELECT
    user_id,
    event_time,
    url,
    duration
FROM web_logs
PREWHERE status_code = 200      -- 先通过稀疏索引过滤，status_code体积小
WHERE duration > 5000           -- 再在读取完整数据后过滤
  AND date = '2024-01-15';

重要提示：ClickHouse从较新版本开始，已经能自动将WHERE条件优化为PREWHERE，手动指定PREWHERE的需求已大幅减少。但在某些复杂查询中，手动控制PREWHERE仍然有价值。

8.5 GROUP BY优化

GROUP BY是分析查询中最常用的操作之一，其优化对整体查询性能影响显著。

优化策略1：减少GROUP BY的列数

-- 不必要的列参与GROUP BY
SELECT region, city, count()
FROM orders
GROUP BY region, city;

-- 如果只需要按region统计
SELECT region, count()
FROM orders
GROUP BY region;  -- 减少GROUP BY列数，降低哈希表复杂度

优化策略2：利用ORDER BY加速GROUP BY

当GROUP BY的列与表的ORDER BY（排序键）一致时，ClickHouse可以利用数据的有序性，使用更高效的聚合算法：

-- 表的ORDER BY是(user_id, date)
-- 当GROUP BY user_id时，可以部分利用有序性
SELECT user_id, count()
FROM events
GROUP BY user_id;

优化策略3：使用聚合组合器减少中间结果

-- 使用-If组合器替代CASE WHEN
-- 不推荐
SELECT
    count(CASE WHEN status = 'success' THEN 1 END) AS success_count,
    count(CASE WHEN status = 'error' THEN 1 END) AS error_count
FROM events
GROUP BY region;

-- 推荐
SELECT
    countIf(status = 'success') AS success_count,
    countIf(status = 'error') AS error_count
FROM events
GROUP BY region;

优化策略4：两阶段聚合

ClickHouse在分布式查询中自动执行两阶段聚合：先在各分片本地聚合，再在协调节点合并。但单机查询也可以利用类似思路：

-- 大表聚合：利用子查询实现多阶段聚合
SELECT
    region,
    sum(local_count) AS total_count,
    sum(local_amount) AS total_amount
FROM (
    SELECT
        region,
        date,
        count() AS local_count,
        sum(amount) AS local_amount
    FROM orders
    WHERE date >= '2024-01-01' AND date <= '2024-03-31'
    GROUP BY region, date  -- 第一阶段：细粒度聚合
) AS daily_stats
GROUP BY region;  -- 第二阶段：粗粒度聚合

9. 执行计划分析实战：识别慢查询的瓶颈

9.1 案例背景

某业务反馈以下查询执行缓慢（耗时约30秒）：

SELECT
    user_id,
    count() AS event_count,
    sum(duration) AS total_duration,
    uniq(url) AS url_count
FROM web_logs
WHERE toDate(event_time) = '2024-01-15'
  AND status_code = 200
GROUP BY user_id
HAVING event_count > 100
ORDER BY total_duration DESC
LIMIT 50;

9.2 第一步：使用EXPLAIN ESTIMATE评估扫描量

EXPLAIN ESTIMATE SELECT
    user_id,
    count() AS event_count,
    sum(duration) AS total_duration,
    uniq(url) AS url_count
FROM web_logs
WHERE toDate(event_time) = '2024-01-15'
  AND status_code = 200
GROUP BY user_id
HAVING event_count > 100
ORDER BY total_duration DESC
LIMIT 50;

结果发现parts = 365，说明扫描了所有分区——分区裁剪失效了！

9.3 第二步：使用EXPLAIN SYNTAX检查重写

EXPLAIN SYNTAX SELECT
    user_id,
    count() AS event_count,
    sum(duration) AS total_duration,
    uniq(url) AS url_count
FROM web_logs
WHERE toDate(event_time) = '2024-01-15'
  AND status_code = 200
GROUP BY user_id
HAVING event_count > 100
ORDER BY total_duration DESC
LIMIT 50;

发现toDate(event_time) = '2024-01-15'未被优化——因为分区键是toYYYYMM(event_time)，而查询使用的是toDate(event_time)，两者不匹配导致分区裁剪失效。

9.4 第三步：优化查询

优化1：修正分区条件

-- 原查询：分区裁剪失效
WHERE toDate(event_time) = '2024-01-15'

-- 优化后：利用分区键
WHERE event_time >= '2024-01-15 00:00:00'
  AND event_time < '2024-01-16 00:00:00'

优化2：添加PREWHERE

-- status_code过滤性强且列体积小
PREWHERE status_code = 200

优化3：避免SELECT*

确保只查询需要的列（原查询已经指定列名，无需修改）。

优化后的完整查询

SELECT
    user_id,
    count() AS event_count,
    sum(duration) AS total_duration,
    uniq(url) AS url_count
FROM web_logs
PREWHERE status_code = 200
WHERE event_time >= '2024-01-15 00:00:00'
  AND event_time < '2024-01-16 00:00:00'
GROUP BY user_id
HAVING event_count > 100
ORDER BY total_duration DESC
LIMIT 50;

9.5 优化效果对比

-- 优化前
EXPLAIN ESTIMATE ... -- parts: 365, rows: 1800000000, bytes: 540000000000
-- 耗时约30秒

-- 优化后
EXPLAIN ESTIMATE ... -- parts: 1, rows: 5000000, bytes: 1500000000
-- 耗时约0.8秒
指标 优化前 优化后 提升倍数
扫描分区数 365 1 365×
扫描行数 18亿 500万 360×
查询耗时 30秒 0.8秒 37.5×

9.6 使用EXPLAIN PIPELINE确认并行度

EXPLAIN PIPELINE SELECT
    user_id,
    count() AS event_count
FROM web_logs
WHERE event_time >= '2024-01-15 00:00:00'
  AND event_time < '2024-01-16 00:00:00'
GROUP BY user_id;

确认各阶段的并行线程数合理，没有单线程瓶颈。

10. 总结与最佳实践

本文系统介绍了ClickHouse的EXPLAIN命令体系和基础查询优化策略。EXPLAIN是查询优化的核心工具，掌握其用法是性能调优的基础。

最佳实践建议：

  1. 养成EXPLAIN习惯：在执行新查询前，先使用EXPLAIN ESTIMATE评估扫描量，确认分区裁剪有效
  2. 分区键设计：确保分区键与常见查询模式匹配，WHERE条件直接使用分区列而非函数包裹
  3. 主键设计：将高基数、高频查询列放在ORDER BY前面，控制主键列数量
  4. **避免SELECT ***：始终明确指定列名，这是列式存储的基本优化
  5. PREWHERE优化：对过滤性强的小体积列使用PREWHERE，减少不必要的列数据读取
  6. EXPLAIN SYNTAX检查：用EXPLAIN SYNTAX确认SQL是否被优化器正确重写，避免别名展开导致的分区裁剪失效
  7. GROUP BY优化：减少GROUP BY列数，使用-If组合器替代CASE WHEN，考虑多阶段聚合
  8. 持续监控：使用system.query_log记录慢查询，定期用EXPLAIN分析并优化

上一篇【第38篇】ClickHouse SELECT高级用法——表达式函数与DISTINCT/UNION ALL
下一篇ClickHouse聚合函数体系详解（上）——基础聚合与组合聚合

# clickhouse # sql # 数据库 # 数据分析
Logo
AtomGit开源社区

AtomGit 是由开放原子开源基金会联合 CSDN 等生态伙伴共同推出的新一代开源与人工智能协作平台。平台坚持“开放、中立、公益”的理念，把代码托管、模型共享、数据集托管、智能体开发体验和算力服务整合在一起，为开发者提供从开发、训练到部署的一站式体验。

更多推荐

cover

comfyui Z-Image运行报错：error in loading state dict for llama2 size mismatch 的解决方法

avatar AtomGit开源社区
cover

基于langchain4j的ai编程助手项目（完整篇）

avatar AtomGit开源社区
cover

算法时代的生存法则：企业如何守住品牌信息的真实底线

avatar AtomGit开源社区