ClickHouse实战：十亿级数据OLAP查询优化

前言

去年公司要搞一个数据分析平台，日志量大概一天3亿条，保留90天，总共约300亿条数据。之前用MySQL分库分表勉强扛着，但复杂查询动不动就几十秒，业务那边已经骂了好几次。

调研了一圈，Druid、Doris、ClickHouse，最后选了ClickHouse。原因很简单：单机性能强悍，运维相对简单，社区活跃。

用了一年多，踩了不少坑，也总结了一些优化经验。这篇文章分享出来，希望能帮到同样在做数据分析的朋友。

ClickHouse是什么

一句话：列式存储的OLAP数据库，专门用来做数据分析，写进去快，查得更快。

和MySQL这类行存数据库的核心区别：

• 行存：一行数据存在一起，适合CRUD单条记录
• 列存：同一列的数据存在一起，适合聚合统计

举个例子，有张100列的表，查询只涉及3列，MySQL要把整行读出来再挑3列，ClickHouse直接只读3列的数据，IO少一个数量级。

环境搭建

单机版很简单：

# Ubuntu/Debian
apt-get install -y apt-transport-https ca-certificates
apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" > /etc/apt/sources.list.d/clickhouse.list
apt-get update
apt-get install -y clickhouse-server clickhouse-client

# 启动
systemctl start clickhouse-server

# 连接
clickhouse-client

默认配置文件在 /etc/clickhouse-server/，主要改这几个：

<!-- config.xml -->
<max_memory_usage>10000000000</max_memory_usage>  <!-- 单查询内存限制10G -->
<max_threads>8</max_threads>  <!-- 查询并行度 -->

建表：选对引擎很重要

ClickHouse有几十种引擎，生产环境90%用MergeTree家族。

基础MergeTree

CREATE TABLE logs (
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    action String,
    duration UInt32,
    city String,
    device String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (user_id, event_time)
SETTINGS index_granularity = 8192;

几个关键点：

1. PARTITION BY：分区键，按月分区是常见做法。查询时指定月份可以跳过无关分区。
2. ORDER BY：排序键，决定数据物理存储顺序。查询最常用的筛选条件要放进去。
3. index_granularity：索引粒度，默认8192行一个索引项。

选错ORDER BY的代价

第一版我把ORDER BY写成了 (event_time, user_id)，结果按user_id查询特别慢。

原因：ORDER BY决定数据排列顺序，也决定稀疏索引的结构。user_id放第二位，按它查需要扫描大量数据块。

改成 (user_id, event_time) 后，按user_id查询速度提升10倍以上。

原则：把等值查询最常用的列放前面，范围查询的列放后面。

写入优化

批量写入是基本

ClickHouse的写入不是针对小事务设计的。每次INSERT都会生成一个数据part，小批量频繁写入会产生大量parts，影响查询性能。

# 错误方式：一条条插
for row in data:
    insert(row)

# 正确方式：批量插
# 至少1000条起批，建议1万~10万条一批
insert_batch(data[:10000])

官方建议每批次不少于1000行，每秒写入不超过1次。

用Buffer引擎缓冲

如果必须高频小批量写入，用Buffer引擎：

CREATE TABLE logs_buffer AS logs
ENGINE = Buffer(currentDatabase(), logs, 
    16,       -- num_layers
    10, 100,  -- min_time, max_time
    10000, 1000000,  -- min_rows, max_rows
    10000000, 100000000  -- min_bytes, max_bytes
);

数据先写Buffer表，积攒到一定量或时间后自动flush到主表。

实际写入架构

我们的架构是 Kafka -> Flink -> ClickHouse：

Kafka (原始日志)
    |
    v
Flink (聚合、清洗)
    |
    v
ClickHouse (每10秒一批，每批约50万条)

Flink里做时间窗口聚合，积攒10秒的数据一次写入，避免小批量问题。

查询优化

看执行计划

EXPLAIN SELECT user_id, count() 
FROM logs 
WHERE event_date = '2024-12-25' AND city = 'Beijing'
GROUP BY user_id;

关注点：

• 有没有用到分区裁剪
• 有没有用到主键索引
• 扫描了多少行

更详细的：

EXPLAIN PIPELINE SELECT ...

能看到每个处理阶段的执行计划。

分区裁剪

-- 好：指定分区
SELECT * FROM logs WHERE event_date >= '2024-12-01' AND event_date < '2024-12-31'

-- 差：分区列加函数
SELECT * FROM logs WHERE toYYYYMM(event_date) = 202412

第二种写法分区裁剪失效，会扫全表。

利用主键索引

ORDER BY的列才有稀疏索引：

-- 表定义 ORDER BY (user_id, event_time)

-- 快：user_id等值查询，精准定位
SELECT * FROM logs WHERE user_id = 12345

-- 较快：user_id + event_time范围
SELECT * FROM logs WHERE user_id = 12345 AND event_time > '2024-12-01'

-- 慢：跳过user_id直接查event_time
SELECT * FROM logs WHERE event_time > '2024-12-01'

避免SELECT *

列存数据库的优势就是只读需要的列：

-- 差：读所有列
SELECT * FROM logs WHERE user_id = 12345

-- 好：只读需要的列
SELECT user_id, action, duration FROM logs WHERE user_id = 12345

100列的表，读3列和读100列，IO差距巨大。

prewhere代替where

SELECT * FROM logs 
PREWHERE event_date = '2024-12-25'  -- 先过滤
WHERE city = 'Beijing'              -- 再过滤

prewhere在读取其他列之前先过滤，减少需要读取的数据量。过滤性强的条件用prewhere。

物化视图预聚合

高频查询的聚合结果提前算好：

-- 创建物化视图，按天按城市预聚合
CREATE MATERIALIZED VIEW logs_daily_city
ENGINE = SummingMergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, city)
AS SELECT
    event_date,
    city,
    count() as cnt,
    sum(duration) as total_duration
