『Mysql』Mysql四种分区方式及组合分区落地实现

您所在的位置：网站首页 › mysql查询分区表sql › 『Mysql』Mysql四种分区方式及组合分区落地实现

『Mysql』Mysql四种分区方式及组合分区落地实现

2024-07-10 02:31| 来源: 网络整理| 查看: 265

请添加图片描述

请添加图片描述 📣读完这篇文章里你能收获到

Mysql分区的概念Mysql分区四种分区方式的落地及案例Mysql分区的管理

请添加图片描述

文章目录一、概念篇1 分区是什么2 Mysql中分区原理3 Mysql中分区局限二、落地篇1 Range分区2 Hash分区3 Key分区4 List分区5 组合分区三、Mysql如何管理分区1 删除分区2 重建分区3 新增分区

请添加图片描述看这篇文章前需要先了解一下以下几个问题~

一、概念篇 1 分区是什么

分区：就是把一张表数据分块存储

目的：提升索引的查询效率

2 Mysql中分区原理 Id 和分区键进行比较找到指定分区和数据库查询一致 3 Mysql中分区局限

必须使用分区字段才行，不然分区查询就会失败。走所有分区

目前Range是范围分区，但是有时候我们会发现。分区大小永远是静态的

请添加图片描述

二、落地篇 1 Range分区

条件

Product-Partiton表

步骤

1、先创建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、然后查询分区表

select * from product-Partiton-Range where Id = 25000 2 Hash分区步骤：先创建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分区只能进行数字字段进行分区，无法进行字符字段进行分区。如果需要对字段值进行分区。必须包含在主键字段内。

3 Key分区步骤：先创建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;

以上分区都是一个特点：所有的分区必须连续和连续大小进行分区。

4 List分区

我们再来看一个场景：如何对商品订单分区。

步骤：先创建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) ); 5 组合分区对商品主键和商品名称进行分区 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 );

请添加图片描述

三、Mysql如何管理分区 1 删除分区 ALERT TABLE users DROP PARTITION p0; #删除分区 p0 2 重建分区 RANGE 分区重建 ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000)); #将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。 LIST 分区重建 ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13)); #将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。 HASH/KEY 分区重建 ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; #用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。 3 新增分区新增 RANGE 分区 #新增一个RANGE分区 ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) DATA DIRECTORY = '/data8/data' INDEX DIRECTORY = '/data9/idx'); 新增 HASH/KEY 分区 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) );

去除限制：默认分区限制分区字段必须是主键（PRIMARY KEY)的一部分

[方法1] 使用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 [方法2] 将原有PK去掉生成新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

请添加图片描述

【本文地址】

『Mysql』Mysql四种分区方式及组合分区落地实现

『Mysql』Mysql四种分区方式及组合分区落地实现

今日新闻

推荐新闻