MySQL复合分区

MySQL复合分区,mysql复合

到底照旧开源软件,MySQL对复合分区的帮衬远远未有Oracle充足。

在MySQL
伍.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子分区,分为二个区,那样,该表1共分了三*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
        )
    );

注意:

一>
假设你在分区中使用了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
        )
    );

二> 在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,那或多或少或许蛮迷惑人的。

惋惜,在本机测试进程中,报“E冠道ROXC60 1030 (HY000): Got error -一 from storage
engine”错误,具体原因还不太知道,猜忌是MySQL的bug。

参考:

到底照旧开源软件,MySQL对复合分区的补助远远未有Oracle丰盛。 在MySQL
伍.陆本子中,只帮忙RANGE和LIST的子分区,且子…

MySQL复合分区

毕竟照旧开源软件,MySQL对复合分区的支撑远远未有Oracle充分。

 

在MySQL
伍.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分区,分为1个区,里面是HASH子分区,分为1个区,那样,该表壹共分了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
        )
    );

 

 

注意:

 

一>
假使您在分区中应用了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
        )
    );

 

二> 在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,那点依旧蛮吸引人的。

 

心痛,在本机测试进程中,报“ECRUISERRO奥迪Q5 1030 (HY000): Got error -一 from storage
engine”错误,具体原因还不太明白,困惑是MySQL的bug。

到底如故开源软件,MySQL对复合分区的支撑远远未有Oracle丰盛。 在MySQL
5.六版本中,只支持RANGE和LIST的子分区,且子分区的类型…

网站地图xml地图