一、动手前先拿到准确的成功数
InnoDB 的表空间只增不减,这是它的基本设计。删除一行记录,页里的空间被标记成可复用,但文件不会缩小;删掉一百万行,ibd 文件仍然是原来那么大。所以「删了数据磁盘没降」不是故障,是预期行为。真正需要判断的是另一件事:这些空闲空间到底有多少,值不值得花代价回收。
很多人凭感觉决定要不要重建表,结果在一个两百万行、只删了几千行的表上跑了四十分钟的 OPTIMIZE,磁盘只回收了 3MB。判断的依据应该是数字,而数字至少要看三个:数据容量、索引容量、以及空闲空间占比。
SELECT table_name,
table_rows,
ROUND(data_length/1024/1024, 1) AS data_mb,
ROUND(index_length/1024/1024, 1) AS index_mb,
ROUND(data_free/1024/1024, 1) AS free_mb,
ROUND(data_free/NULLIF(data_length+index_length,0)*100, 1) AS free_pct
FROM information_schema.tables
WHERE table_schema = 'mysite'
ORDER BY data_free DESC
LIMIT 20;要注意 table_rows 是估算值,InnoDB 采样统计,误差可能到百分之几十;数据量大时这个偏差会误导判断。真正准确的数字要用 information_schema.innodb_tablespaces 或者 ANALYZE TABLE 之后的统计,或者直接看磁盘上的 ibd 文件大小。我一般把估算值和 ibd 文件实际大小放在一起看,两者差距过大说明统计过期,先 ANALYZE 再判断。
还有一个容易误判的来源:一张表看起来「只有 200MB 数据但占了 4GB」,原因可能不是删除,而是碎片化、或者大量长文本字段存在溢出页、或者是索引比数据还大。这三种情况的处理方式完全不同,所以在决定重建之前,先看清是数据大还是索引大。
二、数据空闲、索引空闲和行的碎片不是一回事
InnoDB 的空间浪费有三种形态,混在一起谈会导致方案选错。
第一种是页内空闲。一个 16KB 的页里只存了三行记录,其余空间空着。这通常发生在随机主键插入或者频繁删除之后。它的特点是 data_free 不会明显增长,因为空闲空间仍在表空间内部被分配着,只是页没有被填满。判断方法是看平均行大小与页填充率,实践中可以通过 innodb_ruby 或 information_schema.innodb_tablespaces 的 pages 数据粗略算出来。
第二种是表空间内的空闲区。也就是 data_free 那个数字。删掉大量行、或者 truncate 掉分区之后,表空间里出现大段未使用区间。这部分最容易被回收,重建表就能拿回来。
第三种是索引膨胀。长字符串做主键、或者频繁更新的二级索引,会让 B+ 树页填充率下降,索引空间远大于理论值。这类问题重建只能暂时缓解,很快会再次膨胀,根治要改索引设计。
判断三者比例的最快方式是看 data_length 与 index_length 的比值,再看 data_free。如果表 90% 的体积是索引,重建之后回收有限,应该先去优化索引;如果 data_free 占比超过 30%,重建收益明显;如果两者都不突出但表很大,那多半是页内碎片,属于正常水位,不必处理。
还有一类特殊情形值得单独提:删除的是大字段。当表里有 TEXT 或 BLOB 列,行内只存 20 字节的指针,实际内容存在溢出页。删掉这些行之后,溢出页会释放,但主表的页可能仍是满的。这时的表现是「文件没变小但数据确实少了」,判断要看 information_schema.tables 里 data_free 的变化,而不是只看文件大小。
三、OPTIMIZE TABLE 在 InnoDB 上到底做了什么
在 MyISAM 时代,OPTIMIZE TABLE 就是重建表,语义清晰。在 InnoDB 上它被改造成了 ALTER TABLE ... FORCE 的别名,效果是重建表并重建索引,同时更新统计信息。理解这个映射关系很重要,因为它解释了三个常见困惑。
第一,它要占额外空间。重建过程会创建一份新的表空间,数据复制过去之后才替换旧文件。所以磁盘上必须有「表大小 + 索引大小」那么多的空闲空间,否则会在中途因为磁盘满而失败,而且失败后可能留下临时文件。这是我见过最多次的翻车方式:本来就是为了省空间才去重建,结果因为没空间而失败。
-- 重建前先确认磁盘余量,至少要大于表+索引的体积
SELECT table_name,
ROUND((data_length+index_length)/1024/1024/1024, 2) AS need_gb
FROM information_schema.tables
WHERE table_schema='mysite' AND table_name='post';
-- 再看磁盘
\! df -h /var/lib/mysql第二,它要持有锁。具体行为取决于版本与参数:从 MySQL 5.7 起,InnoDB 的重建支持在线模式,重建期间允许读写,但会在开始和结束阶段短暂持有元数据锁。开始阶段要等所有持有该表元数据锁的事务结束,如果有长事务在跑,这里就会卡住,后续所有对该表的请求都排在后面,表现为整站短暂的雪崩。所以重建前必须确认没有长事务。
SELECT trx_id, trx_started, trx_mysql_thread_id,
TIMESTAMPDIFF(MINUTE, trx_started, NOW()) AS mins, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;第三,它重建的是整张表,不是单个索引。所以如果只是某个索引膨胀,OPTIMIZE 会顺带重建全部索引,代价远大于必要。这种情况更合适的做法是单独删掉并重建那一个索引,虽然也会锁表,但范围小得多,也可以用在线 DDL 的方式减少阻塞。
四、在线重建的三种方式与各自的边界
如果业务不能停,重建就必须走在线 DDL。MySQL 提供了 ALGORITHM 与 LOCK 两个选项来控制行为,理解它们的组合是这件事的核心。
ALGORITHM=INPLACE 表示原地重建,允许并发 DML,但需要额外的临时空间,且在开始与结束阶段需要短暂的排他锁。ALGORITHM=INSTANT 只适用于改元数据的操作,比如加列,不适用于重建表。ALGORITHM=COPY 是旧式做法,全程锁表禁止写入,通常只在 INPLACE 不支持时被迫使用。
-- 声明意图:如果无法用 INPLACE 完成就报错,而不是偷偷降级成 COPY
ALTER TABLE post ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
-- 查看进度(MySQL 5.7+)
SELECT * FROM performance_schema.events_stages_current;LOCK=NONE 的含义是「不阻塞读写」,如果引擎认为做不到就会直接报错。这个「报错」是好事:它让不支持的场景立刻暴露,而不是静默地锁住整张表三十分钟。我在生产上固定写 LOCK=NONE,只有确认无法通过时才降级。
需要提醒的是三个边界。第一,表上有全文索引时,某些版本的 INPLACE 重建会退化为 COPY,一定要先在小库上验证。第二,外键约束会影响重建行为,子表与父表的重建顺序需要安排。第三,重建期间的写入会记录到 online log 里,如果重建耗时很长而写入量很大,这个日志会持续膨胀并占用额外的磁盘空间,这也是为什么会话中途报磁盘满。
对于超大表,还有一种更温和的替代方案:先把表按时间分区,然后对旧分区做重建或直接 DROP PARTITION。DROP PARTITION 是元数据操作,几乎瞬时完成,也不产生额外空间占用。这条路径的前提是表有合适的时间字段,并且查询都能带上分区键,不具备这个条件就不要硬上。
五、pt-online-schema-change 与 gh-ost 的取舍
当表大到在线 DDL 的风险不可接受时,第三方工具是常见选择。两者的核心思路相似:建一张结构一致的新表,用触发器或 binlog 同步增量,分批拷贝存量,最后原子改名切换。差别在于增量同步的实现方式。
pt-online-schema-change 用触发器捕获变更,优点是兼容性广,任何有写权限的账号都能用;缺点是在原表上装三个触发器,会放大写入开销,并且如果一个表已有触发器就会直接拒绝执行。gh-ost 读 binlog 拿增量,不碰原表触发器,写入开销更小,还能在切换前暂停、动态调整速度;缺点是需要 binlog 格式为 ROW,并且需要额外的权限与连接。
# gh-ost 典型用法:限速、限时、干跑验证
gh-ost --host=127.0.0.1 --database=mysite --table=post \
--alter='ENGINE=InnoDB' --allow-on-master \
--max-load='Threads_running=30' --critical-load='Threads_running=80' \
--chunk-size=1000 --max-lag-millis=1500 --execute这两个参数值得单独说。max-load 是软阈值,超过就暂停拷贝,降下来再继续;critical-load 是硬阈值,超过就直接退出。max-lag-millis 针对有从库的场景,拷贝速度会自动调整到从库延迟不超过这个值。这三个参数是把「在线」变成真的关键,不设它们,工具会以最快速度拷贝,把主库 IO 和从库延迟一起打满。
切换阶段是风险最高的时刻,因为要做一次原子改名,这会短暂持有元数据锁。如果有长事务,同样会卡住。所以无论用哪种工具,前置动作都一样:清理长事务、确认没有大查询、选一个流量低谷期。工具做得再好,也替代不了这个判断。
六、重建之外的四种回收办法
不是所有场景都值得重建表。以下四种方法在特定条件下更划算。
第一种是删除后重建索引。如果膨胀主要在索引,DROP INDEX 再 ADD INDEX 比整表重建便宜得多,且 ADD INDEX 在多数版本上支持 INPLACE。前提是这个索引不是主键,主键不能单独删。
第二种是导出再导入。mysqldump 出来的 SQL 重新导入,得到的表是最紧凑的。它的成本是停机时间,但可控且可预测,适合小表(几百 MB 以内)和可以接受几分钟停机的站。顺序上要注意先导结构再导数据,并且在新库上用同样的字符集与排序规则。
第三种是用新表替换。CREATE TABLE new LIKE old,INSERT SELECT 过去,再改名。这个方案的好处是可以在中间随时校验行数与校验和,坏处是同样要占双份空间,而且切换要处理自增主键的偏移。
CREATE TABLE post_new LIKE post;
INSERT INTO post_new SELECT * FROM post;
SELECT (SELECT COUNT(*) FROM post) AS old_cnt,
(SELECT COUNT(*) FROM post_new) AS new_cnt;
RENAME TABLE post TO post_old, post_new TO post;
-- 确认无误后再 drop post_old(先别急,留一天)第四种是分区滚动回收。把历史数据按月分区,每月把超过保留期的分区 DROP 掉。这个方案把「一次大回收」拆成「每月一次瞬时操作」,代价是表结构变复杂,且所有查询都要带分区键才能命中分区裁剪。日志表、统计表最适合这个模式。
四种方法的共同前提是:先估清收益。如果一张 20GB 的表只能回收 5%,花两小时重建完全不划算,不如直接扩容磁盘。判断标准我固定用三条:空闲空间绝对值超过 2GB、空闲占比超过 20%、表的写入压力在可接受范围内。三条都满足才动手。
七、从写入侧减少膨胀
回收只是止血,真正的解法是让表不要膨胀得那么快。膨胀的来源有四个,每个都有对应的改法。
随机主键。用 UUID 或随机字符串做主键,插入位置随机,页分裂频繁,填充率下降,同样的数据占更多页。改用自增整数或有序 UUID(比如时间前缀的 ULID)能显著改善。这个改动涉及业务代码,成本不低,但在新表设计阶段几乎零成本。
频繁更新长字段。InnoDB 更新一行时如果长度变化较大,可能触发页重组甚至行迁移。把经常更新的大字段拆到独立的表,主表只保留索引字段,能让主表的页保持紧凑。
无节制的历史数据。很多表从未清理过,三年积累的会话、日志、访问记录占了大头。这类数据要么定期归档到冷表,要么按时间分区后滚动删除。清理策略应该写在代码里,不是等磁盘告警时人工处理。
重复的索引。同一列上有两个前缀重叠的索引,或者一个索引是另一个的前缀,都是纯浪费。information_schema.statistics 里可以查出这类冗余,删掉不用的索引能让表立刻瘦一圈,而且不需要重建。
-- 找出从未被使用过的索引(需要先开启统计)
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL AND count_star = 0
AND object_schema NOT IN ('mysql','performance_schema')
ORDER BY object_name;这个查询的结果要谨慎对待:统计是自上次重启以来累计的,如果 MySQL 刚重启不久,很多索引都会显示零使用。至少要保证统计窗口覆盖一次完整的业务周期,比如一到两周,包括月末的批量任务。
八、操作的顺序与回退
无论选哪种方法,执行顺序都应该固定,不能临时发挥。我自己的顺序是:确认空间余量、确认无长事务、记录基线(行数、数据大小、索引大小、校验和)、备份、执行、校验、观察一天、清理临时文件。
-- 基线记录:行数与校验和,重建前后必须一致
CHECKSUM TABLE post EXTENDED;
SELECT COUNT(*) FROM post;
SHOW TABLE STATUS LIKE 'post'\G校验和这一项不能省。重建过程中如果发生异常中断、或者磁盘满导致部分数据写入失败,表可能进入不一致状态,而应用不一定立刻报错。有了重建前的校验和,重建后再算一次,一致才算成功。EXTENDED 会做逐行校验,大表上很慢,可以只在关键表上用,普通表用默认模式即可。
回退方案要在动手前想好。在线 DDL 的失败通常是原子的,旧表还在;但用新表替换的方式,改名之后旧表就成了唯一退路,所以旧表至少要保留一天,确认业务正常、数据校验一致、监控没有异常再删。删之前再确认一次磁盘余量,别为了省空间把退路删早了。
最后是观察。重建后的一两天要看三件事:慢查询数量有没有变化、从库延迟是否恢复、磁盘增长率是否回到正常。有时候重建会让统计信息大变,优化器选错执行计划,表现为某些查询突然变慢,这时 ANALYZE TABLE 加一次并观察执行计划即可。整个过程里,唯一不能省的是「先算收益再动手」这一步,其他都可以边做边调整。