MySQL表分区

表分区是将表中的数据按照某种规则（如范围、列表、哈希等）分散存储到多个分区中。每个分区可以独立地进行操作，例如查询、插入、更新和删除。分区的目的是将数据分散到多个存储单元中，从而提高性能和可管理性。

分区的类型

范围分区（RANGE）

根据字段值的范围将数据分配到不同的分区。适用于连续的数值或日期范围。

1
2
3
4
5
6
7
8
9


CREATE TABLE sales (
    id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

列表分区（LIST）

根据字段值的列表将数据分配到不同的分区。适用于离散的值集合。

1
2
3
4
5
6
7
8
9


CREATE TABLE employees (
    id INT,
    department_id INT,
    name VARCHAR(50)
) PARTITION BY LIST (department_id) (
    PARTITION p_sales VALUES IN (1, 2, 3),
    PARTITION p_marketing VALUES IN (4, 5),
    PARTITION p_it VALUES IN (6, 7)
);

哈希分区（HASH）

根据字段值的哈希值将数据分配到不同的分区。适用于均匀分布数据。

1
2
3
4


CREATE TABLE users (
    id INT,
    username VARCHAR(50)
) PARTITION BY HASH (id) PARTITIONS 4;

键分区（KEY）

类似于哈希分区，但使用 MySQL 的内部哈希函数。适用于主键或唯一键的分区。

1
2
3
4
5


CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
) PARTITION BY KEY (order_id) PARTITIONS 4;

分区的优势

提高查询性能

减少扫描范围：查询时，MySQL 只需要扫描相关的分区，而不是整个表，从而减少 I/O 操作。

并行查询：在某些情况下，MySQL 可以并行处理分区查询，进一步提高性能。

简化数据维护

数据清理：可以通过删除分区来快速清理旧数据，而不需要逐条删除记录。

数据备份：可以单独备份或恢复某些分区，而不是整个表。

优化存储管理

分散存储：可以将不同的分区存储在不同的物理设备上，从而优化存储性能。

表空间管理：可以为每个分区指定不同的表空间，便于管理存储空间。

分区的限制

分区键必须是表的主键或唯一键的一部分：如果表有主键或唯一键，分区键必须是这些键的一部分。

分区键不能包含外键：外键不能作为分区键。

分区键必须是数值或日期类型：分区键必须是数值或日期类型，不能是字符串类型。

分区数量有限制：MySQL 对分区数量有限制，通常是 1024 个分区。

分区的维护

添加分区

1

ALTER TABLE sales ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));

删除分区

1

ALTER TABLE sales DROP PARTITION p2022;

合并分区

1

ALTER TABLE sales MERGE PARTITIONS p2022, p2023 INTO PARTITION p2022_2023;

重新组织分区

1
2
3


ALTER TABLE sales REORGANIZE PARTITION p2022, p2023 INTO (
    PARTITION p2022_2023 VALUES LESS THAN (2024)
);

示例：范围分区

假设有一个 sales 表，记录了每年的销售数据。我们可以按年进行范围分区：

1
2
3
4
5
6
7
8
9


CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_date DATE,
    amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

插入数据

1
2
3


INSERT INTO sales (sale_date, amount) VALUES ('2022-01-01', 100.00);
INSERT INTO sales (sale_date, amount) VALUES ('2023-01-01', 200.00);
INSERT INTO sales (sale_date, amount) VALUES ('2024-01-01', 300.00);

查询数据

1

SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

这个查询只会扫描 p2023 分区，而不是整个表。