Learn the principles and common operations of MySQL partition tables through examples

1. Meaning of partition table

A partition table definition allocates multiple portions of a single table across the file system according to rules that can be set to any size. In fact, different parts of the table are stored as separate tables in different locations. The rule selected by the user to achieve data segmentation is called the partition function, which in MySQL can be a modulus, or a simple match on a continuous numeric range or list of numeric values, or an internal HASH function, or a linear HASH function.

The difference between table sharding and partitioning is that logically there is only one table in a partition, while table sharding decomposes a table into multiple tables.

2. Advantages of partition table

1) Partition tables are easier to maintain. For data that has lost its meaning of being saved, it is usually easy to delete the data by deleting the partitions related to the data. Conversely, in some cases, the process of adding new data can be conveniently implemented by adding a new partition specifically for that new data.

2) Some queries can be greatly optimized, mainly by using the fact that the data that satisfies a given WHERE statement can be stored only in one or more partitions, so that there is no need to search other remaining partitions when searching. Because partitions can be modified after a partitioned table is created, you can reorganize your data to improve the efficiency of commonly used queries if you did not do so when you first configured the partitioning scheme.

3) Optimize queries. For example, SUM() and COUNT() can be processed in parallel on multiple partitions, and the final result is simply the sum of the results of all partitions.

4) Get greater query throughput by distributing data queries across multiple disks.

3. Partition table restrictions

1) A table can have a maximum of 1024 partitions;

2) In MySQL 5.1, the partitioning expression must be an integer or an expression that returns an integer. MySQL 5.5 provides support for non-integer expression partitioning;

3) If there are primary key or unique index columns in the partition field, all primary key columns and unique index columns must be included. That is, the partition field either does not contain the primary key or index column, or contains all the primary key and index columns;

4) Foreign key constraints cannot be used in partition tables;

5) MySQL partitioning applies to all data and indexes of a table. You cannot partition only table data without partitioning indexes, partition only indexes without partitioning tables, or partition only part of the table data.

6) The partition key must be of INT type, or an expression that returns INT type, and can be NULL. The only exception is that when the partition type is KEY partition, you can use other types of columns as partition keys (except BLOB or TEXT columns)

7) If the table has a primary key and a unique index, when partitioning by the primary key field, the unique index column should contain the partition key.

8) Currently MySQL does not support partitioning of spatial types and temporary table types. Full-text indexing is not supported.

9) Object restrictions (partition expressions cannot contain stored functions, stored procedures, UDFs, or plugins, declared variables or user variables.)

10) Operation restrictions (addition, subtraction, multiplication and other operations are supported in partition expressions, but the result of the operation must be an INT or NULL. DIV is supported, but /, |, &, ^, <<, >>, and ~ are not supported and are not allowed to appear in partition expressions)

11) sql_mode restrictions (We strongly recommend that you never change mysql's sql_mode after creating a partitioned table. Because in different modes, the results returned by some functions or operations may be different)

12) query_cache and INSERT DELAYED are not supported

13) The partition key cannot be a subquery (even if the subquery returns an int value or null.)

14) Subpartition restrictions (only RANG and LIST partitions can be subpartitioned. HASH and KEY partitions cannot be subpartitioned and the subpartitions must be of HASH or KEY type)

4. Partition Type

1) Horizontal partitioning (by row based on column attributes)

For example, a table containing ten years of invoice records can be partitioned into ten different partitions, each containing records for one year.

Several modes of horizontal partitioning:

* Range: This mode allows the DBA to divide data into different ranges.

For example, a table can be divided into three partitions by year: data from the 1980s, data from the 1990s, and any data after 2000 (including 2000).

* Hash: This mode allows the DBA to calculate the Hash Key of one or more columns of the table, and finally partition the data area according to the different values of the Hash code.

For example, you can create a table that partitions the table's primary key.

* Key: An extension of the above Hash mode. The Hash Key here is generated by the MySQL system.

* List (predefined list): This mode allows the system to split the row data corresponding to the values of the DBA-defined list. For example: The DBA creates a table across three partitions, based on data corresponding to the values of 2004, 2005, and 2006.

* Columns partitioning is a supplement to range and list partitioning. It makes up for the fact that the latter two only support integer partitioning (or by converting to integers), so that the supported data types are greatly increased (all integer types, date and time types, character types), and multi-column partitioning is also supported.

