为什么ClickHouse字典比与小表JOIN更好？

字典存储在RAM中，访问只需微秒，无需磁盘操作。即使与小表JOIN，仍然需要从磁盘读取数据并执行哈希。

对于100万个键，应该选择哪种字典类型？

对于100万个键，hashed是合适的。flat限制为50万键且需要整数键，而hashed没有限制，适用于任何键类型。

如何在不等待LIFETIME的情况下更新字典？

执行SYSTEM RELOAD DICTIONARY dictionary_name。ClickHouse立即重新读取源并替换内存中的内容。

如何创建具有两个字段复合键的字典？

使用LAYOUT(COMPLEX_KEY_HASHED())和PRIMARY KEY (field1, field2)。查询时，在dictGet中将键作为tuple(field1, field2)传递。

如何检查字典占用了多少内存？

SELECT name, formatReadableSize(bytes_allocated) FROM system.dictionaries WHERE status = 'LOADED';

ClickHouse中的字典：无需JOIN的快速查找

1. 为什么需要字典——引用数据的JOIN问题

回到我们的在线赌场。你有一个存储sport_id（1到20的数字）的bets表。但在报表中，你需要显示运动名称：“足球”、“冰球”、“网球”。这些信息通常存储在一个单独的引用表sports中。

-- 使用JOIN的慢查询
SELECT 
    b.user_id,
    s.name AS sport_name,
    sum(b.amount) AS total
FROM bets b
JOIN sports s ON b.sport_id = s.id
GROUP BY b.user_id, s.name;

对于bets表中的十亿行数据和sports表中的20行数据，这个JOIN会复制引用表到每个数据块。ClickHouse执行广播JOIN（将小表发送到所有分片），这虽然快，但仍然消耗内存和CPU。

字典以不同方式解决了这个问题。字典是ClickHouse内部的内存引用表。你可以在微秒内查找键并获取值，而无需执行JOIN。

现实类比： 字典就像考试的小抄。你有一个包含20行的列表：“1 = 足球，2 = 冰球……”。当需要根据ID查找运动名称时，你只需看一眼小抄（内存），而不是去图书馆找一本厚参考书（磁盘）。速度快了数千倍。

为什么这对ClickHouse重要： ClickHouse将数据存储在磁盘上，即使通过JOIN读取一个小表也需要磁盘操作。字典驻留在内存中（经过压缩和优化），访问它只是从RAM中读取。

2. 字典类型——如何选择结构

ClickHouse提供多种字典类型（LAYOUT），取决于：

数据大小（有多少键），
键类型（简单或复合），
是否需要范围查找（例如，某个日期的汇率）。

类型	何时使用	最大键数	特性
`flat`	非常小的字典（最多50万键）	500,000	最快，存储在数组中。键必须是整数（UInt*）。
`hashed`	中等字典（数百万键）	无限制	哈希表。适用于任何键类型。比`flat`稍慢。
`sparse_hashed`	非常大（数千万）	非常多	节省内存（不存储空值），但稍慢。
`range_hashed`	日期范围（按日期的汇率）	无限制	键 + 范围（开始，结束）。支持`get(key, date)`查找。
`complex_key_hashed`	复合键（例如，`market_id, selection_id`）	无限制	键是多个字段的元组。
`ip_trie`	IP地址（前缀查找）	最多50万	用于GeoIP：通过IP查找国家/城市。

如何选择：

少于50万键且键为整数 → flat（最高速度）。
超过50万键或键不是整数 → hashed。
非常多键且有很多空值 → sparse_hashed。
需要基于日期的查找 → range_hashed。
复合键（多个字段） → complex_key_hashed。

类比： flat就像一个带编号抽屉的柜子（索引=数字）。你直接走到17号抽屉。hashed就像一个图书馆目录，你首先通过哈希作者姓氏来计算书架。range_hashed就像一个档案室，你知道日期后搜索文档。

3. 数据源——字典从哪里获取数据

字典可以从各种来源（SOURCE）填充。ClickHouse按指定间隔（LIFETIME）定期从源更新字典。

支持的来源：

