ClickHouse物化视图的聚合下推与性能陷阱

ClickHouse物化视图性能陷阱：

陷阱1：物化视图链过长（写放大）

-- ❌ 错误：三级物化视图链
表A → 物化视图MV1 → 表B → 物化视图MV2 → 表C → 物化视图MV3

• 每次INSERT→MV1写B→MV2写C→MV3，写入放大3倍
• 优化：减少层数，用TO TABLE直写，或用ALTER TABLE ... MATERIALIZE MV异步跑

陷阱2：聚合粒度不匹配

-- ❌ 物化视图SUM按小时，查询按分钟，无法命中
CREATE MATERIALIZED VIEW mv_hourly ENGINE = SummingMergeTree()
AS SELECT toStartOfHour(ts) AS h, sum(amount) FROM raw GROUP BY h;

-- 查询分钟级时，只能扫描原始表
SELECT toStartOfMinute(ts), sum(amount) FROM raw GROUP BY ...;

• 优化：按最小查询粒度建物化视图，或建多级

陷阱3：TO TABLE复用不良

-- ✅ 正确做法：手动建目标表+TO指定
CREATE TABLE agg_daily (
  dt Date, user_id UInt64, total_amount Float64
) ENGINE = SummingMergeTree() ORDER BY (dt, user_id);

CREATE MATERIALIZED VIEW mv_daily TO agg_daily
AS SELECT toDate(ts) AS dt, user_id, sum(amount) AS total_amount 
FROM raw GROUP BY dt, user_id;

陷阱4：分区字段不一致

• 源表和物化视图分区表达式不同，导致查询Partition Pruning失效
• 优化：保持分区字段一致

正确设计案例：

-- 源表
CREATE TABLE raw_orders ... PARTITION BY toYYYYMM(ts);
-- 目标表（预聚合）
CREATE TABLE daily_stats ... ENGINE = SummingMergeTree() PARTITION BY dt;
-- 物化视图
CREATE MATERIALIZED VIEW mv_daily_stats TO daily_stats
AS SELECT toDate(ts) AS dt, product_id, sum(qty) AS qty, count() AS cnt
FROM raw_orders GROUP BY dt, product_id;