关于后端:MySQL分区表详解

本文已收录至 Github，举荐浏览 👉 Java 随想录
微信公众号：Java 随想录

MySQL 数据库中的数据是以文件的局势存在磁盘上的，默认放在 /var/lib/mysql/ 目录上面，咱们能够通过 show variables like '%datadir%'; 命令来查看：

咱们进入到这个目录下，就能够看到咱们定义的所有数据库了，一个数据库就是一个文件夹，一个库中，有其对应的表的信息，如下：

在 MySQL 中，如果存储引擎是 MyISAM，那么在 data 目录下会看到 3 类文件：.frm、.myi、.myd，如下：

*.frm：这个是表定义，是形容表构造的文件。
*.myd：这个是数据信息文件，是表的数据文件。
*.myi：这个是索引信息文件。

如果存储引擎是 InnoDB, 那么在 data 目录下会看到两类文件：.frm、.ibd，如下：

*.frm：表构造文件。
*.ibd：表数据和索引的文件。

无论是哪种存储引擎，只有一张表的数据量过大，就会导致 *.myd、*.myi 以及 *.ibd 文件过大，数据的查找就会变的很慢。

为了解决这个问题，咱们能够利用 MySQL 的分区性能，在物理上将这一张表对应的文件，宰割成许多小块，如此，当咱们查找一条数据时，就不必在某一个文件中进行整个遍历了，咱们只须要晓得这条数据位于哪一个数据块，而后在那一个数据块上查找就行了；另一方面，如果一张表的数据量太大，可能一个磁盘放不下，这个时候，通过表分区咱们就能够把数据调配到不同的磁盘外面去。

艰深地讲表分区是将一大表，依据条件宰割成若干个小表。

如：某用户表的记录超过了 600 万条，那么就能够依据入库日期将表分区，也能够依据所在地将表分区。当然也可依据其余的条件分区。

MySQL 从 5.1 开始增加了对分区的反对，分区的过程是将一个表或索引合成为多个更小、更可治理的局部。对于开发者而言，分区后的表应用形式和不分区基本上还是截然不同，只不过在物理存储上，本来该表只有一个数据文件，当初变成了多个，每个分区都是独立的对象，能够单独解决，也能够作为一个更大对象的一部分进行解决。

须要留神的是，分区性能并不是在存储引擎层实现的，常见的存储引擎如 InnoDB、MyISAM、NDB 等都反对分区。但并不是所有的存储引擎都反对，如 CSV、FEDORATED、MERGE 等就不反对分区，因而在应用此分区性能前，应该对抉择的存储引擎对分区的反对有所理解。

MySQL 分区有长处也有一些毛病，如下：

长处：

查问性能晋升：分区能够将大表划分为更小的局部，查问时只需扫描特定的分区，而不是整个表，从而进步查问性能。特地是在解决大量数据或高并发负载时，分区能够显著缩小查问的响应工夫。
治理和保护的简化：应用分区能够更轻松地治理和保护数据。能够针对特定的分区执行保护操作，如备份、复原、优化和数据清理，而不用解决整个表。这简化了保护工作并缩小了操作的复杂性。
数据管理灵活性：通过分区，能够依据业务需要轻松地增加或删除分区，而无需影响整个表。这使得数据的增长和变动更具弹性，能够依据需要进行动静调整。
改善数据安全性和可用性：能够将不同分区的数据分布在不同的存储设备上，从而进步数据的安全性和可用性。例如，能够将热数据放在高速存储设备上，而将冷数据放在便宜存储设备上，以实现更高的性能和老本效益。

毛病：

复杂性减少：分区引入了额定的复杂性，包含分区策略的抉择、表构造的设计和保护、查问逻辑的调整等。正确地设置和治理分区须要肯定的教训和专业知识。
索引效率降落：对于某些查问，特地是波及跨分区的查问，可能会导致索引效率降落。因为查问须要在多个分区之间进行扫描，可能无奈充分利用索引劣势，从而影响查问性能。
存储空间需要减少：应用分区会导致肯定水平的存储空间节约。每个分区都须要占用肯定的存储空间，包含分区元数据和一些额定的开销。因而，对于分区键的抉择和分区粒度的设置须要衡量存储空间和性能之间的关系。
性能限度：在某些状况下，分区可能会限度某些 MySQL 的性能和个性的应用。例如，某些类型的索引可能无奈在分区表上应用，或者某些 DDL 操作可能须要更简单的解决。

