编程知识 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=utf8

partition 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 development engineers , Answer all your doubts online ~ Introduction to programming language “so easy”

The information contains : Introduction to programming 、 Game programming 、 Curriculum design, etc .

Free learning books :

 


 

Free learning materials :

 

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)  

       DATA DIRECTORY = '/data0/data'  

       INDEX DIRECTORY = '/data0/index',  

       PARTITION p1 VALUES LESS THAN (6000000)  

       DATA DIRECTORY = '/data1/data'  

       INDEX DIRECTORY = '/data1/index',  

       PARTITION p2 VALUES LESS THAN (9000000)  

       DATA DIRECTORY = '/data2/data'  

       INDEX DIRECTORY = '/data2/index',  

       PARTITION p3 VALUES LESS THAN MAXVALUE     

       DATA DIRECTORY = '/data3/data'   

       INDEX DIRECTORY = '/data3/index'  

);  

* List( Predefined list ) –  This mode allows the system to pass through DBA Divide the row data corresponding to the value of the defined list . for example :DBA Partition by user type . 

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     name VARCHAR(30) NOT NULL DEFAULT '' ,

     user_type   int not null

)  

PARTITION BY LIST (user_type ) (  

     PARTITION p0 VALUES IN (0,4,8,12) , 

     PARTITION p1 VALUES IN (1,5,9,13) ,  

     PARTITION p2 VALUES IN (2,6,10,14),  

     PARTITION p3 VALUES IN (3,7,11,15)   

);

Divide into 4 Districts , You can also set the partition to a separate disk .

* Key( Key value )

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     name VARCHAR(30) NOT NULL DEFAULT '',  

     email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY KEY (id) PARTITIONS 4 (  

     PARTITION p0,  

     PARTITION p1,  

     PARTITION p2,  

     PARTITION p3

);

* Hash( Hash )

CREATE TABLE user (  

     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  

     username VARCHAR(30) NOT NULL DEFAULT '',  

     email VARCHAR(30) NOT NULL DEFAULT ''  

)  

PARTITION BY HASH (id) PARTITIONS 4 (  

     PARTITION p0 ,  

     PARTITION p1,  

     PARTITION p2,

     PARTITION p3  

);

table

Sub tables are similar to partitions , The difference is that , Partition is to divide a logical table file into several physical files for storage , The sub table is to divide the original table into several tables . When querying by table, you can use union Or view .

The sub table is divided into vertical division and horizontal division , Among them, horizontal segmentation is the most commonly used . Horizontal segmentation usually refers to segmentation into another database or table . For example, for a membership table , Press right 3 And then we split it up :

table = id%3

If id%3 = 0 Then put the user data into user_0 In the table , Such as id%3=1 Put in user_1 In the table , By analogy .

There's a problem here , This uid It should be all members growing in order , But how did he get it ? Use auto_increment You can't , So that's the sequence .

For some traffic statistics systems , It has a large amount of data , And the attention to the past data is not high , At this time, by year 、 month 、 Day to day , Put daily statistics in a table named by date ; Or divide the table according to the increment , For example, each table 100 All the data , exceed 100 Ten thousand goes into the second watch . You can also press Hash Divide the tables , But the most common is by date and modulus remainder , It is also easy to expand .

New problems may be encountered after splitting the table , That's the query , Pagination and Statistics . The general method is to process in the program , Auxiliary view .

版权声明
本文为[Archangel Vega]所创,转载请带上原文链接,感谢
https://cdmana.com/2020/12/20201225150736483s.html

Scroll to Top