Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "sample analysis of the principle of MySQL partition table", the content is simple and clear, and I hope it can help you solve your doubts. Let me lead you to study and study the "sample analysis of the principle of MySQL partition table".

1. The meaning of partition table

Partition table definition refers to the allocation of multiple parts of a single table across file systems based on rules that can be set to any size. In fact, different parts of the table are stored as separate tables in different locations. The rules selected by the user to implement data segmentation are called partition functions, which in MySQL can be modular, or simply match a continuous range or list of values, or an internal HASH function, or a linear HASH function.

The difference between a partition and a partition is that a partition logically has only one table, while a partition breaks down a table into multiple tables.

2. Advantages of partitioned tables

1) Partition tables are easier to maintain. For data that has lost its preservation meaning, it is usually easy to delete that data by deleting the partitions associated with that data. On the contrary, in some cases, the process of adding new data can be easily achieved by adding a new partition to those new data.

2) some queries can be greatly optimized, mainly because the data that satisfies a given WHERE statement can be saved in only one or more partitions, so that there is no need to find other partitions when searching. Because partitions can be modified after the partition table is created, you can reorganize the data to improve the efficiency of commonly used queries when you first configure the partitioning scheme.

3) optimize the query. When it comes to things such as SUM () and COUNT (), it can be processed in parallel on multiple partitions, and the final result only needs to be obtained by summing up the results of all partitions.

4) to achieve greater query throughput by dispersing data queries across multiple disks.

3. Partition table restrictions

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

2) in MySQL5.1, the partition expression must be an integer or an expression that returns an integer. Support for non-integer expression partitioning is provided in MySQL5.5

3) if there is a primary key or unique index column in the partition field, then many primary key columns and unique index columns must be included. That is, partitioned fields contain either no primary key or index columns, or all primary keys and index columns

4) Foreign key constraints cannot be used in partitioned tables

5) the partition of MySQL applies to all the data and indexes of a table. It cannot partition only the data of the table without partitioning the index, nor can it partition only the index without partitioning the table, nor can it partition only part of the data of the table.

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

7) if there is a primary key and a unique index in the table, the unique index column should contain the partition key when partitioning by the primary key field.

8) currently, mysql does not support partitioning between space types and temporary table types. Full-text indexing is not supported.

9) object restrictions (partition expressions cannot appear Stored functions, stored procedures, UDFs, orplugins,Declared variables or user variables.)

10) Operation restrictions (operations such as addition, subtraction and multiplication are supported in partition expressions, but the result must be an INT or NULL. DIV is supported, but /, |, &, ^, and ~ is not allowed in partition expressions)

11) sql_mode restrictions (officials strongly recommend that you never change the sql_mode of mysql after creating a partition table. Because some functions or operations may return different results in different modes)

12) query_cache and INSERT DELAYED are not supported

13) the partitioning key cannot be a subquery (even if the subquery returns int or null.)

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

4. Partition type

1) horizontal partitioning (divided by row according to column attributes)

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

Several modes of horizontal partitioning:

* Range (scope): this mode allows DBA to divide the data into different ranges.

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

* Hash (hash): this mode allows DBA to calculate the Hash Key of one or more columns of the table, and finally partition the data regions corresponding to different values of the Hash code.

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

* Key (key value): an extension of the above Hash mode, where Hash Key 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 list defined by DBA. For example, DBA creates a table that spans three partitions, based on the data corresponding to the 2004, 2005 and 2006 values.

* Columns partition is a supplement to range,list partition, which makes up for the fact that the latter two only support integer partitions (or by converting to integers), which increases the support for data types (all integer types, date-time types, character types), as well as multi-column partitions.

Note: when inserting data on a multi-column partition table, use tuple comparison, that is, multi-column sorting, first sort according to field1, then sort according to field2, and then partition and store data according to the sorting results.

* Composite (composite mode): a combination of the above modes.

For example, on a table that initializes a Range range partition, you can perform a hash hash partition on one of the partitions.

Vertical partition (by column):

