MySQL 5.7 子分区 – 但行好事莫问前程

子分区
子分区（也称为复合分区）是分区表中每个分区的进一步划分。请看下面的CREATE TABLE语句：

mysql> CREATE TABLE ts (id INT, purchased DATE)
    -> PARTITION BY RANGE( YEAR(purchased) )
    -> SUBPARTITION BY HASH( TO_DAYS(purchased) )
    -> SUBPARTITIONS 2 (
    -> PARTITION p0 VALUES LESS THAN (1990),
    -> PARTITION p1 VALUES LESS THAN (2000),
    -> PARTITION p2 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.02 sec)

表ts有3个范围分区。每个分区（p0、p1和p2）又进一步划分为2个子分区。实际上，整个表被分为3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，前2个只存储所购列中值小于1990的记录。

在MySQL 5.7中，可以对按范围或列表分区的表进行子分区。子分区可以使用哈希分区或键分区。这也称为复合分区。

也可以使用子分区子句显式定义子分区，为各个子分区指定选项。例如，在前面的例子中，创建相同的表ts的一种更冗长的方式是：

mysql> CREATE TABLE ts (id INT, purchased DATE)
    -> PARTITION BY RANGE( YEAR(purchased) )
    -> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
    -> PARTITION p0 VALUES LESS THAN (1990) (
    -> SUBPARTITION s0,
    -> SUBPARTITION s1
    -> ),
    -> PARTITION p1 VALUES LESS THAN (2000) (
    -> SUBPARTITION s2,
    -> SUBPARTITION s3
    -> ),
    -> PARTITION p2 VALUES LESS THAN MAXVALUE (
    -> SUBPARTITION s4,
    -> SUBPARTITION s5
    -> )
    -> );
Query OK, 0 rows affected (0.03 sec)

下面列出了一些语法注意事项：
.每个分区必须有相同数量的子分区。

.如果您在分区表的任何分区上使用SUBPARTITION显式地定义了任何子分区，则必须定义所有子分区。换句话说，下面的语句会失败：

mysql> CREATE TABLE ts1 (id INT, purchased DATE)
    -> PARTITION BY RANGE( YEAR(purchased) )
    -> SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
    -> PARTITION p0 VALUES LESS THAN (1990) (
    -> SUBPARTITION s0,
    -> SUBPARTITION s1
    -> ),
    -> PARTITION p1 VALUES LESS THAN (2000),
    -> PARTITION p2 VALUES LESS THAN MAXVALUE (
    -> SUBPARTITION s2,
    -> SUBPARTITION s3
    -> )
    -> );
ERROR 1064 (42000): Wrong number of subpartitions defined, mismatch with previous setting near '
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2,
SUBPARTITION s3
)
)' at line 8

即使包含SUBPARTITIONS 2子句，这条语句仍然会失败。

.每个子分区子句必须（至少）包含子分区的名称。否则，您可以为子分区设置任何想要的选项，或者允许它假定该选项的默认设置。

.子分区名在整个表中必须是唯一的。例如，下面的CREATE TABLE语句在MySQL 5.7中是有效的：

CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0,
SUBPARTITION s1
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2,
SUBPARTITION s3
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4,
SUBPARTITION s5
)
);

子分区可以用于特别大的MyISAM表，以便在许多磁盘上分发数据和索引。假设您有6个磁盘挂载为/disk0、/disk1、/disk2等。现在考虑下面的例子：

CREATE TABLE ts (id INT, purchased DATE)
ENGINE = MYISAM
PARTITION BY RANGE( YEAR(purchased) )
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0
DATA DIRECTORY = '/disk0/data'
INDEX DIRECTORY = '/disk0/idx',
SUBPARTITION s1
DATA DIRECTORY = '/disk1/data'
INDEX DIRECTORY = '/disk1/idx'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s2
DATA DIRECTORY = '/disk2/data'
INDEX DIRECTORY = '/disk2/idx',
SUBPARTITION s3
DATA DIRECTORY = '/disk3/data'
INDEX DIRECTORY = '/disk3/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s4
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s5
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
)
);

在这种情况下，为每个RANGE的数据和索引使用一个单独的磁盘。许多其他的变化是可能的；另一个例子可能是：

CREATE TABLE ts (id INT, purchased DATE)
ENGINE = MYISAM
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH( TO_DAYS(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990) (
SUBPARTITION s0a
DATA DIRECTORY = '/disk0'
INDEX DIRECTORY = '/disk1',
SUBPARTITION s0b
DATA DIRECTORY = '/disk2'
INDEX DIRECTORY = '/disk3'
),
PARTITION p1 VALUES LESS THAN (2000) (
SUBPARTITION s1a
DATA DIRECTORY = '/disk4/data'
INDEX DIRECTORY = '/disk4/idx',
SUBPARTITION s1b
DATA DIRECTORY = '/disk5/data'
INDEX DIRECTORY = '/disk5/idx'
),
PARTITION p2 VALUES LESS THAN MAXVALUE (
SUBPARTITION s2a,
SUBPARTITION s2b
)
);

这里，存储如下：
.购买日期在1990年之前的行占用了大量的空间，因此以4种方式拆分，一个单独的磁盘专门用于存储数据和两个子分区（s0a和s0b）的索引，这两个分区组成了p0分区。换句话说：
子分区s0a的数据存储在/disk0上。

.子分区s0a的数据存储在/disk0上。
.子分区s0a的索引存储在/disk1上。
.子分区s0b的数据存储在/disk2上。
.子分区s0b的索引存储在/disk3上。

.包含从1990年到1999年的日期的行（分区p1）不需要像1990年以前的行那样多的空间。它们被划分为2个磁盘（/disk4和/disk5），而不是像以前那样存储在p0中的4个磁盘。

.属于p1的第一个子分区（s1a）的数据和索引存储在/disk4中，其中数据存储在/disk4/ Data中，索引存储在/disk4/idx中。
.属于p1的第二个子分区（s1b）的数据和索引存储在/disk5中——数据存储在/disk5/ Data中，索引存储在/disk5/idx中。

.反映2000年至今日期的行（分区p2）所占空间与前两个范围相比都没有那么大。目前，将所有这些都存储在默认位置就足够了。

将来，当从2000年开始的十年的购买数量增长到默认位置不再提供足够的空间时，可以使用ALTER TABLE … REORGANIZE PARTITION语句移动相应的行。

发表评论 取消回复

发表评论取消回复