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



📣读完这篇文章里你能收获到

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

请添加图片描述

文章目录

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

一、概念篇

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
  • 1

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
  • 1
  • 2

2 重建分区

  • RANGE 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));  
#将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
  • 1
  • 2
  • LIST 分区重建
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
#将原来的 p0,p1 分区合并起来,放到新的 p0 分区中。
  • 1
  • 2
  • HASH/KEY 分区重建
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; 
#用 REORGANIZE 方式重建分区的数量变成2,在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。
  • 1
  • 2

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个。
  • 1
  • 给已有的表加上分区
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 去除此限制:默认分区限制分区字段必须是主键(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

相关推荐

  1. Mysql索引实现方式

    2024-03-21 09:26:01       15 阅读
  2. Mysql索引两排序方式分析

    2024-03-21 09:26:01       23 阅读
  3. mysql实现分区

    2024-03-21 09:26:01       35 阅读
  4. MySQL分区

    2024-03-21 09:26:01       29 阅读
  5. MYSQL分区

    2024-03-21 09:26:01       18 阅读
  6. 分布式锁从0到1实现01(mysql/redis/zk)

    2024-03-21 09:26:01       20 阅读
  7. mysql 删除数据的方法

    2024-03-21 09:26:01       18 阅读

最近更新

  1. TCP协议是安全的吗?

    2024-03-21 09:26:01       18 阅读
  2. 阿里云服务器执行yum,一直下载docker-ce-stable失败

    2024-03-21 09:26:01       19 阅读
  3. 【Python教程】压缩PDF文件大小

    2024-03-21 09:26:01       18 阅读
  4. 通过文章id递归查询所有评论(xml)

    2024-03-21 09:26:01       20 阅读

热门阅读

  1. 工作需求ElementUi组件的使用

    2024-03-21 09:26:01       18 阅读
  2. 第二十七章 配置 Web Gateway 的默认参数 - 安全

    2024-03-21 09:26:01       19 阅读
  3. 【GameFramework框架内置模块】11、网络(Network)

    2024-03-21 09:26:01       18 阅读
  4. 在Ubuntu 12.04和CentOS 6上如何添加和删除用户

    2024-03-21 09:26:01       19 阅读
  5. SWIFT环境配置及大模型微调实践

    2024-03-21 09:26:01       18 阅读