ClickHouse SELECT查询过慢，同场景远慢于MongoDB该如何优化？

• 高频查询的衍生字段依赖raw大字段读取：context/level/event/data均从raw字段解析生成，即使定义为MATERIALIZED列，若写入时未正确触发持久化、或查询触发了实时解析逻辑，需要全量扫描raw大字段做JSON计算，会带来极高的IO和CPU开销。
• 未对高频查询场景做针对性优化：虽然排序键首位是device_id，但没有为查询返回字段做列剪枝优化，匹配到13万条数据后需要遍历所有行的全量字段，额外浪费了大量性能。
• 列定义存在错误：event_code列定义为String类型，但使用JSONExtractInt解析，会产生隐式类型转换，额外消耗性能的同时还可能导致数据异常。

1. 修正表结构 #

确保查询字段独立持久化，避免重复解析raw大字段，同时调整索引粒度适配点查场景：

CREATE TABLE IF NOT EXISTS logs ON CLUSTER default (
    raw         String COMMENT '原始日志仅做归档用，非必要不加入查询',
    ts          DateTime64(6) MATERIALIZED toDateTime64(JSONExtractString(raw, 'date_time'), 6),
    device_id   String        MATERIALIZED JSONExtractString(raw, 'device_id'),
    level       Int8          MATERIALIZED JSONExtractInt(raw, 'level'),
    context     String        MATERIALIZED JSONExtractString(raw, 'context'),
    event       String        MATERIALIZED JSONExtractString(raw, 'event'),
    -- 修正event_code类型不匹配问题
    event_code  Int32         MATERIALIZED JSONExtractInt(raw, 'event_code'),
    data        String        MATERIALIZED JSONExtractRaw(raw, 'data'),
    date        Date          DEFAULT toDate(ts),
    week        Date          DEFAULT toMonday(ts)
)
ENGINE ReplicatedReplacingMergeTree()
ORDER BY (device_id, ts)
PARTITION BY week
-- 调低索引粒度，默认8192，日志场景下调到1024可大幅提升点查速度
SETTINGS index_granularity = 1024;

2. 新增跳数索引加速过滤 #

针对高频按device_id查询的场景，新增布隆过滤器索引，快速跳过不匹配的数据块：

ALTER TABLE logs ON CLUSTER default ADD INDEX idx_device_id device_id TYPE bloom_filter(0.01) GRANULARITY 1;
-- 为历史数据构建索引
ALTER TABLE logs ON CLUSTER default MATERIALIZE INDEX idx_device_id;

3. 新增查询投影优化IO #

针对固定返回字段的高频查询场景，新增投影将常用字段单独存储为排序好的小文件，查询时自动命中投影，完全避免扫描raw大字段：

ALTER TABLE logs ON CLUSTER default ADD PROJECTION p_logs_device_query
(
    SELECT device_id, ts, context, level, event, data
    ORDER BY device_id, ts
);
-- 构建历史数据的投影
ALTER TABLE logs ON CLUSTER default MATERIALIZE PROJECTION p_logs_device_query;

4. 查询语句优化 #

• 增加时间范围过滤：日志类查询几乎都有时间范围要求，加上时间条件可以直接跳过无关分区，大幅减少扫描数据量
• 非必要不要查询raw字段，避免触发大字段IO
  优化后查询示例：

SELECT device_id, toDateTime(ts), context, level, event, data 
FROM logs 
WHERE device_id = 'some_uuid'
-- 按需增加时间范围过滤
AND ts >= now() - INTERVAL 30 DAY
ORDER BY ts DESC 
LIMIT 10 
OFFSET 0;

5. 可选归档优化 #

如果raw字段仅用于低频归档，不需要实时查询，可以将raw设置为高压缩级别，降低存储开销的同时不影响常用字段的查询速度：

ALTER TABLE logs ON CLUSTER default MODIFY COLUMN raw String CODEC(LZ4HC(9));

以上优化完成后，目标查询性能可以稳定到100ms以内，明显优于MongoDB的表现。

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