OPTIMIZE TABLE的3个代价与替代方案
大家好，我是数据库小学妹👋 我踩过的坑，你别再踩。 有回清理历史数据，一张大表删掉了一大半。删完我去看磁盘，几乎没动。我以为删错了，又核对了一遍行数，确实少了。数据没了，空间却还占着。我第一反应是跑 OPTIMIZE TABLE，把空间收回来。结果那条命令跑了很久，磁盘更紧张了，业务也开始报慢查询。后来才分清楚，删数据和回收空间，中间隔着一道重建的成本。 ## 删了数据，空间去哪了 InnoDB 删一行，不是马上把它从磁盘上抹掉。它把对应的页标记成可复用，放进表空间的空闲列表。这些页还能被新数据用上。DELETE 之后继续写入，空间是能复用的。可它们不会自动还给操作系统。你看到磁盘没降，是正常的。 碎片也不全是删出来的。主键随机插入、变长字段更新，同样会留空洞。页装满了再插，InnoDB 会做页分裂，把一页拆成两页。行变长放不下，就迁到另一页，原位置留个指针。 空隙攒多了，同样的行数要读更多页。表占的空间变大，扫表的 IO 也跟着涨。还有更隐蔽的一层。页里空着一半，一页能装的有效行就少。buffer pool 缓存的数据跟着变少，命中率会往下掉。空间的问题，最后会变成内存的问题。不过说到底，这些空隙都会算进同一个数。`information_schema.tables` 里的 `DATA_FREE` 就是这块空间。 ```sql SELECT table_name, 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 FROM information_schema.tables WHERE table_schema = 'app_db' ORDER BY data_free DESC; ``` `free_mb` 大，说明表里空着。但空着不等于能还回去，得先看它是不是在独立表空间里。顺便说一句，`TRUNCATE` 和 `DROP` 会把整张表的空间还给操作系统，`DELETE` 不会。所以要清空整表，别用 DELETE 一行一行删。 ## 先问一句：你缺的是空间，还是性能 很多人跑到这一步就直接 OPTIMIZE。先停一下，问自己缺什么。缺空间，才谈回收。缺性能，碎片多半不是主因。查询慢先去查执行计划、索引、统计信息，别拿 OPTIMIZE 当万能药。碎片带来的收益，通常远小于它花的代价。 缺空间也分两种。磁盘还宽裕，那不用管，这些页迟早被新数据复用。磁盘真的紧，才需要缩一次。这里有个前提。只有 `innodb_file_per_table=ON`，每张表才有自己的 `.ibd` 文件，缩表才有意义。要是还开着共享表空间，空间都在 `ibdata1` 里，OPTIMIZE 单表也缩不回操作系统。 ```sql SHOW VARIABLES LIKE 'innodb_file_per_table'; ``` ## 怎么量碎片，别被 DATA_FREE 骗 `DATA_FREE` 能看个大概，但它有个坑。它统计的是表空间里的空闲页，不完全等于碎片。有些空闲页是预留的，有些才是碎片，混在一起。所以别只盯一个数，先把比例算出来。如果表是分区表，`information_schema.tables`里的DATA_FREE是估算值，更准确的方式是查`INFORMATION_SCHEMA.PARTITIONS`，把各分区的DATA_FREE加起来。 ```sql SELECT table_name, ROUND(data_free / (data_length + index_length + data_free) * 100, 1) AS frag_pct FROM information_schema.tables WHERE table_schema = 'app_db' AND data_free > 0 ORDER BY frag_pct DESC; ``` 一般超过百分之二三十，才值得往下谈回收。可比例只是一个数，还得看两样： - 删掉了多少行，表实际行数降了多少 - 查询有没有真的变慢 只有「删了很多、空间吃紧、查询也受影响」同时成立，OPTIMIZE 才值得做。单独看 `DATA_FREE` 大就动手，多数时候是白忙。 ## OPTIMIZE 到底做了什么 它本质是一次表重建。8.0 里 InnoDB 的 OPTIMIZE 会映射成 `ALTER TABLE ... FORCE`，把表重新写一遍，把空洞挤掉。 ```sql OPTIMIZE TABLE t_order; -- 等价于重建表：ALTER TABLE t_order FORCE; ``` 默认走在线 DDL，允许并发写入。但这不代表没代价。第一，开始和结束要拿元数据锁。正好有长事务或慢查询在表上，它就排队等，后面的 DML 也跟着堵。第二，要额外空间。重建时得写一份新的表文件，大小接近原表。磁盘本来就紧，这一下可能直接写满。这也解释了我那次磁盘为什么更紧张。 第三，很吃 IO，也会产生大量 redo 和 binlog。大表重建，主从延迟跟着起来。第四，它是一次性的。表以后继续删、继续写，还会再产生空洞。别把它当日常保养。 还有个附带效果。重建会把二级索引一起重写，统计信息也重新采样。有些表重建后查询变快，不是碎片少了，是统计信息终于对了。这个收益换个方式也能拿到。别把功劳全算在碎片上，更别为了它去动一张大表。 ## 该不该做，看这张表 | 场景 | 建议 | | --- | --- | | 表不大、磁盘紧、低峰窗口够 | 可以 OPTIMIZE | | 大表、写入热点、窗口不够 | 别直接做，走在线工具或分区 | | 碎片占比不高 | 不值得，收益有限 | | 查询慢但碎片不是主因 | 先查索引和执行计划 | | 共享表空间（file_per_table=OFF） | OPTIMIZE 单表缩不回去 | | 刚删完、后面还有大量写入 | 先别做，空页很快被复用 | | 碎片高但表只有几十 MB | 收益太小，不值得开窗口 | 大表要重建，我更倾向两种做法。一种是分区滑动。按时间分区的表，历史分区直接 `DROP PARTITION`，空间立刻回来，不用重建整张表。前提是表和查询都按分区键走。 另一种是在线重建工具。gh-ost 或 pt-online-schema-change，边搬数据边限速，能控制对主库和主从的影响。 ```bash # pt-osc 在线重建，带限速 pt-online-schema-change \ --alter "ENGINE=InnoDB" \ --max-load Threads_running=30 \ --critical-load Threads_running=60 \ --sleep 0.5 \ D=app_db,t=t_order ``` `--max-load` 和 `--critical-load` 是两道刹车，压力高了自动停。比裸跑 OPTIMIZE 稳得多。两个工具的路子不一样，选之前先看清楚。 | 工具 | 怎么搬增量 | 代价 | | --- | --- | --- | | pt-osc | 建触发器，把增量同步进影子表 | 每行写入多一层触发器开销，外键场景受限 | | gh-ost | 订阅 binlog，把增量回放进影子表 | 依赖 ROW 格式，磁盘要留两份表的余量 | 两个都要额外磁盘，也都绕不开 cut-over 那一刻的短暂锁。工具能限速，省不掉空间。空间够不够，先算再决定。下面这条查出重建大致要多大，再拿 `df -h` 对一下余量。留不到原表的一点二倍，就别在这台机器上动手。 ```sql SELECT table_name, ROUND((data_length + index_length) / 1024 / 1024, 1) AS rebuild_mb FROM information_schema.tables WHERE table_schema = 'app_db' AND table_name = 't_order'; ``` ## 真要跑，盯住这几点 重建一开始，先确认别被元数据锁堵住。有长事务持着这张表的 MDL，OPTIMIZE 就得一直等，后面的 DML 跟着排队。 ```sql SELECT waiting_pid, waiting_table, blocking_pid, sql_kill_blocking_query FROM sys.schema_table_lock_waits; ``` waiting 里是 ALTER TABLE，blocking 是一条长事务，那就别干等。先把那条事务处理掉。跑起来之后，盯三样：主从延迟、`Threads_running`、剩余磁盘。哪个不对就停。要停可以 KILL。但在线 DDL 有回滚，回滚本身也要时间，别以为停一下就立刻干净。 ## 缺的是空间，才谈回收 OPTIMIZE 不是日常保养，是一次手术。手术要有指征，要备血，要挑时间。你缺的是磁盘，才谈回收。缺的是速度，先查别处。把两件事混在一起，就容易在不该动刀的时候动刀。结果业务也跟着慢。 还有一种情况容易被忽略。表定期清，机器却一直没扩容。每次靠 OPTIMIZE 硬挤，挤出几十 G，过两个月又满。这是在拿手术替代体检。空间规划得回到容量和分区设计上。一张表一张表地救，救不过来。 ## 避坑清单 删了数据磁盘不降是常态，InnoDB 不会自动把页还给操作系统。别一看没降就慌，先看磁盘还够不够。 OPTIMIZE 要额外空间、要拿 MDL、吃 IO。大表别直接跑，低峰、留足空间，或用在线工具限速。 最后一条是我自己踩的。我有回磁盘紧，直接对大表跑了 OPTIMIZE，没算临时空间。跑到一半磁盘更满了，业务查询也跟着慢。后来才知道，重建要一份接近原表大小的空间。现在我做之前，先看剩余空间够不够原表的一点二倍。不够就先扩容，或者换方案。 ## 写在最后 删数据容易，回收空间要算账。重建的成本、锁的等待、IO 的冲击，还有一份额外空间，全在账上。这笔账也让我重新认识了「删除」。在数据库里，大多数删除只是逻辑上的。行没了，空间还在，空洞替你记着发生过什么。 想让它真的回来，得有人挑时间、留余量、承担一次完整的重建。这件事没有捷径。代价不会消失，只会换个地方付。 所以碎片这道题，难在判断，不在命令。你缺的是磁盘还是速度，现在该动还是该等，都得自己拿主意。磁盘会满，业务会忙。能在这两件事之间做对一次选择，比会敲十条命令值钱。 你们的表碎片是定期清，还是磁盘告警了才清？评论区聊聊。 我是数据库小学妹，帮你少走弯路少踩坑，咱们下篇见👋
