ClickHouse

面向 OLAP 的列式数据库，适合日志分析、行为分析、指标看板、宽表聚合与海量明细查询。以 ClickHouse 23.x/24.x 为参考。

目录

  1. 基础篇

    • 安装与连接

    • 核心概念

    • 数据类型

    • DDL 与 DML

  2. 表引擎篇

    • MergeTree 家族

    • 分区、排序键、主键

    • ReplacingMergeTree

    • Summing / AggregatingMergeTree

    • Distributed

  3. 查询篇

    • SQL 基础

    • 聚合函数

    • JOIN

    • 窗口函数

    • 物化视图

  4. 性能优化篇

    • 建表设计

    • 查询优化

    • 数据写入优化

    • 常见反模式

  5. 集群与高可用篇

    • 分片与副本

    • ReplicatedMergeTree

    • Keeper / ZooKeeper

    • 分布式查询

  6. 运维与监控篇

  7. Python 使用

  8. 常见业务场景

  9. 面试要点

一、基础篇

1.1 安装与连接

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

# 启动
sudo systemctl start clickhouse-server
sudo systemctl enable clickhouse-server

# 客户端连接
clickhouse-client
clickhouse-client --host 127.0.0.1 --port 9000 --user default --password

Docker 快速启动：

docker run -d --name clickhouse \
  -p 8123:8123 -p 9000:9000 \
  clickhouse/clickhouse-server:latest

常用端口：

端口

说明

8123

HTTP 接口

9000

Native TCP 接口

9009

副本间通信

9181

ClickHouse Keeper

1.2 核心概念

概念

说明

列式存储

按列存储数据，聚合查询只读取必要列

Part

MergeTree 写入后的数据分片文件

Partition

逻辑分区，通常按日期或业务周期划分

Primary Key

稀疏主键索引，不保证唯一

Order By

数据在磁盘上的排序方式，影响查询剪枝

Merge

后台合并小 Part，减少文件数量并做去重/聚合

Table Engine

表引擎决定存储、合并、副本、分布式行为

ClickHouse 更适合：

  • 大宽表、日志、埋点、指标、明细分析。

  • 批量写入、低频更新、高并发聚合查询。

  • 按时间范围、维度组合、TopN、漏斗、留存等 OLAP 查询。

不适合：

  • 高频单行更新和事务型业务。

  • 强一致复杂事务。

  • 大量小批量随机写入。

1.3 数据类型

常用基础类型：

UInt8 / UInt16 / UInt32 / UInt64
Int8 / Int16 / Int32 / Int64
Float32 / Float64
Decimal(P, S)
String
FixedString(N)
Date
DateTime
DateTime64
UUID
Bool

常用复合类型：

Nullable(String)
Array(UInt64)
Tuple(String, UInt64)
Map(String, String)
LowCardinality(String)
Enum8('ok' = 1, 'fail' = 2)

选型建议：

场景

类型

字典值、状态、城市、渠道

LowCardinality(String)

金额

Decimal

毫秒级时间

DateTime64(3)

可空字段

Nullable(T)，但会额外存储空值标记

枚举状态

Enum8 / Enum16

1.4 DDL 与 DML

CREATE DATABASE IF NOT EXISTS analytics;

CREATE TABLE analytics.events
(
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_name LowCardinality(String),
    page String,
    properties Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_name, event_date, user_id)
SETTINGS index_granularity = 8192;

写入数据：

INSERT INTO analytics.events
VALUES
('2026-04-28', now(), 1001, 'click', '/home', {'button': 'login'});

查询数据：

SELECT
    event_name,
    count() AS pv,
    uniqExact(user_id) AS uv
FROM analytics.events
WHERE event_date >= today() - 7
GROUP BY event_name
ORDER BY pv DESC
LIMIT 10;

删除或更新：

ALTER TABLE analytics.events DELETE WHERE event_date < today() - 180;

ALTER TABLE analytics.events UPDATE page = '/index'
WHERE page = '/home';

注意：DELETE 和 UPDATE 在 ClickHouse 中是 mutation，后台异步执行，不适合高频使用。

二、表引擎篇

2.1 MergeTree