Note: When inserting data into a multi-column partition table, tuple comparison is used, that is, multi-column sorting, first sorting according to field1, then sorting according to field2, and partitioning and storing data according to the sorting results.

* Composite: A combination of the above modes.

For example, on a table that has been initially range partitioned, you can perform hash partitioning on one of the partitions.

Vertical partitioning (by columns):

For example, if a table contains large text and BLOB columns that are not frequently accessed, you can partition these infrequently used text and BLOB columns into another partition, which can improve access speed while ensuring their data relevance.

Note: Subpartition (keyword subpartition): RANGE or LIST partitioning can be further split to form subpartitions, which can be HASH partitions or KEY partitions. Recommended for use on multiple disks.

Check whether there is support for Partition table

mysql> SHOW PLUGINS ;
 +----------------------------+----------+--------------------+---------+---------+
 | Name | Status | Type | Library | License |
 +----------------------------+----------+--------------------+---------+---------+
 | partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
 +----------------------------+----------+--------------------+---------+---------+
 Or use mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status
 -> FROM INFORMATION_SCHEMA.PLUGINS
 -> WHERE PLUGIN_TYPE='STORAGE ENGINE';

Note: For versions prior to MySQL 5.6.1, you can use the following command to view the have_partitioning parameter. This parameter has been removed in newer versions.

 mysql> SHOW VARIABLES LIKE '%partition%';

5. Several common partition table modes in actual combat

1) Use RANGE partition mode

####Create a test table t1 and insert nearly 4 million rows of data. Without partitions, it takes a long time to query a certain condition

