第 9 章 JOIN 与字典

学习目标：彻底搞清楚「为什么大家说 ClickHouse 不擅长 JOIN」，掌握 8 种 JOIN 类型、5 种 JOIN 算法、ASOF JOIN 的时序匹配神技；学会用 Dictionary 字典作为 JOIN 的最佳替代，干掉 90% 的「右表 KV 查询」。学完之后你能给业务画一棵「该不该 JOIN，该用什么 JOIN」的决策树。

0. 引子：「ClickHouse 不擅长 JOIN」是什么意思

某团队把 MySQL 的 5 张表照搬到 ClickHouse，每天跑 BI:
  SELECT u.name, p.title, c.category, count()
  FROM events e
  JOIN users    u ON e.uid = u.id
  JOIN products p ON e.sku = p.sku
  JOIN catalog  c ON p.cat = c.id
  GROUP BY 1,2,3;

→ 跑 30 秒、爆 32 GB 内存、有时直接 OOM；
→ 同样的 SQL 在 PG 里 3 秒返回，团队怀疑 ClickHouse 是骗人的。

事实是：
  · ClickHouse 的 JOIN 默认把【右表全部加载到内存】做 hash 表
  · 上面 SQL 的右表 = users + products + catalog 全表
  · 几张表加起来千万行，hash 表本身就十几 GB
  · 而且 CK 几乎不重排 JOIN 顺序、不下推谓词进 hash 端

正确姿势：
  · users / products / catalog 全部做成 Dictionary
  · SELECT dictGetString('user_dict','name', uid) ...
  · 内存常驻、O(1) 查询、零拷贝、毫秒级出结果

这就是这一章的全部主题：JOIN 的边界 + 字典的妙用。

9.1 ClickHouse 为什么对 JOIN 这么「冷淡」？

9.1.1 一段类比：图书馆做联表查询

MySQL/PG 像「全能图书管理员」：
  · 知道每本书的索引页码（主键聚簇）
  · 能根据「借书人 ↔ 书」两边索引互相 nested loop 扫
  · 大表可以 sort-merge join，索引下推谓词
  · 优化器会 "重排 JOIN 顺序"：先把小的过滤完再 JOIN

ClickHouse 像「列存仓库管理员」：
  · 主键只是稀疏索引，不是「行查找」用的
  · JOIN 默认只会一招：把右表搬到内存做 hash 表
  · 不重排 JOIN 顺序，按你写的左→右顺序执行
  · 更适合「一张大宽表 + 一两个小字典」的姿势

9.1.2 默认 hash join 的代价

SELECT ... FROM big AS L  JOIN  big2 AS R  ON L.k = R.k;

执行计划（默认）：
  ┌─ build phase ─┐
  │  把 R 全部 SELECT 出来                   ← 扫一遍 R
  │  对每行用 hash(R.k) 放进哈希表           ← 内存 = R 的大小 × 系数
  └────────────────┘
  ┌─ probe phase ─┐
  │  扫描 L                                   ← 流式
  │  每行用 hash(L.k) 去查右表 hash           ← O(1) 命中
  │  匹配则输出                               ← 流式输出
  └────────────────┘

内存 = 整个右表的大小（× hash 表 load factor 系数）。
右表越大越糟，几亿行的右表直接 OOM。
CK 默认不会自动选小表当右表（不重排），写错 SQL 就得吃苦头。

📌 第一条军规：永远把小表写在 JOIN 右边！（大数据用语「左大右小」）

9.2 JOIN 类型全家福

9.2.1 标准 JOIN

类型	含义	等价
`INNER JOIN`	双方都有匹配才输出	默认（不写就是 INNER）
`LEFT JOIN`	左表全保留，右无匹配补 NULL/默认值	`LEFT OUTER JOIN`
`RIGHT JOIN`	右表全保留	`RIGHT OUTER JOIN`
`FULL JOIN`	双侧无匹配都补 NULL	`FULL OUTER JOIN`
`CROSS JOIN`	笛卡尔积	`,`

9.2.2 ClickHouse 特有：`ANY` / `ALL` 修饰符

sql

-- ALL（默认）：右表多匹配会复制左行（标准 SQL 语义）
SELECT L.id FROM L ALL INNER JOIN R USING(id);

-- ANY：右表只取一条匹配，不复制左行
SELECT L.id FROM L ANY INNER JOIN R USING(id);

ANY 适合「我只想知道有没有匹配 + 用一个值」，能避免数据膨胀，速度更快。

9.2.3 `SEMI` / `ANTI` JOIN

sql

-- SEMI：左表中在右表存在的（不复制行、不取右表列）
SELECT L.* FROM L LEFT SEMI JOIN R USING(id);
-- 等价于 SELECT L.* FROM L WHERE id IN (SELECT id FROM R)

-- ANTI：左表中在右表不存在的
SELECT L.* FROM L LEFT ANTI JOIN R USING(id);
-- 等价于 SELECT L.* FROM L WHERE id NOT IN (SELECT id FROM R)

9.2.4 `ASOF JOIN`：时序匹配神器 ⭐

