SQL 数据分区设计与性能优化

分区字段选错导致查询全扫，主因是partition key与查询条件不匹配；应优先选高频where字段，避免高基数或低区分度字段，按月分区更合理，分区键字段必须建索引，join需包含双方分区键。

分区字段选错导致查询全扫

分区表没提速反而更慢，大概率是 partition key 和实际查询条件不匹配。比如按 create_time 月分区，但业务查的全是 user_id，mysql 或 postgresql 都没法跳过无关分区。

实操建议：

• 先看高频 WHERE 条件里出现最多的字段，优先选它做分区键；
• 避免用高基数字段（如 uuid）或低区分度字段（如 status 只有 3 个值）；
• PostgreSQL 的 LIST 分区适合枚举类字段，RANGE 更适合时间或数字连续场景；
• MySQL 8.0+ 支持 EXPLAIN PARTITIONS，执行前加这个看是否命中目标分区。

分区数量太多引发元数据压力

单表分出几百个分区，PostgreSQL 会明显变慢——每次查询都要遍历分区目录、加载元数据；MySQL 在 INFORMATION_SCHEMA.PARTITIONS 查询时也会卡顿。

实操建议：

• 时间分区建议按「月」而非「天」，一年最多 12 个，三年留 36 个足够；
• MySQL 单表分区数上限是 8192，但实际超过 200 就该警惕；
• PostgreSQL 中每个分区是独立子表，d+ 表名 会列出所有，数量多时肉眼难定位；
• 删除旧分区别用 DROP table，MySQL 用 ALTER TABLE ... DROP PARTITION，PG 用 DETACH PARTITION 再删，避免锁表。

INSERT 性能突降：分区表达式不支持索引下推

插入时发现慢得离谱，尤其是批量写入，可能是分区表达式用了函数但没建对应索引。例如按 YEAR(create_time) 分区，但 create_time 字段本身没索引，MySQL 就没法快速定位该进哪个分区。

实操建议：

• 分区键字段必须有索引（哪怕只是单列索引），否则每次 INSERT 都要扫描所有分区判断归属；
• MySQL 不允许对分区表达式建索引（如 YEAR(create_time)），只能对原始列建；
• PostgreSQL 允许在分区键上建索引，但要注意全局索引和本地索引的区别：CREATE INDEX ON 表名 (分区键) LOCAL 才能随分区自动分裂；
• 批量导入前关掉 foreign_key_checks 和 autocommit，减少事务开销。

跨分区 JOIN 结果不准或报错

两张分区表 JOIN，结果少数据，或者直接报 Error: cannot join partitioned tables on columns that are not part of the partition key（PG），本质是优化器不敢保证分区对齐。

实操建议：

• JOIN 条件必须包含双方的分区键，且类型一致（比如都是 date，别一边是 timestamp）；
• MySQL 不强制校验，但若分区键不参与 JOIN，可能走不到分区裁剪，性能崩；
• 如果必须按非分区字段关联，考虑把其中一张表去分区化，或改用物化视图/临时表中转；
• PG 中用 ATTACH PARTITION 时确保子表约束完全匹配父表分区定义，否则后续 JOIN 可能被跳过。

分区不是银弹，最常被忽略的是“查询是否真的落到单个分区”——光看 EXPLAIN 输出不够，得结合 EXPLAIN ANALYZE 看实际扫描行数和分区数。另外，备份恢复时分区结构容易漏同步，特别是跨环境迁移，pg_dump 要加 --inserts 和 --no-tablespaces 才稳。