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。

💬 开启对话式破冰 ➔
⚡
Test muscle memory on this topic via 3-Layer Flashcards
Active Recall: Mentally formulate Prompt ➔ Math Derivation ➔ Benchmark Implementation in 3 minutes.
🎯 Drill This Card Now➔