mysql> CREATE TABLE `t1` ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Table primary key', 
 `pid` int(10) unsigned NOT NULL COMMENT 'Product ID', 
 `price` decimal(15,2) NOT NULL COMMENT 'Unit price', 
 `num` int(11) NOT NULL COMMENT 'Purchase quantity', 
 `uid` int(10) unsigned NOT NULL COMMENT 'Customer ID', 
 `atime` datetime NOT NULL COMMENT 'Order time', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Modification time', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag', 
 PRIMARY KEY (`id`,`atime`) 
 ) 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00'); 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00'); 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00'); 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00'); 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00'); 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00'); 
 INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00'); 
 /**********************************Master-slave replication of large amounts of data******************************/ 
 mysql> INSERT INTO `t1`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`; 
 mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); 
 1048576 rows in set (5.62 sec) #5.62s for no partition table

If you want to partition an existing table, you can use ALTER TABLE to change the table to a partitioned table. This operation will create a partitioned table, automatically copy the data, and then delete the original table.

Note: This will consume a lot of server resources (it will take more than 1 minute to process more than 4 million pieces of data)

mysql> ALTER TABLE t1 PARTITION BY RANGE (YEAR(atime)) 
 -> ( 
 -> PARTITION p0 VALUES LESS THAN (2016), 
 -> PARTITION p1 VALUES LESS THAN (2017),
 -> PARTITION p2 VALUES LESS THAN (2018), 
 -> PARTITION p3 VALUES LESS THAN MAXVALUE );
 Query OK, 4194304 rows affected (1 min 8.32 sec)
 mysql> EXPLAIN PARTITIONS SELECT * FROM `t1`; #View partition status+----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+------+
 | 1 | SIMPLE | t1 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4180974 | 100.00 | NULL |
 +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+------+
 1 row in set, 2 warnings (0.00 sec)

Use the same query as above to test the results

mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP();
 1048576 rows in set (4.46 sec) #Compared to the above query without partition execution time, it is nearly 1 second less
 mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); #View the partitions used by the query +----+-------------+-------+------------+------+---------------+------+---------+---------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
 | 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 3135804 | 3.33 | Using where |
 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
 1 row in set, 2 warnings (0.00 sec)

At the same time, please note that after the table is partitioned, the storage files of the table in the data folder where MySQL stores the data are also split into multiple

 -rw-r----- 1 mysql mysql 8.7K February 14 14:49 t1.frm
 -rw-r----- 1 mysql mysql 36M February 14 14:50 t1#P#p0.ibd
 -rw-r----- 1 mysql mysql 64M February 14 14:50 t1#P#p1.ibd
 -rw-r----- 1 mysql mysql 92M February 14 14:50 t1#P#p2.ibd
 -rw-r----- 1 mysql mysql 64M February 14 14:50 t1#P#p3.ibd

In actual production environments, another approach is mostly used: create a new partition table that is the same as the original table, then export the data from the original table, then import it into the new table, and finally create a normal index.

mysql> CREATE TABLE `t2` ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Table primary key', 
 `pid` int(10) unsigned NOT NULL COMMENT 'Product ID', 
 `price` decimal(15,2) NOT NULL COMMENT 'Unit price', 
 `num` int(11) NOT NULL COMMENT 'Purchase quantity', 
 `uid` int(10) unsigned NOT NULL COMMENT 'Customer ID', 
 `atime` datetime NOT NULL COMMENT 'Order time', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Modification time', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag', 
 PRIMARY KEY (`id`,`atime`) 
 ) 
 
 PARTITION BY RANGE COLUMNS(atime) ( 
 PARTITION p0 VALUES LESS THAN ('2016-01-01'),
 PARTITION p1 VALUES LESS THAN ('2016-02-01'),
 PARTITION p2 VALUES LESS THAN ('2016-03-01'),
 PARTITION p3 VALUES LESS THAN ('2016-04-01'),
 PARTITION p4 VALUES LESS THAN ('2016-05-01'),
 PARTITION p5 VALUES LESS THAN ('2016-06-01'),
 PARTITION p6 VALUES LESS THAN ('2016-07-01'),
 PARTITION p7 VALUES LESS THAN ('2016-08-01'),
 PARTITION p8 VALUES LESS THAN ('2016-09-01'),
 PARTITION p9 VALUES LESS THAN ('2016-10-01'),
 PARTITION p10 VALUES LESS THAN ('2016-11-01'),
 PARTITION p11 VALUES LESS THAN ('2016-12-01'),
 PARTITION p12 VALUES LESS THAN ('2017-01-01'),
 PARTITION p13 VALUES LESS THAN ('2017-02-01'),
 PARTITION p14 VALUES LESS THAN ('2017-03-01'),
 PARTITION p15 VALUES LESS THAN ('2017-04-01'),
 PARTITION p16 VALUES LESS THAN ('2017-05-01'),
 PARTITION p17 VALUES LESS THAN ('2017-06-01'),
 PARTITION p18 VALUES LESS THAN ('2017-07-01'),
 PARTITION p19 VALUES LESS THAN ('2017-08-01'),
 PARTITION p20 VALUES LESS THAN ('2017-09-01'),
 PARTITION p21 VALUES LESS THAN ('2017-10-01'),
 PARTITION p22 VALUES LESS THAN ('2017-11-01'),
 PARTITION p23 VALUES LESS THAN ('2017-12-01'),
 PARTITION p24 VALUES LESS THAN ('2018-01-01'),
 PARTITION p25 VALUES LESS THAN ('2018-02-01'),
 PARTITION p26 VALUES LESS THAN ('2018-03-01'),
 PARTITION p27 VALUES LESS THAN ('2018-04-01'),
 PARTITION p28 VALUES LESS THAN ('2018-05-01'),
 PARTITION p29 VALUES LESS THAN ('2018-06-01'),
 PARTITION p30 VALUES LESS THAN ('2018-07-01'),
 PARTITION p31 VALUES LESS THAN ('2018-08-01'),
 PARTITION p32 VALUES LESS THAN ('2018-09-01'),
 PARTITION p33 VALUES LESS THAN ('2018-10-01'),
 PARTITION p34 VALUES LESS THAN ('2018-11-01'),
 PARTITION p35 VALUES LESS THAN ('2018-12-01'),
 PARTITION p36 VALUES LESS THAN MAXVALUE
 );

Note: The primary key of the table is only id, and the partition field is atime. Here the primary key should be changed to id, stsdate joint primary key. The partition table requires the partition field to be the primary key or part of the primary key!!!

mysql> EXPLAIN PARTITIONS SELECT * FROM `t2`\G;
 *************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t2
 partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36
 type: ALL
 possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1
 filtered: 100.00
 Extra: NULL
 1 row in set, 2 warnings (0.00 sec)
 ***********************************************Insert data*****************************************************
 INSERT INTO `t2`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`;
 Query OK, 4194304 rows affected (1 min 18.54 sec)
 Records: 4194304 Duplicates: 0 Warnings: 0

