ClickHouse：百亿行表秒级聚合查询的列存 OLAP 数据库

PostgreSQL 适合 OLTP（事务），千万行查询还行，几十亿行后聚合就力不从心。
ClickHouse 是俄罗斯 Yandex 开源的列存 OLAP 数据库，专为"几十亿行表 + 复杂
SELECT 聚合"设计。日志分析 / BI / 指标存储常用。

安装

# Debian / Ubuntu
sudo apt install -y apt-transport-https ca-certificates dirmngr
GNUPGHOME=$(mktemp -d)
sudo GNUPGHOME="$GNUPGHOME" gpg --no-default-keyring --keyring /usr/share/keyrings/clickhouse-keyring.gpg --keyserver hkp://keyserver.ubuntu.com:80 --recv-keys 8919F6BD2B48D754
echo "deb [signed-by=/usr/share/keyrings/clickhouse-keyring.gpg] https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt update
sudo apt install -y clickhouse-server clickhouse-client

sudo systemctl enable --now clickhouse-server
clickhouse-client

或 docker：

docker run -d --name ch \
  -p 8123:8123 -p 9000:9000 \
  -v $(pwd)/ch-data:/var/lib/clickhouse \
  clickhouse/clickhouse-server

建表（关键：选对引擎 + 排序键）

CREATE TABLE events (
    event_date  Date,
    event_time  DateTime,
    user_id     UInt64,
    type        LowCardinality(String),
    country     LowCardinality(String),
    amount      Float64,
    payload     String,
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, type, user_id)
SETTINGS index_granularity = 8192;

关键设计：

ENGINE = MergeTree：最常用的引擎，类似 LSM tree，写快读快
PARTITION BY：按月分区，删旧数据 DROP PARTITION 即可
ORDER BY（"主键"）：决定数据物理顺序 + 稀疏索引；按最常 WHERE 的列排序
LowCardinality(String)：枚举类字段（status / country）的压缩 + 加速

插入

INSERT INTO events VALUES
    ('2026-05-23', '2026-05-23 10:00:00', 42, 'login', 'CN', 0.0, ''),
    ('2026-05-23', '2026-05-23 10:01:00', 43, 'purchase', 'US', 99.50, '{...}');

或批量从 CSV：

INSERT INTO events FROM INFILE 'events.csv.gz' FORMAT CSVWithNames;

或从 JDBC / Python：

from clickhouse_driver import Client

client = Client('localhost')
client.execute(
    'INSERT INTO events VALUES',
    [(d, t, uid, type, country, amt, payload) for ... in batch]
)

ClickHouse 偏好 批量大插入（每次 > 1000 行），单条 INSERT 性能差。

聚合查询

-- 国家维度统计昨日金额
SELECT
    country,
    count() AS events,
    sum(amount) AS total,
    avg(amount) AS avg_amt
FROM events
WHERE event_date = today() - 1
GROUP BY country
ORDER BY total DESC
LIMIT 10;

10 亿行 + 单机 16 cores：通常 < 1 秒。同样的查询在 PostgreSQL 几十秒到几分钟。

-- 时间序列：每小时按 type 的趋势
SELECT
    toStartOfHour(event_time) AS hour,
    type,
    count()
FROM events
WHERE event_date BETWEEN today() - 7 AND today()
GROUP BY hour, type
ORDER BY hour, type;

函数：ClickHouse 100s 内置

uniq(col) / uniqExact(col)：基数（HyperLogLog 估算 / 精确）
quantile(0.95)(col)：分位数
groupArray(col) / groupUniqArray(col)：聚合成数组
topK(10)(col)：Top N
argMax(col, by)：返回 by 最大时 col 的值

SELECT
    country,
    uniq(user_id) AS dau,
    quantile(0.95)(amount) AS p95,
    topK(3)(type) AS top_types
FROM events
WHERE event_date = today()
GROUP BY country;

物化视图（pre-aggregated）

百亿行表也希望仪表盘秒级响应？预聚合：

CREATE MATERIALIZED VIEW events_daily_mv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, country, type)
AS
SELECT
    event_date,
    country,
    type,
    count() AS events,
    sum(amount) AS total
FROM events
GROUP BY event_date, country, type;

之后插 events 表的同时自动累加 daily 表。
仪表盘查 events_daily_mv 而不是原表，几毫秒返回。

用 PostgreSQL 表

-- 从 PG 拉数据（postgres engine）
CREATE TABLE pg_users (
    id UInt64, name String, email String
) ENGINE = PostgreSQL(
    'localhost:5432', 'mydb', 'users', 'user', 'pass'
);

SELECT count() FROM pg_users;
-- 实时去 PG 查

或者 ClickHouse 作为 PG 的 OLAP 副本（通过 Kafka / debezium 同步）。

读 Parquet / S3

SELECT count() FROM s3('s3://bucket/data/*.parquet',
                       'access_key', 'secret_key', 'Parquet');
-- 不导入，直接查 S3 上的 Parquet

SELECT * FROM file('events.parquet', 'Parquet') LIMIT 10;

让 ClickHouse 当"S3 上 SQL 查询引擎"，类似 DuckDB / Athena。

副本 + 集群

单机能跑 TB 级。继续增长：

副本（Replica）：高可用 + 读扩展
分片（Shard）：水平扩容

<remote_servers>
    <my_cluster>
        <shard>
            <replica><host>ch01</host><port>9000</port></replica>
            <replica><host>ch02</host><port>9000</port></replica>
        </shard>
        <shard>
            <replica><host>ch03</host><port>9000</port></replica>
            <replica><host>ch04</host><port>9000</port></replica>
        </shard>
    </my_cluster>
</remote_servers>

CREATE TABLE events_dist
ON CLUSTER my_cluster
AS events ENGINE = Distributed(my_cluster, default, events, rand());

查 events_dist 自动并行到所有 shard。

性能 tip

避免 SELECT *：列存的核心优势——只读你要的列
WHERE 用 ORDER BY 的前缀列：才能命中稀疏索引
批量 INSERT > 1000 行/次
LowCardinality(String) 给低基数字符串字段
final 修饰 用在 ReplacingMergeTree 上，性能差，少用

何时选 ClickHouse

日志 / 事件 / metric > 1 亿行
聚合密集 / OLAP 类查询
不需要事务

不适合：

事务 / 强一致（用 PG）
频繁 UPDATE / DELETE（ClickHouse 这两个是异步 ALTER）
小数据（< 1 GB）—— 杀鸡用牛刀

工具

clickhouse-client：CLI（很强）
clickhouse-cli（rust 版）：交互体验更好
Tabix / DBeaver / Metabase / Superset：可视化
Grafana 内置 ClickHouse data source

踩过的坑

单条 INSERT 性能极差（百行/秒）：批量插入百倍速度差距。
ORDER BY 选错列：所有查询的 WHERE 都不能用上索引，全表扫。
按"最常被 WHERE filter 的列"排。
UPDATE / DELETE：在 ClickHouse 是 ALTER TABLE ... UPDATE，
异步、慢、影响压缩。设计阶段尽量避免。
分区数 > 1000 性能下降。partition 颗粒度通常按月。