MySQL 分区表实战：千万行日志表怎么删、怎么归档才不锁库

个人站长迟早会遇到这么一张表：访问日志、订单流水、埋点数据，跑个一两年轻轻松松上千万行。查询越来越慢、备份越来越久还算轻的，真正要命的是清理历史数据。你写一句 DELETE FROM logs WHERE created_at < '2025-01-01'，跑了半小时，把 InnoDB 的 undo 撑爆、主从延迟暴涨，线上接口开始超时——这就是没做分区的代价。

MySQL 的表分区正是为这种「按时间维度管理海量数据」的场景设计的。它让清理历史数据从「DELETE 几百万行」变成「秒级 DROP 一个分区」，本文讲清楚 RANGE 分区的建法、按时间归档的完整流程、以及分区表的那些查询限制。

一、分区到底是什么

分区（Partitioning）是在逻辑上还是一张表、物理上却是多个文件的机制。以 RANGE 按时间分区为例，MySQL 会根据分区键把每一行分配到某个分区里，每个分区在磁盘上对应独立的 .ibd 文件。

它带来两个直接好处：

• 分区裁剪（Partition Pruning）：查询带上分区键条件时，MySQL 只扫描命中的分区，不碰其他分区。查「本月日志」只读本月那个分区文件，速度几乎与表大小无关。
• 秒级清理：删历史数据只要 ALTER TABLE ... DROP PARTITION，直接删文件，不用逐行删除、不产生大量 undo、几乎不影响主从延迟。

需要先破除一个误区：分区不是索引的替代品。分区键如果不进查询条件，分区裁剪就不生效，你依然要扫全部数据。所以分区键的选择必须和你的主要查询条件对齐。

二、建一张按月分区的日志表

MySQL 8.0 中，分区键必须是主键或唯一键的一部分。这是个高频报错点：如果你直接对 id 为主键、按 created_at 分区的表建分区，会报 ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function。解法是让主键同时包含分区键，如下：

CREATE TABLE access_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  ip VARCHAR(45) NOT NULL,
  uri VARCHAR(512) NOT NULL,
  status SMALLINT UNSIGNED NOT NULL,
  cost_ms INT UNSIGNED NOT NULL,
  PRIMARY KEY (id, created_at),
  KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION p202603 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

逐点说明：

1. TO_DAYS(created_at) 把日期转成一个整数，RANGE 要求分区边界是整数才能高效比较。LESS THAN (TO_DAYS('2026-02-01')) 意味着 p202601 装的就是 2026 年 1 月的全部数据。
2. 最后一个 pmax VALUES LESS THAN (MAXVALUE) 是兜底分区。强烈建议保留它：如果哪天自动建分区的脚本挂了，新数据会落进 pmax 而不是插入失败。真实生产里，插入失败往往比「数据落错分区」更可怕。
3. 分区键必须是主键一部分，所以主键写成 (id, created_at)。这不影响 id 单列的查询（最左前缀依然命中主键），但如果你的业务里有 WHERE id = ? 之外的唯一约束，需要一并调整。

三、自动维护：让新月份的分区自己长出来

按月分区最烦的是「每个月都要手动建下个月的分区」。写个定时任务自动扩展，思路是：找到当前 pmax 的边界，往后加一个分区，并 DROP 掉最老的分区。下面是存储过程的核心逻辑：

-- 新增下个月分区
DELIMITER $$
CREATE PROCEDURE add_next_partition()
BEGIN
  DECLARE next_month DATE;
  SET next_month = DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
  SET next_month = DATE(DATE_FORMAT(next_month, '%Y-%m-01'));

  -- 先拆掉 pmax，再加新分区，最后把 pmax 加回来
  ALTER TABLE access_logs REORGANIZE PARTITION pmax INTO (
    PARTITION p_next VALUES LESS THAN (TO_DAYS(DATE_ADD(next_month, INTERVAL 1 MONTH))),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
  );
END$$
DELIMITER ;

CALL add_next_partition();

注意这里用的是 REORGANIZE PARTITION pmax 而不是直接 ADD PARTITION——因为已经有 MAXVALUE 分区了，你不能在它后面再加分区，只能把 MAXVALUE 拆开。这一步会重建 pmax 里的所有数据，如果 pmax 里堆了很多行会很慢，所以兜底分区要定期清空（下面讲）。

清理老分区的语句就极其廉价了：

-- 秒级删除 2026 年 1 月的全部数据
ALTER TABLE access_logs DROP PARTITION p202601;

想归档而不是直接删，可以先把分区数据导出到文件（配合 SELECT ... INTO OUTFILE 或 mysqldump --where），再从库里 DROP。归档到别处、留个冷备，比直接删安全。

四、查询时怎么确认裁剪生效了

