主键设计不合理

ClickHouse的主键设计与传统OLTP数据库的B树索引有本质区别,其核心在于通过稀疏索引和有序存储优化分析查询。本文为您介绍ClickHouse主键概念及最佳实践。

主键误区

对于初次使用ClickHouse的用户,经常难以完全理解其独特的主键概念。通常会有以下误区。

  • 误区1:主键唯一性 ClickHouse主键不强制唯一,重复主键行也可共存,由ORDER BY子句决定数据存储顺序。

  • 误区2:主键等同于索引 主键仅保证数据有序,过滤非首列时可能仍需扫描多数据块,需配合跳数索引使用。

了解主键

基础概念理解

其实ClickHouse与基于B+TreeOLTP数据库不同,它使用了一个稀疏索引,该索引针对每秒插入的数百万行和PB级数据集进行了设计。与OLTP数据库相反,ClickHouse的索引具有以下特点。

  • 有序存储

    数据按ORDER BY指定的主键顺序存储在磁盘上,每个data part内部严格排序,以快速识别可能匹配查询的数据。

  • 稀疏索引

    ClickHouse的索引为稀疏索引,其以固定粒度(如每8192行)记录主键值,形成轻量级索引。查询时,索引快速定位可能匹配的data part,随后在块内顺序扫描筛选目标行。

  • 设计特点 针对海量数据插入(每秒百万行)和PB级分析查询优化,牺牲点查询效率,换取高吞吐范围查询和卓越压缩率。

通过示例理解

结合上述基础概念,通过一个示例来进一步理解ClickHouse的主键。如下图所示:

  1. 设计主键。

    1. T表主键设计背景:

      • A、B、C应该按照基数升序(低基数列在前)进行排序。基数是指列中不同值的数量。

      • 查询使用A、B、C列频繁过滤数据,表明这些列在查询中为使用为频繁的列。

    2. 建表T。

      使用DDL语句创建表T,其中指定了ORDER BY (A, B, C),列A被指定为稀疏主键,表T的数据将按照列A、B、C的顺序进行排序。

    3. T数据存储:

      • 数据在存储中以data part形式存储,并且这些部分是按照指定的列(A、B、C)进行排序的。

      • 每个data part都有一个稀疏主键,该索引基于数据的排序顺序创建。

  2. 分析查询T表,了解主键功能。

    示例语句:SELECT ... FROM T WHERE A = ... AND B = ... AND C BETWEEN ... AND ... GROUP BY ... ORDER BY ... LIMIT ...

    ClickHouse接收到上述查询语句后,其查询处理引擎会根据查询条件,使用主键快速定位相关数据,并通过流式读取有序存储的数据块,减少磁盘寻道时间,从而提高查询效率。

图片

不当主键影响

  • 查询性能显著下降。

    如果主键未包含高频过滤条件列时,查询无法利用稀疏索引快速跳过无关数据块,导致扫描大量冗余数据,甚至扫描全表,进而严重影响了查询性能。

  • 数据压缩率降低。

    主键顺序未按照基数升序排列(低基数列在前)时,相同值将分散于不同数据块中,导致压缩算法无法有效去重。

  • 合并效率低下。

    主键设计不合理导致数据块内排序混乱,合并时需处理更多重叠或碎片化的数据块。频繁的合并操作占用I/OCPU资源,也影响写入吞吐量。

主键选择的关键原则

  • 选择高频过滤列

    • 列数量限制:通常不超过三列,避免索引膨胀和排序开销。

    • 过滤频率优先:主键列应为查询中频繁用于WHEREGROUP BYJOIN条件的列。

  • 列顺序的权衡

    • 第一列最关键:

      对数据裁剪影响最大,应选择高频且高筛选率的列(如时间字段)。

      例如,查询通常按日期过滤,将日期作为主键第一列可快速跳过无关数据块。

    • 后续列进行压缩优化:

      按列的基数升序排列(低基数列在前),提升压缩率。

      例如,主键(country, city, user_id)country基数最低,相同值连续存储压缩效果最佳。

    • 查询性能与压缩的平衡:

      若高基数列需频繁查询,可将其前置,但可能牺牲压缩率。

      折中方案:使用跳数索引(如bloom_filter)补充高频但非主键列的查询。但跳数索引也需设计合理,具体注意事项,请参见过度使用跳数索引

相关文档

更多主键与索引的信息,请参见主键和索引