MySQL partition table

Partition Table

Advantages of partitioned tables:
Data is stored separately in different files and can support larger data files
Improve efficiency of queries, deletions, and updates under certain conditions

1. Range partition Table
Mysql> CREATE TABLE mytest (ID int,name varchar () not Null,birthday date,primary key (id,birthday)) auto_increment=1 Partition by Range (birthday) (partition P01 values less than (+), partition P02 values less than, partitio n p03 values less than maxvalue);


Exercise: Create a new table with student number, student name, contact number, four fields of the faculty, including 5 numeric values, respectively
1, 2, 3, 4, 5 represents five different faculties, which require that the data in the table be saved to 5 different data files according to the hospital system, after the creation is completed
Observe information such as the name and size of the data file, and insert a few data

2. list partition Table
Mysql> CREATE TABLE mytest1 (SID int not null,name varchar (a), area varchar (ten)) partition by list (SID) (Partition P01 Values in (partition), the P02 values in (3,4), the partition P03 values in (5,6));
Note: Columns that require partitioning must be of type integer

Exercise: Create a table with employee ID, employee name, employee address, 1, 4, and 7 of the employee ID in the P01 partition, 2, 5, 8
Store to the P02 partition, 3, 6, 9 to the P03 partition, and then store 10 data with an ID of 1-10 to observe the storage situation


Mysql> CREATE TABLE Xixi (ID int not null,name varchar (a), addr varchar) partition by list (ID%10) (Partition P01 VA Lues in (1,4,7), partition P02 values in (2,5,8), partition P03 values in (3,6,9));


3. Hash partition Table
Mysql> CREATE TABLE mytest1 (SID int not null,name varchar (a), area varchar (ten)) partition by hash (SID) Partitions 3;

Mysql> CREATE TABLE mytest1 (SID int not null,name varchar (a), area varchar (ten)) partition by linear hash (SID) Partitio NS 3;

4. Key Partition
Mysql> CREATE TABLE mytest1 (SID int not null,name varchar), area varchar (+) partition by key (area) partitions 3;

Mysql> CREATE TABLE mytest1 (SID int not null,name varchar), area varchar (+) partition by linear key (area) Partitio NS 3;
5. Partition Management
New Partition
Mysql> ALTER TABLE Sinfo add partition (partition GD06 values less than (7));
mysql> ALTER TABLE MYTEST1 partition by key (area) partitions 4;

ALTER TABLE haha add partition (partition P03 values in (3,6,9));

Delete Partition
mysql> ALTER TABLE Sinfo drop partition gd01;
mysql> ALTER TABLE MYTEST1 partition by key (area) partitions 3;

Merging Partitions

Mysql> ALTER TABLE Sinfo REORGANIZE partition p03,p04 into (
Partition p034 values in (0,3,6,9));

This article is from the "Kenasel" blog, make sure to keep this source http://kenasel.blog.51cto.com/10620829/1839493

MySQL partition table