MySQL Partition Introduction

Introduction

Partitioning refers to the decomposition of a large table into smaller parts according to certain rules, where the rules generally use partitioning rules to slice the table horizontally; it doesn't change logically, but actually the table has been split into multiple physical objects, each divided into a separate object. There are a number of advantages to partitioned tables in the case of a table without partitioning: Concurrency statistics query, Fast Archive delete partition data, distributed storage, query performance is better.

mysql5.7 after the query statement supports the specified partition, for example: "" Specifies that the partition also applies,,,, SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 DELETE INSERT REPLACE UPDATE LOAD DATA and, 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 , orFEDERATED存储引擎不支持分区，同一个表所有的分区必须使用相同的存储引擎，不能分区1使用MYISAM分区2又使用INNODB；不同的分区表可以是不同的存储引擎。

Partition type

Currently, there are several main types of partitions available for MySQL:

Range Partition: Based on a given range of contiguous intervals, range is primarily an integer-based partition, and some time-type fields can be converted to shaping with corresponding functions. A range column partition based on non-shaping is also available in versions over 5.5.

List partition: Similar to the range partition, the difference is that the list partition is partitioned based on the list of enumerated values, and it also supports the use of function transformations. A list column is also available that supports non-shaping partitions.

Hash partition: Assigns data to different partitions based on the number of partitions given. Expression based on nonnegative integer value

Key partition: Hash-like partition.

Note: The partition number is starting at 0, noting the case sensitivity of the partition name, and the keyword issue.

Regardless of the partition type, either the primary key or unique key is not present in the partition table, or the primary key or unique key is contained within the partition column, and the table with the primary key or unique key cannot use a field other than the primary key or unique key as the partition field.

SummaryThe latest version of 5.7 also does not have a partition scheme that is specifically based on the time type of the field, but partitions of the time type can be substituted in other ways such as the range and the list partition can use the time function, the key partition can be directly based on the time type of the field partition; The conversion function of field based on time type MySQL provides "Year (), MONTH (), Day (), To_days (), To_seconds (), WEEKDAY (), DayOfYear ()" ；Each partition is described in detail in the next article.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly give the link. Welcome to the exchange of discussions

MySQL Partition Introduction