ClickHouse SummingMergeTree多ORDER BY字段优化方案咨询

问题背景 #

当前使用的events_1h表结构如下，采用SummingMergeTree引擎，ORDER BY包含大量维度字段以支持任意组合的交叉分析，但担心未来数据量和指标增长后出现性能问题：

CREATE TABLE events_1h 
(
`round_time` DateTime,

`dt` UInt8,

`aa_id` UInt64,
`bb_id` UInt64,
`cc_id` UInt64,

`cpu_architecture` String,

`browser_name` String,
`browser_version` String,
`browser_major` String,

`os_name` String,
`os_version` String,

`device_type` String,
`device_vendor` String,
`device_model` String,

`country` FixedString(2),
`city` UInt32,

`aso` UInt32,
`asn` UInt32,

`referer` String,

`request` UInt32,
`answer` UInt32,

`Impression` UInt32,

`Error` UInt32,

`start` UInt32,

// Many other events here   

)
ENGINE = SummingMergeTree
PRIMARY KEY (round_time, dt, aa_id, bb_id, cc_id)
ORDER BY (round_time, dt, aa_id, bb_id, cc_id, cpu_architecture, browser_name, browser_version, browser_major, os_name, os_version, device_type, device_vendor, device_model, country, city, aso, asn, referer);

核心问题解答 #

1. 用自定义哈希字段替换ORDER BY中的多维度字段是否有意义？ #

没必要，且不推荐：

• ClickHouse的ORDER BY是用来确定数据在磁盘上的排序存储顺序，而非通过哈希进行汇总。SummingMergeTree的合并逻辑是基于ORDER BY字段的分组——只有当两行数据的所有ORDER BY字段值完全一致时，才会触发指标求和合并。
• 若用自定义哈希替换这些维度字段，会存在哈希碰撞风险：不同维度组合可能生成相同哈希值，导致错误的指标合并，破坏数据准确性。
• 原ORDER BY字段作为维度用于查询过滤、分组时，哈希字段无法替代它们的索引加速作用——查询时仍需原维度字段进行过滤，哈希字段对这类查询没有帮助。

2. ORDER BY底层是否会转换为哈希？ #

不会。ClickHouse的ORDER BY是直接按照字段值的顺序进行排序存储，不会自动转换为哈希。SummingMergeTree的合并逻辑严格依赖ORDER BY字段的精确匹配，而非哈希值。

通用优化实践 #

针对多维度交叉分析、数据量和指标增长的场景，可采用以下优化方案：

• 合理调整ORDER BY与PRIMARY KEY：

  • PRIMARY KEY是用于索引的前缀，当前的PRIMARY KEY已经是ORDER BY的前缀（时间+核心ID），这部分设计合理，能保证时间范围查询的高效性。
  • 若部分维度的查询频率极低，可考虑从ORDER BY中移除，仅保留高频查询的维度，减少排序和合并的开销。对于低频维度的分析需求，可通过物化视图单独处理。

• 使用物化视图拆分维度组合：

  • 针对常用的维度组合（比如round_time + country + device_type、round_time + browser_name + os_name），创建单独的物化视图，采用对应的ORDER BY和PRIMARY KEY，避免主表ORDER BY字段过多。
  • 物化视图会自动同步主表数据，且各自存储对应维度组合的汇总数据，查询时直接访问对应视图即可，大幅提升特定组合的查询性能。

• 优化字段类型：

  • 对于字符串类型的维度（如browser_name、os_name），可转换为LowCardinality(String)类型，减少存储开销并提升查询性能——LowCardinality会对字符串进行字典编码，降低重复值的存储成本，同时加速过滤和分组操作。
  • 对于固定枚举值的字段（如device_type），可直接使用Enum类型，进一步压缩存储并提升性能。

• 数据分区与TTL：

  • 基于round_time或dt设置分区策略（如按天分区），减少查询时扫描的数据范围。
  • 设置TTL自动清理过期数据，避免历史数据占用过多存储资源，保证查询性能。

• 使用Projection（投影）：

  • ClickHouse的Projection功能可以为表创建不同的排序和聚合视图，无需单独创建物化视图。针对高频查询的维度组合，创建对应的Projection，查询时ClickHouse会自动选择最优的投影进行扫描，提升查询效率。
  • 示例：为round_time + country + browser_name的组合创建投影：

ALTER TABLE events_1h ADD PROJECTION proj_country_browser
(
    SELECT 
        round_time,
        country,
        browser_name,
        sum(request) as request,
        sum(answer) as answer,
        sum(Impression) as Impression
    GROUP BY round_time, country, browser_name
)

• 限制查询范围：
  • 对于任意组合的交叉分析，尽量在查询时指定时间范围（通过round_time或dt过滤），利用PRIMARY KEY的前缀索引快速定位数据，减少扫描的磁盘数据量。

内容的提问来源于stack exchange，提问作者Alex Toff