Or export the data and then import it, and then add the index

mysqldump -u dbname -p --no-create-info dbname t2 > t2.sq

Modify the table name, import data, test it and delete the original table.

2) Use LIST partition mode (if the original table has a strong primary key, when creating a new table, the original primary key and the partition field are created as a joint primary key)

mysql> CREATE TABLE `tb01` ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Table primary key', 
 `pid` int(10) unsigned NOT NULL COMMENT 'Product ID', 
 `price` decimal(15,2) NOT NULL COMMENT 'Unit price', 
 `num` int(11) NOT NULL COMMENT 'Purchase quantity', 
 `uid` int(10) unsigned NOT NULL COMMENT 'Customer ID', 
 `atime` datetime NOT NULL COMMENT 'Order time', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Modification time', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag', 
 PRIMARY KEY (`id`,`num`) 
 ); 
 *****************************Insert test data**********************************************************
 INSERT INTO `tb01`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`;
 Query OK, 3145728 rows affected (46.26 sec)
 Records: 3145728 Duplicates: 0 Warnings: 0
 mysql> ALTER TABLE tb01 PARTITION BY LIST(num) 
 ( 
 PARTITION pl01 VALUES IN (1,3), 
 PARTITION pl02 VALUES IN (2,4), 
 PARTITION pl03 VALUES IN (5,7), 
 PARTITION pl04 VALUES IN (6,8), 
 PARTITION pl05 VALUES IN (9,10) 
 );
 Query OK, 3145728 rows affected (48.86 sec)
 Records: 3145728 Duplicates: 0 Warnings: 0
The following files are generated in the mysql data file -rw-r----- 1 mysql mysql 8.7K February 15 11:35 tb01.frm
 -rw-r----- 1 mysql mysql 56M February 15 11:36 tb01#P#pl01.ibd
 -rw-r----- 1 mysql mysql 32M February 15 11:36 tb01#P#pl02.ibd
 -rw-r----- 1 mysql mysql 36M February 15 11:36 tb01#P#pl03.ibd
 -rw-r----- 1 mysql mysql 36M February 15 11:36 tb01#P#pl04.ibd
 -rw-r----- 1 mysql mysql 52M February 15 11:36 tb01#P#pl05.ibd

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb01`;
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+------+
| 1 | SIMPLE | tb01 | pl01,pl02,pl03,pl04,pl05 | ALL | NULL | NULL | NULL | NULL | 3136392 | 100.00 | NULL |
+----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+------+
1 row in set, 2 warnings (0.00 sec)

3) COLUMNS partition

Create a multi-column partition table tb02, where neither column is a joint primary key

