The use of Mysql database partitioning function Tutorial _mysql

0, what is a database partition
to say what is a database partition, take MySQL for example. The data in the MySQL database is documented as the situation exists on the disk, by default placed in the/mysql/ Data below (can be viewed through the datadir in my.cnf), a table mainly corresponds to three files, one is the FRM table structure, one is the MyD table data, one is the Myi table index. If the amount of data in a table is too large, then the myd,myi will become very large, find the data will be very slow, this time we can take advantage of the MySQL partition function, in physics, this table corresponding to the three files, split into many small pieces, so that we look up a data, we do not have to search all the , just know where this piece of data is, and then find it together. If the table's data is too large for a disk to fit in, we can assign the data to a different disk.

One, horizontal zoning
what is a horizontal partition? is horizontal to partition, for example, Ming, if there are 100W data, divided into 10, the first 10W data to be placed in the primary partition, the second 10W data to the second partition, and so on. That is to divide the table into the very, the root uses the merge to divide the table, a bit like oh. When you take out a piece of data, the data contains all the fields in the table structure, that is, the horizontal partitions, without altering the structure of the table.

ALTER TABLE ' Yl_hospital_url ' PARTITION by RANGE (ID) ( 
PARTITION ' p0 ' VALUES less THAN (100000),  
PARTITION ' P1 ' V Alues less THAN (200000),  
PARTITION ' P2 ' values less THAN (300000),  
PARTITION ' p3 ' values less THAN (400000), 
   partition ' P4 ' values less THAN (500000),  
PARTITION ' P5 ' values less THAN (600000),  
PARTITION ' P6 ' values LES S THAN (700000), 
PARTITION ' P6 ' values less THAN (700000),  
PARTITION ' P7 ' values less THAN (MAXVALUE)  
); 

Query speed before partitioning

Query speed before partitioning

Second, the MySQL partition
I think there's only one way to partition MySQL, but using different algorithms, the rules allocate data to different chunks.
1,mysql5.1 and above support partition function
when installing the installation, we can check

[Root@blackghost mysql-5.1.50]#/configure--help |grep-a 3 Partition 
 = Partition Support = = 
 Plugin name:
   partition 
 Description:   MySQL Partitioning Support 
 Supports build:  static 
 configurations:  Max, max-no-ndb. 

Check it out, and if you find any of the above, it means that he supports partitioning, which is open by default. If you've already installed MySQL,

Mysql> Show variables like "%part%"; 
+-------------------+-------+ 
| Variable_name   | Value | 
+-------------------+-------+ 
| have_partitioning | YES  | 
+-------------------+-------+ 
1 row in Set (0.00 sec) 

Take a look at the variables, if supported, there will be the above hints.

2,range partition
the table in the range partition is partitioned in one of the following ways, with each partition containing the rows of the values of the partition expression in a given contiguous interval

Create a range partition table

mysql> CREATE TABLE IF not EXISTS ' user ' ( 
 ->  ' id ' int (one) not NULL auto_increment COMMENT ' userid ', 
 ->  ' name ' varchar NOT NULL default ' COMMENT ' name ', 
 ->  ' sex ' int (1) NOT null default ' 0 ' COMMENT ' 0 is male, 1 is female ', 
 ->  PRIMARY KEY (' id ') 
 ->) Engine=myisam DEFAULT Charset=utf8 auto_increment=1 
 ->  PARTITION by RANGE (ID) ( 
 ->   PARTITION p0 values less THAN (3), 
 -> PARTITION   values P1 less (6), 
 ->   PARTITION p2 values less THAN (9),-> PARTITION P3   VALUES less THAN (), 
 ->
   partition P4 VALUES less THAN MAXVALUE 
 ->); 
Query OK, 0 rows affected (0.13 sec) 

Insert some data

