Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "four Mysql zoning methods and how to achieve combined partition landing". The editor shows you the operation process through an actual case. The operation method is simple, fast and practical. I hope this article "Mysql four zoning methods and how to achieve combined partition landing" can help you solve the problem.

Question 1. What is the partition?

Partition: to store the data of a table in blocks

Objective: to improve the query efficiency of the index

Why should 2.Mysql use Partition

Start with the data analysis.

Then the index is optimized.

Then introduce the partition

Partition principle in 3.Mysql

Client-> Id compares with partition key-> find specified partition-> consistent with database query

Partition limitation in 4.Mysql

You must use the partition field, or the partition query will fail. Go to all sections.

Currently, Range is a range partition, but sometimes we find out. The partition size is always static.

So there will be an uneven size of the partition table. How to balance the size of the partition table?

2. Implement 1.Range partition on the ground

Conditions

Product-Partiton table

Steps

1. Create Product-Partiton-Range first

CREATE TABLE `Range` (`Id` BIGINT (8) NOT NULL, `ProductName` CHAR (245) NOT NULL DEFAULT'1), `ProductId` CHAR (NOT NULL DEFAULT'1), `ProductDescription` CHAR (255) NOT NULL DEFAULT'1, `ProductUrl` CHAR (255) NOT NULL DEFAULT'1, PRIMARY KEY (`Id`), INDEX `ProductId` (`ProductId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY RANGE (Id) PARTITIONS 3 (PARTITION part0 VALUES LESS THAN (12980) PARTITION part1 VALUES LESS THAN (25960), PARTITION part2 VALUES LESS THAN MAXVALUE)

2. Then query the partition table

Select * from product-Partiton-Range where Id = 250002.Hash partition

Steps

1. Create Product-Partiton-Hash first

CREATE TABLE `Hash` (`Id` BIGINT (8) NOT NULL, `ProductName` CHAR (245) NOT NULL DEFAULT'1), `ProductId` (CHAR) NOT NULL DEFAULT'1), `ProductDescription` CHAR (255) NOT NULL DEFAULT'1), `ProductUrl` CHAR (255) NOT NULL DEFAULT'1, PRIMARY KEY (`Id`), INDEX `ProductId` (`ProductId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY HASH (Id) PARTITIONS 3

Hash partitions can only be partitioned by numeric fields, not by character fields. If you need to partition the field values.

Must be included in the primary key field.

3.Key partition

Steps

1. Create Product-Partiton-Key first

CREATE TABLE `product-Partiton- Key` (`Id` BIGINT (8) NOT NULL, `ProductName` CHAR (245) NOT NULL DEFAULT'1), `ProductId` CHAR (245) NOT NULL DEFAULT'1), `ProductDescription` CHAR (255) NOT NULL DEFAULT'1), `ProductUrl` CHAR (255) NOT NULL DEFAULT'1, PRIMARY KEY (`Id`), INDEX `ProductId` (`ProductId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY KEY (ProductName) PARTITIONS 3 # Establishment of compound primary keys CREATE TABLE `Id` BIGINT (8) NOT NULL, `ProductName` CHAR (245) NOT NULL DEFAULT'1), `ProductId` CHAR (255) NOT NULL DEFAULT'1, `ProductDescription` CHAR (255) NOT NULL DEFAULT'1, `ProductUrl` CHAR (255) NOT NULL DEFAULT'1, PRIMARY KEY (`Id`), INDEX `ProductId` (`ProductId`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY KEY (ProductName) PARTITIONS 3

All the above partitions have the same feature: all partitions must be partitioned in continuous and continuous sizes.

Let's look at another scenario: how to partition commodity orders.

How to Landing List Partition in 4.Mysql

Steps

1. Create Product-Partiton-List first

CREATE TABLE `ProductList` (`Id` BIGINT (8) NOT NULL, `ProductName` CHAR (245) NOT NULL DEFAULT'1), `ProductId` CHAR (245) NOT NULL DEFAULT'1), `ProductDescription` CHAR (255) NOT NULL DEFAULT'1), `ProductUrl` CHAR (25) NOT NULL DEFAULT'1), `ProductStatus` int NOT NULL DEFAULT 0, PRIMARY KEY (`Id`), INDEX `ProductId` (`ProductId`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY LIST (ProductId) (PARTITION a VALUES IN (5prime6), PARTITION b VALUES IN (2) 7) 8))

The primary key of the commodity and the name of the commodity are divided into regions.

How to combine Partition on the ground in 5.Mysql

Steps

CREATE TABLE `product-Partiton- flex` (`Id` BIGINT (8) NOT NULL, `ProductName` CHAR (245) NOT NULL DEFAULT'1), `ProductId` CHAR (245) NOT NULL DEFAULT'1, `ProductUrl` CHAR (255) NOT NULL DEFAULT'1, `ProductUrl`ProductUrl`ProductName`, PRIMARY KEY (`Id`, `ProductName`), INDEX `ProductId` (`ProductId`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4PARTITION BY RANGE (Id) PARTITIONS 3SUBPARTITION BY KEY (ProductName) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (12980) PARTITION p1 VALUES LESS THAN (25960), PARTITION p2 VALUES LESS THAN MAXVALUE) Third, how does Mysql manage partition 1. Delete partition ALERT TABLE users DROP PARTITION p0; # delete partition p02. Rebuild 2.1RANGE partition rebuild ALTER TABLE users REORGANIZE PARTITION p0jinp1 INTO (PARTITION p0 VALUES LESS THAN (6000000)); # merge the original p0jinp1 partition and put it into the new p0 partition. 2.2 LIST partition rebuilds ALTER TABLE users REORGANIZE PARTITION p0 INTO p1 INTO (PARTITION p0 VALUES IN); # merges the original p0 score p1 partition into the new p0 partition. 2.3 HASH/KEY partition reconstruction ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; # the number of partitions rebuilt by REORGANIZE has become 2, where the number can only be reduced rather than increased. If you want to add more, you can use the ADD PARTITION method. 3. Add new partition 3.1New RANGE partition # add a RANGE partition ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN) DATA DIRECTORY ='/ data8/data' INDEX DIRECTORY ='/ data9/idx'); 3.2add HASH/KEY partition ALTER TABLE users ADD PARTITION PARTITIONS 8; # expand the total number of partitions to 8. Add alter table results partition by RANGE (month (ttime)) (PARTITION p0 VALUES LESS THAN (1), PARTITION p1 VALUES LESS THAN (2), PARTITION p2 VALUES LESS THAN (3), PARTITION p3 VALUES LESS THAN (4), PARTITION p4 VALUES LESS THAN (5), PARTITION p5 VALUES LESS THAN (6), PARTITION p6 VALUES LESS THAN (7), PARTITION p7 VALUES LESS THAN (8), PARTITION p8 VALUES LESS THAN (9), PARTITION p9 VALUES LESS THAN (10) to existing tables PARTITION p10 VALUES LESS THAN (11), PARTITION p11 VALUES LESS THAN (12), PARTITION P12 VALUES LESS THAN (13)) 4. The default partition restriction partition field must be part of the primary key (PRIMARY KEY) to remove this restriction

[method 1] use ID:

Mysql > ALTER TABLE np_pk-> PARTITION BY HASH (TO_DAYS (added))-> PARTITIONS 4 positive error 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning functionmysql > ALTER TABLE np_pk-> PARTITION BY HASH (id)-> PARTITIONS 4 female query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0

[method 2] remove the original competition and generate new competition

Mysql > alter table results drop PRIMARY KEY;Query OK, 5374850 rows affected (7 min 4.05sec) Records: 5374850 Duplicates: 0 Warnings: 0mysql > alter table results add PRIMARY KEY (id, ttime); Query OK, 5374850 rows affected (7 min 4.05sec) Records: 5374850 Duplicates: 0 Warnings: 0 on "Mysql four zoning methods and how to achieve combined partition landing" content is introduced here, thank you for reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.