Summary of MySQL partitioning_MySQL

Foreword

 Partitioning refers to decomposing a large table into multiple smaller parts according to certain rules. The rules here are generally to use partitioning rules to split the table horizontally; the logic has not changed but in fact the table has been split into Multiple physical objects are created, each divided into an independent object. Compared with a single table without partitions, a partitioned table has many advantages, including: concurrent statistical queries, fast archiving and deletion of partitioned data, decentralized storage, and better query performance.

Mysql5.7 and later query statements support specified partitions. For example: "SELECT * FROM t PARTITION (p0,p1) WHERE c < 5" The same applies to specified partitionsDELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML .

Database version: mysql5.7.12

Whether it supports partitions

SHOW PLUGINS ;

If the status of the query partition is active, it means that partitioning is supported. If it is a source code installation, add "-DWITH_PARTITION_STORAGE_ENGINE=1 " during the compilation process.

Note: MERGE, CSV, or FEDERATED storage engines do not support partitioning. All partitions of the same table must use the same storage engine. You cannot use MYISAM for partition 1 and INNODB for partition 2; different partition tables can use different storage engines. .

Introduction to Partitions

At present, the partition types available for mysql mainly include the following:

RANGE partitioning: Based on a given continuous interval range, RANGE is mainly based on integer partitioning. For non-integer fields, expressions need to be used to convert them into integers.

LIST partitioning: is partitioned based on the listed list of enumeration values.

  COLUMNS partition: Can directly partition non-integer fields without conversion through expressions. At the same time, COLUMNS partition also supports multiple field combination partitions. Only RANGELIST has COLUMNS partition. COLUMNS is an upgrade of RANGE and LIST partitions.

      HASH partitioning: Distribute data to different partitions based on the given number of partitions. HASH partitioning can only HASH integers, and non-integer fields can only be converted into integers through expressions.

​​​ KEY partitioning: Supports partitioning of all data types except text and BLOB. Key partitioning can be partitioned directly based on fields without conversion to integers.

Instructions

1. Pay attention to the case sensitivity of partition names and keyword issues.

2. Regardless of the partition type, either there is no primary key or unique key in the partitioned table, or the primary key or unique key is included in the partition column. For tables with primary keys or unique keys, fields other than the primary key or unique key cannot be used as partitions. field.

前 The previous version of the 3.5.7 version shows the execution plan of the partition: Explain Partitions; after 5.7

4. There is no mandatory requirement for the partition column to be non-null. It is recommended that the partition column be NOT NULL. In the RANGE partition, if a NULL value is inserted into the partition column, it will be treated as the smallest value. In the LIST partition, the NULL value must be In the enumeration list, otherwise the insertion fails, and NULL values ​​in the HASH/KEY partition will be treated as 0.

    5. Mysql provides conversion functions based on time type fields "YEAR(), MONTH(), DAY(), TO_DAYS(), TO_SECONDS(), WEEKDAY(), DAYOFYEAR()"

6. After splitting and merging partitions, the statistical information of the modified partition will be invalid. The statistical information of the unmodified partition will still be there, and it will not affect the addition of the newly inserted value to the statistical information. At this time, the Analyze operation needs to be performed on the table.

7. It is recommended to use COLUMNS partitioning for RANGLIST partitioning of non-integer fields.

Delete and add partitions

The usage of each partition is introduced in detail in the introduction of each partition, but they all introduce the creation of partitions and the modification and deletion of individual partitions when creating a table. You can also add partitions to an existing table, which can be done all at once. Delete the entire table partition.

1. Remove the table partition

ALTER TABLE tablename
REMOVE PARTITIONING ;

Note: Using remove to remove a partition only removes the definition of the partition and does not delete the data. This is different from drop PARTITION, which will delete the data together

2. Create a partition for a table that already has records. Take adding a range partition as an example. The syntax is the same as creating a table and creating a partition.

ALTER TABLE `tb_partition`.`tb_varchar` 
PARTITION BY RANGE(id) PARTITIONS 3( PARTITION part0 VALUES LESS THAN (5000), PARTITION part1 VALUES LESS THAN (10000), PARTITION part2 VALUES LESS THAN (MAXVALUE)) ;

Note: After creating a partition for an existing table, the data will be distributed to each partition file according to the partition definition

Summary

The above is the entire introduction and summary of MySQL partitioning. I hope this article will be helpful to everyone when using mysql.