ClickHouse错误160：24小时区间查询超时，求优化方案

1. 优化模糊查询逻辑，避免前缀通配的性能损耗 #

article like '%job%'这类前缀带%的模糊匹配无法利用常规索引，是查询慢的核心原因之一，针对ClickHouse可以这样优化：

• 给article字段添加倒排索引：修改表结构，为该字段创建倒排索引，支持前缀通配的快速匹配：

  ALTER TABLE stage.log_table ADD INDEX idx_article article TYPE inverted();
  

• 用multiSearchAny替代like：该函数对多关键词匹配的效率远高于OR连接的like语句，写法如下：

  multiSearchAny(article, ['job', 'node']) > 0
  

2. 充分利用分区与主键索引 #

• 确认time（或你UNION ALL中误用的platform_time）是表的分区键，确保时间范围条件精确命中分区，避免全表扫描。
• 将direction、article加入表的排序键（ORDER BY），这样过滤时ClickHouse可以更快定位符合条件的数据块。

3. 预创建物化视图，提前聚合高频查询结果 #

如果这类按name、article聚合的查询是高频操作，创建物化视图预计算聚合结果，能大幅降低实时查询的压力：

CREATE MATERIALIZED VIEW mv_log_size_agg
ENGINE = MergeTree()
PARTITION BY toDate(time)
ORDER BY (name, article, direction)
AS
SELECT
    name,
    article,
    direction,
    sum(byteSize(message)) AS size_in_bytes,
    toDate(time) AS log_date
FROM stage.log_table
GROUP BY name, article, direction, toDate(time);

后续查询直接使用物化视图，只需过滤日期和条件：

SELECT name, article, sum(size_in_bytes) AS size_in_bytes
FROM mv_log_size_agg
WHERE log_date BETWEEN '2022-11-03' AND '2022-11-04'
  AND direction = 0
  AND multiSearchAny(article, ['job', 'node']) > 0
GROUP BY name, article
ORDER BY size_in_bytes DESC;

4. 修正UNION ALL的查询逻辑 #

你当前的UNION ALL仅拆分了时间范围，但未合并最终聚合结果，且存在字段误用（原查询用time，UNION ALL中用platform_time），调整后写法如下：

SET max_execution_time=60;
SELECT name, article, sum(size_in_bytes) AS size_in_bytes
FROM (
    -- 第一段12小时数据
    SELECT name, article, sum(byteSize(message)) AS size_in_bytes
    FROM stage.log_table
    WHERE time >= '2022-11-03 00:00:00' AND time < '2022-11-03 12:00:00'
      AND multiSearchAny(article, ['job', 'node']) > 0
      AND direction = 0
    GROUP BY name, article

    UNION ALL

    -- 第二段12小时数据
    SELECT name, article, sum(byteSize(message)) AS size_in_bytes
    FROM stage.log_table
    WHERE time >= '2022-11-03 12:00:00' AND time <= '2022-11-04 00:00:00'
      AND multiSearchAny(article, ['job', 'node']) > 0
      AND direction = 0
    GROUP BY name, article
)
GROUP BY name, article
ORDER BY size_in_bytes DESC;

5. 减少不必要的数据扫描 #

• 仅查询必需字段：确保SELECT语句中只保留name、article、message（用于计算byteSize），避免查询无关字段增加IO开销。
• 启用数据采样（可选）：如果业务允许近似结果，可通过采样快速获取聚合值，比如FROM stage.log_table SAMPLE 0.1，采样比例根据数据量调整。

6. 临时调整查询参数（需权限） #

如果服务器允许，可临时调整参数突破限制：

• 临时增大max_execution_time：比如SET max_execution_time=120;，但需注意不要影响其他查询。
• 调高max_rows_to_read：针对预估行数超标的情况，临时增加允许读取的行数上限。

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