mysql> INSERT into ' test '. ' User ' (' name ', ' Sex ') VALUES (' Tank ', ' 0 ') 
 ->, (' Zhang ', 1), (' Ying ', 1), (' Zhang ', 1), (' Map ' , 0), (' Test1 ', 1), (' Tank2 ', 1) 
 ->, (' Tank1 ', 1), (' Test2 ', 1), (' Test3 ', 1), (' Test4 ', 1), (' Test5 ', 1), (' Tank3 ', 1) 
 ->, (' Tank4 ', 1), (' Tank5 ', 1), (' Tank6 ', 1), (' Tank7 ', 1), (' Tank8 ', 1), (' Tank9 ', 1)->, (' Tank10 ', 1), (' " 
 Tank11 ', 1), (' tank12 ', 1), (' Tank13 ', 1), (' Tank21 ', 1), (' tank42 ', 1); 
Query OK, rows affected (0.05 sec) 
records:25 duplicates:0 warnings:0 

To the place where the database table files are located, MY.CNF has a configuration inside, DataDir is behind

[Root@blackghost test]# ls |grep user |xargs du-sh 
4.0K user#p#p0  . MyD 
4.0K  user#p#p0. Myi 
4.0K  user#p#p1. MyD 
4.0K  user#p#p1. Myi 
4.0K  user#p#p2. MyD 
4.0K  user#p#p2. Myi 
4.0K  user#p#p3. MyD 
4.0K  user#p#p3. Myi 
4.0K  user#p#p4. MyD 
4.0K  user#p#p4. Myi 
12K  user.frm 
4.0K  user.par 

Remove data

Mysql> select COUNT (ID) as count from user; 
+-------+ 
| count | 
+-------+ 
|  | 
+-------+ 
1 row in Set (0.00 sec) 

Delete Fourth partition

mysql> ALTER TABLE user drop partition P4; 
Query OK, 0 rows affected (0.11 sec) 
records:0 duplicates:0 warnings:0 

/** stored in the partition of the data is lost, the fourth partition has 14 data, the remaining 3 partitions 
only 11 data, but the statistics of the file size is 4.0K, from here we can see that the partition of the 
smallest block is 4K 
* * 
Mysql> select COUNT (ID) as count from user; 
+-------+ 
| count | 
+-------+ 
|  One | 
+-------+ 
1 row in Set (0.00 sec) 

Block fourth has been deleted

[Root@blackghost test]# ls |grep user |xargs du-sh 
4.0K user#p#p0  . MyD 
4.0K  user#p#p0. Myi 
4.0K  user#p#p1. MyD 
4.0K  user#p#p1. Myi 
4.0K  user#p#p2. MyD 
4.0K  user#p#p2. Myi 
4.0K  user#p#p3. MyD 
4.0K  user#p#p3. Myi 
12K  user.frm 
4.0K  user.par 

/* Can partition the existing table, and will automatically allocate the data in the table to the appropriate partitions 
, so that is better, you can save a lot of things, see the following operation * * 
mysql> ALTER TABLE AA partition by RANGE (ID) 
 -> (PARTITION p1 values less than (1), 
 -> PARTITION p2 VALUES less than (5),-> partitio 
 N P3 VALUES less than MAXVALUE); 
Query OK, rows affected (0.21 sec)  //Partitioning of 15 data 
records:15 duplicates:0 warnings:0 

Total 15 article

Mysql> Select COUNT (*) from AA; 
+----------+ 
| count (*) | 
+----------+ 
|    | 
+----------+ 
1 row in Set (0.00 sec) 

Delete a partition

mysql> ALTER TABLE AA drop partition p2; 
Query OK, 0 rows affected (0.30 sec) 
records:0 duplicates:0 warnings:0 

Only 11, which means that the existing table partitions are successful

Mysql> Select COUNT (*) from AA; 
+----------+ 
| count (*) | 
+----------+ 
|    One | 
+----------+ 
1 row in Set (0.00 sec)  

3,list partition
the definition and selection of each partition in the list partition is based on the value of a column that is subordinate to a value in a list set of values, and a range partition is a collection of contiguous interval values.

"//This way fails mysql> CREATE TABLE IF not EXISTS ' List_part ' (-> ' id ' int (one) not NU LL auto_increment COMMENT ' user ID ',-> ' province_id ' int (2) not NULL DEFAULT 0 COMMENT ' province ',-> ' name ' varchar (50 Not null default ' COMMENT ' name ',-> ' sex ' int (1) NOT null default ' 0 ' COMMENT ' 0 is male, 1 is female ',-> PRIMARY KEY (' I d ')->) engine=innodb DEFAULT Charset=utf8 auto_increment=1-> PARTITION by LIST (province_id) (-> part Ition p0 values in (1,2,3,4,5,6,7,8),-> PARTITION P1 of Values in (9,10,11,12,16,21),-> PARTITION P2 values I 
N (13,14,15,19),-> PARTITION P3 VALUES in (17,18,20,22,23,24)->); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table ' s partitioning function 

This way succeeds 
mysql> CREATE TABLE IF not EXISTS ' List_part ' ( 
 ->  ' id ' int (one) not NULL COMMENT ' user ID ', 
 -& gt;  ' province_id ' int (2) NOT null default 0 COMMENT ' province ', 
 ->  ' name ' varchar ' NOT null default ' COMMENT ' name ', 
   ->  ' sex ' int (1) Not NULL default ' 0 ' COMMENT ' 0 is male, 1 is female ' 
 -> ' engine=innodb default Charset=utf8 
 ; PARTITION by LIST (province_id) ( 
 ->   PARTITION p0 VALUES in (1,2,3,4,5,6,7,8), 
 ->   PARTITION P1 values in (9,10,11,12,16,21), 
 ->   PARTITION p2 values in (13,14,15,19), 
 ->   PARTITION VALUES in (17,18,20,22,23,24) 
 ->); 
Query OK, 0 rows affected (0.33 sec)

This creates the list partition, and if there is a primary 銉, the primary key must be in the partition, or it will be an error. If I do not use the primary key, the partition is created successfully, in general, a table will certainly have a primary key, which is a partition of the limitations of it.
If you are testing your data, refer to the test for the range partition to manipulate

4,hash partition
a hash partition is primarily used to ensure that the data is evenly distributed across a predetermined number of partitions, and all you have to do is specify a column value or expression based on the column value that will be hashed, and specify the number of partitions that the partitioned table will be divided into.

mysql> CREATE TABLE IF not EXISTS ' Hash_part ' ( 
 ->  ' id ' int (one) not NULL auto_increment ' Comment id ', 
 ->  ' comment ' varchar (1000) NOT null default ' comment ' comment ', 
 ->  ' ip ' varchar ' NOT null default ' COM ment ' source IP ', 
 ->  PRIMARY KEY (' id ') 
 ->) engine=innodb DEFAULT Charset=utf8 auto_increment=1 
 - > PARTITION by HASH (ID) 
 -> partitions 3; 
Query OK, 0 rows affected (0.06 sec) 

Test please refer to the Range partition action

5,key partition
partitioning according to key is similar to following a hash partition, except for a user-defined expression used by the hash partition, and the hash function of the key partition is provided by the MySQL server.

mysql> CREATE TABLE IF not EXISTS ' Key_part ' ( 
 ->  ' news_id ' int (one) not NULL COMMENT ' news id ', 
 ->  ' Content ' varchar (1000) NOT null default ' COMMENT ' news content ', 
 -> ' u_id ' varchar ' is not  null default ' COMMENT ' to Source IP ', 
 ->  ' create_time ' DATE not NULL DEFAULT ' 0000-00-00 00:00:00 ' COMMENT ' time ' 
 -> ' Engine=innodb DEFA ULT Charset=utf8 
 -> PARTITION by LINEAR HASH (year (create_time)) 
 -> partitions 3; 
Query OK, 0 rows affected (0.07 sec) 

Test please refer to the Range partition action

6, Sub partition
a child partition is a second division of each partition in a partitioned table, and a child partition can use either a hash or a key partition. This is also known as a composite partition (composite partitioning).
1, if you create a child partition in a partition, the other partitions also have child partitions
2, if you create a partition, the number of sub partitions in each partition must be the same
3, the same partition within the sub-partition, the name is not the same, different partitions within the sub-partition name can be the same (5.1.50 not applicable)

 mysql> CREATE TABLE IF not EXISTS ' Sub_part ' (-> ' news_id ' int (one) not NULL COM  ment ' news id ',-> ' content ' varchar (1000) NOT null default ' COMMENT ' news content ',-> ' u_id ' int (one) NOT NULL default 0s COMMENT ' source ip ',-> ' create_time ' DATE not NULL DEFAULT ' 0000-00-00 00:00:00 ' COMMENT ' time '-> ') Engine=innod 
 B DEFAULT Charset=utf8-> PARTITION by RANGE (create_time)-> subpartition by HASH (To_days (create_time)) ( -> PARTITION p0 values less THAN (1990) (Subpartition s0,subpartition s1,subpartition S2),-> PARTITION p1 values Less THAN (subpartition s3,subpartition s4,subpartition good),-> PARTITION p2 VALUES less THAN (MAXVALUE 
Tition tank0,subpartition tank1,subpartition Tank3)->); Query OK, 0 rows affected (0.07 sec) 

The official web site says that sub partitions within different partitions can have the same name, but mysql5.1.50 does not. Prompts the following error
Error 1517 (HY000): Duplicate partition name S1
The 7,columns partition
columns partition is the type of partition introduced in MySQL 5.5, and the introduction of the columns partition solves the MySQL 5.5 version before the range and list partitions support only integer partitions. This results in the need for additional function calculations to get integers, or the problem of converting to an integer repartition through an additional conversion table. Columns partitions can be subdivided into range columns partitions and list columns partitions, and Range columns partitions and list columns partitions support integers, datetime, and string three data types.
Scenario:
Daily report of commodity sales, annual report and so on
every day a table, table name with year month date each table is divided into 24 partitions, the data per hour is divided into 1 districts.

 CREATE TABLE ' Year_log ' (' id ' int () DEFAULT NULL, ' money ' int (one) unsigned not NUL L, ' date ' datetime default NULL engine=innodb default Charset=utf8 PARTITION by RANGE (date) (PARTITION P20 
 
Modified values less THAN (2008), PARTITION p2008 values less THAN (2009), PARTITION p2009 values less THAN); CREATE TABLE ' Daily_log ' (' id ' int () NOT NULL, ' Sid ' char ' is not NULL, ' sname ' char () DEFAULT null, ' Date ' datet IME not NULL, PRIMARY KEY (' id ', ' Date ')] Engine=innodb DEFAULT Charset=utf8 PARTITION by RANGE COLUMNS (' Date ') (Parti tion P1 values less THAN (' 2000-01-02 '), PARTITION P2 values less THAN (' 2000-01-03 '), PARTITION P3 values less THAN (' 2 000-01-04 '), PARTITION P4 values less THAN (' 2000-01-05 '), PARTITION P5 values less THAN (' 2000-01-06 '), PARTITION P6 V Alues less THAN (' 2000-01-07 '), PARTITION P7 values less THAN (' 2000-01-08 '), PARTITION p367 values less THAN (MAXVALUE) 

); 

Third, zoning management
1, delete the partition

mysql> ALTER TABLE user drop partition P4;  

2, new partition

Range to add a new partition 
mysql> ALTER TABLE user add partition (partition P4 values less than); 
Query OK, 0 rows affected (0.06 sec) 
records:0 duplicates:0 warnings:0 

list Add new partition
Mysql> ALTER TABLE List_part add partition (partition P4 values in (25,26,28));
Query OK, 0 rows affected (0.01 sec)
records:0 duplicates:0 warnings:0

hash re-partition

Mysql> ALTER TABLE Hash_part add partition partitions 4; 
Query OK, 0 rows affected (0.12 sec) 
records:0 duplicates:0 warnings:0 

Key re-partition

Mysql> ALTER TABLE Key_part add partition partitions 4; 
Query OK, 1 row affected (0.06 sec)  //data will also be reassigned 
records:1 duplicates:0 warnings:0 

Child partitions Add new partitions, although I do not specify a child partition, but the system will name the child partition

Mysql> ALTER TABLE Sub1_part ADD partition (partition P3 values less than); 
Query OK, 0 rows affected (0.02 sec) 
records:0 duplicates:0 warnings:0 

Mysql> Show CREATE TABLE sub1_part\g; 1. Row *************************** table:sub1_part Create table:create Table ' Sub1_part ' (' news_id ' int () not NULL COMMENT ' news id ', ' content ' varchar (1000) NOT null default ' COMMENT ' news content ', ' u_id ' varchar ' NOT null default ' COM  ment ' source IP ', ' create_time ' date not NULL default ' 0000-00-00 ' COMMENT ' time ') Engine=innodb DEFAULT Charset=utf8!50100 PARTITION by RANGE (create_time) subpartition by HASH (To_days (Create_time)) (PARTITION p0 VALUES less THAN (1990 ) (subpartition s0 ENGINE = InnoDB, subpartition s1 ENGINE = InnoDB, subpartition s2 ENGINE = InnoDB), PARTITION p 1 VALUES less THAN (subpartition s3 ENGINE = InnoDB, subpartition s4 ENGINE = InnoDB, subpartition good Engin E = InnoDB, PARTITION p2 VALUES less THAN (3000) (subpartition tank0 ENGINE = InnoDB, subpartition tank1 ENGINE = I Nnodb, subpartition tank3 ENGINE = InnoDB), PARTITION P3 VALUES Less THAN MAXVALUE (subpartition p3sp0 ENGINE = InnoDB,//child partition's name is automatically generated subpartition p3sp1 ENGINE = InnoDB, subpartition 
 P3SP2 ENGINE = InnoDB)) 1 row in Set (0.00 sec)

3, repartition

Range Re-partition

mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 into (PARTITION p0 VALUES less THAN); 
Query OK, one rows affected (0.08 sec) 
records:11 duplicates:0 warnings:0 
 

List re-partition

mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 to (PARTITION p0 VALUES in (1,2,3,4,5)); 
Query OK, 0 rows affected (0.28 sec) 
records:0 duplicates:0 warnings:0 

Hash and key partitions can not be used reorganize, the official website says very clearly

mysql> ALTER TABLE key_part REORGANIZE PARTITION coalesce PARTITION 9; 
Error 1064 (42000): You have a error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' PARTITION 9 ' at line 1 

Iv. Advantages of Zoning
1, partitions can be divided into more than one disk, storage a bit larger
2, according to the lookup condition, that is, the condition after the where, find only the appropriate partitions do not need to search all
3, the large data search can be processed in parallel.
4, Spread data query across multiple disks for greater query throughput

The difference and connection of MySQL table and partition

One, what is the MySQL sub-table, partition
What is a table, from the superficial point of view, is to divide a table into n multiple small tables
What is partition, partition is to divide the data of a table into n multiple blocks, these blocks can be on the same disk, also can on different disk, refer to MySQL partition function detailed, and example

Two, MySQL and partition what is the difference?
1, to achieve the way on
a), the MySQL table is a real table, a table divided into many tables, each small table is the end of a table, all correspond to three files, one. MyD data file,. Myi index file,. frm table structure file.

[Root@blackghost test]# ls |grep user  
alluser. MRG  
alluser.frm  
user1. MyD  
user1. Myi  
user1.frm  
user2. MyD  
User2. Myi  
user2.frm  

To explain briefly, the above table is to use the merge storage engine (one of the table), AllUser is the total table, the following two tables, User1,user2. All two of them are separate tables, and when we take the data, we can take it through the total table. Here the total table is not. MyD,. Myi These two files, that is to say, the total table he is not a table, no data, the data are placed in the table. Let's take a look. MRG, what the hell is this thing?

[Root@blackghost test]# cat AllUser. MRG |more  
user1  
user2  
#INSERT_METHOD =last  

From the above we can see that alluser. There are a few mrg relationships inside, and the way in which data is inserted. The total table can be understood as a shell, or a join pool.
b), the partition is different, a large table to partition, he is still a table, will not become two tables, but he kept the data block more.

[root@blackghost test]# ls |grep aa  
aa#p#p1. MyD  
Aa#p#p1. Myi  
aa#p#p3. MyD  
aa#p#p3. Myi  
aa.frm  
aa.par 

From the above we can see that a AA this table, divided into two districts, p1 and P3, was originally three districts, was I deleted a district. We all know a table corresponds to three files. MyD,. Myi,.frm. What about partitions? data files and index files are segmented according to certain rules, and a. Par file is added, and when you open the. par file, you can see that he has recorded the partition information of this table, which is in the root table. MRG a bit like. After the partition, it is still a sheet, not multiple tables.

2, on data processing
a), after the table, the data are stored in the table, the total sheet is only a shell, access to data occur in a single table. Look at the following example:
The select * from AllUser where id= ' 12 ' appears to operate on the table AllUser, not actually. Is the operation of the AllUser inside the table.
b, partitioning, does not exist the concept of the table, the partition is only the file that holds the data into a lot of small pieces, after the partition of the table, or a table. Data processing is still done by yourself.

3, improve performance on
a) After the table, the concurrency of the single table increased, disk I/O performance increased. Why does concurrency improve, because the search time is shortened, if the high concurrency, the total table can be based on different queries, the concurrent pressure into a different small table. How does disk I/O performance get high, originally a very large. The MyD file is now also allocated to individual small tables. MyD.
b) MySQL proposed the concept of zoning, I think I want to break through the disk I/O bottleneck, want to improve the disk read and write ability, to increase MySQL performance. Eg: millions of rows of tables are divided into 10 partitions, each containing 100,000 rows of data, so the time required to query the partition is only one-tenth of the total table scan, a clear contrast. You can also index 100,000 rows of tables at a much faster rate than millions of rows. If you can build these partitions on a different disk, this time I/O read and write speed is "unimaginable" (no word, really too fast, theoretically 100 times times the speed of Ascension Ah, this is how fast response speed ah, so a bit unimaginable)
At this point, zoning and the focus of the table is different, the focus is to access data, how to improve the MySQL concurrency ability, and partitioning, how to break through the disk reading and writing ability, so as to improve the MySQL performance.

4), the Ease of implementation
A There are many ways to divide the table, and it is the simplest way to use the merge to form a table. This way the root partitioning is almost as easy and transparent to the program code. It's more troublesome than zoning if you're using a different way of dividing the table.
B, the partitioning implementation is relatively simple, creating a partitioned table, the root building normal table is no different, and the code side is transparent.

Third, the MySQL table and partition what is the connection?
1, can improve the quality of MySQL, high concurrency in the state has a good surface.
2, the table and division are not contradictory, can cooperate with each other, for those with large access, and table data more than the table, we can take the form of a combination of table and partition (if the merge of this form, can not be combined with the partition, you may use other questionnaires), a small number of visits, but table data a lot of tables, We can take zoning by the way and so on.