「按最近时间匹配」—— 时序场景必备。

sql

-- 表 trades：每笔交易的成交价
-- 表 quotes：每秒的最新报价
-- 想给每笔 trade 找「成交时刻最近的报价」

SELECT
    t.symbol,
    t.ts          AS trade_ts,
    t.price       AS trade_price,
    q.ts          AS quote_ts,
    q.bid, q.ask
FROM trades AS t
ASOF LEFT JOIN quotes AS q
  ON t.symbol = q.symbol
  AND t.ts >= q.ts;     -- 最后一个不等条件用作 ASOF 匹配键

匹配规则：

等值条件可以有多个（如 symbol = symbol）；
最后一个不等条件是 ASOF 键，CK 会找「右表满足该不等的最近一条」；
支持 >=、>、<=、< 四种比较符。

📌 典型场景：股票报价对齐、IoT 传感器数据补值、广告归因（最近一次曝光）、A/B 实验事件归一化。

9.2.5 `GLOBAL JOIN`：分布式查询救命符

在分布式表（Distributed）查询里，普通 JOIN 会被每个分片独立执行一次，意味着右表会在每个分片上各自重新算一遍 → N×N 子查询风暴。

sql

SELECT * FROM dist_events e
JOIN (SELECT id, name FROM users) u   -- ❌ 每个分片都算一次右子查询
ON e.uid = u.id;

SELECT * FROM dist_events e
GLOBAL JOIN (SELECT id, name FROM users) u   -- ✅ coordinator 算一次再广播
ON e.uid = u.id;

GLOBAL 让 coordinator 节点先把右表算出来，然后广播到所有分片做 hash join，避免 N×N 风暴。

📌 代价：右子查询结果会传到每个分片，所以右表必须小（几万行级），否则网络爆炸。

9.3 JOIN 算法切换：`SETTINGS join_algorithm`

ClickHouse 实际上提供了 5 种 JOIN 算法，可以用 SETTINGS 切换。

sql

SELECT * FROM L JOIN R ON L.k = R.k
SETTINGS join_algorithm = 'parallel_hash';

算法	关键字	工作原理	适用
`hash`	默认	右表全建 hash 表	右表能塞内存
`parallel_hash`	`parallel_hash`	右表分桶并行 build hash	右表大但能塞内存
`partial_merge`	`partial_merge`	双侧排序后 merge join	右表巨大塞不下，能 spill
`grace_hash`	`grace_hash`	右表分桶 + 必要时 spill 到磁盘	巨型右表，避免 OOM
`direct`	`direct`	右表是 Dictionary 或 EmbeddedRocksDB，直接 KV 查	右表 = 字典或 KV 引擎
`auto`	`auto`	自动尝试 hash → grace_hash	不确定时

一段经验

日常 ad-hoc：    join_algorithm = 'auto'              ← 默认安全
明确小右表：     join_algorithm = 'hash'              ← 最快
明确大右表：     join_algorithm = 'parallel_hash'     ← 内存够
内存不够：       join_algorithm = 'grace_hash'        ← 牺牲速度换稳定
右表是字典：     join_algorithm = 'direct'            ← 极致快

内存阈值与溢出策略

sql

SELECT ... SETTINGS
    max_bytes_in_join = 5000000000,         -- 5 GB
    join_overflow_mode = 'break';           -- 超了直接停 (vs 'throw')

join_overflow_mode = 'break' 表示「超内存就把右表读到此为止继续 probe」，结果会不完整，要小心使用；要么用 grace_hash 牺牲速度但完整。

9.4 字典 Dictionary：JOIN 的最佳替代

9.4.1 一段类比：身份证 → 姓名查询表

JOIN 像「现场翻档案柜」：
  来一个客户，跑去档案柜里翻
  人多了档案柜里挤满临时复印件（hash 表）

字典像「服务台前的老黄历」：
  服务台始终有一本「身份证 → 姓名」对照表
  来一个客户，3 秒翻表答上来
  这本表是【常驻内存的、由后台周期性同步的】

ClickHouse 的字典本质：

┌──────────────────────────────────┐
│  Dictionary：常驻内存的 KV         │
│                                    │
│  · 由 Dictionary Source 加载       │
│    (MySQL / PG / HTTP / FILE / CK)│
│                                    │
│  · 按 LIFETIME 周期性刷新           │
│                                    │
│  · 通过 dictGet 函数 O(1) 查询     │
│                                    │
│  · 比 JOIN 快 10x ~ 100x          │
└──────────────────────────────────┘

9.4.2 创建一个字典

sql

CREATE DICTIONARY learn_ck.user_dict
(
    id   UInt64,
    name String,
    city String,
    vip  UInt8 DEFAULT 0
)
PRIMARY KEY id
SOURCE(MYSQL(
    host 'mysql' port 3306
    user 'reader' password '***'
    db 'shop' table 'users'
    invalidate_query 'SELECT max(updated_at) FROM users'
))
LAYOUT(HASHED())
LIFETIME(MIN 300 MAX 600);

