ClickHouse 完全指南

官方文档：https://clickhouse.com/docs/zh
适用版本：ClickHouse 24.x（2026-05-07 整理）

目录

• 核心概念
• MergeTree 系列引擎
• 数据库与表操作
• 字段类型
• 数组函数
• 查询语法
• Python 客户端
• 踩坑与注意事项

核心概念

列式存储原理

ClickHouse 是列式存储数据库。与行式存储（MySQL、PostgreSQL）不同，列式存储将同一列的所有数据连续存放在磁盘上。

行式存储 vs 列式存储：

OLAP vs OLTP

ClickHouse 适用场景：日志分析、用户行为统计、监控指标聚合、实时报表。

MergeTree 系列引擎

MergeTree 是 ClickHouse 最重要的存储引擎族，所有生产场景几乎都用此系列。

MergeTree

最基础的引擎，数据按 ORDER BY 键排序存储，后台定期合并数据片段（parts）。

CREATE TABLE events
(
    event_date Date,
    user_id    UInt32,
    event_type String,
    value      Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id);

ReplacingMergeTree

在后台合并时，对相同 ORDER BY 键的行进行去重，保留最新版本。用于模拟 UPSERT 语义。

CREATE TABLE user_profiles
(
    user_id    UInt32,
    name       String,
    updated_at DateTime
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

注意：去重仅在后台合并时发生，查询时可能仍有重复行，需配合 FINAL 关键字或 GROUP BY 手动去重。

-- 强制去重（性能较差，谨慎使用）
SELECT * FROM user_profiles FINAL;

SummingMergeTree

合并时对相同 ORDER BY 键的行进行数值列求和，适合预聚合场景。

CREATE TABLE page_views_agg
(
    date      Date,
    page_id   UInt32,
    views     UInt64,
    duration  Float64
)
ENGINE = SummingMergeTree((views, duration))
ORDER BY (date, page_id);

括号内指定需要求和的列，未指定的数值列也会被求和，非数值列保留第一行的值。

AggregatingMergeTree

比 SummingMergeTree 更通用，支持存储聚合函数的中间状态（使用 AggregateFunction 类型）。通常配合物化视图使用。

CREATE TABLE visits_agg
(
    date       Date,
    page_id    UInt32,
    uniq_users AggregateFunction(uniq, UInt32)
)
ENGINE = AggregatingMergeTree()
ORDER BY (date, page_id);

写入时需使用 -State 后缀函数，查询时使用 -Merge 后缀函数。

-- 写入
INSERT INTO visits_agg
SELECT date, page_id, uniqState(user_id)
FROM raw_visits
GROUP BY date, page_id;

-- 查询
SELECT date, page_id, uniqMerge(uniq_users)
FROM visits_agg
GROUP BY date, page_id;

引擎对比

PARTITION BY 与 ORDER BY 的关系

• PARTITION BY：决定数据如何分区存储在磁盘上，查询时可跳过不相关分区（分区裁剪）
• ORDER BY：决定分区内数据的物理排序，同时决定 MergeTree 系列的去重/聚合键，查询时支持稀疏索引加速

两者关系：

• ORDER BY 的第一列通常与 PARTITION BY 相关，保证同一分区内数据局部性好
• PRIMARY KEY 若未指定，默认等于 ORDER BY；可单独设置 PRIMARY KEY 为 ORDER BY 的前缀（仅用于索引，不影响排序）
• 分区数量建议控制在数千以内，过多分区严重影响性能（见踩坑部分）

数据库与表操作

创建数据库

CREATE DATABASE IF NOT EXISTS analytics;
USE analytics;

CREATE TABLE 完整语法

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    column1 Type1 [DEFAULT expr1] [COMMENT 'comment'],
    column2 Type2 [CODEC(compression_codec)],
    ...
    INDEX index_name expr TYPE type GRANULARITY n
)
ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'disk'|TO VOLUME 'vol']]
[SETTINGS setting = value, ...];

常用子句说明：

TTL 示例：

