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

文
章
目
录

• 1. 常规HASH分区（HASH Partitioning）
• 特点
• 创建hash分区表示例
• 验证分区
• 插入数据验证
• 时间类型字段
•  注意事项
• 2. 线性HASH分区（LINEAR HASH Partitioning）
• 特点
• 创建线性hash分区表示例
• 3.hash分区管理
• 增加分区
• 合并分区
• 移除分区
• 4.总结

本文重点讲解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分区表，以及如何增加、合并以及删除哈希分区的全部内容，希望对你有帮助，学习愉快哦！