Detailed explanation of mysql partition function and example analysis

Detailed explanation of mysql partition function and example analysis

First, what is database partitioning?

I wrote an article about MySQL table partitioning some time ago. Now let’s talk about what database partitioning is, taking MySQL as an example. The data in the mysql database is stored on the disk in the form of files, which are placed under /mysql/data by default (you can view it through datadir in my.cnf). A table mainly corresponds to three files, one is frm to store the table structure, one is myd to store the table data, and one is myi to store the table index. If the amount of data in a table is too large, then myd and myi will become very large, and searching for data will become very slow. At this time, we can use the partitioning function of MySQL to physically divide the three files corresponding to this table into many small blocks. In this way, when we look for a piece of data, we don’t need to search all of them. We just need to know which block the data is in and then look for it in that block. If the data in the table is too large to fit on one disk, we can distribute the data to different disks.

Two ways to partition

1. Horizontal partition

What is horizontal partitioning? That is, partitioning is done horizontally. For example, if there are 1 million data items, they are divided into ten parts. The first 100,000 data items are placed in the first partition, the second 100,000 data items are placed in the second partition, and so on. That is to say, the table is divided into ten parts, and merge is used to divide the table, which is a bit similar. When a piece of data is retrieved, it contains all the fields in the table structure, that is, horizontal partitioning does not change the structure of the table.

2. Vertical partition

What is vertical partitioning? That is, partitioning is done vertically. For example, when designing the user table, we did not think it through at the beginning and put all the personal information into one table. In this way, there will be relatively large fields in this table, such as personal profiles, and these profiles may not be read by many people. So when someone wants to read them, they can search for them and separate the large fields when dividing the table.

I feel that database partitioning is like cutting an apple. Should you cut it horizontally or vertically? It depends on your personal preference. The partitioning provided by MySQL belongs to the first type, horizontal partitioning, and is subdivided into many ways. The following example will illustrate this.

Second, mysql partition

I think there is only one way to partition MySQL, it just uses different algorithms and rules to distribute data to different blocks.

1. MySQL 5.1 and above support partitioning function

When installing, we can check

[root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition 
=== Partition Support === 
Plugin Name: partition 
Description: MySQL Partitioning Support 
Supports build: static 
Configurations: max, max-no-ndb

Check it out. If you find the above thing, it means it supports partitioning and is turned on by default. If you have already installed mysql

mysql> show variables like "%part%"; 
+-------------------+------+ 
| Variable_name | Value | 
+-------------------+------+ 
| have_partitioning | YES | 
+-------------------+------+ 
1 row in set (0.00 sec)

Check the variables, if supported, there will be the above prompt.

2. Range partition

A table partitioned by RANGE is partitioned in such a way that each partition contains rows whose values ​​of the partitioning expression lie within a given contiguous interval.

//Create a range partition tablemysql> CREATE TABLE IF NOT EXISTS `user` ( 
 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'User ID', 
 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name', 
 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 for male, 1 for female', 
 -> PRIMARY KEY (`id`) 
 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY RANGE (id) ( 
 -> PARTITION p0 VALUES LESS THAN (3), 
 -> PARTITION p1 VALUES LESS THAN (6), 
 -> PARTITION p2 VALUES LESS THAN (9), 
 -> PARTITION p3 VALUES LESS THAN (12), 
 -> PARTITION p4 VALUES LESS THAN MAXVALUE 
 -> ); 
Query OK, 0 rows affected (0.13 sec) 
 
//Insert some datamysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0') 
 -> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1) 
 -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1) 
 -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1) 
 -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1); 
Query OK, 25 rows affected (0.05 sec) 
Records: 25 Duplicates: 0 Warnings: 0 
 
