One, what is a database partition
Some time ago wrote an article on the MySQL table, the following to say what is a database partition, MySQL as an 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.
Two ways to Partition
1, 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.
2, longitudinal zoning
What is a vertical partition? Is the vertical zoning, for example, in the design of user tables, the beginning of the time did not consider good, and put all the information of the individual into a table, so that the table will have a larger field, such as personal profile, and these profiles, may not have a lot of people to see, so wait until someone to see the time, In the search, the table, you can put such a large segment, separated.
Feel the partition of the database seems to be cut Apple, in the end is cut horizontally, or vertical cut, according to personal preferences, MySQL provides the partition belongs to the first, horizontal zoning, and subdivided into many ways. Here are some examples to illustrate.
Second, the MySQL partition
I think there's only one way to partition MySQL, but using different algorithms, you can assign data to different chunks.
1,mysql5.1 and above support partition function
When installing the installation, we can check
| The code is as follows |
Copy Code |
[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,
| The code is as follows |
Copy Code |
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
| The code is as follows |
Copy Code |
| Create a range partition table mysql> CREATE TABLE IF not EXISTS ' user ' (
-> ' id ' int (one) not NULL auto_increment COMMENT ' user ID ',
-> ' 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 p1 VALUES less THAN (6),
-> PARTITION p2 VALUES less THAN (9),
-> PARTITION P3 VALUES less THAN (12),
-> 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), (' Ying ', 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 |
+-------+
| 25 |
+-------+
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
The data stored in the/** is missing, the fourth partition has 14 data, and the remaining 3 partitions Only 11 data, but the size of the file is 4.0K, from here we can see the partition's
The minimum block is 4K
*/
Mysql> select COUNT (ID) as count from user;
+-------+
| Count |
+-------+
| 11 |
+-------+
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
/* You can partition an existing table and press? Automatically assigns the data in the table to the appropriate partitions
, this 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),
-> PARTITION P3 VALUES less than MAXVALUE);
Query OK, rows affected (0.21 sec)//partition 15 data
Records:15 duplicates:0 warnings:0
Total 15 article
Mysql> Select COUNT (*) from AA;
+----------+
| COUNT (*) |
+----------+
| 15 |
+----------+
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 (*) |
+----------+
| 11 |
+----------+
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.
| The code is as follows |
Copy Code |
| This way fails mysql> CREATE TABLE IF not EXISTS ' List_part ' (
-> ' id ' int (one) not NULL auto_increment COMMENT ' user ID ',
-> ' 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 ',
-> PRIMARY KEY (' id ')
->) Engine=innodb DEFAULT Charset=utf8 auto_increment=1
-> 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 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 success
mysql> CREATE TABLE IF not EXISTS ' List_part ' (
-> ' id ' int (one) not NULL COMMENT ' user ID ',
-> ' 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 P3 VALUES in (17,18,20,22,23,24)
->);
Query OK, 0 rows affected (0.33 sec) |
When the above creates a list partition, if there is a master, 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.
| code is as follows |
copy code |
mysql> CREATE TABLE IF not EXISTS ' Hash_part ' (
-> ' id ' int (one) not NULL auto_increment COMMENT ' Comment ID ',
-> ' comment ' varchar (1000) not NULL DEFAULT ' comment ' comment ',
-> ' IP ' varchar not NULL DEFAULT ' COMMENT ' source IP ',
-> PRIMARY KEY (' id ') &NB Sp
->) 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.
| The code is as follows |
Copy Code |
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 not NULL DEFAULT ' COMMENT ' source IP ',
-> ' create_time ' date not NULL DEFAULT ' 0000-00-00 00:00:00 ' COMMENT ' time '
->) Engine=innodb DEFAULT 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)
| The code is as follows |
Copy Code |
| mysql> CREATE TABLE IF not EXISTS ' Sub_part ' (
-> ' news_id ' int (one) not NULL COMMENT ' 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=innodb DEFAULT Charset=utf8
-> PARTITION by RANGE (year (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 good),
-> PARTITION p2 VALUES less THAN MAXVALUE (subpartition tank0,subpartition tank1,subpartition)
->);
Query OK, 0 rows affected (0.07 sec)
The official website says sub partitions in different partitions can have the same name, but mysql5.1.50 does not prompt the following error
ERROR 1517 (HY000): Duplicate partition name S1 |
Third, zoning management
| The code is as follows |
Copy Code |
| 1, delete the partition
mysql> ALTER TABLE user drop partition P4;
2, new partition Range Add 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 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_PARTG;
1. Row ***************************
Table:sub1_part
Create table:create Table ' Sub1_part ' (
' news_id ' int (one) not NULL COMMENT ' news id ',
' Content ' varchar (1000) not NULL DEFAULT ' COMMENT ' news content ',
' u_id ' varchar not NULL DEFAULT ' COMMENT ' source IP ',
' Create_time ' date not NULL DEFAULT ' 0000-00-00 ' COMMENT ' time '
) Engine=innodb DEFAULT Charset=utf8
!50100 PARTITION by RANGE (year (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 p1 VALUES less THAN (2000)
(subpartition s3 ENGINE = InnoDB,
Subpartition S4 ENGINE = InnoDB,
Subpartition good ENGINE = InnoDB),
PARTITION P2 VALUES less THAN (3000)
(subpartition tank0 ENGINE = InnoDB,
Subpartition Tank1 ENGINE = InnoDB,
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, 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, the storage of a larger point
2, depending on the lookup criteria, that is, the conditions behind the where, find only find the appropriate partitions do not have to find all the
3, for large data search can be parallel processing.
4, spread data queries across multiple disks for greater query throughput