在思考应用分区时，须要综合思考业务需要、查问模式、数据规模和硬件资源等因素，并衡量分区带来的劣势和毛病。对于特定的利用和数据场景，分区可能是一个无效的解决方案，但并不适用于所有状况。

同时分区表也存在一些限度，如下：

限度：

在 mysql5.6.7 之前的版本，一个表最多有 1024 个分区；从 5.6.7 开始，一个表最多能够有 8192 个分区。
分区表无奈应用外键束缚。
NULL 值会使分区过滤有效。
所有分区必须应用雷同的存储引擎。

分区表在以下状况下能够施展其劣势，实用于以下几种应用场景：

大型表处理：当面对十分大的表时，分区表能够进步查问性能。通过将表宰割为更小的分区，查问操作只须要解决特定的分区，从而缩小扫描的数据量，进步查问效率。这在解决日志数据、历史数据或其余须要大量存储和高性能查问的场景中十分有用。
工夫范畴查问：对于按工夫排序的数据，分区表能够依照工夫范畴进行分区，每个分区蕴含特定时间段内的数据。这使得按工夫范畴进行查问变得更高效，例如在某个时间段内检索数据、生成报表或执行时间段的聚合操作。
数据归档和数据保留：分区表可用于数据归档和数据保留的需要。旧数据能够归档到独自的分区中，并将其存储在低成本的存储介质上。同时，能够保留较新数据在高性能的存储介质上，以便疾速查问和操作。
并行查问和负载平衡：通过哈希分区或键分区，能够将数据平均地散布在多个分区中，从而实现并行查问和负载平衡。查问能够同时在多个分区上进行，并在最终合并后果，进步查问性能和零碎吞吐量。
数据删除和保护：应用分区表，能够更轻松地删除或清理不再须要的数据。通过删除整个分区，能够更疾速地删除大量数据，而不会影响整个表的操作。此外，能够针对特定分区执行保护工作，如从新构建索引、备份和优化，以缩小对整个表的影响。

分区表并非实用于所有状况。在抉择应用分区表时，须要综合思考数据量、查问模式、存储资源和硬件能力等因素，并评估分区对性能和治理的影响。

分区有 2 种形式，程度切分和垂直切分。MySQL 数据库反对的分区类型为程度分区，它不反对垂直分区。

此外，MySQL 数据库的分区是部分分区索引，一个分区中既寄存了数据又寄存了索引。而全局分区是指，数据寄存在各个分区中，然而所有数据的索引放在一个对象中。目前，MySQL 数据库还不反对全局分区。

RANGE 分区是 MySQL 中的一种分区策略，依据某一列的范畴值将数据分布到不同的分区。每个分区蕴含特定的范畴。上面是 RANGE 分区的定义形式、特点以及代码示例。

定义形式：

指定分区键：抉择作为分区根据的列作为分区键，通常是日期、数值等具备范畴个性的列。
分区函数：通过 PARTITION BY RANGE 指定应用 RANGE 分区策略。
定义分区范畴：应用 VALUES LESS THAN 子句定义每个分区的范畴。

RANGE 分区的特点：

范畴划分：依据指定列的范畴进行分区，实用于须要按范畴进行查问和治理的状况。
灵便的范畴定义：能够定义任意数量的分区，并且每个分区能够具备不同的范畴。
高效查问：依据查问条件的范畴，MySQL 可能疾速定位到特定的分区，进步查问效率。
动静治理：能够依据业务需要轻松增加或删除分区，适应数据增长或变更的需要。