//Go to the place where the database table files are stored. There is a configuration in my.cnf. After datadir is [root@BlackGhost test]# ls |grep user |xargs du -sh 
4.0K user#P#p0.MYD 
4.0K user#P#p0.MYI 
4.0K user#P#p1.MYD 
4.0K user#P#p1.MYI 
4.0K user#P#p2.MYD 
4.0K user#P#p2.MYI 
4.0K user#P#p3.MYD 
4.0K user#P#p3.MYI 
4.0K user#P#p4.MYD 
4.0K user#P#p4.MYI 
12K user.frm 
4.0K user.par 
 
//Get datamysql> select count(id) as count from user; 
+-------+ 
| count | 
+-------+ 
| 25 | 
+-------+ 
1 row in set (0.00 sec) 
 
//Delete the fourth partitionmysql> alter table user drop partition p4; 
Query OK, 0 rows affected (0.11 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
/**The data stored in the partitions is lost. There are 14 data in the fourth partition and only 11 data in the remaining three partitions. However, the file sizes are all 4.0K. From this we can see that the smallest block size of the partition is 4K 
*/ 
mysql> select count(id) as count from user; 
+-------+ 
| count | 
+-------+ 
| 11 | 
+-------+ 
1 row in set (0.00 sec) 
 
//The fourth block has been deleted [root@BlackGhost test]# ls |grep user |xargs du -sh 
4.0K user#P#p0.MYD 
4.0K user#P#p0.MYI 
4.0K user#P#p1.MYD 
4.0K user#P#p1.MYI 
4.0K user#P#p2.MYD 
4.0K user#P#p2.MYI 
4.0K user#P#p3.MYD 
4.0K user#P#p3.MYI 
12K user.frm 
4.0K user.par 
 
/* You can partition the existing table, and automatically assign the data in the table to the corresponding partition according to the rules. This is better and can save a lot of things. See the following operations*/ 
mysql> alter table aa partition by RANGE(id) 
 -> (PARTITION p1 VALUES less than (1), 
 -> PARTITION p2 VALUES less than (5), 
 -> PARTITION p3 VALUES less than MAXVALUE); 
Query OK, 15 rows affected (0.21 sec) //Partition 15 data Records: 15 Duplicates: 0 Warnings: 0 
 
//There are 15 records in totalmysql> select count(*) from aa; 
+----------+ 
| count(*) | 
+----------+ 
| 15 | 
+----------+ 
1 row in set (0.00 sec) 
 
//Delete a partitionmysql> alter table aa drop partition p2; 
Query OK, 0 rows affected (0.30 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//There are only 11 entries, indicating that the existing table partition is successfulmysql> select count(*) from aa; 
+----------+ 
| count(*) | 
+----------+ 
| 11 | 
+----------+ 
1 row in set (0.00 sec)

3. List partition

The definition and selection of each partition in LIST partitioning is based on the value of a column belonging to a value in a value list set, while RANGE partitioning belongs to a set of continuous interval values.

// This method failsmysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'User ID', 
 -> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name', 
 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 for male, 1 for female', 
 -> PRIMARY KEY (`id`) 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY LIST (province_id) ( 
 -> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), 
 -> PARTITION p1 VALUES IN (9,10,11,12,16,21), 
 -> PARTITION p2 VALUES IN (13,14,15,19), 
 -> PARTITION p3 VALUES IN (17,18,20,22,23,24) 
 -> ); 
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
 
//This method succeedsmysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 -> `id` int(11) NOT NULL COMMENT 'User ID', 
 -> `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 -> `name` varchar(50) NOT NULL DEFAULT '' COMMENT 'Name', 
 -> `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0 for male, 1 for female' 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY LIST (province_id) ( 
 -> PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), 
 -> PARTITION p1 VALUES IN (9,10,11,12,16,21), 
 -> PARTITION p2 VALUES IN (13,14,15,19), 
 -> PARTITION p3 VALUES IN (17,18,20,22,23,24) 
 -> ); 
Query OK, 0 rows affected (0.33 sec)

When creating the list partition above, if there is a primary key, the primary key must be included in the partition, otherwise an error will be reported. If I don't use the primary key, the partition is created successfully. Generally speaking, a table must have a primary key. This is a limitation of the partition.

