MySQL 子分区（SUBPARTITION）在时间序列表上的应用

时间序列表很少用SUBPARTITION，因其不提升查询剪枝效率且mysql不支持子分区字段的WHERE条件裁剪；仅在单一分区写入热点且主键无法缓解冲突时有用，需满足RANGE COLUMNS一级分区、静态子分区数等严格条件。

为什么时间序列表很少用 SUBPARTITION

子分区（SUBPARTITION）本身不解决时间序列的核心痛点——查询剪枝效率和冷热数据分离。它只是对已存在的分区再切分，而 MySQL 的分区裁剪（partition pruning）在大多数情况下只支持一级分区识别，SUBPARTITION 层几乎不参与优化。实际执行 EXPLaiN PARTITIONS 会发现，即使你按 YEAR(created_at) 分区、再按 HASH(user_id) 子分区，查询 WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31' 仍可能扫描所有子分区，而非仅目标年份下的子分区。

常见错误现象：select count(*) FROM t_log PARTITION(p2023) 能走分区裁剪，但 SELECT COUNT(*) FROM t_log WHERE created_at >= '2023-01-01' 却扫了全部子分区——说明子分区没带来预期的过滤能力。

• 子分区必须配合 LIST 或 RANGE 一级分区，不能单独存在
• SUBPARTITION BY HASH / KEY 仅用于均匀分布数据，不支持范围查询下推
• MySQL 8.0 仍未支持子分区字段参与 WHERE 条件裁剪

SUBPARTITION 在什么场景下真有用

真正能发挥子分区价值的场景非常有限，集中在「单一分区内部写入热点」且「主键/索引无法缓解冲突」时。比如：一个按月分区的日志表，每月分区写入集中在几个高频 user_id，导致 B+ 树页分裂严重、锁竞争高。此时可对每个 PARTITION 再做 SUBPARTITION BY HASH(user_id) SUBPARTITIONS 8，把热点分散到多个物理段。

关键约束条件：

• 一级分区必须是 RANGE COLUMNS 或 LIST COLUMNS（推荐 RANGE COLUMNS(created_at)，避免 TO_DAYS() 函数失效）
• 子分区数必须是静态常量（不能是变量或表达式），且最好为 2 的幂次（如 4/8/16）
• 子分区字段应与高频写入/更新的列强相关，且该列基数足够高（避免子分区倾斜）

示例建表语句片段：

CREATE table t_log (   id BIGINT,   user_id BIGINT,   created_at DATETIME,   content TEXT ) PARTITION BY RANGE COLUMNS(created_at) (   PARTITION p2023 VALUES LESS THAN ('2024-01-01')      SUBPARTITION BY HASH(user_id) SUBPARTITIONS 8,   PARTITION p2024 VALUES LESS THAN ('2025-01-01')      SUBPARTITION BY HASH(user_id) SUBPARTITIONS 8 );

比 SUBPARTITION 更靠谱的时间序列方案

对绝大多数时间序列表，直接放弃 SUBPARTITION，改用更可控的组合策略：

• 一级分区用 RANGE COLUMNS(created_at)，粒度建议为「月」或「周」（避免日分区导致分区数爆炸）
• 在 created_at 和高频查询字段（如 user_id）上建联合索引，顺序按查询模式定（例如 (created_at, user_id) 支持时间范围 + 用户过滤）
• 冷数据归档用 ALTER TABLE ... REORGANIZE PARTITION 拆出旧分区，再 DROP 或导出，比子分区清理更安全
• 若需写入扩展性，优先考虑应用层分库分表（如按 user_id % 16 路由），而非依赖 MySQL 子分区

性能影响提示：子分区会让 INFORMATION_SCHEMA.PARTITIONS 行数翻倍增长，备份工具（如 mysqldump --single-transaction）可能因元数据锁等待变长；而简单 RANGE 分区 + 合理索引，监控和维护成本低得多。

如果非要试 SUBPARTITION，请先验证裁剪行为

上线前必须确认你的查询是否真的受益。不要依赖文档描述，要用 EXPLAIN PARTITIONS 实测：

• 执行 EXPLAIN PARTITIONS SELECT * FROM t_log WHERE created_at >= '2023-06-01' AND created_at
• 检查输出中的 partitions 列：理想是只出现类似 p2023sp0,p2023sp3 这样的子分区名；若显示 p2023sp0,p2023sp1,...,p2023sp7 全部 8 个，则子分区未生效
• 对比同样条件下无子分区的表，看 rows 和 key_len 是否有实质下降

容易被忽略的一点：子分区定义一旦生效，后续修改只能通过 REORGANIZE PARTITION，不能直接 ALTER TABLE ... SUBPARTITION；而重组织过程会锁表，线上谨慎操作。