CREATE TABLE logs
(
    ts      DateTime,
    level   String,
    message String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(ts)
ORDER BY ts
TTL ts + INTERVAL 30 DAY DELETE;

ALTER TABLE

-- 新增列
ALTER TABLE events ADD COLUMN os String DEFAULT '' AFTER event_type;

-- 删除列
ALTER TABLE events DROP COLUMN os;

-- 修改列类型
ALTER TABLE events MODIFY COLUMN value Float32;

-- 修改 TTL
ALTER TABLE logs MODIFY TTL ts + INTERVAL 7 DAY;

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

-- 清空表
TRUNCATE TABLE events;

注意：ClickHouse 的 ALTER TABLE 是异步的，ADD COLUMN / DROP COLUMN 操作立即返回，实际变更在后台执行，但不影响查询。

INSERT INTO

ClickHouse 写入设计为批量操作，每次 INSERT 产生一个新的 part，后台异步合并。

-- 基础写入
INSERT INTO events (event_date, user_id, event_type, value)
VALUES ('2024-01-01', 1001, 'click', 1.0);

-- 从另一张表写入（批量）
INSERT INTO events_archive
SELECT * FROM events WHERE event_date < '2024-01-01';

-- 指定格式写入（HTTP 接口常用）
INSERT INTO events FORMAT CSV
2024-01-01,1001,click,1.0
2024-01-01,1002,view,1.0

字段类型

数值类型

字符串类型

时间类型

-- 带时区的 DateTime
CREATE TABLE t (ts DateTime('Asia/Shanghai'));

复合类型

数组函数

ClickHouse 对数组操作有丰富的内置支持。

arrayJoin

将数组展开为多行，类似 SQL 的 UNNEST。

SELECT arrayJoin([1, 2, 3]) AS val;
-- 返回三行: 1, 2, 3

SELECT user_id, arrayJoin(tags) AS tag
FROM articles;
-- 将每篇文章的 tags 数组展开，每个 tag 一行

arrayMap

对数组每个元素应用 lambda 函数，返回新数组。

SELECT arrayMap(x -> x * 2, [1, 2, 3]);
-- [2, 4, 6]

SELECT arrayMap(x -> x + 1, prices) AS adjusted_prices
FROM products;

arrayFilter

过滤数组中满足条件的元素。

SELECT arrayFilter(x -> x > 2, [1, 2, 3, 4]);
-- [3, 4]

-- 过滤掉 0 值
SELECT arrayFilter(x -> x != 0, values) AS non_zero
FROM metrics;

arraySum

对数组元素求和。

SELECT arraySum([1, 2, 3, 4]);
-- 10

SELECT user_id, arraySum(daily_scores) AS total_score
FROM user_stats;

其他常用数组函数

查询语法

SELECT 基础与 MySQL 的差异

-- 基础查询结构（与 MySQL 基本相同）
SELECT
    toDate(ts) AS date,
    user_id,
    count()    AS cnt
FROM events
WHERE ts >= '2024-01-01'
  AND event_type = 'click'
GROUP BY date, user_id
ORDER BY date DESC, cnt DESC
LIMIT 100;

ClickHouse 与 MySQL SELECT 的主要差异：

聚合函数

-- 分位数示例
SELECT
    quantile(0.5)(response_time)  AS p50,
    quantile(0.95)(response_time) AS p95,
    quantile(0.99)(response_time) AS p99
FROM requests
WHERE date = today();

GROUP BY WITH ROLLUP / CUBE

-- ROLLUP: 从右到左逐级汇总
SELECT
    toYear(event_date) AS year,
    toMonth(event_date) AS month,
    sum(value) AS total
FROM events
GROUP BY year, month WITH ROLLUP
ORDER BY year, month;

-- CUBE: 所有维度组合的汇总
SELECT
    region,
    product,
    sum(revenue) AS total
FROM sales
GROUP BY region, product WITH CUBE;

-- TOTALS: 额外返回总计行
SELECT
    event_type,
    count() AS cnt
FROM events
GROUP BY event_type WITH TOTALS;

窗口函数

ClickHouse 从 21.6 开始正式支持窗口函数，语法与标准 SQL 兼容。

-- 排名
SELECT
    user_id,
    score,
    row_number() OVER (ORDER BY score DESC) AS rn,
    rank()       OVER (ORDER BY score DESC) AS rnk,
    dense_rank() OVER (ORDER BY score DESC) AS drnk
FROM leaderboard;

-- 分组内排名
SELECT
    user_id,
    category,
    revenue,
    row_number() OVER (
        PARTITION BY category
        ORDER BY revenue DESC
    ) AS category_rank
FROM sales;

-- 滑动窗口聚合
SELECT
    ts,
    value,
    avg(value) OVER (
        ORDER BY ts
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7
FROM metrics;

PREWHERE vs WHERE

这是 ClickHouse 特有的优化机制。

-- 手动使用 PREWHERE（通常不需要，引擎自动优化）
SELECT user_id, name, email, address, phone
FROM users
PREWHERE is_active = 1
WHERE created_at > '2024-01-01';
-- is_active 先过滤，只有满足条件的行才读取 name/email/address/phone

多数情况下让 ClickHouse 自动决定即可，不必手动写 PREWHERE。

WITH CTE

-- 基础 CTE
WITH
    active_users AS (
        SELECT DISTINCT user_id
        FROM events
        WHERE event_date >= today() - 30
    )
SELECT u.user_id, u.name
FROM users u
WHERE u.user_id IN (SELECT user_id FROM active_users);

-- 多个 CTE
WITH
    daily_stats AS (
        SELECT
            toDate(ts) AS date,
            count() AS cnt
        FROM events
        GROUP BY date
    ),
    avg_stat AS (
        SELECT avg(cnt) AS avg_cnt FROM daily_stats
    )
SELECT date, cnt, avg_cnt
FROM daily_stats, avg_stat
ORDER BY date;

JOIN 类型

ClickHouse 的 JOIN 与 MySQL 有重要差异：右表（小表）必须能放入内存，不支持 hash join 溢出到磁盘（默认配置）。

-- 基础 JOIN 语法
SELECT a.user_id, a.event_type, b.name
FROM events AS a
INNER JOIN users AS b ON a.user_id = b.user_id;

JOIN 类型：

ClickHouse JOIN 注意事项：

• 大表 JOIN 大表性能差，尽量用右表为小表（维度表）
• 推荐使用字典（Dictionary）代替频繁的维度表 JOIN
• 分布式查询中 JOIN 需要注意数据分布，可能产生大量网络传输

Python 客户端

clickhouse-driver（同步，原生 TCP 协议）

pip install clickhouse-driver

from clickhouse_driver import Client

# 创建连接
client = Client(
    host='localhost',
    port=9000,
    database='analytics',
    user='default',
    password='',
    settings={'use_numpy': True}
)

# 查询，返回列表
rows = client.execute('SELECT user_id, count() FROM events GROUP BY user_id LIMIT 10')
# [(1001, 500), (1002, 300), ...]

# 查询带列名
rows, columns = client.execute(
    'SELECT user_id, count() AS cnt FROM events GROUP BY user_id LIMIT 10',
    with_column_types=True
)
# columns: [('user_id', 'UInt32'), ('cnt', 'UInt64')]

# 参数绑定（防注入）
rows = client.execute(
    'SELECT * FROM events WHERE event_type = %(event_type)s AND event_date = %(date)s',
    {'event_type': 'click', 'date': '2024-01-01'}
)

# 批量插入（推荐方式）
data = [
    ('2024-01-01', 1001, 'click', 1.0),
    ('2024-01-01', 1002, 'view', 1.0),
]
client.execute(
    'INSERT INTO events (event_date, user_id, event_type, value) VALUES',
    data
)

# 流式查询（大数据量）
settings = {'max_block_size': 100000}
for block in client.execute_iter('SELECT * FROM big_table', settings=settings):
    process(block)

Client 构造参数：

asynch（异步，原生 TCP 协议）

pip install asynch

import asyncio
from asynch import connect

async def main():
    conn = await connect(
        host='localhost',
        port=9000,
        database='analytics',
        user='default',
        password=''
    )
    cursor = await conn.cursor()

    # 查询
    await cursor.execute('SELECT count() FROM events')
    result = await cursor.fetchall()

    # 批量插入
    await cursor.execute(
        'INSERT INTO events (event_date, user_id, event_type, value) VALUES',
        [
            ('2024-01-01', 1001, 'click', 1.0),
            ('2024-01-01', 1002, 'view', 1.0),
        ]
    )
    await conn.commit()
    await conn.close()

asyncio.run(main())

clickhouse-connect（HTTP 协议）

适合通过 HTTP/HTTPS 连接，支持 pandas/arrow 格式输出，适合数据科学场景。

pip install clickhouse-connect

import clickhouse_connect

# 创建客户端
client = clickhouse_connect.get_client(
    host='localhost',
    port=8123,
    username='default',
    password='',
    database='analytics'
)

# 查询为 QueryResult
result = client.query('SELECT * FROM events LIMIT 10')
print(result.result_rows)   # 列表形式
print(result.column_names)  # 列名

# 查询为 pandas DataFrame
df = client.query_df('SELECT * FROM events LIMIT 10')

# 查询为 Apache Arrow
table = client.query_arrow('SELECT * FROM events LIMIT 10')

# 插入 pandas DataFrame
import pandas as pd
df = pd.DataFrame({
    'event_date': ['2024-01-01'],
    'user_id': [1001],
    'event_type': ['click'],
    'value': [1.0]
})
client.insert_df('events', df)

# 批量插入列表
client.insert(
    'events',
    data=[
        ['2024-01-01', 1001, 'click', 1.0],
        ['2024-01-01', 1002, 'view', 1.0],
    ],
    column_names=['event_date', 'user_id', 'event_type', 'value']
)

批量插入最佳实践

ClickHouse 每次 INSERT 都会产生一个新的 part，过于频繁的小批量 INSERT 会导致 part 数量爆炸，触发 Too many parts 错误，后台合并压力大。

# 错误做法：逐行插入
for row in rows:
    client.execute('INSERT INTO events VALUES', [row])  # 每行一个 part，严禁

# 正确做法一：累积后批量插入
BATCH_SIZE = 10000
buffer = []

for row in rows:
    buffer.append(row)
    if len(buffer) >= BATCH_SIZE:
        client.execute('INSERT INTO events VALUES', buffer)
        buffer.clear()

if buffer:
    client.execute('INSERT INTO events VALUES', buffer)

# 正确做法二：使用队列异步缓冲
import asyncio
from asyncio import Queue

async def insert_worker(queue: Queue, client):
    batch = []
    while True:
        try:
            row = await asyncio.wait_for(queue.get(), timeout=1.0)
            batch.append(row)
            if len(batch) >= 10000:
                await flush(client, batch)
                batch.clear()
        except asyncio.TimeoutError:
            if batch:
                await flush(client, batch)
                batch.clear()

批量插入建议：

踩坑与注意事项

不支持事务

ClickHouse 不支持跨行的 ACID 事务。每条 INSERT 语句是原子的（要么整批成功，要么失败），但不支持 BEGIN/COMMIT/ROLLBACK。

如果需要保证幂等写入，使用 ReplacingMergeTree 并在业务层保证 ORDER BY 键的唯一性。

UPDATE/DELETE 代价极高

ClickHouse 的 UPDATE/DELETE 是通过重写整个数据 part 实现的（Mutation），非常慢且消耗大量 IO。

-- 这两个操作都是异步重写 part，代价极高
ALTER TABLE events UPDATE value = 0 WHERE user_id = 1001;
ALTER TABLE events DELETE WHERE user_id = 1001;

-- 查看 mutation 状态
SELECT * FROM system.mutations WHERE is_done = 0;

替代方案：

• 不需要更新：设计时让数据只追加
• 需要更新最新状态：使用 ReplacingMergeTree，INSERT 新版本，查询时用 FINAL 或 argMax
• 逻辑删除：增加 is_deleted 标志列，查询时过滤

分区数量不宜过多

每个分区对应磁盘上的独立目录，每次 INSERT 在相关分区中创建新 part。分区数量过多导致：

• Too many parts 错误（默认超过 300 个 active parts 报错）
• 后台合并线程压力大
• 内存占用增加

建议：

• 时间分区粒度不低于月（toYYYYMM(date)），高频写入场景用天（toYYYYMMDD(date)）已是上限
• 不要用高基数字段（如 user_id）做分区键
• 单表分区总数控制在数百到数千以内

Nullable 类型的性能损耗

Nullable(T) 需要额外存储一个 null mask 文件，聚合函数对 Nullable 列的处理更慢。除非业务上确实存在 NULL，否则用默认值代替（空字符串、0、1970-01-01 等）。

JOIN 右表必须小

大表与大表 JOIN 在 ClickHouse 中性能极差，右表必须能放入内存。替代方案：

• 使用字典（Dictionary）做维度查找，替代 JOIN
• 提前在 ETL 阶段做宽表，避免查询时 JOIN

时区问题

ClickHouse 默认使用服务器时区存储 DateTime。跨时区部署时需要明确指定时区：

-- 显式指定时区
CREATE TABLE t (ts DateTime('Asia/Shanghai'));

-- 查询时转换
SELECT toDateTime(ts, 'Asia/Shanghai') FROM t;

数据采样（SAMPLE BY）

如果表定义了 SAMPLE BY，可以用采样查询快速得到近似结果：

-- 只扫描 10% 的数据，结果近似
SELECT count() * 10 AS approx_total
FROM events SAMPLE 0.1;

最佳实践

使用 MergeTree 系列引擎：普通 OLAP 场景首选 ReplacingMergeTree（幂等写入）或 AggregatingMergeTree（预聚合），避免使用 Memory 引擎存储持久数据。

按查询模式设计分区键：PARTITION BY toYYYYMM(ts) 让按月过滤的查询只扫描相关分区。避免分区过细（超过 1000 个分区会拖慢后台合并）。

主键（ORDER BY）排在过滤字段前：把最高基数、最常 WHERE 的字段放前面。典型模式：ORDER BY (tenant_id, event_date, user_id)。

批量写入代替逐行 INSERT：每次写入至少 1000 行，使用 clickhouse-client 的 --format 批量导入或 HTTP batch API，单行 INSERT 会造成大量小文件影响合并性能。

-- 批量插入（推荐）
INSERT INTO events (ts, user_id, action)
VALUES
  ('2024-01-01 00:00:00', 1, 'click'),
  ('2024-01-01 00:00:01', 2, 'view'),
  -- ...
;

使用物化视图做实时预聚合：将高频聚合查询结果写入 AggregatingMergeTree，查询时用 *Merge 函数合并中间状态，避免每次全表扫描。

CREATE MATERIALIZED VIEW mv_hourly
ENGINE = AggregatingMergeTree()
ORDER BY (hour, user_id)
AS
SELECT
    toStartOfHour(ts) AS hour,
    user_id,
    countState() AS cnt
FROM events
GROUP BY hour, user_id;

-- 查询
SELECT hour, user_id, countMerge(cnt) FROM mv_hourly GROUP BY hour, user_id;

右表用字典替代 JOIN：维度表（城市、品类）转为 Dictionary，通过 dictGet() 点查，避免大表 JOIN 内存溢出。

常见陷阱

陷阱：ORDER BY 与主键混淆

现象： 以为 PRIMARY KEY 决定存储顺序，实际查询没有走稀疏索引。
原因： ClickHouse 的 PRIMARY KEY 仅定义稀疏索引范围，存储顺序由 ORDER BY 决定。若只写 ORDER BY 不写 PRIMARY KEY，则主键默认与 ORDER BY 相同。
解决： 明确只写 ORDER BY (col1, col2)，不要与 PRIMARY KEY 混用，除非有意只对前几列建稀疏索引。

陷阱：高频小批量写入导致"too many parts"错误

现象： 写入一段时间后查询变慢，日志出现 Too many parts，最终抛出异常。
原因： 每次 INSERT 生成一个 Part，后台 Merge 线程来不及合并，Part 数超过 max_parts_in_total 限制（默认 100000）。
解决： 应用层做缓冲，每次写入 ≥ 1000 行；或使用 Buffer 引擎中间层聚合后批量落盘。

-- Buffer 引擎：先写 Buffer，满足条件后自动 flush 到目标表
CREATE TABLE events_buffer AS events
ENGINE = Buffer(default, events, 4, 10, 60, 1000, 100000, 10000000, 1000000000);

陷阱：DateTime 时区存储与查询不一致

现象： 插入数据后按时间范围查询结果偏移 8 小时。
原因： 表建时未指定时区，使用服务器默认 UTC 存储；应用层传入的是本地时间字符串，导致偏移。
解决： 建表时明确指定 DateTime('Asia/Shanghai')，同时确保写入端也传带时区的 ISO 8601 字符串。

参见

MySQL基础完全指南
PostgreSQL完全指南