Dictionaries 与 External Data Sources

• 字典可对接的多种数据源（local、file、executable、HTTP、MySQL、ClickHouse、MongoDB、Redis 等）
• 数据源类型与部署方式的匹配
• 字典在查询中的加速作用

ClickHouse 字典支持非常广泛的数据源，包括本地文件（local/file）、可执行程序（executable）、HTTP 服务、MySQL、PostgreSQL、ClickHouse 自身、MongoDB、Redis 等。字典把这些外部数据加载到内存中，供查询时通过 dictGet 快速按 key 取值，实现维表加速。选择哪种源取决于数据存储位置与更新频率：MySQL/ClickHouse 适合已有关系库或集群维表，HTTP/executable 适合自定义生成逻辑，Redis 适合低延迟缓存。

字典本质是「内存维表」，把外部数据源的数据定期加载到内存，避免查询时逐条访问外部系统。数据源多样性使字典能灵活接入各类系统。加载模式（lazy/eager/periodic）决定数据新鲜度，需与数据源特性匹配。字典值是只读的，适合引用型维度数据。

• flat：按 key 顺序存储的数组索引，适合稠密 key
• hashed：哈希表，适合大数据量
• range：区间映射，支持范围键

flat 布局用数组按 key 连续存储，适合 key 连续且总量可控的场景，内存占用小、访问快；hashed 布局用哈希表存储，适合大数据量、key 不连续的场景，内存占用随 key 数增长；range 布局支持区间 key 映射（如日期区间映射到版本），适合时间版本化维表。hierarchical 字典通过「父键」列表达层级关系（如区域-城市-国家），支持层级查询函数。

布局选择影响内存与查询性能的平衡：flat 最快但要求 key 连续、内存按范围分配；hashed 更灵活但内存开销大；range 解决区间映射。hierarchical 字典用于树形维度，配合 dictGetHierarchy 等函数实现层级遍历。亿级 key 时需精确估算内存来选择布局。

• 未命中时返回类型默认值
• dictGetOrDefault 自定义默认值
• 与 JOIN 内连接/外连接语义的差异

当字典查询 key 未命中时，dictGet 返回该属性类型的默认值（如 0、空字符串），dictGetOrDefault 则返回显式指定的默认值。对于 NULL 键或未命中，默认值会掩盖「不存在」这一事实，可能造成误判。与 JOIN 相比，字典查询等价于便捷的「左关联」，但 JOIN 能区分内连接（丢弃未命中行）与外连接（保留并置 NULL），语义更明确；字典查询默认值无法区分「值为 0」与「未命中」。

dictGet 把维表访问变成 O(1) 内存查找，但不产生结果行，只是补列。若需区分未命中，需用 dictHas 判断或选择能表达缺失的默认值。JOIN 则支持完整的连接语义，但代价是逐行匹配。两者取决定理：小维表且需高性能补列用字典，需要精确连接语义、大维表或复杂连接用 JOIN。

• 字典的生命周期（LIFETIME）与自动刷新
• SYSTEM RELOAD DICTIONARY 手动刷新时机
• DDL 变更（ALTER/CREATE OR REPLACE）对字典的影响

字典通过 CREATE DICTIONARY 声明，LIFETIME 定义刷新周期（可指定 MIN/MAX 随机范围），系统按周期自动从源重新加载数据。SYSTEM RELOAD DICTIONARY 可手动立即触发刷新，适合数据源刚更新需要秒级生效的场景。DDL 变更（如 ALTER DICTIONARY 改属性、CREATE OR REPLACE DICTIONARY 重建）会改变字典结构或配置，需在刷新后生效，期间查询可能短暂使用旧数据或报错。

字典生命周期管理核心是「刷新时机」：自动刷新依赖 LIFETIME，手动刷新用 SYSTEM RELOAD。由于加载是异步的，刷新期间可能出现新旧数据并存。DDL 变更需谨慎，因为重建字典会清空内存并重新加载，可能造成短暂查询失败。合理设置 LIFETIME 与结合手动 RELOAD 可平衡数据新鲜度与加载压力。若字典源数据更新频繁但 LIFETIME 未到，可手动 SYSTEM RELOAD 提升新鲜度；若 DDL 变更失败，字典保持旧定义继续可用，直到重建成功。

• MySQL 引擎表创建以映射外部库
• 读取与写入的转发
• 查询下推与性能边界

