MySQL 大表归档与分区实战：日志表瘦身、RANGE 分区滚动与冷热分表方案

  1.  Home
  2. Text  
    3. Share to：

为什么你的数据库会「跑着跑着就慢了」

个人站有一个共同的宿命：刚上线时数据库只有几兆，跑得飞快；三年后 typecho_comments 表里攒了几十万条 spam 评论，typecho_contents 里也有几千篇文章加修订版本，于是后台列表页开始转圈，VACUUM 式的全表扫描把磁盘 IO 打满，甚至某天凌晨因为磁盘写满导致 MySQL 直接崩掉。

这个过程通常不是缓慢恶化的，而是突然跨过某个阈值的——索引无法再装入内存，每次查询都变成随机磁盘读。解决办法不是「升级服务器配置」（那只是把问题往后推），而是把历史数据搬出去。这篇文章讲的就是 MySQL 大表瘦身的三种手段：归档、分区、以及两者的配合。

第一步：先量化，别凭感觉优化

动手之前必须知道「哪张表最大、增长最快」。核心查询是看表的行数和磁盘占用：

SELECT table_name,
       table_rows,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb,
       ROUND((data_length+index_length)/1024/1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'zz1984'
ORDER BY (data_length+index_length) DESC
LIMIT 15;

注意 table_rows 对 InnoDB 是估算值，不精确。要拿准确行数得用 SELECT COUNT(*)，但对大表来说这个操作本身就很慢。实践中可以接受估算值来初筛，确认目标表之后再用 COUNT(*) 精确统计一次（放在低峰期跑）。

另一个必须看的指标是索引的「膨胀程度」：如果某张表的 index_mb 比 data_mb 还大，说明索引建多了，值得单独审视。

仓库表 vs 日志表：先分清是哪种再选方案

大表分两类，处理思路完全不同：

  • 仓库表：数据全都需要长期保留，比如文章表、用户表。这类表不能删数据，只能靠「分区 + 冷热分离」或者「读写分离」来分摊压力。
  • 日志表：只有近期数据有价值，比如访问日志、评论验证记录、错误日志。Typecho 的 typecho_comments 里绝大多数是 spam，这类表应该暴力归档——把超过 N 个月的数据搬走。

个人站 90% 的性能问题出在第二类。先确定目标表属于哪一类，再往下走。

方案一：历史数据归档（适合日志表）

归档的基本流程是「导出 → 校验 → 删除」。绝对不要直接 DELETE FROM xxx WHERE created < ... 了事，原因有两个：第一，一次性删除几百行万会长时间持有锁，站点在这期间基本不可用；第二，删完数据就没了，一旦发现导错了无法挽回。

推荐的稳妥做法分四步。第一步，导出到独立的归档表（同库内，避免网络问题）：

CREATE TABLE typecho_comments_archive LIKE typecho_comments;

INSERT INTO typecho_comments_archive
SELECT * FROM typecho_comments
WHERE created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH));

第二步，校验行数完全一致：

SELECT
  (SELECT COUNT(*) FROM typecho_comments_archive) AS archived,
  (SELECT COUNT(*) FROM typecho_comments
    WHERE created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH))) AS expected;

两个数字必须相等才继续。第三步，分批删除原表数据，每批间隔一下，避免长事务：

DELETE FROM typecho_comments
WHERE created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH))
ORDER BY cid
LIMIT 2000;
-- 重复执行直到 affected rows = 0

第四步，用 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 回收空间。注意 OPTIMIZE TABLE 对 InnoDB 来说实际是「重建表」，会消耗与表大小相当的额外磁盘空间，执行前务必确认剩余空间够用：

SELECT ROUND(SUM(data_length+index_length)/1024/1024,2) AS need_mb
FROM information_schema.tables
WHERE table_schema='zz1984' AND table_name='typecho_comments';

空间不够时可以用 pt-online-schema-change 或干脆先归档到别的表再重建。个人站更实际的做法是：先删数据回收逻辑空间，OPTIMIZE 放到深夜再跑。

