MySQL分区表设计与使用
约 1812 字大约 6 分钟
mysqlpartition
2025-05-06
分区表(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快几个数量级,适合数据生命周期管理- 分区键必须包含在主键和唯一索引中,这是最常见的限制
- 分区不是万能的,数据量不大或查询不走分区键时,可能适得其反
- 考虑分区表之前,先评估是否可以通过索引优化或分库分表解决
贡献者
更新日志
2026/3/14 13:09
查看所有更新日志
9f6c2-feat: organize wiki content and refresh site setup于