在业务系统长期运行过程中，订单、日志类表会产生大量历史数据，当单表数据量超过千万级别时，查询指定月份的历史数据往往需要扫描全表，耗时从几百毫秒增长到数秒甚至更久。通过MySQL的RANGE分区功能按月份拆分表，配合定期归档过期数据，可以大幅提升历史数据的查询效率。
什么是RANGE分区
RANGE分区是MySQL分区类型中的一种，按照某个列的取值范围将数据分配到不同的分区中，每个分区存储一个范围区间的数据。对于按时间维度增长的业务数据，使用时间列的月份作为分区键是非常常见的方案，查询时MySQL会自动过滤掉无关的分区，只扫描目标分区的数据，这就是分区修剪特性。
按月创建RANGE分区表
假设我们有一个订单表order_info，需要存储近2年的订单数据，按create_time列的月份进行分区，建表语句如下：
-- 创建按月RANGE分区的订单表
CREATE TABLE order_info (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL,
-- 其他业务字段
INDEX idx_user_id (user_id),
INDEX idx_create_time (create_time)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(create_time)) (
-- 2024年1月分区
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
-- 2024年2月分区
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
-- 2024年3月分区
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
-- 后续月份分区按同样规则添加
-- 兜底分区，存储超过所有定义范围的数据
PARTITION p_future VALUES LESS THAN MAXVALUE
);
这里使用TO_DAYS(create_time)作为分区表达式，将日期转换为天数，每个分区对应一个自然月的数据。注意分区键create_time需要包含在主键或者唯一索引中，否则建表会报错。
分区表的查询优化效果
当我们查询2024年2月的订单数据时，执行如下SQL：
SELECT * FROM order_info WHERE create_time >= '2024-02-01' AND create_time < '2024-03-01';
MySQL会自动识别到查询条件对应的分区是p202402，只会扫描这个分区的数据，而不会扫描其他月份的分区。如果表总数据量是5000万，单个分区数据量是200万，那么查询扫描的数据量会减少95%以上，查询速度会有明显提升。
我们可以通过EXPLAIN语句查看分区修剪的效果：
EXPLAIN SELECT * FROM order_info WHERE create_time >= '2024-02-01' AND create_time < '2024-03-01';
执行结果中的partitions字段会显示p202402，说明只使用了这一个分区。
历史数据归档实践
对于超过2年的历史订单数据，业务上很少查询，我们可以将其归档到归档表order_info_archive中，减少在线分区表的数据量。归档表的结构和在线表一致，不需要分区：
-- 创建归档表
CREATE TABLE order_info_archive (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
order_no VARCHAR(32) NOT NULL,
user_id BIGINT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
create_time DATETIME NOT NULL,
INDEX idx_user_id (user_id),
INDEX idx_create_time (create_time)
) ENGINE=InnoDB;
归档操作可以按月份批量执行，步骤如下：
- 第一步：将目标月份分区的数据插入到归档表
- 第二步：确认数据插入成功后，删除在线表对应的分区
具体执行SQL如下：
-- 1. 插入2022年1月的数据到归档表 INSERT INTO order_info_archive SELECT * FROM order_info WHERE create_time >= '2022-01-01' AND create_time < '2022-02-01'; -- 2. 确认归档数据量正确后，删除对应分区 ALTER TABLE order_info DROP PARTITION p202201;
注意删除分区操作会直接删除分区内的所有数据，执行前一定要确认数据已经成功归档，避免数据丢失。如果需要保留分区结构，也可以使用ALTER TABLE ... TRUNCATE PARTITION清空分区数据，而不是删除分区。
分区维护注意事项
为了持续发挥分区表的性能优势，需要定期维护分区：
- 每月初提前创建下一个月的分区，避免数据写入兜底分区
p_future - 定期清理过期分区，执行归档操作后删除或者清空对应分区
- 避免在分区键上做函数运算，比如
WHERE DATE(create_time) = '2024-02-01'，会导致分区修剪失效，全表扫描 - 分区数量不建议过多，单表分区数控制在100以内比较合适，过多分区会增加元数据管理开销
适用场景说明
按月RANGE分区适合数据按时间顺序增长、查询多基于时间范围的场景，比如订单表、操作日志表、统计报表表等。如果查询经常跨多个时间范围，或者没有时间维度的查询条件，分区表的优势会不明显，甚至会因为分区管理开销导致性能下降，这类场景不建议使用分区表。