SQL 子分区（subpartition）的多级分区设计与管理开销评估

mysql 8.0 不支持 hash 或 key 分区下使用 subpartition，仅允许 range/list 主分区搭配 hash/key 子分区；reorganize partition 会锁整个主分区及所有子分区；子分区过多会显著拖慢执行计划生成；mysqldump 不漏定义但恢复易失败，需人工校验 ddl。

MySQL 8.0 中 SUBPARTITION 不支持 HASH 或 KEY 类型的子分区？

对，MySQL 官方明确限制：只有 RANGE 或 LIST 分区才能带 SUBPARTITION，且子分区类型只能是 HASH 或 KEY —— 但反过来不行。你不能用 HASH 分区再套 SUBPARTITION BY RANGE，会直接报错 Error 1064 (42000)。

常见错误现象是建表时语句看起来合法，比如：

CREATE table t (   id BIGINT,   dt DATE ) PARTITION BY HASH(YEAR(dt)) SUBPARTITION BY RANGE (id) (...);

执行就失败。根本原因是 MySQL 的分区引擎不支持这种嵌套逻辑，不是语法写错，是设计限制。

• 使用场景仅限于“先按时间范围分大区（PARTITION BY RANGE），再在每个大区内哈希打散（SUBPARTITION BY HASH）”
• 如果想按 ID 哈希 + 时间二级拆分，必须把时间字段放进哈希表达式里，例如 PARTITION BY HASH(id + YEAR(dt))，放弃子分区
• 5.7 和 8.0 行为一致，别指望升级解决

ALTER TABLE ... REORGANIZE PARTITION 操作会锁表多久？

不是“锁几秒”，而是取决于被重组分区的数据量和磁盘 I/O 能力。MySQL 在 REORGANIZE 子分区时，会复制数据、重建索引、更新元数据——整个过程是排他锁（WRITE LOCK），DML 全部阻塞。

容易踩的坑是误以为只锁子分区。实际上：REORGANIZE PARTITION p2023 会锁住整个 p2023 分区及其所有子分区，哪怕你只想调整其中两个 SUBPARTITION。

• 线上环境务必避开高峰；可用 pt-online-schema-change 替代，但要注意它不原生支持子分区 DDL，需手动拆解成多个原子操作
• INFORMATION_SCHEMA.PARTITIONS 里的 DATA_LENGTH 和 INDEX_LENGTH 是估算值，真实拷贝耗时可能比预估高 2–3 倍（尤其当有大量二级索引时）
• 若子分区数过多（如单个主分区下 64 个 SUBPARTITION），REORGANIZE 可能触发内部缓冲区溢出，报错 ERROR 1731 (HY000)

子分区数量太多导致 select 执行计划变慢？

会。优化器在生成执行计划时，需要评估每个子分区是否可能命中查询条件。子分区数从 8 个涨到 256 个，EXPLAIN 的分析阶段耗时可能从 1ms 升到 200ms+，尤其当 WHERE 条件无法精确裁剪（比如用 LIKE '%abc' 或函数包裹分区键）时。

这不是缓存问题，是优化器线性扫描分区元数据的固有开销。你看到 EXPLAIN 输出里 partitions 列列出几十个名字，背后就是实打实的判断逻辑。

• 验证方法：对比 SELECT @@optimizer_search_depth 默认值（62）和设为 30 后的 EXPLAIN 耗时，能明显感知差异
• 参数 eq_range_index_dive_limit 对子分区无效，别试图调它来缓解
• 真正有效的做法是控制单个主分区下的子分区数 ≤ 16，靠增加主分区数量（如按月而非按年）来摊薄数据密度

备份与恢复时 mysqldump 会不会漏掉子分区定义？

不会漏定义，但默认行为会导致恢复失败。因为 mysqldump 导出子分区表时，会把 CREATE TABLE 和 INSERT 分开输出，而子分区的 ENGINE 和 ROW_FORMAT 等属性只在建表语句里声明；如果恢复时目标实例版本或配置不同（比如老版本不支持 innodb_strict_mode=ON），建表就卡住，后续数据全丢。

更隐蔽的问题是字符集继承：若子分区没显式指定 CHARACTER SET，它会继承主分区设置，但 mysqldump --compatible=mysql40 可能抹掉这部分，导致恢复后乱码。

• 强制导出完整 DDL：加 --no-create-info --skip-triggers 分离结构与数据，人工检查建表语句中是否有 SUBPARTITION 关键字及对应参数
• 恢复前先在测试库跑 mysql -e "SHOW CREATE TABLE tG"，确认子分区定义和线上一致
• Percona XtraBackup 8.0+ 支持子分区，但要求源和目标 MySQL 版本小版本号一致，否则 xtrabackup_checkpoints 里的 subpartition_count 字段可能解析失败

子分区不是银弹，它的管理开销藏在 DDL 执行、执行计划生成、备份链路这些地方，而不是文档里写的那几行 SQL。真正麻烦的往往不是“怎么建”，而是“建完之后谁来扛住运维节奏”。