ClickHouse 字典（Dictionary）详解

原创于 2025-09-07 07:23:28 发布 · 995 阅读

26 ·

CC 4.0 BY-SA版权

文章标签：

#clickhouse #字典 #Dictionary #学习

OLAP 专栏收录该内容

34 篇文章

订阅专栏

在 ClickHouse 的高级功能中，字典（Dictionary） 是一种高效管理维度数据的核心机制。它类似于传统数仓中的“维度表”，但性能远超普通表，特别适合处理如城市信息、用户标签、商品类目、IP 地址映射等“小表大用”的场景。

本篇将全面、深入地详解 ClickHouse 字典的原理、配置、使用方式与最佳实践。

🧩 一、什么是 ClickHouse 字典？

✅ 定义

字典（Dictionary） 是 ClickHouse 中一种内存驻留的键值映射结构，用于存储维度数据（如 ID → 名称），支持高速查找，常用于替代 JOIN 操作。

🔁 类比：

类似 Redis 的 Hash
类似数据库的“缓存表”
但更轻量、集成度更高

✅ 核心目标

✅ 替代低效的 JOIN（避免大表关联小表）
✅ 提升查询性能（内存哈希查找，O(1)）
✅ 支持实时更新（从 MySQL、Kafka、文件等加载）
✅ 支持复杂映射（如 IP 范围、层级结构）

🏗️ 二、字典 vs 普通表 JOIN：为什么更快？

对比项	普通表 JOIN	字典（Dictionary）
存储位置	磁盘（需读取）	内存（默认）
查找方式	全表扫描或索引	哈希查找（O(1)）
更新机制	手动 `INSERT/UPDATE`	自动定时/实时刷新
查询语法	`JOIN dim_table`	`dictGet('dict_name', 'attr', id)`
性能	慢（尤其大表 JOIN）	极快（微秒级）

✅ 示例：将 city_id 映射为 city_name

-- 普通 JOIN（慢）
SELECT u.user_id, c.city_name 
FROM user_log u 
JOIN cities c ON u.city_id = c.id;

-- 字典（快）
SELECT user_id, dictGet('city_dict', 'city_name', city_id) AS city_name
FROM user_log;

📦 三、字典的配置方式

ClickHouse 支持两种方式定义字典：

方式 1：DDL 语句（推荐，v20+）

CREATE DICTIONARY city_dict (
    id UInt64,
    city_name String DEFAULT 'Unknown',
    province String DEFAULT 'Unknown'
)
PRIMARY KEY id
SOURCE(MYSQL(
    host 'mysql-server' port 3306
    user 'ch_user' password '123456'
    db 'dim_db' table 'cities'
))
LAYOUT(HASHED())
LIFETIME(3600);  -- 每小时刷新一次

方式 2：XML 配置文件（传统方式）

文件路径：/etc/clickhouse-server/dict/city_dict.xml

<dictionary>
    <name>city_dict</name>
    <source>
        <mysql>
            <host>mysql-server</host>
            <port>3306</port>
            <user>ch_user</user>
            <password>123456</password>
            <db>dim_db</db>
            <table>cities</table>
        </mysql>
    </source>
    <layout><hashed /></layout>
    <structure>
        <id><name>id</name></id>
        <attribute>
            <name>city_name</name>
            <type>String</type>
            <null_value>Unknown</null_value>
        </attribute>
        <attribute>
            <name>province</name>
            <type>String</type>
            <null_value>Unknown</null_value>
        </attribute>
    </structure>
    <lifetime>3600</lifetime>
</dictionary>

✅ 建议使用 DDL，更易管理、版本控制。

🔗 四、字典的数据源（Source）

源类型	说明
`MySQL`	实时同步 MySQL 维度表
`ClickHouse`	从另一张 ClickHouse 表加载
`MongoDB`	读取 MongoDB 集合
`Redis`	从 Redis 读取 key-value
`File`	本地文件（CSV、TSV、JSON）
`HTTP`	从 HTTP 接口获取
`Kafka`	实时消费 Kafka 消息更新字典（实验性）

✅ 推荐：MySQL + File 作为主要数据源。

🧱 五、字典的布局（Layout）—— 决定性能的关键

布局	适用场景
`HASHED`	通用，推荐（内存哈希表）
`COMPLEX_KEY_HASHED`	多列作为主键（如 `(country, city)`）
`RANGE_HASHED`	带时间范围的映射（如费率表）
`IP_TRIE`	IP 地址范围映射（如 GeoIP）
`DIRECT`	不缓存，每次查源表（不推荐）

示例：IP → 地理位置（GeoIP）

CREATE DICTIONARY geoip_dict (
    prefix_range String,
    country String,
    region String
)
PRIMARY KEY prefix_range
SOURCE(FILE(path '/opt/dicts/geoip.csv' format 'CSV'))
LAYOUT(IP_TRIE())
LIFETIME(86400);

查询：

SELECT 
    ip,
    dictGet('geoip_dict', 'country', tuple(IPv4StringToNum(ip))) AS country
FROM access_log;

🔄 六、字典的生命周期（Lifetime）

控制字典多久刷新一次：

LIFETIME(MIN 300 MAX 3600)

MIN：最小刷新间隔
MAX：最大刷新间隔
0：不自动刷新（静态字典）

✅ 建议：

静态数据：LIFETIME(0)
动态数据：LIFETIME(300, 1800)（5~30 分钟）

🛠️ 七、字典的使用方法

1. 查询字典值

SELECT 
    dictGet('city_dict', 'city_name', toUInt64(1001)) AS city,
    dictGet('city_dict', 'province', toUInt64(1001)) AS province;

2. 在查询中使用

SELECT 
    user_id,
    event_date,
    dictGet('city_dict', 'city_name', city_id) AS city_name
FROM user_log
LIMIT 100;

3. 查看字典状态

SELECT 
    name,
    status,
    last_exception,
    bytes_allocated,
    hit_rate
FROM system.dictionaries;

✅ hit_rate 接近 1 表示命中率高。

⚠️ 八、注意事项与最佳实践

项目	建议
字典大小	建议 < 1GB（避免内存溢出）
数据源性能	MySQL 查询要快，避免阻塞
更新频率	避免过于频繁刷新（影响性能）
错误处理	设置 `null_value` 防止空值异常
监控	监控 `system.dictionaries` 的 `status` 和 `hit_rate`
替代方案	超大维度表 → 考虑 `JOIN` + `Cache` 引擎

✅ 九、最佳实践场景

场景	字典方案
城市/省份映射	`MySQL → HASHED` 字典
用户标签	`ClickHouse 表 → HASHED`
商品类目	`File → HASHED`
IP 地理位置	`File → IP_TRIE`
实时用户画像	`Kafka → COMPLEX_KEY_HASHED`（实验）
多语言翻译	`MySQL → HASHED`

🎯 十、总结：字典的核心价值

能力	说明
⚡ 极致查询性能	内存哈希查找，微秒级响应
🔄 自动更新	定时从外部系统同步
🧩 灵活数据源	支持 MySQL、File、Kafka 等
🗃️ 减少 JOIN	避免大表关联，提升查询效率
🌐 支持复杂映射	IP 范围、层级、多键