题干与适用场景
你维护一个 BigQuery 星型模型：store_sales 是事实表，customer 是维表。团队希望在表上声明主键和外键，让优化器利用唯一性和关系信息减少 Join。面试官要求你回答三个问题：约束不执行时到底保证了什么；优化器可以做哪些等价变换；数据漂移时如何避免静默错误。
假设查询只选择事实表列，事实表的外键允许 NULL，维表主键应当唯一且非 NULL。BigQuery 文档明确说明这些约束不会被引擎强制执行，维护者必须自行保证数据符合声明；违反约束的查询可能返回错误结果。
面试官考察点
- 是否区分“优化器可相信的元数据”和“写入时的完整性校验”。
- 是否能从唯一性和可选匹配推导 Join 消除，而不是笼统说“加索引会更快”。
- 是否知道
NOT ENFORCED不是软校验，也不会自动拒绝重复键或悬空外键。 - 是否能把约束治理落到装载流程、异常监控和回滚，而不是只给 DDL。
普通回答停留在“主键唯一、外键关联”。强回答会指出：优化器可能依据错误声明重写查询，因此坏数据的后果是结果错误而非单纯性能下降；每个约束都需要可重复的验证证据。
回答前需要澄清的问题
- 题目讨论的是 BigQuery 原生表还是外部表？约束支持范围和可用的优化规则不同，先限定原生表。
- 查询是否只投影左表列？若要返回右表列，Join 消除通常不成立。
- 外键列是否允许
NULL？NULL表示没有匹配要求，推导过滤条件时必须保留这一点。 - 约束是由同一条数据管道维护，还是跨系统复制？跨系统场景需要把一致性检查放在落表前和落表后。
这些问题会改变结论：投影右表列、重复主键或非空悬空外键都会使 Join 消除不安全；如果只能接受最终一致性，则要把检查结果作为发布门槛。
30 秒回答框架
“BigQuery 的主外键是声明式元数据，默认 NOT ENFORCED，不会在写入时拦截重复主键或悬空外键。它们的价值是给优化器提供唯一性和关系信息，例如只取事实表列时可以消除不改变结果的内连接。前提是数据真的满足声明；否则优化器可能产生错误结果。我的做法是先限定投影和 NULL 语义，再在每次装载后做重复键、悬空键和计数对账，失败就阻止约束发布或回滚，而不是把约束当作校验器。”
分步骤深入解答
1. 先分清约束的两个角色
主键声明表达“每行唯一且非 NULL”；外键表达“非 NULL 值应出现在被引用主键中”。BigQuery 可以读取这些声明来优化查询，但不会替你执行写入校验。NOT ENFORCED 仍然是显式契约，错误在于数据不满足契约，而不是语法无效。
2. 推导内连接消除
考虑只选择事实表列的查询：
SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
ON ss.sales_customer = c.customer_name;若 customer.customername 是唯一非空主键，且 ss.salescustomer 要么匹配一个客户、要么为 NULL，连接不会复制事实行。对只选择 ss 列的查询，优化器可以改写为：
SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;这个变换依赖两个事实：非 NULL 外键必有匹配，且匹配至多一行。若查询选择了 c 的列、需要区分无匹配行，或主键重复，变换就不再等价。
3. 外连接和连接重排的边界
左外连接在右表连接键唯一、且投影只来自左表时也可能被消除。多表查询还可以利用主外键推断基数，调整连接顺序。它们都是基于声明的推导，不代表 BigQuery 在运行时扫描右表验证唯一性。
4. 把数据正确性放进装载门禁
每个批次至少执行三类检查：
-- 主键重复或空值
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;
-- 悬空外键
SELECT COUNT(*) AS orphan_count
FROM store_sales AS ss
LEFT JOIN customer AS c
ON ss.sales_customer = c.customer_name
WHERE ss.sales_customer IS NOT NULL
AND c.customer_name IS NULL;再用批次行数、非空外键数量和匹配数量做对账。检查结果写入数据质量表，只有通过门禁的版本才发布新的约束声明或让下游查询使用优化结果。
5. 选择声明、校验或查询改写
声明约束适合稳定、可验证的维度键，收益是优化器能自动利用关系；应用层校验适合需要尽早拒绝坏数据的入口；查询改写适合约束暂时不可信的迁移期，但会增加重复逻辑。三者可以并存：先在流水线校验，再声明约束，最后用关键报表的结果对账监控。
6. 设计失效保护
当检查失败时保留上一版可信表或视图，标记当前批次为不可发布，并告警数据负责人。不要仅删除约束来“修复”错误，因为这会隐藏根因；先找出重复键来源、复制延迟或删除顺序，再决定重放、去重或补齐维度。
高质量示范回答
“我会把 BigQuery 主外键当作优化契约，不当作事务约束。先确认查询只取事实列、外键 NULL 的语义，以及维表键的唯一性。满足这些条件时，内连接可以被改写成对事实表的非空过滤，某些左连接也能消除，连接顺序还能依据基数优化。关键风险是 BigQuery 不执行这些约束；重复主键或悬空外键会让优化器依据错误元数据重写查询，结果可能静默错误。
“上线前我会在每个批次检查主键空值与重复、外键孤儿行，并做行数和匹配数对账；失败批次不发布新表或约束，保留上一版。对迁移中的不可信约束，我会先关闭依赖该约束的查询改写或改用显式 Join，直到连续批次通过检查。这样既得到元数据优化收益，也把正确性责任落实到可审计的流水线。”
常见错误
- 错误表现：说
PRIMARY KEY会拒绝重复行 → 失败原因：BigQuery 文档明确为不强制执行 → 修正方法：把重复检查放进装载门禁。 - 错误表现：看到外键就直接删除 Join → 失败原因：外键可能悬空，且查询可能需要右表列 → 修正方法：先验证数据和投影条件。
- 错误表现：只检查一次历史数据 → 失败原因：增量批次、回填和复制延迟会重新引入坏键 → 修正方法：按批次持续检查并保留指标。
- 错误表现：发现结果异常就删除约束 → 失败原因：失去优化信息却没有修复数据源 → 修正方法：冻结发布、定位根因、恢复可信版本。
追问及应对
维表当天出现重复主键，已经发布的查询怎么办？
先暂停依赖约束的优化查询，切换到显式去重或可信快照，标记受影响批次并回溯结果。修复维表后重新跑对账，再恢复约束声明。
外键允许 NULL 时，为什么改写要加非空过滤？
NULL 表示事实行没有客户匹配；内连接会丢掉该行，而原查询也只保留有匹配的行，所以改写必须保留 WHERE sales_customer IS NOT NULL，否则会改变结果。
如何证明优化器的 Join 消除没有改变结果？
在代表性分区上并行运行原查询和改写查询，比较行数、主键集合和聚合结果；同时记录约束检查版本。只有数据质量检查和结果对账都通过，才把优化后的查询推广到全量。
什么时候宁愿不用约束优化？
约束来自延迟高或无法审计的复制源、回填频繁且没有批次门禁时，先不用依赖声明的改写。多扫描一次但结果可信，比在错误元数据上追求省成本更重要。
BigQuery 能否用约束替代跨表事务？
不能。约束不执行写入一致性，也不提供跨表原子提交；需要事务语义时应在上游系统或装载编排中实现，并把最终校验结果传递到仓库。
参考资料
- Google Cloud Documentation：BigQuery primary and foreign keys。
- Google Cloud Blog：Join Optimizations with BigQuery Primary and Foreign Keys。