不要凭感觉以为分区一定被裁剪了，用 EXPLAIN 看 partitions 列：

EXPLAIN SELECT COUNT(*) FROM access_logs
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';

如果结果里 partitions 列只显示 p202603，说明裁剪生效。如果显示 p202601,p202602,p202603,pmax，说明 MySQL 没有裁剪——这时候要么是查询条件没用上分区键，要么是分区键表达式（TO_DAYS(created_at)）导致优化器无法推断，需要把查询写成对分区列的直接区间判断。

实测经验：WHERE created_at BETWEEN '2026-03-01' AND '2026-03-31' 这种对原始列的区间条件，裁剪效果最好，不要对列套函数（比如 WHERE DATE(created_at) = '2026-03-05'，函数一挥，裁剪就废了）。

五、分区表不能做的事

分区虽然好用，但限制不少，踩坑前先知道：

• 不能是外键的一部分。InnoDB 分区表不支持外键，涉及外键约束的表没法直接分区。
• 全表唯一索引必须包含分区键。想在某列上建全局唯一索引？它必须带上分区键，否则建不了。
• 分区数别太多。有人说「那我按天分区」，一年 365 个分区，MySQL 打开和元数据管理的开销会变大。按月或按周是常见平衡点；单表分区数建议控制在几百以内。
• 分区表对 FULLTEXT 索引、SPATIAL 索引支持有限。要做全文检索的日志表，先想清楚。
• 所有分区必须同一引擎、同一字符集，不能混用。

六、迁移已有大表：不停机转成分区表

更多的情况是：表已经存在、已经有几千万行，你不想重建。把普通表改成分区表，标准做法是「原地 CONVERT」：

-- 前提：表结构本身满足分区要求（主键包含分区键）
ALTER TABLE access_logs
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202601 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p202602 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

⚠️ 这条 DDL 会重建整张表、复制全部数据，是重操作。上千万行的表可能跑几十分钟甚至更久，期间原表会被加锁。对线上可用的表，正确姿势是用 pt-online-schema-change（Percona Toolkit）或 MySQL 8.0 的 ALGORITHM=INPLACE 配合分批，先建一张同结构的分区空表，用触发器把新数据双写，再分批把老数据迁移过去，最后 rename 切换。这套流程较重，量不大的话，选一个访问低谷期直接 CONVERT 反而更简单可靠。

迁移前务必做两件事：先备份（mysqldump 或物理备份都行），以及先在从库或测试库演练一遍，摸清耗时。别拿生产表当试验田。

七、分区的备份与恢复注意事项

分区表在备份上和普通表有个容易忽略的差异：用 mysqldump 逻辑备份时，默认导出的是一张完整表的 INSERT 语句，不会保留分区定义。恢复回来是一张巨大的普通表，白折腾。要保留分区结构，得用 SHOW CREATE TABLE 单独导出建表语句，或者用物理备份工具（Percona XtraBackup）做文件级备份。

而分区表在「单分区恢复」上又有独特优势——假如只有上个月的数据损坏，你可以单独把那个分区的 .ibd 文件从备份里拷回来，用 ALTER TABLE ... IMPORT PARTITION 挂回去（需要该分区是独立表空间）。这是普通表做不到的粒度。所以生产上我习惯把分区表配物理备份，逻辑备份只用于异地冷备。

八、按周分区还是按月分区

分区粒度怎么定，取决于两个因素：单分区数据量、以及你要保留多久。经验值：

• 按月分区：适合每月数据量在 500 万行以内、保留 12～24 个月的场景。前面日志表的例子就是这种，一个月一个分区，清理时按月 DROP，粒度粗但管理简单。
• 按周分区：适合每天写入几十万行、月增千万级的高频表。按周分区能让单个分区更小、裁剪更精确，清理也更灵活（保留最近 8 周）。代价是分区数量变成月度的四倍多，一年 52 个分区要留意别让总数失控。
• 按天分区：不到万不得已别用。除非你日增数据量本身就极大，否则 365 个分区带来的元数据开销和 DDL 复杂度不划算。

我自己站点的访问日志表用的是按月分区配一个 MAXVALUE 兜底，保留 13 个月，每个月 1 号跑一个存储过程自动建下月分区、DROP 掉最老的。三年下来从没因为清理数据影响过线上。

九、跟其他方案比，什么时候该上分区

分区不是万能的，我按数据规模给你一个决策参考：

对绝大多数个人站长，日志表跑到几千万行时，RANGE 分区就是性价比最高的答案：不用引入新组件、不用改业务代码、一句 DDL 就能清理历史。它土，但它真的省事。真到了分区都扛不住的量级，那已经不是你一个人维护得了的规模了，该上专门的时序数据库了。