编程知识 cdmana.com

Partition and table of MySQL

mysql Partition and table of

Partition

Partition is to store the files and indexes of a data table in different physical files .

 


 

mysql Supported partition types include Range、List、Hash、Key, among Range More commonly used :

RANGE Partition : Based on the column value belonging to a given continuous interval , Assign multiple rows to partitions .

LIST Partition : It's like pressing RANGE Partition , The difference lies in LIST Partitioning is based on matching column values to a certain value in a set of discrete values .

HASH Partition : Partition selection based on the return value of user-defined expression , The expression evaluates with the column values of the rows to be inserted into the table . This function can contain MySQL Effective in 、 Any expression that produces a nonnegative integer value .

KEY Partition : It's like pressing HASH Partition , The difference lies in KEY Partition only supports one or more columns to be calculated , And MySQL The server provides its own hash function . One or more columns must contain integer values .

 


 

Case study :

Build a user surface With id partition id Less than 10 The in user_1 Partition id Less than 20 The in user_2 Partition

create table user( id int not null auto_increment, username varchar(10), primary key(id))engine = innodb charset=utf8partition by range (id)(partition user_1 values less than (10),partition user_2 values less than (20));

 

After the foundation of Add partition :

maxvalue Represents the maximum value   This is greater than or equal to 20 Of id All stored in user_3 Partition

alter table user add partition( partition user_3 values less than maxvalue);

 

 


 

Delete partition :

alter table user drop partition user_3;

Now open mysql Data directory for

You can see more user#P#user_1.ibd and user#P#user_2.ibd   These two documents

If the storage engine used by the table is MyISAM type , Namely :

user#P#user_1.MYD,user#P#user_1.MYI and user#P#user_2.MYD,user#P#user_2.MYI

Detailed explanation of partition mode :

* Range( Range ) – This model allows DBA Divide the data into different ranges . for example DBA You can divide a table into three partitions by year ,80 years (1980's) The data of ,90 years (1990's) The data and any information in 2000 year ( Include 2000 year ) Later data .

CREATE TABLE users (   id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,   usersname VARCHAR(30) NOT NULL DEFAULT '',   email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY RANGE (id) (   PARTITION p0 VALUES LESS THAN (3000000),   PARTITION p1 VALUES LESS THAN (6000000),  PARTITION p2 VALUES LESS THAN (9000000),   PARTITION p3 VALUES LESS THAN MAXVALUE  );

ad locum , Divide the user table into 4 Zones , per 300 Ten thousand records as the boundary , Each partition has its own independent data 、 The directory where index files are stored .

 


 

You can also separate the physical disks where these partitions are located, completely independent , Can improve disk IO throughput .

If you want to be a programmer too , Want to master programming quickly , Pay close attention to Xiaobian and join the learning Penguin circle !

There are senior professional software open .........

版权声明
本文为[Irving the procedural ape]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225150217984v.html

Scroll to Top