Mysql tuning: implementation of four Mysql partition methods and combined partitions

1, Question

1. What is the partition

Partition: it is to store a table data in blocks

Objective: to improve the query efficiency of the index

2. Why should MySQL use partitions

Start with data analysis

Then index optimization is performed

Then introduce the partition

3. Partition principle in MySQL

Client ----------- > ID and partition key are compared ----------- > the specified partition -------------- found is consistent with the database query

4. Partition limitations in MySQL

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

At present, Range is a Range partition, but sometimes we find it. Partition size is always static.

Therefore, there will be a partition table with uneven size. How to make the partition table size balanced?

2, Partition implementation

1.Range partition

condition

• Product Parton table

step

1. Create the product Parton range first

CREATE TABLE `product-Partiton-Range` ( `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=utf8mb4 PARTITION 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 = 25000

2.Hash partition

step

1. Create product Parton hash first

CREATE TABLE `product-Partiton-Hash` ( `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=utf8mb4 PARTITION BY HASH (Id) PARTITIONS 3;

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

Must be included in the primary key field.

3.Key partition

step

1. Create a product Parton key first

CREATE TABLE `product-Partiton-Key` ( `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=utf8mb4 PARTITION BY KEY (ProductName) PARTITIONS 3; #Create composite primary key CREATE TABLE `product-Partiton-Key` ( `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=utf8mb4 PARTITION BY KEY (ProductName) PARTITIONS 3;

All the above partitions have one feature: all partitions must be partitioned with continuous and continuous sizes.

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

4. How to implement List partition in MySQL

step

1. Create a product Parton list first

CREATE TABLE `product-Partiton-List` ( `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', `ProductStatus` int NOT NULL DEFAULT 0, PRIMARY KEY (`Id`), INDEX `ProductId` (`ProductId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PARTITION BY LIST(ProductId) ( PARTITION a VALUES IN (1,5,6), PARTITION b VALUES IN (2,7,8) );

Product primary key and product name are partitioned.

5. How to implement combined partitions in MySQL

step

CREATE TABLE `product-Partiton-flex` ( `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`,`ProductName`), INDEX `ProductId` (`ProductId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PARTITION BY RANGE (Id) PARTITIONS 3 SUBPARTITION BY KEY(ProductName) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (12980), PARTITION p1 VALUES LESS THAN (25960), PARTITION p2 VALUES LESS THAN MAXVALUE );

3, How does Mysql manage partitions

1. Delete partition

ALERT TABLE users DROP PARTITION p0; #Delete partition p0

2. Re zoning

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000)); #Merge the original p0 and P1 partitions and put them into the new p0 partition.

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13)); #Merge the original p0 and P1 partitions and put them into the new p0 partition.

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; #The number of partitions reconstructed by REORGANIZE becomes 2. Here, the number can only be reduced but not increased. If you want to add, you can use the ADD PARTITION method.

#Add a RANGE partition ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) DATA DIRECTORY = '/data8/data' INDEX DIRECTORY = '/data9/idx');

ALTER TABLE users ADD PARTITION PARTITIONS 8; #Expand the total number of partitions to 8.

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) , PARTITION p10 VALUES LESS THAN (11), PARTITION p11 VALUES LESS THAN (12), PARTITION P12 VALUES LESS THAN (13) );

4. Default partition restriction: the partition field must be part of the primary key. In order to remove this restriction

• [method 1] use ID:

mysql> ALTER TABLE np_pk -> PARTITION BY HASH( TO_DAYS(added) ) -> PARTITIONS 4; #ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function mysql> ALTER TABLE np_pk -> PARTITION BY HASH(id) -> PARTITIONS 4; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0

• [method 2] remove the original PK and generate a new PK

mysql> alter table results drop PRIMARY KEY; Query OK, 5374850 rows affected (7 min 4.05 sec) Records: 5374850 Duplicates: 0 Warnings: 0 mysql> alter table results add PRIMARY KEY(id, ttime); Query OK, 5374850 rows affected (7 min 4.05 sec) Records: 5374850 Duplicates: 0 Warnings: 0

Add new comment

0 comments