A Brief analysis of Mysql Data Table Partitioning Technology
- 2020-06-19 11:49:25
- OfStack
In this section, look at the partitioning techniques in Mysql (RANGE, LIST, HASH)
The partitioning technique of Mysql is similar to the horizontal partitioning table, but it is a horizontal partitioning table at the logical level. For the application, it is still a table. In other words, the partition does not actually split a table.
There are several partition types in Mysql 5.1(after) :
RANGE partition: Assigns multiple rows to a partition based on column values belonging to a given continuous interval
LIST partitioning: Similar to RANGE partitioning, except that LIST partitioning is based on column values matching a value from a discrete set of values
HASH partitioning: Selects partitions based on the return value of a user-defined expression that evaluates to the column values of the rows to be inserted into the table, and this function can contain any expression that is valid in Mysql and produces non-negative integer values
KEY partitions: They have been partitioned as HASH, but the difference is that the KEY partition only supports one or more columns, and the Mysql server provides its own hash function
Precautions for partitioning:
1, do the partition, or do not define the primary key, or add the partition field to the primary key
2, the partition field cannot be NULL, otherwise how to determine the partition scope, so try NOT NULL
First you can check to see if your version of Mysql supports PARTITION
mysql> show plugins;
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
Or:
mysql> show variables like "%part%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
RANGE partition
Suppose you create a table that holds records of the staff of 20 video stores numbered from 1 to 20. If you want to divide it into four smaller partitions, you can use the RANGE partition to create the following database table:
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 store employees who have left at different times separately, you can use the date field separated as one key. The SQL statement created is 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 located in four distribution areas,
+------------------+--------------------------------------+
| region | Video store ID No. |
+------------------+--------------------------------------+
| North district | 3, 5, 6, 9, 17 |
| east area | 1, 2, 10, 11, 19, 20 |
| west area | 4, 12, 13, 14, 18 |
| Central business district | 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 go to the Mysql data store file, whose location is defined in the Mysql configuration file
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
According to the following :
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.MYIAs you can see here, it splits the storage file according to our definition
employees.frm = Table structure
employees.par = partition, Statement is 1 A partition table
.MYD = The data file
.MYI = Index file
HASH partition
HASH partitions are used primarily to ensure that data is evenly distributed among a predetermined number of partitions
If you want to store employees who joined at different times separately, you can use the date field hired as an key
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
-> );
# The thing to notice here is that PARTITIONS . Much more 1 a sOne thing to note here is that the example above USES the Myisam storage engine, which USES separate tablespaces by default, so you can see the different partitions in the disk space above
The InnoDB engine USES Shared table space by default. Even if you partition the InnoDB table, you will find that it is not physically partitioned like Myisam, so you need to modify the Mysql configuration file:
shawn@Shawn:~$ sudo vi /etc/mysql/my.cnf;
# add :
innodb_file_per_table=1
# restart mysql
shawn@Shawn:~$ sudo /etc/init.d/mysql restartIf you partition InooDB, it will have the following effect:
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.ibdPartition management
Deleted partitions
mysql> show variables like "%part%";
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
The new partition
#range Add a new partition
mysql> alter table employees add partition ( partition p4 values less than (26) );
#list Add a new partition
mysql> alter table employees add partition( partition pSouth values in (21, 22, 23) );
#hash To partition
mysql> alter table employees add partition partitions 5;