MySQL - 水平分区

MySQL - 水平分区

分区（Partitioning）是一种高级数据库技术，用于将非常大的表分解成更小、更易于管理的片段，称为分区（partitions）。虽然从逻辑上讲，表仍然是一个单一的实体，但其数据在物理上存储在这些独立的分区中。这可以显著提高性能和可管理性。

MySQL 支持两种主要的分区类型：水平分区（Horizontal Partitioning）和垂直分区（Vertical Partitioning）。本教程重点介绍水平分区，其中表的行根据分区键（partitioning key）被划分到不同的分区中。

为什么要使用水平分区？

*   **性能**：仅访问数据子集的查询可以运行得更快。如果查询的 `WHERE` 子句与分区方案匹配，数据库引擎可以只扫描相关分区，而不是整个表。这称为**分区剪裁**（partition pruning）。
*   **可管理性**：诸如备份、恢复或删除旧数据等管理任务可以在单个分区上执行。例如，要删除时间序列表中特定年份的所有数据，只需删除相应的分区，这比运行 `DELETE` 查询快得多。
*   **存储**：您可以将不同的分区分配到不同的物理存储设备上，从而优化 I/O。

RANGE 分区

RANGE 分区根据列的值是否落在给定连续范围内将行分配到分区。它非常适合具有清晰范围的数据，例如日期或价格。

示例：按年份分区

让我们创建一个 Sales 表，并根据 order_date 的年份对其进行分区。这对于大型时间序列（time-series）表来说是一个非常常见的用例。

CREATE TABLE Sales (
    order_id INT AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, order_date) -- 分区键必须是主键的一部分
)
PARTITION BY RANGE(YEAR(order_date)) (
    PARTITION p_2021 VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_catchall VALUES LESS THAN MAXVALUE -- 最佳实践: 一个兜底分区
);

现在，让我们插入一些数据。MySQL 将自动把每一行放置到正确的（对应）分区中。

INSERT INTO Sales (product_name, order_date, amount) VALUES
    ('Laptop', '2021-11-15', 1200.00),
    ('Mouse', '2022-01-20', 25.00),
    ('Keyboard', '2022-07-30', 75.00),
    ('Monitor', '2023-03-10', 300.00),
    ('Docking Station', '2024-02-05', 150.00);

要查看查询如何从分区中受益，我们可以使用 EXPLAIN。

EXPLAIN SELECT * FROM Sales WHERE order_date >= '2022-01-01' AND order_date < '2023-01-01';

EXPLAIN 语句的输出将显示一个 partitions 列，表明 MySQL 只扫描了 p_2022 分区，而不是整个表。

LIST 分区

LIST 分区根据列的值是否匹配一组离散值中的一个来将行分配到分区。它非常适合分类数据（categorical data），例如国家代码、部门 ID 或状态码。

示例：按地区分区

让我们创建一个按 region（地区）分区的 Users 表。

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    region ENUM('North America', 'Europe', 'Asia', 'Other') NOT NULL
)
PARTITION BY LIST(region) (
    PARTITION p_na VALUES IN ('North America'),
    PARTITION p_eu VALUES IN ('Europe'),
    PARTITION p_asia VALUES IN ('Asia'),
    PARTITION p_other VALUES IN ('Other')
);

插入数据将根据 region 将行路由到正确的（对应）分区。

INSERT INTO Users (username, region) VALUES
    ('john_doe', 'North America'),
    ('jane_smith', 'Europe'),
    ('akira', 'Asia');

查询欧洲用户的语句将只扫描 p_eu 分区。

SELECT * FROM Users WHERE region = 'Europe';

HASH 和 KEY 分区

HASH 和 KEY 分区用于确保数据在指定数量的分区之间均匀分布。您无需定义明确的值范围；相反，MySQL 会对列值使用哈希函数（hashing function）来确定分区。

• HASH 分区：分区表达式必须返回一个整数。分区通过 MOD(expression, num_partitions) 完成。
• KEY 分区：与 HASH 类似，但 MySQL 提供其自己的内部哈希函数。它允许对非整数列（如字符串或日期）进行分区，并且如果未指定列，则默认可以使用表的主键。

示例：按用户 ID 进行 KEY 分区

当没有明显的 RANGE 或 LIST 业务逻辑，但您仍希望拆分大型表以获得更好的性能时，这会很有用。

CREATE TABLE Sessions (
    session_id VARCHAR(128) PRIMARY KEY,
    user_id INT NOT NULL,
    login_time DATETIME NOT NULL
)
PARTITION BY KEY(user_id)
PARTITIONS 8; -- 将数据分散到 8 个分区中

在这种情况下，给定 user_id 的所有会话数据都将位于同一分区中，这对于按 user_id 过滤的查询非常高效。

子分区

子分区（Sub-partitioning，也称为复合分区 composite partitioning）涉及通过第二种分区方法进一步划分每个分区。例如，您可以按日期范围（RANGE）对表进行分区，然后通过客户 ID 的哈希（HASH）对每个分区进行子分区。

示例：RANGE-HASH 子分区

CREATE TABLE Orders (
    order_id INT NOT NULL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    PRIMARY KEY (order_id, order_date, customer_id)
)
PARTITION BY RANGE(YEAR(order_date))
SUBPARTITION BY HASH(customer_id)
SUBPARTITIONS 4 (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

这将创建总共 3 * 4 = 12 个分区。像 SELECT * FROM Orders WHERE order_date = '2023-05-20' AND customer_id = 123; 这样的查询将极其快速，因为 MySQL 可以首先剪裁到 p2023 分区，然后再次剪裁到 customer_id = 123 所在的确切子分区。

管理分区

您可以使用 ALTER TABLE 语句管理分区。

*   **删除分区**：`ALTER TABLE Sales DROP PARTITION p_2021;` （立即删除 2021 年的所有数据）。
*   **添加分区**：`ALTER TABLE Sales ADD PARTITION (PARTITION p_2024 VALUES LESS THAN (2025));` （仅适用于 `RANGE` 和 `LIST`）。
*   **截断分区**：`ALTER TABLE Users TRUNCATE PARTITION p_asia;` （删除分区中的所有数据，但保留分区本身）。
*   **检查分区状态**：`SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'Sales';`