方案二：分区表（适合按时间查询的大表）

如果某张表的数据必须保留，但查询总是带时间条件（比如「查最近 7 天的访问统计」），那分区是最优解。MySQL 支持 RANGE 分区，按月份切分：

ALTER TABLE visit_log
PARTITION BY RANGE (TO_DAYS(created)) (
  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
);

分区的威力在于分区裁剪（partition pruning）：当查询条件是 WHERE created >= '2026-03-01' 时，MySQL 只会扫描 p202603 及之后的分区，前面几个月的数据连索引都不用碰。用 EXPLAIN 可以验证：

EXPLAIN SELECT COUNT(*) FROM visit_log WHERE created >= '2026-03-01';
-- 输出里 partitions 列应该只列出 p202603,pmax

分区的几个硬性限制必须知道：分区键必须包含在主键或唯一键里，否则建不了；单表最多 8192 个分区；分区表不支持外键。对个人站而言这些限制基本不构成问题，因为日志表通常没有外键。

分区表的日常维护：滚动建分区

分区表建好之后需要定期「滚动」：加新分区、删旧分区。删分区的速度极快，因为它是 DROP PARTITION，直接删文件，不逐行删除：

-- 每月执行一次
ALTER TABLE visit_log ADD PARTITION (
  PARTITION p202610 VALUES LESS THAN (TO_DAYS('2026-11-01'))
);
ALTER TABLE visit_log DROP PARTITION p202508;

要把这件事写进 crontab，但注意 ADD PARTITION 需要一个 pmax 兜底分区存在，否则插入超过最后分区边界的数据会直接报错「Table has no partition for value」。标准做法是始终保留一个 VALUES LESS THAN MAXVALUE 的分区：先 REORGANIZE 把它拆成「新月份 + 新的 pmax」，而不是直接 ADD。这个细节很多人第一次做分区表时会踩：

