如何创建列表分区表_LIST Partition针对地区或状态字段的数据隔离

LIST分区必须用VALUES IN枚举常量值，不支持范围或表达式；mysql自动迁移更新的分区行，postgresql和oracle则报错；查询需带分区列才能裁剪，否则性能更差。

LIST 分区必须用 VALUES IN 明确枚举，不能范围或通配

mysql 和 oracle 的 list 分区本质是“精确匹配”，字段值必须落在你写死的集合里，否则插入直接报错 Error 1503 (hy000): a primary key must include all columns in the table's partitioning function（常见于主键没包含分区列）或更直白的 error 1708 (hy000): cannot add or update a child row: a foreign key constraint fails（实际是值不匹配分区定义）。

比如按地区隔离，region 是 VARCHAR(10)，就得这样写：

CREATE TABLE orders (   id INT,   region VARCHAR(10),   amount DECIMAL(10,2) ) PARTITION BY LIST (region) (   PARTITION p_cn VALUES IN ('BJ', 'SH', 'GZ'),   PARTITION p_us VALUES IN ('NY', 'CA', 'TX'),   PARTITION p_jp VALUES IN ('TYO', 'OSA') );

• 值必须是常量，不能是子查询、函数或变量，VALUES IN (select code FROM regions) ❌ 不合法
• NULL 单独算一个值，如果数据里可能有 NULL，得显式加 PARTITION p_null VALUES IN (NULL)
• Oracle 要求分区列不能为对象类型；MySQL 8.0+ 支持表达式分区，但 LIST 仍只接受列名本身，不支持 PARTITION BY LIST (UPPER(region))

PostgreSQL 没有原生 LIST 分区，得用 FOR VALUES IN 模拟

PostgreSQL 从 10 开始只有 RANGE 和 LIST 两种声明式分区，但它叫 FOR VALUES IN，语法看着像，行为也接近，但底层不共享元数据，管理成本更高。

建表要两步：先建主表，再 CREATE TABLE ... PARTITION OF：

CREATE TABLE orders (   id INT,   region TEXT,   amount NUMERIC ) PARTITION BY LIST (region);  CREATE TABLE orders_cn PARTITION OF orders   FOR VALUES IN ('BJ', 'SH', 'GZ');  CREATE TABLE orders_us PARTITION OF orders   FOR VALUES IN ('NY', 'CA', 'TX');

• 主表不能有数据，所有插入都路由到子分区；查主表会自动 union ALL 所有子分区，但 EXPLAIN 看执行计划，若 WHERE region = 'BJ' 能剪枝，只扫 orders_cn
• 新增分区要手动 CREATE TABLE ... PARTITION OF，不能像 MySQL 那样 ALTER TABLE ADD PARTITION
• 子分区索引需单独建，主表上建的索引不继承

分区列值变更时，MySQL 会自动迁移行，PostgreSQL 不会

这是最容易踩的静默坑：在 MySQL 中更新分区列（如把 region 从 'BJ' 改成 'NY'），只要目标值在另一个分区的 VALUES IN 里，MySQL 自动把这行挪过去——不报错、不提醒、不触发触发器。

PostgreSQL 完全相反：UPDATE orders SET region = 'NY' WHERE id = 123; 如果原行在 orders_cn 分区，而 'NY' 属于 orders_us，就会报错 ERROR: new row for relation "orders_cn" violates partition constraint。

• MySQL 这个“自动迁移”在大表上可能锁表几秒，且无法通过 slow log 捕获（它不算慢查询）
• PostgreSQL 必须先 delete 原行，再 INSERT 到新分区，应用层要自己处理
• Oracle 的 LIST 分区更新分区键会直接报错 ORA-14402: updating partition key column would cause a partition change，必须加 ALTER TABLE ... ENABLE ROW MOVEMENT

查询性能不总随分区数线性提升，注意统计信息和谓词下推

分区不是银弹。如果查询没带上分区列（比如只查 WHERE amount > 1000），MySQL 和 PostgreSQL 都会扫全分区，性能比单表还差——多了打开文件、合并结果集的开销。

真正起效的前提是优化器能做分区裁剪（partition pruning），而这依赖两点：准确的统计信息 + 谓词能下推到分区键。

• MySQL 8.0+ 默认收集分区级统计信息，但 ANALYZE TABLE 要显式执行，否则旧统计可能导致错误选择执行计划
• PostgreSQL 的 ANALYZE 默认不分析子分区，得手动 ANALYZE orders_cn 或设 default_statistics_target 更高
• 用 IN 多值查询时，MySQL 5.7 对 WHERE region IN ('BJ','SH') 能裁剪，但 WHERE region != 'BJ' 无法裁剪，会扫除 p_cn 外所有分区

分区列选错、数据倾斜（比如 90% 订单都在 p_cn）、或者频繁跨分区 JOIN，都会让 LIST 分区变成负优化。真要隔离地区数据，有时应用层路由比数据库分区更可控。