For example, a table containing large text and BLOB columns, these text and BLOB columns are not often accessed, these infrequently used text and BLOB can be divided into another partition, which can improve access speed while ensuring their data relevance.

Note: subpartitions (keyword subparttition): use RANGE or LIST partitions to form subpartitions again, which can be HASH partitions or KEY partitions. It is recommended to use on multiple disks.

Check to see if Partition partitioning tables are supported

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: in versions prior to MySQL 5.6.1, you can issue the command to view the have_partitioning parameter, which has been removed in the new version.

Mysql > SHOW VARIABLES LIKE'% partition%'

5. Several modes of partition table commonly used in actual combat

1) use RANGE partition mode

# create test table T1, insert nearly 4 million rows of data, and no more partitions, it takes time to query a 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 (15penny 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 deletion ID', PRIMARY KEY (`id`, `atime`) INSERT INTO T1 (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1mc12.23dl 89757cURRENTTIMESTAMP ()) INSERT INTO T1 (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`) VALUES (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `num`, `uid`, `atime`) VALUES (`pid`, `price`, `num`, `uid`, `atime`) VALUES INSERT INTO T1 (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`) VALUES (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`) VALUES INSERT INTO T1 (`pid`, `price`, `num`, `uid`, `atime`) VALUES / * * copy a large amount of data from master and slave * * / 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 如果是针对已有的表进行表分区，可以使用ALTER TABLE来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。 注: 这种会使服务器资源消耗比较大(400多万数据要1分多钟) 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` # check the partition situation +-+-- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | 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)

Also use the query above to test the results

Mysql > SELECT * FROM `t1` WHERE `uid` = 89757 AND `atime`

< CURRENT_TIMESTAMP(); 1048576 rows in set (4.46 sec) #与上面没有分区查询执行的时间相比少了接近1s mysql>

EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid` = 89757 AND `atime`

< CURRENT_TIMESTAMP(); #查看查询使用的分区情况 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 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) 同时也要注意，进行表分区以后，mysql存放的数据文件夹中该表的存放文件也被拆分为多个 -rw-r----- 1 mysql mysql 8.7K 2月 14 14:49 t1.frm -rw-r----- 1 mysql mysql 36M 2月 14 14:50 t1#P#p0.ibd -rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p1.ibd -rw-r----- 1 mysql mysql 92M 2月 14 14:50 t1#P#p2.ibd -rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p3.ibd 实际生产环境中，大多是采用另外一种方式:新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表，最后建立普通索引。 mysql>

CREATE TABLE `t2` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'table master', `pid` int (10) unsigned NOT NULL COMMENT 'product ID', `price` decimal (15journal 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', `utime` int (10) unsigned NOT NULL DEFAULT 0 COMMENT 'modification time' `isdel` tinyint (4) NOT NULL DEFAULT'0' COMMENT 'soft deletion logo', 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, where the primary key should be changed to id,stsdate federated primary key, and the partition table requires that the partition field 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 select_type p1, SIMPLE table: T2 partitions: p0 select_type, p3, p4, p6, p6, p7, p8, 9, p10, p12, p13, p14, p15, p16, p17, p18, 19, p20, p21, p22, p23, p24, 25, p27, 27, 28, 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 sec) Records: 4194304 Duplicates: 0 Warnings: 0

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

Mysqldump-u dbname-p-- no-create-info dbname T2 > t2.sq

Change the table name, import the data, test the ok, and delete the original table.

2) use LIST partitioning mode (if there is a primary key in the original table that strongly creates a new table, the original primary key and the field to be partitioned are created together as a federated primary key)

Mysql > CREATE TABLE `tb01` (`id`int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'table master', `pid` int (10) unsigned NOT NULL COMMENT 'product ID', `price` decimal (15L2) 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 deletion ID', 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 (sec) Records: 3145728 Duplicates: 0 Warnings: 0 mysql > ALTER TABLE tb01 PARTITION BY LIST (num) (PARTITION pl01 VALUES IN (1), PARTITION pl02 VALUES IN (2), PARTITION pl03 VALUES IN (5), PARTITION pl04 VALUES IN (6), PARTITION pl05 VALUES IN (9)) Query OK, 3145728 rows affected (48.86 sec) Records: 3145728 Duplicates: 0 Warnings: 0 generate in mysql data file The following documents-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 2 February 15 11:36 tb01#P#pl04.ibd-rw-r- 1 mysql mysql 52m February 15 11:36 tb01#P#pl05.ibdmysql > EXPLAIN PARTITIONS SELECT * FROM `tb01` +- -+-+ | 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 federated primary key

Mysql > CREATE TABLE tb02 (- > an int not null,-> b int not null->)-> PARTITION BY RANGE COLUMNS (apartition b) (- > partition p0 values less than (0L10),-> partition p1 values less than (10L20),-> partition p2 values less than (10L30),-> 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) # insert test data Query OK manually, 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

The main purpose of HASH is to distribute the data as evenly as possible among the set number of partitions. when performing a hash partition, mysql executes a hash function on the partition key to determine which partition the data is placed in. HASH partition is divided into conventional HASH partition and linear HASH partition, the former uses modulus algorithm, and the latter uses the operation rule of the power of linear 2.

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 (15penny 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', `utime` int (10) unsigned NOT NULL DEFAULT 0 COMMENT 'modification time' `isdel` tinyint (4) NOT NULL DEFAULT'0' COMMENT 'soft deletion ID', 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); INSERT INTO tb03 (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1); 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 partition makes the data evenly distributed on each partition as far as possible, which improves the query efficiency, it increases the cost of partition management. For example, before there were 5 partitions, now you have to add a partition, the algorithm has mod (expr,5) to (expr,6), and most of the data of the original 5 partitions have to be recalculated and re-partitioned. Although the use of linear HASH partitions reduces the cost of partition management, the data is not distributed as evenly as regular HASH.

5) KEY partition

KEY partitions are similar to HASH partitions, but you cannot customize expressions, but many types of partitioning keys are supported, except for text types such as Text,Blob.

CREATE TABLE `tb04` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'table master key', `pid` int (10) unsigned NOT NULL COMMENT 'product ID', `price` decimal (15pime2) 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', `utime` int (10) unsigned NOT NULL DEFAULT 0 COMMENT 'modification time' `isdel` tinyint (4) NOT NULL DEFAULT'0' COMMENT 'soft deletion ID', 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); INSERT INTO tb04 (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`); # check how the records fall in the partition with task execution 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 that the partition should not be modified as much as possible in the production environment. Alter will read out the data that exists in the old table and store it in the newly defined table. The process IO will be very large and the whole table will be locked.

* 1 * Delete partition: example with the above tb01 table

-- P05 partition query data is not deleted, mainly verifying 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 pl05 partitions, for example: delete multiple partitions at once, 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 is also deleted, carefully operate +-+ | count (1) | +-+ | 0 | +-+ 1 row in set (0.01 sec)

Note: deleting a partition will delete the data, so proceed with caution; do not delete hash or key partitions.

* 2* add partitions

Note: the value of the new partition cannot contain the value in the value list of any existing partition, otherwise an error will be reported; the new partition will reorganize the data and the original data will not be lost. After having a MAXVALUE value, you cannot directly add partitions. For example, take the T1 table above 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 mysql > ALTER TABLE tb01 ADD PARTITION to the pl05 partition deleted on tb01 (PARTITION pl05 VALUES IN (9 sec 10)); Query OK, 0 rows affected (0 Warnings) Records: 0 Duplicates: 0 Warnings: 0

* 3 * decompose Partition

Note: the Reorganize partition keyword can modify some or all partitions of a table without losing data. The overall scope of the partition should be consistent before and after 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 data mysql > insert tb05 values. 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 loss mysql > explain partitions select * from tb05 where salary=80 # View has fallen on the new partition p01 +-+ -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | 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 the decomposed p01 and p02 in the tb05 table above into p1

Mysql > alter table tb05 reorganize partition p01 partition 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, only adjacent partitions can be redefined, and partitions cannot be skipped, and the redefined partition interval must be consistent with the original partition interval, and the partition type can not 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 | 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 the tb03 table

