MySQL Partition

MySQL Partition

Partition divides a database table into distinct partition

  • To manage and manipulate it as a partition.
  • Into physical data partition which may reduce data block read in range scan

Check if partition is supported

mysql> SHOW VARIABLES LIKE '%partition%';

Syntax

mysql> CREATE TABLE tbl (
         col1 VARCHAR(10) NOT NULL,
         col2 VARCHAR(10) NOT NULL,
         ...
       )
       PARTITION BY partition_type(col1)
       PARTITIONS size;

Example, 5 partition based on col1 value

mysql> CREATE TABLE tbl (
         col1 VARCHAR(10) NOT NULL,
         col2 VARCHAR(10) NOT NULL,
         ...
       )
       PARTITION BY KEY(col1)
       PARTITIONS 5;
Partition Partition Based On
RANGE Based on column values falling within a certain range
LIST Based on column values matching a set of values
HASH Based on column hash value calculated by a user function
KEY Based on 1 or more column hash value calculated by a pre-defined hash function
Range Parition
mysql> CREATE TABLE tbl (
        ...
       )
       PARTITION BY RANGE( MONTH(col1) ) (
       PARTITION p0 VALUES LESS THAN (6),
       PARTITION p1 VALUES LESS THAN (13),
       );
List Partition
mysql> CREATE TABLE tbl (
        ...
       )
       PARTITION BY LIST(MONTH(val))(
       PARTITION p0 VALUES IN (1,3,5,7,9,11),
       PARTITION p1 VALUES IN (2,4,6,8,10,12)
       );
Hash Partition

Syntax

mysql> CREATE TABLE tbl (
       ...
       )
       PARTITION BY HASH(func(col1))
       PARTITIONS size;

Samples:

mysql> CREATE TABLE tbl (
       ...
       )
       PARTITION BY HASH(col1)
       PARTITIONS 5;
mysql> CREATE TABLE tbl (
       ...
       )
       PARTITION BY mod(col1, 5)
       PARTITIONS 5;
KEY Partition

KEY partition is similar to HASH except the hash function "func" above is pre-defined by MySQL

Syntax

mysql> CREATE TABLE tbl (
       ...
       )
       PARTITION BY KEY(col1)
       PARTITIONS size;
Sub-Partition
mysql> CREATE TABLE tbl (id INT, hire DATE)
         PARTITION BY RANGE(YEAR(hire))
         SUBPARTITION BY HASH(MONTH(hire))
         SUBPARTITIONS 2 (
         PARTITION p0 VALUES LESS THAN (1980),
         PARTITION p1 VALUES LESS THAN (2010)
       );

Manage MySQL partition

Show table information including partition if any

mysql> SHOW CREATE TABLE tbl;

Repartition the table with another partition scheme

mysql> ALTER TABLE tbl PARTITION BY KEY(col1) PARTITIONS 5;

Drop all rows in p2 partition

mysql> ALTER TABLE tr DROP PARTITION p2;

Operation on partition data

mysql> ALTER TABLE tab REBUILD PARTITION p0, p1;
mysql> ALTER TABLE tab ANALYZE PARTITION p0, p1;
mysql> ALTER TABLE tab OPTIMIZE PARTITION p0, p1;
mysql> ALTER TABLE tab REPAIR PARTITION p0, p1;
mysql> ALTER TABLE tab CHECK PARTITION p0, p1;