If you want to test the data, please refer to the range partition test.

4. Hash partition

HASH partitioning is mainly used to ensure that data is evenly distributed among a predetermined number of partitions. All you have to do is specify a column value or expression based on the column value to be hashed, and specify the number of partitions into which the partitioned table will be divided.

mysql> CREATE TABLE IF NOT EXISTS `hash_part` ( 
 -> `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Comment ID', 
 -> `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT 'Comment', 
 -> `ip` varchar(25) NOT NULL DEFAULT '' COMMENT 'Source IP', 
 -> PRIMARY KEY (`id`) 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY HASH(id) 
 -> PARTITIONS 3; 
Query OK, 0 rows affected (0.06 sec)

For testing, please refer to the operation of range partition

5. Key partition

Partitioning by KEY is similar to partitioning by HASH, except that HASH partitioning uses a user-defined expression, while the hash function for KEY partitioning is provided by the MySQL server.

mysql> CREATE TABLE IF NOT EXISTS `key_part` ( 
 -> `news_id` int(11) NOT NULL COMMENT 'News ID', 
 -> `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content', 
 -> `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'Source IP', 
 -> `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time' 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY LINEAR HASH(YEAR(create_time)) 
 -> PARTITIONS 3; 
Query OK, 0 rows affected (0.07 sec)

For testing, please refer to the operation of range partition

6. Subpartition

Subpartitioning is the further division of each partition in the partition table. Subpartitioning can use either HASH partitioning or KEY partitioning. This is also called composite partitioning.

1. If a subpartition is created in a partition, other partitions must also have subpartitions

2. If partitions are created, the number of sub-partitions in each partition must be the same

3. Sub-partitions in the same partition have different names, but sub-partitions in different partitions can have the same name (not applicable to 5.1.50)

mysql> CREATE TABLE IF NOT EXISTS `sub_part` ( 
 -> `news_id` int(11) NOT NULL COMMENT 'News ID', 
 -> `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content', 
 -> `u_id` int(11) NOT NULL DEFAULT 0s COMMENT 'Source IP', 
 -> `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Time' 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY RANGE(YEAR(create_time)) 
 -> SUBPARTITION BY HASH(TO_DAYS(create_time))( 
 -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2), 
 -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good), 
 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3) 
 -> ); 
Query OK, 0 rows affected (0.07 sec)

The official website says that subpartitions in different partitions can have the same name, but MySQL 5.1.50 does not allow this and will prompt the following error:

ERROR 1517 (HY000): Duplicate partition name s1

3. Partition Management

1. Delete the partition

1.mysql> alter table user drop partition p4;

2. Add a new partition

//range add new partitionmysql> alter table user add partition(partition p4 values ​​less than MAXVALUE); 
Query OK, 0 rows affected (0.06 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//list add new partitionmysql> alter table list_part add partition(partition p4 values ​​in (25,26,28)); 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//hash repartitionmysql> alter table hash_part add partition partitions 4; 
Query OK, 0 rows affected (0.12 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//key repartitionmysql> alter table key_part add partition partitions 4; 
Query OK, 1 row affected (0.06 sec) //Some data will be reallocated. Records: 1 Duplicates: 0 Warnings: 0 
 
//Subpartitioning adds a new partition. Although I did not specify a subpartition, the system will name the subpartition. mysql> alter table sub1_part add partition(partition p3 values ​​less than MAXVALUE); 
Query OK, 0 rows affected (0.02 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
mysql> show create table sub1_part\G; 
*************************** 1. row *************************** 
 Table: sub1_part 
Create Table: CREATE TABLE `sub1_part` ( 
 `news_id` int(11) NOT NULL COMMENT 'News ID', 
 `content` varchar(1000) NOT NULL DEFAULT '' COMMENT 'News content', 
 `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT 'Source IP', 
 `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT 'Time' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
!50100 PARTITION BY RANGE (YEAR(create_time)) 
SUBPARTITION BY HASH (TO_DAYS(create_time)) 
(PARTITION p0 VALUES LESS THAN (1990) 
 (SUBPARTITION s0 ENGINE = InnoDB, 
 SUBPARTITION s1 ENGINE = InnoDB, 
 SUBPARTITION s2 ENGINE = InnoDB), 
 PARTITION p1 VALUES LESS THAN (2000) 
 (SUBPARTITION s3 ENGINE = InnoDB, 
 SUBPARTITION s4 ENGINE = InnoDB, 
 SUBPARTITION good ENGINE = InnoDB), 
 PARTITION p2 VALUES LESS THAN (3000) 
 (SUBPARTITION tank0 ENGINE = InnoDB, 
 SUBPARTITION tank1 ENGINE = InnoDB, 
 SUBPARTITION tank3 ENGINE = InnoDB), 
 PARTITION p3 VALUES LESS THAN MAXVALUE 
 (SUBPARTITION p3sp0 ENGINE = InnoDB, //The subpartition name is automatically generated SUBPARTITION p3sp1 ENGINE = InnoDB, 
 SUBPARTITION p3sp2 ENGINE = InnoDB)) 
1 row in set (0.00 sec)

3. Re-partitioning

//range repartitionmysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); 
Query OK, 11 rows affected (0.08 sec) 
Records: 11 Duplicates: 0 Warnings: 0 
 
//list repartitionmysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5)); 
Query OK, 0 rows affected (0.28 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//Hash and key partitions cannot be REORGANIZE, the official website explains it very clearly mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9; 
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1

Fourth, the advantages of partitioning

1. Partitions can be divided into multiple disks for larger storage

2. According to the search conditions, that is, the conditions after where, only the corresponding partitions need not be searched in full.

3. Parallel processing can be performed when searching big data.

4. Spread data queries across multiple disks to achieve greater query throughput

The above detailed explanation of MySQL partition function and example analysis are all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.

You may also be interested in:
  • Detailed introduction to MySQL partitioning technology
  • A detailed introduction to MySQL partition performance
  • Detailed analysis of table partitioning technology in MySQL
  • Summary of MySQL partitioning
  • How to create mysql table partitions
  • A brief analysis of the difference between MySQL table sharding and partitioning

<<:  A comprehensive analysis of what Nginx can do

>>:  Install Python virtual environment in Ubuntu 18.04

Recommend

Several ways to solve the problem of floating causing the height of the parent element to collapse in CSS

In the past, float was often used for layout, but...

A brief analysis of whether using iframe to call a page will cache the page

Recently, I have a project that requires using ifr...

Detailed explanation of Mysql transaction isolation level read commit

View MySQL transaction isolation level mysql> ...

Introduction to the use of MySQL official performance testing tool mysqlslap

Table of contents Introduction Instructions Actua...

How to completely uninstall Docker Toolbox

Docker Toolbox is a solution for installing Docke...

Decompression, installation, backup and restore of MySQL under Windows environment

The system environment is server2012 1. Download ...

Problems encountered when updating the auto-increment primary key id in Mysql

Table of contents Why update the auto-increment i...

Learning about UDP in Linux

Table of contents 1. Introduction to UDP and Linu...

In-depth explanation of MySQL stored procedures (in, out, inout)

1. Introduction It has been supported since versi...

Solution to overflow of html table

If the table is wide, it may overflow. For exampl...

A brief introduction to MySQL database optimization techniques

A mature database architecture is not designed wi...

When Nginx is turned on, the port is occupied and prompts: Address already in use

As shown in the figure: Check port usage: sudo ne...

A brief analysis of the basic concepts of HTML web pages

What is a web page? The page displayed after the ...

Typical cases of MySQL index failure

Table of contents Typical Cases Appendix: Common ...

Definition and function of zoom:1 attribute in CSS

Today I was asked what the zoom attribute in CSS ...