SQL 子分区 HASH 与 LIST 的多级分区场景适用性对比

该用 hash 子分区而非 list 子分区的场景是：数据天然均匀、查询以等值为主、且无法预定义取值范围，如用户 id、订单号等高基数无业务含义主键；hash 自动打散避免热点，而 list 需枚举所有可能值，漏值即报错。

什么时候该用 HASH 子分区，而不是 LIST

HASH 子分区适合数据天然均匀、查询以等值为主、且你不想 / 没法预定义取值范围的场景。比如用户 ID、订单号这类高基数、无业务含义的整型主键，HASH 能自动打散到子分区，避免热点。

LIST 子分区则要求你提前知道并枚举所有可能的取值或区间——比如 region_code 只有 'CN'、'US'、'JP' 三种，或者 status 固定为 0、1、2。一旦新增一个没声明的值（如插入 'KR'），直接报错：ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 或更常见的 ERROR 1708 (HY000): Cannot add a partition to a subpartitioned table without specifying the subpartition（实际错误取决于 DDL 操作）。

• HASH 不关心值内容，只看 MOD(表达式， N) 结果，增删子分区只需调整数量，不影响已有数据分布逻辑
• LIST 的子分区定义必须覆盖所有可能值，漏一个就无法插入；后续扩展需 ALTER TABLE …… REORGANIZE SUBPARTITION，操作重、锁表久
• 查询带 WHERE region_code = 'US' 时，LIST 能精准裁剪到单个子分区；而 HASH 即使条件匹配，也得扫描全部子分区（除非主分区也参与过滤）

多级分区下，HASH + LIST 和 LIST + HASH 的性能差异在哪

MySQL 5.7+ 支持两级分区：一级用 RANGE 或 LIST，二级用 HASH 或 LIST。但顺序很重要——第一级决定数据怎么“切片”，第二级决定每片怎么“再分桶”。

典型组合是 RANGE COLUMNS(order_date) SUBPARTITION BY HASH(user_id) SUBPARTITIONS 4：先按时间分大块（如每月一个主分区），再在每块内用 user_id 哈希成 4 个子分区。这样既能按时间快速归档，又能避免单月数据因用户倾斜导致某子分区过大。

• LIST 做一级 + HASH 做二级：适用于“有限分类 × 高频离散 ID”的场景，比如 platform IN ('ios','android','web') 是一级，user_id 哈希是二级。但要注意一级 LIST 不能动态扩容
• HASH 做一级 + LIST 做二级：MySQL 不允许。语法会直接报错：ERROR 1064 (42000): You have an error in your SQL syntax —— 因为 HASH 分区不支持嵌套 LIST 子分区，仅支持 HASH 或 KEY 子分区
• 真正生效的组合只有：RANGE/LIST 一级 + HASH/LIST 二级；且二级为 LIST 时，必须对同一列（或表达式）重复定义，容易写错列名或括号层级

子分区数设多少才不踩坑

子分区数不是越多越好，尤其用 HASH 时，盲目设成 32 或 64 容易触发 MySQL 内部限制和性能拐点。

MySQL 单表最大分区数（含子分区）是 8192，但真实瓶颈常在更低处：每个子分区对应独立的 .ibd 文件，文件句柄、内存元数据、查询计划复杂度都随子分区数线性增长。实测在 16 核机器上，子分区超 16 个后，SELECT COUNT(*) 的响应延迟明显抖动。

• SUBPARTITIONS N 中的 N 必须是 2 的幂（MySQL 5.7 要求），否则建表失败：ERROR 1064 (42000): VALUES must be specified for each subpartition
• 如果一级是 RANGE 分 12 个月，二级 HASH 设 SUBPARTITIONS 8，总分区数就是 12×8=96 —— 看似安全，但备份工具（如 mydumper）可能因并发打开太多文件失败
• 线上建议：子分区数 ≤ 主分区数，且总数控制在 64 以内；若主分区已按时间分了 24 个，则子分区最多设 2 或 4，别硬凑 8

LIST 子分区里 NULL 怎么处理最稳妥

LIST 子分区对 NULL 极其敏感——它不会被任何显式列出的值匹配，除非你专门写一条 VALUES IN (NULL)。

常见翻车现场：字段允许 NULL，建表时只写了 VALUES IN (1,2,3)，结果所有 INSERT …… NULL 全部失败，报错：ERROR 1503 (HY000): Table has no partition for value NULL。

• 要么在 LIST 定义中显式包含 VALUES IN (NULL)，单独配一个子分区接收空值（注意：一个子分区只能有一个 VALUES IN 子句，不能混写 VALUES IN (1,2,NULL)）
• 要么改用 COALESCE(col, -1) 在插入前兜底，让业务层保证非空，然后 LIST 只管 -1,1,2,3
• 千万别依赖“没写 NULL 就默认归到第一个分区”——MySQL 不这么干，它严格匹配，不匹配就拒绝

复杂点在于，这个行为和主分区的 LIST 一致，但子分区更容易被忽略。上线前务必用 INSERT 测试 NULL 路径，别等凌晨批量导入崩了才看到日志里满屏的 ERROR 1503。