ClickHouse 提供 MySQL、PostgreSQL 等表引擎，通过 CREATE TABLE ... ENGINE=MySQL('host:port', 'db', 'table', 'user', 'password') 创建映射表，之后可像本地表一样 SELECT，并可把 INSERT 转发到外部库。这类引擎表不存储数据，每次查询实时访问外部库，因此延迟较高，适合小数据量或作为数据落地的通道。查询会尽量下推 WHERE 等条件到外部库以减少传输量。

外部引擎表把 ClickHouse 当作外部库的「客户端」，适合需要跨库引用或临时对账的场景。但它不参与 ClickHouse 的索引与缓存，性能取决于外部库。与字典相比，字典把数据加载进内存更快，但实时性差；外部引擎表实时但慢。两者需按数据量、实时性、查询频率取舍。

• ODBC/JDBC Bridge 作为独立进程
• 通过表函数/表引擎访问外部库
• 与原生引擎的差异

ODBC/JDBC Bridge 是 ClickHouse 提供的独立辅助进程，用于通过 ODBC 或 JDBC 驱动连接 ClickHouse 原生引擎不支持的数据库（如 SQL Server、Oracle 等）。配置好 Bridge 后，可通过 odbc() 表函数或 jdbc() 表函数创建外部表访问，进行查询下推。它是 ClickHouse 与外部数据库交互的通用通道，弥补了原生引擎覆盖面不足的问题。

ODBC/JDBC Bridge 以独立进程运行，ClickHouse 通过 IP 与其通信，所需配置更复杂，但灵活性高。由于经过 Bridge 转一层，性能较原生引擎略低，适合小数据量或低频访问。相比 MySQL/PostgreSQL 原生引擎，Bridge 支持更多数据库类型，但部署与运维成本更高。

• dictGet 按 key 取属性值
• dictGetOrDefault 指定默认值
• 其他字典函数（dictHas、dictGetHierarchy）

dictGet('dict_name', 'attr', key) 从字典中按 key 获取指定属性值，是字典查询的核心函数。dictGetOrDefault 增加默认值参数，未命中时返回该默认值。此外还有 dictHas 判断键是否存在、dictGetHierarchy 获取层级父键等。这些函数在查询中作为标量表达式使用，可对每行补列，O(1) 内存访问。

dictGet 系列函数把外部维表变成内存查找，性能远高于 JOIN。它们支持在 SELECT、WHERE、JOIN 中灵活使用。理解各函数语义（默认值、未命中、层级）是正确用字典的关键。相比 JOIN，dictGet 不产生额外结果行，只补列，适合维度补全场景。

• lazy 懒加载、eager 立即加载、periodic 周期更新
• 更新失败时保留旧数据
• 查询降级与可用性

字典的加载模式决定数据何时进入内存：lazy 在首次查询时加载，eager 在字典创建时立即加载，periodic 则按 LIFETIME 周期刷新。更新策略上，若某次刷新失败，字典会保留上一次成功加载的数据（旧数据），保证查询持续可用而非报错；当更新或加载阻塞时，查询可能等待或返回旧数据，具体取决于配置与超时。默认情况下，字典加载失败时查询会报错，但已加载成功的字典在后续刷新失败时继续用旧数据。

加载模式与更新策略影响数据新鲜度与可用性的平衡。lazy 节省启动资源但首次查询慢；eager 加载快但启动开销大；periodic 保证周期性新鲜。更新失败时「保留旧数据」是关键降级机制，避免因数据源抖动导致查询中断。生产上常配合监控字典更新状态，及时告警。

• 小维表用字典、大维表用 JOIN 的边界
• 字典命中率与性能
• 命中的优化手段

字典适合小维表（如几万到百万级 key），因为数据加载进内存、查询 O(1) 补列，性能显著优于 JOIN；大维表（千万级以上）或需要完整连接语义时用 JOIN，因为字典全量加载内存开销大。提升字典命中率的方法：确保 key 类型与字典主键一致、使用正确的 key 映射、对未命中情况用 dictHas 过滤或补充默认值、优化 key 的编码减少无效查询。

边界并非绝对，取决于内存与查询频率。字典命中率高意味着大部分查询都在内存中取到值，扫描成本低；命中率低则大量走默认值，结果失真。可通过清理无效 key、从源端过滤缺失维、或结合 dictHas 判断未命中来提升有效性。JOIN 在数据量大或需要多列、复杂连接时更合适。