MergeTree 是 ClickHouse 最核心的表引擎。

CREATE TABLE orders
(
    order_date Date,
    order_id UInt64,
    user_id UInt64,
    amount Decimal(18, 2),
    status LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_date)
ORDER BY (order_date, user_id, order_id);

关键参数：

参数

说明

PARTITION BY

数据分区，影响数据生命周期和分区裁剪

ORDER BY

磁盘排序键，决定稀疏索引效果

PRIMARY KEY

主键索引，默认等于 ORDER BY

SAMPLE BY

采样键

TTL

数据过期、迁移、聚合

2.2 分区、排序键、主键

分区建议：

  • 常见按月：PARTITION BY toYYYYMM(date)。

  • 数据量特别大可按天：PARTITION BY toYYYYMMDD(date)。

  • 分区不要过细，否则 Part 数量膨胀。

  • 查询条件中尽量包含分区字段。

排序键建议：

  • 把最常用过滤条件放前面。

  • 低基数字段适合放前面，如业务线、事件名、状态。

  • 高基数字段如 user_id 通常放在中后段。

  • 排序键不是越长越好，过长会影响写入和合并。

示例：

-- 行为日志：常按事件、时间、用户查
ORDER BY (event_name, event_date, user_id)

-- 订单明细：常按时间、商户、订单查
ORDER BY (order_date, merchant_id, order_id)

2.3 ReplacingMergeTree

用于最终一致去重，常见于日志补偿、CDC 同步。

