MySQL数据表分区

一、什么是数据表分区

MySQL中的分区表是一种数据库设计技术，它将一个大表物理地分割成多个较小的部分，这些部分被称为分区。虽然从逻辑上看，分区表仍然像一个单独的表，但在物理层面，每个分区都是存储在一个独立的文件上，可以位于同一台服务器的不同硬盘上，或者跨多台服务器分布，以提高查询性能和管理效率。

二、使用数据表分区的优点

1）与单个磁盘或文件系统分区相比，可以存储更多的数据。
2）对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。
相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
同样的，你可以很快的通过删除分区来移除旧数据。你还可以优化、检查、修复个别分区。
3）一些查询可以得到极大的优化。 可以把一些归类的数据放在一个分区中，可以减少服务器检查数据的数量加快查询。
这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。
PS：因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
4）涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进行并行处理。
这种查询的一个简单例子如

“SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id；”。 

通过“并行”，这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
5）通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。

三、MySQL分区的方式

MySQL 支持多种分区方式，包括 RANGE、LIST、HASH、KEY 等方式

3.1 RANGE分区：

根据分区键的范围进行划分，每个分区包含一个范围内的值。例如，可以将时间列作为 RANGE 分区键，将数据按照年、月、日等时间段进行划分。创建 RANGE 分区表的方法如下：

CREATE TABLE mytable(
    id INT,
    created_at DATE
)
PARTITION BY RANGE (YEAR(created_at))(

    PARTITION P0 VALUES LESS THAN(2021)
    PARTITION P1 VALUES LESS THAN(2022)
    PARTITION p2 VALUES LESS THAN(2023)
    PARTITION P3 VALUES LESS THAN(2024)
    PARTITION P4 VALUES LESS THAN MAXVALUE
);

2.LIST 分区：根据分区键的列表进行划分，每个分区包含一个列表内的值。例如，可以将国家列作为 LIST 分区键，将数据按照不同的国家进行划分。创建 LIST 分区表的方法如下：

 

CREATE TABLE mytable1(
    id INT,
    hobby varchar(20)
)
PARTITION BY LIST (hobby)(

    PARTITION P0 VALUES IN  ('唱','跳','rap','打篮球')
    PARTITION P1 VALUES IN  ('看书','看电影')
    PARTITION p2 VALUES IN  ('跑步','游泳')
    PARTITION P3 VALUES IN  ('听歌','画画')
    PARTITION P4 VALUES IN  ('打游戏','上网')
);

3. HASH 分区：根据分区键的哈希值进行划分，每个分区包含哈希值相同的记录。例如，可以将用户 ID 列作为 HASH 分区键，将数据按照用户 ID 进行划分。创建 HASH 分区表的方法如下

CREATE TABLE mytable2(
    id INT,
    user_id varchar(20)
)
PARTITION BY HASH (user_id) PARTITION  5;

4.KEY 分区：根据分区键的值的哈希值进行划分，与 HASH 分区类似，但只针对非唯一索引。例如，可以将用户姓名作为 KEY 分区键，将数据按照姓名的哈希值进行划分。创建 KEY 分区表的方法如下 :

CREATE TABLE mytable2(
    id INT,
    user_name varchar(20),
    INDEX(user_name)
)
PARTITION BY KEY (user_name) PARTITION  5;

以上是分区表的创建方法示例，具体的分区键和分区数量可以根据实际情况进行调整。需要注意的是，分区表的使用需要根据实际情况进行优化和调整，以充分发挥分区的优势

如果已经创建了分区表并且需要扩展分区，可以使用 ALTER TABLE 命令进行操作。下面是几个常见的扩展分区的操作：

1.增加新分区：可以通过 ALTER TABLE ADD PARTITION 命令增加新的分区。例如，假设已经有一个 RANGE 分区表，包含了 2010 年到 2020 年的数据，现在需要增加一个 2021 年的分区，可以执行如下命令:

ALTER TABLE mytable ADD PARTITION (
    PARTITION  P5 VALUE LESS THEN (2025)
)

2.合并分区：可以通过 ALTER TABLE COALESCE PARTITION 命令将相邻的分区合并成一个分区。例如，假设 RANGE 分区表中的 2019 年和 2020 年分区中的数据已经非常少了，可以将它们合并成一个分区，执行如下命令：

ALTER TABLE mytable COALESCE PARTITION P0,P1

3.重新分区：可以通过 ALTER TABLE REORGANIZE PARTITION 命令重新划分分区，将数据重新分配到不同的分区中。例如，假设已经有一个 HASH 分区表，现在需要将分区数量增加到 8，可以执行如下命令：

ALTER TABLE mytable REORGENIZE PARTITION P0,P1,P2,P3,P4 INTO (

    PARTITION P0 HASH KEY(id) PARTITIONS 2,
    PARTITION P1 HASH KEY(id) PARTITIONS 2,
    PARTITION P2 HASH KEY(id) PARTITIONS 2,
    PARTITION P3 HASH KEY(id) PARTITIONS 2    
);

在进行分区扩展操作时，需要注意一些细节问题，比如对于包含数据的分区不能直接删除，需要先将数据转移或删除。此外，在进行分区操作时需要谨慎，可以先在测试环境进行测试，以免出现不可预料的问题。

4.移动分区：可以通过 ALTER TABLE REORGANIZE PARTITION 命令移动分区中的数据到新的分区中。例如，假设已经有一个 RANGE 分区表，包含了 2010 年到 2020 年的数据，现在需要将 2020 年的数据移动到一个新的分区中，可以执行如下命令:

ALTER TABLE mytable REORGANIZE PARTITION P4 INTO(
    PARTITION P4 VALUES LESS THEN (2021)
    PARTITION P5 VALUES LESS THEN (2022)
) 

5.分离分区：可以通过 ALTER TABLE DROP PARTITION 命令分离某个分区，从而将分区从表中删除。例如，假设已经有一个 RANGE 分区表，包含了 2010 年到 2020 年的数据，现在需要将 2010 年的数据从表中删除，可以执行如下命令 

ALTER TABLE mytable DROP PARTITION P1;

什么量级的表需要用到分区:
一般来说，需要考虑使用分区的数量级别是千万级别甚至更高的数据量。具体来说，以下是一些考虑使用分区的场景：

       数据量大：如果表中数据量很大，特别是超过了百万条，那么使用分区可以加快数据的查询和维护速度。
       频繁的查询操作：如果表中的数据经常需要被查询和过滤，而且查询条件往往和某些列有关，那么使用分区可以将数据按照这些列进行分组，提高查询效率。
       数据访问的分布式：如果表的数据需要在多个地方访问，例如分布式系统中的多个节点，那么使用分区可以减少数据传输的开销，提高访问速度。

需要注意的是，这些场景只是一般情况下使用分区的建议，具体是否需要使用分区还需要根据具体的情况来判断。如果数据量较小或者查询操作较少，使用分区反而会增加系统的复杂度，降低性能

原文链接：https://blog.csdn.net/qq_43077857/article/details/129148071