mysql partition function and examples

Keywords: MySQL less P4 Database

First, what is database partitioning

Some time ago I wrote an article about it. mysql sub table In this article, let's talk about what database partition is. Take mysql as an example. The data in mysql database is stored on disk in the form of files. By default, it is placed under / mysql/data (which can be viewed by datadir in my.cnf). A table mainly corresponds to three files. One is frm storage table structure, the other is myd storage table data, and the other is myi storage table index. If the amount of data in a table is too large, then myd and myi will become very large and the search data will become very slow. At this time, we can use the partitioning function of mysql to physically divide the three files corresponding to this table into many small pieces. In this way, when we search for a data, we do not need to look up all the data, as long as we know where the data is. Then look for it in that area. If the table's data is too large, one disk may not fit, at this time, we can allocate the data to different disks.
Two ways of partitioning

1. Horizontal zoning

What is horizontal partition? For example, if there are 100W pieces of data and they are divided into ten parts, the first 10W pieces of data are put in the first partition, the second 10W pieces of data are put in the second partition, and so on. That is to say, divide the table into ten parts, and use merge to divide the table. It's a bit like that. When a data is extracted, the data contains all the fields in the table structure, that is to say, the horizontal partition does not change the structure of the table.

2. Vertical partition

What is vertical partition? For example, when designing user tables, we didn't think well at the beginning, but put all personal information into a table, so that there will be larger fields in the table, such as personal profiles, which may not be seen by many people, so when someone wants to see, they will go to look up and sort the tables. At the same time, you can separate such large fields.

It seems that the partition of database is like cutting apples, whether horizontally or vertically. According to personal preferences, the partition provided by mysql belongs to the first one, horizontally partitioned, and divided into many ways. An example will be given below.

Second, the partition of mysql

I think there's only one way to partition mysql, just using different algorithms and rules to distribute data to different blocks.

1, MySQL 5.1 and above support partitioning