CREATE TABLE user_profile
(
    user_id UInt64,
    name String,
    age UInt8,
    version UInt64,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;

查询最终结果：

SELECT *
FROM user_profile FINAL
WHERE user_id = 1001;

注意：

  • 去重发生在后台 Merge 时，不是写入立即去重。

  • FINAL 查询成本较高，大表慎用。

  • 更推荐业务层保证查询时按版本聚合：

SELECT
    user_id,
    argMax(name, version) AS name,
    argMax(age, version) AS age
FROM user_profile
GROUP BY user_id;

2.4 SummingMergeTree

适合预聚合数值指标。

CREATE TABLE daily_metrics
(
    dt Date,
    app_id UInt64,
    pv UInt64,
    uv UInt64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, app_id);

相同排序键的数据在合并时会对数值列求和。

查询时仍建议 GROUP BY，因为数据可能尚未完全合并：

SELECT dt, app_id, sum(pv), sum(uv)
FROM daily_metrics
GROUP BY dt, app_id;

2.5 AggregatingMergeTree

适合保存聚合状态。

CREATE TABLE event_agg
(
    dt Date,
    event_name String,
    uv_state AggregateFunction(uniq, UInt64),
    pv_state AggregateFunction(count)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, event_name);

写入聚合状态：

INSERT INTO event_agg
SELECT
    event_date,
    event_name,
    uniqState(user_id),
    countState()
FROM events
GROUP BY event_date, event_name;

读取最终结果：

SELECT
    dt,
    event_name,
    uniqMerge(uv_state) AS uv,
    countMerge(pv_state) AS pv
FROM event_agg
GROUP BY dt, event_name;

2.6 Distributed

Distributed 表用于分布式查询，本身不存储数据。

CREATE TABLE events_local ON CLUSTER cluster_01
(
    event_date Date,
    user_id UInt64,
    event_name String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_name, user_id);

CREATE TABLE events_all ON CLUSTER cluster_01
AS events_local
ENGINE = Distributed(cluster_01, default, events_local, rand());

分片键建议：

  • 用户行为：cityHash64(user_id)。

  • 订单：cityHash64(order_id)。

  • 避免 rand() 用于需要同用户聚合或 JOIN 的场景。

三、查询篇

3.1 SQL 基础

SELECT
    toDate(event_time) AS dt,
    event_name,
    count() AS cnt
FROM events
WHERE event_time >= now() - INTERVAL 1 DAY
GROUP BY dt, event_name
HAVING cnt > 100
ORDER BY cnt DESC
LIMIT 20;

常用函数：

toDate(event_time)
toYYYYMM(event_date)
dateDiff('day', start_time, end_time)
if(status = 'paid', 1, 0)
multiIf(score >= 90, 'A', score >= 80, 'B', 'C')
JSONExtractString(properties, 'source')

3.2 聚合函数

SELECT
    count() AS pv,
    uniq(user_id) AS approx_uv,
    uniqExact(user_id) AS exact_uv,
    sum(amount) AS gmv,
    avg(amount) AS avg_amount,
    quantile(0.95)(latency) AS p95_latency
FROM requests
WHERE dt = today();

常用聚合：

函数

说明

count()

行数

sum() / avg()

求和 / 平均

min() / max()

最大最小值

uniq()

近似去重

uniqExact()

精确去重，内存开销更大

argMax(value, version)

取最大版本对应的值

groupArray()

聚合为数组

quantile()

分位数

3.3 JOIN

SELECT
    e.user_id,
    u.city,
    count() AS cnt
FROM events e
LEFT JOIN users u ON e.user_id = u.user_id
WHERE e.event_date = today()
GROUP BY e.user_id, u.city;

JOIN 建议：

  • 小表 JOIN 大表时，让小表在右侧。

  • 大表 JOIN 大表成本高，优先考虑宽表化。

  • 维表可使用 Dictionary。

  • 分布式 JOIN 关注数据是否同分片。

3.4 物化视图

物化视图常用于实时预聚合。

CREATE TABLE event_daily
(
    dt Date,
    event_name String,
    pv UInt64,
    uv AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(dt)
ORDER BY (dt, event_name);

CREATE MATERIALIZED VIEW mv_event_daily
TO event_daily
AS
SELECT
    event_date AS dt,
    event_name,
    count() AS pv,
    uniqState(user_id) AS uv
FROM events
GROUP BY dt, event_name;

查询：

SELECT
    dt,
    event_name,
    sum(pv) AS pv,
    uniqMerge(uv) AS uv
FROM event_daily
GROUP BY dt, event_name;

注意：物化视图只处理创建之后写入的数据，历史数据需要手动回填。

四、性能优化篇

4.1 建表设计

核心原则：

  • 分区字段要与生命周期和查询条件匹配。

  • ORDER BY 以高频过滤条件优先。

  • 字符串枚举字段使用 LowCardinality。

  • 能用数值类型就不用字符串。

  • 避免过多 Nullable。

  • 大字段、JSON 字段尽量拆出高频查询字段。

4.2 查询优化

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE event_date >= today() - 7
  AND event_name = 'click';

优化清单：

  • 查询只选择必要列，避免 SELECT *。

  • 过滤条件尽量命中分区键和排序键。

  • 先过滤再聚合。

  • 用 PREWHERE 提前读取过滤列。

  • 大表避免频繁 FINAL。

  • 精确去重成本高，能接受误差时用 uniq。

  • 避免在过滤字段上套复杂函数导致索引失效。

4.3 写入优化

建议：

  • 批量写入，每批至少几千到几万行。

  • 避免频繁小 INSERT。

  • 使用异步写入或 Buffer/Kafka 引擎承接流量。

  • 控制分区数量，避免一次写入跨太多分区。

  • 写入前尽量按排序键排序。

示例：

clickhouse-client --query "INSERT INTO events FORMAT CSV" < events.csv

Python 批量写入见 Python 使用。

4.4 常见反模式

反模式

问题

把 ClickHouse 当 MySQL 使用

高频更新、事务不合适

分区按用户 ID

分区数量爆炸

每条数据单独 INSERT

产生大量小 Part

大表频繁 FINAL

查询 CPU 和 IO 开销大

排序键与查询条件无关

无法有效跳过数据

大量 JSON 查询不拆字段

解析成本高

五、集群与高可用篇

5.1 分片与副本

Shard 1: replica1, replica2
Shard 2: replica1, replica2
Shard 3: replica1, replica2

概念

作用

Shard

水平拆分数据，提升容量和吞吐

Replica

副本，提高可用性

Distributed 表

对多个分片发起查询

Local 表

每个节点真实存储数据

5.2 ReplicatedMergeTree

CREATE TABLE events_local ON CLUSTER cluster_01
(
    event_date Date,
    user_id UInt64,
    event_name String
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/events_local',
    '{replica}'
)
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, event_name, user_id);

副本依赖 Keeper/ZooKeeper 保存元数据和协调复制。

5.3 Keeper / ZooKeeper

ClickHouse Keeper 是 ClickHouse 官方实现的 ZooKeeper 兼容协调服务。

主要作用：

  • 复制表元数据协调。

  • 副本状态维护。

  • 分布式 DDL 协调。

  • Leader 选举。

生产建议：

  • Keeper 至少 3 节点。

  • 与 ClickHouse 数据盘分离。

  • 监控延迟、连接数、磁盘空间。

六、运维与监控篇

常用系统表：

SELECT * FROM system.parts WHERE active;
SELECT * FROM system.merges;
SELECT * FROM system.mutations;
SELECT * FROM system.query_log ORDER BY event_time DESC LIMIT 10;
SELECT * FROM system.processes;
SELECT * FROM system.replicas;

常用排障：

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

-- 查看异常 mutation
SELECT database, table, mutation_id, command, is_done, latest_fail_reason
FROM system.mutations
WHERE is_done = 0;

监控指标：

  • 查询延迟、QPS、失败率。

  • 写入速率、Part 数量、Merge 队列。

  • CPU、内存、磁盘 IO、磁盘空间。

  • 副本延迟、Keeper 状态。

七、Python 使用

安装：

pip install clickhouse-connect

连接与查询：

import clickhouse_connect

client = clickhouse_connect.get_client(
    host="localhost",
    port=8123,
    username="default",
    password="",
    database="analytics",
)

rows = client.query("""
    SELECT event_name, count() AS cnt
    FROM events
    WHERE event_date = today()
    GROUP BY event_name
    ORDER BY cnt DESC
""").result_rows

for event_name, cnt in rows:
    print(event_name, cnt)

批量写入：

data = [
    ("2026-04-28", "2026-04-28 12:00:00", 1001, "click", "/home"),
    ("2026-04-28", "2026-04-28 12:01:00", 1002, "view", "/product"),
]

client.insert(
    "events",
    data,
    column_names=["event_date", "event_time", "user_id", "event_name", "page"],
)

八、常见业务场景

8.1 日志分析

SELECT
    level,
    count() AS cnt
FROM app_logs
WHERE log_time >= now() - INTERVAL 1 HOUR
GROUP BY level
ORDER BY cnt DESC;

8.2 用户行为分析

SELECT
    event_name,
    count() AS pv,
    uniq(user_id) AS uv
FROM events
WHERE event_date BETWEEN today() - 7 AND today()
GROUP BY event_name;

8.3 接口监控

SELECT
    path,
    count() AS requests,
    quantile(0.95)(latency_ms) AS p95,
    sumIf(1, status_code >= 500) AS errors
FROM access_logs
WHERE dt = today()
GROUP BY path
ORDER BY requests DESC
LIMIT 20;

九、面试要点

9.1 ClickHouse 为什么快？

  • 列式存储，只读取需要的列。

  • 数据压缩率高，减少 IO。

  • 稀疏索引和排序键可跳过大量数据。

  • 向量化执行，CPU 利用率高。

  • 多线程并行查询。

  • MergeTree 后台合并优化数据布局。

9.2 ClickHouse 和 MySQL 的区别？

对比

ClickHouse

MySQL

类型

OLAP

OLTP

存储

列式

行式

查询

大规模聚合分析

点查、事务、增删改

更新

不适合高频更新

支持事务更新

一致性

更偏最终一致

强事务一致性

9.3 如何设计高性能表？

  • 根据查询模式设计 PARTITION BY 和 ORDER BY。

  • 控制分区粒度。

  • 使用合适数据类型。

  • 避免小批量写入。

  • 大宽表优先，减少大表 JOIN。

  • 结合物化视图做预聚合。

9.4 Part 太多怎么办？

  • 合并小批量写入，减少 INSERT 次数。

  • 检查分区是否过细。

  • 提高批量写入大小。

  • 观察 system.parts 和 system.merges。

  • 必要时执行 OPTIMIZE TABLE ... FINAL，但生产慎用。

参考资源