MySQL - 垂直分区

MySQL - 表分区

在数据库管理中，分区（Partitioning）是一种将大型表分解为更小、更易于管理的部分（称为分区）的技术。虽然这些分区可以单独存储和管理，但数据库仍然将它们视为单个逻辑表。这可以显著提高某些类型查询的性能。

区分两种主要的分区类型很重要：水平分区（Horizontal Partitioning）和垂直分区（Vertical Partitioning）。MySQL 的原生分区功能实现的是水平分区，它根据指定的键将表的行拆分到不同的分区中。而垂直分区则涉及将表的列拆分成多个表，这通常通过数据库设计（如范式化）而不是内置命令来实现。本教程侧重于 MySQL 内置的水平分区功能。

理解 MySQL 的水平分区

MySQL 提供了多种分区类型，例如 RANGE、LIST、HASH 和 KEY。本指南将重点介绍 COLUMNS 分区，它是 RANGE 和 LIST 的一个变体，允许将多个列和更多数据类型用作分区键（partition key）。

分区的主要优势包括：

• 性能提升： 只访问数据子集的查询可以运行得更快，因为数据库只需要扫描相关的分区（这个过程称为“分区裁剪”，partition pruning），而不是整个表。
• 更轻松的管理： 诸如备份、恢复或重建索引等管理任务可以在单个分区上执行，从而减少了超大型表的维护窗口。

RANGE COLUMNS 分区

RANGE COLUMNS 分区根据列值是否落在指定范围内将行分配到不同的分区。这对于具有自然范围的数据（如日期或价格）特别有用。

示例：对电商订单表进行分区

假设一个 orders 表变得非常大。我们可以根据 order_date 对其进行分区，以提高基于日期的报告性能。

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    order_total DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, order_date) -- 分区键必须是主键的一部分
)
PARTITION BY RANGE COLUMNS(order_date) (
    PARTITION p2022 VALUES LESS THAN ('2023-01-01'),
    PARTITION p2023 VALUES LESS THAN ('2024-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

让我们插入一些示例数据：

INSERT INTO orders (customer_id, order_date, order_total) VALUES
(101, '2022-11-15', 150.75),
(102, '2023-03-20', 89.99),
(103, '2023-08-10', 230.00),
(101, '2024-01-05', 45.50);

要查看 MySQL 如何分布这些行，我们可以查询 INFORMATION_SCHEMA.PARTITIONS 表：

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'orders';

输出显示了每个分区的行数：

分区名称	行数
p2022	1
p2023	2
p_future	1

演示分区裁剪

当我们对分区键执行带有 WHERE 子句的查询时，MySQL 会足够智能地仅扫描相关的分区。我们可以使用 EXPLAIN 语句来验证这一点。

EXPLAIN SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';

EXPLAIN 命令的输出将显示一个 partitions 列，表明只访问了 p2023 分区，这证明了性能优势。

LIST COLUMNS 分区

LIST COLUMNS 分区用于根据与一组离散值的匹配来将行分配到分区。它非常适合分类数据，例如产品类别、国家代码或状态。

示例：按区域对用户表进行分区

让我们创建一个 users 表，并根据 region 列对其进行分区。

CREATE TABLE users (
    user_id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    region VARCHAR(10) NOT NULL,
    signup_date DATE,
    PRIMARY KEY (user_id, region)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north_america VALUES IN('USA', 'CAN', 'MEX'),
    PARTITION p_europe VALUES IN('GBR', 'DEU', 'FRA'),
    PARTITION p_asia VALUES IN('JPN', 'IND', 'SGP')
);

现在，让我们插入一些用户：

INSERT INTO users (username, region, signup_date) VALUES
('john_doe', 'USA', '2023-01-15'),
('jane_smith', 'DEU', '2023-02-20'),
('kenji_t', 'JPN', '2023-04-10'),
('maria_g', 'MEX', '2023-05-01');

让我们检查分区状态：

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'users';

输出显示了按区域分区后的用户分布情况：

分区名称	行数
p_north_america	2
p_europe	1
p_asia	1

查询特定分区

与范围分区类似，按 region 过滤的查询将受益于分区裁剪。您也可以显式地查询单个分区，这对于维护任务非常有用。

SELECT * FROM users PARTITION(p_europe);

此查询仅返回来自欧洲分区的用户：

用户 ID	用户名	区域	注册日期
2	jane_smith	DEU	2023-02-20

最佳实践与注意事项

• 分区键选择： 用于分区的列应经常出现在您最常见且对性能要求最高的查询的 WHERE 子句中。
• 主键： 分区键必须是表主键或任何唯一键的一部分。
• 分区过多： 创建过多分区会因增加元数据管理开销而降低性能。对于大多数用例，几十到几百个分区是一个合理的范围。
• 维护： 规划分区维护。例如，在基于时间范围的分区中，您可能需要为未来时间段添加新分区并删除旧分区。