MySQL大型数据集分区管理优化指南
介绍 在本实验中，你将学习如何为大型数据集配置 MySQL 分区，从而提升查询性能并简化数据管理。本实验主要讲解按范围对数据表进行分区，重点是基于 `sales` 表中的 `sale_date` 列实现按年份分区。 你将首先连接到 MySQL 服务器并创建 `sales_data` 数据库。随
## 介绍
在本实验中，你将学习如何为大型数据集配置 MySQL 分区，从而提升查询性能并简化数据管理。本实验主要讲解按范围对数据表进行分区，重点是基于 `sales` 表中的 `sale_date` 列实现按年份分区。
你将首先连接到 MySQL 服务器并创建 `sales_data` 数据库。随后，创建 `sales` 表，并根据 `sale_date` 的年份设置分区，分别为 2020、2021、2022、2023 年以及未来数据建立对应分区。后续步骤还会介绍如何查询特定分区中的数据、如何使用 `ALTER TABLE` 重组分区，以及如何检查分区对 SQL 查询速度和执行效率的影响。
**注意：** 在本实验中，你只需要在开始时连接一次 MySQL shell，并在最后退出。后续所有 SQL 命令都应在同一个 MySQL 会话中执行。
## 创建分区表
在这一步中，我们将在 MySQL 中创建一个数据库以及一个分区表。分区的作用，是按照指定规则将一张大表拆分为多个更小、更易维护的逻辑部分。这种方式有助于管理大型数据集，并且对于基于分区键筛选数据的查询，通常可以显著提升查询效率。
首先，在 LabEx VM 中打开终端。你应该已经位于 `~/project` 目录下。
以 root 用户连接到 MySQL 服务器（本操作在整个实验开始时只需执行一次）：
```bash
sudo mysql -u root
```
现在你已经进入 MySQL shell。在完成整个实验之前，后续所有 SQL 命令都需要在当前会话中执行。先创建一个名为 `sales_data` 的数据库，用于存放后续实验中的数据表：
```sql
CREATE DATABASE sales_data;
```
然后切换到刚刚创建的数据库：
```sql
USE sales_data;
```
接下来，创建一张名为 `sales` 的表，并按照 `sale_date` 列对应的年份进行分区。这里会分别为 2020、2021、2022、2023 年创建分区，同时再增加一个用于存放未来日期数据的通用分区。
```sql
CREATE TABLE sales (
sale_id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10, 2) NOT NULL,
PRIMARY KEY (sale_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);
```
下面进一步说明 `PARTITION BY RANGE` 这部分的含义：
- `PARTITION BY RANGE (YEAR(sale_date))`：表示这张表会基于 `sale_date` 列经过 `YEAR()` 函数提取出的年份范围进行分区。
- `PARTITION p2020 VALUES LESS THAN (2021)`：创建一个名为 `p2020` 的分区。凡是 `sale_date` 年份小于 2021 的数据行，也就是 2020 年的数据，都会存储到这个分区中。
- `PARTITION p2021 VALUES LESS THAN (2022)`：创建名为 `p2021` 的分区，用于保存 2021 年的数据。
- `PARTITION p2022 VALUES LESS THAN (2023)`：创建名为 `p2022` 的分区，用于保存 2022 年的数据。
- `PARTITION p2023 VALUES LESS THAN (2024)`：创建名为 `p2023` 的分区，用于保存 2023 年的数据。
- `PARTITION pFuture VALUES LESS THAN MAXVALUE`：创建名为 `pFuture` 的分区，用于保存 `sale_date` 年份大于或等于 2024 的所有数据。`MAXVALUE` 是一个特殊值，它始终大于任何其他分区边界值。
执行 `CREATE TABLE` 语句后，你可以使用以下命令检查表结构和分区定义：
```sql
SHOW CREATE TABLE sales;
```
在输出结果中查找 `PARTITION BY RANGE` 子句，确认该表已经按预期创建为分区表。
现在，向 `sales` 表插入一些示例数据。MySQL 会根据 `sale_date` 的值，自动将每一行写入对应的分区。
```sql
INSERT INTO sales (sale_id, sale_date, amount) VALUES
(1, '2020-12-31', 100.00),
(2, '2021-01-15', 150.00),
(3, '2021-12-25', 200.00),
(4, '2022-06-01', 120.00),
(5, '2022-12-31', 180.00),
(6, '2023-03-10', 250.00),
(7, '2023-09-20', 300.00),
(8, '2024-01-01', 350.00);
```
至此，你已经成功创建了一个 MySQL 分区表，并向其中插入了测试数据。下一步中，我们将学习如何查询特定分区中的数据。
## 查询特定分区数据
在这一步中，我们将学习如何通过定位特定分区来高效查询分区表中的数据。这正是 MySQL 分区的重要优势之一，因为数据库只需要扫描相关分区，而不必读取整张表，从而减少处理的数据量并提升查询性能。
**提醒：** 你应该仍然处于 MySQL shell 中，并且当前使用的是 `sales_data` 数据库。如果不是，请执行：
```sql
USE sales_data;
```
要从特定分区中查询数据，你可以编写针对分区键的 `WHERE` 条件。通常情况下，MySQL 查询优化器能够根据 `WHERE` 子句自动判断需要访问哪些分区。
例如，如果要检索 2021 年的所有销售数据，可以使用以下查询。请注意，这里对 `sale_date` 使用的是直接范围条件。在 `WHERE` 子句中直接写 `YEAR(sale_date)` 这样的函数，可能会阻止 MySQL 进行分区修剪（partition pruning），从而导致扫描所有分区。
```sql
SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
```
如果你想查看 MySQL 在执行这条查询时实际访问了哪些分区，可以使用 `EXPLAIN PARTITIONS`：
```sql
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
```
在 `EXPLAIN PARTITIONS` 的输出中，重点查看 `partitions` 列。它应该显示 `p2021`，这说明 MySQL 只扫描了 `p2021` 分区来完成本次查询。
```sql
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales | p2021 | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
```
你也可以查询跨多个分区的数据。例如，获取 2022 年和 2023 年的销售记录：
```sql
SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
```
再次使用 `EXPLAIN PARTITIONS`，你会看到 MySQL 访问的是 `p2022` 和 `p2023` 两个分区：
```sql
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
```
`partitions` 列将显示 `p2022,p2023`。
```sql
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales | p2022,p2023 | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
```
这说明 MySQL 在执行查询时能够自动修剪并排除无关分区，因此在大表场景下，查询速度通常会明显提升，尤其是当全表扫描代价较高时。
如果你想查看每个分区中的大致行数，可以查询 `INFORMATION_SCHEMA.PARTITIONS` 表：
```sql
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
```
这个查询可以帮助你直观了解数据在各个分区之间的分布情况。
```sql
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 1 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 1 |
+----------------+------------+
```
现在，你已经成功完成了特定分区数据的查询，并观察到了 MySQL 如何利用分区机制优化查询执行。
## 重组和管理分区
在这一步中，我们将学习如何使用 `ALTER TABLE` 语句调整现有表的分区结构。这在数据持续增长、业务规则变化或需要重新规划分区策略时非常实用。
**提醒：** 你应该仍然处于 MySQL shell 中并使用 `sales_data` 数据库。如果不是，请执行：
```sql
USE sales_data;
```
假设现在我们要为 2024 年单独增加一个新分区。目前，2024 年及之后的数据都保存在 `pFuture` 分区中。由于 `pFuture` 是通过 `VALUES LESS THAN MAXVALUE` 定义的，并且它必须始终位于最后，因此不能直接使用 `ADD PARTITION` 来新增 2024 年分区。
正确的做法是使用 `REORGANIZE`（重组）`pFuture` 分区，把它拆分为两个新的分区：一个用于 2024 年的 `p2024`，另一个继续作为保存 2025 年及以后数据的新 `pFuture` 分区。
```sql
ALTER TABLE sales REORGANIZE PARTITION pFuture INTO (
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pFuture VALUES LESS THAN MAXVALUE
);
```
这条命令会把原来的 `pFuture` 分区重新组织，将 2024 年的数据移动到新的 `p2024` 分区中，同时重新定义 `pFuture`，使其继续覆盖 2025 年及之后的数据。因此，`sale_date` 为 `'2024-01-01'` 的那一行也会被移入 `p2024`。
下面验证更新后的分区结构和每个分区的行数：
```sql
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
```
你应该能够看到新增的 `p2024` 分区，且 2024 年的数据已经进入该分区。
```sql
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 0 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
```
接下来，演示如何合并分区。假设我们希望把 `p2020` 和 `p2021` 合并成一个新的分区 `p2020_2021`。
```sql
ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
PARTITION p2020_2021 VALUES LESS THAN (2022)
);
```
这条命令会把 `p2020` 和 `p2021` 中的数据重新合并到名为 `p2020_2021` 的新分区中。`VALUES LESS THAN (2022)` 用来定义这个合并后分区的上界。
再次检查分区结构：
```sql
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
```
你将会看到 `p2020` 和 `p2021` 已不再存在，取而代之的是 `p2020_2021`，其中包含合并后的数据行数。
```sql
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
```
最后，我们来删除一个分区。这里可以删除 `p2024` 分区。请注意，删除分区会同时删除该分区中的所有数据。
```sql
ALTER TABLE sales DROP PARTITION p2024;
```
最后再验证一次分区结构：
```sql
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
```
此时，`p2024` 分区应该已经不再显示。
```sql
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 0 |
+----------------+------------+
```
至此，你已经成功使用 `ALTER TABLE` 完成了分区重组、分区合并与分区删除。这也说明了 MySQL 分区表在后续维护和扩展方面具有很强的灵活性。
## 检查分区对查询速度的影响
在这一步中，我们将了解分区对查询性能的影响。虽然当前实验中的数据量很小，但你仍然可以观察到分区修剪（partition pruning）的工作原理：MySQL 只扫描真正需要的分区。对于更大规模的数据表，这种优化效果会更加明显。
**提醒：** 你应该仍然处于 MySQL shell 中并使用 `sales_data` 数据库。如果不是，请执行：
```sql
USE sales_data;
```
为了观察分区带来的性能影响，我们可以使用 `EXPLAIN` 语句查看 SQL 的执行计划。更具体地说，`EXPLAIN PARTITIONS` 可以直接显示查询会访问哪些分区。
先运行一个基于分区键（也就是 `sale_date` 对应年份）进行过滤的查询：
```sql
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
```
查看输出中的 `partitions` 列。正常情况下，它应该显示只扫描了 `p2023` 分区。
接着，再运行一个不是直接基于分区键，而是基于另一个字段 `amount` 进行过滤的查询：
```sql
EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;
```
在这种情况下，由于筛选条件并没有直接作用在分区键 `sale_date` 上，MySQL 可能需要扫描多个分区，甚至扫描全部分区，才能找到符合条件的数据。`EXPLAIN PARTITIONS` 输出中的 `partitions` 列会展示它实际考虑了哪些分区。对于当前这个小型测试数据集，扫描所有分区是很常见的情况。
如果你想更细致地查看查询执行过程和耗时，可以使用 MySQL 的 profiling（剖析）功能。
先启用剖析：
```sql
SET profiling = 1;
```
然后重新执行上面的两条查询：
```sql
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
SELECT * FROM sales WHERE amount > 200;
```
先查看剖析结果：
```sql
SHOW PROFILES;
```
这条命令会列出已经执行过的查询以及各自的耗时。接下来，你可以根据某条语句对应的 `Query_ID`，继续查看更详细的剖析信息：
```sql
SHOW PROFILE FOR QUERY [Query_ID];
```
将 `[Query_ID]` 替换为 `SHOW PROFILES` 输出中你想分析的查询 ID 即可。这样就可以看到该查询在执行过程中经历了哪些阶段，以及每个阶段分别消耗了多少时间。
在当前这个数据集较小的场景下，性能差异可能不会特别明显；但在真实生产环境中，当表中的数据达到数百万行甚至更大规模时，差别通常会非常直观。能够触发分区修剪的查询，比如基于 `sale_date` 范围过滤的查询，通常会比那些需要扫描多个分区或全部分区的查询执行得更快。
最后，记得关闭剖析功能：
```sql
SET profiling = 0;
```
这一步展示了如何借助 `EXPLAIN PARTITIONS` 和 profiling 剖析功能，分析 MySQL 分区对查询执行方式以及整体性能的实际影响。
## 总结
在本实验中，你已经学习了如何在 MySQL 中为大型数据集实现分区，以提升查询性能并优化数据管理。整个流程包括：创建数据库和数据表，并基于日期列的年份进行范围分区；查询指定分区中的数据，观察 MySQL 如何通过分区修剪优化 SQL 查询；使用 `ALTER TABLE` 对分区进行拆分、重组、合并和删除；最后，结合 `EXPLAIN PARTITIONS` 与剖析功能，进一步理解分区对查询速度的影响。
对于 MySQL 大表管理来说，分区是一项非常实用且常见的优化技术，尤其适合按时间范围管理数据、提升查询效率以及降低维护成本。 shell：
```sql
exit;
来源：https://labex.io/zh/tutorials/mysql-mysql-partitioning-for-large-datasets-550912
本站内容用于信息整理与展示，如有侵权或内容问题请及时联系处理。
相关推荐
补充同频道和同主题内容，方便继续浏览更多相关内容。
同类最新
继续查看同栏目最近更新的文章。
Redis是什么：核心特性、架构与应用场景解析
Redis是一款基于内存的键值型NoSQL数据库，以超高读写速度和丰富的数据结构著称。本文系统梳理Redis的核心特性、架构组成、性能优势及典型应用场景，并通过与Memcached、MySQL、MongoDB的对比，帮助开发者快速判断Redis是否适合当前业务需求。
Windows 安装 MongoDB 完整图文教程
本文详细介绍在 Windows 系统上安装 MongoDB 的完整流程。从官网下载 MSI 安装包开始，逐步演示自定义安装路径、配置 Windows 服务、跳过 MongoDB Compass 等关键选项，并提供通过系统服务列表验证安装是否成功的方法，帮助开发者快速搭建本地 MongoDB 环境。
Linux 安装 MongoDB 完整指南：依赖配置、环境变量与服务启动
本文详解在 Linux 系统下安装 MongoDB 的完整流程，涵盖依赖包安装、二进制包下载解压、环境变量配置、数据与日志目录创建及服务启动验证。通过标准化命令与路径说明，帮助开发者快速完成部署并确认服务状态。
MacOS安装MongoDB完整教程
本文介绍在MacOS系统下安装MongoDB的完整流程，涵盖下载、解压、目录配置、环境变量设置及服务启动。通过明确的命令与参数说明，帮助开发者快速完成环境搭建并验证安装结果。
Ubuntu系统安装与配置Redis完整指南
本文详解在Ubuntu系统中安装Redis的两种主流方式：apt在线安装与源码编译安装。涵盖版本选择逻辑、服务启停与状态检查、连接验证方法，以及在线练习工具与桌面GUI客户端的对比与使用建议，帮助开发者快速搭建并验证Redis运行环境。