ClickHouse——MergeTree

## MergeTree

MergeTree 可以理解成 ClickHouse 里最常用的“分析表底座”。官方文档里也把 MergeTree 家族描述为 ClickHouse 存储能力的核心，具备列式存储、自定义分区、稀疏主键索引、二级跳读索引等能力。

它的写入方式很有特点：

```text
写入数据 -> 生成 part -> part 内排序 -> 后台合并 part
```

也就是说，插入不是每行慢慢维护一个传统 B 树索引，而是把一批数据组织成 part。每个 part 内部按排序键排好，再由后台任务逐步合并。这个模型非常适合大批量写入和分析查询。

![MergeTree part granule marks](https://zoytown-sg.oss-ap-southeast-1.aliyuncs.com/blogs/mergetree_parts_granules_qtak.svg)

## 一张基础表先跑起来

先建一张事件明细表：

```sql
CREATE TABLE app_events
(
    tenant_id UInt64,
    app_id UInt32,
    user_id UInt64,
    event_name LowCardinality(String),
    event_day Date,
    event_ms UInt64,
    amount Decimal(18, 2),
    properties String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_day)
ORDER BY (tenant_id, app_id, event_name, event_day, user_id)
SETTINGS index_granularity = 8192;
```

这张表里最重要的不是字段类型，而是下面两行：

```sql
PARTITION BY toYYYYMM(event_day)
ORDER BY (tenant_id, app_id, event_name, event_day, user_id)
```

`PARTITION BY` 用来做粗粒度切分，`ORDER BY` 决定 part 内排序，也决定稀疏索引能不能帮查询少读数据。

## `ORDER BY` 是 MergeTree 的灵魂

ClickHouse 的 `ORDER BY` 不是单纯为了查询结果排序。它是排序键，也是默认主键来源。

如果查询经常长这样：

```sql
SELECT
    event_name,
    count() AS total,
    sum(amount) AS revenue
FROM app_events
WHERE tenant_id = 10001
  AND app_id = 8
  AND event_name IN ('pay', 'refund')
  AND event_day >= {start_day:Date}
  AND event_day <= {end_day:Date}
GROUP BY event_name;
```

那排序键把 `tenant_id`、`app_id`、`event_name` 放在前面就很合理。查询条件命中排序键前缀，ClickHouse 才更容易用稀疏索引定位读取范围。

如果你把 `user_id` 放第一位，但大部分查询都是按租户、应用和事件名筛选，查询就可能读很多不必要的数据。

![MergeTree ORDER BY PARTITION BY 设计](https://zoytown-sg.oss-ap-southeast-1.aliyuncs.com/blogs/mergetree_key_partition_lxre.svg)

设计排序键时可以按这几个问题来想：

- 查询最常等值过滤哪些列
- 哪些列的过滤条件选择性强
- 哪些列放一起能提升压缩效果
- 排序键会不会太长，拖累写入和内存
- 是否需要单独写 `PRIMARY KEY`

多数场景不用单独写 `PRIMARY KEY`，因为 ClickHouse 会把排序键当作主键。只有你想让主键比排序键更短时，才考虑单独指定，而且主键必须是排序键前缀。

## 稀疏索引和 granule

MergeTree 的主键索引不是一行一个索引项，而是按 granule 建 marks。默认 `index_granularity` 是 `8192`，也就是大致每隔一批行记录一个 mark。

查询时，ClickHouse 根据排序键条件判断哪些 granule 值得读，哪些可以跳过。

这就是为什么它能扛巨大数据量：索引很小，常驻内存压力低，但依然能减少大量磁盘读取。

代价也很明确：它不是传统 OLTP 主键索引，不保证主键唯一，也不适合按任意单行随机查。你可以插入多行相同排序键的数据，这在明细分析里很常见。

## `PARTITION BY` 别切太碎

很多人刚用 ClickHouse 时，会把分区当成“越细越快”。这很容易翻车。

官方文档也提醒：分区不是替代 `ORDER BY` 的性能按钮，通常不需要过细分区，更不要拿客户 id、姓名这种高基数字段做分区。

合理分区的目标是：

- 方便批量删除或替换一批数据
- 让查询能跳过明显无关的数据块
- 控制 part 数量，不把后台合并压垮

常见写法：

```sql
PARTITION BY toYYYYMM(event_day)
```

如果数据量没那么大，也可以不写分区：

```sql
ENGINE = MergeTree
ORDER BY (tenant_id, app_id, event_name)
```

没有分区不丢人。乱分区才麻烦。

## part 合并：后台默默干活

MergeTree 插入会产生 part。小批量频繁写入会产生很多小 part，后台合并压力就会上来。

更推荐批量写入：

```sql
INSERT INTO app_events
SELECT ...
```

或者客户端一次塞一批行，不要一行一个 insert。

查看 part 状态：

```sql
SELECT
    table,
    partition,
    name,
    rows,
    bytes_on_disk,
    active
FROM system.parts
WHERE database = currentDatabase()
  AND table = 'app_events'
ORDER BY rows DESC
LIMIT 20;
```

如果活跃 part 数量长期很多，通常要检查写入批次、分区粒度和后台合并配置。

## 数据跳读索引：不是越多越好

除了主键稀疏索引，MergeTree 还支持二级数据跳读索引。

比如日志表里经常按 `trace_id` 查：

```sql
CREATE TABLE app_logs
(
    service LowCardinality(String),
    level LowCardinality(String),
    log_day Date,
    trace_id String,
    message String,
    attrs String,
    INDEX idx_trace trace_id TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(log_day)
ORDER BY (service, level, log_day);
```

`bloom_filter` 适合“判断某个值大概率不存在”的场景。它能帮忙跳过一批 granule，但不是万能索引。

跳读索引适合：

- 条件列不适合放进排序键前缀
- 过滤条件经常出现
- 数据分布能让索引跳过足够多数据

不适合：

- 低选择性列
- 几乎每次都要全表扫的查询
- 写入压力很高但索引收益很弱的表

建索引之前，先用真实查询验证收益。

## ReplacingMergeTree：去重不是立刻发生

`ReplacingMergeTree` 常被用来处理“同一业务 id 多次写入，最终保留一条”的场景。

```sql
CREATE TABLE user_profile
(
    user_id UInt64,
    version UInt64,
    name String,
    city LowCardinality(String),
    updated_ms UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;
```

它会在 part 合并过程中，对相同排序键的数据保留版本更大的行。

重点来了：去重发生在后台合并中，不是插入后马上全局生效。查询如果想得到合并后的结果，可以用：

```sql
SELECT *
FROM user_profile FINAL
WHERE user_id = 10001;
```

但 `FINAL` 可能比较重，不建议在高频大查询里随手加。更稳的办法是把使用场景设计清楚：明细表保留原始写入，最终状态表用 ReplacingMergeTree，查询层按需要取舍。

## SummingMergeTree 和 AggregatingMergeTree

如果你要做预聚合，可以看这两个引擎。

`SummingMergeTree` 会在合并时，把相同排序键下的数值列求和：

```sql
CREATE TABLE metric_daily
(
    tenant_id UInt64,
    metric_day Date,
    metric_name LowCardinality(String),
    value UInt64
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(metric_day)
ORDER BY (tenant_id, metric_day, metric_name);
```

它适合简单加和类指标。

更复杂的聚合状态，用 `AggregatingMergeTree`：

```sql
CREATE TABLE uv_state
(
    tenant_id UInt64,
    stat_day Date,
    event_name LowCardinality(String),
    users AggregateFunction(uniqCombined64, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(stat_day)
ORDER BY (tenant_id, stat_day, event_name);
```

写入聚合状态：

```sql
INSERT INTO uv_state
SELECT
    tenant_id,
    event_day AS stat_day,
    event_name,
    uniqCombined64State(user_id) AS users
FROM app_events
GROUP BY
    tenant_id,
    stat_day,
    event_name;
```

查询时合并状态：

```sql
SELECT
    tenant_id,
    stat_day,
    event_name,
    uniqCombined64Merge(users) AS uv
FROM uv_state
GROUP BY
    tenant_id,
    stat_day,
    event_name;
```

简单求和用 `SummingMergeTree`，复杂聚合状态用 `AggregatingMergeTree`。别拿普通 MergeTree 硬扛所有预聚合需求。

## ReplicatedMergeTree：生产更常见

单机表可以用 `MergeTree`。如果是多副本部署，生产环境通常会用 `ReplicatedMergeTree` 系列。

示例：

```sql
CREATE TABLE app_events_replica
(
    tenant_id UInt64,
    app_id UInt32,
    event_day Date,
    event_name LowCardinality(String),
    user_id UInt64
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/app_events_replica',
    '{replica}'
)
PARTITION BY toYYYYMM(event_day)
ORDER BY (tenant_id, app_id, event_name, event_day);
```

复制路径和副本名会依赖集群配置。不要在不了解集群拓扑的情况下复制粘贴生产建表 SQL。

## 连接 MySQL：临时查用 table function，长期用 engine 或落表

ClickHouse 官方提供 MySQL 表引擎和 `mysql` table function。MySQL 引擎支持对远端 MySQL 表执行 `SELECT` 和 `INSERT`。

临时查询：

```sql
SELECT *
FROM mysql(
    'mysql-host:3306',
    'shop',
    'orders',
    'reader',
    'reader_password'
)
LIMIT 10;
```

建一张外部表：

```sql
CREATE TABLE mysql_orders
(
    id UInt64,
    buyer_id UInt64,
    status String,
    amount Decimal(18, 2),
    order_day Date
)
ENGINE = MySQL(
    'mysql-host:3306',
    'shop',
    'orders',
    'reader',
    'reader_password'
);
```

把 MySQL 数据落到 MergeTree：

```sql
CREATE TABLE orders_local
(
    id UInt64,
    buyer_id UInt64,
    status LowCardinality(String),
    amount Decimal(18, 2),
    order_day Date
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(order_day)
ORDER BY (status, order_day, buyer_id, id);

INSERT INTO orders_local
SELECT
    id,
    buyer_id,
    status,
    amount,
    order_day
FROM mysql_orders;
```

外部表适合联邦查询、轻量同步、维表补充。真正高频分析，还是建议落到 MergeTree。

![ClickHouse 连接 MySQL Mongo](https://zoytown-sg.oss-ap-southeast-1.aliyuncs.com/blogs/clickhouse_mysql_mongo_bridge_vcjp.svg)

## MySQL 连接的一点细节

官方文档里提到，简单 `WHERE` 条件可以下推到 MySQL 侧，例如 `=`、`!=`、`>`、`>=`、`<`、`<=`。更复杂的条件可能会在 ClickHouse 侧处理，也就意味着远端读取量可能会变大。

生产配置建议用 named collection，避免密码到处散：

```sql
CREATE NAMED COLLECTION mysql_creds AS
    host = 'mysql-host',
    port = 3306,
    database = 'shop',
    user = 'reader',
    password = 'reader_password';

SELECT *
FROM mysql(mysql_creds, table = 'orders')
WHERE id = 10001;
```

如果要提高复用连接的能力，可以关注 MySQL 表引擎的连接池相关设置，比如 `connection_pool_size`、`connection_auto_close` 等。具体值不要拍脑袋，按并发和远端 MySQL 承受能力来调。

## 连接 MongoDB：更像只读外部源

MongoDB 表引擎是只读表引擎，用来读取远端 MongoDB collection。官方文档也说明，MongoDB table function 支持 `SELECT` 查询。

临时查询：

```sql
SELECT *
FROM mongodb(
    'mongo-host:27017',
    'shop',
    'events',
    'reader',
    'reader_password',
    'event_name String, user_id UInt64, amount Float64',
    'authSource=admin'
)
LIMIT 10;
```

建一张外部表：

```sql
CREATE TABLE mongo_events
(
    _id String,
    event_name String,
    user_id UInt64,
    amount Float64,
    raw String
)
ENGINE = MongoDB(
    'mongo-host:27017',
    'shop',
    'events',
    'reader',
    'reader_password',
    'authSource=admin',
    '_id'
);
```

如果用 URI：

```sql
CREATE TABLE mongo_events_uri
(
    _id String,
    event_name String,
    user_id UInt64,
    amount Float64
)
ENGINE = MongoDB(
    'mongodb://reader:reader_password@mongo-host:27017/shop?authSource=admin',
    'events',
    '_id'
);
```

把 MongoDB 数据落到 MergeTree：

```sql
CREATE TABLE events_local
(
    event_name LowCardinality(String),
    user_id UInt64,
    amount Float64
)
ENGINE = MergeTree
ORDER BY (event_name, user_id);

INSERT INTO events_local
SELECT
    event_name,
    user_id,
    amount
FROM mongo_events;
```

MongoDB 的文档结构比较灵活，ClickHouse 列结构比较硬。接入时最好先把字段结构整理清楚，嵌套对象可以转成 JSON 字符串，真正要高频分析的字段单独拆列。

## MongoDB 查询要留意支持范围

官方文档说明，MongoDB 连接只支持简单表达式，例如固定字段过滤、排序和限制行数。这些表达式会被转换到 MongoDB 侧执行。

如果表达式不受支持，可能导致更多数据被拉到 ClickHouse 侧处理。对大 collection 来说，这会很痛。

所以建议：

- 外部查询先加明确过滤条件
- `_id` 查询要处理好 oid 列
- 不要把 MongoDB 外部表当成高频分析主表
- 高频分析先抽到 MergeTree

## Python 写入 ClickHouse

Python 可以用 `clickhouse-connect` 操作 ClickHouse。

```bash
pip install clickhouse-connect
```

创建客户端：

```python
import clickhouse_connect


client = clickhouse_connect.get_client(
    host="127.0.0.1",
    port=8123,
    username="default",
    password="",
)
```

建表：

```python
client.command("""
CREATE TABLE IF NOT EXISTS py_events
(
    tenant_id UInt64,
    app_id UInt32,
    event_day Date,
    event_name LowCardinality(String),
    user_id UInt64,
    amount Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_day)
ORDER BY (tenant_id, app_id, event_name, event_day, user_id)
""")
```

批量写入：

```python
from datetime import date


sample_day = date.today()

rows = [
    [10001, 8, sample_day, "pay", 90001, 19.9],
    [10001, 8, sample_day, "refund", 90002, -9.9],
    [10001, 9, sample_day, "pay", 90003, 29.9],
]

client.insert(
    "py_events",
    rows,
    column_names=[
        "tenant_id",
        "app_id",
        "event_day",
        "event_name",
        "user_id",
        "amount",
    ],
)
```

查询：

```python
result = client.query("""
SELECT
    event_name,
    count() AS total,
    sum(amount) AS amount_sum
FROM py_events
WHERE tenant_id = {tenant_id:UInt64}
GROUP BY event_name
ORDER BY total DESC
""", parameters={"tenant_id": 10001})

for row in result.result_rows:
    print(row)
```

写入时尽量批量提交。MergeTree 不喜欢一行一行喂，小批量会带来很多小 part。

## Python 从 MySQL 抽取再写入 MergeTree

如果你不想直接用 MySQL 引擎，也可以在 Python 里自己抽取，再写入 ClickHouse。这种方式适合需要清洗、字段映射、质量校验的场景。

```bash
pip install pymysql clickhouse-connect
```

```python
from datetime import date

import clickhouse_connect
import pymysql


mysql_conn = pymysql.connect(
    host="mysql-host",
    port=3306,
    user="reader",
    password="reader_password",
    database="shop",
    cursorclass=pymysql.cursors.DictCursor,
)

ch = clickhouse_connect.get_client(
    host="127.0.0.1",
    port=8123,
    username="default",
    password="",
)

with mysql_conn.cursor() as cursor:
    start_day = date.today()
    cursor.execute("""
        SELECT id, buyer_id, status, amount, order_day
        FROM orders
        WHERE order_day >= %s
    """, (start_day,))
    rows = cursor.fetchall()

ch_rows = [
    [
        item["id"],
        item["buyer_id"],
        item["status"],
        item["amount"],
        item["order_day"],
    ]
    for item in rows
]

ch.insert(
    "orders_local",
    ch_rows,
    column_names=["id", "buyer_id", "status", "amount", "order_day"],
)
```

这条路线更啰嗦，但控制力更强。你可以在 Python 中做类型处理、异常重试、脏数据隔离，再把干净数据写进 MergeTree。

## Python 从 MongoDB 抽取再写入 MergeTree

MongoDB 的结构更灵活，Python 清洗通常更自然。

```bash
pip install pymongo clickhouse-connect
```

```python
import clickhouse_connect
from pymongo import MongoClient


mongo = MongoClient("mongodb://reader:reader_password@mongo-host:27017/shop")
collection = mongo["shop"]["events"]

ch = clickhouse_connect.get_client(
    host="127.0.0.1",
    port=8123,
    username="default",
    password="",
)

docs = collection.find(
    {"event_name": {"$in": ["pay", "refund"]}},
    {"event_name": 1, "user_id": 1, "amount": 1},
).limit(10000)

rows = []

for doc in docs:
    rows.append([
        doc.get("event_name", ""),
        int(doc.get("user_id", 0)),
        float(doc.get("amount", 0)),
    ])

ch.insert(
    "events_local",
    rows,
    column_names=["event_name", "user_id", "amount"],
)
```

MongoDB 抽取时尤其要小心字段缺失、类型不一致、嵌套结构。不要假设每个文档都长得一样。

## 一套比较稳的落地姿势

如果是分析系统，我更建议这样分层：

```text
MySQL / MongoDB：业务源
外部表或 Python 抽取：接入层
MergeTree 明细表：分析底座
Replacing / Summing / Aggregating：状态表和聚合表
视图或 BI：查询出口
```

外部表不是不能查，而是不要把它当核心分析存储。ClickHouse 真正的性能优势，还是来自 MergeTree 的列式存储、排序、索引和后台合并。

## 建表小抄

明细大表：

```sql
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_day)
ORDER BY (tenant_id, app_id, event_name, event_day, user_id)
```

最终状态：

```sql
ENGINE = ReplacingMergeTree(version)
ORDER BY business_id
```

简单加和预聚合：

```sql
ENGINE = SummingMergeTree
ORDER BY (tenant_id, metric_day, metric_name)
```

复杂聚合状态：

```sql
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, stat_day, event_name)
```

多副本：

```sql
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/table_name', '{replica}')
```

## 常见坑

`ORDER BY` 不等于展示排序，它是表的读取效率核心。

`PARTITION BY` 不要切得过细，尤其不要按高基数字段分区。

主键不唯一，不要拿 MergeTree 当 OLTP 唯一索引。

频繁小批写入会产生很多小 part，批量写入更友好。

`ReplacingMergeTree` 的去重依赖后台合并，查询一致性需求强时要谨慎使用 `FINAL`。

外部 MySQL/Mongo 查询不要不加过滤就扫大表。

MongoDB 文档结构要先整理，否则 ClickHouse 表结构会很难维护。