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

　　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分区那么大的空间。当前，在默认的位置能够足够保存所有这些记录。