mysql表分区6_MySQL 表分区（六）

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

Query OK,0 rows affected (0.48sec)

mysql>desc information_schema.partitions;+-------------------------------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------------------------------+---------------------+------+-----+---------+-------+

| TABLE_CATALOG | varchar(64) | NO | | NULL | |

| TABLE_SCHEMA | varchar(64) | NO | | NULL | |

| TABLE_NAME | varchar(64) | NO | | NULL | |

| PARTITION_NAME | varchar(64) | YES | | NULL | |

| SUBPARTITION_NAME | varchar(64) | YES | | NULL | |

| PARTITION_ORDINAL_POSITION | int(6) unsigned | YES | | NULL | |

| SUBPARTITION_ORDINAL_POSITION | int(6) unsigned | YES | | NULL | |

| PARTITION_METHOD | varchar(13) | YES | | NULL | |

| SUBPARTITION_METHOD | varchar(13) | YES | | NULL | |

| PARTITION_EXPRESSION | varchar(2048) | YES | | NULL | |

| SUBPARTITION_EXPRESSION | varchar(2048) | YES | | NULL | |

| PARTITION_DESCRIPTION | text | YES | | NULL | |

| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |

| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |

| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |

| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |

| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |

| DATA_FREE | bigint(21) unsigned | YES | | NULL | |

| CREATE_TIME | timestamp | NO | | NULL | |

| UPDATE_TIME | datetime | YES | | NULL | |

| CHECK_TIME | datetime | YES | | NULL | |

| CHECKSUM | bigint(21) | YES | | NULL | |

| PARTITION_COMMENT | text | NO | | NULL | |

| NODEGROUP | varchar(256) | YES | | NULL | |

| TABLESPACE_NAME | varchar(259) | YES | | NULL | |

+-------------------------------+---------------------+------+-----+---------+-------+

25 rows in set (0.01sec)

mysql> select partition_name,subpartition_name from information_schema.partitions where table_name='ts';+----------------+-------------------+

| PARTITION_NAME | SUBPARTITION_NAME |

+----------------+-------------------+

| p0 | p0sp0 |

| p0 | p0sp1 |

| p1 | p1sp0 |

| p1 | p1sp1 |

| p2 | p2sp0 |

| p2 | p2sp1 |

+----------------+-------------------+

6 rows in set (0.00sec)

mysql> selectto_days(now());+----------------+

| to_days(now()) |

+----------------+

| 737917 |

+----------------+

1 row in set (0.00sec)

mysql>desc ts;+-----------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| purchased | date | YES | | NULL | |

+-----------+---------+------+-----+---------+-------+

2 rows in set (0.00sec)

mysql> insert into ts values(1,now());

Query OK,1 row affected, 1 warning (0.10sec)

mysql> insert into ts values(1,'1980-01-01');

Query OK,1 row affected (0.09sec)

mysql> select * fromts partition(p1);

Emptyset (0.00sec)

mysql> select * fromts partition(p2);+------+------------+

| id | purchased |

+------+------------+

| 1 | 2020-05-07 |

+------+------------+

1 row in set (0.00sec)

mysql> select * fromts partition(p0sp0);+------+------------+

| id | purchased |

+------+------------+

| 1 | 1980-01-01 |

+------+------------+

1 row in set (0.00sec)

mysql> select * fromts partition(p0sp1);

Emptyset (0.00sec)

mysql> select purchased,to_days(purchased) fromts;+------------+--------------------+

| purchased | to_days(purchased) |

+------------+--------------------+

| 1980-01-01 | 723180 |

| 2020-05-07 | 737917 |

+------------+--------------------+

2 rows in set (0.00sec)

mysql> select * fromts partition(p2sp0);

Emptyset (0.00sec)

mysql> select * fromts partition(p2sp0);

Emptyset (0.00sec)

mysql> select * fromts partition(p2);+------+------------+

| id | purchased |

+------+------------+

| 1 | 2020-05-07 |

+------+------------+

1 row in set (0.00sec)

mysql> explain select * from ts where purchased='2020-05-07';+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | ts | p2_p2sp1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)