Mysql > Alter table tb03 remove partitioning; # will not lose data 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 of type VARCHAR,BLOB, or TEXT), 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 the partition.

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

* 9 * check the partition:

You can check partitions in almost the same way as using CHECK TABLE for non-partitioned tables. This command tells the tb04 table partition p1rec p2 whether the data or index has been corrupted. If this happens, use "ALTER TABLE... REPAIR PARTITION" to patch the partition.

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

6. Simple application in practical production

Scenario: there was a non-partitioned big data scale SmsSend (example table, about 28 million rows), the statistical process is very time-consuming, consider using annual partition, and backup the historical database to transfer the data from the past 2014 to the new backup table smssendbak. If online redefinition is time-consuming, you can use exchange processing!

1) View the current SmsSend table

Mysql > SHOW CREATE TABLE SmsSend # check the creation information and do not partition | SmsSend | CREATE TABLE `SmsSend` (`Guid` char (36) NOT NULL COMMENT 'unique ID', `SID` varbinary (85) DEFAULT NULL COMMENT 'merchant unique number', `Mobile` longtext NOT NULL COMMENT 'receive mobile number (with "," split)', `SmsContent` varchar (500) NOT NULL COMMENT 'SMS content', `SmsCount` int (11) NOT NULL DEFAULT'1' COMMENT 'number', `Status` int (11) NOT NULL COMMENT 'current status (0) 1. Send successfully;-1, send 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) 2, Group send)', `ReceiveTime` datetime DEFAULT NULL COMMENT 'time to receive reply report', `Priority` int (11) NOT NULL DEFAULT'0' COMMENT 'priority', `UserAccount` varchar (50) DEFAULT NULL COMMENT 'operator', `ChainStoreGuid` char (36) DEFAULT NULL COMMENT 'unique store ID', `RelationKey`longtext COMMENT 'reply report associated ID', `Meno`Meno`remarks', `IsFree` bit (1) NOT NULL DEFAULT bounty 0' COMMENT'is free) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | mysql > SELECT COUNT (*) FROM SmsSend # Line record +-+ | COUNT (*) | +-+ | 28259803 | +-+ 1 row in set (1 min 52.60 sec) # We can see that online partitioning under big data's table is relatively slow and consumes performance mysql > 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 records mysql > 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'; #2014年的数据落在第一分区 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 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 original table partition record as well as the new backup table

Smssendbak record

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 partitioned in the original SmsSend table above +-+ | count (1) | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql > SELECT COUNT (1) FROM smssendbak # View new smssendbak backup table transfer record +-+ | COUNT (1) | +-+ | 10 | +-+ 1 row in set (0.00 sec) * tables used for testing * * * * 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 (15int 2) NOT NULL COMMENT' unit price', `num` int (11) NOT NULL COMMENT 'purchase quantity', `uid`int 'customer ID', `atime`datetime NOT NULL COMMENT', `utime`int (10) unsigned NOT NULL DEFAULT 0 COMMENT 'modification time, `isdel`tinyint (4) NOT NULL DEFAULT' 0' COMMENT 'soft deletion mark',) Insert data: INSERT INTO tb (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `nume`, `uid`, `atime`); INSERT INTO tb (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num7, `uid`, `atime`) VALUES (`pid`, `price`, `num`, `uid`, `atime`) VALUES INSERT INTO tb (`pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`) VALUES (1pid`, `price`, `num`, `uid`, `atime`) VALUES (`pid`, `price`, `num`, `uid`, `atime`) VALUES INSERT INTO tb (`pid`, `price`, `num`, `uid`, `atime`) VALUES * * insert a large amount of data (million or more 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-growing primary key id (during modification, it is recommended that the library be read-only), do the following: Alter table tb change id id int (10) # first delete the self-growing Alter table tb drop primary key;# delete the main building Alter table tb change id id int not null auto_increment; # if you want to reset to the self-increasing field Alter table tb auto_increment=1; # since the beginning of the above is all the contents of the article "sample Analysis of the principle of MySQL Partition Table", thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.