PARTITION Technology for Mysql Data Tables

In this chapter, let's take a look at the Partitioning technology (RANGE, LIST, HASH) in Mysql)
 
The partitioning Technology of Mysql is a bit similar to that of horizontal table sharding, but it is a horizontal table sharding at the logic layer. For applications, it is still a table, in other words: A partition does not actually split a table. After a partition, the table is still a table. It splits the storage file.

There are several partition types in Mysql 5.1 (later:
 
RANGE partitioning: allocates multiple rows to the partition based on the column values that belong to a given continuous interval.

LIST partition: similar to partitioning by RANGE. The difference is that LIST partitions are selected based on column values matching a value in a discrete value set.

HASH partition: Select partitions Based on the return values of user-defined expressions. This expression calculates the values of these rows to be inserted into the table, this function can contain any expressions that are valid in Mysql and generate non-negative integer values.

KEY partition: It is used repeatedly by HASH partition. The difference is that KEY partition only supports computing one or more columns, and the Mysql server provides its own HASH function.
 
Precautions for partitioning:

1. When partitioning, either the primary key is not defined or the partition field is added to the primary key.
2. The partition field cannot be NULL. Otherwise, how can we determine the partition range?
 
First, you can check whether your Mysql version supports PARTITION.
Copy codeThe Code is as follows:
Mysql> show plugins;
 
| Partition | ACTIVE | storage engine | NULL | GPL |

Or:
Copy codeThe Code is as follows:
Mysql> show variables like "% part % ";
 
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Have_partitioning | YES |
+ ----------------- + ------- +

RANGE partitioning
 
Assume that you have created the following table, which stores records of employees of 20 audio stores, ranging from 1 to 20. If you want to divide it into four small partitions, you can use the RANGE partition. The database table created is as follows:
Copy codeThe Code is as follows:
Mysql-> create table employees (
-> Id int not null,
-> Fname VARCHAR (30 ),
-> Lname VARCHAR (30 ),
-> Hired date not null default '2017-01-01 ',
-> Separated date not null default '2017-12-31 ',
-> Job_code int not null,
-> Store_id INT NOT NULL
->) ENGINE = Myisam default charset = utf8
-> Partition by range (store_id )(
-> PARTITION P0 values less than (6 ),
-> PARTITION P1 values less than (11 ),
-> PARTITION P2 values less than (16 ),
-> PARTITION P3 values less than (21)
-> );
If you want to store employees who have resigned in different periods separately, you can use the Date Field separated (that is, the departure time) as a key. The created SQL statement is as follows:
Copy codeThe Code is as follows:
Mysql-> create table employees (
-> Id int not null,
-> Fname VARCHAR (30 ),
-> Lname VARCHAR (30 ),
-> Hired date not null default '2017-01-01 ',
-> Separated date not null default '2017-12-31 ',
-> Job_code int not null,
-> Store_id INT NOT NULL
->) ENGINE = Myisam default charset = utf8
-> Partition by range (YEAR (separated ))(
-> PARTITION P0 values less than (2001 ),
-> PARTITION P1 values less than (2011 ),
-> PARTITION P2 values less than (2021 ),
-> PARTITION P3 VALUES LESS THAN MAXVALUE
-> );
 
List Partition
 
In the same example, if these 20 photo and video stores are distributed in four regions with the Authority,
Copy codeThe Code is as follows:
+ ------------------ + ---------------------------------------- +
| Region | ID of the audio and video store |
+ ------------------ + ---------------------------------------- +
| Northern District | 3, 5, 6, 9, 17 |
| Eastern region | 1, 2, 10, 11, 19, 20 |
| West District | 4, 12, 13, 14, 18 |
| Central Area | 7, 8, 15, 16 |
+ ------------------ + ---------------------------------------- +
 
Mysql-> create table employees (
-> Id int not null,
-> Fname VARCHAR (30 ),
-> Lname VARCHAR (30 ),
-> Hired date not null default '2017-01-01 ',
-> Separated date not null default '2017-12-31 ',
-> Job_code int not null,
-> Store_id INT NOT NULL
->) ENGINE = Myisam default charset = utf8
-> Partition by list (store_id )(
-> PARTITION pNorth values in (3, 5, 6, 9, 17 ),
-> PARTITION pEast values in (1, 2, 10, 11, 19, 20 ),
-> PARTITION pWest values in (4, 12, 13, 14, 18 ),
-> PARTITION pCentral values in (7, 8, 15, 16)
-> );

After creation, you can enter the Mysql data storage file, which is defined in the Mysql configuration file.
Copy codeThe Code is as follows:
Shawn @ Shawn :~ $ Sudo vi/etc/mysql/my. cnf;
 
[Mysqld]
Datadir =/var/lib/mysql
 
Shawn @ Shawn :~ $ Cd/var/lib/mysql/dbName
Shawn @ Shawn:/var/lib/mysql/dbName $ ll
 
Shown as follows:
8768 Jun 7 employees. frm
48 Jun 7 employees. par
0 Jun 7 employees # P # pCentral. MYD
1024 Jun 7 employees # P # pCentral. MYI
0 Jun 7 employees # P # pEast. MYD
1024 Jun 7 employees # P # pEast. MYI
0 Jun 7 employees # P # pNorth. MYD
1024 Jun 7 employees # P # pNorth. MYI
0 Jun 7 employees # P # pWest. MYD
1024 Jun 7 employees # P # pWest. MYI
It can be seen from this that it splits the storage file according to our definition.
Copy codeThe Code is as follows:
Employees. frm = table structure
Employees. par = partition, declaring a partition table
. MYD = Data File
. MYI = index file
 
HASH Partition
 
HASH partitions are mainly used to ensure that data is evenly distributed in pre-defined partitions.
If you want to store employees added in different periods separately, you can use the Date Field hired as a key.
Copy codeThe Code is as follows:
Mysql-> create table employees (
-> Id int not null,
-> Fname VARCHAR (30 ),
-> Lname VARCHAR (30 ),
-> Hired date not null default '2017-01-01 ',
-> Separated date not null default '2017-12-31 ',
-> Job_code int not null,
-> Store_id INT NOT NULL
->) ENGINE = Myisam default charset = utf8
-> Partition by hash (YEAR (hired ))(
-> PARTITIONS 4
-> );

# Note the following: PARTITIONS, with an additional s
The above example uses the Myisam storage engine, which uses independent tablespace by default, so you can see different partitions in the above disk space.
The InnoDB engine uses the shared tablespace by default. Even if you partition the InnoDB table, you can see that it does not perform physical partitions like Myisam, so you need to modify the Mysql configuration file:
Copy codeThe Code is as follows:
Shawn @ Shawn :~ $ Sudo vi/etc/mysql/my. cnf;
 
# Add:
Innodb_file_per_table = 1
 
# Restart mysql
Shawn @ Shawn :~ $ Sudo/etc/init. d/mysql restart
Now you can partition the InooDB as follows:
Copy codeThe Code is as follows:
8768 Jun 7 employees. frm
48 Jun 7 22: 54 employees. par
98304 Jun 7 employees # P # pCentral. ibd
98304 Jun 7 employees # P # pEast. ibd
98304 Jun 7 employees # P # pNorth. ibd
98304 Jun 7 employees # P # pWest. ibd
Partition Management
 
Delete Partition
Copy codeThe Code is as follows:
Mysql> alter table employees drop partition pWest;
Add Partition
Copy codeThe Code is as follows:
# Add a new partition in range
Mysql> alter table employees add partition (partition p4 values less than (26 ));

# Add a new partition in list
Mysql> alter table employees add partition (partition pSouth values in (21, 22, 23 ));

# Hash re-partitioning
Mysql> alter table employees add partition partitions 5;