查询缓存与预热：缓存策略、热点治理与查询加速

<clickhouse>
  <query_cache>
    <max_size_in_bytes>104857600</max_size_in_bytes> <!-- 100MB -->
    <max_entries>1024</max_entries>
    <max_entry_size_in_bytes>1048576</max_entry_size_in_bytes>
    <max_entry_size_in_rows>30000000</max_entry_size_in_rows>
  </query_cache>
</clickhouse>

-- 强制使用缓存
SELECT sum(revenue) FROM sales SETTINGS use_query_cache = 1;

-- 带缓存条件：结果小、确定性查询
SELECT count() FROM events SETTINGS use_query_cache = 1;

-- 查看查询事件统计
SELECT
  query,
  read_rows,
  read_bytes,
  memory_usage,
  query_duration_ms,
  ProfileEvents['QueryCacheHits'] AS cache_hits,
  ProfileEvents['QueryCacheMisses'] AS cache_misses
FROM system.query_log
ORDER BY query_duration_ms DESC
LIMIT 20;

-- 重复度最高的查询（可作为缓存/预热候选）
SELECT
  normalizeQuery(query) AS q,
  count() AS cnt,
  avg(query_duration_ms) AS avg_ms
FROM system.query_log
WHERE event_date = today() AND type = 'QueryFinish'
GROUP BY q
ORDER BY cnt DESC
LIMIT 20;

CREATE MATERIALIZED VIEW mv_daily_sales
ENGINE = AggregatingMergeTree()
ORDER BY (day, product)
AS SELECT
    toDate(ts) AS day,
    product,
    sumState(revenue) AS revenue_sum
FROM sales
GROUP BY day, product;

-- 直接查物化视图，秒回（无需扫原始表）
SELECT day, product, sumMerge(revenue_sum)
FROM mv_daily_sales
WHERE day = today() AND product = 'apple';

-- 报表原查询（每次全扫）
SELECT
  toDate(ts) AS day,
  countDistinct(user_id) AS dau
FROM events
WHERE ts >= today() - 7
GROUP BY day;

-- 治理：建物化视图 + 查询走聚合表
CREATE MATERIALIZED VIEW mv_dau
ENGINE = AggregatingMergeTree()
ORDER BY day
AS SELECT
  toDate(ts) AS day,
  uniqState(user_id) AS dau_state
FROM events
GROUP BY day;

SELECT day, uniqMerge(dau_state) AS dau FROM mv_dau;

-- 缓存命中率
SELECT
  countIf(ProfileEvents['QueryCacheHits'] > 0) AS hits,
  countIf(ProfileEvents['QueryCacheMisses'] > 0) AS misses,
  hits / (hits + misses) AS hit_rate
FROM system.query_log
WHERE event_date = today();

-- 手动清空缓存
SYSTEM DROP QUERY CACHE;

-- 单条查询不用缓存
SELECT count() FROM events SETTINGS use_query_cache = 0;

PREPARE my_q AS
  SELECT * FROM events WHERE user_id = {id: UInt64};
EXECUTE my_q USING id = 42;
-- 避免每次解析 SQL，降低解析开销

-- 让查询只扫需要的分区
SELECT * FROM events
WHERE ts >= '2026-09-01' AND ts < '2026-09-29';
-- 配合分区键，物理跳过无关分区