如何为ClickHouse创建合适索引解决时序数据查询卡顿问题

一、ClickHouse与MongoDB索引的核心差异通俗解释 #

• MongoDB用的B-Tree是稠密行级索引：每个行的索引键值都在索引树里存了一份，能直接定位到具体行的物理位置，类似词典的每个词条都标了精确页码。优势是点查精准，劣势是索引体积大，写入时维护成本高。
• ClickHouse用的是稀疏块级索引：默认每8192行组成一个数据块（granule），索引只存每个数据块的起始索引键值，不会给每行单独建索引。类似书籍的目录，只标每一章的起始页码，找内容时先定位到对应章节，再扫章节内的内容。优势是索引体积极小（通常只有数据总量的万分之几），写入基本无额外负担，非常适合时序大数据场景，劣势是不能直接定位到单行。

二、该场景的最优索引方案 #

方案1：调整排序键（性能最高，优先选择） #

ClickHouse的主键索引完全依赖排序键，数据写入时会按排序键的顺序物理落盘，是性能最高的索引，没有之一。
针对你的查询逻辑（先匹配tag1、tag2等值，再匹配server_time时间范围），直接将排序键设置为 (tag1, tag2, server_time) 即可。
建表时的示例语法：

CREATE TABLE table_name
(
    `tag1` String,
    `tag2` String,
    `server_time` DateTime,
    -- 其他字段
)
ENGINE = MergeTree
ORDER BY (tag1, tag2, server_time)
PRIMARY KEY (tag1, tag2, server_time) -- 主键默认和排序键一致，可省略

该配置下，相同tag1、tag2的所有数据会连续存储，且内部按时间排序，你查10分钟范围的请求只会扫描对应tag下连续的几个数据块，不会扫其他无关数据，性能可以提升上千倍。

方案2：建二级跳数索引（无需重导数据，适合存量表） #

如果表已经存储了大量数据，不想修改排序键重导数据，可以建二级跳数索引快速优化：

-- 给tag1、tag2建布隆过滤器索引，快速跳过tag不匹配的数据块
ALTER TABLE table_name ADD INDEX idx_tag1_tag2 (tag1, tag2) TYPE bloom_filter GRANULARITY 1;
-- 给server_time建minmax索引，快速跳过时间范围不匹配的数据块
ALTER TABLE table_name ADD INDEX idx_server_time (server_time) TYPE minmax GRANULARITY 4;

-- 刷新生效存量数据的索引
OPTIMIZE TABLE table_name FINAL;

三、额外优化点 #

• 你的查询SQL里'tag2'='xxx'存在语法错误，单引号会把tag2识别为字符串常量，导致过滤条件永远为假，需要去掉tag2的单引号改为tag2='xxx'
• 尽量避免使用select *，按需查询需要的字段，列存引擎的查询性能和查询的字段数量成反比。

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