SOURCE：数据源，可以是 MySQL / PG / ClickHouse / HTTP / FILE / EXECUTABLE。
LAYOUT：在内存里如何组织（详见 9.4.4）。
LIFETIME(MIN m MAX M)：每 m 秒检查一次源，如果 invalidate_query 结果变了就在 ≤ M 秒内重新拉取。

9.4.3 字典源（SOURCE）九大类

sql

SOURCE(MYSQL(host '...' port 3306 user '...' password '...' db '...' table '...'))
SOURCE(POSTGRESQL(host '...' port 5432 user '...' password '...' db '...' table '...'))
SOURCE(CLICKHOUSE(host '...' port 9000 user '...' password '...' db '...' table '...'))
SOURCE(HTTP(url 'http://api/users.tsv' format 'TabSeparated'))
SOURCE(FILE(path '/var/data/dict.csv' format 'CSV'))
SOURCE(REDIS(host '...' port 6379 storage_type 'simple' db_index 0))
SOURCE(MONGODB(host '...' port 27017 db '...' collection '...'))
SOURCE(EXECUTABLE(command 'cat /path/dict.tsv' format 'TabSeparated'))
SOURCE(EXECUTABLE_POOL(command 'python3 lookup.py' format 'TabSeparated'))   -- 进程池，按需查询

9.4.4 字典布局（LAYOUT）选型

布局	内存模型	适用
`FLAT`	数组按 id 直接索引	id 范围连续小（< 50 万），最快
`HASHED`	hash 表	默认推荐，几百万行也行
`SPARSE_HASHED`	稀疏 hash	行数多 + 内存敏感
`COMPLEX_KEY_HASHED`	复合主键	主键多列
`RANGE_HASHED`	区间映射	「某天某小时之间用这套配置」
`IP_TRIE`	IP 前缀树	IP → 地理位置
`POLYGON`	多边形 R 树	经纬度 → 城市
`CACHE`	LRU cache，按需从 source 拉	字典超大、热点稀疏
`COMPLEX_KEY_CACHE`	复合主键 + cache	同上
`SSD_CACHE`	SSD 持久化 cache	字典 > 内存
`DIRECT`	不缓存，每次查都问 source	不推荐，调试用

9.4.5 字典查询函数：`dictGet*`

sql

SELECT
    e.uid,
    dictGetString('learn_ck.user_dict', 'name', e.uid)        AS user_name,
    dictGetString('learn_ck.user_dict', 'city', e.uid)        AS city,
    dictGetUInt8 ('learn_ck.user_dict', 'vip',  e.uid)        AS is_vip,
    dictGetOrNull('learn_ck.user_dict', 'name', e.uid)        AS name_or_null,
    dictHas      ('learn_ck.user_dict', e.uid)                AS exists
FROM events AS e
WHERE toDate(e.ts) = today()
LIMIT 100;

类型化版本：

dictGetString / dictGetUInt8 / dictGetUInt32 / dictGetInt64 / dictGetFloat64
/ dictGetDate / dictGetDateTime / dictGetUUID / dictGetIPv4

通用版本（自动推断类型）：

sql

dictGet(dict_name, attr_name, key)
dictGetOrDefault(dict_name, attr_name, key, default)

9.4.6 字典 vs JOIN 性能对比

sql

-- A) 用 JOIN
SELECT u.name, count()
FROM events e JOIN users u ON e.uid = u.id
GROUP BY u.name;
-- → 5.2 s, peak memory 4.8 GB

-- B) 用字典
SELECT dictGetString('user_dict','name', e.uid) AS name, count()
FROM events e
GROUP BY name;
-- → 0.3 s, peak memory 80 MB

差距：耗时 17×，内存 60×。这就是为什么字典是 ClickHouse 的「JOIN 杀器」。

9.4.7 字典在 JOIN 的另一种用法：`join_algorithm='direct'`

sql

SELECT e.*, u.name, u.city
FROM events e LEFT JOIN learn_ck.user_dict AS u ON e.uid = u.id
SETTINGS join_algorithm = 'direct';

ClickHouse 识别右表是字典时，直接走 dictGet 的代码路径，效果等同于上面的 dictGet 调用，但 SQL 兼容标准 JOIN 写法。

9.5 真实案例

9.5.1 订单表 + 商品字典

sql

