数据库

ClickHouse SQL 编写与性能优化

ClickHouse 的性能潜力需要通过正确的 SQL 编写来释放。本文详解查询优化策略、索引利用、物化视图、JOIN 最佳实践和查询级性能调优。

Plume 5 分钟阅读 2276 字

1. ClickHouse SQL 优化哲学

ClickHouse 是为扫描海量数据而设计的 OLAP 数据库。这意味着它的优化策略与传统 OLTP 数据库截然不同：

  • OLTP 优化目标：通过索引快速定位少量行（点查）
  • ClickHouse 优化目标：通过筛选和数据裁剪减少扫描的数据量，利用向量化执行最大化吞吐量

核心理念：ClickHouse 不需要避免全表扫描——它需要避免扫描不需要的数据。

2. 主键与索引的最佳利用

2.1 主键设计原则

ClickHouse 的主键不是唯一约束，而是数据排序键。设计良好的主键让查询能够跳过大量的数据块。

-- 好的主键设计（符合查询模式）
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    page_url String
) ENGINE = MergeTree()
ORDER BY (event_time, user_id, event_type);

-- 查询时可以利用主键顺序做范围剪枝
SELECT count() FROM events 
WHERE event_time > '2024-01-01' AND event_time < '2024-01-02';
-- 可以跳过 event_time 范围外的所有数据块

主键设计黄金法则：

  1. 最常出现在 WHERE 的列放前面：如果 90% 的查询有 WHERE event_time = ? 条件，event_time 应该在最前面
  2. 基数高的列放前面：主键按字典序排序，高基数列在前面能使数据分布更均匀
  3. 不要太多列：主键列越多，排序开销越大，写入性能下降
  4. 不是查询条件的列不要放进主键：每增加一列都会降低压缩效率

2.2 稀疏索引的查询利用

-- 主键为 (user_id, event_type)
-- 查询1：可以利用 user_id 剪枝
SELECT * FROM events WHERE user_id = 12345;
-- ✅ 能利用稀疏索引，只扫描 user_id ∈ [附近范围] 的数据块

-- 查询2：无法利用 user_id 索引（跳过了前缀）
SELECT * FROM events WHERE event_type = 'click';
-- ❌ 需要全表扫描，因为 event_type 不是主键前缀

-- 查询3：可以部分利用（先剪枝 user_id，再过滤 event_type）
SELECT * FROM events WHERE user_id = 12345 AND event_type = 'click';
-- ✅ user_id 剪枝后，数据量已大幅减少

2.3 投影（Projection）

Projection 是 ClickHouse 为特定查询模式创建的预排序数据副本：

ALTER TABLE events ADD PROJECTION event_type_projection (
    SELECT user_id, event_type, count()
    ORDER BY event_type, user_id
);

-- 现在查询 event_type 时，ClickHouse 会自动使用 projection
SELECT count() FROM events WHERE event_type = 'click';
-- 使用 projection，无需全表扫描

3. 查询优化技术

3.1 PREWHERE vs WHERE

ClickHouse 自动将 WHERE 条件中能高效过滤的条件移动到 PREWHERE：

-- ClickHouse 会自动优化
SELECT name, age FROM users WHERE city = 'Beijing' AND name LIKE 'A%';

-- 内部执行逻辑大致如下：
-- PREWHERE city = 'Beijing'  -- 先加载 city 列，过滤出匹配的行号
-- WHERE name LIKE 'A%'       -- 只对过滤后的行加载 name 列

不需要手动写 PREWHERE（除非在做高级优化）。

3.2 LIMIT 优化

ClickHouse 支持带 LIMIT 的 ORDER BY 优化——不需要对所有数据排序：

-- 如果只需要 Top 10，ClickHouse 会用部分排序算法
SELECT user_id, sum(value) AS total
FROM events
GROUP BY user_id
ORDER BY total DESC
LIMIT 10;
-- ✅ ClickHouse 不会对所有用户排序，只维护大小为 10 的优先队列

3.3 避免 SELECT *

-- ❌ 不要这样写：加载所有列
SELECT * FROM events WHERE event_date = today();

-- ✅ 只查询需要的列
SELECT user_id, event_type FROM events WHERE event_date = today();
-- 如果表有 100 列，后者可能减少 90% 的 I/O

3.4 使用 MATERIALIZED 列预计算

CREATE TABLE events (
    event_time DateTime,
    event_date Date MATERIALIZED toDate(event_time),
    -- MATERIALIZED 列自动从其他列计算，不增加写入成本
    user_id UInt64,
    value Float64,
    value_squared Float64 MATERIALIZED value * value
    -- 预计算常用表达式
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);

-- 查询时可以直接用 MATERIALIZED 列
SELECT avg(value_squared) FROM events;
-- value_squared 已经预计算，无需在查询时计算

4. JOIN 优化

4.1 ClickHouse JOIN 的限制

ClickHouse 的 JOIN 性能不如专门的 OLTP 数据库。最佳实践是：

-- ❌ 大表 JOIN 大表（性能差）
SELECT a.*, b.name
FROM large_a a
JOIN large_b b ON a.id = b.id;

-- ✅ 大表 JOIN 小表（用 JOIN 引擎将小表加载到内存）
-- 先创建内存中的 JOIN 表
CREATE TABLE users_join (
    user_id UInt64,
    name String,
    city String
) ENGINE = Join(ANY, LEFT, user_id);

