Mysql四种分区方式及组合分区落地怎么实现
一、问题1.分区是什么
分区：就是把一张表数据分块存储
目的：提升索引的查询效率
2.Mysql为什么要使用分区先从数据分析
然后进行索引优化
然后引入分区
3.Mysql中分区原理客户端--------->
Id 和分区键进行比较------------->
找到指定分区---------->
和数据库查询一致
必须使用分区字段才行，不然分区查询就会失败。走所有分区。
目前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分区步骤
1、先创建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分区步骤
1、先创建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.Mysql中如何落地List分区步骤
1、先创建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.Mysql中如何落地组合分区步骤
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.重建分区2.1RANGE 分区重建ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
#将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。 2.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 分区中。 2.3 HASH/KEY 分区重建ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
#用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。 3. 新增分区3.1 新增 RANGE 分区#新增一个RANGE分区
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
DATA DIRECTORY = '
/data8/data'
INDEX DIRECTORY = '
/data9/idx'
);
3.2 新增 HASH/KEY 分区ALTER TABLE users ADD PARTITION PARTITIONS 8;
#将分区总数扩展到8个。 3.3 给已有的表加上分区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
Mysql数据库的分区方式在数据处理和优化性能方面起到了至关重要的作用。目前，Mysql一共有四种分区方式，分别是范围分区、列表分区、哈希分区和键值分区。本文将详细介绍这四种分区方式，并探讨如何将它们组合使用，实现更加灵活高效的分区方案。
范围分区与列表分区的优点及实现方法
范围分区是根据某一列的值范围将数据按段分为多个分区，利于数据维护和管理。列表分区是根据某一列的值集合将数据分为多个分区，适合对于离散数据分区。为了实现范围分区和列表分区，需要在创建表时指定分区的依据列，并设置分区规则，如下所示。
CREATE TABLE table_name(
id INT,
name VARCHAR(20)
)
PARTITION BY RANGE(id)(
PARTITION p0 VALUES LESS THAN(100),
PARTITION p1 VALUES LESS THAN(200),
PARTITION p2 VALUES LESS THAN(300)
);
CREATE TABLE table_name(
id INT,
name VARCHAR(20)
)
PARTITION BY LIST(name)(
PARTITION p0 VALUES IN('John', 'Alice'),
PARTITION p1 VALUES IN('Mike', 'Lily'),
PARTITION p2 VALUES IN('Tom', 'Mary')
);
哈希分区与键值分区的特点及实现方式
哈希分区是根据哈希算法将数据分布到多个分区中，既能保证数据平均分布，又能高效地实现数据查询。键值分区是根据某一列作为键值，将数据按照一定规律分配到若干个分区中，适合用于业务场景特别明确的情况。创建哈希分区和键值分区的方法如下：
CREATE TABLE table_name(
id INT,
name VARCHAR(20)
)
PARTITION BY HASH(id)
PARTITIONS 4;
CREATE TABLE table_name(
id INT,
name VARCHAR(20)
)
PARTITION BY KEY(id)
PARTITIONS 4;
如何实现组合分区
在实际应用中，单一的分区方式可能无法满足需求，我们需要将多种分区方式组合使用。组合分区的实现方法有两种，分别是子分区和联合分区。子分区将分区内再次划分为多个子分区，利于更细粒度的数据管理和更好的维护性。联合分区是将多个分区方式联合使用，利用灵活的组合方式，能够更好地应对实际需求。下面是子分区和联合分区的实现方法。
CREATE TABLE table_name(
id INT,
name VARCHAR(20),
created_at DATE
)
PARTITION BY RANGE(YEAR(created_at))
SUBPARTITION BY HASH(id)
SUBPARTITIONS 4(
PARTITION p0 VALUES LESS THAN(2018),
PARTITION p1 VALUES LESS THAN(2019),
PARTITION p2 VALUES LESS THAN(2020)
);
CREATE TABLE table_name(
id INT,
name VARCHAR(20),
created_at DATE
)
PARTITION BY RANGE(YEAR(created_at))
SUBPARTITION BY HASH(MONTH(created_at))
SUBPARTITIONS 12(
PARTITION p0 VALUES LESS THAN(2018),
PARTITION p1 VALUES LESS THAN(2019),
PARTITION p2 VALUES LESS THAN(2020)
);
总结
Mysql的分区方式可以更好地帮我们管理数据，提高数据查询和处理效率。范围分区、列表分区、哈希分区和键值分区各自有其适用场景，我们可以根据具体业务需求选择组合分区方案。通过组合分区的方式，我们可以更加灵活地进行数据管理和维护，提高应用的性能和稳定性。
