ClickHouse的主键设计与传统OLTP数据库的B树索引有本质区别,其核心在于通过稀疏索引和有序存储优化分析查询。本文为您介绍ClickHouse主键概念及最佳实践。
主键误区
对于初次使用ClickHouse的用户,经常难以完全理解其独特的主键概念。通常会有以下误区。
误区1:主键唯一性 ClickHouse主键不强制唯一,重复主键行也可共存,由
ORDER BY
子句决定数据存储顺序。误区2:主键等同于索引 主键仅保证数据有序,过滤非首列时可能仍需扫描多数据块,需配合跳数索引使用。
了解主键
基础概念理解
其实ClickHouse与基于B+Tree
的OLTP数据库不同,它使用了一个稀疏索引,该索引针对每秒插入的数百万行和PB级数据集进行了设计。与OLTP数据库相反,ClickHouse的索引具有以下特点。
有序存储
数据按
ORDER BY
指定的主键顺序存储在磁盘上,每个data part内部严格排序,以快速识别可能匹配查询的数据。稀疏索引
ClickHouse的索引为稀疏索引,其以固定粒度(如每8192行)记录主键值,形成轻量级索引。查询时,索引快速定位可能匹配的data part,随后在块内顺序扫描筛选目标行。
设计特点 针对海量数据插入(每秒百万行)和PB级分析查询优化,牺牲点查询效率,换取高吞吐范围查询和卓越压缩率。
通过示例理解
结合上述基础概念,通过一个示例来进一步理解ClickHouse的主键。如下图所示:
设计主键。
T表主键设计背景:
列A、B、C应该按照基数升序(低基数列在前)进行排序。基数是指列中不同值的数量。
查询使用A、B、C列频繁过滤数据,表明这些列在查询中为使用为频繁的列。
建表T。
使用DDL语句创建表T,其中指定了
ORDER BY (A, B, C)
,列A被指定为稀疏主键,表T的数据将按照列A、B、C的顺序进行排序。表T数据存储:
数据在存储中以data part形式存储,并且这些部分是按照指定的列(A、B、C)进行排序的。
每个data part都有一个稀疏主键,该索引基于数据的排序顺序创建。
分析查询T表,了解主键功能。
示例语句:
SELECT ... FROM T WHERE A = ... AND B = ... AND C BETWEEN ... AND ... GROUP BY ... ORDER BY ... LIMIT ...
ClickHouse接收到上述查询语句后,其查询处理引擎会根据查询条件,使用主键快速定位相关数据,并通过流式读取有序存储的数据块,减少磁盘寻道时间,从而提高查询效率。
不当主键影响
查询性能显著下降。
如果主键未包含高频过滤条件列时,查询无法利用稀疏索引快速跳过无关数据块,导致扫描大量冗余数据,甚至扫描全表,进而严重影响了查询性能。
数据压缩率降低。
主键顺序未按照基数升序排列(低基数列在前)时,相同值将分散于不同数据块中,导致压缩算法无法有效去重。
合并效率低下。
主键设计不合理导致数据块内排序混乱,合并时需处理更多重叠或碎片化的数据块。频繁的合并操作占用I/O和CPU资源,也影响写入吞吐量。
主键选择的关键原则
选择高频过滤列
列数量限制:通常不超过三列,避免索引膨胀和排序开销。
过滤频率优先:主键列应为查询中频繁用于
WHERE
、GROUP BY
或JOIN
条件的列。
列顺序的权衡
第一列最关键:
对数据裁剪影响最大,应选择高频且高筛选率的列(如时间字段)。
例如,查询通常按
日期
过滤,将日期
作为主键第一列可快速跳过无关数据块。后续列进行压缩优化:
按列的基数升序排列(低基数列在前),提升压缩率。
例如,主键
(country, city, user_id)
,country
基数最低,相同值连续存储压缩效果最佳。查询性能与压缩的平衡:
若高基数列需频繁查询,可将其前置,但可能牺牲压缩率。
折中方案:使用跳数索引(如
bloom_filter
)补充高频但非主键列的查询。但跳数索引也需设计合理,具体注意事项,请参见过度使用跳数索引。
相关文档
更多主键与索引的信息,请参见主键和索引。