I IT 面试题库
ClickHouse MergeTree 表的 ORDER BY 该怎么设计?为什么按第二主键列过滤时索引几乎不起作用?
高级场景题大数据开发工程师数据仓库工程师ClickHouseMergeTree稀疏索引
题目
一张几千万行的用户行为表,按 (UserID, URL) 排序后查 UserID 极快、查 URL 却几乎全表扫描。请解释稀疏索引的工作原理并给出建表与优化建议。
参考答案
内容深度:详细 · 无代码示例 · 来源:外部链接 · 未复核 · 77 天前更新 · 技术内容可能随版本变化,建议结合官方文档理解
稀疏索引的原理
- MergeTree 不为每一行建索引。数据按排序键在磁盘上物理有序存放,每
index_granularity(默认 8192 行)行构成一个 granule,主索引 primary.idx 为每个 granule 只记一个 mark(该 granule 第一行的主键列值)。千万行表的索引通常只有几百 KB,可完全常驻内存。
- 查询分两步:①在内存里对 mark 数组做二分查找选出可能命中的 granule;②通过 mark 文件(记录压缩块偏移 + granule 偏移)定位,只把这些 granule 整块解压读入。实测 8.87M 行的表,命中首列时只选 1/1083 个 granule、读 8.19K 行。
PRIMARY KEY 与 ORDER BY 的关系
ORDER BY 决定物理顺序,同时决定压缩率;PRIMARY KEY 只决定稀疏索引内容,且必须是 ORDER BY 的前缀。只写 ORDER BY 时主键隐式等于排序键。
- 因此可以让排序键比主键多带几列(例如
PRIMARY KEY (UserID, URL)、ORDER BY (UserID, URL, EventTime)):主键保持精简省内存,排序键让相似数据物理相邻以提高压缩率。
为什么第二列过滤失效
- 过滤第一主键列时,mark 值全局有序,走二分查找。
- 只过滤第二列时,ClickHouse 只能做 generic exclusion search(通用排除搜索),其效果严重依赖前驱列的基数:前驱列基数低,后继列在多个连续 mark 内有序,可安全排除大量 granule;前驱列基数高(如 UserID 有 11.9 万种),后继列(URL 有 239 万种)在 mark 层面基本无序,几乎无法排除——实测选中 1076/1083 个 granule、读 8.81M 行,等同全表扫描。
设计建议
- 复合主键按基数升序排(低基数在前)。官方对比:升序
(IsRobot, UserID, URL) 查 UserID 只读 20.32K 行、UserID 列压缩比 39x;降序 (URL, UserID, IsRobot) 读 7.92M 行、压缩比仅 3x。
- 主键只放常用过滤列;高基数列(如 UUID)不要放第一列,否则物理顺序随机、其他列压缩率崩塌。
- 想同时高效支持两个高基数列过滤:建第二张表、物化视图,或用 Projection(最透明,优化器自动选择)。
minmax 类 skip index 对高基数且取值分散的列基本无效(目标值几乎总落在每组的 min/max 之间),它更适合有聚集性的低基数列。
题目与答案就在本页;收藏、批注和错题重练在应用内