Join 膨胀 / 笛卡尔排查

难度与前置

难度：进阶
前置：会写多表 JOIN；知道「一行含义」；能看任务输入输出行数。
读完能做什么：用行数比与键分布定位膨胀点，改成正确粒度或加去重/半连接，并用验收 SQL 证明金额不再翻倍。

适用场景

• 聚合结果金额/次数突然变成「合理值的 N 倍」
• Shuffle 读写暴涨、任务变慢或 OOM，怀疑 Join 产出行数爆炸
• 明细 Join 维表、订单头 Join 订单行、事实 Join 多版本维表

不适用：

• 已知需要「行转列展开」的业务（如一单多券核销明细），但应显式写清膨胀比
• 单纯倾斜热点键（见 Hive 倾斜、Spark Join 倾斜）——可并存，先分清是膨胀还是倾斜
• 笛卡尔是故意做的小维表交叉（需限制两侧行数并注释）

真实场景（一条）

dwd_order_item（订单行）JOIN dim_user_df（用户日快照）算「行 GMV × 用户等级」。维表同一 user_id 因拉链误用成多行有效（end_date 未闭合），Join 后行数 ×3.2，日报 GMV 膨胀 220%。

排查：对比 Join 前后 COUNT(*) 与 COUNT(DISTINCT order_item_id)；对 user_id 做维表重复键检测。修复：Join 前维表 QUALIFY ROW_NUMBER()=1 或改用正确当日快照。验收：SUM(gmv) 与独立汇总表一致，相对误差 < 0.01%。

怎么做（步骤清单）

1. 先写清两侧「一行含义」与预期基数

侧	一行含义	预期相对另一侧
事实 A	一行 = 一个订单行	1
维表 B	一行 = 一个 user_id 在 dt 的快照	对 A 应为 0～1

预期膨胀比 ≈ 1 时，若实际 ≫ 1，就是问题。一对多要在文档写「允许 ×N，N=行数/头数」。

2. 用行数探针钉住是哪一次 Join

-- 逐步 CTE，每次 Join 后打点
WITH a AS (
  SELECT * FROM dwd_order_item_di WHERE dt = '${biz_date}'
),
b AS (
  SELECT * FROM dim_user_df WHERE dt = '${biz_date}'
),
j AS (
  SELECT a.*, b.user_level
  FROM a LEFT JOIN b ON a.user_id = b.user_id
)
SELECT
  (SELECT COUNT(*) FROM a) AS cnt_a,
  (SELECT COUNT(*) FROM b) AS cnt_b,
  (SELECT COUNT(*) FROM j) AS cnt_j,
  (SELECT COUNT(*) FROM j) * 1.0 / NULLIF((SELECT COUNT(*) FROM a), 0) AS blowup_ratio;

经验阈值：blowup_ratio 无业务说明时 > 1.05 即告警；> 2 优先当事故查。

3. 查「重复键」——膨胀的最常见根因

-- 维表 / 右表：Join 键是否唯一
SELECT user_id, COUNT(*) AS cnt
FROM dim_user_df
WHERE dt = '${biz_date}'
GROUP BY user_id
HAVING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 50;

-- 事实侧：若是 INNER 且左表也有重复业务键，同样会乘起来
SELECT order_item_id, COUNT(*) AS cnt
FROM dwd_order_item_di
WHERE dt = '${biz_date}'
GROUP BY order_item_id
HAVING COUNT(*) > 1
LIMIT 50;

4. 分清膨胀类型

类型	特征	处理
重复维键	右表键不唯一	Join 前去重 / 修拉链有效区间
多对多	两侧键都非唯一	先各自聚到共同粒度再 Join
条件过宽	ON 1=1 或漏等值条件	补全键；禁止隐式笛卡尔
分区列未进 Join	扫多日维表	dim.dt = fact.dt 或侧视视图
OR 条件 Join	优化器难推，易放大	拆 UNION 或先打宽键

