再谈where和on

小智来给你拆一拆：在 MaxCompute（以及 Hive/Spark 等开源引擎）里，**ON 与 WHERE 的“生效时机”不同**，直接决定能不能把过滤条件“推”到最底层，从而少读数据、少 shuffle、少花钱。下面把原理 → 最佳实践 → 跨平台差异一次讲透，方便你以后横展到任何大数据平台。

---

### 1. 原理：条件到底在哪一步被“看见”
| 阶段 | 谁干活 | 能否减少数据读取 |
|---|---|---|
| ① 分区裁剪（Partition Pruning） | 元数据层 | 只认 **WHERE 里对分区列的直接过滤** |
| ② 文件/Stripe/RowGroup 级过滤 | 存储格式（ORC/Parquet）+ 引擎下推 | 需要 **列 + 谓词** 出现在剩余 WHERE 或 ON 里，且引擎支持 |
| ③ Map/Shuffle 前过滤 | JOIN 算子 | ON 里的条件决定 **哪些维度表记录被广播/加载**；WHERE 在 JOIN 结束后再过滤 |

一句话：
- **分区列** → 必须出现在 **WHERE** 才能裁剪目录；
- **非分区列** → 想少读维度表/少 shuffle，条件最好写在 **ON** 里，让构建端（build side）先过滤再进内存。

---

### 2. 本案例的最佳写法（MaxCompute 专属）
```sql
-- 维度表先物化白名单，只有 0 记录，减少广播量
WITH white_list AS (
SELECT vehicle_series_code, data_id
FROM dim_iov_rvdc_dtc_did_data_df
WHERE data_filter = 0 -- 先过滤，再参与 JOIN
)
INSERT OVERWRITE TABLE target PARTITION (dt)
SELECT t.*
FROM dwd_rvdc_fact_di_rvdc_dtc3_did3_di t
JOIN /*+ MAPJOIN(w) */ white_list w
ON t.vehiclemodel = w.vehicle_series_code
AND t.code = w.data_id
WHERE t.dt = '${bizdate}'; -- 分区裁剪，必须放 WHERE
```

为什么这样最优？
1. `data_filter = 0` 写在 **子查询的 WHERE** → 维度表先被过滤，再进入内存/广播，**build side 最小**；
2. 车型与 code 写在 **ON** → 让 MaxCompute 在构建 Hash Table 时就只保留匹配键，**避免先做大表笛卡尔积再过滤**；
3. 分区列 `dt` 写在 **WHERE** → 元数据层直接跳过无关目录，**IO 最少**。

---

### 3. 如果把条件放错地方，会发生什么？
| 错误写法 | 后果 |
|---|---|
| `ON t.dt = '20250601'` | 分区列出现在 ON → **无法裁剪**，全表目录都会被扫一遍； |
| `WHERE w.data_filter = 0` | 先 JOIN 再过滤 → 维度表全部加载，**广播/Shuffle 数据量翻倍**； |
| `WHERE t.code = w.data_id` | 非分区列放 WHERE → 语义错误（JOIN 后过滤），**结果集先膨胀再裁剪**，性能差； |

---

### 4. 横展到开源大数据平台
| 平台 | 分区裁剪 | ORC/Parquet 下推 | 建议 |
|---|---|---|---|
| Hive 2.x/3.x | 只认 WHERE 分区列 | 支持 | 同 MaxCompute，子查询先过滤维度表 |
| Spark 3.x | 只认 WHERE 分区列 | 支持 + 动态分区裁剪(DPP) | 可用 `broadcast(df)` 替代 MAPJOIN，其余逻辑一致 |
| Trino/Presto | 只认 WHERE 分区列 | 支持 | 维度表先 `WHERE data_filter = 0` 再 `JOIN` |
| Flink Batch | 只认 WHERE 分区列 | 支持 | 维度表先过滤，再 `JOIN` |

> 结论跨平台通用：**分区列放 WHERE，非分区列尽量在 JOIN 前（子查询或 ON）先过滤**，就能吃到“存储层 + 计算层”双重下推红利。

---

### 5. 小智一句话总结
- **分区列** → 永远放 **WHERE**；
- **维度表过滤** → 先 **WHERE 子查询** 或 **ON 里先过滤**，再 JOIN；
- **大表非分区列** → 想下推就写成 **ON** 条件，让构建端提前瘦身。

记住这三句，以后换 Hive、Spark、Trino 都能直接复用，性能不掉坑！