分区表(Partitioned Table)将一张逻辑表的数据按规则分散到多个物理分区中存储。合理使用分区可以显著提升大表的查询性能和管理效率。

分区的核心价值

分区类型

RANGE 分区

按列值的范围分区，最常用的分区方式。

CREATE TABLE orders (
    id BIGINT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    amount DECIMAL(10,2),
    created_at DATE NOT NULL,
    PRIMARY KEY (id, created_at)  -- 分区键必须包含在主键中
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

RANGE COLUMNS：支持多列和非整型列：

CREATE TABLE logs (
    id BIGINT NOT NULL,
    log_date DATE NOT NULL,
    level VARCHAR(10),
    message TEXT,
    PRIMARY KEY (id, log_date)
) PARTITION BY RANGE COLUMNS (log_date) (
    PARTITION p202501 VALUES LESS THAN ('2025-02-01'),
    PARTITION p202502 VALUES LESS THAN ('2025-03-01'),
    PARTITION p202503 VALUES LESS THAN ('2025-04-01')
);

LIST 分区

按列值的离散集合分区。

CREATE TABLE user_data (
    id INT NOT NULL,
    region VARCHAR(20) NOT NULL,
    data TEXT,
    PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS (region) (
    PARTITION p_east VALUES IN ('shanghai', 'hangzhou', 'nanjing'),
    PARTITION p_north VALUES IN ('beijing', 'tianjin', 'dalian'),
    PARTITION p_south VALUES IN ('guangzhou', 'shenzhen', 'xiamen'),
    PARTITION p_west VALUES IN ('chengdu', 'chongqing', 'xian')
);

HASH 分区

按列值的哈希值均匀分布数据。

-- 常规 HASH
CREATE TABLE sessions (
    id BIGINT NOT NULL,
    user_id INT NOT NULL,
    data JSON,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id)
PARTITIONS 8;

-- LINEAR HASH（使用线性 2 的幂算法，增减分区更高效）
CREATE TABLE cache_data (
    id BIGINT NOT NULL,
    cache_key VARCHAR(255) NOT NULL,
    value BLOB,
    PRIMARY KEY (id, cache_key)
) PARTITION BY LINEAR HASH (id)
PARTITIONS 16;

KEY 分区

类似 HASH 分区，但使用 MySQL 内部的哈希函数，支持非整型列。

CREATE TABLE events (
    id BIGINT NOT NULL,
    event_name VARCHAR(100),
    created_at DATETIME,
    PRIMARY KEY (id)
) PARTITION BY KEY (id)
PARTITIONS 8;

-- KEY 分区可以不指定列，默认使用主键
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) PARTITION BY KEY ()
PARTITIONS 4;

子分区 (Sub-partitioning)

在分区基础上再进行一层分区，也称为复合分区。仅支持 RANGE/LIST + HASH/KEY 组合。

CREATE TABLE big_table (
    id BIGINT NOT NULL,
    created_at DATE NOT NULL,
    user_id INT NOT NULL,
    data TEXT,
    PRIMARY KEY (id, created_at, user_id)
) PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH (user_id)
SUBPARTITIONS 4 (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027)
);
-- 总共 3 × 4 = 12 个子分区

分区裁剪 (Partition Pruning)

分区裁剪是分区表性能的核心——查询时优化器自动跳过不包含目标数据的分区。

-- 查看分区裁剪效果
EXPLAIN SELECT * FROM orders WHERE created_at = '2025-06-15';
-- partitions: p2025  ← 只扫描一个分区

EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2024-06-01' AND '2025-03-01';
-- partitions: p2024,p2025  ← 扫描两个分区

-- 无法裁剪的情况
EXPLAIN SELECT * FROM orders WHERE amount > 1000;
-- partitions: p2023,p2024,p2025,p_future  ← 全分区扫描

裁剪生效条件：

• WHERE 条件必须包含分区键
• 分区键上的函数要与分区定义一致
• 不支持对分区键使用不等式与 OR 的复杂组合

