MySQL Partition _ MySQL

MySQL partition

Partitions are divided into vertical partitions and horizontal partitions:

Vertical table sharding splits a table Tab with N1 + N2 fields into a child table with the N1 field Tab1 and a child table with the (N2 + 1) fields Tab2;

The sub-table Tab2 contains the primary key information about the sub-table Tab1. Otherwise, the association between the two tables will be lost.

Of course, vertical table sharding will bring about modification of SQL statements on the program side. if the application has been applied for a long time, the upgrade of the program will be time-consuming and error-prone, that is, the upgrade cost will be high.

The Horizontal partitioning technology splits a table into multiple tables. the common method is to split the records in the table according to a Hash algorithm,

A simple splitting method is as follows.

Similarly, this partitioning method must modify the SQL statements in the front-end application.

In addition, an SQL statement may modify two tables. Therefore, you must write two SQL statements to complete a logical transaction, making the judgment logic of the program more and more complex, in this way, the program maintenance cost is high, and the advantage of using the database is also lost.

Therefore, the partitioning technology can effectively avoid the above drawbacks and become a powerful solution to massive data storage.

Run the show variables command to determine whether MySQL supports partitioning.

show variables like "%partition%" ; 

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   | 
+-------------------+-------+

The MySQL partition engine runs in a separate layer and can interact with any of these layers.

All partitions in the same partition table must use the same storage engine.

To configure a dedicated storage engine for a partition table, you must use the [STORAGE] ENGINE option only.

[STORAGE] the ENGINE must be listed before any other partition options used in the create table statement.

You can use the data directory (DATA path) and index directory (INDEX path) options of the PARTITION clause of the create table statement used to CREATE a partition table, specify a specific path for the data and index of each partition.

The MAX_ROWS and MIN_ROWS options can be used to set the maximum and minimum number of rows, which can be saved in each partition.

Partition type:

Range partitioning	Based on the column value of a given continuous interval
List partition	Select a value based on the column value matching a value in a discrete value set
Hash partition	Partitions selected using the return values of user-defined expressions
Key partition	KEY partitions only support the calculation of one or more columns

The partition is always automatically sequentially numbered when it is created and records from 0.

When a new row is inserted into a partition table, these partition numbers are used to identify the correct partitions.

References:

Http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html