> ## Content Index
> Fetch the complete content index at: https://blog.vercanti.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# ClickHouse 完全指南
- URL: https://blog.vercanti.com/clickhouse-wan-quan-zhi-nan/
- Published: 2026-08-28T14:35:34.000Z
- Updated: 2026-08-28T14:59:05.000Z
- Description: ClickHouse 是列式存储数据库。与行式存储（MySQL、PostgreSQL）不同，列式存储将同一列的所有数据连续存放在磁盘上。 行式存储 vs 列式存储： ClickHouse 适用场景：日志分析、用户行为统计、监控指标聚合、实时报表。 MergeTree 是 ClickHouse 最重要的存储引擎族，所有生产场景几乎都用此系列。 最基础的引擎，数据按 ORDER BY 键排序存储，后台定期合并数据片段（parts）。 在后台合并时，对相同 ORDER BY 键的行进行去重，保留最新版本。用于模拟 UPSERT 语义。 注意：去重仅在后台合并时
- Author: yellowdog
- Tags: 数据库

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

---

## 目录

- [核心概念](#%E6%A0%B8%E5%BF%83%E6%A6%82%E5%BF%B5)
- [MergeTree 系列引擎](#mergetree-%E7%B3%BB%E5%88%97%E5%BC%95%E6%93%8E)
- [数据库与表操作](#%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%8E%E8%A1%A8%E6%93%8D%E4%BD%9C)
- [字段类型](#%E5%AD%97%E6%AE%B5%E7%B1%BB%E5%9E%8B)
- [数组函数](#%E6%95%B0%E7%BB%84%E5%87%BD%E6%95%B0)
- [查询语法](#%E6%9F%A5%E8%AF%A2%E8%AF%AD%E6%B3%95)
- [Python 客户端](#python-%E5%AE%A2%E6%88%B7%E7%AB%AF)
- [踩坑与注意事项](#%E8%B8%A9%E5%9D%91%E4%B8%8E%E6%B3%A8%E6%84%8F%E4%BA%8B%E9%A1%B9)

---

## 核心概念

### 列式存储原理

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

**行式存储 vs 列式存储**：

| 维度   | 行式存储（MySQL）     | 列式存储（ClickHouse） |
| ---- | --------------- | ---------------- |
| 读取方式 | 按行读取，每次读整行      | 按列读取，只读需要的列      |
| 写入性能 | 高（适合单行写入）       | 一般（适合批量写入）       |
| 聚合查询 | 慢（需扫描所有列）       | 快（只扫描聚合列）        |
| 压缩效率 | 低（同列数据类型相同但不连续） | 高（同列数据相似，压缩比高）   |
| 适用场景 | OLTP（事务处理）      | OLAP（分析查询）       |

### OLAP vs OLTP

| 特性    | OLAP                         | OLTP                          |
| ----- | ---------------------------- | ----------------------------- |
| 全称    | Online Analytical Processing | Online Transaction Processing |
| 典型操作  | 聚合、扫描大量行                     | 点查、单行增删改                      |
| 数据量   | 数亿到数百亿行                      | 通常数千万行以内                      |
| 写入模式  | 批量写入                         | 单行写入、更新、删除                    |
| 代表数据库 | ClickHouse、Doris、Hive        | MySQL、PostgreSQL              |
| 查询延迟  | 秒级（但扫描量大）                    | 毫秒级                           |

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

---

## MergeTree 系列引擎

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

### MergeTree

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

```sql
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 语义。

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

```

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

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

```

### SummingMergeTree

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

```sql
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` 类型）。通常配合物化视图使用。

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

```

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

```sql
-- 写入
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;

```

### 引擎对比

| 引擎                   | 合并时行为        | 适用场景          |
| -------------------- | ------------ | ------------- |
| MergeTree            | 仅排序合并，不去重不聚合 | 通用原始数据存储      |
| ReplacingMergeTree   | 去重，保留最新行     | 状态表、UPSERT 语义 |
| SummingMergeTree     | 对数值列求和       | 计数器、简单预聚合     |
| AggregatingMergeTree | 合并聚合函数中间状态   | 复杂预聚合、物化视图    |

### PARTITION BY 与 ORDER BY 的关系

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

两者关系：

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

---

## 数据库与表操作

### 创建数据库

```sql
CREATE DATABASE IF NOT EXISTS analytics;
USE analytics;

```

### CREATE TABLE 完整语法

```sql
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, ...];

```

**常用子句说明**：

| 子句           | 是否必须         | 说明                               |
| ------------ | ------------ | -------------------------------- |
| ENGINE       | 必须           | 指定存储引擎                           |
| ORDER BY     | MergeTree 必须 | 排序键，决定主键索引                       |
| PARTITION BY | 可选           | 分区表达式，建议按时间分区                    |
| PRIMARY KEY  | 可选           | 默认等于 ORDER BY，可设为其前缀             |
| TTL          | 可选           | 数据过期时间，支持删除或迁移到冷存储               |
| SETTINGS     | 可选           | 引擎级别参数，如 index\_granularity=8192 |

**TTL 示例**：

```sql
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

```sql
-- 新增列
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，后台异步合并。

```sql
-- 基础写入
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

```

---

## 字段类型

### 数值类型

| 类型            | 字节 | 范围               | 说明                  |
| ------------- | -- | ---------------- | ------------------- |
| UInt8         | 1  | 0 \~ 255         | 无符号整数               |
| UInt16        | 2  | 0 \~ 65535       | 无符号整数               |
| UInt32        | 4  | 0 \~ 4294967295  | 无符号整数               |
| UInt64        | 8  | 0 \~ 1.8×10¹⁹    | 无符号整数               |
| Int8          | 1  | \-128 \~ 127     | 有符号整数               |
| Int16         | 2  | \-32768 \~ 32767 | 有符号整数               |
| Int32         | 4  | \-2³¹ \~ 2³¹-1   | 有符号整数               |
| Int64         | 8  | \-2⁶³ \~ 2⁶³-1   | 有符号整数               |
| Float32       | 4  | IEEE 754 单精度     | 注意精度损失              |
| Float64       | 8  | IEEE 754 双精度     | 注意精度损失              |
| Decimal(P, S) | 变长 | 精确小数             | P=总位数，S=小数位数，金融场景使用 |

### 字符串类型

| 类型                  | 说明                                   |
| ------------------- | ------------------------------------ |
| String              | 变长字符串，无长度限制，UTF-8                    |
| FixedString(N)      | 固定长度 N 字节，不足则补零，适合 MD5/UUID 等定长值     |
| UUID                | 128 位 UUID，等价于 FixedString(16)，有专用函数 |
| Enum8('a'=1, 'b'=2) | 枚举，最多 256 个值，节省存储                    |
| Enum16(...)         | 枚举，最多 65536 个值                       |

### 时间类型

| 类型                              | 精度 | 范围                       | 说明         |
| ------------------------------- | -- | ------------------------ | ---------- |
| Date                            | 天  | 1970-01-01 \~ 2149-06-06 | 2 字节存储     |
| Date32                          | 天  | 1900-01-01 \~ 2299-12-31 | 4 字节存储     |
| DateTime                        | 秒  | 1970-01-01 \~ 2106-02-07 | 4 字节，可附带时区 |
| DateTime64(precision, timezone) | 亚秒 | 精度 0\~9（毫秒=3，微秒=6，纳秒=9）  | 8 字节       |

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

```

### 复合类型

| 类型                 | 示例                     | 说明                        |
| ------------------ | ---------------------- | ------------------------- |
| Array(T)           | Array(UInt32)          | 变长数组，元素类型必须一致             |
| Tuple(T1, T2, ...) | Tuple(String, UInt32)  | 固定长度异构元组                  |
| Map(K, V)          | Map(String, UInt64)    | 键值对，K 必须是基础类型             |
| Nullable(T)        | Nullable(String)       | 允许 NULL，有额外存储开销，尽量避免      |
| LowCardinality(T)  | LowCardinality(String) | 低基数列字典编码，适合枚举值少的 String 列 |

---

## 数组函数

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

### arrayJoin

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

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

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

```

### arrayMap

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

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

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

```

### arrayFilter

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

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

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

```

### arraySum

对数组元素求和。

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

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

```

### 其他常用数组函数

| 函数                           | 说明                         |
| ---------------------------- | -------------------------- |
| length(arr)                  | 数组长度                       |
| arrayFirst(f, arr)           | 返回第一个满足条件的元素               |
| arrayExists(f, arr)          | 是否存在满足条件的元素                |
| arrayAll(f, arr)             | 是否所有元素都满足条件                |
| indexOf(arr, x)              | 返回元素 x 的下标（从 1 开始），不存在返回 0 |
| has(arr, x)                  | 数组是否包含元素 x                 |
| arrayUniq(arr)               | 数组唯一值数量                    |
| arrayDistinct(arr)           | 去重后的数组                     |
| arraySort(arr)               | 升序排序                       |
| arrayReverse(arr)            | 反转数组                       |
| arraySlice(arr, offset, len) | 切片                         |
| array\_concat(arr1, arr2)    | 拼接两个数组                     |

---

## 查询语法

### SELECT 基础与 MySQL 的差异

```sql
-- 基础查询结构（与 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 的主要差异**：

| 差异点      | MySQL           | ClickHouse                    |
| -------- | --------------- | ----------------------------- |
| count()  | 需写 count(\*)    | count() 即可（等价 count(\*)）      |
| GROUP BY | 必须列出所有非聚合列      | 同 MySQL，但支持列编号 GROUP BY 1, 2  |
| NULL 处理  | 标准 SQL          | 大量函数对 NULL 行为不同，尽量避免 Nullable |
| 子查询      | 灵活              | 支持但性能不如 JOIN，复杂子查询建议改写        |
| 大小写      | 关键字不区分大小写       | 同 MySQL                       |
| 字符串引号    | 单引号为字符串，双引号为标识符 | 同 MySQL                       |
| 反引号      | 用于标识符           | 用于标识符（同 MySQL）                |

### 聚合函数

| 函数                        | 说明                   |
| ------------------------- | -------------------- |
| count()                   | 行数                   |
| count(col)                | 非 NULL 值的行数          |
| sum(col)                  | 求和                   |
| avg(col)                  | 平均值                  |
| min(col)                  | 最小值                  |
| max(col)                  | 最大值                  |
| uniq(col)                 | 去重计数（近似，HyperLogLog） |
| uniqExact(col)            | 精确去重计数（内存消耗大）        |
| quantile(level)(col)      | 近似分位数，level 为 0\~1   |
| quantileExact(level)(col) | 精确分位数                |
| groupArray(col)           | 将列值聚合成数组             |
| groupUniqArray(col)       | 去重后聚合成数组             |
| argMax(val, ts)           | 返回 ts 最大时对应的 val     |
| argMin(val, ts)           | 返回 ts 最小时对应的 val     |

```sql
-- 分位数示例
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

```sql
-- 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 兼容。

```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 特有的优化机制。

| 特性    | WHERE      | PREWHERE                              |
| ----- | ---------- | ------------------------------------- |
| 执行时机  | 读取所有指定列后过滤 | 先读过滤列，过滤后再读其余列                        |
| 适用场景  | 通用         | 过滤性强的条件 + 查询列较多时                      |
| 数据读取量 | 全部查询列都读取   | 过滤列先读，被过滤掉的行的其他列不读                    |
| 自动优化  | \-         | ClickHouse 会自动将部分 WHERE 条件转为 PREWHERE |

```sql
-- 手动使用 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

```sql
-- 基础 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 溢出到磁盘（默认配置）。

```sql
-- 基础 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 类型**：

| JOIN 类型          | 说明                           |
| ---------------- | ---------------------------- |
| INNER JOIN       | 取交集，与 MySQL 相同               |
| LEFT OUTER JOIN  | 左表所有行，右表无匹配为 NULL            |
| RIGHT OUTER JOIN | 右表所有行，左表无匹配为 NULL            |
| FULL OUTER JOIN  | 两表并集                         |
| CROSS JOIN       | 笛卡尔积                         |
| LEFT SEMI JOIN   | 左表中在右表有匹配的行（不展开右表）           |
| LEFT ANTI JOIN   | 左表中在右表无匹配的行                  |
| LEFT ANY JOIN    | 右表只取第一条匹配（不重复）               |
| ASOF JOIN        | 时间序列近似匹配 JOIN（ClickHouse 特有） |

**ClickHouse JOIN 注意事项**：

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

---

## Python 客户端

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

```bash
pip install clickhouse-driver

```

```python
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` 构造参数：

| 参数                     | 类型       | 默认值         | 说明              |
| ---------------------- | -------- | ----------- | --------------- |
| host                   | str      | 'localhost' | 服务器地址           |
| port                   | int      | 9000        | 原生 TCP 端口       |
| database               | str      | 'default'   | 默认数据库           |
| user                   | str      | 'default'   | 用户名             |
| password               | str      | ''          | 密码              |
| connect\_timeout       | int      | 10          | 连接超时（秒）         |
| send\_receive\_timeout | int      | 300         | 读写超时（秒）         |
| compression            | bool/str | False       | 传输压缩（lz4/zstd）  |
| settings               | dict     | {}          | ClickHouse 查询设置 |

### asynch（异步，原生 TCP 协议）

```bash
pip install asynch

```

```python
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 格式输出，适合数据科学场景。

```bash
pip install clickhouse-connect

```

```python
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` 错误，后台合并压力大。

```python
# 错误做法：逐行插入
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()

```

**批量插入建议**：

| 建议           | 说明                                   |
| ------------ | ------------------------------------ |
| 单次插入行数       | 1000 \~ 100000 行                     |
| 插入频率         | 每秒不超过 1\~2 次（同一张表）                   |
| 使用缓冲区        | 应用层累积后批量提交                           |
| 考虑 Buffer 引擎 | ClickHouse Buffer 引擎在内存中缓冲写入，定期刷入目标表 |

---

## 踩坑与注意事项

### 不支持事务

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

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

### UPDATE/DELETE 代价极高

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

```sql
-- 这两个操作都是异步重写 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`。跨时区部署时需要明确指定时区：

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

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

```

### 数据采样（SAMPLE BY）

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

```sql
-- 只扫描 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 会造成大量小文件影响合并性能。

```sql
-- 批量插入（推荐）
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` 函数合并中间状态，避免每次全表扫描。

```sql
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` 引擎中间层聚合后批量落盘。

```sql
-- 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基础完全指南](https://blog.vercanti.com/mysql-ji-chu-wan-quan-zhi-nan/)  
[PostgreSQL完全指南](https://blog.vercanti.com/postgresql-wan-quan-zhi-nan/)