When installing, we can check it out.

  1. [root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition  
  2.  === Partition Support ===  
  3.  Plugin Name:      partition  
  4.  Description:      MySQL Partitioning Support  
  5.  Supports build:   static  
  6.  Configurations:   max, max-no-ndb  
[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. If you find the above item, it means that it supports partitioning, which is open by default. If you have installed mysql

  1. mysql> show variables like "%part%";  
  2. +-------------------+-------+  
  3. | Variable_name     | Value |  
  4. +-------------------+-------+  
  5. | have_partitioning | YES   |  
  6. +-------------------+-------+  
  7. 1 row in set (0.00 sec)  
mysql> show variables like "%part%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

Look at the variables, and if supported, there will be hints above.

2. range partition

The table partitioned according to the RANGE partition is partitioned in the following way, each partition contains rows whose partition expression values are located in a given continuous interval.

  1. //Create range partition table  
  2. mysql> CREATE TABLE IF NOT EXISTS `user` (  
  3.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user ID',  
  4.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name',  
  5.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 For men, 1 for women',  
  6.  ->   PRIMARY KEY (`id`)  
  7.  -> ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
  8.  -> PARTITION BY RANGE (id) (  
  9.  ->     PARTITION p0 VALUES LESS THAN (3),  
  10.  ->     PARTITION p1 VALUES LESS THAN (6),  
  11.  ->     PARTITION p2 VALUES LESS THAN (9),  
  12.  ->     PARTITION p3 VALUES LESS THAN (12),  
  13.  ->     PARTITION p4 VALUES LESS THAN MAXVALUE  
  14.  -> );  
  15. Query OK, 0 rows affected (0.13 sec)  
  16.   
  17. //Insert some data  
  18. mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0')  
  19.  -> ,('zhang',1),('ying',1),('Zhang',1),('Reflect',0),('test1',1),('tank2',1)  
  20.  -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1)  
  21.  -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1)  
  22.  -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1);  
  23. Query OK, 25 rows affected (0.05 sec)  
  24. Records: 25  Duplicates: 0  Warnings: 0  
  25.   
  26. //Look at the place where the database table files are stored. my.cnf has configuration. After datadir  
  27. [root@BlackGhost test]# ls |grep user |xargs du -sh  
  28. 4.0K    user#P#p0.MYD  
  29. 4.0K    user#P#p0.MYI  
  30. 4.0K    user#P#p1.MYD  
  31. 4.0K    user#P#p1.MYI  
  32. 4.0K    user#P#p2.MYD  
  33. 4.0K    user#P#p2.MYI  
  34. 4.0K    user#P#p3.MYD  
  35. 4.0K    user#P#p3.MYI  
  36. 4.0K    user#P#p4.MYD  
  37. 4.0K    user#P#p4.MYI  
  38. 12K    user.frm  
  39. 4.0K    user.par  
  40.   
  41. //Remove data  
  42. mysql> select count(id) as count from user;  
  43. +-------+  
  44. | count |  
  45. +-------+  
  46. |    25 |  
  47. +-------+  
  48. 1 row in set (0.00 sec)  
  49.   
  50. //Delete the fourth partition  
  51. mysql> alter table user drop partition p4;  
  52. Query OK, 0 rows affected (0.11 sec)  
  53. Records: 0  Duplicates: 0  Warnings: 0  
  54.   
  55. /**The data stored in the partition is lost. There are 14 data in the fourth partition and the remaining three partitions. 
  56. There are only 11 pieces of data, but all the files are 4.0K in size. From here we can see the partition. 
  57. The smallest block is 4K 
  58. */  
  59. mysql> select count(id) as count from user;  
  60. +-------+  
  61. | count |  
  62. +-------+  
  63. |    11 |  
  64. +-------+  
  65. 1 row in set (0.00 sec)  
  66.   
  67. //The fourth block has been deleted  
  68. [root@BlackGhost test]# ls |grep user |xargs du -sh  
  69. 4.0K    user#P#p0.MYD  
  70. 4.0K    user#P#p0.MYI  
  71. 4.0K    user#P#p1.MYD  
  72. 4.0K    user#P#p1.MYI  
  73. 4.0K    user#P#p2.MYD  
  74. 4.0K    user#P#p2.MYI  
  75. 4.0K    user#P#p3.MYD  
  76. 4.0K    user#P#p3.MYI  
  77. 12K    user.frm  
  78. 4.0K    user.par  
  79.   
  80. /*Existing tables can be partitioned, and the data in the tables can be automatically partitioned according to rules. 
  81. In this way, we can save a lot of things. Look at the following operations*/  
  82. mysql> alter table aa partition by RANGE(id)  
  83.  -> (PARTITION p1 VALUES less than (1),  
  84.  -> PARTITION p2 VALUES less than (5),  
  85.  -> PARTITION p3 VALUES less than MAXVALUE);  
  86. Query OK, 15 rows affected (0.21 sec)   //Partitioning 15 data  
  87. Records: 15  Duplicates: 0  Warnings: 0  
  88.   
  89. //There are altogether 15 articles.  
  90. mysql> select count(*) from aa;  
  91. +----------+  
  92. | count(*) |  
  93. +----------+  
  94. |       15 |  
  95. +----------+  
  96. 1 row in set (0.00 sec)  
  97.   
  98. //Delete a partition  
  99. mysql> alter table aa drop partition p2;  
  100. Query OK, 0 rows affected (0.30 sec)  
  101. Records: 0  Duplicates: 0  Warnings: 0  
  102.   
  103. //There are only 11, indicating that the existing table partitioning has been successful.  
  104. mysql> select count(*) from aa;  
  105. +----------+  
  106. | count(*) |  
  107. +----------+  
  108. |       11 |  
  109. +----------+  
  110. 1 row in set (0.00 sec)  