ALTER TABLE visit_log REORGANIZE PARTITION pmax INTO (
  PARTITION p202610 VALUES LESS THAN (TO_DAYS('2026-11-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

方案三：冷热分表 + 视图（适合既想保留又不想拖慢查询）

如果程序不方便改成按月写不同表，可以用「冷热表」方案：热表只保留近 3 个月数据（查询快、索引小、常驻内存），冷表存放历史数据。然后用 UNION 视图把两者合一，对外看起来还是一张表：

CREATE VIEW comments_all AS
  SELECT * FROM typecho_comments
  UNION ALL
  SELECT * FROM typecho_comments_archive;

这个方案的代价是：视图上的查询无法使用分区裁剪，且带 ORDER BY ... LIMIT 的查询性能会比较差（需要对两个表都排序）。所以它适合「后台偶发查询历史数据」的场景，不适合高频的前台查询。个人站的评论列表通常分页展示，前台只查热表就够了，冷表留给后台「历史评论」入口单独查——这才是合理的用法。

自动化归档脚本

手工做一次归档不算难，难的是每月都记得做。写一个脚本挂到 crontab 上，逻辑是「分批删除 + 记录日志」：

#!/bin/bash
DB=zz1984
PASS=$(cat /root/.my.cnf | grep password | cut -d= -f2)
CUTOFF=UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH))
while true; do
    AFFECTED=$(mysql -uroot -p"$PASS" $DB -N -e \
      "DELETE FROM typecho_comments WHERE created < $CUTOFF ORDER BY cid LIMIT 2000; SELECT ROW_COUNT();")
    echo "$(date '+%F %T') deleted $AFFECTED rows" >> /var/log/zz_archive.log
    [ "$AFFECTED" -eq 0 ] && break
    sleep 2
done

配上 /etc/cron.d/zz_archive：

30 4 1 * * root /root/zz_archive.sh

每月 1 号凌晨 4:30 执行。注意脚本里用了 ORDER BY cid LIMIT 2000——分批删除时带上主键排序能减少锁范围，也让删除过程更可预测。日志写到 /var/log/zz_archive.log，方便回溯「这个月删了多少」。

归档之前的两条铁律

第一，归档必须发生在备份之后。任何删除操作前，先 mysqldump 一份完整的库。个人站的数据丢了没人能帮你恢复，这个顺序不能颠倒。第二，不要在生产高峰期做大表变更。OPTIMIZE TABLE、ALTER TABLE 加分区、大批量 DELETE 都会占用 IO，放在凌晨 3-5 点执行，并提前确认没有定时任务在同一时段跑备份。

用 pt-archiver 把归档做得更省心

手写删除循环能用，但有两个缺点：一是删除过程会一直持有行锁，二是没法顺便把数据同步到归档库或归档表。如果你不介意装一个 Percona Toolkit（个人站也可以用），pt-archiver 是更成熟的选择：

pt-archiver \
  --source h=127.0.0.1,D=zz1984,t=typecho_comments \
  --dest h=127.0.0.1,D=zz1984,t=typecho_comments_archive \
  --where "created < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 12 MONTH))" \
  --limit 1000 --commit-each --purge --statistics

几个参数的含义值得记住：--limit 1000 是每批处理的行数，--commit-each 让每批处理完就提交（而不是攒到最后一次性提交），--purge 表示插入归档表成功后从源表删除，--statistics 输出处理统计。--commit-each 是关键——它保证了事务不会长时间开着，即使中途出错也只是最后一批没提交，不会产生半条数据。

还可以加 --no-delete 先跑一轮「只复制不删除」的演练，确认归档表里的数据确实是你要的那批，再改成正式执行。这个「先演练后执行」的习惯，能避免绝大多数数据事故。

归档之后别忘了清理索引和统计信息

很多人归档完就结束了，其实还有两件收尾工作。第一件是检查索引是否需要精简：大表上常见的现象是「历史上为了优化某个慢查询加了好几个复合索引」，归档之后查询模式变了，这些索引可能已经用不上，只增加写入开销。用 SHOW INDEX FROM 表名 列出所有索引，配合 sys.schema_unused_indexes 视图找从未被使用的索引：

SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'zz1984';

注意这个视图统计的是「自 MySQL 启动以来」的使用情况，所以必须在数据库运行足够久（覆盖完整的业务周期，比如包含一次月末任务）之后看才有意义。刚重启过 MySQL 就去看，结论会是「所有索引都没用过」。

第二件是更新统计信息。InnoDB 的优化器依赖索引基数（cardinality）来决定执行计划。大量删除数据后，基数还停留在旧值上，优化器可能仍然选择一条早已不划算的索引。手动刷新统计信息：

ANALYZE TABLE typecho_comments, typecho_comments_archive;

这行命令很快，不阻塞读写，值得在每次归档后跑一次。做完这两步，才算真正完成了「归档 - 回收 - 重优化」的闭环。

检查清单

  • 用 information_schema 找出最大的 5 张表，确认是仓库表还是日志表
  • 确认剩余磁盘空间足够 OPTIMIZE TABLE（约等于表大小的额外空间）
  • 归档前完成一次完整 mysqldump 备份
  • 归档用「导出 → 校验行数 → 分批删除」三步，不直接大删
  • 分区表的查询用 EXPLAIN 确认发生分区裁剪
  • 分区表始终保留 pmax 兜底分区，滚动用 REORGANIZE PARTITION
  • 归档脚本挂 crontab，并输出日志便于回溯

小结

大表瘦身是个人站从「能跑」走向「稳定」的必经一课。核心思路只有一句：让每一张热表都能被完整装进内存。日志表靠归档，需要保留的表靠分区，两者都不合适的靠冷热分表加视图。做完之后你会发现，服务器配置没变，但后台列表页的响应时间可能从 3 秒降到 200 毫秒——这才是性价比最高的「性能优化」，比盲目升级 CPU 内存划算得多。

Last modification：September 21st, 2026 at 10:26 pm
© 允许规范转载

相关文章

Leave a Comment