MySQL 分区管理大型数据集

介绍

在本实验中，你将学习如何为大型数据集实现 MySQL 分区，以提高查询性能和数据管理。本实验重点关注按范围对表进行分区，特别是使用 sales 表的 sale_date 列。

你将首先连接到 MySQL 服务器并创建一个 sales_data 数据库。然后，你将创建 sales 表，并按 sale_date 的年份将其分区，为 2020、2021、2022、2023 年以及一个未来分区创建分区。后续步骤将涵盖从特定分区查询数据、使用 ALTER TABLE 重组分区以及检查分区对查询速度的影响。

注意： 对于本实验，你只需在开始时连接一次 MySQL shell，并在结束时退出。后续步骤中的所有 SQL 命令都应在同一个 MySQL 会话中执行。

创建分区表

在此步骤中，我们将在 MySQL 中创建一个数据库和一个分区表。分区通过根据指定规则将表划分为更小、更易于管理的部分来帮助管理大型数据集。这可以显著提高查询性能，特别是对于基于分区键过滤数据的查询。

首先，在 LabEx VM 中打开一个终端。你应该已经位于 ~/project 目录中。

以 root 用户连接到 MySQL 服务器（此操作在本实验开始时仅执行一次）：

sudo mysql -u root

你现在已进入 MySQL shell。在完成本实验之前，所有后续 SQL 命令都应在此会话中运行。

让我们创建一个名为 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 使用分区修剪（partition pruning），这会导致它扫描所有分区。

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 分区中。你无法使用 ADD PARTITION 添加新分区，因为 pFuture 分区是用 VALUES LESS THAN MAXVALUE 定义的，它必须始终是最后一个分区。

相反，我们需要 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 重组、合并和删除了分区。这展示了随着数据的发展管理分区表的灵活性。

检查分区对查询速度的影响

在此步骤中，我们将探讨分区如何影响查询性能。虽然我们当前的数据集很小，但我们仍然可以观察到分区修剪（partition pruning）的原理，即 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 列。它应该表明只扫描了 p2023 分区。

现在，让我们运行一个不直接按分区键过滤，而是按另一个列（amount）过滤的查询：

EXPLAIN PARTITIONS SELECT * FROM sales WHERE amount > 200;

在这种情况下，由于查询条件不是直接作用于分区键（sale_date），MySQL 可能需要扫描多个或所有分区来查找匹配的行。EXPLAIN PARTITIONS 输出中的 partitions 列将显示考虑了哪些分区。对于我们的小数据集，它可能仍然会扫描所有分区。

为了更详细地了解查询执行过程和花费的时间，你可以使用 MySQL 的剖析（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;

查看剖析结果：

SHOW PROFILES;

输出将列出已执行的查询及其持续时间。然后，你可以使用特定查询的 Query_ID 来检查其详细信息：

SHOW PROFILE FOR QUERY [Query_ID];

将 [Query_ID] 替换为你想要从 SHOW PROFILES 输出中分析的查询的 ID。查看执行的不同阶段以及每个阶段花费的时间。

虽然在我们的数据集很小的情况下，时间差异可能微不足道，但在拥有数百万行的实际场景中，能够利用分区修剪的查询（例如按 YEAR(sale_date) 过滤的查询）将比需要扫描多个或所有分区的查询快得多。

最后，禁用剖析：

SET profiling = 0;

此步骤演示了如何使用 EXPLAIN PARTITIONS 和剖析来理解分区如何影响查询执行和性能。

总结

在此实验中，你学习了如何为大型数据集实现 MySQL 分区，以提高查询性能和数据管理效率。你首先创建了一个数据库和一个表，该表根据日期列的年份进行范围分区。然后，你练习了从特定分区查询数据，并观察了 MySQL 如何使用分区修剪来优化查询。最后，你学习了如何使用 ALTER TABLE 语句通过添加、拆分和合并分区来重组分区，并探索了如何使用 EXPLAIN PARTITIONS 和剖析来理解分区对查询速度的影响。分区是 MySQL 中高效管理和查询大型表的强大技术。

完成所有步骤后，你可以通过输入以下命令退出 MySQL shell：

exit;