MySQL Partition and Example demo

First, why partition?

Requirements: Big Data. Solution: Divide and conquer, the finer point is. Dividing large tables and large indexes into smaller operations units
In MySQL, partitioning allows tables, indexes, and index orchestration tables to be subdivided into smaller units. After partitioning, each partition has its own separate name. For DBAs, these partitions can be managed uniformly or separately.
However, for applications, partitioned tables are the same as those that do not have partitions. In other words, the partition is transparent to the application, but the database is re-organized for the data.
MySQL partition, this is the official explanation of MySQL for the partition.

1. the role of partitioning

A) Improve performance


It should be said that almost all partitions are designed to improve performance. Not who eats eggs in the morning to eat cakes, said we have to play in a district! No. So how does partitioning improve the performance of retrieving data? Before partitioning, data is always oriented to the entire database and table at query and retrieval time. After partitioning, MySQL generates specific data files and index files for each partition. Only certain portions of the data are retrieved at a time, so the database can be executed and maintained better. The reason for this is because the table is assigned to a different physical drive after partitioning, which reduces physical I/O contention when accessing multiple partitions concurrently.
b) Easy to manage
For some useless redundant historical data, it is possible to delete the corresponding partition directly after the partition. It is easier to operate because tens of thousands of data are far more difficult and longer to execute than to delete files directly.
c) Fault tolerance
After partitioning, a table is divided into three files from the previous file, compared to partitions. After partitioning, even if a file is corrupted, it does not affect other data.
2. Common Partitioning method

Range partition
List partition
Hash partition
Key partition

Second, how to partition?

1. See if the database supports partitioning

SHOW VARIABLES like '%partition% ';

For MySQL, there are 5.1 and 5.5 versions that support partitioning operations at this stage. Display as Yes indicates that the database supports partitioning operations.

2. Four Common partitions Range Partitioning

Commonly known as: range partitioning. Based on the value of the field in the table, partitioning is based on a contiguous interval given a segment.

Partitioning when creating tables directly

CREATE TABLE teacher (ID varchar () NOT NULL, name varchar (+), age varchar (), birthdate date not null,salary int) partitio n by Range (birthdate) (partition P1 values less than (1970), partition P2 values less than (1990), Partition P3 values less than MaxValue);

Ps: Create a teacher table and divide the table into P1, P2, p3 three partitions based on the Birthdate field while creating the teacher table.

Partitioning after creating a table

ALTER TABLE Teacher partition by range (year (birthdate)) (partition P1 values less than (1970), partition P2 values less than (1990), partition P3 values less than maxvalue);

Ps: To the table partition that has been created, divided into P1, p2, p3.

LIST Partitioning

Common names: List partitions. In fact, the list partition and range partition should be said to be the same, the difference is that the range partition is based on a continuous interval, whereas the list partition is based on a set of distributed hash values.

CREATE TABLE student (ID varchar () NOT NULL, Studentno Int (a) not NULL, name varchar (+), age varchar ()) partition By list (Studentno) (partition P1 values in (1,2,3,4), partition P2 values in  (5,6,7,8), partition P3 values in (9,10, 11));

Ps: CREATE table student as above, and divide the student table into three partitions of P1, P2, p3. It is important to note that in general, the partitioning field for a table is a numeric type such as int.

HASH Partitioning

Nickname: Hash partition. The hash partition is primarily based on a field of the table and the number of partitions specified.

CREATE TABLE user (  ID int () NOT NULL,  role varchar (a) NOT NULL,  description varchar ()) partition by hash (ID) partitions 10;

Ps: Create the user table above and divide the user table by an average of 10 partitions. The more restrictive is the need to know how much of the table's data is needed to better distribute the partition evenly.

Key Partitioning

Like partitioning by hash, the difference is that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.

CREATE TABLE role (ID int (a) not null,name varchar (a) not NULL) partition by linear key (ID) partitions 10;

3. Partition Table Management

To add a partition to a specified table

ALTER TABLE user add partition (partition P4 values less than MAXVALUE);  

Deletes the specified table specified partition

ALTER TABLE student drop partition P1;

Creating sub-partitions

CREATE TABLE ROLE_SUBP (ID int () not null,name Int (a) not NULL) partition by list (ID) subpartition by hash (name) Subpartiti ONS 3 (  partition P1 values in (ten),  partition P2 values in (20))

Composite partitioning

ALTER TABLE userreorganize partition P1,P3 into (partition P1 values less than (1000));

MySQL Partition and Example demo