BI 看板查询优化预聚合表的设计粒度与更新策略每次打开 BI 看板要等 30 秒才加载出来不是数据太多是你的预聚合表设计有问题。一、BI 看板慢的根源实时计算 vs 预计算做数据分析的同学应该都遇到过这种场景老板站在你身后等着看数据BI 看板那个小圈圈转了半分钟还没出来空气都快凝固了。BI 看板的查询为什么慢因为看板背后的查询通常不是简单SELECT * FROM table LIMIT 10——它要做多维度的聚合、交叉、排序、同比环比。一张营销看板可能同时查渠道、地区、时间段三个维度每个维度还要算总和、均值、趋势。这种复杂查询跑在原始明细表上不慢才怪。解决思路其实很朴素把贵的计算提前做好存起来查询时直接取结果。这就是预聚合表。但这个方案有个核心矛盾粒度太粗查不了下钻粒度太细表太大失去了预聚合的意义。怎么选粒度是最需要权衡的地方。二、设计粒度到底预聚到哪一层预聚合表的设计粒度决定了能查多细和能查多快的平衡。我一般会做三层缓存具体设计 SQL 如下-- -- L1: 小时级预聚合表 -- 场景看最近 7 天的逐小时趋势 -- 保留小时粒度但合并了同小时的重复计算 -- CREATE TABLE IF NOT EXISTS dws_traffic_hourly AS SELECT stat_hour, -- 统计小时 channel_id, -- 渠道 region, -- 地区 COUNT(DISTINCT user_id) AS uv, -- 独立访客 COUNT(1) AS pv, -- 页面浏览量 SUM(if(event_type click, 1, 0)) AS click_cnt, -- 点击数 -- 预计算转化率避免查询时临时算 SUM(if(event_type order, 1, 0)) / COUNT(DISTINCT user_id) AS cvr FROM ods_user_behavior_log WHERE dt date_sub(current_date(), 90) -- 保留近 90 天 GROUP BY stat_hour, channel_id, region; -- -- L2: 天级预聚合表 -- 场景周报月报按天看趋势 -- CREATE TABLE IF NOT EXISTS dws_traffic_daily AS SELECT dt, -- 日期 channel_id, region, COUNT(DISTINCT user_id) AS uv, COUNT(1) AS pv, SUM(if(event_type click, 1, 0)) AS click_cnt, SUM(if(event_type order, 1, 0)) AS order_cnt, SUM(if(event_type order, amount, 0)) AS revenue -- 总营收 FROM ods_user_behavior_log WHERE dt date_sub(current_date(), 365) -- 保留近 1 年 GROUP BY dt, channel_id, region; -- -- L3: 维度汇总表不按时间拆分的总览 -- 场景看板首页概览卡片 -- CREATE TABLE IF NOT EXISTS dws_traffic_summary AS SELECT channel_id, region, COUNT(DISTINCT user_id) AS total_uv, COUNT(1) AS total_pv FROM ods_user_behavior_log GROUP BY channel_id, region;前端看板可以根据用户选择的日期范围自动切表查近 7 天按小时的趋势 → 选 L1查近 30 天按天汇总 → 选 L2查总览卡片 → 选 L3。这种分层设计处理几亿行数据也能做到秒级响应。需要注意的是自动切表的逻辑要写在查询路由层而非前端避免每个看板页面各自实现一遍。三、增量更新别每天重建全量表预聚合表写好了但怎么更新是个大坑。如果每天全量重建凌晨跑一次可能要几个小时而且会把历史数据的变化覆盖掉比如昨天的订单今天退款了。增量更新的标准做法是分区覆盖-- -- 增量更新策略只更新有变化的分区 -- 用 INSERT OVERWRITE 只覆盖指定分区 -- -- 每天凌晨只重建昨天的分区 INSERT OVERWRITE TABLE dws_traffic_daily PARTITION (dt 20240714) -- 只覆盖这一天 SELECT channel_id, region, COUNT(DISTINCT user_id) AS uv, COUNT(1) AS pv, SUM(if(event_type click, 1, 0)) AS click_cnt, SUM(if(event_type order, 1, 0)) AS order_cnt, SUM(if(event_type order, amount, 0)) AS revenue FROM ods_user_behavior_log WHERE dt 20240714 GROUP BY channel_id, region;但这里有个要注意的问题数据回刷。比如昨天的订单今天才完成支付我们得处理晚到数据。解决方式是设一个回刷窗口import datetime from airflow import DAG from airflow.operators.hive_operator import HiveOperator # # Airflow DAG预聚合表增量更新 # default_args { owner: zhudaxi, retries: 1, } dag DAG( dws_traffic_daily_update, default_argsdefault_args, schedule_interval0 3 * * *, # 每天凌晨 3 点跑 start_datedatetime(2024, 1, 1), catchupFalse, ) # 同时更新昨天和前天的分区覆盖延迟到第二天才完整的数据 for offset in [1, 2]: target_date {{ macros.ds_add(ds, -%d) }} % offset update_task HiveOperator( task_idfupdate_dws_traffic_{offset}d_ago, hqlf INSERT OVERWRITE TABLE dws_traffic_daily PARTITION (dt {target_date}) SELECT ... FROM ods_user_behavior_log WHERE dt {target_date} , dagdag, )四、物化视图与 OLAP 引擎另一种思路预聚合表之外如果你的技术栈里有 ClickHouse、Doris、StarRocks 这类 OLAP 引擎可以用物化视图来做更灵活的预聚合。物化视图比预聚合表好在用户不用关心数据在哪。查询器会自动判断这个查询能不能用物化视图来加速能的话就自动路由过去用户完全无感。-- -- ClickHouse 物化视图自动维护预聚合 -- -- 原始明细表用 MergeTree 引擎ClickHouse 默认引擎 CREATE TABLE events ( event_time DateTime, event_type String, user_id UInt64, channel String, amount Float64 ) ENGINE MergeTree() PARTITION BY toYYYYMM(event_time) ORDER BY (event_type, event_time); -- 自动聚合的物化视图 -- POPULATE 表示创建时立即用已有数据填充 CREATE MATERIALIZED VIEW events_daily_mv ENGINE SummingMergeTree() PARTITION BY toYYYYMM(event_date) ORDER BY (event_date, channel, event_type) POPULATE AS SELECT toDate(event_time) AS event_date, channel, event_type, count() AS cnt, sum(amount) AS total_amount, uniqExact(user_id) AS uv -- ClickHouse 的精确去重 FROM events GROUP BY event_date, channel, event_type;物化视图的优势是维护成本低原始表写入后物化视图自动更新不需要定时任务。但要注意它也有代价——每次写入原始表都会触发物化视图更新写入 QPS 高的场景要评估性能影响。五、总结BI 看板查询优化的本质是用存储空间换查询时间。预先把计算做好存起来用户查询时直接取这是性价比最高的优化手段。几个关键决策点粒度怎么选遵循80/20 原则覆盖 80% 常用查询场景的粒度就够了不用追求完美覆盖全量重建还是增量更新只要不是实时性要求极高增量更新永远优先存多久保留时长 业务最长查询时间窗口 数据回刷缓冲期一般 2-3 天最后提醒一句预聚合表只是查询优化的手段之一。如果系统本身就慢比如 Hive 表没有分区、没有建索引先解决基础设施问题再搞预聚合。不然就是在泥潭上盖楼越盖越沉。本文由朱大喜原创欢迎点赞收藏~