mysql> CREATE TABLE tb02(
 -> a int not null,
 -> b int not null
 -> )
 -> PARTITION BY RANGE COLUMNS(a,b)(
 -> partition p0 values less than(0,10),
 -> partition p1 values less than(10,20),
 -> partition p2 values less than(10,30),
 -> partition p3 values less than (maxvalue, maxvalue)
 -> );
 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb02`; #View +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+-------------+------+---------------+------+---------+------+------+------+------+
 | 1 | SIMPLE | tb02 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
 +----+-------------+-------+-------------+------+---------------+------+---------+------+------+------+------+
 1 row in set, 2 warnings (0.00 sec)
 mysql> insert into tb02 values (11,13); #Manually insert test data Query OK, 1 row affected (0.01 sec)
 mysql> select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.partitions where table_schema=schema() and table_name='tb02'; 
 +----------------+----------------------+------------+
 | PARTITION_NAME | PARTITION_EXPRESSION | TABLE_ROWS |
 +----------------+----------------------+------------+
 | p0 | `a`,`b` | 0 |
 | p1 | `a`,`b` | 0 |
 | p2 | `a`,`b` | 0 |
 | p3 | `a`,`b` | 1 |
 +----------------+----------------------+------------+
 4 rows in set (0.03 sec)

4) Hase partition

HASH is mainly used to distribute data as evenly as possible among a set number of partitions. When performing hash partitioning, MySQL will perform a hash function on the partition key to determine which partition the data should be placed in. HASH partitioning is divided into regular HASH partitioning and linear HASH partitioning. The former uses the modulo algorithm, and the latter uses the linear power of 2 operation rule.

CREATE TABLE `tb03` ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Table primary key', 
 `pid` int(10) unsigned NOT NULL COMMENT 'Product ID', 
 `price` decimal(15,2) NOT NULL COMMENT 'Unit price', 
 `num` int(11) NOT NULL COMMENT 'Purchase quantity', 
 `uid` int(10) unsigned NOT NULL COMMENT 'Customer ID', 
 `atime` datetime NOT NULL COMMENT 'Order time', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Modification time', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag', 
 PRIMARY KEY (`id`)
 ) 
 PARTITION BY HASH(id) partitions 4;
 Insert 2 rows of data:
 INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); 
 INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
 mysql> explain partitions select * from tb03 where id=1;
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | 1 | SIMPLE | tb03 | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 1 row in set, 2 warnings (0.00 sec)
 mysql> explain partitions select * from tb03 where id=2;
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | 1 | SIMPLE | tb03 | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 1 row in set, 2 warnings (0.00 sec)

Note: Although HASH partitioning distributes data evenly on each partition as much as possible, thus improving query efficiency, it increases the cost of partition management. For example, if there were 5 partitions before and one more partition is to be added, the algorithm changes from mod(expr,5) to (expr,6), and most of the data in the original 5 partitions need to be recalculated and repartitioned. Although using linear HASH partitioning will reduce the cost of partition management, the data is not as evenly distributed as regular HASH.

5) KEY partition

KEY partitioning is similar to HASH partitioning, but you cannot use custom expressions. However, it supports many types of partition keys, except for text types such as Text and Blob.

CREATE TABLE `tb04` ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Table primary key', 
 `pid` int(10) unsigned NOT NULL COMMENT 'Product ID', 
 `price` decimal(15,2) NOT NULL COMMENT 'Unit price', 
 `num` int(11) NOT NULL COMMENT 'Purchase quantity', 
 `uid` int(10) unsigned NOT NULL COMMENT 'Customer ID', 
 `atime` datetime NOT NULL COMMENT 'Order time', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Modification time', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag', 
 PRIMARY KEY (`id`)
 ) 
 PARTITION BY KEY(id) partitions 4;
 Insert 2 rows of data:
 INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); 
 INSERT INTO tb04(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP());
 #Use the execution task to check the partitions where the records fall.mysql> explain partitions select * from tb04 where id=1;
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | 1 | SIMPLE | tb04 | p0 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 1 row in set, 2 warnings (0.00 sec)
 mysql> explain partitions select * from tb04 where id=2;
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 | 1 | SIMPLE | tb04 | p3 | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
 +----+-------------+-------+------------+-------+---------------+--------+---------+-------+------+------+------+------+
 1 row in set, 2 warnings (0.00 sec)

6) Partition table management

It is recommended not to modify partitions in a production environment. Alter will read the data in the old table and store it in the newly defined table. The process IO will be very large and the entire table will be locked.

*1* Delete partition: Example: tb01 table above

--The p05 partition query data is not deleted, mainly to verify whether the partition data is deleted when it is deleted

mysql> select count(1) from tb01 where num=10;
 +----------+
 | count(1) |
 +----------+
 |524288|
 +----------+
 1 row in set (0.37 sec)
 mysql> alter table tb01 drop partition pl05; #Delete the pl05 partition, for example, to delete multiple partitions at one time, alter table tb01 drop partition pl04,pl05;
 Query OK, 0 rows affected (0.06 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 mysql> select count(1) from tb01 where num=10; #The result data will also be deleted, please operate with caution+----------+ 
 | count(1) |
 +----------+
 | 0 |
 +----------+
 1 row in set (0.01 sec）

Note: Deleting a partition will delete data, so proceed with caution; you cannot delete hash or key partitions.

*2*Add partition

Note: The value of the new partition cannot contain any value in the value list of any existing partition, otherwise an error will be reported; the newly added partition will reorganize the data and the original data will not be lost. After the MAXVALUE value is set, partitions cannot be added directly. For example, the t1 table above is used as an example.

mysql> ALTER TABLE t1 ADD PARTITION (PARTITION P4 VALUES LESS THAN (2018) ) ; 
 ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
 Example: Add the pl05 partition deleted from tb01mysql> ALTER TABLE tb01 ADD PARTITION(PARTITION pl05 VALUES IN (9,10));
 Query OK, 0 rows affected (0.05 sec)
 Records: 0 Duplicates: 0 Warnings: 0

*3* Decomposition of partitions

Note: The Reorganize partition keyword can modify some or all partitions of a table without losing data. The overall range of the partitions should be consistent before and after the decomposition.

Example:

mysql> create table tb05
 -> (dep int,
 -> birthdate date,
 -> salary int
 -> )
 -> partition by range(salary)
 -> (
 -> partition p1 values less than (1000),
 -> partition p2 values less than (2000),
 -> partition p3 values less than maxvalue
 -> );
 Query OK, 0 rows affected (0.08 sec)
 ****Insert a test datamysql> insert tb05 values(1,'2016-03-06',80);
 Query OK, 1 row affected (0.01 sec)
 mysql>alter table tb05 reorganize partition p1 into(
 partition p01 values less than (100),
 partition p02 values less than (1000)
 ); ----No data will be lostmysql> explain partitions select * from tb05 where salary=80; #Check that it has fallen on the new partition p01+----+-------------+-------+------------+---------------+------+---------+------+----------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | 1 | SIMPLE | tb05 | p01 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
 1 row in set, 2 warnings (0.00 sec)

*4*Merge partitions

Note: Merge 2 partitions into one.

Example: Merge p01 and p02 in the above tb05 table into p1

mysql> alter table tb05 reorganize partition p01,p02 into(partition p1 values less than (1000)); --No data loss Query OK, 0 rows affected (0.05 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 mysql> explain partitions select * from tb05 where salary=80;
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
 | 1 | SIMPLE | tb05 | p1 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
 1 row in set, 2 warnings (0.00 sec)

*5* Redefine the hash partition table:

When redefining RANGE and LIST partitions, you can only redefine adjacent partitions and cannot skip partitions. The redefined partition range must be consistent with the original partition range, and the partition type cannot be changed.

Example:

mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
 +----+-------------+-------+-------------+------+---------------+------+---------+------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+-------------+------+---------------+------+---------+------+------+------+------+
 | 1 | SIMPLE | tb03 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
 +----+-------------+-------+-------------+------+---------------+------+---------+------+------+------+------+
 1 row in set, 2 warnings (0.00 sec)
 mysql> Alter table tb03 partition by hash(id)partitions 8; #No data loss Query OK, 4 rows affected (0.13 sec)
 Records: 4 Duplicates: 0 Warnings: 0
 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
 +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+------+------+
 | 1 | SIMPLE | tb03 | p0,p1,p2,p3,p4,p5,p6,p7 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
 +----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+------+------+
 1 row in set, 2 warnings (0.02 sec)

*6* Delete all partitions of the table:

Example: Delete all partitions of table tb03

mysql> Alter table tb03 remove partitioning; #No data loss Query OK, 4 rows affected (0.07 sec)
 Records: 4 Duplicates: 0 Warnings: 0
 mysql> EXPLAIN PARTITIONS SELECT * FROM `tb03`;
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
 | 1 | SIMPLE | tb03 | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
 +----+-------------+-------+------------+------+---------------+------+---------+------+------+------+------+
 1 row in set, 2 warnings (0.00 sec)

*7* Defragment the partition

Note: If you delete a large number of rows from a partition or make many changes to a row with variable length (that is, a column with VARCHAR, BLOB, or TEXT type), you can use ALTER TABLE ... OPTIMIZE PARTITION to reclaim unused space and defragment the partition data file.

 ALTER TABLE tb03 optimize partition p1,p2;

*8* Analysis partition:

Read and save the key distribution of a partition.

 mysql> ALTER TABLE tb04 CHECK partition p1,p2;
 +--------------+-------+----------+----------+
 | Table | Op | Msg_type | Msg_text |
 +--------------+-------+----------+----------+
 | testsms.tb04 | check | status | OK |
 +--------------+-------+----------+----------+
 1 row in set (0.01 sec)

*9* Check partitions:

You can check partitions in much the same way as you would use CHECK TABLE for a nonpartitioned table. This command can tell tb04 whether the data or indexes in partitions p1 and p2 of the table have been damaged. If this happens, use "ALTER TABLE ... REPAIR PARTITION" to repair the partition.

mysql> ALTER TABLE tb04 CHECK partition p1,p2;
 +--------------+-------+----------+----------+
 | Table | Op | Msg_type | Msg_text |
 +--------------+-------+----------+----------+
 | testsms.tb04 | check | status | OK |
 +--------------+-------+----------+----------+
 1 row in set (0.01 sec)

6. Simple application in actual production

Scenario: Previously, there was a large data table SmsSend (example table, about 28 million rows) without partitions. The statistical process was very time-consuming. Consider using year partitions and backing up the historical database to transfer the data from 2014 to the new backup table smssendbak. If online redefinition is time-consuming, exchange processing can be used!

1) View the current SmsSend table

mysql> SHOW CREATE TABLE SmsSend; #View creation information, no partitioning | SmsSend | CREATE TABLE `SmsSend` (
 `Guid` char(36) NOT NULL COMMENT 'Unique identifier',
 `SID` varbinary(85) DEFAULT NULL COMMENT 'Seller unique number',
 `Mobile` longtext NOT NULL COMMENT 'Receive mobile phone number (separated by ",")',
 `SmsContent` varchar(500) NOT NULL COMMENT 'SMS content',
 `SmsCount` int(11) NOT NULL DEFAULT '1' COMMENT 'Number of messages',
 `Status` int(11) NOT NULL COMMENT 'Current status (0, waiting to send; 1, sent successfully; -1, sent failed)',
 `SendChanelKeyName` varchar(20) DEFAULT NULL COMMENT 'Send channel ID',
 `SendTime` datetime NOT NULL COMMENT 'Send success time',
 `SendType` int(11) NOT NULL DEFAULT '1' COMMENT 'SMS sending type (1, single send; 2, group send)',
 `ReceiveTime` datetime DEFAULT NULL COMMENT 'The time when the reply report was received',
 `Priority` int(11) NOT NULL DEFAULT '0' COMMENT 'Priority',
 `UserAccount` varchar(50) DEFAULT NULL COMMENT 'Operator',
 `ChainStoreGuid` char(36) DEFAULT NULL COMMENT 'Operation store unique identifier',
 `RelationKey` longtext COMMENT 'Reply report association identifier',
 `Meno` text COMMENT 'Notes',
 `IsFree` bit(1) NOT NULL DEFAULT b'0' COMMENT 'Is it free?'
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
 mysql> SELECT COUNT(*) FROM SmsSend; #row records+----------+
 | COUNT(*) |
 +----------+
 |28259803|
 +----------+
 1 row in set (1 min 52.60 sec)
 #It can be seen that online partitioning is slow and consumes performance under large data tablesmysql> ALTER TABLE SmsSend PARTITION BY RANGE (YEAR(SendTime)) 
 -> (
 -> PARTITION py01 VALUES LESS THAN (2015),
 -> PARTITION py02 VALUES LESS THAN (2016),
 -> PARTITION py03 VALUES LESS THAN (2017) ); 
 Query OK, 28259803 rows affected (20 min 36.05 sec)
 Records: 28259803 Duplicates: 0 Warnings: 0
 #View the number of partition recordsmysql> select count(1) from SmsSend partition(py01);
 +----------+
 | count(1) |
 +----------+
 | 10 |
 +----------+
 1 row in set (0.00 sec)
 mysql> explain partitions select * from SmsSend where SendTime < '2015-01-01'; #The data of 2014 falls into the first partition +----+-------------+---------+------------+---------------+------+---------+------+------+----------+----------+-------------+
 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
 +----+-------------+---------+------------+------+---------------+-----+---------+------+------+----------+-------------+
 | 1 | SIMPLE | SmsSend | py01 | ALL | NULL | NULL | NULL | NULL | 10 | 33.33 | Using where |
 +----+-------------+---------+------------+------+---------------+-----+---------+------+------+----------+-------------+
 1 row in set, 2 warnings (0.00 sec)
 mysql> select count(1) from SmsSend partition(py02);
 +----------+
 | count(1) |
 +----------+
 | 10 |
 +----------+
 1 row in set (0.00 sec）

2) Quickly create a smssendbak backup table with the same structure as the original SmsSend table, and delete all partitions of the backup table

mysql> CREATE TABLE smssendbak LIKE SmsSend;
 Query OK, 0 rows affected (0.14 sec)
 mysql> ALTER TABLE smssendbak REMOVE PARTITIONING;
 Query OK, 0 rows affected (0.19 sec)
 Records: 0 Duplicates: 0 Warnings: 0

3) Use EXCHANGE PARTITION to transfer partition data to the backup table and view the partition records of the original table and the new backup table

SMSensdbak records

mysql> ALTER TABLE SmsSend EXCHANGE PARTITION py01 WITH TABLE smssendbak;
 Query OK, 0 rows affected (0.13 sec)
 mysql> select count(1) from SmsSend partition(py01); #Compare the records of the original SmsSend table partition above+----------+
 | count(1) |
 +----------+
 | 0 |
 +----------+
 1 row in set (0.00 sec)
 mysql> SELECT COUNT(1) FROM smssendbak; #View the new smssendbak backup table transfer record+----------+
 | COUNT(1) |
 +----------+
 | 10 |
 +----------+
 1 row in set (0.00 sec)
*****************Table used in the test****************************************************************************
 Create a basic test table:
 CREATE TABLE `tb` ( 
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Table primary key', 
 `pid` int(10) unsigned NOT NULL COMMENT 'Product ID', 
 `price` decimal(15,2) NOT NULL COMMENT 'Unit price', 
 `num` int(11) NOT NULL COMMENT 'Purchase quantity', 
 `uid` int(10) unsigned NOT NULL COMMENT 'Customer ID', 
 `atime` datetime NOT NULL COMMENT 'Order time', 
 `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'Modification time', 
 `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Soft delete flag', 
 ) ;
 Insert data:
 INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); 
 INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00'); 
 INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00'); 
 INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00'); 
 INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00'); 
 INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00'); 
 INSERT INTO tb(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00'); 
 
 ************************************Insert a large amount of data (more than one million is recommended)****************************************
 INSERT INTO `tb`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`;