FROM logs
GROUP BY event_date, city;

新数据写入logs时自动聚合到物化视图。查询日/城市维度的汇总直接查物化视图，速度快几十倍。

跳数索引

对于不在ORDER BY里的列，可以加跳数索引：

-- 给city列加布隆过滤器索引
ALTER TABLE logs ADD INDEX idx_city city TYPE bloom_filter GRANULARITY 4;

-- 给duration加minmax索引
ALTER TABLE logs ADD INDEX idx_duration duration TYPE minmax GRANULARITY 4;

bloom_filter适合等值查询，minmax适合范围查询。

几个踩过的坑

坑1：String类型太慢

最开始action、city这些列都用String，后来发现GROUP BY特别慢。

改用LowCardinality：

ALTER TABLE logs MODIFY COLUMN city LowCardinality(String);
ALTER TABLE logs MODIFY COLUMN action LowCardinality(String);

LowCardinality内部是字典编码，对于基数低（不同值少）的列，存储和计算都更高效。我们改完后相关查询快了3倍。

坑2：不小心全表扫描

线上出过一次事故，有人写了个没有where条件的count查询，直接把CPU打满了。

解决：设置查询限制

-- 用户级别限制
CREATE USER analyst SETTINGS max_rows_to_read = 100000000;

-- 查询级别限制
SET max_rows_to_read = 100000000;
SELECT ...

坑3：JOIN性能

ClickHouse的JOIN不是强项，大表JOIN特别慢。

优化方向：

1. 小表放右边（会被广播到所有节点）
2. 用字典代替维度表JOIN
3. 数据预先宽表化，避免JOIN

-- 创建字典
CREATE DICTIONARY city_dict (
    city_id UInt32,
    city_name String,
    province String
) PRIMARY KEY city_id
SOURCE(CLICKHOUSE(
    HOST 'localhost' PORT 9000 USER 'default'
    TABLE 'city_dim' DB 'default'
))
LIFETIME(MIN 300 MAX 600)
LAYOUT(HASHED());

-- 用字典代替JOIN
SELECT 
    dictGet('city_dict', 'city_name', city_id) as city_name,
    count()
FROM logs
GROUP BY city_id;

坑4：磁盘打满

日志数据量太大，磁盘很快就满了。

配置TTL自动清理：

ALTER TABLE logs MODIFY TTL event_date + INTERVAL 90 DAY;

90天前的数据自动删除。

也可以配置冷热分层，旧数据迁移到便宜的存储：

<storage_configuration>
    <disks>
        <hot>
            <path>/data/clickhouse/hot/</path>
        </hot>
        <cold>
            <path>/data/clickhouse/cold/</path>
        </cold>
    </disks>
    <policies>
        <tiered>
            <volumes>
                <hot>
                    <disk>hot</disk>
                </hot>
                <cold>
                    <disk>cold</disk>
                </cold>
            </volumes>
            <move_factor>0.1</move_factor>
        </tiered>
    </policies>
</storage_configuration>

监控与运维

系统表查性能

-- 查询日志
SELECT 
    query, 
    read_rows, 
    read_bytes, 
    result_rows,
    memory_usage,
    query_duration_ms
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_start_time DESC
LIMIT 20;

-- 慢查询
SELECT * FROM system.query_log 
WHERE query_duration_ms > 10000 
ORDER BY query_start_time DESC;

-- 各表大小
SELECT 
    table,
    formatReadableSize(sum(bytes_on_disk)) as size,
    sum(rows) as rows
FROM system.parts
WHERE active
GROUP BY table
ORDER BY sum(bytes_on_disk) DESC;

常规运维操作

-- 手动合并parts（一般不需要，后台自动做）
OPTIMIZE TABLE logs FINAL;

-- 查看parts
SELECT table, partition, name, rows, bytes_on_disk 
FROM system.parts 
WHERE table = 'logs';

-- 删除分区
ALTER TABLE logs DROP PARTITION '202401';

多节点管理

多个ClickHouse节点时，我会用星空组网把它们串到一个虚拟网络里，维护的时候不用一台台跳。特别是跨机房部署的时候，组到一起后用Ansible批量执行命令很方便。

性能数据参考

分享一下我们的实际数据：

• 单表300亿行，约8TB存储（压缩后）
• 8核32G的机器
• 简单聚合查询（单分区）：100ms以内
• 复杂聚合（跨月、多维度）：3-10秒
• 物化视图查询：50ms以内

相比之前MySQL分库分表的方案，复杂查询从几十秒降到几秒，业务那边再也没骂过。

总结

ClickHouse的使用核心就几点：

1. 建表：ORDER BY要根据查询模式设计
2. 写入：批量写，别小批量频繁写
3. 查询：利用好分区裁剪和主键索引，只读需要的列
4. 预计算：高频查询用物化视图

它不是万能的：OLTP场景别用，JOIN重的场景谨慎用。但在OLAP场景，特别是日志分析、用户行为分析、实时报表这些方向，确实是目前性价比最高的选择之一。

刚开始用的时候可能觉得概念多，用熟了会发现套路就那些。希望这篇文章能帮你少踩点坑。