CLICKHOUSE — 另一个ClickHouse表
MYSQL — MySQL表
POSTGRESQL — PostgreSQL表
HTTP — REST API（JSON或XML）
FILE — 本地文件（CSV，TSV）
REDIS — Redis（键值）
MONGODB — MongoDB集合

MySQL示例：

CREATE DICTIONARY currencies_dict
(
    code String,
    name String,
    rate Decimal(10,4)
)
PRIMARY KEY code
SOURCE(MYSQL(
    host 'mysql-host'
    port 3306
    user 'reader'
    password 'secret'
    db 'reference'
    table 'currencies'
))
LIFETIME(MIN 3600 MAX 7200)   -- 每1-2小时更新一次
LAYOUT(HASHED());

为什么方便： 你的货币引用可以每小时从财务部门维护的外部MySQL数据库更新一次。ClickHouse自动拉取更改；你不需要编写ETL脚本。

4. 从ClickHouse表创建字典——逐步操作

最常见的场景：你已经在ClickHouse中有一个引用表，并希望将其转换为字典以实现快速查找。

步骤1：创建引用表（如果不存在）

CREATE TABLE sports
(
    id       UInt32,          -- 运动ID（1, 2, 3...）
    name     String,          -- '足球', '冰球', '网球'
    category String           -- '团队', '个人', '电子竞技'
)
ENGINE = MergeTree()
ORDER BY id;

-- 填充数据
INSERT INTO sports VALUES (1, '足球', '团队'), (2, '冰球', '团队'), (3, '网球', '个人');

步骤2：在该表之上创建字典

CREATE DICTIONARY sports_dict
(
    id       UInt32,          -- 键列
    name     String,          -- 要检索的值
    category String           -- 另一个值
)
PRIMARY KEY id                -- 查找键
SOURCE(CLICKHOUSE(
    host 'localhost'
    port 9000
    user 'default'
    password ''
    db 'default'
    table 'sports'
))
LIFETIME(MIN 300 MAX 600)     -- 每5-10分钟更新一次
LAYOUT(HASHED());              -- 对于我们的20条记录，flat也可以，但hashed也行

参数解析：

PRIMARY KEY id — 用于查找的列。必须唯一。
SOURCE(CLICKHOUSE(...)) — 数据源。你可以指定任何主机，不限于localhost。
LIFETIME(MIN 300 MAX 600) — 字典将每5-10分钟完全重新加载。MIN和MAX用于随机化，以防止所有服务器上的所有字典同时更新。
LAYOUT(HASHED()) — 内存结构。对于20条记录，flat更好，但我们保留hashed作为示例。

创建后会发生什么： ClickHouse读取整个sports表，将其作为哈希表加载到内存中。现在你可以使用dictGet进行快速访问。

5. 在查询中使用字典——dictGet及其相关函数

真正的魔法在SELECT中开始。你不再使用JOIN sports，而是使用字典函数。

dictGet——主要函数

-- 根据sport_id获取运动名称
SELECT 
    user_id,
    sport_id,
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    amount
FROM bets
LIMIT 10;

语法： dictGet('dictionary_name', 'value_column', key)

dictGetOrDefault——带默认值

-- 如果未找到sport_id，返回'未知'
SELECT 
    user_id,
    sport_id,
    dictGetOrDefault('sports_dict', 'name', sport_id, '未知') AS sport_name
FROM bets;

dictHas——检查键是否存在

-- 查找无效sport_id的投注
SELECT DISTINCT sport_id
FROM bets
WHERE dictHas('sports_dict', sport_id) = 0;   -- 返回字典中不存在的sport_id

带聚合的完整示例

-- 按总投注金额排名前5的运动，无需JOIN！
SELECT 
    dictGet('sports_dict', 'name', sport_id) AS sport_name,
    sum(amount) AS total_amount,
    count() AS bet_count
FROM bets
WHERE created_at >= today() - 7
GROUP BY sport_id
ORDER BY total_amount DESC
LIMIT 5;

为什么比JOIN快： 没有磁盘读取，没有跨分片分发引用表，没有查询时的哈希计算。字典已经在每个ClickHouse节点的内存中。