****Note: If you want to delete the self-incrementing primary key id (during the modification process, it is recommended to change the database to read-only), do the following:
  Alter table tb change id id int(10); #Delete the auto-increment first Alter table tb drop primary key; #Delete the primary key Alter table tb change id id int not null auto_increment; #If you want to reset it to an auto-increment field Alter table tb auto_increment=1; #Start of auto-increment

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.

You may also be interested in:

MySQL Best Practices: Basic Types of Partition Tables
Basic introductory tutorial on MySQL partition tables
Best Practices Guide for MySQL Partitioned Tables
Correct use of MySQL partition tables
Detailed explanation of the limitations and restrictions of MySQL partitioned tables
Management and maintenance of Mysql partition tables
MySQL optimization partition table
A method to improve performance using MySQL partition tables in Rails
MySQL tutorial on how to use partition tables (delete partition tables)
Detailed explanation of MySQL partition table

<<: How to deploy Vue project under nginx

>>: Vue uses v-model to encapsulate the entire process of el-pagination components

How to communicate between WIN10 system and Docker internal container IP

Problems and solutions encountered when deploying a project when upgrading the Mysql database from version 5.6.28 to version 8.0.11

Blog

Learn the principles and common operations of MySQL partition tables through examples

How to communicate between WIN10 system and Docker internal container IP

