解读

面试官问“分区表交换”的实现差异，核心想验证两点：

1. 你是否理解分区表交换的本质——用元数据瞬间把“一张普通表”变成“某个分区”或反之，避免数据拷贝。
2. 你是否能对比 MySQL 与 PostgreSQL 在语法、锁粒度、索引处理、触发器/外键、云托管限制五个维度的差异，并给出在Google Cloud SQL场景下的最佳实践。
   国内大厂面试常把“分区交换”与“日终批处理”“历史数据归档”结合追问，答不出锁机制或 Cloud SQL 限制会被直接降档。

知识点

1. 分区表交换定义：
   通过ALTER TABLE … EXCHANGE PARTITION（MySQL）或ALTER TABLE … ATTACH/DETACH PARTITION（PostgreSQL）把一张结构相同、数据不重叠的普通表与指定分区互换，操作仅修改数据字典，秒级完成。

2. MySQL 8.0 实现要点（Cloud SQL 完全支持）：

   • 语法：ALTER TABLE pt EXCHANGE PARTITION p202405 WITH TABLE nt;
   • 锁：默认共享元数据锁（MDL），阻塞对该分区的写；需保证nt无并发写。
   • 索引：要求分区表与交换表索引定义完全一致，包括 invisible 属性，否则报错 1731。
   • 约束：交换表不允许存在外键，也不能被外键引用；触发器不会被自动带入，需手工重建。
   • 校验：默认做逐行范围校验，大数据量可关闭WITHOUT VALIDATION提速，但后续查询可能返回错误行。
   • Cloud SQL 注意：必须开启binlog=ROW且gtid_mode=ON时，交换操作会记录为 DDL 事件，可能触发只读副本延迟。

3. PostgreSQL 13+ 实现要点（Cloud SQL 默认支持）：

   • 语法：ALTER TABLE pt DETACH PARTITION p202405; 把分区变普通表；ALTER TABLE pt ATTACH PARTITION p202405 FOR VALUES FROM … TO …; 把普通表变分区。
   • 锁：ATTACH 需要SHARE UPDATE EXCLUSIVE锁，阻塞同时建分区；DETACH 需要ACCESS EXCLUSIVE锁，阻塞所有读写。
   • 索引：不要求索引一致，但ATTACH 时会自动把普通表上的索引“附加”为分区索引，若列顺序不同会重建，可能耗时。
   • 约束：ATTACH 前必须手动创建与分区键匹配的检查约束，且标记为NOT VALID也不行，Cloud SQL 会强制校验。
   • 触发器：父表触发器会级联到分区，DETACH 后触发器留在原表；ATTACH 时普通表触发器会被丢弃，需要重新创建。
   • 外键：允许存在，但 ATTACH 时若父表被外键引用，会全表扫描验证引用行，大表可能锁很久；Cloud SQL 不提供NOT VALID选项，必须一次性验证。

4. 性能与可用性差异：

   • MySQL 交换是单语句原子操作，失败回滚代价小；PostgreSQL 需两条 DDL，中间若失败需手工清理。
   • MySQL 校验阶段可关闭验证提升速度，PostgreSQL 必须验证，大数据量 ATTACH 可能触发 Cloud SQL 的 24 h 语句超时。
   • 在 Cloud SQL 高可用场景，MySQL 交换只产生一次 failover 候选延迟；PostgreSQL 的 DETACH+ATTACH 两次 DDL 会两次阻塞主库，对SLA 要求 4 个 9 的金融业务不可接受，需改用分区级联+视图方案。

答案

“分区表交换”在 MySQL 与 PostgreSQL 中的实现差异可归纳为语法、锁粒度、索引与约束校验、触发器/外键处理、云托管限制五点：

1. 语法：MySQL 用单条EXCHANGE PARTITION完成双向互换；PostgreSQL 需先DETACH再ATTACH，两条独立 DDL。
2. 锁粒度：MySQL 仅对目标分区加共享元数据锁；PostgreSQL 的DETACH需ACCESS EXCLUSIVE锁，整表阻塞写，Cloud SQL 高并发场景更易出现连接堆积。
3. 索引校验：MySQL 要求索引定义 100% 一致，否则直接拒绝；PostgreSQL 允许不一致，但ATTACH时会自动合并索引，可能触发大索引重建，Cloud SQL 的自动存储扩容在重建期间会临时翻倍 IOPS 费用。
4. 约束与外键：MySQL 禁止外键，交换表必须干净；PostgreSQL 允许外键，但ATTACH时会全表验证引用完整性，若父表被外键引用，亿级记录验证在 Cloud SQL 可能超时，需提前SET statement_timeout = 0并申请维护窗口。
5. 触发器：MySQL 交换后触发器不会自动迁移，需手工重建；PostgreSQL 的触发器在ATTACH时被丢弃，必须在父表重新CREATE TRIGGER，否则下游CDC（如 Debezium）会漏数据。

实战建议：

• 在 Cloud SQL MySQL 做日终归档，可关闭验证+ROW 格式 binlog，交换后立刻OPTIMIZE TABLE回收空间，只读副本延迟可控制在 3 s 内。
• 在 Cloud SQL PostgreSQL 做历史分区滚动，务必预建检查约束、提前ANALYZE，并把ATTACH放在业务低峰+维护期，否则24 h 超时会导致操作回滚并留下孤儿索引，需提工单清理。

拓展思考

1. 如果业务要求零停机归档，PostgreSQL 能否用分区级联视图+写屏蔽替代交换？
   答：可以，先DETACH旧分区，再CREATE TABLE new_202406 LIKE并INSERT SELECT增量，最后ATTACH新分区；但两次 DDL 锁仍无法避免，需借助pg_repack或逻辑复制槽实现无锁切换，Cloud SQL 目前不支持 pg_repack，只能退而求其次用双写+流量灰度。

2. MySQL 8.0 的DDL 原子性在 Cloud SQL 如何验证？
   答：可在交换前故意造一条越界记录，观察EXCHANGE失败时binlog 是否无残留；Cloud SQL 的Point-in-Time Recovery可回退到交换前 1 s，验证元数据回滚正确性，这是国内金融云验收的必测项。

3. 分区交换后统计信息失效如何兜底？
   答：MySQL 需ANALYZE TABLE pt UPDATE HISTOGRAM;；PostgreSQL 需ANALYZE pt;并手动设置父表继承统计信息，否则Cloud SQL Query Planner会把新分区当空表，导致索引走错、CPU 飙升 10 倍，这类案例在双 11 大促已多次出现，面试时提及可加分。