分区管理操作

RANGE/LIST 分区管理

-- 添加分区
ALTER TABLE orders ADD PARTITION (
    PARTITION p2026 VALUES LESS THAN (2027)
);

-- 删除分区（数据也会被删除！）
ALTER TABLE orders DROP PARTITION p2023;

-- 合并分区
ALTER TABLE orders REORGANIZE PARTITION p2024, p2025 INTO (
    PARTITION p2024_2025 VALUES LESS THAN (2026)
);

-- 拆分分区
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 清空分区数据（保留分区结构）
ALTER TABLE orders TRUNCATE PARTITION p2023;

HASH/KEY 分区管理

-- 增加分区数量（数据会重新分布）
ALTER TABLE sessions ADD PARTITION PARTITIONS 4;
-- 从 8 变为 12 个分区

-- 减少分区数量
ALTER TABLE sessions COALESCE PARTITION 4;
-- 从 12 变为 8 个分区

交换分区

-- 将分区数据快速移到普通表（用于归档）
CREATE TABLE orders_archive_2023 LIKE orders;
ALTER TABLE orders_archive_2023 REMOVE PARTITIONING;

ALTER TABLE orders EXCHANGE PARTITION p2023 WITH TABLE orders_archive_2023;
-- p2023 变为空分区，数据转移到 orders_archive_2023

分区限制

-- 错误示例：分区键不在主键中
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    created_at DATE
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p1 VALUES LESS THAN (2025)
);
-- ERROR: A PRIMARY KEY must include all columns in the table's partitioning function

-- 正确：分区键包含在主键中
CREATE TABLE t1 (
    id INT,
    created_at DATE,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p1 VALUES LESS THAN (2025),
    PARTITION p2 VALUES LESS THAN (2026)
);

适用场景与性能考量

适用场景

场景	分区策略	说明
时序数据(日志、监控)	RANGE by date	按时间自动归档和清理
多租户数据	LIST by tenant_id	按租户隔离和管理
大表均匀分布	HASH by id	减少热点
按地区分库	LIST by region	数据本地性

不适用场景

• 表数据量不大（< 千万行），分区带来的管理复杂度不值得
• 查询条件不包含分区键，无法利用分区裁剪
• 需要跨分区事务的高并发场景
• 需要外键约束的场景

性能对比

-- 测试分区裁剪效果
-- 假设 orders 表有 5000 万行，按年分区

-- 有分区裁剪
SELECT COUNT(*) FROM orders WHERE created_at = '2025-06-15';
-- 扫描 ~1000 万行(一个分区)

-- 无分区裁剪
SELECT COUNT(*) FROM orders WHERE amount > 1000;
-- 扫描 5000 万行(全部分区)

分区表与索引

每个分区有独立的索引结构。查询跨多个分区时，需要分别在各分区的索引中查找后合并结果。

-- 查看分区信息
SELECT
    TABLE_NAME,
    PARTITION_NAME,
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

-- 独立分析分区统计信息
ALTER TABLE orders ANALYZE PARTITION p2025;

-- 独立优化分区
ALTER TABLE orders OPTIMIZE PARTITION p2024;

-- 独立检查分区
ALTER TABLE orders CHECK PARTITION p2025;

总结

• RANGE 分区是最常用的类型，特别适合时间序列数据
• 分区裁剪是性能提升的关键，查询条件必须包含分区键
• DROP PARTITION 比 DELETE 快几个数量级，适合数据生命周期管理
• 分区键必须包含在主键和唯一索引中，这是最常见的限制
• 分区不是万能的，数据量不大或查询不走分区键时，可能适得其反
• 考虑分区表之前，先评估是否可以通过索引优化或分库分表解决

贡献者

withesse

更新日志

2026/3/14 13:09

查看所有更新日志

• 9f6c2-feat: organize wiki content and refresh site setup于 2026/3/14

编辑此页