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分区,分为一个区,里面是HASH子分区,分为二个区,那样,该表一共分了3*2=6个分区。

当然,也能够用SUBPA奥迪Q7TITION语句来展现定义子分区。

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>
借使你在分区中央银行使了SUBPA中华VTITION语句,则每种分区中都亟须定义,且各个分区中子分区的数额必须保持一致。譬如以下二种用法就会报错:

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> 在SUBPA大切诺基TITION语句中,可钦赐该分区的大体地点。譬如:

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,那或多或少要么蛮吸引人的。

可惜,在本机测试进度中,报“EXC60ROKuga 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

相关文章