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”語句移動到其他的位置。

        注：更多精彩文章請關注三聯編程教程欄目。