• flat：连续小 key
• hashed：大数据随机 key
• range：区间/版本映射

flat 布局用数组按 key 存储，适合 key 连续且总数不大的场景（如几百到几十万），内存小、访问最快；hashed 用哈希表，适合 key 不连续、数据量大的场景；range 支持区间键映射（如时间区间到版本），适合时效性维表；complex_key_hashed 支持复合 key；cache 用 LRU 缓存部分 key，适合超大字典只访问部分 key；direct 每次查询实时访问源，不缓存。

选择 layout 需考虑 key 特征、数据量、内存预算与访问模式。flat 简单但受限于 key 连续；hashed 通用但内存大；range 解决区间映射；cache 适合稀疏访问但命中率影响性能；direct 实时但慢。生产上需结合亿级 key 内存估算与查询模式选择。

• lazy/eager/periodic 的加载时机
• 字典失效（数据源变更、结构重建）的处理
• 手动刷新与监控

字典更新策略与加载模式相关：lazy 首次访问时加载，eager 建表时立即加载，periodic 按 LIFETIME 周期刷新。字典失效通常指数据源不可用、结构变更或刷新失败，处理方式包括：保留旧数据继续查询、SYSTEM RELOAD DICTIONARY 手动重刷、监控字典状态并告警、必要时重建字典。定期监控 system.dictionaries 表可查看字典加载时间与状态。

更新策略决定数据新鲜度，失效处理决定可用性。periodic 是最常用策略，配合 LIFETIME 随机范围避免集中刷新。失效时保留旧数据是默认降级，但需监控避免长期使用过期数据。合理配置重试与告警，平衡新鲜度与稳定性。

• WHERE 条件下推到外部库
• 连接（JOIN）条件下推
• 性能边界与大数据量限制

外部表引擎会把查询中的 WHERE 条件尽可能下推到外部数据库执行，减少从外部库拉取的数据量。某些情况下也能把连接条件下推。但 ClickHouse 无法利用外部库的索引做完整优化，且每次查询实时访问网络，因此性能受限于外部库负载与网络延迟。大数据量全表扫描时性能差，适合小数据量或低频访问。

查询下推是外部引擎性能的关键，能下推的谓词越多，传输数据越少。但 ClickHouse 仍可能在本地做部分处理，聚合、JOIN 等复杂操作常需先拉全量到本地，开销大。因此外部引擎表适合小表引用、对账、数据落地，不适合高频大查询。大数据量应改用字典或导入本地表。

• local 模式：每个节点各自加载本地字典
• global 模式：全局广播字典
• 一致性与数据同步风险

字典有 local 与 global 两种模式（在分布式查询中体现）。local 模式下，每个节点独立加载自己的字典副本，查询数据的节点用本地字典补列，数据分散但各节点字典可能不一致；global 模式下，字典被全局广播，所有节点使用同一份字典数据，保证一致性但增加网络与内存开销。一致性风险主要来自：local 模式下各节点字典刷新时间不同，导致同一 key 在不同节点取值不同。

local 模式性能好、无广播开销，但各节点字典可能因刷新时序不同而不一致，适合字典数据全局一致或可接受短暂不一致的场景。global 模式保证一致性，但广播成本高，适合数据量小、强一致需求的场景。需根据字典数据特性与查询一致性要求选择。

• complex_key_hashed 支持复合键
• hierarchical 字典表达父子层级
• dictGet 缓存与命中率提升

complex_key_hashed 布局支持复合主键（多个列组成的 key），用于需要多列联合定位的维度。hierarchical 字典通过一个「父键」列表达父子关系（如区域-城市-国家），配合 dictGetHierarchy 等函数实现层级遍历与聚合。提升 dictGet 缓存与命中率：保证 key 类型与字典主键一致、清理无效 key、用 dictHas 判断命中、对高频 key 预加载、选择 cache 布局时合理设置缓存大小。

父子维度（如组织树、行政区划）用 hierarchical 字典建模，配合层级函数可做上卷聚合。复杂键用 complex_key_hashed 支持多列 key。命中率提升的核心是减少无效查询与 key 类型不匹配，确保字典数据与查询 key 对齐。cache 布局的命中率取决于缓存容量与访问局部性。

• INSERT 到 MySQl 引擎表转发到外部库
• 批量插入的粒度
• 无跨库事务与一致性风险

