mysql数据库子分区教程

　　mysql数据库子分区教程

　　子分区是分区表中每个分区的再次分割。例如，考虑下面的CREATE TABLE 语句：

　　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

　　);

　　表ts 有3个RANGE分区。这3个分区中的每一个分区——p0, p1, 和 p2 ——又被进一步分成了2个子分区。实际上，整个表被分成了3 * 2 = 6个分区。但是，由于PARTITION BY RANGE子句的作用，这些分区的头2个只保存“purchased”列中值小于1990的那些记录。

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

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

　　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

　　)

　　);

　　几点要注意的语法项：

　　· 每个分区必须有相同数量的子分区。

　　· 如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区，那么就必须定义所有的子分区。换句话说，下面的语句将执行失败：

　　· 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),

　　· PARTITION p2 VALUES LESS THAN MAXVALUE

　　· (

　　· SUBPARTITION s2,

　　· SUBPARTITION s3

　　· )

　　· );

　　即便这个语句包含了一个SUBPARTITIONS 2子句，但是它仍然会执行失败。

　　· 每个SUBPARTITION 子句必须包括 (至少)子分区的一个名字。否则，你可能要对该子分区设置任何你所需要的选项，或者允许该子分区对那些选项采用其默认的设置。

　　· 在每个分区内，子分区的名字必须是唯一的，但是在整个表中，没有必要保持唯一。例如，下面的CREATE TABLE 语句是有效的：

　　· 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 s0,

　　· SUBPARTITION s1

　　· ),

　　· PARTITION p2 VALUES LESS THAN MAXVALUE

　　· (

　　· SUBPARTITION s0,

　　· SUBPARTITION s1

　　· )

　　· );

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

　　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

　　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 s0

　　DATA DIRECTORY = '/disk2/data'

　　INDEX DIRECTORY = '/disk2/idx',

　　SUBPARTITION s1

　　DATA DIRECTORY = '/disk3/data'

　　INDEX DIRECTORY = '/disk3/idx'

　　),

　　PARTITION p2 VALUES LESS THAN MAXVALUE

　　(

　　SUBPARTITION s0

　　DATA DIRECTORY = '/disk4/data'

　　INDEX DIRECTORY = '/disk4/idx',

　　SUBPARTITION s1

　　DATA DIRECTORY = '/disk5/data'

　　INDEX DIRECTORY = '/disk5/idx'

　　)

　　);

　　在这个例子中，每个RANGE分区的数据和索引都使用一个单独的磁盘。还可能有许多其他的变化;下面是另外一个可能的例子：

　　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 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年前的记录占了大量的存储空间，所以把它分为了四个部分进行存储，组成p0分区的两个子分区(s0a 和s0b)的数据和索引都分别用一个单独的磁盘进行存储。换句话说：

　　o 子分区s0a 的数据保存在磁盘/disk0中。

　　o 子分区s0a 的索引保存在磁盘/disk1中。

　　o 子分区s0b 的数据保存在磁盘/disk2中。

　　o 子分区s0b 的索引保存在磁盘/disk3中。

　　· 保存购买日期从1990年到1999年间的记录(分区p1)不需要保存购买日期在1990年之前的记录那么大的存储空间。这些记录分在2个磁盘(/disk4和/disk5)上保存，而不是4个磁盘：

　　o 属于分区p1的第一个子分区(s1a)的数据和索引保存在磁盘/disk4上 — 其中数据保存在路径/disk4/data下，索引保存在/disk4/idx下。

　　o 属于分区p1的第二个子分区(s1b)的数据和索引保存在磁盘/disk5上 — 其中数据保存在路径/disk5/data下，索引保存在/disk5/idx下。

　　· 保存购买日期从2000年到现在的记录(分区p2)不需要前面两个RANGE分区那么大的空间。当前，在默认的位置能够足够保存所有这些记录。

　　将来，如果从2000年开始后十年购买的数量已经达到了默认的位置不能够提供足够的保存空间时，相应的记录(行)可以通过使用“ALTER TABLE ... REORGANIZE PARTITION”语句移动到其他的位置。