6. 复合键——带Tuple的dictGet

当键由多个字段组成时（例如，market_id + selection_id），使用LAYOUT(COMPLEX_KEY_HASHED())并将键作为元组传递。

创建具有复合键的字典：

-- 赔率字典：(market_id, selection_id) → 赔率值
CREATE DICTIONARY odds_dict
(
    market_id    UInt32,
    selection_id UInt32,
    odds_value   Decimal(10,3)
)
PRIMARY KEY (market_id, selection_id)   -- 复合键！
SOURCE(CLICKHOUSE(
    table 'odds_reference'
))
LIFETIME(MIN 60 MAX 120)
LAYOUT(COMPLEX_KEY_HASHED());            -- 必须是complex_key！

在查询中使用：

-- 获取特定市场和结果的赔率
SELECT 
    bet_id,
    market_id,
    selection_id,
    dictGet('odds_dict', 'odds_value', tuple(market_id, selection_id)) AS odds
FROM bets;

什么是元组？ 元组只是用括号括起来的一组值。tuple(market_id, selection_id)创建一个像(100, 5)这样的键。

7. 范围字典——用于历史数据（某日期的汇率）

假设你有每日变化的历史汇率。对于每笔欧元投注，你需要投注当日的汇率。

源表（例如，在MySQL中）：

currency	start_date	end_date	rate
EUR	2025-01-01	2025-01-31	1.05
EUR	2025-02-01	2025-02-28	1.08
EUR	2025-03-01	2099-12-31	1.10

创建范围字典：

CREATE DICTIONARY eur_rates_dict
(
    currency   String,
    start_date Date,
    end_date   Date,
    rate       Decimal(10,4)
)
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'eur_rates'))
LIFETIME(MIN 3600 MAX 7200)
LAYOUT(RANGE_HASHED())                     -- 特殊类型
RANGE(MIN start_date MAX end_date);        -- 指定范围列

使用：

-- 对于每笔欧元投注，获取投注日期的汇率
SELECT 
    bet_id,
    amount_eur,
    created_at,
    dictGet('eur_rates_dict', 'rate', tuple(currency, created_at)) AS rate
FROM bets
WHERE currency = 'EUR';

ClickHouse自动找到created_at在给定货币的start_date和end_date之间的记录。

类比： 这就像一个价格变化日历。你说，“给我3月15日的汇率”，字典检查它的日历：3月15日落在3月1日到3月31日之间，汇率1.10。

8. 监控字典——system.dictionaries

要了解字典的状态，可以使用系统表system.dictionaries。

SELECT *
FROM system.dictionaries
WHERE name = 'sports_dict';

有用的列：

列	显示内容
`status`	`LOADED` — 已加载，`LOADING` — 正在加载，`FAILED` — 错误
`origin`	来源（ClickHouse, MySQL...）
`type`	类型（flat, hashed, range_hashed...）
`key`	键类型
`attribute.names`	可用列
`bytes_allocated`	内存使用量（字节）
`query_count`	查找次数
`hit_rate`	命中率（越高越好）
`load_factor`	字典填充程度（对于hashed）
`creation_time`	加载时间
`last_exception`	如果状态为FAILED，错误信息在此

内存监控：

SELECT 
    name,
    formatReadableSize(bytes_allocated) AS memory,
    query_count,
    hit_rate
FROM system.dictionaries
WHERE status = 'LOADED'
ORDER BY bytes_allocated DESC;

如果字典占用数GB，你可能选择了错误的LAYOUT（例如，hashed而不是sparse_hashed）。

9. 热重载——SYSTEM RELOAD DICTIONARY

字典根据LIFETIME自动更新。但有时你需要强制更新：

你刚刚修复了源中的数据，不想等待10分钟。
字典失败（例如，源不可用），你修复了问题。

-- 重新加载特定字典
SYSTEM RELOAD DICTIONARY sports_dict;

-- 重新加载所有字典
SYSTEM RELOAD DICTIONARIES;

