M5-021Medium性能与陷阱 · Performance & traps
BigQuery 里查询很慢/很贵,你会怎么优化?
A BigQuery query is slow and expensive. How do you optimise it?
💡 考场高分一句话答案:
先做分区裁剪与列裁剪,再看 join 顺序与数据倾斜,最后考虑预聚合与近似函数。
Partition pruning and column pruning first, then join order and data skew, and finally pre-aggregation and approximate functions.
📌 核心考察要点 (Key Points)
- 分区裁剪:WHERE 必须直接过滤分区列(不要对分区列做函数运算)
- 列裁剪:避免 SELECT *,只取需要的列(BigQuery 按列计费)
- 数据倾斜:大表 join 小表用 broadcast;热点 key 加随机前缀打散
- 预聚合:把常用指标做成日粒度汇总表;近似函数(APPROX_*)降成本
- Partition pruning: filter the partition column directly; never wrap it in a function
- Column pruning: avoid SELECT * because BigQuery bills by column scanned
- Skew: broadcast small tables, salt hot keys
- Cost: pre-aggregate to daily summary tables; use APPROX_* functions
🔍 大厂面试官连环追问 (Follow-ups)
- 什么是数据倾斜?怎么发现?
- APPROX_COUNT_DISTINCT 的误差有多大?
嫌字多看太累?进入苏格拉底式对话推演实验室
零打字负担,以大厂考官真实困境切入,通过点选直觉选项暴露思维盲区,手把手推导 Why。
⚡
想检验本题的肌肉记忆?进入 3 阶闪卡主动回忆盲测
隐藏答案遮罩,自测:审题锚点 ➔ 数理公式机理 ➔ 工业标杆代码与连环追问。3 分钟攻破认知盲区!