基于SummingMergeTree聚合后按原始datetime排序性能问题求助

问题根源 #

你的SummingMergeTree表使用(id, 15m_datetime)作为ORDER BY主键，这意味着索引是按用户ID和15分钟时间窗口构建的。当你用原始datetime字段做过滤和排序时，由于datetime不是主键索引的前缀，ClickHouse无法利用主键索引快速定位数据，只能执行全表扫描。

解决方案 #

方案1：调整主键顺序，利用15分钟窗口缩小扫描范围

将表的ORDER BY改为(15m_datetime, id, datetime)，让15分钟时间窗口作为索引前缀。这样查询时可以先通过15m_datetime快速定位到目标时间范围的数据块，再过滤原始datetime，避免全表扫描。同时不会改变原有的聚合粒度（同一id和15m_datetime的行仍会被聚合）。

修改后的建表语句（补充了必要的聚合字段）：

CREATE TABLE aggregated_data
(
    id UInt32,
    datetime DateTime,
    15m_datetime DateTime,
    count UInt64 -- 示例聚合字段，需替换为实际业务字段
)
ENGINE = SummingMergeTree(count) -- 指定要聚合的字段
PARTITION BY toYYYYMM(datetime)
ORDER BY (15m_datetime, id, datetime)
SETTINGS index_granularity = 8192;

优化后的查询语句：

SELECT
    *
FROM aggregated_data
WHERE 15m_datetime >= toStartOfFifteenMinutes('2024-01-01 00:00:00')
  AND 15m_datetime <= toStartOfFifteenMinutes('2024-01-07 23:45:00')
  AND datetime >= '2024-01-01 00:00:00' 
  AND datetime <= '2024-01-07 23:45:00'
ORDER BY datetime;

方案2：给datetime字段添加二级范围索引

如果无法修改原表结构，可以给datetime字段添加二级范围索引，让查询时能利用该索引过滤数据块。

添加并重建索引的语句：

-- 添加二级索引
ALTER TABLE aggregated_data
ADD INDEX datetime_idx datetime TYPE range(1000) GRANULARITY 8192;

-- 为已有数据重建索引（新写入数据会自动生成索引）
ALTER TABLE aggregated_data
MATERIALIZE INDEX datetime_idx;

添加索引后，查询时ClickHouse会自动使用datetime_idx缩小扫描范围，无需修改原有查询语句。

方案3：创建物化视图适配查询需求

如果原表的聚合逻辑不能改动，且该类查询频率很高，可以创建一个物化视图，按datetime排序存储聚合后的数据，让查询直接利用视图的主键索引。

创建物化视图的语句：

CREATE MATERIALIZED VIEW mv_aggregated_by_datetime
ENGINE = MergeTree()
PARTITION BY toYYYYMM(datetime)
ORDER BY (datetime, id)
POPULATE -- 同步原表已有数据
AS SELECT
    id,
    datetime,
    15m_datetime,
    count -- 对应原表的聚合字段
FROM aggregated_data;

查询时直接访问物化视图：

SELECT *
FROM mv_aggregated_by_datetime
WHERE datetime >= '2024-01-01 00:00:00' AND datetime <= '2024-01-07 23:45:00'
ORDER BY datetime;

注意：此方案会占用额外存储空间，且物化视图的数据同步存在微小延迟（默认实时同步）。

关键注意事项 #

• 原示例的SummingMergeTree建表语句缺少聚合字段，实际使用时必须指定要聚合的数值字段（如count、amount等），否则引擎无法完成聚合逻辑。

内容的提问来源于stack exchange，提问作者Ivan Sushkov