3 Methods for MYSQL table partitioning

Background knowledge:

Table partitioning is to store logically the same range of data in the same file, just like the supermarket, put the same kind of goods in the same area, the different goods in different places. The difference is in the supermarket

is classified according to the purpose, the table partition is divided according to its range of values.

What's the use of partitioning?

1. After partitioning, you can use multiple files to save the data in the table, and these files can be located on different hard disks so that you can take advantage of the IO capability of multiple hard disks simultaneously.

2, the partition after the lock will be more flexible, if you want to lock a large number of data rows at a time, and the table is not partitioned, MYSQL may be to reduce the cost of the lock, will lock up the row

Level is a table lock, if the table is partitioned, the data rows to be accessed are only in the one or two partitions of the table, so that only the two partitions can be locked. Instead of locking the entire table.

3, because the current MySQL database is saved in a folder, as if the folder can not cross the hard disk, so the 1th said the benefits of no, but in other

This benefit exists in databases such as SQL Server.

Method 1,

Partition by range (COLUMN_NAME) partition pt ... Values less than method

CREATE TABLE T1 (X int, Y int)
Partition by Range (X) (
Partition P0 values less than (10),
Partition P3 values less than (100),
Partition P5 values less than maxvalue);

So I gave T1 three zones based on the value of X.

Method 2

Partiton by list (column) partition Pt.. Values in (Value1,value2,...);

CREATE TABLE T2 (X int, Y int)
PARTITION by LIST (X) (
Partition P1 values in (a),
Partition P2 values in (4,5,6),
Partition P3 values in (7,8,9))
;

Here is the file created by Method 1, Method 2.

3 Methods for MYSQL table partitioning