5. 止血模板

维表去重后再 Join：

WITH dim_one AS (
  SELECT *
  FROM (
    SELECT
      d.*,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY update_ts DESC) AS rn
    FROM dim_user_df d
    WHERE dt = '${biz_date}'
  ) t
  WHERE rn = 1
)
SELECT f.*, d.user_level
FROM dwd_order_item_di f
LEFT JOIN dim_one d ON f.user_id = d.user_id
WHERE f.dt = '${biz_date}';

先聚合到共同粒度：

-- 错误：头表字段直接 Join 行表再 SUM → 头字段被行数放大
-- 正确：行表先按 order_id 汇总，再接头表属性
WITH item_agg AS (
  SELECT order_id, SUM(gmv) AS gmv, COUNT(*) AS item_cnt
  FROM dwd_order_item_di
  WHERE dt = '${biz_date}'
  GROUP BY order_id
)
SELECT h.channel, SUM(i.gmv) AS gmv
FROM dwd_order_header_di h
JOIN item_agg i ON h.order_id = i.order_id
WHERE h.dt = '${biz_date}'
GROUP BY h.channel;

6. 计划与引擎侧辅助

• Hive：EXPLAIN EXTENDED / EXPLAIN CBO 看 join 类型与估计行数
• Spark：Stage 的 input/shuffle records；AQE 日志中的 join 策略
• 发现 BroadcastNestedLoopJoin 或无等值条件 → 高度可疑笛卡尔

# Spark UI / 日志关键词（示警）
rg -n "BroadcastNestedLoopJoin|CartesianProduct|blowup|skew" spark_driver.log || true

常见坑（至少牢记这些）

1. 拉链维表当快照用
   未限制 start_date/end_date 或 dt，一人多行，事实被乘开。

2. 订单头字段在行级 SUM
   SUM(header.pay_amount) 在行表 Join 后按行数翻倍。头指标在头粒度算，或行表只用行金额。

3. 漏写 Join 条件或写成 OR
   ON a.dt = b.dt OR a.id = b.id 极易爆。等值条件拆开验证。

4. MAPJOIN / Broadcast 误判小表
   「小表」实际有重复键，Broadcast 后仍膨胀，只是更快算错。

5. 用 DISTINCT 掩盖问题
   Join 后再 SELECT DISTINCT 把性能打爆且可能误删真重复。应在源头去重并修粒度。

如何验收（可量化）

• [ ] Join 前后行数比有文档化预期；实现 blowup_ratio 监控或单测
• [ ] 右表 Join 键唯一性检查通过（或明确一对多并验收乘数）
• [ ] 核心金额：SUM 与「不经该 Join 的对照汇总」一致（阈值内）
• [ ] 执行计划无意外 Cartesian / NestedLoop（除非标注）
• [ ] 补数重跑同分区，结果稳定（幂等）

-- 验收：金额不膨胀
SELECT
  (SELECT SUM(gmv) FROM dwd_order_item_di WHERE dt = '${biz_date}') AS base_gmv,
  (SELECT SUM(gmv) FROM ads_order_channel_1d WHERE dt = '${biz_date}') AS ads_gmv,
  (SELECT SUM(gmv) FROM ads_order_channel_1d WHERE dt = '${biz_date}')
    - (SELECT SUM(gmv) FROM dwd_order_item_di WHERE dt = '${biz_date}') AS diff;

-- 验收：重复维键为 0
SELECT COUNT(*) AS dup_user_keys
FROM (
  SELECT user_id FROM dim_user_df WHERE dt = '${biz_date}'
  GROUP BY user_id HAVING COUNT(*) > 1
) t;

相关面试题

占位：与题库小程序对齐后补链接（建议关联：Join 膨胀、笛卡尔积、粒度对齐、维表去重）。

---

改写说明：结构与判断标准参考 Wood 原创数仓/面试文，已改写成可执行清单；未包含未公开公司数据与内部表名。