MySQL表分区：两千万行日志表的按月切割

  1.  Home
  2. Text  
    3. Share to：

MySQL表分区：把两千万行日志表按天切开

草根站长最典型的数据库困境，是一个当初只为「记录一下」而建的表，两三年之后涨到几千万行，删不动、查不快、备份还越来越慢。访问日志表、点击统计表、爬虫记录表，几乎都是这个形态。

常见的两条路：一是加索引，二是定期 DELETE 老数据。前者救不了「总量太大」的问题，后者更糟——大批量 DELETE 会长时间持锁、撑爆 undo、主从延迟飙升，我们有一次在凌晨跑了一个 DELETE FROM access_log WHERE created_at < '2024-01-01'，删了四十分钟，期间站点所有写入排队，前台开始 502。

这篇文章讲第三条路：按时间分区，用 DROP PARTITION 代替 DELETE。它不是银弹，但在这个具体场景下几乎是完美解——删除变成毫秒级的元数据操作,查询能命中分区裁剪，备份也能按分区做。

一、先确认这条路适不适合你

MySQL 分区（这里指 InnoDB 的 RANGE / LIST 分区，不是分库分表）有几个硬限制，先对上再动手：

  • 分区键必须包含在所有唯一索引里。这是最大的坑：如果表的主键是 id AUTO_INCREMENT，唯一索引里没有 created_at，那么 PARTITION BY RANGE 直接用 created_at 会报 ERROR 1503: A PRIMARY KEY must include all columns in the table's partitioning function。解决办法是把主键改成 (id, created_at) 复合主键。
  • 不支持外键。日志表一般没有，有的话要先删。
  • 分区数量建议控制在 100~200 以内。每个分区在内存里都有元数据和打开的表对象；一位数以内的分区没有意义，上千个分区会让 information_schema 和表打开都变慢。
  • MySQL 8.0 支持 HASH/RANGE/LIST + 子分区；5.7 只有部分组合。我们是 8.0，5.7 的差异在最后一段讲。

判断标准很实在：这张表有没有「按时间整体淘汰」的需求？有没有「按时间段查询」的需求？两个都满足，分区才划算。如果只是「查询慢」，那应该先做索引和 SQL 优化。

二、表结构与分区定义

下面是我们的访问日志表，两千万行左右，每天新增约 12 万行：

CREATE TABLE access_log (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at  DATETIME NOT NULL,
  ip          VARBINARY(16) NOT NULL,
  uri         VARCHAR(255) NOT NULL,
  status      SMALLINT UNSIGNED NOT NULL,
  cost_ms     SMALLINT UNSIGNED NOT NULL,
  ua_hash     BINARY(8) DEFAULT NULL,
  PRIMARY KEY (id, created_at),
  KEY idx_time_status (created_at, status),
  KEY idx_uri_time (uri(64), created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(created_at) (
  PARTITION p202608 VALUES LESS THAN ('2026-09-01'),
  PARTITION p202609 VALUES LESS THAN ('2026-10-01'),
  PARTITION p202610 VALUES LESS THAN ('2026-11-01'),
  PARTITION p202611 VALUES LESS THAN ('2026-12-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

几个设计要点：

用 RANGE COLUMNS 而不是 RANGE。RANGE(TO_DAYS(created_at)) 是老写法，能用但要包函数，可读性差，而且在做分区裁剪的时候，WHERE created_at >= '2026-10-01' 这种条件有时无法直接映射到分区表达式。RANGE COLUMNS(created_at) 直接用日期字符串做边界，裁剪判定更直接。

一定要有 pmax 这个兜底分区。没有它，一旦写入超出最后一个分区边界，会直接报 ERROR 1526: Table has no partition for value，所有写入全挂。用 MAXVALUE 兜住，然后再定期把 pmax 拆开。

按月而不是按天。按天分区一年就是 365 个，接近上限且元数据开销明显。按月 12 个/年，保留 18 个月才 18 个分区，非常好管理。如果单月数据量特别大（比如上亿行），再考虑按天。

主键是 (id, created_at) 而不是只有 id。这是分区的硬性要求，代价是 id 不再全局唯一（实际上 AUTO_INCREMENT 仍然全局唯一，只是索引层面允许重复），以及按 id 单列查询时会走不了主键索引。如果业务里大量按 id 查，需要额外建 KEY(id)。

三、已有大表怎么改造（在线，不锁表）

直接 ALTER TABLE ... PARTITION BY 会重建整张表，两千万行大概要十几分钟到半小时，期间表可读但不可写（8.0 的在线 DDL 对分区操作支持有限）。我们的做法是建新表 + 分批迁数据 + 原子改名：

-- 1) 建结构相同的新表（改名 access_log_new，分区定义同上）
-- 2) 分批灌数据，每批 5 万行，按主键区间推进，避免长事务
INSERT INTO access_log_new
SELECT * FROM access_log
WHERE id > 0 AND id <= 50000;

-- 3) 重复上面的语句，区间每次 +50000，直到追上 max(id)
-- 4) 追平后短暂停写几秒，做最后一次增量补齐
INSERT INTO access_log_new
SELECT * FROM access_log a
LEFT JOIN access_log_new b ON b.id = a.id
WHERE b.id IS NULL;

-- 5) 原子切换
RENAME TABLE access_log TO access_log_old,
             access_log_new TO access_log;

RENAME TABLE 两条语句一起执行是原子的，切换瞬间不可见，不需要停站。切换之后保留 access_log_old 一周，确认没问题再 DROP。

分批大小要控制：每批 5 万行大约 1~2 秒，不会造成明显的主从延迟；如果每批 50 万行，从库可能落后半分钟以上。每批之间 sleep 0.2 秒，给复制线程留出追赶时间。

