SQL 分区：小白进阶的第一扇门

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)
);

上述代码创建了一个 sales 表，按销售日期的年份进行范围分区，将 2022 年的数据存于 p2022 分区，2023 年的数据存于 p2023 分区，2024 年的数据存于 p2024 分区。

2.LIST（列表）分区

假设要创建一个 employees 表，按部门列表进行分区：

CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50)
)
PARTITION BY LIST (department) (
    PARTITION p_sales VALUES IN ('Sales'),
    PARTITION p_marketing VALUES IN ('Marketing'),
    PARTITION p_hr VALUES IN ('HR')
);

此代码创建了一个 employees 表，按部门名称进行列表分区，将销售部门的数据存于 p_sales 分区，市场部门的数据存于 p_marketing 分区，人力资源部门的数据存于 p_hr 分区。

3.HASH（哈希）分区

创建一个 customers 表，按客户 ID 进行哈希分区，划分成 4 个分区：

CREATE TABLE customers (
    id INT,
    name VARCHAR(50)
)
PARTITION BY HASH (id)
PARTITIONS 4;

该代码创建了一个 customers 表，按客户 ID 进行哈希分区，将数据均匀分配到 4 个分区中。

4.KEY（键）分区

假设要创建一个 employees 表，按id进行KEY分区

-- 创建使用键分区的表
CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    hire_date DATE
)
-- 按照 id 字段进行键分区，分为 4 个分区
PARTITION BY KEY (id) PARTITIONS 4;

五、适用场景

大数据量表：单表数据超过千万行，查询效率显著下降。
历史数据清理：定期删除过期分区（如日志保留最近6个月）。
热点数据隔离：将频繁访问的数据独立分区（如最新订单）。

六、基本操作指南

1.添加分区

对于 RANGE 或 LIST 分区，可以使用 ALTER TABLE 语句添加分区：

ALTER TABLE sales ADD PARTITION (
    PARTITION p2022 VALUES LESS THAN (2023)
);

对于 HASH 或 KEY 分区，由于它们是基于哈希函数进行分区的，因此不能直接添加分区，但可以通过重新创建表或调整分区数量来间接实现。

2.删除分区

-- 删除单个分区（仅RANGE/LIST支持）
ALTER TABLE sales DROP PARTITION p2020;

-- 删除所有分区（保留数据）
ALTER TABLE sales REMOVE PARTITIONING;

3. 合并与拆分分区

-- 合并分区
ALTER TABLE sales REORGANIZE PARTITION p2020, p2021 INTO (
    PARTITION p_combined VALUES LESS THAN (2022)
);

-- 拆分分区
ALTER TABLE sales REORGANIZE PARTITION p_max INTO (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

4.查看分区信息

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'sales_range';

七、分区的局限性

1.主键约束：主键必须包含分区键。

2.临时表不支持分区。

-- 错误示例：主键未包含分区键
CREATE TABLE invalid_table (
    id INT PRIMARY KEY,
    sale_date DATE
) PARTITION BY RANGE (YEAR(sale_date)) (...);

-- 正确示例：主键包含分区键
CREATE TABLE valid_table (
    id INT,
    sale_date DATE,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (...);