发生了什么： ClickHouse重新读取源（例如，sports表）并替换内存中的字典内容。在重新加载期间，使用dictGet的查询将等待（或返回旧数据，取决于版本）。对于关键系统，请在夜间执行重载。

如何验证字典正确加载：

SELECT status, last_exception 
FROM system.dictionaries 
WHERE name = 'sports_dict';

如果状态为LOADED，一切正常。如果为FAILED，检查last_exception。

10. 示例架构：博彩平台的所有引用字典

想象一个完整的博彩平台架构。你有数十个引用字典，在查询中不断用于丰富数据。

要创建的字典：

-- 1. 运动（20条记录，FLAT）
CREATE DICTIONARY sports_dict (id UInt32, name String, category String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'sports'))
LIFETIME(3600) LAYOUT(FLAT());

-- 2. 联赛/锦标赛（1万条记录，HASHED）
CREATE DICTIONARY leagues_dict (id UInt32, name String, sport_id UInt32, country_id UInt32)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'leagues'))
LIFETIME(3600) LAYOUT(HASHED());

-- 3. 国家（200条记录，FLAT）
CREATE DICTIONARY countries_dict (id UInt32, name String, code String)
PRIMARY KEY id
SOURCE(CLICKHOUSE(table 'countries'))
LIFETIME(86400) LAYOUT(FLAT());   -- 很少更改，每天更新一次

-- 4. 带有历史汇率的货币（RANGE）
CREATE DICTIONARY exchange_rates_dict (currency String, start_date Date, end_date Date, rate Decimal(10,4))
PRIMARY KEY currency
SOURCE(CLICKHOUSE(table 'exchange_rates'))
LIFETIME(3600) LAYOUT(RANGE_HASHED()) RANGE(MIN start_date MAX end_date);

-- 5. 按国家和投注类型的佣金（COMPLEX_KEY）
CREATE DICTIONARY commission_dict (country_id UInt32, bet_type String, commission Decimal(5,2))
PRIMARY KEY (country_id, bet_type)
SOURCE(CLICKHOUSE(table 'commissions'))
LIFETIME(7200) LAYOUT(COMPLEX_KEY_HASHED());

在单个查询中使用：

SELECT 
    b.user_id,
    dictGet('sports_dict', 'name', b.sport_id) AS sport_name,
    dictGet('leagues_dict', 'name', b.league_id) AS league_name,
    dictGet('countries_dict', 'name', dictGet('leagues_dict', 'country_id', b.league_id)) AS country_name,
    b.amount_eur * dictGet('exchange_rates_dict', 'rate', tuple('EUR', toDate(b.created_at))) AS amount_usd,
    dictGet('commission_dict', 'commission', tuple(dictGet('leagues_dict', 'country_id', b.league_id), 'prematch')) AS commission
FROM bets b
WHERE b.created_at >= today() - 7;

这种方法的优势：

速度： 没有JOIN，只有直接的内存查找。
可读性： 代码更清晰——你可以立即看到使用了哪些字典。
可管理性： 更新引用（例如，西班牙的佣金）在一个地方完成，而不是在ETL脚本中。
内存效率： 字典以压缩方式存储，通常比表中反规范化的列占用更少空间。

如果不使用字典会发生什么？ 你要么反规范化数据（在每个投注行中重复运动名称——数据量增加10倍以上），要么在每个聚合上执行JOIN（在数十亿行上缓慢且痛苦）。

下一步

现在你已经了解了字典的一切。接下来的主题：

通过HTTP更新字典 — 如何从外部API拉取数据。
在物化视图中使用字典 — 用于预丰富数据。
字典集群 — 字典在ClickHouse集群（Distributed）中的行为。

总结： 字典是在ClickHouse中处理引用数据不可或缺的工具。它们将小表的慢速JOIN转换为闪电般的内存查找。规则很简单：如果引用每分钟更改不超过一次，并且其大小允许存储在RAM中——就将其设为字典。你的查询会感谢你。

← 上一篇: ClickHouse中的TTL：自动数据生命周期管理
→ 下一篇: 特殊ClickHouse引擎：当MergeTree不适用时

— Editorial Team

ClickHouse中的字典：无需JOIN的快速查找