Shulou(Shulou.com)06/01 Report--

MySQL supports horizontal partitions and does not support vertical partitions.

Horizontal partitions, in behavioral units, are divided into different physical files; vertical partitions are divided for columns.

MySQL partitions are supported by MyISAM as well as InnoDB, so partitioning is not an engine-level thing.

Partitioning doesn't necessarily make access faster, especially for OLTP applications, where OLAP applications tend to work better with partitions.

The following partition types are supported by MySQL.

RANGE row data is divided based on column values of a given contiguous interval. LIST is similar to RANGE, except that LIST is not for continuous interval values, but for discrete sets. HASH is partitioned according to the custom expression return value, and negative number return is not supported. KEY partitions 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 partitioned column must be part of the unique index. Conversely, if the current table does not have a primary key defined or a unique index, the partitioned column can be any column.

Example of partition operation:

Create table sales (id int, price decimal (9), 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 function to get range value

Create table sales (id int, price decimal, 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), 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 exampl

Create table sales (id int, price decimal (9 price decimal), season int, finyear datetime) partition by LIST (id) (partition S1 values in), partition S2 values in (2, 4, 6, 8))

HASH partition exampl

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

KEY partition exampl

Create table sales (id int, price decimal (9 Magazine 2), season int, finyear datetime) engine=innodbpartition by key (finyear); data storage structure and use effect after partitioning

Take RANGE partition as an example

Insert data

Insert into sales select 1meme 10.05 repartee 2018-01-01 * 2018-01-01 * 2018-03-01 *

Execute query

Explain partitions select * from sales where finyear < '2016-01-01'

As you can see, the query optimizer directly skips the fourth partition and queries in the first three partitions, which is what you want.

In addition to partitioning, there are sub-tables, sub-libraries, and sharding operations, each of which is very important and has its own applicable scenarios.

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.