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

Tags: Operation & Maintenance Database MySQL

Posted on Tue, 16 Nov 2021 03:54:09 -0500 by saint4

Programmer Help

mysql partition management and maintenance of operation and maintenance tool