一、分区基础概念
1. 什么是分区？
分区是将一个大表物理上分割成多个小表（分区），逻辑上仍是一个完整表。类似书籍分章节存放。
2. 核心价值
- 性能提升：查询只需扫描相关分区
- 管理便捷：可单独备份/恢复分区
- 存储优化：冷热数据分离存储
3. 分区键选择原则
- 必须包含在主键/唯一键中
- 选择高频查询条件列
- 避免选择低区分度列（如性别）
二、分区类型详解
1. RANGE分区（范围分区）
适用场景：数据有自然范围划分（特别是时间）
CREATE TABLE sales (
id INT,
sale_date DATE,
amount DECIMAL(10,2),
PRIMARY KEY(id, sale_date)
) PARTITION BY RANGE(YEAR(sale_date)) (
PARTITION p2020 VALUES LESS THAN(2021),
PARTITION p2021 VALUES LESS THAN(2022),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
特点：
- 明确的范围边界定义
- 适合时间序列数据归档
- 支持高效的范围查询
2. HASH分区（哈希分区）
适用场景：需要均匀分布的无规则数据
CREATE TABLE users (
user_id BIGINT,
username VARCHAR(50),
PRIMARY KEY(user_id)
) PARTITION BY HASH(user_id)
PARTITIONS 4;
特点：
- 数据均匀分布到各分区
- 无法预知具体存储位置
- 分区数建议为2的幂次方
3. LIST分区（列表分区）
适用场景：离散值精确分类
CREATE TABLE products (
product_id INT,
category VARCHAR(20),
PRIMARY KEY(product_id, category)
) PARTITION BY LIST(category) (
PARTITION p_electronics VALUES IN ('phone', 'laptop'),
PARTITION p_clothing VALUES IN ('shirt', 'pants')
);
特点：
- 精确控制数据到指定分区
- 适合枚举类型数据
- 新增值需修改分区定义
三、二级分区高级应用
1. 二级分区概念
在一级分区基础上再进行细分，形成两级分区结构。
2. 最佳实践组合
RANGE+HASH（时间+业务ID）是最常用组合：
CREATE TABLE orders (
order_id BIGINT,
user_id BIGINT,
order_date DATE,
PRIMARY KEY(order_id, order_date, user_id)
)
/* 一级：按日期的RANGE分区 */
PARTITION BY RANGE(TO_DAYS(order_date))
/* 二级：按用户ID的HASH分区 */
SUBPARTITION BY HASH(user_id)
SUBPARTITIONS 8 (
PARTITION p202301 VALUES LESS THAN(TO_DAYS('2023-02-01')),
PARTITION p202302 VALUES LESS THAN(TO_DAYS('2023-03-01'))
);
3. 其他有效组合
- RANGE + LIST：时间+地区
- LIST + HASH：产品类别+供应商
- HASH + RANGE：用户ID+时间（较少用）
四、分区管理操作
1. 添加分区
-- RANGE分区添加
ALTER TABLE sales ADD PARTITION (
PARTITION p2023 VALUES LESS THAN(2024)
);
-- LIST分区添加
ALTER TABLE products ADD PARTITION (
PARTITION p_home VALUES IN ('furniture', 'appliance')
);
2. 合并分区
ALTER TABLE sales REORGANIZE PARTITION p2020,p2021 INTO (
PARTITION p2020_2021 VALUES LESS THAN(2022)
);
3. 删除分区
-- 删除整个分区（包括数据）
ALTER TABLE sales DROP PARTITION p2020;
-- 删除分区数据但保留结构
ALTER TABLE sales TRUNCATE PARTITION p2021;
4. 分区维护监控
-- 查看分区定义
SHOW CREATE TABLE sales;
-- 查看分区数据分布
SELECT
partition_name,
table_rows
FROM information_schema.partitions
WHERE table_name = 'sales';
-- 分析查询命中的分区
EXPLAIN PARTITIONS
SELECT * FROM sales
WHERE sale_date BETWEEN '2021-01-01' AND '2021-12-31';
五、分区设计决策指南
1. 分区键选择原则
- 必须包含在WHERE条件中的列
- 高区分度的列（不同值多）
- 业务增长自然的维度（如时间）
2. 分区数量建议
- 一级分区：通常不超过100个
- 二级子分区：通常4-32个
- 总分区数：不超过1000个
3. 设计检查清单
- 主键是否包含所有分区键？
- 分区键是否有足够的区分度？
- 查询条件是否能利用分区裁剪？
- 分区数量是否在合理范围内？
- 是否有冷热数据分离需求？
六、实战案例集锦
案例1：电商订单系统
CREATE TABLE orders (
order_id BIGINT,
user_id BIGINT,
order_date DATETIME,
merchant_id INT,
PRIMARY KEY(order_id, order_date, user_id)
)
PARTITION BY RANGE(TO_DAYS(order_date))
SUBPARTITION BY HASH(merchant_id)
SUBPARTITIONS 16 (
PARTITION p2023q1 VALUES LESS THAN(TO_DAYS('2023-04-01')),
PARTITION p2023q2 VALUES LESS THAN(TO_DAYS('2023-07-01'))
);
案例2：IoT设备监控
CREATE TABLE iot_metrics (
metric_id BIGINT,
device_type VARCHAR(20),
metric_time DATETIME,
value DOUBLE,
PRIMARY KEY(metric_id, device_type, metric_time)
)
PARTITION BY LIST(device_type)
SUBPARTITION BY RANGE(UNIX_TIMESTAMP(metric_time)) (
PARTITION p_sensor VALUES IN ('temp', 'humidity') (
SUBPARTITION p_sensor_202301 VALUES LESS THAN(UNIX_TIMESTAMP('2023-02-01')),
SUBPARTITION p_sensor_202302 VALUES LESS THAN(UNIX_TIMESTAMP('2023-03-01'))
),
PARTITION p_actuator VALUES IN ('valve', 'switch') (
SUBPARTITION p_actuator_202301 VALUES LESS THAN(UNIX_TIMESTAMP('2023-02-01')),
SUBPARTITION p_actuator_202302 VALUES LESS THAN(UNIX_TIMESTAMP('2023-03-01'))
)
);
七、常见问题解决方案
问题1：分区键不在主键中
错误：
CREATE TABLE invalid_tbl (
id INT PRIMARY KEY,
create_time DATETIME
) PARTITION BY RANGE(TO_DAYS(create_time)) (...);
解决：
CREATE TABLE valid_tbl (
id INT,
create_time DATETIME,
PRIMARY KEY(id, create_time)
) PARTITION BY RANGE(TO_DAYS(create_time)) (...);
问题2：分区数量过多
症状：元数据管理开销大，性能下降
方案：
- 合并小分区
- 减少二级子分区数量
- 考虑按更大时间粒度分区
问题3：数据分布不均
症状：某些分区数据量过大
方案：
- 调整HASH分区数量
- 考虑使用RANGE-LIST组合
- 对热点数据单独分区