磁盘没变小，但数据库越来越大
很多个人站长的第一台服务器只有 40G 系统盘，站点跑了两年，某天 df -h 一看：明明内容只有几千篇文章、图片也挂在对象存储上，/var/lib/mysql 却吃掉了十几个 G。更奇怪的是，用 du -sh 逐个库统计，加起来和 df 对不上；再看单张表，typecho_contents 才几十 MB，可 ibdata1 或者某张日志表却有 3 个 G。
这类"看不见的膨胀"绝大多数是两个原因：InnoDB 表碎片，以及被删掉的行留下的空洞没有归还给操作系统。很多人第一反应是 OPTIMIZE TABLE，敲下去却卡了几十分钟、业务被锁死，最后还得回滚。本文就把 InnoDB 的空间账算清楚：碎片怎么量化、OPTIMIZE TABLE 到底做了什么、什么时候会锁表、以及不锁表的替代方案。
先搞清楚 InnoDB 的空间账本：data_free 是什么
InnoDB 以页（page，默认 16KB）为最小单位管理数据。一张表在磁盘上占用的空间，并不等于有效数据的体积。当你 DELETE 掉大量行、或者频繁 UPDATE 变长字段（比如 TEXT）时，InnoDB 标记页内的空间为可用，但这些空洞不会立刻还给操作系统——它们留在表空间里，等后续 INSERT 来填补。
这就造成了两个层面的"浪费"：
- 页内空闲：页里被删掉的数据留下的空隙，可复用但当前是空的。
- 页级空闲（data_free）：整块数据页已经没有任何有效行了，理论上可以回收。
量化它们用的就是 information_schema.TABLES：
SELECT table_schema, table_name,
engine,
ROUND(data_length/1024/1024, 2) AS data_mb,
ROUND(index_length/1024/1024, 2) AS index_mb,
ROUND(data_free/1024/1024, 2) AS free_mb,
ROUND(data_free/(data_length+index_length+1)*100, 1) AS free_pct,
table_rows
FROM information_schema.TABLES
WHERE table_schema = 'zz1984'
AND data_free > 100*1024*1024 -- 只看空洞超过 100MB 的表
ORDER BY data_free DESC;几个关键判读点：
data_length是聚集索引（主键索引，也就是存放完整行数据的那棵树）占用的字节数，index_length是二级索引占用的字节数。data_free是"已分配但未被使用的空间"，单位字节。它是估算值，不是精确值，但量级够用。free_pct（data_free 占比）比绝对数值更有意义。我一般以 30% 以上且 data_free 超过 200MB 作为"值得重建"的门槛。几十 MB 的空洞不值得动，重建的代价比重建省下的空间大得多。table_rows对 InnoDB 是估算值，别拿它当准数。要精确行数得SELECT COUNT(*)，但那在大表上很慢，碎片判断用不着。
OPTIMIZE TABLE 在 InnoDB 上到底做了什么
这是最容易踩坑的地方。在 MyISAM 时代，OPTIMIZE TABLE 就是 myisamchk 式的整理碎片，很快。但在 InnoDB 上，OPTIMIZE TABLE 会被映射成 ALTER TABLE ... FORCE（引擎不支持原地优化），本质上是一次全表重建：
- 创建一个结构相同的新表（临时文件）；
- 把原表的每一行按主键顺序重新插入新表；
- 重建所有二级索引；
- 删除旧表文件，把新表文件重命名为原表名。
重建后空间会被压缩，索引也被整理（页填充率变高，范围扫描更快）。但代价也很明确：整个过程耗时长，且早期版本会长时间锁表。一张 5GB 的表在廉价 VPS 上重建可能要好几分钟到几十分钟，期间写入被阻塞，网站表现为「转圈不动」。
重建时发生什么，可以看返回结果：
mysql> OPTIMIZE TABLE zz1984.typecho_contents;
+-------------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------------------------------+----------+----------+-------------------------------------------------------------------+
| zz1984.typecho_contents | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| zz1984.typecho_contents | optimize | status | OK |
+-------------------------------+----------+----------+-------------------------------------------------------------------+看到 Table does not support optimize, doing recreate + analyze instead 这句，就说明它走的是"重建"路线，不是原地整理。这条提示本身就是判断依据。
会话线程与在线 DDL：怎么写才能少锁表
MySQL 服务器还有一个容易忽略的坑：表空间文件的大小不会在重建后自动缩小给操作系统，除非 innodb_file_per_table=ON（MySQL 5.6.6+ 默认开启）。如果这张表建在共享表空间 ibdata1 里，那么无论怎么 OPTIMIZE，ibdata1 只会变大不会变小——空洞被标记为可用，但文件本身仍然占着磁盘。
确认配置：
SHOW VARIABLES LIKE 'innodb_file_per_table'; -- 期望 ON在 ON 的前提下，每张表有独立的 .ibd 文件，重建时会生成新的 .ibd 并释放旧的，磁盘空间才真正归还。如果历史表的 .ibd 混在 ibdata1 里，唯一办法是把库整体导出重建：
# 已确认 innodb_file_per_table=ON 后
mysqldump --single-transaction --routines --triggers --events zz1984 > zz1984.sql
# 在测试库导入验证无误，再考虑重建库回到"少锁表"这件事。真要在生产上重建一张大表，有几个降低风险的写法：
1. 用 ALGORITHM=INPLACE 的加字段思路重建
如果目的只是整理碎片、不是为了改结构，不要裸跑 OPTIMIZE。可以用"加一个无关紧要的列再删掉"的技巧触发在线 DDL：
ALTER TABLE zz1984.typecho_contents
ADD COLUMN _tmp_opt TINYINT NULL, ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE zz1984.typecho_contents
DROP COLUMN _tmp_opt, ALGORITHM=INPLACE, LOCK=NONE;ALGORITHM=INPLACE, LOCK=NONE 让 MySQL 尽量在"允许并发读写"的方式下完成。注意：如果服务器认为只能 COPY 方式，会直接报错（提示该操作无法在 in-place 情况下完成），这反而是好事——它阻止了你误用锁表方式。所以这两个参数就是你的安全保险：宁可失败，也不要静默锁表。
2. 把重建挪到流量低谷
对个人站来说，最朴素也最有效的办法是把这类维护操作挪到访问低谷（比如凌晨三四点），并用 pt-online-schema-change 或 gh-ost 这类工具做真正的在线重表——它们用触发器或 binlog 把原表变更同步到影子表，重建期间原表照常读写。缺点是部署稍复杂，适合有一定运维基础的站长。
3. 重建前先确认磁盘有足够余量
重建期间，旧表和新表会同时存在。也就是说，重建一张 5GB 的表，瞬时可能需要 5GB 额外磁盘。如果磁盘已经 90% 满，重建跑到一半报 Disk full，表和临时文件一起留在地上，非常难收拾。所以：重建前 df -h 至少保留 表体积的 1.5 倍空余。
比 OPTIMIZE 更划算的做法：先看是什么在膨胀
有时候你不需要重建表，而是需要删掉本来就不该存在的数据。我在自己站上排查过好几次，膨胀的从来不是正文表，而是这些：
- 日志/统计表：访问统计、搜索历史、评论验证码记录，以每天几万行的速度长。这类表应该做分区滚动或定期归档，而不是攒到几个 G 了再重建。归档思路见 MySQL 大表分区的做法。
- 被删文章留下的孤儿关联：Typecho/WP 删文章时，如果直接删
typecho_contents不行，typecho_relationships和typecho_comments里的关联记录会残留，日积月累也是几百 MB。清理方式：
-- 找出没有对应文章的评论（孤儿评论）
SELECT c.coid, c.cid
FROM zz1984.typecho_comments c
LEFT JOIN zz1984.typecho_contents p ON p.cid = c.cid
WHERE p.cid IS NULL
LIMIT 20;
-- 确认无误后再删
DELETE c FROM zz1984.typecho_comments c
LEFT JOIN zz1984.typecho_contents p ON p.cid = c.cid
WHERE p.cid IS NULL;删除大批数据本身也会制造碎片（页内空洞），所以正确顺序是：先删无用数据，再做一次重建把空洞收掉。反过来先 OPTIMIZE 再删数据，等于白干。
一套可复用的碎片巡检脚本
与其等磁盘告警，不如每周跑一次巡检，把"值得处理"的表列出来：
#!/bin/bash
# /root/check_frag.sh —— InnoDB 碎片巡检
mysql -uroot -p"$DBPASS" -N -e "
SELECT CONCAT(
table_schema,'.',table_name,
' total=',ROUND((data_length+index_length)/1024/1024,1),'MB',
' free=',ROUND(data_free/1024/1024,1),'MB',
' free_pct=',ROUND(data_free/(data_length+index_length+1)*100,1),'%',
' rows=',table_rows
)
FROM information_schema.TABLES
WHERE engine='InnoDB'
AND data_free > 200*1024*1024
AND data_free/(data_length+index_length+1) > 0.3
ORDER BY data_free DESC;"输出一旦有表上榜，就按上文流程在低谷期处理：先清理无用数据 → 确认磁盘余量 ≥1.5 倍 → 用 ALGORITHM=INPLACE, LOCK=NONE 或在线工具体重建 → 复查 free_pct 是否回落。
重建之间还有第三条路：不缩文件，只整理索引
如果磁盘空间并不紧张，你真正想要的是"查询性能回到正常"，那就未必需要重建整张表。InnoDB 的二级索引在大量删除后也会碎片化——B+ 树里出现大量空页，范围扫描要走更多页，命中率下降。这种情况下，可以考虑 ANALYZE TABLE 配合索引层面的调整：
-- 更新统计信息，让优化器重新选择执行计划
ANALYZE TABLE zz1984.typecho_contents;
-- 查看表的索引与基数，判断是否有多余索引可以删
SHOW INDEX FROM zz1984.typecho_contents;
-- 查看当前索引占用
SELECT index_name, ROUND(SUM(stat_value*@@innodb_page_size)/1024/1024,1) AS idx_mb
FROM mysql.innodb_index_stats
WHERE table_name='typecho_contents' AND stat_name='size'
GROUP BY index_name;ANALYZE TABLE 很轻量，不重建文件，只是重新采样索引基数。它解决不了碎片，但能解决"索引明明在、优化器却不用"的问题。真正要收回磁盘的，还是逃不开重建这一步——这是 InnoDB 的架构决定的，没有免费的午餐。
结语：空间不会凭空消失，只是你还没看见它
InnoDB 的空间管理是"懒惰"的：它优先复用空洞，只在必要时才向操作系统申请。这个设计对写入性能是好事，代价就是磁盘上会出现"数据没那么多、文件却很大"的落差。理解 data_length / index_length / data_free 三个数字，配合 innodb_file_per_table 的确认，你就能准确判断是碎片问题还是数据本身的问题。
最后三条经验：一，先清理再重建，顺序反了要重来；二，重建前留够磁盘，否则很容易把小事搞成事故；三，能用在线工具就别裸跑 OPTIMIZE，个人站也值得对自己的访客负责。碎片整理不是日常操作，一个季度做一次巡检、发现问题再动手，就足够了。