A graphic tutorial on how to install MySQL in Windows

Make your website run fast

Problems and solutions encountered when deploying a project when upgrading the Mysql database from version 5.6.28 to version 8.0.11

Basic JSON Operation Guide in MySQL 5.7

Dockerfile implementation code when starting two processes in a docker container

Docker data storage tmpfs mounts detailed explanation

HTML vertical column display text to make the text display in vertical columns

Summary of some common writing methods that cause MySQL index failure

Website front-end performance optimization: JavaScript and CSS

Recommend

The use of FrameLayout in six layouts

Detailed explanation of the process of querying user permissions using mysql statements

Detailed explanation of mktemp, a basic Linux command

Detailed explanation of how to efficiently import multiple .sql files into MySQL

CSS web page responsive layout to automatically adapt to PC/Pad/Phone devices

Summary of Mysql high performance optimization skills

Pure CSS to display the √ sign in the lower right corner after selecting the product

Some indicators of excellent web front-end design

Detailed explanation of how to create MySql scheduled tasks in navicat

HTML hyperlink style (four different states) setting example

How to modify the ssh port number in Centos8 environment

Border-radius IE8 compatible processing method

MySql import CSV file or tab-delimited file

jQuery plugin to achieve code rain effect

Basic concepts and common methods of Map mapping in ECMAScript6