四、日常运维：滚动建分区 + 丢老分区

分区的价值全在这两个动作上。建新分区：

-- 把兜底的 pmax 拆开，插入 2026 年 12 月的分区
ALTER TABLE access_log REORGANIZE PARTITION pmax INTO (
  PARTITION p202612 VALUES LESS THAN ('2027-01-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

REORGANIZE PARTITION pmax 如果 pmax 是空的，操作是毫秒级的（只改元数据）；如果里面已经有数据，MySQL 会真的搬数据，那就慢了。所以必须提前建下个月的分区，别等到月末最后一天才建，那时候 pmax 已经攒了几天数据。

丢老分区，这是整个方案的杀手锏：

ALTER TABLE access_log DROP PARTITION p202512;

执行时间与分区里的行数无关，都是几十毫秒。对比一下之前那个 40 分钟的 DELETE，这就是分区最直接的价值。注意 DROP PARTITION 不可回滚，也没有 binlog 里的行记录（只有 DDL 语句），所以删之前一定要确认分区名：

SELECT PARTITION_NAME, TABLE_ROWS,
       ROUND(DATA_LENGTH/1024/1024) AS data_mb
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA='zz1984' AND TABLE_NAME='access_log'
ORDER BY PARTITION_ORDINAL_POSITION;

我们写了个 cron 脚本，每月 1 号凌晨：先建下下个月的分区（留两个月缓冲），再删掉 18 个月前的分区。脚本里加了一个断言——如果要删的分区名不在预期列表里就 abort，防止手滑。

五、分区裁剪：查询真的变快了吗

分区表最大的误解是「分了区就一定快」。只有查询条件能命中分区键时才会裁剪，否则它会扫所有分区，反而比不分区更慢（因为要打开 N 张表）。

-- 会裁剪：只扫 p202610
EXPLAIN SELECT COUNT(*) FROM access_log
WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';
-- partitions 列显示 p202610

-- 不裁剪：扫全部分区
EXPLAIN SELECT COUNT(*) FROM access_log WHERE status = 404;

判断依据就是 EXPLAIN 输出里的 partitions 列。如果它列出了所有分区，那说明条件没用上分区键，这笔查询的性能不会因为分区而改善。

几个容易踩的点：

  • 不要对分区键用函数。WHERE DATE(created_at) = '2026-10-01' 会让裁剪失效，改成 WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02'。
  • 不要用 BETWEEN 跨月做精确统计，虽然能裁剪到多个分区，但每个分区的边界判断会变复杂，跨几十个分区时优化器可能放弃裁剪。跨月查询时用显式的 >= / <。
  • ORDER BY created_at DESC LIMIT 20 这种查询，分区表的效率不如单表加索引。因为 MySQL 需要对每个分区分别取 20 条再归并。如果这是核心查询，考虑维护一张「最新 1000 条」的小表。

六、几个真实踩过的坑

坑一：唯一索引忘了带分区键。我们加了一个 UNIQUE KEY uk_trace (trace_id) 做去重，结果 ALTER 直接报 1503，因为唯一索引里没有 created_at。最后的解法是改成 UNIQUE KEY uk_trace (trace_id, created_at)——语义上其实没变（同一个 trace_id 不可能跨月重复），但满足了约束。

坑二：information_schema.PARTITIONS 的 TABLE_ROWS 是估算值。InnoDB 的这个字段来自统计信息，可能偏差 10% 以上。要精确数就 SELECT COUNT(*) FROM access_log PARTITION (p202610)，单分区计数很快。

坑三：备份工具要能识别分区。mysqldump 导出分区表时会写成一条完整的 CREATE TABLE ... PARTITION BY，恢复时一次建好，这个没问题。但如果你的备份脚本用了 --ignore-table 或者按分区单独导出，要确认版本支持（8.0 的 --partition 选项）。我们用 xtrabackup，它对分区表没有特殊处理，按物理文件备份，恢复后分区结构完整。

坑四：DROP PARTITION 不会立即释放磁盘空间。InnoDB 会把 ibd 文件标记为可复用，但物理空间要等后续写入复用，或者 OPTIMIZE TABLE（对分区表，实际是重建）。如果磁盘紧张，删完分区后要观察 df，必要时单独处理。

坑五：5.7 的差异。MySQL 5.7 的分区表不支持 ALTER TABLE ... REORGANIZE PARTITION 的在线执行，也不支持分区表上的某些在线 DDL。如果你的站还在 5.7，滚动建分区要放在维护窗口里做，或者干脆升级到 8.0——8.0 在分区管理上的体验好太多。

七、什么时候不要用分区

最后给个反向的判断，避免为了用而用：

  • 表小于 500 万行 —— 加索引就够了，分区的元数据开销反而是负担。
  • 查询模式以「单行主键查询」为主 —— 分区不会让单行查询更快，反而可能因为打开多张表而略慢。
  • 业务需要跨所有历史数据做聚合统计（比如「三年来每月总量」）—— 分区裁剪帮不上，还得扫全表，收益有限。
  • 团队里没人会写分区运维脚本 —— 分区最大的风险不是性能，是「忘了建下个月的分区导致写入全挂」。没有自动化就别上。

我们这张日志表用分区跑了八个月，最大的收益不是查询变快（虽然按时间段查确实快了 3~5 倍），而是「删数据」这件事从一个需要提心吊胆的运维操作，变成了一个每月自动执行的毫秒级 DDL。对一个只有一个人维护的站点来说，把风险操作变成无聊的自动化，比任何性能数字都值钱。

Last modification：October 27th, 2026 at 08:24 pm
© 允许规范转载

相关文章

Leave a Comment