mysql分区 alter_mysql分区举例---子分区

mysql允许RANGE和LIST分区上再进行HASH和KEY的子分区

mysql> create table ts(a int,b date)

-> partition by range(year(b))

-> subpartition by hash(TO_DAYS(b))

-> subpartitions 2(

-> partition p0 values less than (1990),

-> partition p1 values less than (2000),

-> partition p2 values less than maxvalue

-> );

Query OK, 0 rows affected (0.89 sec)

产生6个分区，3*2=6

mysql> select * from information_schema.partitions

-> where table_schema=database() and

-> table_name='ts'\G;

*************************** 1. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: ts

PARTITION_NAME: p0

SUBPARTITION_NAME: p0sp0

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: 1

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: HASH

PARTITION_EXPRESSION: year(b)

SUBPARTITION_EXPRESSION: TO_DAYS(b)

PARTITION_DESCRIPTION: 1990

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 2. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: ts

PARTITION_NAME: p0

SUBPARTITION_NAME: p0sp1

PARTITION_ORDINAL_POSITION: 1

SUBPARTITION_ORDINAL_POSITION: 2

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: HASH

PARTITION_EXPRESSION: year(b)

SUBPARTITION_EXPRESSION: TO_DAYS(b)

PARTITION_DESCRIPTION: 1990

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 3. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: ts

PARTITION_NAME: p1

SUBPARTITION_NAME: p1sp0

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: 1

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: HASH

PARTITION_EXPRESSION: year(b)

SUBPARTITION_EXPRESSION: TO_DAYS(b)

PARTITION_DESCRIPTION: 2000

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 4. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: ts

PARTITION_NAME: p1

SUBPARTITION_NAME: p1sp1

PARTITION_ORDINAL_POSITION: 2

SUBPARTITION_ORDINAL_POSITION: 2

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: HASH

PARTITION_EXPRESSION: year(b)

SUBPARTITION_EXPRESSION: TO_DAYS(b)

PARTITION_DESCRIPTION: 2000

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 5. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: ts

PARTITION_NAME: p2

SUBPARTITION_NAME: p2sp0

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: 1

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: HASH

PARTITION_EXPRESSION: year(b)

SUBPARTITION_EXPRESSION: TO_DAYS(b)

PARTITION_DESCRIPTION: MAXVALUE

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

*************************** 6. row ***************************

TABLE_CATALOG: def

TABLE_SCHEMA: test

TABLE_NAME: ts

PARTITION_NAME: p2

SUBPARTITION_NAME: p2sp1

PARTITION_ORDINAL_POSITION: 3

SUBPARTITION_ORDINAL_POSITION: 2

PARTITION_METHOD: RANGE

SUBPARTITION_METHOD: HASH

PARTITION_EXPRESSION: year(b)

SUBPARTITION_EXPRESSION: TO_DAYS(b)

PARTITION_DESCRIPTION: MAXVALUE

TABLE_ROWS: 0

AVG_ROW_LENGTH: 0

DATA_LENGTH: 16384

MAX_DATA_LENGTH: NULL

INDEX_LENGTH: 0

DATA_FREE: 0

CREATE_TIME: NULL

UPDATE_TIME: NULL

CHECK_TIME: NULL

CHECKSUM: NULL

PARTITION_COMMENT:

NODEGROUP: default

TABLESPACE_NAME: NULL

6 rows in set (0.02 sec)

第二种方式：

CREATE TABLE ts2 (

a int(11) DEFAULT NULL,

b date DEFAULT NULL

)

PARTITION BY RANGE (year(b))

SUBPARTITION BY HASH (to_days(b))

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

几条SQL:

1.create table ts3 like ts2;

建立ts3表和ts2一模一样

2.alter table ts3 drop partition p0;

删除ts3表的p0分区