MySql子分区

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

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来查看分区情况：

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 子句来明确定义子分区也是可能的。例如，创建在前面例子中给出的同一个表的、一个更加详细的方式如下：

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 语句是有效的：

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 s0,
            SUBPARTITION s1
    ),
    PARTITION partition_2 VALUES LESS THAN (MAXVALUE)
    (
            SUBPARTITION s0,
            SUBPARTITION s1
    )
);

 

        子分区可以用于特别大的表，在多个磁盘间分配数据和索引。假设有6个磁盘(Linux)，分别为/disk0,/disk1../disk5等。现在考虑下面的例子：

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
                DATA DIRECTORY = '/disk0/data'
                INDEX DIRECTORY = '/disk0/idx',
            SUBPARTITION s1
                DATA DIRECTORY = '/disk1/data'
                INDEX DIRECTORY = '/disk1/idx'
    ),
    PARTITION partition_1 VALUES LESS THAN (20000)
    (
            SUBPARTITION s2
                DATA DIRECTORY = '/disk2/data'
                INDEX DIRECTORY = '/disk2/idx',
            SUBPARTITION s3
                DATA DIRECTORY = '/disk3/data'
                INDEX DIRECTORY = '/disk3/idx'
    ),
    PARTITION partition_2 VALUES LESS THAN (MAXVALUE)
    (
            SUBPARTITION s4
                DATA DIRECTORY = '/disk4/data'
                INDEX DIRECTORY = '/disk4/idx',
            SUBPARTITION s5
                DATA DIRECTORY = '/disk5/data'
                INDEX DIRECTORY = '/disk5/idx'
    )
);

 

        关于磁盘的分配还可以出现诸如数据文件与索引不在同一块磁盘的情况、数据量大的分区单独占有一块硬盘等等，这些还等我们在实际工作中灵活运用。