MySQL複合分區

MySQL複合分區

到底還是開源軟體，MySQL對複合分區的支援遠遠沒有Oracle豐富。在MySQL 5.6版本中，只支援RANGE和LIST的子分區，且子分區的類型只能為HASH和KEY。

譬如：

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    );

上述建立語句中，最外層是RANGE分區，分為3個區，裡面是HASH子分區，分為2個區，這樣，該表一共分了3*2=6個分區。

當然，也可以用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        )    );

注意：

1> 如果你在分區中使用了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        ),        PARTITION p2 VALUES LESS THAN MAXVALUE (            SUBPARTITION s3,            SUBPARTITION s4        )    );

  

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        )    );

2> 在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 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        )    );

以上這個建立語句，將不同的分區分布到不同的實體路徑下，無疑會極大的分散IO，這一點還是蠻迷人的。

可惜，在本機測試過程中，報“ERROR 1030 (HY000): Got error -1 from storage engine”錯誤，具體原因還不太清楚，懷疑是MySQL的bug。

參考：

http://dev.mysql.com/doc/refman/5.6/en/partitioning-subpartitions.html

http://dev.mysql.com/doc/refman/5.6/en/tablespace-placing.html

本文永久更新連結地址：