-- 订单事件表（亿级）
CREATE TABLE learn_ck.orders
(
    ts        DateTime,
    order_id  UInt64,
    uid       UInt64,
    sku_id    UInt64,
    qty       UInt32,
    amount    Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (uid, ts);

-- 商品维表（百万级），用字典
CREATE DICTIONARY learn_ck.product_dict
(
    sku_id      UInt64,
    title       String,
    category    String,
    brand       String,
    price       Decimal(18, 2)
)
PRIMARY KEY sku_id
SOURCE(CLICKHOUSE(host 'localhost' port 9000 user 'default' db 'learn_ck' table 'products'))
LAYOUT(HASHED())
LIFETIME(MIN 600 MAX 1200);

-- 报表 SQL：每个类目销量 Top 10
SELECT
    dictGetString('learn_ck.product_dict', 'category', sku_id) AS cat,
    dictGetString('learn_ck.product_dict', 'title',    sku_id) AS title,
    sum(qty)    AS qty,
    sum(amount) AS gmv
FROM learn_ck.orders
WHERE toDate(ts) = today()
GROUP BY cat, title
ORDER BY cat, gmv DESC
LIMIT 10 BY cat;

LIMIT 10 BY cat 配合字典，一句出每类目 Top 10，全程不用 JOIN。

9.5.2 IP → 地理位置（IP_TRIE）

sql

CREATE DICTIONARY learn_ck.geo_ip
(
    prefix     String,
    country    String,
    city       String,
    latitude   Float64,
    longitude  Float64
)
PRIMARY KEY prefix
SOURCE(FILE(path '/var/lib/clickhouse/user_files/geo_ip.csv' format 'CSVWithNames'))
LAYOUT(IP_TRIE())
LIFETIME(MIN 86400 MAX 86400);

-- 查询
SELECT
    dictGet('learn_ck.geo_ip', ('country', 'city'), tuple(IPv4StringToNum('8.8.8.8'))) AS geo;
-- → ('US','Mountain View')

IP_TRIE 内部是一棵 IP 前缀树（CIDR 路由表），查询是 O(IP 长度) 而不是 O(N)。

9.6 📌 与 MySQL/PG 优化器 JOIN 的对比

维度	MySQL InnoDB	PostgreSQL	ClickHouse
JOIN 算法	nested loop / BNL（8.0+ hash）	nested loop / hash / merge	hash 为主（外加 grace_hash / parallel_hash / partial_merge / direct）
是否重排 JOIN 顺序	✓（cost-based）	✓（基于统计的 dynamic programming）	几乎不重排，按你写的左→右执行
谓词下推到 hash 端	✓	✓	部分（要看版本与子查询形式）
索引帮助 JOIN	强（聚簇 / 二级索引）	强（B-Tree / Hash / GiST）	弱（主键是稀疏索引，不能行级查）
大右表 OOM 风险	较低（流式 BNL）	较低（spill 到磁盘）	高（默认 hash 全装内存，要手动切 grace_hash）
替代方案	加索引 / 重写 SQL	加索引 / 物化视图	Dictionary / 物化视图 / 宽表
字典原生支持	无	无（用扩展 dblink）	DICTIONARY 一等公民
时序匹配	无 ASOF	无 ASOF（社区扩展 timescale）	ASOF JOIN 内建

核心心法：

MySQL/PG 的 JOIN 是优化器替你想；ClickHouse 的 JOIN 是你替优化器想。能用宽表 / 字典 / 物化视图替代 JOIN 的，就别 JOIN。

9.7 决策树：到底该 JOIN 还是 Dictionary？

┌── 右表行数 < 1000 万 且 主要是「KV 查询」？
│       └── ✓ → Dictionary（HASHED 布局）
│       └── ✗ → 继续
│
├── 右表是 IP / 经纬度 / 区间映射？
│       └── ✓ → Dictionary（IP_TRIE / POLYGON / RANGE_HASHED）
│       └── ✗ → 继续
│
├── 右表是「时序数据，按时间点匹配」？
│       └── ✓ → ASOF JOIN
│       └── ✗ → 继续
│
├── 分布式表 + 小右表？
│       └── ✓ → GLOBAL JOIN
│       └── ✗ → 继续
│
├── 内存能装下右表？
│       └── ✓ → 普通 hash JOIN（小表写右）
│       └── ✗ → grace_hash JOIN 或 partial_merge
│
└── 还嫌慢？
        └── 改造成「**宽表**」（写入时把维度打平到事实表）

9.8 本章小结

┌────────────────────────────────────────────────────────────┐
│                       关键拍板（背下来）                       │
├────────────────────────────────────────────────────────────┤
│ ① CK 默认 hash JOIN：右表全装内存 → 第一军规：小表写右         │
│                                                             │
│ ② JOIN 类型 8 种：INNER/LEFT/RIGHT/FULL/CROSS/ANY/SEMI/ANTI │
│   ASOF：时序最近匹配神器                                      │
│   GLOBAL：分布式表的右表广播                                  │
│                                                             │
│ ③ JOIN 算法 5 种：hash / parallel_hash / partial_merge /    │
│   grace_hash / direct (字典)                                │
│                                                             │
│ ④ 内存控制：max_bytes_in_join + join_overflow_mode           │
│                                                             │
│ ⑤ Dictionary 是 90% 「JOIN 维表」场景的最佳解                  │
│   · SOURCE：MySQL / PG / CK / HTTP / FILE / EXECUTABLE      │
│   · LAYOUT：FLAT / HASHED / IP_TRIE / RANGE_HASHED / CACHE  │
│   · 函数：dictGet / dictHas / dictGetOrDefault              │
│   · 比 JOIN 快 10~100×，内存少 50×                            │
│                                                             │
│ ⑥ 真不能字典 → 物化视图 → 宽表 → 才考虑 JOIN                  │
└────────────────────────────────────────────────────────────┘

9.9 面试高频题

Q1：为什么大家说「ClickHouse 不擅长 JOIN」？

考察点：底层执行模型理解。

标准答案：

默认算法是 hash join，build phase 把整个右表装进内存的 hash 表。右表越大，内存代价越线性增长。
几乎不重排 JOIN 顺序：MySQL/PG 优化器会基于统计信息把小表放右边、把过滤条件下推；ClickHouse 几乎完全按你写的左→右执行，写错 SQL 直接 OOM。
没有「行级索引」帮助 JOIN：MergeTree 的主键只是稀疏索引，不能像 InnoDB 聚簇 / PG B-Tree 那样做 nested loop + 索引下推。
分布式 JOIN 默认每个分片各自重算右子查询，需要显式加 GLOBAL 才会让 coordinator 广播。
谓词下推不完整：复杂子查询 + JOIN 时，过滤可能不会推到 hash 构建端。

正确姿势：

把维表做成 Dictionary（首选）；
写入时把维度打平成宽表；
物化视图预聚合；
必须 JOIN 时把小表写右、用 parallel_hash 或 grace_hash 控内存。

加分项：能补一句「24.x 后 ClickHouse 在 query optimization 层面增加了一些 JOIN reorder 实验性功能（query_plan_enable_optimizations），但生产仍要手写顺序」。

易错点：把「不擅长 JOIN」理解成「ClickHouse 完全不能 JOIN」 —— 实际可以，只是要小心。

Q2：`hash` / `parallel_hash` / `grace_hash` / `partial_merge` 怎么选？

考察点：JOIN 算法的具体差别与适用场景。

标准答案：

算法	内存	速度	何时用
`hash`	高（右表大小）	最快	右表能轻松塞内存
`parallel_hash`	高	更快（并行 build）	右表大但内存够，多核利用
`partial_merge`	中（双侧排序后流式）	中	内存有限，右表也大
`grace_hash`	低（分桶 + 必要时 spill 到磁盘）	慢	巨型右表防 OOM
`direct`	极低	极快	右表是 Dictionary 或 EmbeddedRocksDB

选择策略：

默认用 auto，先 hash，OOM 时自动切 grace_hash。
明确知道右表能装下 → 显式 hash 拿最快。
多核机器 + 大右表 → parallel_hash。
必须避免 OOM → grace_hash（牺牲耗时）。
右表是字典 → direct，毫秒返回。

加分项：能解释 grace_hash 的工作原理 —— 把双侧按 hash 值分桶，每个桶独立 build/probe，桶超内存就 spill 到磁盘文件，类似 Spark / Hadoop 的 hash join 实现。

易错点：以为 partial_merge 是 sort-merge join 的简单版，其实是 ClickHouse 的特殊算法，会 spill 到 disk 但比 grace_hash 多一次排序成本。

Q3：`ASOF JOIN` 是什么？什么场景下必用？

考察点：是否做过时序场景。

标准答案：

ASOF JOIN 解决「给左表每行找右表里时间最近的一条」的问题，比如：

给每笔交易匹配「成交时刻最近的一条报价」；
给每条 IoT 测量值匹配「同设备同时刻的环境数据」；
给每条曝光匹配「最近一次相关曝光做归因」。

语法：

sql

SELECT t.*, q.price
FROM trades t
ASOF LEFT JOIN quotes q
  ON t.symbol = q.symbol AND t.ts >= q.ts;

关键约定：

等值条件可以多个（symbol = symbol 等）；
最后一个不等条件是 ASOF 键，比较符可为 >=、>、<=、<；
右表必须按 ASOF 键有序（CK 内部会确保），所以 ORDER BY 含 ts 时性能最优；
没有匹配（即时间在右表全部记录之前）时，ASOF LEFT JOIN 该列填 NULL/默认值，ASOF INNER JOIN 直接丢弃。

加分项：

能补「ASOF 在金融行业是标配，KDB+ / TimescaleDB 也都有类似算子」。
能提到「ASOF JOIN 当前不能与 OR 条件混用，且不支持 FULL ASOF」。

易错点：把 ASOF 写在「等值条件之间」 —— 必须写在最后一个条件。

Q4：什么是 `GLOBAL JOIN`？为什么分布式查询要用它？

考察点：理解 Distributed 表与 fan-out 执行模型。

标准答案：

ClickHouse 的 Distributed 表把查询 fan-out 到所有分片，每个分片各自执行完整 SQL，再把结果汇总到 coordinator。
普通 JOIN：每个分片独立执行右子查询，意味着右表查询要跑 N 次（N 个分片），如果右子查询本身也含 Distributed 表，就是 N×N 子查询风暴。
GLOBAL JOIN 让 coordinator 先在本地把右子查询算完，得到一个临时表，广播到所有分片，分片直接用这份临时表做 hash join，不再 N 次重算。
代价：广播的右表大小 × 分片数的网络开销，所以右表必须小（一般几万行级）。
对 IN 子查询同样可用 GLOBAL IN。

加分项：能补「现代实践更推荐用 Dictionary 替代 GLOBAL JOIN：字典天生在每个节点都常驻一份，查询走 dictGet，零网络成本」。

易错点：把 GLOBAL 用在了大右表上，导致广播阶段把网络打爆。

Q5：什么是字典 Dictionary？跟 JOIN 比什么时候用？

考察点：核心心法 —— 用字典替代 JOIN。

标准答案：

字典是 ClickHouse 内建的「常驻内存的 KV 表」：

通过 CREATE DICTIONARY 声明，从外部 source（MySQL/PG/HTTP/FILE/CK）加载到内存；
按 LIFETIME 周期性刷新，可以配 invalidate_query 增量判断；
不同 LAYOUT 适应不同 KV 模式（HASHED、IP_TRIE、RANGE_HASHED、CACHE 等）；
通过 dictGet* 系列函数 O(1) 查询；
也支持以 LEFT JOIN dict_name + join_algorithm='direct' 形式参与标准 SQL JOIN。

与 JOIN 对比：

维度	字典	JOIN
内存	常驻 + 共享	每次 JOIN 都建 hash 表
查询耗时	O(1) 查表	O(右表) build + 探查
数据新鲜度	受 LIFETIME 控制（秒~分钟级延迟）	实时
适用	低更新频率的维表（用户、商品、IP 库）	实时 / 频繁变化 / 复杂关联

结论：维表查询 90% 的场景用字典，剩下 10% 用 JOIN。

加分项：

能列举布局：HASHED（默认）、FLAT（连续小 id）、IP_TRIE（IP 库）、RANGE_HASHED（区间映射）、CACHE（超大字典 LRU）。
能提到 EXECUTABLE_POOL 源用 Python 脚本动态返回字典数据。

易错点：把超大表（亿级）也做成字典 —— 内存爆炸，应该用宽表打平或 CACHE 布局。

Q6：右表是百万级维表，怎么避免 JOIN OOM？

考察点：实战调优。

标准答案（按推荐顺序）：

首选改造成 Dictionary：

sql

CREATE DICTIONARY user_dict(...) SOURCE(...) LAYOUT(HASHED()) LIFETIME(...);
SELECT dictGetString('user_dict','name',uid) FROM events;

不能字典则切算法：

sql

SELECT ... SETTINGS join_algorithm = 'parallel_hash';
-- 或更稳的
SELECT ... SETTINGS join_algorithm = 'grace_hash',
                    grace_hash_join_initial_buckets = 16;

限内存 + 控制行为：

sql

SET max_bytes_in_join = 5000000000;
SET join_overflow_mode = 'break';   -- 或 'throw'

写法上，永远把小表放右，并尽量在右表内 WHERE 先过滤再 JOIN。
物化宽表：如果维表稳定，用 MaterializedView 把 user 维度 join 进事实表预聚合，查询时不用再 JOIN。
GLOBAL JOIN（分布式场景）：让 coordinator 先算右表广播。

加分项：能提到 24.x 后的 enable_filter_push_down_for_join_with_subquery 等新参数，让谓词更容易下推到 JOIN 内部。

易错点：盲目调高 max_bytes_in_join —— 治标不治本，最终还是会 OOM，只是把那一刻往后推。

📌 第 7~9 章总结：你已经掌握了 ClickHouse 「写得对、查得快、连得动」三件事。下一章我们进入第 10 章 —— 物化视图与 Projection，把这一章的「字典」「-State 后缀」串起来，做出真正的实时数仓骨架。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

join_vs_dict.py

python

#!/usr/bin/env python3
"""
第 9 章 · JOIN vs Dictionary 对比脚本

  · 自动造数据：100 万订单 + 10 万商品 + 5 万用户
  · 同一个查询分别用 JOIN / dictGet / direct JOIN 三种姿势跑
  · 输出耗时与峰值内存
  · 重复跑 3 次取平均，避免冷启动误差

依赖：pip install clickhouse-connect
准备：先 clickhouse-client < init.sql
"""
from __future__ import annotations

import random
import time
from datetime import datetime, timedelta
from decimal import Decimal

import clickhouse_connect

HOST = "127.0.0.1"
PORT = 8123
ORDERS = 1_000_000
PRODUCTS = 100_000
USERS = 50_000


def seed(client) -> None:
    print("=" * 80)
    print("[seed] truncate target tables ...")
    for t in ("orders", "products", "users"):
        client.command(f"TRUNCATE TABLE learn_ck.{t}")

    print(f"[seed] inserting {USERS:,} users ...")
    cats = ["3C", "服装", "母婴", "美妆", "食品", "家居", "运动"]
    brands = ["Apple", "Nike", "Adidas", "MAC", "蒙牛", "宜家", "李宁"]
    cities = ["北京", "上海", "深圳", "广州", "杭州", "成都"]
    user_rows = [
        (i, f"u{i}", random.choice(cities), random.randint(0, 1), datetime.now())
        for i in range(1, USERS + 1)
    ]
    client.insert("learn_ck.users", user_rows,
                  column_names=["id", "name", "city", "vip", "updated_at"])

    print(f"[seed] inserting {PRODUCTS:,} products ...")
    p_rows = [
        (i, f"商品-{i}", random.choice(cats), random.choice(brands),
         Decimal(f"{random.randint(10, 999)}.{random.randint(0, 99):02d}"),
         datetime.now())
        for i in range(1, PRODUCTS + 1)
    ]
    client.insert("learn_ck.products", p_rows,
                  column_names=["sku_id", "title", "category", "brand", "price", "updated_at"])

    print(f"[seed] inserting {ORDERS:,} orders ...")
    end = datetime.now()
    start = end - timedelta(days=7)
    span = int((end - start).total_seconds())
    rows = []
    cols = ["ts", "order_id", "uid", "sku_id", "qty", "amount"]
    for i in range(1, ORDERS + 1):
        ts = start + timedelta(seconds=random.randint(0, span))
        qty = random.randint(1, 5)
        amt = Decimal(f"{random.randint(10, 9999) * qty}.{random.randint(0, 99):02d}")
        rows.append((ts, i, random.randint(1, USERS),
                     random.randint(1, PRODUCTS), qty, amt))
        if len(rows) >= 100_000:
            client.insert("learn_ck.orders", rows, column_names=cols)
            rows.clear()
    if rows:
        client.insert("learn_ck.orders", rows, column_names=cols)

    print("[seed] reload dictionaries ...")
    client.command("SYSTEM RELOAD DICTIONARIES")
    print("[seed] done.")


def time_query(client, sql: str, runs: int = 3) -> tuple[float, int, int]:
    """返回 (avg_ms, peak_mem_bytes, returned_rows)"""
    times = []
    last_mem = 0
    last_rows = 0
    for _ in range(runs):
        client.command("SYSTEM FLUSH LOGS")
        t0 = time.time()
        res = client.query(sql)
        elapsed = (time.time() - t0) * 1000
        times.append(elapsed)
        last_rows = len(res.result_rows)
    client.command("SYSTEM FLUSH LOGS")
    mem_row = client.query(
        f"""
        SELECT max(memory_usage)
        FROM system.query_log
        WHERE event_time > now() - INTERVAL 1 MINUTE
          AND type = 'QueryFinish'
          AND query LIKE %(snip)s
        """,
        parameters={"snip": "%" + sql.strip().split("\n")[0][:40] + "%"},
    ).result_rows
    if mem_row and mem_row[0][0]:
        last_mem = int(mem_row[0][0])
    return sum(times) / len(times), last_mem, last_rows


def bench(client) -> None:
    print("=" * 80)
    print("[bench] 同一个查询：每个用户每个商品的销售总额 Top 10")
    print("=" * 80)

    Q1 = """
    -- 方式 A：传统 JOIN
    SELECT
        u.name AS user_name,
        p.title AS product,
        sum(o.amount) AS gmv
    FROM learn_ck.orders o
    JOIN learn_ck.users    u ON o.uid    = u.id
    JOIN learn_ck.products p ON o.sku_id = p.sku_id
    GROUP BY user_name, product
    ORDER BY gmv DESC
    LIMIT 10
    SETTINGS join_algorithm = 'hash'
    """

    Q2 = """
    -- 方式 B：dictGet 字典查询
    SELECT
        dictGetString('learn_ck.user_dict',    'name',  uid)    AS user_name,
        dictGetString('learn_ck.product_dict', 'title', sku_id) AS product,
        sum(amount) AS gmv
    FROM learn_ck.orders
    GROUP BY user_name, product
    ORDER BY gmv DESC
    LIMIT 10
    """

    Q3 = """
    -- 方式 C：direct JOIN（左 JOIN 字典 + join_algorithm = 'direct'）
    SELECT
        u.name  AS user_name,
        p.title AS product,
        sum(o.amount) AS gmv
    FROM learn_ck.orders o
    LEFT JOIN learn_ck.user_dict    u ON o.uid    = u.id
    LEFT JOIN learn_ck.product_dict p ON o.sku_id = p.sku_id
    GROUP BY user_name, product
    ORDER BY gmv DESC
    LIMIT 10
    SETTINGS join_algorithm = 'direct'
    """

    for name, sql in [("A. JOIN (hash)", Q1),
                      ("B. dictGet    ", Q2),
                      ("C. direct JOIN", Q3)]:
        try:
            t, mem, rows = time_query(client, sql)
            mem_disp = f"{mem/1024/1024:7.1f} MB" if mem else "  n/a "
            print(f"  {name}  →  avg {t:7.1f} ms   peak mem {mem_disp}   rows {rows}")
        except Exception as e:
            print(f"  {name}  →  FAILED: {e}")

    print()
    print("[bench] 单一字段查询（百万行 + 50K 维表）")
    Q4 = """
    SELECT count() FROM learn_ck.orders o
    JOIN learn_ck.users u ON o.uid = u.id
    WHERE u.vip = 1
    SETTINGS join_algorithm = 'hash'
    """
    Q5 = """
    SELECT count() FROM learn_ck.orders
    WHERE dictGetUInt8('learn_ck.user_dict', 'vip', uid) = 1
    """
    for name, sql in [("A. JOIN ", Q4), ("B. dict ", Q5)]:
        t, mem, _ = time_query(client, sql)
        mem_disp = f"{mem/1024/1024:7.1f} MB" if mem else "  n/a "
        print(f"  {name}  →  avg {t:7.1f} ms   peak mem {mem_disp}")


def main() -> None:
    client = clickhouse_connect.get_client(host=HOST, port=PORT, username="default")
    seed(client)
    bench(client)


if __name__ == "__main__":
    main()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189

join_vs_dict.py ↗

第 9 章 JOIN 与字典 ​

0. 引子：「ClickHouse 不擅长 JOIN」是什么意思 ​

9.1 ClickHouse 为什么对 JOIN 这么「冷淡」？ ​

9.1.1 一段类比：图书馆做联表查询 ​

9.1.2 默认 hash join 的代价 ​

9.2 JOIN 类型全家福 ​

9.2.1 标准 JOIN ​

9.2.2 ClickHouse 特有：ANY / ALL 修饰符 ​

9.2.3 SEMI / ANTI JOIN ​

9.2.4 ASOF JOIN：时序匹配神器 ⭐ ​

9.2.5 GLOBAL JOIN：分布式查询救命符 ​

9.3 JOIN 算法切换：SETTINGS join_algorithm ​

一段经验 ​

内存阈值与溢出策略 ​

9.4 字典 Dictionary：JOIN 的最佳替代 ​

9.4.1 一段类比：身份证 → 姓名查询表 ​

9.4.2 创建一个字典 ​

9.4.3 字典源（SOURCE）九大类 ​

9.4.4 字典布局（LAYOUT）选型 ​

9.4.5 字典查询函数：dictGet* ​

9.4.6 字典 vs JOIN 性能对比 ​

9.4.7 字典在 JOIN 的另一种用法：join_algorithm='direct' ​

9.5 真实案例 ​

9.5.1 订单表 + 商品字典 ​

9.5.2 IP → 地理位置（IP_TRIE） ​

9.6 📌 与 MySQL/PG 优化器 JOIN 的对比 ​

9.7 决策树：到底该 JOIN 还是 Dictionary？ ​

9.8 本章小结 ​

9.9 面试高频题 ​

Q1：为什么大家说「ClickHouse 不擅长 JOIN」？ ​

Q2：hash / parallel_hash / grace_hash / partial_merge 怎么选？ ​

Q3：ASOF JOIN 是什么？什么场景下必用？ ​

Q4：什么是 GLOBAL JOIN？为什么分布式查询要用它？ ​

Q5：什么是字典 Dictionary？跟 JOIN 比什么时候用？ ​

Q6：右表是百万级维表，怎么避免 JOIN OOM？ ​

🎬 可视化演示 ​

💻 示例代码 ​

第 9 章 JOIN 与字典

0. 引子：「ClickHouse 不擅长 JOIN」是什么意思

9.1 ClickHouse 为什么对 JOIN 这么「冷淡」？

9.1.1 一段类比：图书馆做联表查询

9.1.2 默认 hash join 的代价

9.2 JOIN 类型全家福

9.2.1 标准 JOIN

9.2.2 ClickHouse 特有：`ANY` / `ALL` 修饰符

9.2.3 `SEMI` / `ANTI` JOIN

9.2.4 `ASOF JOIN`：时序匹配神器 ⭐

9.2.5 `GLOBAL JOIN`：分布式查询救命符

9.3 JOIN 算法切换：`SETTINGS join_algorithm`

一段经验

内存阈值与溢出策略

9.4 字典 Dictionary：JOIN 的最佳替代

9.4.1 一段类比：身份证 → 姓名查询表

9.4.2 创建一个字典

9.4.3 字典源（SOURCE）九大类

9.4.4 字典布局（LAYOUT）选型

9.4.5 字典查询函数：`dictGet*`

9.4.6 字典 vs JOIN 性能对比

9.4.7 字典在 JOIN 的另一种用法：`join_algorithm='direct'`

9.5 真实案例

9.5.1 订单表 + 商品字典

9.5.2 IP → 地理位置（IP_TRIE）

9.6 📌 与 MySQL/PG 优化器 JOIN 的对比

9.7 决策树：到底该 JOIN 还是 Dictionary？

9.8 本章小结

9.9 面试高频题

Q1：为什么大家说「ClickHouse 不擅长 JOIN」？

Q2：`hash` / `parallel_hash` / `grace_hash` / `partial_merge` 怎么选？

Q3：`ASOF JOIN` 是什么？什么场景下必用？

Q4：什么是 `GLOBAL JOIN`？为什么分布式查询要用它？

Q5：什么是字典 Dictionary？跟 JOIN 比什么时候用？

Q6：右表是百万级维表，怎么避免 JOIN OOM？

🎬 可视化演示

💻 示例代码