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分区