MySQL如何创建hash分区并增加合并分区

本文重点讲解MySQL如何创建hash分区表，以及如何增加、合并以及删除哈希分区，让我们来一起学习下吧！

HASH分区是一种在数据库中将数据按照哈希算法进行分区的方法。它可以将数据均匀地散列到不同的分区中，以实现负载均衡和提高查询效率。

在MySQL中，有两种常见的HASH分区方式：常规HASH和线性HASH。

1. 常规HASH分区（HASH Partitioning）

常规hash是基于分区个数的取模（%）运算。根据余数将数据记录插入到指定的分区。

特点

常规HASH分区通过对整数字段进行哈希算法计算来确定数据所属的分区。
可以使用MySQL内置的函数或者表达式来将非整型字段转换为整数。
在插入数据时，MySQL会根据哈希结果选择对应的分区存储数据。

创建hash分区表示例

CREATE TABLE my_table ( id INT NOT NULL, name VARCHAR(50) ) PARTITION BY HASH(id) PARTITIONS 4;

上述示例创建了一个名为`my_table`的表，并使用`id`字段进行常规HASH分区。根据指定的4个分区，MySQL会根据`id`字段的哈希结果将数据均匀地存储到这四个分区中。

验证分区

我们使用如下SQL验证4个分区情况：

SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = SCHEMA () AND TABLE_NAME = 'my_table';

效果如下图，我们看到4个分区名分别为p0~p3：

插入数据验证

现在我们使用如下SQL插入数据来验证下数据插入情况：

INSERT INTO my_table VALUES(1,'小明'),(2,'小张'),(3,'小王'),(4,'小李');

然后我们查看每个分区插入的都是什么数据，比如这里先查p1分区的：

select * from my_table partition(p1)

结果如图，是id=1的小明，因为1对分区数量4取余正好为1，因此插入p1分区，其他的3个我想大家应该也知道分别插入到了哪几个分区了。

时间类型字段

除了整数直接进行HASH分区，我们也可以针对日期时间类型进行分区，不过也需要事先转换为整数类型，比如下面根据出生日期的年份进行5个hash分区：

CREATE TABLE student ( id INT NOT NULL, birthday DATE NOT NULL DEFAULT '1970-01-01', ) PARTITION BY HASH( YEAR(birthday ) ) PARTITIONS 5;

注意事项

1）常规哈希分区在保证平均性和简便性方面表现出色，通过取模的方式可以让数据非常平均的分布每一个分区，但是由于分区在创建表的时候已经固定了，因此如过频繁进行新增或收缩操作时可能引起大量数据迁移。

2）另外，使用复杂的表达式或函数作为哈希字段可能会影响性能。因此，在选择字段作为哈希键时，应尽量选择简单且能满足需求的字段。

2. 线性HASH分区（LINEAR HASH Partitioning）

mysql线性分区LINEAR HASH和HASH的唯一区别就是PARTITION BY LINEAR HASH

特点

线性HASH分区是MySQL特有的一种分区方式。
它使用一个线性算法来决定数据应该被散列到哪个分区中。
相比于常规HASH分区，线性HASH分区具有更好的负载均衡性能。

创建线性hash分区表示例

假设我们有一个存储用户信息的表，包含用户ID（user_id）、用户名（username）和年龄（age）。我们希望根据用户ID将数据进行线性哈希分区。

CREATE TABLE user_data ( user_id INT, username VARCHAR(50), age INT ) PARTITION BY LINEAR HASH(user_id) PARTITIONS 4;

上述代码中，PARTITION BY LINEAR HASH(user_id)表示根据user_id进行线性哈希分区，并且设置了4个分区。接下来，插入一些示例数据：

INSERT INTO user_data ( user_id, username, age ) VALUES ( 1, '小明', 25 ),( 2, '小张', 30 ),( 3, '小王', 35 ),( 4, '小李', 20 );

当查询特定用户时，可以直接使用相应的user_id进行检索：

SELECT * FROM user_data WHERE user_id = 2;

需要说明的是这将只在对应的分区中进行查找操作，而不会涉及全表扫描。

另外，我们还可以通过explain来查看一下id=2的所在分区：

explain select * from user_data where user_id=2

查询结果如图，由图可知，id=2的记录在p2分区：

3.hash分区管理

hash分区管理主要涉及分区的增加、合并以及移除，以上面的my_table表为例我们一起看下。

增加分区

如下SQL可以新增5个hash分区：

ALTER TABLE my_table add PARTITION partitions 5;

大家可以自己查看下现在分区就变为了p0~p8，一共9个分区了。

合并分区

以下SQL可以减去2个分区，现在分区就也变成了p0~p6，并且数据会根据现有的分区进行了重新的分布。

ALTER TABLE my_table COALESCE PARTITION 2;

移除分区

以下SQL可以移除所有分区，但是需要注意的是使用remove移除分区是仅仅移除分区的定义，并不会删除数据，而drop PARTITION不一样，drop会连同数据一起删除。

ALTER TABLE my_table REMOVE PARTITIONING ;

4.总结

以上就是关于MySQL如何创建hash分区表，以及如何增加、合并以及删除哈希分区的全部内容，希望对你有帮助，学习愉快哦！