MySQL分区表（二）

说明：之前有写过一篇博客，介绍MySQL如何建立分区表，本文介绍如何建立子分区表。子分区，就是在原来分区的基础上，再嵌套一个分区。

例如，按照记录的创建时间分区，在此基础上，再按照租户ID嵌套一个子分区。如果按照时间建立了12个分区，租户ID有3个，那么最终建立的子分区就是：12 × 3 = 36个；

下面介绍如何建立和使用子分区：

建立

如下，建立一个子分区表，tb_user；

-- 建立子分区表tb_user
CREATE TABLE `tb_user`
(
    `id`          int         NOT NULL AUTO_INCREMENT,
    `tenant_id`   varchar(50) NOT NULL,
    `username`    varchar(20) DEFAULT NULL,
    `password`    varchar(20) DEFAULT NULL,
    `create_date` bigint      NOT NULL,
    PRIMARY KEY (`id`, `tenant_id`, `create_date`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci
    COMMENT ='用户表'
    PARTITION BY RANGE (`create_date`) SUBPARTITION BY key (`tenant_id`)
        (
        PARTITION tb_user_1735660800000 VALUES LESS THAN (1735660800000)
            (
            SUBPARTITION tb_user_1735660800000_01,
            SUBPARTITION tb_user_1735660800000_02
            ),
        PARTITION tb_user_1767196800000 VALUES LESS THAN (1767196800000)
            (
            SUBPARTITION tb_user_1767196800000_01,
            SUBPARTITION tb_user_1767196800000_02
            )
        );

PARTITION BY后面部分是分区相关的SQL，表示按照create_date字段range分区，后根据tenant_id字段key分区，以上是建表时就定义好的。

如果是针对一张已存在的表建立子分区，就用下面这个，当然，建立分区的字段需要是主键，如果不是，需要先修改表结构；

-- 对tb_table建立子分区
ALTER TABLE tb_user PARTITION BY RANGE (`create_date`) SUBPARTITION BY key (`tenant_id`)
    (
    -- 2025-01-01 00:00:00
    PARTITION tb_user_1735660800000 VALUES LESS THAN (1735660800000)
        (
        SUBPARTITION tb_user_1735660800000_01,
        SUBPARTITION tb_user_1735660800000_02
        ),
    -- 2026-01-01 00:00:00
    PARTITION tb_user_1767196800000 VALUES LESS THAN (1767196800000)
        (
        SUBPARTITION tb_user_1767196800000_01,
        SUBPARTITION tb_user_1767196800000_02
        )
    );

执行后，可以看到这张表的分区，是有两层级的。

使用

下面插入四条数据，如下：

-- 插入数据
-- 1706803200000：2024-02-02 00:00:00
-- 1706803200000：2025-02-02 00:00:00
insert into `tb_user` (tenant_id, username, password, create_date)
values ('1', '分区1', '123456', 1706803200000),
       ('2', '分区2', '12345', 1738425600000),
       ('2', '分区3', '1234', 1706803200000),
       ('1', '分区4', '123', 1738425600000);

然后敲下面的SQL，查看各个分区的情况：

-- 查看各个分区数据
select
    PARTITION_NAME,
    SUBPARTITION_NAME,
    partition_ordinal_position,
    partition_method,
    partition_expression,
    partition_description,
    table_name,
    table_rows
from information_schema.partitions
where table_name = 'tb_user';

可以看到，每个子分区里各存了一条记录，说明分区建立成功了。

另外

子分区，需要注意以下两点：

（1）对现有表建立分区，历史数据不会被整理到指定分区里，只有修改了历史记录的分区字段，才会被分配到指定分区里；

（2）当数据从一个分区修改到另一个分区时，会落入到子分区值相同的那个分区；

第1点，如下，先插入数据，再分区，查看分区情况，

-- 1.创建表
CREATE TABLE `tb_user`
(
    `id`          int         NOT NULL AUTO_INCREMENT,
    `tenant_id`   varchar(50) NOT NULL,
    `username`    varchar(20) DEFAULT NULL,
    `password`    varchar(20) DEFAULT NULL,
    `create_date` bigint      NOT NULL,
    PRIMARY KEY (`id`, `tenant_id`, `create_date`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci COMMENT ='用户表';

-- 2.插入数据
-- 1706803200000：2024-02-02 00:00:00
-- 1738425600000：2025-02-02 00:00:00
insert into `tb_user` (tenant_id, username, password, create_date)
values ('1', '分区1', '123456', 1706803200000),
       ('2', '分区2', '12345', 1738425600000),
       ('2', '分区3', '1234', 1706803200000),
       ('1', '分区4', '123', 1738425600000);

-- 3.分区
alter table tb_user PARTITION BY RANGE (`create_date`) SUBPARTITION BY key (`tenant_id`)
    (
    PARTITION tb_user_1735660800000 VALUES LESS THAN (1735660800000)
        (
        SUBPARTITION tb_user_1735660800000_01,
        SUBPARTITION tb_user_1735660800000_02
        ),
    PARTITION tb_user_1767196800000 VALUES LESS THAN (1767196800000)
        (
        SUBPARTITION tb_user_1767196800000_01,
        SUBPARTITION tb_user_1767196800000_02
        )
    );

-- 4.查看各个分区数据
select PARTITION_NAME,
       SUBPARTITION_NAME,
       partition_ordinal_position,
       partition_method,
       partition_expression,
       partition_description,
       table_name,
       table_rows
from information_schema.partitions
where table_name = 'tb_user';

数据没有落入到分区里；

如果修改历史数据的分区字段，如下：

-- 5.修改数据
UPDATE tb_user
SET tenant_id = '2'
WHERE id = 1;

数据分配到了指定分区里；

第2点，如上，create_date是一级分区字段，tenant_id是二级分区字段，当某条记录修改了create_date，从一个分区换到了另一个分区，数据会落入到这个分区里tenant_id相同值的那个子分区。（感觉是废话，这是当然的）

总结

本文介绍了如何在MySQL中建立子分区，关于MySQL建立List、Range分区，参考下面这篇文章：

• MySQL分区表（一）