当单表数据量达到千万级甚至亿级时，很多DBA会想到用分区表来拆分数据。但有时候光是分区还不够，比如按照时间做了Range分区之后，每个分区内部的数据量依然巨大，单分区的索引体积和查询压力仍然居高不下。这时可以考虑在分区的基础上再做一层细分，也就是子分区（Subpartitioning）。子分区能够让数据分布更加均匀，也让分区裁剪在更细的粒度上生效。不过子分区的使用限制相当多，稍不注意就会建表失败或者达不到预期的优化效果。
一、什么是子分区以及基本语法
子分区顾名思义，就是在分区的基础上再分区，也被称为复合分区（composite partitioning）。MySQL中子分区只能搭配两种一级分区类型使用：Range分区和List分区，而子分区本身只能采用Hash分区或Key分区。也就是说，你能遇到的组合只有四种：Range-Hash、Range-Key、List-Hash、List-Key。
下面是一个典型的Range-Hash复合分区建表语句，按照订单创建月份做范围分区，每个范围分区内再按用户ID做哈希子分区：
CREATE TABLE orders (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2),
created_at DATETIME NOT NULL,
PRIMARY KEY (order_id, created_at, user_id)
)
PARTITION BY RANGE (TO_DAYS(created_at)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01'))
(
SUBPARTITION s0 COMMENT = '一号子分区',
SUBPARTITION s1 COMMENT = '二号子分区'
),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01'))
(
SUBPARTITION s2,
SUBPARTITION s3
)
);写法上有两种风格：一种是用SUBPARTITION关键字逐个定义子分区，如上例所示；另一种是通过SUBPARTITION BY HASH(user_id) SUBPARTITIONS 4的方式让每个一级分区自动裂解成固定数量的子分区，这种写法更简洁，推荐在子分区不需要单独命名或指定存储位置时使用：
CREATE TABLE orders2 (
order_id BIGINT NOT NULL,
user_id BIGINT NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (order_id, created_at, user_id)
)
PARTITION BY RANGE (TO_DAYS(created_at))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 4 (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);二、子分区的使用限制与常见错误
子分区的限制比普通分区更严格，实际使用中最常见的报错基本都集中在以下几点。
第一，一级分区类型受限。如果你试图在Hash分区或者Key分区下面再建子分区，MySQL会直接报错。因为Hash和Key分区本身就是把数据均匀打散的，再套一层Hash没有意义。只有Range和List分区支持子分区，这是硬性规定，没有变通的办法。
第二，子分区数量必须一致。每个一级分区下的子分区个数必须相同，而且只能在建表时定义，建表之后无法通过ALTER TABLE增加或减少子分区的数量。如果p202401下面有两个子分区，p202402下面却定义了三个，建表就会失败。这一点和一级分区不同，一级分区是可以后续通过ADD PARTITION灵活扩展的。
第三，分区键必须纳入主键。如果表有主键或者唯一键，那么分区列和子分区列必须全部包含在这些键中。比如上面例子中，user_id和created_at都要出现在PRIMARY KEY里，否则会报每个分区键必须包含在表的每个唯一键中的错误。这个限制经常让业务上的主键设计被迫调整，需要在建表前规划清楚。
第四，子分区总数有上限。MySQL中一个表最多只能有8192个分区（含子分区），如果一级分区是36个月，每个分区下再切32个子分区，总数就是1152个，还算安全；但如果是按天分区再叠加子分区，很容易撞到上限，并且分区数量过多会显著拖慢打开表、执行计划生成等操作，反而得不偿失。
三、子分区对查询性能的影响与使用建议
子分区最大的价值在于分区裁剪（partition pruning）可以在两个维度上生效。当查询条件同时包含一级分区列和子分区列时，MySQL能够精确定位到具体的子分区，扫描的数据量会大幅减少。比如下面的查询，created_at用于裁剪Range分区，user_id用于裁剪Hash子分区：
EXPLAIN SELECT * FROM orders2 WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01' AND user_id = 10086;
通过EXPLAIN观察partitions列，如果显示的是单个子分区名（如p202401_sp2这样的形式），说明两层裁剪都生效了；如果显示整个一级分区下的所有子分区，说明子分区裁剪没有命中，通常是因为查询条件中缺少子分区列，或者对子分区列做了函数包装导致裁剪失效。
在使用建议上，有几点经验值得参考。首先，子分区的粒度要结合查询模式来定，如果绝大多数查询都只按时间过滤，那么加子分区带来的收益有限，反而增加了维护复杂度；只有当高频查询经常同时携带另一个维度条件（如用户ID、地区ID）时，子分区才真正有价值。其次，子分区的数量建议保持在2到8个之间，过多没有意义，Hash子分区本身就是为了打散，几个足以让单分区数据量降下来。最后，要注意子分区的维护操作，虽然不能增减子分区数量，但可以对单个子分区执行TRUNCATE、REPAIR等操作，排查问题时可以按子分区粒度定位，信息系统的information_schema.PARTITIONS表中记录了每个子分区的行数，是监控数据分布是否均匀的重要依据。
总的来说，子分区是个好用但约束很多的特性。建表前先把一级分区列、子分区列和主键的关系理清楚，确认查询模式确实需要两个维度的裁剪，再动手设计，才能避免返工并真正发挥它的性能价值。