INSERT INTO users_join SELECT * FROM users;

-- 然后查询
SELECT e.*, joinGet(users_join, 'name', e.user_id) AS user_name
FROM events e
WHERE event_date = today();

4.2 字典（Dictionary）替代小表 JOIN

-- 创建外部字典（从文件、数据库或 HTTP 加载）
CREATE DICTIONARY user_dict (
    user_id UInt64,
    name String,
    city String DEFAULT 'unknown'
)
PRIMARY KEY user_id
SOURCE(CLICKHOUSE(HOST 'localhost' PORT 9000 DB 'default' TABLE 'users'))
LIFETIME(MIN 300 MAX 3600)  -- 每 5 到 60 分钟刷新
LAYOUT(HASHED());           -- 使用哈希表存储

-- 查询时使用字典（完全在内存，零 JOIN 开销）
SELECT 
    event_type,
    dictGet('user_dict', 'name', user_id) AS user_name,
    dictGet('user_dict', 'city', user_id) AS user_city
FROM events
WHERE event_date = today();

4.3 使用 GLOBAL JOIN

在分布式查询中，默认的 JOIN 在每个分片上独立执行。如果维表不大，使用 GLOBAL JOIN 把右表广播到所有节点：

-- 分布式环境下的 JOIN
SELECT e.*, u.name
FROM events_distributed e
GLOBAL JOIN users_distributed u ON e.user_id = u.user_id;
-- users 表会被完整复制到每个分片，然后本地 JOIN
-- 适合 users 表不大（百万级以内）的场景

5. 物化视图

物化视图是 ClickHouse 最重要的性能优化手段之一。它在数据写入时自动触发，将聚合结果预计算到另一张表中。

5.1 创建物化视图

-- 目标表：存储聚合结果
CREATE TABLE event_stats (
    event_date Date,
    event_type String,
    event_count UInt64,
    total_value Float64
) ENGINE = SummingMergeTree()
ORDER BY (event_date, event_type);

-- 物化视图：写入 events 时自动聚合
CREATE MATERIALIZED VIEW event_stats_mv
TO event_stats
AS SELECT
    toDate(event_time) AS event_date,
    event_type,
    count() AS event_count,
    sum(value) AS total_value
FROM events
GROUP BY event_date, event_type;

-- 现在写入 events 时，event_stats 会自动更新
INSERT INTO events VALUES (now(), 1, 'click', 10.0);

-- 查询预聚合结果（比实时聚合快 10-100 倍）
SELECT * FROM event_stats WHERE event_date = today();

5.2 物化视图的使用场景

场景物化视图方案加速比
每日报表按天预聚合100x+
Top N 查询维护排序后的聚合表50x+
漏斗分析预计算各步骤用户数30x+
每分钟指标TO 到 AggregatingMergeTree实时
去重计数使用 uniqState 预聚合20x+

6. 分区与 TTL

6.1 分区策略

-- 按日期分区（最常用）
PARTITION BY toYYYYMMDD(event_time)

-- 按月分区（数据量特别大时）
PARTITION BY toYYYYMM(event_time)

-- 按事件类型分区（类型有限且查询经常按类型过滤）
PARTITION BY event_type

-- 组合分区
PARTITION BY (toYYYYMM(event_time), event_type)

分区的好处：

  • 快速删除：ALTER TABLE ... DROP PARTITION '202401' 瞬间删除一个月的数据
  • 分区剪枝：查询 WHERE event_time > '2024-06-01' 时只扫描需要的分区
  • 独立优化：每个分区可以独立 merge，并行处理

6.2 TTL（生存时间）

-- 自动删除旧数据
CREATE TABLE events (
    event_time DateTime,
    user_id UInt64,
    event_type String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id)
TTL event_time + INTERVAL 3 MONTH;  -- 3 个月后自动删除

-- 也可以移动到更便宜的存储（分层存储）
TTL event_time + INTERVAL 1 MONTH TO VOLUME 'cold',
    event_time + INTERVAL 6 MONTH DELETE;

7. 查询性能分析

7.1 EXPLAIN 分析

-- 查看查询执行计划
EXPLAIN SELECT count() FROM events WHERE event_date = today();

-- 查看更详细的执行信息
EXPLAIN PIPELINE SELECT count() FROM events WHERE event_date = today();

-- 查看索引使用情况
EXPLAIN ESTIMATE SELECT * FROM events WHERE user_id = 12345;

7.2 查询日志分析

-- 启用查询日志
SET log_queries = 1;

-- 查看慢查询
SELECT 
    query,
    event_time,
    query_duration_ms,
    read_rows,
    read_bytes,
    result_rows
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
  AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 20;

8. 总结

ClickHouse 的 SQL 优化核心在于：让查询引擎尽可能少地读取数据。

优化手段效果适用场景
精心设计主键数据块级剪枝所有查询
只查需要的列减少 I/O列数多的宽表
物化视图查询时零计算聚合报表、漏斗
字典替代 JOIN内存 O(1) 查找维度表关联
合理使用 LIMIT部分排序Top N 查询
分区策略分区级剪枝时间序列数据
TTL自动清理日志、监控

ClickHouse 的性能优化不是"调参数"的艺术，而是"数据布局"的学问。数据按照查询模式组织好了，查询自然就会快。

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「数据库」更多文章

  1. ClickHouse 表引擎详解
  2. ClickHouse 监控与运维
  3. ClickHouse 生产案例与最佳实践