Mysql partition and instance demonstration

Mysql partition and instance demonstration
I. Why partition? Requirement: big data. Solution: divide and conquer. Divides large tables and large indexes into a smaller operation unit.
In mysql, partitions allow table, index, and index orchestration tables to be subdivided into smaller units. After a partition, each partition has its own name. For DBAs, these partitions can be managed in a unified manner or separately.
However, for applications, the partitioned tables are the same as those without partitions. In other words, partitions are transparent to applications, but the database reorganizes the data.
Mysql partition. This is mysql's official explanation of the partition.
1.Partition FunctionA) Improve Performance


It should be said that almost all partitions are used to improve performance. It's not who eats the Egg bread one morning. Let's have fun in different zones! No. So how does partitioning improve the performance of data retrieval? Before data is partitioned, data is always queried and retrieved for the entire database and table. After partitioning, mysql generates specific data files and index files for each partition. Only a specific part of data is retrieved each time, so it is better to execute and maintain the database. The reason is that after the partition, the table is assigned to different physical drives, so that the physical I/O contention can be reduced when multiple partitions are accessed at the same time.
B) Ease of Management
For some useless redundant historical data, you can directly Delete the corresponding partition based on the partition. It is easier to operate, because it is much more difficult and time-consuming to execute tens of thousands of data records than to delete files directly.
C) Fault Tolerance
After partitioning, a table is divided into three files from the previous file compared to before partitioning. After partitioning, even if a file is damaged, other data will not be affected.
2 ..Common partitioning Methods

RANGE partitioning
LIST Partition
HASH Partition
KEY Partition

Ii. How to partition?

1.Check whether the database supports partitioning

SHOW VARIABLES LIKE '%partition%';

 

For mysql, currently versions 5.1 and support partition operations. If YES, the database supports partition operations.

2. Four common partitions Range Partition

Commonly known as range partition. Partitions are based on the value of a table field and a given continuous interval.

 

Directly create table Time Zone

 

 

create table teacher(id varchar(20) not null ,name varchar(20),age varchar(20),birthdate date not null,salary int)partition by range(year(birthdate))(partition p1 values less than (1970),partition p2 values less than (1990),partition p3 values less than maxvalue);

 

 

Ps: Create a teacher table. When creating a teacher table, divide the table into three partitions: p1, p2, and p3 Based on the birthdate field.

 

 

Partition 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: partitions of the created table, including p1, p2, and p3.

 

LIST Partition

Common Name: List partition. In fact, list partitions and range partitions are the same. The difference is that range partitions are based on a continuous interval; list partitions are based on a group of distributed hash values.

 

create table student (id varchar(20) not null , studentno int(20) not null, name varchar(20), age varchar(20) ) 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 the student table and divide it into three partitions: p1, p2, and p3. Note that the partition field of a table is of the int or other numerical type.

 

HASH Partition

 

Nickname: Hash partition. Hash partitions are based on a table field and the number of specified partitions.

 

create table user (  id int(20) not null,  role varchar(20) not null,  description varchar(50) )partition by hash(id) partitions 10;

 

 

Ps: create a user table and divide the user table into ten partitions on average. The limit is that you need to know how much data the table has to distribute partitions evenly.

 

 

Key Partition

Similar to HASH partitioning, the difference is that KEY partitioning only supports computing one or more columns, and the MySQL server provides its own HASH function. One or more columns must contain integer values.

 

 

create table role( id int(20) not null,name varchar(20) not null)partition by linear key(id)partitions 10;

 

 

 

3. Partition Table Management

Add partitions to a specified table

 

alter table user add partition(partition p4 values less than MAXVALUE);  

 

Deletes a specified partition from a specified table.

Alter table student drop partition p1;

 

Create a subpartition

 

create table role_subp(id int(20) not null,name int(20) not null)partition by list(id)subpartition by hash(name)subpartitions 3(  partition p1 values in(10),  partition p2 values in(20))

 

 

Composite Partition

 

 

alter table userreorganize partition p1,p3 into(partition p1 values less than (1000));