Summarize Mysql's Partitioning _mysql

Objective

Partitioning is the decomposition of a large table into smaller parts based on certain rules, the rule here is to use partitioning rules to slice the table horizontally; there is no logical change, but in fact the table has been split into multiple physical objects, each divided into a separate object. Partitioned tables have many advantages over those that do not have partitions: concurrent statistical queries, rapid archival deletion of partitioned data, decentralized storage, and better query performance.

mysql5.7 query statements support the specified partition for example: " SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 " specifies that the partition also applies DELETE, INSERT, REPLACE, UPDATE, and LOAD DATA, LOAD XML .

database version: mysql5.7.12

Whether to support partitioning

Show PLUGINS;

Query partition state is active on behalf of the support partition, if the source is installed in the process of compiling to add " -DWITH_PARTITION_STORAGE_ENGINE=1 \ ".

Note: MERGE, CSV, or federated storage Engine does not support partitioning, all partitions of the same table must use the same storage engine, cannot partition 1 use MyISAM partition 2 and use InnoDB; different partition tables can be different storage engines.

Section Introduction

Currently, the types of partitions available to MySQL are mainly as follows:

Range Partition: based on a given range of contiguous intervals, range is primarily an integer-based partition, and for a field that is not shaped, it needs to be converted to a shape by an expression.

List partition: is partitioned based on the list of enumerated values listed.

Columns Partitioning: You can partition a non-reshaping field directly without the conversion of an expression, and the columns partition also supports multiple field combination partitions, only rangelist exist columns partitions. Columns is an upgrade of the range and list partitions.

Hash Partitioning: allocates data to different partitions based on the number of partitions given, and hash partitions can only be hashed for integers, and fields that are not shaped can only be converted to integers by an expression.

key partition: a partition that supports all data types except text and blobs, and a key partition can be partitioned directly based on a field without converting to an integer.

Description

1. Note the case sensitivity of the partition name, and keyword problems.

2. Regardless of the type of partition, either there is no primary key or unique key in the partition table, or the primary key or unique key is included in the partition column, the table with the primary key or unique key cannot use a field other than the primary key or the unique key as the partition field.

3.5.7 Previous versions show zoning execution plan use: Explain partitions;5.7 direct execution later: explain

4. There is no mandatory partitioning column is not NULL, the proposed column is not null; In the range section, inserting a null value into a partition column is treated as a minimum value, in which the null value must otherwise be inserted in the enumeration list, or the insertion fails in the hash/ Null values in a key partition are treated as 0来.

5. Conversion functions based on the Time Type field MySQL provides "year (), MONTH (), Day (), To_days (), To_seconds (), Weekday (), DayOfYear ()"

6. Splitting the merged partitions causes the statistics of the modified partitions to fail, and the statistics for the partitions that are not modified are still in, without affecting the newly inserted values added to the statistics; you need to perform a analyze operation on the table.

7. Columns partitions are recommended for rang\list partitions for non-reshaping fields.

Remove add partition

The use of each partition is described in detail in each section introduction, but it is all about creating a partition and modifying a delete partition when creating a table, or by adding a partition to a table that already exists, and you can delete the entire table partition at one time.

1. Remove a partition from a table

ALTER TABLE tablename
REMOVE partitioning;

Note: Removing a partition using remove removes the definition of the partition and does not delete the data and drop partition, which are deleted along with the data

2. Create partitions on tables that already have records to increase the range partition as an example, as with the syntax for creating a table-built 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 you create a partition on an existing table, the data is distributed to each partition file according to the definition of the partition

Summarize

The above is the MySQL partition summary of the entire content, I hope this article on the use of MySQL when you help.