Mysql four partitioning methods and how to implement combined partitioning
- WBOYforward
- 2023-05-26 17:55:291803browse
1. Questions
1. What is partitioning
Partitioning: It is to store the data of a table in blocks
Purpose: to improve the query efficiency of the index
2.Why does Mysql use partitioning?
First start with data analysis
Then perform index optimization
Then introduce partitioning
3.Partitioning in Mysql Principle
Client---------> Compare Id and partition key------------->Find the specified partition------ ---->Same as database query
4. Partition limitations in Mysql
You must use the partition field, otherwise the partition query will fail. Walk all zones.
Currently Range is a range partition, but sometimes we will find it. 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. Partition implementation
1.Range partition
Conditions
Product-Partiton table
Steps
1. First create Product-Partiton-Range
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, and then query the partition table
select * from product-Partiton-Range where Id = 25000
2.Hash partition
Steps
1. First create Product-Partiton-Hash
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 partition can only be partitioned by numeric fields, but cannot be partitioned by character fields. If you need to partition the field values.
Must be included in the primary key field.
3.Key Partition
Steps
1. First create Product-Partiton-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; #建立复合主键 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;
The above partitions all have one feature: all partitions must Contiguous and contiguous size partitioning.
Let’s look at another scenario: how to partition product orders.
4. How to implement List partitioning in Mysql
Steps
1. First create Product-Partiton-List
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)
);to partition the product primary key and product name .
5. How to combine partitions in Mysql
Steps
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 to manage partitions in Mysql
1. Delete partitions
ALERT TABLE users DROP PARTITION p0; #删除分区 p0
2. Rebuild partition
2.1RANGE partition rebuild
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000)); #将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
2.2 LIST partition rebuild
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13)); #将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。
2.3 HASH/KEY partition rebuild
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; #用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
3. New Partition
3.1 Add a new RANGE partition
#新增一个RANGE分区
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '/data8/data'
INDEX DIRECTORY = '/data9/idx');3.2 Add a new HASH/KEY partition
ALTER TABLE users ADD PARTITION PARTITIONS 8; #将分区总数扩展到8个。
3.3 Add a partition to an existing table
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 .The default partition restriction is that the 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;
#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 one 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
The above is the detailed content of Mysql four partitioning methods and how to implement combined partitioning. For more information, please follow other related articles on the PHP Chinese website!