MySQL Partition summary

MySQL supports a partition type of horizontal partitioning and does not support vertical partitioning.

Horizontal partitioning, in behavioral units, into different physical files; vertical partitioning is for column partitioning.

MySQL partition, in addition to InnoDB support, MyISAM also support, so the partition is not an engine-level thing.

Partitioning does not necessarily make access faster, especially for OLTP applications, where it is often better to use partitioning for OLAP applications.

There are several types of partitions supported by MySQL.

• The RANGE row data is based on the column values of a given contiguous interval.
• The list is similar to range, except that the list is oriented not to continuous interval values, but to discrete collections.
• The HASH is partitioned according to the custom expression return value and does not support negative returns.
• KEY is partitioned according to the hash function provided by MySQL

Regardless of the type of partitioning, if the current table has a primary key or a unique index, the partitioning column must be part of a unique index. Conversely, if the current table does not have a primary key defined and there is no unique index, the partition column can be any column.

Partition Operation Example:

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by range(season) (prtition s1 values less than (4),partition s2 values less than (7),partition s3 values less than (10),partition s4 values less than (13));

Use a function to get a range value

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by range(year(finyear)) ( partition s1 values less than (2016), partition s2 values less than (2017), partition s3 values less than (2018),partition s4 values less than (2020));

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by range(year(finyear)*100+ month(finyear)) ( partition s1 values less than (201804), partition s2 values less than (201807), partition s3 values less than (201810),partition s4 values less than (201813));

List Partition Example

create table sales(id int, price decimal(9,2), season int, finyear datetime) partition by LIST(id) ( partition s1 values in (1,3,5,7,9), partition s2 values in (2,4,6,8,10));

Example of a hash partition

create table sales(id int, price decimal(9,2), season int, finyear datetime) engine=innodbpartition by hash(year(finyear));

Key Partitioning Example

create table sales(id int, price decimal(9,2), season int, finyear datetime) engine=innodbpartition by key(finyear);

Data storage structure after partitioning and its use effect

Take the range partition as an example

Inserting data

insert into sales select 1,10.05,1,‘2018-01-01‘;insert into sales select 2,10.05,3,‘2018-03-01‘;insert into sales select 3,10.05,4,‘2018-04-01‘;

Execute Query

explain partitions select * from sales where finyear < ‘2016-01-01‘;

As you can see, the query optimizer jumps directly over the fourth partition and queries in the first three partitions, which is the effect.

In addition to partitioning, there are sub-tables, sub-libraries, partitioning operations, each of which is important, but also have their own applicable scenarios.

MySQL Partition summary