MySQL 引擎表支持通过 INSERT 把数据写入外部 MySQL 库，写操作会实时转发到外部库执行。批量插入时，ClickHouse 会把一个 block 的批数据作为一个或多个 INSERT 发送到 MySQL，但整个过程不构成跨库事务：ClickHouse 与 MySQL 之间没有分布式事务保障，一旦中途失败，可能已写入部分行，造成数据不完整或重复。因此每次 INSERT 的粒度与失败重试需要业务层控制。

跨库写入的最大风险是「无原子性」：ClickHouse 本地写入与 MySQL 转发是两个独立动作，要么都成功要么需业务补偿。MySQL 引擎表写入通常用于把 ClickHouse 的聚合结果回灌到 MySQL 供在线查询，但需接受一致性与性能限制。批量大小影响转发次数与失败影响面，需权衡。

• 外部引擎表：实时访问但慢
• 字典：内存加载快但非实时
• 适用场景取舍

外部引擎表（如 MySQL、PostgreSQL）每次查询实时访问外部库，数据实时新鲜，但每次查询都有网络与外部库开销，性能差；字典把数据加载到内存，查询 O(1) 补列，性能远优于外部引擎表，但数据是定期刷新的，非实时。因此实时性要求高、数据量小用外部引擎表；查询性能要求高、可接受一定延迟用字典。

两者的核心权衡是「实时性 vs 性能」。外部引擎表保证实时但慢，字典快但数据滞后。生产上通常：低频引用、数据量小用外部引擎表；高频维表补列、数据可延迟用字典。也可用字典 local 模式 + 周期刷新兼顾性能。

• 字典内存占用与 key/属性数量相关
• flat 数组按 key 范围分配
• hadhed 哈希表内存开销

字典内存占用主要由 key 数与属性列大小决定。flat 布局按 key 最大值分配数组，即使 key 稀疏也会占用按最大 key 计算的内存，key 越连续内存效率越高；hashed 布局用哈希表，内存约等于 key 数 × 每个 key 的条目开销（含指针、哈希等），通常每个条目有几十字节固定开销。亿级 key 时，hashed 布局常需数十 GB 内存，需提前规划容量，可考虑用 cache 布局只缓存热 key 或拆分字典。

容量规划需结合 key 数量、属性列字节数、布局开销估算。flat 若 key 连续密集则内存高效，但 key 稀疏或范围大则浪费；hashed 随 key 数线性增长且有固定条目开销。亿级 key 场景内存压力大，需评估是否真的需要全量字典，或用 cache 布局 + 合理缓存大小控制内存。监控 system.dictionaries 的内存占用。

• executable 源调用外部程序生成字典
• HTTP 源拉取字典数据
• 超时与错误处理

executable 字典源通过调用外部可执行程序（如脚本）生成字典数据，程序从 stdin 读、向 stdout 输出，适合需要动态生成或聚合的维表；HTTP 源通过请求 HTTP 接口拉取字典数据（如 JSON 格式），适合对接已有 HTTP 服务。两者都需要处理超时与错误：可配置加载超时、重试与失败降级，源不可用时字典保留旧数据或告警。

自定义源把字典与外部系统解耦，灵活但需处理网络/进程的不确定性。超时处理是关键：源无响应时，加载应超时失败并保留旧数据，避免查询阻塞。HTTP 源需处理响应格式、鉴权、重试。executable 源需注意进程生命周期与资源。工程上需监控源可见性与加载延迟。

• 凭据安全存储与脱敏
• 明文密码风险
• TLS 加密与白名单治理

外部数据源（MySQL 引擎表、HTTP 字典等）的连接凭据（用户名、密码）若不慎以明文写在 DDL 或配置中，存在泄露风险。治理措施包括：使用环境变量或密钥管理服务注入凭据、避免在日志与审计中暴露密码、对连接启用 TLS 加密传输、通过 IP 白名单/防火墙限制外部库访问来源、使用最小权限账号。定期轮换凭据并审计访问。

安全治理的核心是「最小暴露、加密传输、最小权限」。明文密码在 DDL 中是常见风险，应通过配置注入或密钥管理替代。TLS 防止传输被窃听，白名单限制访问来源，最小权限账号降低泄露影响。凭据轮换与审计是持续性的安全实践。需在 DBA 与安全团队协作下落实。