MySQL子分区详解-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_3312/article/details/82540015

前面已经学习了MySQL的分区管理，MySQL还提供了子分区这个概念，子分区是分区表中每个分区的再次分割，具体语法如下：

Sql代码

CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
PARTITION BY RANGE (USR_ID) SUBPARTITION BY HASH(USR_ID)
SUBPARTITIONS 2
(
PARTITION partition_0 VALUES LESS THAN (10000),
PARTITION partition_1 VALUES LESS THAN (20000),
PARTITION partition_2 VALUES LESS THAN (MAXVALUE)
);

表USER有3个RANGE分区。这3个分区中的每一个分区——partition_0,partition_1,partition_2 又被进一步分成了2个子分区。实际上，整个表被分成了3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，这些分区的头2个(也就是partition_0的两个子分区)只保存“USR_ID”列中值小于10000的那些记录。
我们输入以下SQL来查看分区情况：

Sql代码

SELECT table_name,partition_name,subpartition_name,partition_method,subpartition_method,partition_expression,subpartition_expression,partition_description FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='USER';

结果如下：

在MySQL中，对于已经通过RANGE或LIST分区了的表再进行子分区是可能的。子分区既可以使用HASH希分区，也可以使用KEY分区。这也被称为复合分区（Composite Partitioning）。

为了对个别的子分区指定选项，使用SUBPARTITION 子句来明确定义子分区也是可能的。例如，创建在前面例子中给出的同一个表的、一个更加详细的方式如下：

Sql代码

CREATE TABLE `USER` (
`USR_ID` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户编号',
`CUSTORM_ID` INT(11) DEFAULT NULL COMMENT '客户代码',
`STATUS` INT(11) DEFAULT NULL COMMENT '用户状态',
`CREATE_DATE` DATETIME DEFAULT NULL COMMENT '创建日期',
`ACTIV_CODE` VARCHAR(32) DEFAULT NULL COMMENT '激活码',
`USR_TYPE` INT(11) DEFAULT NULL COMMENT '用户类型',
PRIMARY KEY (`USR_ID`)
)
PARTITION BY RANGE (USR_ID) SUBPARTITION BY HASH(USR_ID)
(
PARTITION partition_0 VALUES LESS THAN (10000)
(
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION partition_1 VALUES LESS THAN (20000)
(
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION partition_2 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION s4,
SUBPARTITION s5
)
);

分区结果为：

以下几点需要我们注意：

        1.每个分区必须有相同数量的子分区。
        2.注意两种子分区语句的语法，一个是SUBPARTITIONS+分区数；一个是在内部自定义声明。
        3.如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区，否则会执行失败。即便这个语句包含了一个SUBPARTITIONS 2子句，但是它仍然会执行失败。
        4.每个SUBPARTITION 子句必须包括 (至少)子分区的一个名字。否则，你可能要对该子分区设置任何你所需要的选项，或者允许该子分区对那些选项采用其默认的设置。
        5.在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一。例如，下面的CREATE TABLE 语句是有效的：

Sql代码