上一篇【第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表
FilterWHERE条件过滤有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秒
指标优化前优化后提升倍数
扫描分区数3651365×
扫描行数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

最高赢取价值万元大奖！仓颉生态创新开发挑战赛等你来挑战

avatar AtomGit开源社区
cover

开源鸿蒙跨平台直播｜Flutter HCPP 开源鸿蒙实践：让原生视图回到系统合成层

avatar AtomGit开源社区
cover

本周精选 G-Star 项目推荐

avatar AtomGit开源社区