MySQL如何实现数据表的多级分区_Range与Hash分区优化策略-mysql教程-PHP中文网

mysql多级分区必须使用subpartition，仅partition by为单级；需主分区（如range按年）与子分区（如hash按用户id）组合，且子分区数建表时固定、各主分区数量须一致。

mysql如何实现数据表的多级分区_range与hash分区优化策略

MySQL多级分区必须用SUBPARTITION，单靠PARTITION BY不够

MySQL不支持“分区套分区”的嵌套语法，所谓多级分区实际是主分区（PARTITION）+ 子分区（SUBPARTITION）的组合。如果你只写 PARTITION BY RANGE 或 PARTITION BY HASH，那只是单级；要实现按年份再按用户ID哈希，就必须显式声明 SUBPARTITION BY HASH 并指定子分区数量。

主分区决定数据粗粒度分布（比如按 create_time RANGE 分年），子分区负责在每个主分区内进一步打散（比如按 user_id HASH 成 4 份）
子分区数必须在建表时固定，后续不能 ALTER 修改，只能重建表
所有主分区必须拥有相同数量的子分区，不能有的 2 个、有的 8 个
如果主分区键和子分区键不一致（比如主用日期、子用ID），要注意字段必须都在分区表达式中出现，且不能是函数依赖未包含列（如 YEAR(create_time) 可以，但 DATE_FORMAT(create_time, '%Y') 在某些版本会报错）

RANGE + HASH 多级分区的实际建表写法

典型场景：按年份范围划分主分区，再在每一年内按用户ID哈希避免热点。关键不是“能不能”，而是字段选型和表达式是否合法。

主分区键必须是整型或可转为整型的表达式（YEAR(create_time) 合法，create_time 直接用不合法）
子分区键可以是另一列，但该列必须在表结构中存在，且不能是 TEXT/BLOB 类型
子分区必须用 SUBPARTITION BY HASH，不能用 SUBPARTITION BY RANGE —— MySQL 不支持子分区再 RANGE

CREATE TABLE orders (
  id BIGINT NOT NULL,
  user_id INT NOT NULL,
  create_time DATETIME NOT NULL
) PARTITION BY RANGE (YEAR(create_time))
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 4 (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025)
);

为什么Range主分区后加Hash子分区能缓解热点，但不是万能的

这种组合本质是把“时间局部性”和“数据均匀性”分开处理：RANGE 让查询能快速定位年份区间（比如 WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31' 走索引+分区裁剪），HASH 则让同一时间窗口内的写入压力分散到多个物理段。

MySQL

编写正确的MySQL查询，避免字符集、索引和锁方面的常见陷阱。

下载

但如果查询条件只含 user_id，没有 create_time，MySQL 无法裁剪主分区，会扫描全部年份分区下的所有子分区，性能反而比单一分区差
子分区数太少（如只有 2 个）起不到打散作用；太多（如 64 个）会增加管理开销和 open_files_limit 压力
Range 分区边界一旦设死（比如 LESS THAN (2025)），2025 年数据写入会直接报错，必须提前用 ALTER TABLE ... REORGANIZE PARTITION 扩容，这点极易被忽略
MyISAM 不支持子分区，必须用 InnoDB

常见报错和绕过方式

建表或维护时卡在几个经典错误上，基本都跟表达式合法性或语法顺序有关。

ERROR 1064 (42000): VALUES LESS THAN clause cannot be used with HASH or KEY partitions：说明你在 SUBPARTITION BY HASH 下误写了 VALUES LESS THAN，删掉即可 —— 子分区不定义值范围
ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed：用了 NOW()、CURRENT_DATE 或带时区的函数，换成 YEAR(col)、TO_DAYS(col) 这类确定性表达式
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function：如果表有主键，主键必须包含所有分区键字段（比如主键是 (id)，但分区键是 YEAR(create_time)，就会报错；得改成 PRIMARY KEY (id, create_time) 或去掉主键）
想给已有表加子分区？不行。MySQL 不支持 ALTER TABLE ... PARTITION BY ... SUBPARTITION BY ...，只能导出数据 + 重建表