MySQL如何通过分区表提升历史数据查询速度_按月RANGE分区与归档

林译川

2026-10-22 • 👁 0人浏览过

RANGE分区按月查历史数据高效，因MySQL可跳过不匹配分区避免全表扫描；需查询条件含分区键且不用函数包裹，建表用TO_DAYS()转换日期，分区字段须为主键/唯一索引一部分。为什么 RANGE 分区对按月查历史数据有效因为 MySQL 在查询时能直接跳过不匹配的分区，避免全表扫描。比如

WHERE create_time >= '2023-04-01'

，只要分区键是

create_time

且按月 RANGE 划分，优化器就会只访问 2023 年 4 月及之后的分区，其他几十个旧分区完全不读。但前提是：查询条件必须包含分区键，且不能用函数包裹（如

YEAR(create_time)

或

DATE_FORMAT(create_time, '%Y-%m')

会失效）。常见错误现象：建了分区，

EXPLAIN

却显示

type: ALL

，

partitions: NULL

加了

ORDER BY create_time DESC LIMIT 20

，结果变慢——因为跨分区排序要合并所有匹配分区的结果怎么写按月 RANGE 分区的 CREATE TABLE 核心是用

TO_DAYS()

转换日期为整数，避免手动维护大量日期字面量；同时预留未来分区并定期添加，否则插入超出范围的数据会报错

ERROR 1526 (HY000): Table has no partition for value

。实操建议：分区字段必须是主键/唯一索引的一部分（MySQL 5.7+ 要求），所以如果主键是

id

，就得改成联合主键

(id, create_time)

或把

create_time

加进唯一索引用

TO_DAYS('2024-01-01')

而不是

UNIX_TIMESTAMP()

，后者在跨时区或夏令时下可能出错初始建表至少覆盖未来 3 个月，例如当前是 2024-04，就建到 2024-07 简短示例：

CREATE TABLE order_history (
  id BIGINT NOT NULL,
  create_time DATE NOT NULL,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
  PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
  PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
  PARTITION p202404 VALUES LESS THAN (TO_DAYS('2024-05-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

归档旧分区不能只靠 DROP PARTITION

DROP PARTITION

确实快，但它会永久删掉数据，且无法回滚。生产环境更常见的是“迁移 + 清理”：先把旧分区数据导出，再用

ALTER TABLE ... REORGANIZE PARTITION

或

TRUNCATE PARTITION

清空。使用场景：归档 2022 年全年数据 → 先

SELECT ... INTO OUTFILE

导出，再

ALTER TABLE order_history TRUNCATE PARTITION p202201,p202202,...

想把某个月数据迁到冷备库 → 用

mysqldump --where="create_time >= '2022-01-01' AND create_time ，比全表 dump 快得多

容易踩的坑：

TRUNCATE PARTITION

会重置自增 ID（如果该分区含自增列），但不影响其他分区分区表不支持

OPTIMIZE TABLE

全局操作，只能对单个分区执行：

OPTIMIZE PARTITION p202301

从库上分区定义必须和主库一致，否则复制可能中断分区后查询变慢？先看执行计划是否真的用了分区裁剪最常被忽略的是隐式类型转换：如果

create_time

是

DATE

类型，但查询里传了字符串

'2023-04-01 00:00:00'

，MySQL 可能放弃分区裁剪，转而全分区扫描。验证方法只有两个：执行

EXPLAIN PARTITIONS SELECT ...

，确认

partitions

列只列出预期的几个分区名开启

performance_schema

，查

events_statements_summary_by_digest

中的

digest_text

是否含

PARTITION

关键字性能影响点：分区数不宜过多（一般不超过 64 个），否则打开分区文件、合并结果集的开销会上升每个分区仍是独立的 B+ 树，所以单个分区内的索引效率和普通表一致，别指望“分区=自动索引优化” 复杂点在于：分区键的选择和业务查询模式必须严格对齐。比如按月分区，却总查“最近 90 天”，那就会跨 3–4 个分区，排序、聚合压力反而比单表大。

相关文章