mysql partition management and maintenance of operation and maintenance tool
The above two blogs briefly introduce what partitions and sub partitions are. This document mainly introduces partition management
If you have a large amount of data, it is recommended not to try the operation * *. It should be because a large number of operations will affect I/O performance and may lead to accidents * *. It is recommended to have professional personnel to operate. You can learn about this article
View the current table partition structure
mysql> show tables; +-----------------+ | Tables_in_test2 | +-----------------+ | t1 | | t11 | | t12 | | t2 | | t3 | | t4 | | t5 | | t6 | +-----------------+ 8 rows in set (0.00 sec) ##You can see the current partition structure mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified
Simply modify the partition
It is recommended not to use it in the production environment. It is best to plan it when creating a table. This should be a DDL statement
##Modify the partition mysql> alter table t1 partition by hash(age) partitions 2; Query OK, 0 rows affected (0.29 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (age) PARTITIONS 2 */ 1 row in set (0.00 sec) ERROR: No query specified
Modify the partition before coming back
mysql> alter table t1 partition by range (age) -> ( -> partition p01 values less than (10), -> partition p02 values less than (20), -> partition p03 values less than (30), -> partition p04 values less than (maxvalue) -> ); mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified
Deletion of partition
Deleting the specified RANGE or LIST partition is very simple, but it should be noted that deleting the partition also deletes all the data in the partition. If you only want to delete the data in the partition, you should use the TRUNCATE statement
##delete a partition mysql> alter table t1 drop partition p04; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified ##Clear the data in the partition mysql> alter table t1 truncate partition p01; Query OK, 0 rows affected (0.01 sec)
Adding partitions
RANGE partition add partition
For a table with a rang e partition, you can only use ADD PARTITION to add a new partition to the high end of the partition list, that is, you can only add to the "high position"
It should be noted that the range partition contains maxvalue. When it is worth it, you can't add partitions. There is no top end. For example, if maxvalue is stored on p04, you can't add partitions
Contains maxvalue, cannot add partition
mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION p04 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified mysql> alter table t1 add partition ( partition p05 values less than (40) ); ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition ###The error reporting content is simple and clear ####The following is the partition added without maxvalue mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB) */ ###At this time, the highest level is 30, so the partition you add can only be more than 30 1 row in set (0.00 sec) ERROR: No query specified mysql> alter table t1 add partition ( partition p04 values less than (40) ); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ ###Partition added successfully 1 row in set (0.00 sec) ERROR: No query specified
LIST partition add partition
When adding partitions, you cannot include any value in the existing partition value list
mysql> show create table t2\G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pos_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (cid) (PARTITION p01 VALUES IN (1,2,3) ENGINE = InnoDB, PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB, PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified ####It can be seen from the above figure that this table is a list partition. When adding partitions, you can't add any values in the original partition. For example, you can't add id values such as 1, 2, 3, 4, 5, 6, 7, 8 and 9 ###Error reporting demonstration mysql> alter table t2 add partition -> ( -> partition p04 values in (9,10,11) -> ); ERROR 1495 (HY000): Multiple definition of same constant in list partitioning #Because the previous partition already contains the value of 9, you cannot have any previous value when adding a partition ####Add partition mysql> alter table t2 add partition ( partition p04 values in (10,11,12) ); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2\G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pos_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (cid) (PARTITION p01 VALUES IN (1,2,3) ENGINE = InnoDB, PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB, PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB, PARTITION p04 VALUES IN (10,11,12) ENGINE = InnoDB) */ 1 row in set (0.01 sec) ERROR: No query specified
Management of HASH or KEY partitions
For HASH partition and KEY partition, you can use COALESC to reduce the number of partitions (the number subtracted from the original basis), and use ALTER... ADD PARTITION to increase the number of partitions (the number of partitions increased from the original basis)
mysql> show create table t3\G; *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pos_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (cid) PARTITIONS 4 */ 1 row in set (0.00 sec) ERROR: No query specified ###Reduce partition mysql> alter table t3 coalesce partition 2; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t3\G; *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pos_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (cid) PARTITIONS 2 */ 1 row in set (0.00 sec) ERROR: No query specified ##Add partition mysql> alter table t3 add partition partitions 4;~ Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t3\G; *************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `id` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pos_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY HASH (cid) PARTITIONS 6 */ 1 row in set (0.00 sec) ERROR: No query specified
Reorganization of partitions
The main purpose is to avoid "hot partitions". After a table has been partitioned, several partitions are found, and the query operation is too frequent. This is hot partitions. Of course, we don't want to, so we need to reorganize these hot partitions
RANGE partition reorganization
REORGANIZE can be used to REORGANIZE existing partitions. In this way, an existing partition can be divided into multiple partitions, or multiple partitions can be merged into one partition
However, it should be noted that the mode of the new partition cannot have any overlapping space (applicable to tables partitioned according to RANFGE) or value set (applicable to tables partitioned according to list), and the original interval must also be overwritten
For a RANGE partitioned table, you can only reorganize adjacent partitions and cannot skip the RANGE partition. For example, you used to partition 10, 20, 30, but you reorganized 5, 25. That is, you skipped less than20. This is not allowed
###Split the original partition mysql> alter table t1 reorganize partition p01 into( -> partition s0 values less than (5), -> partition s1 values less than (10) -> ); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION s0 VALUES LESS THAN (5) ENGINE = InnoDB, PARTITION s1 VALUES LESS THAN (10) ENGINE = InnoDB, #Effect after reorganization PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified ##Combine split partitions mysql> alter table t1 reorganize partition s0,s1 into ( -> partition p01 valuesless than (10) -> ); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G; *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE (age) (PARTITION p01 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p02 VALUES LESS THAN (20) ENGINE = InnoDB, PARTITION p03 VALUES LESS THAN (30) ENGINE = InnoDB, PARTITION p04 VALUES LESS THAN (40) ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified
LIST partition
One problem to pay attention to when reorganizing here is that you can't drop any value when reorganizing. For example, there are 1,2,3 in one of your partitions. You can't reorganize only 1,2, but 3 has no place. This is not allowed
####Partition split mysql> alter table t2 reorganize partition p01 into -> ( -> partition s0 values in (1,2), -> partition s1 values in (3) -> ); Query OK, 0 rows affected (1.68 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2\G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pos_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (cid) (PARTITION s0 VALUES IN (1,2) ENGINE = InnoDB, PARTITION s1 VALUES IN (3) ENGINE = InnoDB, PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB, PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB, PARTITION p04 VALUES IN (10,11,12) ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified ###Partition combination mysql> alter table t2 reorganize partition s0,s1 into -> ( -> partition p01 values in (1,2,3) -> ); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2\G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) DEFAULT NULL, `cid` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, `pos_date` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 /*!50100 PARTITION BY LIST (cid) (PARTITION p01 VALUES IN (1,2,3) ENGINE = InnoDB, PARTITION p02 VALUES IN (4,5,6) ENGINE = InnoDB, PARTITION p03 VALUES IN (7,8,9) ENGINE = InnoDB, PARTITION p04 VALUES IN (10,11,12) ENGINE = InnoDB) */ 1 row in set (0.00 sec) ERROR: No query specified
Rebuild partition
For partitioned tables, mysql does not support the commands check table,optimize table.analyiz table or repair tables. Instead, you can use many extensions of ALTER TABLE to directly perform these operations on one or more partitions
The principle is to save all records in the deleted partition first, and then insert them again. It can also be used to defragment the partition. It is recommended not to use it easily
mysql> alter table t1 rebuild partition p01,p02; Query OK, 0 rows affected (1.65 sec) Records: 0 Duplicates: 0 Warnings: 0
Optimize zoning
If a large number of records are deleted or a large number of operations are performed in the partition, a large number of fragments may be caused. Optimization can recover unused space and defragment the partition data files. If the amount of data is large, it will increase io burden. It is recommended not to try it easily
mysql> alter table t1 optimize partition p01,p02; +----------+----------+----------+---------------------------------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+---------------------------------------------------------------------------------------------+ | test2.t1 | optimize | note | Table does not support optimize on partitions. All partitions will be rebuilt and analyzed. | | test2.t1 | optimize | status | OK | +----------+----------+----------+---------------------------------------------------------------------------------------------+ 2 rows in set (0.05 sec)
Check and repair partitions
###inspect mysql> alter table t1 check partition p01,p02; +----------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+-------+----------+----------+ | test2.t1 | check | status | OK | +----------+-------+----------+----------+ 1 row in set (0.00 sec) ###repair mysql> alter table t1 repair partition p01,p02; +----------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+--------+----------+----------+ | test2.t1 | repair | status | OK | +----------+--------+----------+----------+ 1 row in set (0.00 sec)
Conclusion
Still that sentence, the beauty of tomorrow is created by yourself. Come on, boy