Partition analysis of MYSQL data table partitioning technology _mysql

In this section, let's look at the partitioning techniques in Mysql (RANGE, LIST, HASH)

The Mysql partitioning technique is somewhat similar to the horizontal table, but it is a horizontal table at the logical level, and it is a table for the application, in other words: The partition is not actually splitting a table, the table after the partition is a table, it is to split the storage file.

There are several types of partitions in Mysql 5.1 (after):

Range partition: Assigning multiple rows to a partition based on a column value that belongs to a given contiguous interval

List partition: Similar to a RANGE partition, the difference is that the list partition is selected based on a column value matching a value in a discrete set of values

Hash partition: A selection partition based on the return value of a user-defined expression that uses the column values of the rows that will be inserted into the table, which can contain any expressions that are valid in Mysql that produce nonnegative integer values

Key partition: Lifetimes in hash partition, except that the key partition supports only one or more columns, and the MYSQL server provides its own hash function

What should be noted for partitioning:

1, do the partition, either do not define the primary key, or the partition field into the primary key
2, the partition field can not be null, otherwise how to determine the scope of the partition, so try NOT null

First you can check if your Mysql version supports PARTITION.

Copy Code code as follows:

Mysql> Show plugins;

| Partition | ACTIVE | STORAGE ENGINE | NULL | GPL |

Or:

Copy Code code as follows:

Mysql> Show variables like "%part%";

+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+

RANGE Partition

Suppose you create a table that holds the staff records of 20 video stores, numbering from 1 to 20 for the 20 video stores. If you want to divide it into 4 small partitions, you can use the range partition to create the following database tables:

Copy Code code as follows:

Mysql-> CREATE TABLE Employees (
-> ID INT not NULL,
-> fname VARCHAR (30),
-> lname VARCHAR (30),
-> hired DATE not NULL DEFAULT ' 1970-01-01 ',
-> separated DATE not 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 separate the employees from different periods of time, you can use the Date field separated (that is, the separation time) as a key and create the following SQL statement:

Copy Code code as follows:

Mysql-> CREATE TABLE Employees (
-> ID INT not NULL,
-> fname VARCHAR (30),
-> lname VARCHAR (30),
-> hired DATE not NULL DEFAULT ' 1970-01-01 ',
-> separated DATE not 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 the 20 video stores are distributed in 4 areas with distribution rights,

Copy Code code as follows:

+------------------+--------------------------------------+
| Region | Video Store ID number |
+------------------+--------------------------------------+
| North District | 3, 5, 6, 9, 17 |
| East Side | 1, 2, 10, 11, 19, 20 |
| West Side | 4, 12, 13, 14, 18 |
| Center Area | 7, 8, 15, 16 |
+------------------+--------------------------------------+

Mysql-> CREATE TABLE Employees (
-> ID INT not NULL,
-> fname VARCHAR (30),
-> lname VARCHAR (30),
-> hired DATE not NULL DEFAULT ' 1970-01-01 ',
-> separated DATE not 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)
->);

When you are done, you can enter the MySQL data store file, which is defined in the MySQL configuration file.

Copy Code code 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

The display is as follows:
8768 June 7 22:01 employees.frm
7 June 22:01 Employees.par
0 June 7 22:01 employees#p#pcentral.myd
1024 June 7 22:01 Employees#p#pcentral.myi
0 June 7 22:01 employees#p#peast.myd
1024 June 7 22:01 Employees#p#peast.myi
0 June 7 22:01 employees#p#pnorth.myd
1024 June 7 22:01 Employees#p#pnorth.myi
0 June 7 22:01 employees#p#pwest.myd
1024 June 7 22:01 Employees#p#pwest.myi

As you can see from here, it splits the storage file according to our definition.

Copy Code code as follows:

EMPLOYEES.FRM = Table Structure
Employees.par = partition, declaration is a partitioned table
. myd = Data File
. myi = Index File

HASH Partition

A HASH partition is primarily used to ensure that data is distributed evenly across a predetermined number of partitions
If you want to store employees in different periods, you can hired the date field as a key

Copy Code code as follows:

Mysql-> CREATE TABLE Employees (
-> ID INT not NULL,
-> fname VARCHAR (30),
-> lname VARCHAR (30),
-> hired DATE not NULL DEFAULT ' 1970-01-01 ',
-> separated DATE not 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
->);

#这里注意的是 partitions, one more S.

To mention here, the example above is using the Myisam storage engine, which uses a standalone tablespace, so you can see different partitions on the disk space above
The InnoDB engine uses the shared table space by default, so even if you partition the InnoDB table, you'll see that it doesn't have a physical partition like Myisam, so you'll need to modify the Mysql configuration file:

Copy Code code as follows:

shawn@shawn:~$ sudo vi/etc/mysql/my.cnf;

#添加:
Innodb_file_per_table=1

#重启 MySQL
shawn@shawn:~$ sudo/etc/init.d/mysql Restart

When you partition the INOODB, you will have the following effect:

Copy Code code as follows:

8768 June 7 22:54 employees.frm
7 June 22:54 Employees.par
98304 June 7 22:54 EMPLOYEES#P#PCENTRAL.IBD
98304 June 7 22:54 EMPLOYEES#P#PEAST.IBD
98304 June 7 22:54 EMPLOYEES#P#PNORTH.IBD
98304 June 7 22:54 EMPLOYEES#P#PWEST.IBD

Partition Management

Delete Partition

Copy Code code as follows:

Mysql> ALTER TABLE employees drop partition pwest;

New Partition

Copy Code code as follows:

#range添加新分区
Mysql> ALTER TABLE Employees add partition (partition P4 values less than (26));

#list添加新分区
Mysql> ALTER TABLE Employees add partition (partition Psouth values in (21, 22, 23));

#hash重新分区
Mysql> ALTER TABLE Employees add partition partitions 5;