Partitioning: It is to store the data of a table in blocks
Purpose: to improve the query efficiency of the index
First start with data analysis
Then perform index optimization
Then introduce partitioning
Client---------> Compare Id and partition key------------->Find the specified partition------ ---->Same as database query
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?
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
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.
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.
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 .
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 );
ALERT TABLE users DROP PARTITION p0; #删除分区 p0
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000)); #将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13)); #将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; #用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
#新增一个RANGE分区 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; #将分区总数扩展到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) );
[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!