1, What is partition ###### 1 and what is partition table

Partitioned table is to divide the data of the same table into multiple storage locations according to some rules. Partitioned storage of data improves the performance of the database. The data to be stored is physically multiple files, but logically it is still a table. Any operation on the table is the same as before there is no partition. When performing operations such as adding, deleting, modifying, and querying, the database will automatically find the corresponding partition through the and then perform the operation.

Tip:

1. MySQL supports partitioning from 5.1.3;
2. In MySQL 8.0, only InnoDB and NDB storage engines support partitioning.

2, Benefits of partitioned tables

1. Store more
   More data can be stored than a single disk or file system partition.
2. Improve query throughput
   Greater query throughput is achieved by dispersing data queries across multiple disks.
3. parallel processing
   Queries designed to aggregate functions such as sum() and count() can be easily processed in parallel.
4. Improve query efficiency
   Some queries can be greatly optimized. Queries only get data from one or several partitions.
5. Easy to manage
   It is easy to delete unprotected historical data according to the partition.

3, Four types of partitioned tables

The partition types supported by MySQL include Range, List, Hash and Key. The most commonly used is Range.

1. Range partition
   Data can be divided into different ranges. Multiple rows are allocated to multiple partitions based on column values belonging to a given continuous interval. For example, the student table is divided into several partitions according to the year of birth. An example of creating a Range partition is as follows:

create table student(
    id int not null auto_increment,
    name varchar(30),
    year int,
    province int,
    primary key(id,year)
)
partition by RANGE(year)(
    partition p1 VALUES LESS THAN (1990) DATA DIRECTORY='d:/data/p1',
     partition p2 VALUES LESS THAN (1995) DATA DIRECTORY='d:/data/p2',
      partition p3 VALUES LESS THAN (2000) DATA DIRECTORY='d:/data/p3',
       partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTORY='d:/data/p4',
)

In the code, a student table is created first, and then partitioned with the year field. In SQL, p1, p2, p3 and p4 are partition names, followed by VALUES are partition conditions, and DATA DIRECTORY is the storage location of partition files. It should be noted here that if you want to use a field for Range partitioning, you must gradually include the partition field.

1. List partition
   The pre ordering system divides the data through the values of the predefined list. It selects a value in a discrete set based on the column value matching. For example, the student table is divided by age. The example code is as follows:

create table student(
    id int not null auto_increment,
    name varchar(30),
    age int,
    province int,
    primary key(id,age)
)
partition by RANGE(age)(
    partition p1 VALUES IN (20,21,22,23,24),
     partition p2 VALUES IN (25,26,27,28,29),
      partition p3 VALUES IN (30,31,32,33,34)
)

1. Hash partition
   It is allowed to calculate the Hash Key of one or more columns of the table, and finally partition the data area corresponding to different values of the Hash code. Partition selection is based on the value returned by the user-defined expression. For example, it is divided into 10 districts according to the age of students. The example code is as follows:

create table student(
    id int not null auto_increment,
    name varchar(30),
    age int,
    province int,
    primary key(id,age)
)
partition by HASH(age)
partition 10;

1. Key partition
   An extension of the Hash mode. The Hash Key here is generated by the MySQL system. For example, it is divided into 10 districts according to the age of students. The example code is as follows:

create table student(
    id int not null auto_increment,
    name varchar(30),
    age int,
    province int,
    primary key(id,age)
)
partition by key(age)
partition 10;

4, Common partition and other operations

1. New partition

# grammar
alter table 'table_name' add partition(Zoning conditions)
# example
alter table 'student' add partition(paetition p5 VALUES LESS THAN MAXVALUE);

1. Partition an existing table

# grammar
alter table 'table_name' partition by Partition type(Partition condition column)(
    Zoning conditions
)
# example
alter table 'student' partition by RANGE(age)(
    partition p1 VALUES LESS THAN (20) DATA DIRECTPRY='d:/data/p1',
    partition p2 VALUES LESS THAN (30) DATA DIRECTPRY='d:/data/p2',
    partition p3 VALUES LESS THAN (40) DATA DIRECTPRY='d:/data/p3',
    partition p4 VALUES LESS THAN MAXVALUE DATA DIRECTPRY='d:/data/p4',
)

1. delete a partition

# grammar
alter table 'table_name' drop partition Partition name;
# example
alter table 'student' drop partition p1;

1. Remove partition

# grammar
alter table 'table_name' remove partitioning
# example
alter table 'student' remove partitioning

Tip: deleting a partition will delete the data under the partition, and the data will not be removed. In addition, deleting a partition can specify the partition to be deleted, but removing a partition is to remove the partition of the whole table. Deleting partitions does not apply to tables that use HASH partitions.