简介
在本实验中，你将学习如何为大型数据集实施 MySQL 分区，以提升查询性能并改进数据管理。本实验重点介绍如何按范围对表进行分区，具体来说，是根据 sales 表中的 sale_date 列进行分区。
首先，连接到 MySQL 服务器并创建 sales_data 数据库。接着，创建 sales 表，并根据 sale_date 的年份将数据分到 2020、2021、2022、2023 年对应的分区，以及一个用于存放未来日期数据的分区。之后，你将学习如何查询指定分区中的数据、使用 ALTER TABLE 重新组织分区，以及检查分区对查询速度的影响。
注意： 本实验只需在开始时进入一次 MySQL shell，并在结束时退出。后续步骤中的所有 SQL 命令都应在同一个 MySQL 会话中执行。
创建分区表
本步骤将在 MySQL 中创建数据库和分区表。分区会根据指定规则将表拆分成更小、更易管理的部分，帮助管理大型数据集。对于按分区键筛选数据的查询，分区还可以显著提升查询性能。
首先，在 LabEx 虚拟机中打开终端。你应该已经位于 ~/project 目录中。
以 root 用户身份连接到 MySQL 服务器（本实验开始时只需执行一次）：
sudo mysql -u root
现在你已进入 MySQL shell。在完成本实验之前，后续所有 SQL 命令都应在当前会话中执行。
启用会话记录，以便步骤检查程序读取当前会话中输入的 SQL 命令。只要 MySQL 会话保持打开，会话记录就会持续启用：
tee /home/labex/project/partitioning-session.log
在完成本实验之前，请保持会话记录启用。在这里输入的 SQL 命令不会记录到终端 shell 历史记录中。
创建名为 sales_data 的数据库，用于存放数据表：
CREATE DATABASE sales_data;
切换到刚创建的数据库：
USE sales_data;
现在，创建名为 sales 的表，并根据 sale_date 列的年份进行分区。我们将为 2020、2021、2022 和 2023 年分别创建分区，再创建一个用于存放未来日期数据的分区。
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 语句后，运行以下命令查看表结构和分区设置：
SHOW CREATE TABLE sales;
检查输出中的 PARTITION BY RANGE 子句，确认表已按照指定方式创建分区。
现在向 sales 表插入一些示例数据。MySQL 会根据每行的 sale_date 自动将其放入相应分区。
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 shell 中，并且正在使用 sales_data 数据库。如果不是，请运行：
USE sales_data;
要查询指定分区中的数据，可以使用 WHERE 子句筛选分区键。通常，MySQL 的查询优化器可以根据 WHERE 子句判断哪些分区与查询相关。
例如，要查询 2021 年的所有销售记录，可以运行以下语句。注意，这里直接对 sale_date 使用范围条件。在 WHERE 子句中使用 YEAR(sale_date) 之类的函数可能会妨碍 MySQL 执行分区裁剪，导致它扫描所有分区。
SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
要查看 MySQL 为此查询访问了哪些分区，可以使用 EXPLAIN PARTITIONS 语句：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2021-01-01' AND sale_date < '2022-01-01';
查看 EXPLAIN PARTITIONS 的输出时，关注 partitions 列。该列应显示 p2021，表示 MySQL 只扫描 p2021 分区来完成查询。
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 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 年的销售数据：
SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
再次使用 EXPLAIN PARTITIONS，可以查看 MySQL 是否访问了 p2022 和 p2023 两个分区：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2022-01-01' AND sale_date < '2024-01-01';
partitions 列应显示 p2022,p2023。
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | sales | p2022,p2023 | ALL | PRIMARY | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+---------------+------+---------------+------+---------+------+------+-------------+
这说明，执行查询时，分区可以让 MySQL 裁剪（排除）无关分区，从而更快地返回结果。对于扫描整张表会耗费大量时间的大型表，这种效果尤其明显。
要查看每个分区中的行数，可以查询 INFORMATION_SCHEMA.PARTITIONS 表：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
查询结果可以清楚地显示数据在各个分区中的分布情况。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 1 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 1 |
+----------------+------------+
现在你已成功查询指定分区中的数据，并观察到 MySQL 如何利用分区优化查询。
重新组织和管理分区
本步骤将介绍如何使用 ALTER TABLE 语句修改现有表的分区结构。随着数据增长或需求变化，你可以通过这种方式调整分区方案。
提醒： 你应该仍在 MySQL shell 中，并且正在使用 sales_data 数据库。如果不是，请运行：
USE sales_data;
假设我们要为 2024 年添加一个新分区。目前，2024 年及之后的数据都存放在 pFuture 分区中。由于 pFuture 分区使用 VALUES LESS THAN MAXVALUE 定义，而它必须始终是最后一个分区，因此不能使用 ADD PARTITION 添加新分区。
此时需要使用 REORGANIZE 拆分 pFuture 分区。我们将把 pFuture 拆分成两个新分区：一个存放 2024 年的数据（p2024），另一个新的 pFuture 分区存放此后的数据。
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。
运行以下查询，检查更新后的分区结构和行数：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
你应该能看到新建的 p2024 分区。2024 年的数据现在位于 p2024 中。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020 | 0 |
| p2021 | 2 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
接下来，合并分区。假设我们要将 p2020 和 p2021 合并为一个名为 p2020_2021 的分区。
ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
PARTITION p2020_2021 VALUES LESS THAN (2022)
);
此命令会将 p2020 和 p2021 中的数据合并到名为 p2020_2021 的新分区中。VALUES LESS THAN (2022) 子句定义了合并后分区的新边界。
再次检查分区结构：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
你会看到 p2020 和 p2021 已被移除，p2020_2021 则包含合并后的行数。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| p2024 | 0 |
| pFuture | 0 |
+----------------+------------+
最后，删除一个分区。我们可以删除 p2024。请注意，删除分区也会删除该分区中的所有数据。
ALTER TABLE sales DROP PARTITION p2024;
最后一次检查分区结构：
SELECT
PARTITION_NAME,
TABLE_ROWS
FROM
INFORMATION_SCHEMA.PARTITIONS
WHERE
TABLE_SCHEMA = 'sales_data' AND TABLE_NAME = 'sales';
输出中不应再列出 p2024 分区。
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p2020_2021 | 3 |
| p2022 | 2 |
| p2023 | 2 |
| pFuture | 0 |
+----------------+------------+
现在你已成功使用 ALTER TABLE 重新组织、合并和删除分区。这展示了如何随着数据变化灵活管理分区表。
检查分区对查询速度的影响
本步骤将介绍分区对查询性能的影响。虽然当前数据集很小，但你仍然可以观察分区裁剪的原理：MySQL 只扫描必要的分区。对于更大的数据集，这种效果会明显得多。
提醒： 你应该仍在 MySQL shell 中，并且正在使用 sales_data 数据库。如果不是，请运行：
USE sales_data;
要观察分区对查询的影响，可以使用 EXPLAIN 语句查看查询的执行计划。具体来说，EXPLAIN PARTITIONS 会显示查询访问了哪些分区。
运行以下查询，按分区键（sale_date 的年份）筛选数据：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
查看输出中的 partitions 列。该列应显示 MySQL 只扫描 p2023 分区。
接下来，运行一个不直接按分区键筛选、而是按其他列（amount）筛选的查询：
EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;
由于查询条件没有直接使用分区键（sale_date），MySQL 可能需要扫描多个分区或所有分区，才能找到符合条件的行。EXPLAIN PARTITIONS 输出中的 partitions 列会显示查询检查了哪些分区。由于当前数据集较小，MySQL 仍可能扫描所有分区。
你还可以使用 MySQL 的 profiling 功能，进一步查看查询执行过程和各阶段耗时。
启用 profiling：
SET profiling = 1;
现在再次运行这两个查询：
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';
SELECT * FROM sales WHERE amount > 200;
查看 profiling 结果：
SHOW PROFILES;
输出会列出已执行的查询及其耗时。你可以使用对应的 Query_ID 查看某个查询的详细信息：
SHOW PROFILE FOR QUERY [Query_ID];
将 [Query_ID] 替换为 SHOW PROFILES 输出中你要分析的查询 ID。查看查询执行的不同阶段以及各阶段耗时。
当前数据集很小，因此查询耗时的差异可能微乎其微。但在有数百万行数据的实际场景中，能够利用分区裁剪的查询（例如按 YEAR(sale_date) 筛选的查询）会明显快于需要扫描多个或所有分区的查询。
最后，关闭 profiling：
SET profiling = 0;
本步骤演示了如何使用 EXPLAIN PARTITIONS 和 profiling 了解分区对查询执行和性能的影响。
总结
在本实验中，你学习了如何为大型数据集实施 MySQL 分区，以提升查询性能并改进数据管理。你先创建数据库和表，并根据日期列的年份对表进行范围分区。接着，你练习了如何查询指定分区中的数据，并观察 MySQL 如何使用分区裁剪优化查询。最后，你学习了如何使用 ALTER TABLE 添加、拆分和合并分区，并了解了如何通过 EXPLAIN PARTITIONS 和 profiling 检查分区对查询速度的影响。分区是 MySQL 高效管理和查询大型表的有力方法。
完成所有步骤后，输入以下命令退出 MySQL shell：
exit;