//Create range partition table
mysql> CREATE TABLE IF NOT EXISTS `user` (
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user ID',
 ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name',
 ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 For men, 1 for women',
 ->   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),('Reflect',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, 25 rows affected (0.05 sec)
Records: 25  Duplicates: 0  Warnings: 0

//Look at the place where the database table files are stored. my.cnf has configuration. After datadir
[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 the 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 partition is lost. There are 14 data in the fourth partition and the remaining three partitions.
There are only 11 pieces of data, but all the files are 4.0K in size. From here we can see the partition.
The smallest block is 4K
*/
mysql> select count(id) as count from user;
+-------+
| count |
+-------+
|    11 |
+-------+
1 row in set (0.00 sec)

//The fourth block 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

/*Existing tables can be partitioned, and the data in the tables can be automatically partitioned according to rules.
In this way, we can save a lot of things. Look at the following operations*/
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, 15 rows affected (0.21 sec)   //Partitioning 15 data
Records: 15  Duplicates: 0  Warnings: 0

//There are altogether 15 articles.
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

//There are only 11, indicating that the existing table partitioning has been 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 LIST partition is based on that the value of a column belongs to a value list set, while the RANGE partition belongs to a set of continuous interval values.

  1. //Failure in this way  
  2. mysql> CREATE TABLE IF NOT EXISTS `list_part` (  
  3.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'user ID',  
  4.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT 'province',  
  5.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name',  
  6.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 For men, 1 for women',  
  7.  ->   PRIMARY KEY (`id`)  
  8.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
  9.  -> PARTITION BY LIST (province_id) (  
  10.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
  11.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
  12.  ->     PARTITION p2 VALUES IN (13,14,15,19),  
  13.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
  14.  -> );  
  15. ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
  16.  
  17. //This approach succeeds. 
  18. mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
  19.  ->   `id` int(11) NOT NULL  COMMENT 'user ID', 
  20.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT 'province', 
  21.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name', 
  22.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 For men, 1 for women'  
  23.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
  24.  -> PARTITION BY LIST (province_id) (  
  25.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
  26.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
  27.  ->     PARTITION p2 VALUES IN (13,14,15,19),  
  28.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
  29.  -> );  
  30. Query OK, 0 rows affected (0.33 sec)  
//Failure in this way
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
 ->   `id` int(11) NOT NULL 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 For men, 1 for women',
 ->   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 approach succeeds.
mysql> CREATE TABLE IF NOT EXISTS `list_part` (
 ->   `id` int(11) NOT NULL  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 For men, 1 for women'
 -> ) 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 list partition is created above, if there is a primary key in the partition, the primary key must be in it, otherwise an error will be reported. If I don't use the primary key, the partition will be created successfully. In general, a table will have a primary key, which is the limitation of a partition.

If you test data, refer to the range partition test for operation.

4, hash partition

HASH partitions are mainly used to ensure that data is evenly distributed in pre-determined number of partitions. All you have to do is specify a column value or expression based on the column value to be hashed and the number of partitions that the partitioned table will be partitioned into.

  1. mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  
  2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'comment ID',  
  3.  ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT 'comment',  
  4.  ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT 'source IP',  
  5.  ->   PRIMARY KEY (`id`)  
  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
  7.  -> PARTITION BY HASH(id)  
  8.  -> PARTITIONS 3;  
  9. Query OK, 0 rows affected (0.06 sec)  
mysql> CREATE TABLE IF NOT EXISTS `hash_part` (
 ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'comment ID',
 ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT 'comment',
 ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '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)

Refer to the operation of range partition for testing

5, key partition

Partitioning by KEY is similar to partitioning by HASH, except for user-defined expressions used by HASH partitions, and the hash function of KEY partitions is provided by MySQL servers.

  1. mysql> CREATE TABLE IF NOT EXISTS `key_part` (  
  2.  ->   `news_id` int(11) NOT NULL  COMMENT 'Journalism ID',  
  3.  ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',  
  4.  ->   `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'source IP',  
  5.  ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'time'  
  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
  7.  -> PARTITION BY LINEAR HASH(YEAR(create_time))  
  8.  -> PARTITIONS 3;  
  9. Query OK, 0 rows affected (0.07 sec)  
mysql> CREATE TABLE IF NOT EXISTS `key_part` (
 ->   `news_id` int(11) NOT NULL  COMMENT 'Journalism ID',
 ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',
 ->   `u_id` varchar(25) 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)

Refer to the operation of range partition for testing

6. Subpartition

Subpartitions are the re-partitions of each partition in the partition table. Subpartitions can use either HASH or KEY partitions. This is also called composite partitioning.

1. If a subpartition is created in one partition, the other partitions should also have subpartitions

2. If partitions are created, the number of subpartitions in each partition must be the same

3. Subpartitions in the same partition have different names. Subpartition names in different partitions can be the same (5.1.50 does not apply).

  1. mysql> CREATE TABLE IF NOT EXISTS `sub_part` (  
  2.  ->   `news_id` int(11) NOT NULL  COMMENT 'Journalism ID',  
  3.  ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',  
  4.  ->   `u_id`  int(11) NOT NULL DEFAULT 0s COMMENT 'source IP',  
  5.  ->   `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'time'  
  6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
  7.  -> PARTITION BY RANGE(YEAR(create_time))  
  8.  -> SUBPARTITION BY HASH(TO_DAYS(create_time))(  
  9.  -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2),  
  10.  -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),  
  11.  -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)  
  12.  -> );  
  13. Query OK, 0 rows affected (0.07 sec)  
mysql> CREATE TABLE IF NOT EXISTS `sub_part` (
 ->   `news_id` int(11) NOT NULL  COMMENT 'Journalism ID',
 ->   `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',
 ->   `u_id`  int(11) 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 (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good),
 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3)
 -> );
Query OK, 0 rows affected (0.07 sec)

The official website says that sub-partitions within different partitions can have the same name, but MySQL 5.1.50 will not prompt the following error

ERROR 1517 (HY000): Duplicate partition name s1

III. Zoning Management

1. Delete partitions

  1. mysql> alter table user drop partition p4;  
mysql> alter table user drop partition p4;

2. New partitions

  1. //range adds new partitions  
  2. mysql> alter table user add partition(partition p4 values less than MAXVALUE);  
  3. Query OK, 0 rows affected (0.06 sec)  
  4. Records: 0  Duplicates: 0  Warnings: 0  
  5.   
  6. //list adds new partitions  
  7. mysql> alter table list_part add partition(partition p4 values in (25,26,28));  
  8. Query OK, 0 rows affected (0.01 sec)  
  9. Records: 0  Duplicates: 0  Warnings: 0  
  10.   
  11. //hash redistribution  
  12. mysql> alter table hash_part add partition partitions 4;  
  13. Query OK, 0 rows affected (0.12 sec)  
  14. Records: 0  Duplicates: 0  Warnings: 0  
  15.   
  16. //key re-partitioning  
  17. mysql> alter table key_part add partition partitions 4;  
  18. Query OK, 1 row affected (0.06 sec)    //Data will also be redistributed  
  19. Records: 1  Duplicates: 0  Warnings: 0  
  20.   
  21. //A new partition is added to the subpartition. Although I did not specify a subpartition, the system will name the subpartition.  
  22. mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);  
  23. Query OK, 0 rows affected (0.02 sec)  
  24. Records: 0  Duplicates: 0  Warnings: 0  
  25.   
  26. mysql> show create table sub1_part\G;  
  27. *************************** 1. row ***************************  
  28.  Table: sub1_part  
  29. Create Table: CREATE TABLE `sub1_part` (  
  30.  `news_id` int(11) NOT NULL COMMENT 'Journalism ID',  
  31.  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',  
  32.  `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'source IP',  
  33.  `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT 'time'  
  34. ) ENGINE=InnoDB DEFAULT CHARSET=utf8  
  35. !50100 PARTITION BY RANGE (YEAR(create_time))  
  36. SUBPARTITION BY HASH (TO_DAYS(create_time))  
  37. (PARTITION p0 VALUES LESS THAN (1990)  
  38.  (SUBPARTITION s0 ENGINE = InnoDB,  
  39.  SUBPARTITION s1 ENGINE = InnoDB,  
  40.  SUBPARTITION s2 ENGINE = InnoDB),  
  41.  PARTITION p1 VALUES LESS THAN (2000)  
  42.  (SUBPARTITION s3 ENGINE = InnoDB,  
  43.  SUBPARTITION s4 ENGINE = InnoDB,  
  44.  SUBPARTITION good ENGINE = InnoDB),  
  45.  PARTITION p2 VALUES LESS THAN (3000)  
  46.  (SUBPARTITION tank0 ENGINE = InnoDB,  
  47.  SUBPARTITION tank1 ENGINE = InnoDB,  
  48.  SUBPARTITION tank3 ENGINE = InnoDB),  
  49.  PARTITION p3 VALUES LESS THAN MAXVALUE  
  50.  (SUBPARTITION p3sp0 ENGINE = InnoDB,    //Names of subpartitions are automatically generated  
  51.  SUBPARTITION p3sp1 ENGINE = InnoDB,  
  52.  SUBPARTITION p3sp2 ENGINE = InnoDB))  
  53. 1 row in set (0.00 sec)  
//range adds new partitions
mysql> alter table user add partition(partition p4 values less than MAXVALUE);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

//list adds new partitions
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 redistribution
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-partitioning
mysql> alter table key_part add partition partitions 4;
Query OK, 1 row affected (0.06 sec)    //Data will also be redistributed
Records: 1  Duplicates: 0  Warnings: 0

//A new partition is added to the subpartition. Although I did not specify a subpartition, the system will name the subpartition.
mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE);
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(11) NOT NULL COMMENT 'Journalism ID',
 `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content',
 `u_id` varchar(25) 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,    //Names of subpartitions are automatically generated
 SUBPARTITION p3sp1 ENGINE = InnoDB,
 SUBPARTITION p3sp2 ENGINE = InnoDB))
1 row in set (0.00 sec)

3. Rezoning

  1. //range Repartitioning  
  2. mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);  
  3. Query OK, 11 rows affected (0.08 sec)  
  4. Records: 11  Duplicates: 0  Warnings: 0  
  5.   
  6. //list re-partitioning  
  7. mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));  
  8. Query OK, 0 rows affected (0.28 sec)  
  9. Records: 0  Duplicates: 0  Warnings: 0  
  10.   
  11. //REORGANIZE is not available for hash and key partitions. The official website makes it clear.  
  12. mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;  
  13. ERROR 1064 (42000): You have an 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  
//range Repartitioning
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);
Query OK, 11 rows affected (0.08 sec)
Records: 11  Duplicates: 0  Warnings: 0

//list re-partitioning
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5));
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

//REORGANIZE is not available for hash and key partitions. The official website makes it clear.
mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9;
ERROR 1064 (42000): You have an 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

Fourth, the advantages of zoning

1. Partitions can be divided into multiple disks and stored a little more.

2. According to the search condition, that is, the condition behind where, the search only finds the corresponding partition and does not need to find all the partitions.

3. Parallel processing can be performed in large data search.

4. Decentralize data queries across multiple disks to achieve greater query throughput

80


Reprinted please indicate
Author: Sea Eagle
Address: http://blog.51yip.com/mysql/1013.html

Posted by sidney on Fri, 29 Mar 2019 01:15:28 -0700

Hot Keywords

©2024 Programmer Group