MySQL partition Overview

Environment:

mysql> select version();+-----------+| version() |+-----------+| 5.5.28    |+-----------+1 row in set (0.00 sec)

Partition is a table design mode.

Partitioning, divide and conquer, focuses on high availability (management), and ancillary value is the improvement of performance

And:
Transparent to the storage engine Layer
Transparent to Application Layer

Supported partition types:

For tables:
Horizontal partitioning → records of different rows in the same table are allocated to different physical files.
For indexes:
▼ Local partition index → A partition stores both data and Indexes

You can run the following command to check whether the partition function is enabled for the current database:

mysql> show variables like '%partition%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| have_partitioning | YES   |+-------------------+-------+1 row in set (0.00 sec)

MySQL partition type:
● Range partitioning
● List partitioning
● Hash Partition
● Key Partition

No matter what type of partition is created, if the table has a primary key or a unique index, the partition column must be an integral part of the unique index.

mysql> create table t( col1 int not null,    ->                 col2 int not null,    ->                 col3 int not null,    ->                 col4 int not null,    ->                 unique key (col1,col2)    ->               )    -> partition by hash(col3)    -> partitions 4;ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functionmysql> create table t( col1 int not null,    ->                 col2 int not null,    ->                 col3 int not null,    ->                 col4 int not null,    ->                 unique key (col1,col2)    ->               )    -> partition by hash(col2)    -> partitions 4;Query OK, 0 rows affected (0.08 sec)