MysqlPARTITION Data Table Partitioning technology

In this section, we will take a look at the Mysql partition technology (RANGE, LIST, HASH) and horizontal table sharding technology, however, it is a horizontal table sharding at the logic layer. For an application, it is still a table. In other words, a partition is not actually sharding a table, but a table after a partition, it stores

In this section, we will take a look at the Mysql partition technology (RANGE, LIST, HASH) and horizontal table sharding technology, however, it is a horizontal table sharding at the logic layer. For an application, it is still a table. In other words, a partition is not actually sharding a table, but a table after a partition, it stores

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.

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> show plugins; | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | Or: mysql> show variables like "%part%"; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+

One day in 2013

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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql-> CREATE TABLE employees ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATENOT NULL DEFAULT '1970-01-01', -> separated DATENOT NULL DEFAULT '9999-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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql-> CREATE TABLE employees ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATENOT NULL DEFAULT '1970-01-01', -> separated DATENOT NULL DEFAULT '9999-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,

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

+------------------+--------------------------------------+ | 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 DATENOT NULL DEFAULT '1970-01-01', -> separated DATENOT NULL DEFAULT '9999-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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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 22:01 employees.frm 48 Jun 7 22:01 employees.par 0 Jun 7 22:01 employees#P#pCentral.MYD 1024 Jun 7 22:01 employees#P#pCentral.MYI 0 Jun 7 22:01 employees#P#pEast.MYD 1024 Jun 7 22:01 employees#P#pEast.MYI 0 Jun 7 22:01 employees#P#pNorth.MYD 1024 Jun 7 22:01 employees#P#pNorth.MYI 0 Jun 7 22:01 employees#P#pWest.MYD 1024 Jun 7 22:01 employees#P#pWest.MYI

It can be seen from this that it splits the storage file according to our definition.

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

mysql-> CREATE TABLE employees ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30), -> hired DATENOT NULL DEFAULT '1970-01-01', -> separated DATENOT NULL DEFAULT '9999-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:

1 2 3 4 5 6 7

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:

1 2 3 4 5 6

8768 Jun 7 22:54 employees.frm 48 Jun 7 22:54 employees.par 98304 Jun 7 22:54 employees#P#pCentral.ibd 98304 Jun 7 22:54 employees#P#pEast.ibd 98304 Jun 7 22:54 employees#P#pNorth.ibd 98304 Jun 7 22:54 employees#P#pWest.ibd

Partition Management

Delete Partition

1	mysql> alter table employees drop partition pWest;

Add Partition

1 2 3 4 5 6 7 8

# 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;

If you have better suggestions and code snippets, please leave a message.