以下是一个应用 RANGE 分区的代码示例：

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(sales_date)) (PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

在上述示例中，咱们创立了名为 sales 的表，应用 RANGE 分区策略。依据 sales_date 列的年份范畴将数据分布到不同的分区。

PARTITION BY RANGE (YEAR(sales_date))：指定应用 RANGE 分区，基于 sales_date 列的年份进行分区。
PARTITION p1 VALUES LESS THAN (2020)：定义名为 p1 的分区，蕴含年份小于 2020 的数据。
PARTITION p2 VALUES LESS THAN (2021)：定义名为 p2 的分区，蕴含年份小于 2021 的数据。
PARTITION p3 VALUES LESS THAN (2022)：定义名为 p3 的分区，蕴含年份小于 2022 的数据。
PARTITION p4 VALUES LESS THAN MAXVALUE：定义名为 p4 的分区，蕴含超出定义范畴的数据。

RANGE 分区容许依据列值的范畴将数据扩散到不同的分区中，实用于按范畴进行查问和治理的状况。它提供了更灵便的数据管理和查问效率的晋升。

LIST 分区是依据某一列的离散值将数据分布到不同的分区。每个分区蕴含特定的列值列表。上面是 LIST 分区的定义形式、特点以及代码示例。

定义形式：
1. 指定分区键：抉择作为分区根据的列作为分区键，通常是具备离散值的列，如地区、类别等。
2. 分区函数：通过 PARTITION BY LIST 指定应用 LIST 分区策略。
3. 定义分区列表：应用 VALUES IN 子句定义每个分区蕴含的列值列表。
LIST 分区的特点：
1. 列值离散：依据指定列的具体取值进行分区，实用于具备离散值的列。
2. 灵便的分区定义：能够定义任意数量的分区，并且每个分区能够具备不同的列值列表。
3. 高效查问：依据查问条件的列值间接定位到特定分区，进步查问效率。
4. 动静治理：能够依据业务需要轻松增加或删除分区，适应数据增长或变更的需要。
以下是一个应用 LIST 分区的代码示例：
```
CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    region VARCHAR(50)
)
PARTITION BY LIST (region) (PARTITION p_east VALUES IN ('New York', 'Boston'),
    PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco'),
    PARTITION p_other VALUES IN (DEFAULT)
);
```
在上述示例中，咱们创立了名为 users 的表，应用 LIST 分区策略。依据 region 列的具体取值将数据分布到不同的分区。
- PARTITION BY LIST (region)：指定应用 LIST 分区，基于 region 列的值进行分区。
- PARTITION p_east VALUES IN ('New York', 'Boston')：定义名为 p_east 的分区，蕴含值为 ’New York’ 和 ’Boston’ 的 region 列的数据。
- PARTITION p_west VALUES IN ('Los Angeles', 'San Francisco')：定义名为 p_west 的分区，蕴含值为 ’Los Angeles’ 和 ’San Francisco’ 的 region 列的数据。
- PARTITION p_other VALUES IN (DEFAULT)：定义名为 p_other 的分区，蕴含其余 region 列值的数据。

HASH 分区是应用哈希算法将数据平均地散布到多个分区中。上面是 HASH 分区的定义形式、特点以及代码示例。

定义形式：
1. 指定分区键：抉择作为分区根据的列作为分区键。
2. 分区函数：通过 PARTITION BY HASH 指定应用 HASH 分区策略。
3. 定义分区数量：应用 PARTITIONS 关键字指定分区的数量。
HASH 分区的特点：
1. 数据均匀分布：HASH 分区应用哈希算法将数据平均地散布到不同的分区中，确保数据在各个分区之间均衡。
2. 并行查问性能：通过将数据扩散到多个分区，HASH 分区能够进步并行查问的性能，多个查问能够同时在不同分区上执行。
3. 简化治理：HASH 分区使得数据管理更加灵便，能够轻松地增加或删除分区，以适应数据增长或变更的需要。
以下是一个应用 HASH 分区的代码示例：
```
CREATE TABLE sensor_data (
    id INT,
    sensor_name VARCHAR(50),
    value INT
)
PARTITION BY HASH (id)
PARTITIONS 4;
```
在上述示例中，咱们创立了名为 sensor_data 的表，应用 HASH 分区策略。依据 id 列的哈希值将数据分布到 4 个分区中。
- PARTITION BY HASH (id)：指定应用 HASH 分区，基于 id 列的哈希值进行分区。
- PARTITIONS 4：指定创立 4 个分区。

KEY 分区是依据某一列的哈希值将数据分布到不同的分区。不同于 HASH 分区，KEY 分区应用的是列值的哈希值而不是哈希函数。上面是 KEY 分区的定义形式、特点以及代码示例。

定义形式：

指定分区键：抉择作为分区根据的列作为分区键。
分区函数：通过 PARTITION BY KEY 指定应用 KEY 分区策略。
定义分区数量：应用 PARTITIONS 关键字指定分区的数量。

KEY 分区的特点：

哈希散布：KEY 分区应用列值的哈希值将数据分布到不同的分区中，与哈希函数不同，它应用的是列值的哈希值。
高度自定义：KEY 分区容许依据业务需要自定义分区逻辑，能够灵便地抉择分区键和分区数量。
并行查问性能：通过将数据扩散到多个分区，KEY 分区能够进步并行查问的性能，多个查问能够同时在不同分区上执行。
简化治理：KEY 分区使得数据管理更加灵便，能够轻松地增加或删除分区，以适应数据增长或变更的需要。

以下是一个应用 KEY 分区的代码示例：

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    order_date DATE
)
PARTITION BY KEY (customer_id)
PARTITIONS 5;

