如何加速ClickHouse大表的服务+操作+时间范围查询？

现状说明 #

当前使用的建表语句：

CREATE TABLE default.my_table
(
    `timestamp` DateTime CODEC(Delta(4), ZSTD(1)),
    `service` LowCardinality(String) CODEC(ZSTD(1)),
    `operation` LowCardinality(String) CODEC(ZSTD(1)),
    `durationUs` UInt64 CODEC(ZSTD(1)),
    `url` String CODEC(ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toDate(timestamp)
ORDER BY -toUnixTimestamp(timestamp)
TTL timestamp + toIntervalDay(45)
SETTINGS index_granularity = 1024

该表存储任务调用信息，核心查询模式为：

SELECT * from my_table where service='abc' and operation='xyz' and timestamp > '2天前的时间戳'

目前表数据量已达31亿+，此类查询耗时常超1分钟，核心目标是通过索引优化减少需要读取的数据块数量。

具体优化手段 #

1. 调整ORDER BY主键（优先级最高） #

ClickHouse的MergeTree主键是稀疏索引，直接决定数据块的过滤效率。当前主键仅按时间倒序排列，对service和operation的过滤完全起不到作用，导致查询需要扫描大量无关数据块。

建议修改为复合主键：

ORDER BY (toDate(timestamp), service, operation, -toUnixTimestamp(timestamp))

这样查询时，ClickHouse可以通过主键索引快速定位到符合日期范围、指定service和operation的数据块，直接跳过绝大多数无关块，这是提升该类查询性能最有效的手段。

2. 分区键调整的利弊分析 #

将分区键改为(toDate(timestamp), service)是可选方案，但要结合数据分布判断：

• 优势：能直接把指定service的日期数据隔离到单独分区，进一步缩小扫描范围
• 劣势：如果单个service数据量极大（比如占总数据30%以上），该分区会异常庞大，反而影响合并、TTL清理等操作；若service数量上千，每天会生成上千个分区，增加元数据管理开销。
  如果你的service数量不多且数据分布相对均匀，这个调整能锦上添花；但如果service分布极度不均，优先调整主键的收益更高。

3. 跳数索引应对数据分布不均场景 #

如果调整主键后，部分service的数据仍分散在多个块中，可以给service和operation建立跳数索引：

ALTER TABLE my_table ADD INDEX service_idx service TYPE minmax GRANULARITY 8192;
ALTER TABLE my_table ADD INDEX operation_idx operation TYPE minmax GRANULARITY 8192;

跳数索引会在数据块级别记录字段的极值，查询时能快速跳过不符合条件的块，即使数据分布不均也能有效减少读取量。注意：跳数索引会增加写入时的CPU和存储开销，需要平衡写入和查询的优先级。

4. 其他辅助优化 #

• 避免SELECT *：明确写出需要的字段，减少数据传输和读取的IO开销
• 调整index_granularity：当前为1024，大表可尝试调至8192，减少索引存储空间，同时不影响过滤效率
• 确保TTL生效：定期清理过期数据，降低总数据量
• 预聚合表：如果这类查询是高频操作，可提前按service、operation、toDate(timestamp)预聚合所需字段，查询直接访问预聚合表，性能会有数量级的提升

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