在上述示例中，咱们创立了名为 orders 的表，应用 KEY 分区策略。依据 customer_id 列的哈希值将数据分布到 5 个分区中。

PARTITION BY KEY (customer_id)：指定应用 KEY 分区，基于 customer_id 列的哈希值进行分区。
PARTITIONS 5：指定创立 5 个分区。

MySQL 在 5.5 版本引入了 COLUMNS 分区类型，其中包含 RANGE COLUMNS 分区和 LIST COLUMNS 分区。以下是对这两种 COLUMNS 分区的具体阐明：

RANGE COLUMNS 分区：RANGE COLUMNS 分区是依据列的范畴值将数据分布到不同的分区的分区策略。它相似于 RANGE 分区，然而依据多个列的范畴值进行分区，而不是只依据一个列。这使得范畴的定义更加灵便，能够基于多个列的组合来进行分区。

上面是一个 RANGE COLUMNS 分区的代码示例：
```
CREATE TABLE sales (
    id INT,
    sales_date DATE,
    region VARCHAR(50),
    amount DECIMAL(10,2)
)
PARTITION BY RANGE COLUMNS(region, sales_date) (PARTITION p1 VALUES LESS THAN ('East', '2022-01-01'),
    PARTITION p2 VALUES LESS THAN ('West', '2022-01-01'),
    PARTITION p3 VALUES LESS THAN ('East', MAXVALUE),
    PARTITION p4 VALUES LESS THAN ('West', MAXVALUE)
);
```
在上述示例中，咱们创立了一个名为 sales 的表，并应用 RANGE COLUMNS 分区策略。依据 region 和 sales_date 两列的范畴将数据分布到不同的分区。每个分区依据这两列的范畴值进行划分。
LIST COLUMNS 分区：LIST COLUMNS 分区是依据列的离散值将数据分布到不同的分区的分区策略。它相似于 LIST 分区，然而依据多个列的离散值进行分区，而不是只依据一个列。这使得离散值的定义更加灵便，能够基于多个列的组合来进行分区。

上面是一个 LIST COLUMNS 分区的代码示例：
```
CREATE TABLE users (
    id INT,
    username VARCHAR(50),
    region VARCHAR(50),
    category VARCHAR(50)
)
PARTITION BY LIST COLUMNS(region, category) (PARTITION p_east VALUES IN (('New York', 'A'), ('Boston', 'B')),
    PARTITION p_west VALUES IN (('Los Angeles', 'C'), ('San Francisco', 'D')),
    PARTITION p_other VALUES IN (DEFAULT)
);
```
在上述示例中，咱们创立了一个名为 users 的表，并应用 LIST COLUMNS 分区策略。依据 region 和 category 两列的离散值将数据分布到不同的分区。每个分区依据这两列的离散值进行划分。

在 MySQL5.6.1 之前能够通过命令 show variables like '%have_partitioning%' 来查看 MySQL 是否反对分区。如果 have_partitioning 的值为 YES，则示意反对分区。

从 MySQL5.6.1 开始，have_partitioning 参数曾经被去掉了，而是用 SHOW PLUGINS 来代替。若有 partition 行且 STATUS 列的值为 ACTIVE，则示意反对分区，如下所示：

CREATE TABLE sales (
    id INT,
    sales_date DATE,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(sales_date)) (PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2021),
    PARTITION p3 VALUES LESS THAN (2022),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

ALTER TABLE sales ADD PARTITION (PARTITION p5 VALUES LESS THAN (2023)
);

ALTER TABLE sales DROP PARTITION p3;

ALTER TABLE sales REORGANIZE PARTITION p1, p2, p5 INTO (PARTITION p1 VALUES LESS THAN (2020),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

ALTER TABLE sales COALESCE PARTITION p1, p2;

ALTER TABLE sales ANALYZE PARTITION p1;

本篇文章就到这里，感激浏览，如果本篇博客有任何谬误和倡议，欢送给我留言斧正。文章继续更新，能够关注公众号第一工夫浏览。

关于后端:MySQL分区表详解

分区表介绍

表分区的优缺点和限度

分区实用场景

分区形式

分区策略

RANGE 分区

LIST 分区

HASH 分区

KEY 分区

COLUMNS 分区

常见分区命令

是否反对分区

创立分区表

向分区表增加新的分区

删除指定的分区

从新组织分区

合并相邻的分区：

剖析指定分区的统计信息：