MySQL表碎片：何时整理与怎么不锁业务

  1.  Home
  2. Text  
    3. Share to：

MySQL 表碎片这个话题，常常在两个极端之间摇摆：一边是有人在论坛上看到「删了数据要 OPTIMIZE 一下」，于是每周对全库跑一遍；另一边是有人说「InnoDB 不用管碎片，无所谓」。这两种做法都不对。碎片确实存在、确实会让查询变慢、也确实会占磁盘，但它不是随时都要处理，处理方式选错还可能锁表几个小时。这篇文章讲清楚碎片从哪来、用什么指标判断、什么时候该动手、以及动手时怎么不锁业务。

一、碎片是怎么产生的

InnoDB 的表空间是按「页」组织的，一页默认 16KB。当你删除或更新一条记录时，InnoDB 并不会立刻把页腾空、把后面的数据挪过来填满，而是把记录标记删除，留下空位。如果空位足够放下一条新记录，就复用它；不够，就在别处新开一页。时间一长，页与页之间、页内部都会出现大量空位。

产生碎片的主要动作有三类：

  • 大量 DELETE：日志表、会话表、临时任务表的典型形态。删得多、写得少，空间就被空着。
  • 不定长字段更新：例如 VARCHAR(255) 的字段从短字符串改成很长的字符串，原位置放不下，只能迁移到新页，原来的位置留下空洞。
  • 主键不连续插入：用 UUID 或随机数当主键，插入位置随机，页分裂频繁，页内填充率长期偏低。

其中第三类问题最容易被忽视，也最容易根治：把主键换成自增 BIGINT、UUID 只作为唯一索引的附加列，页分裂会明显减少。这是「结构层面消除碎片」，比事后 OPTIMIZE 高效得多。

二、三个指标怎么读

不要凭「表很大」或「删过很多数据」来判断，用 information_schema.TABLES 看三个数：

SELECT TABLE_NAME,
       ENGINE,
       TABLE_ROWS,
       ROUND(DATA_LENGTH / 1024 / 1024, 1)  AS data_mb,
       ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS idx_mb,
       ROUND(DATA_FREE / 1024 / 1024, 1)    AS free_mb,
       ROUND(DATA_FREE / (DATA_LENGTH + INDEX_LENGTH + DATA_FREE) * 100, 1) AS free_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db'
  AND DATA_FREE > 50 * 1024 * 1024
ORDER BY DATA_FREE DESC;

三个数的含义：

  1. DATA_LENGTH：数据实际占用，含碎片。
  2. DATA_FREE：已经被释放、但仍在表空间内部没还给操作系统（或没给其它表用）的空间。这个数才是碎片的度量。
  3. free_pct：空闲空间占比。经验阈值是 超过 25% 且 DATA_FREE 大于 1GB 才考虑整理。只有 5%~10% 的空闲实际上是正常的水位，OPTIMIZE 带来的收益小于风险。

还有一个更直接的判据是「行平均长度」的异常变化：同一张表如果半年内行数没怎么变，但 DATA_LENGTH 翻了倍，说明膨胀严重。这个判法比绝对值更贴近真实感受。

三、OPTIMIZE TABLE 到底做了什么

很多人以为它是「原地整理」，实际不是。InnoDB 的执行流程是：

  1. 建一张同结构的新表（在线 DDL 时叫临时表）。
  2. 把原表数据按主键顺序逐行复制到新表，重建所有二级索引。
  3. 复制期间原表的增量变更记录在一个日志里，复制结束后再应用。
  4. 用 RENAME 原子替换表名，删除旧表空间。

所以它的本质是 重建表，代价是「一个表的数据量」的磁盘临时空间 + 大量随机 IO。三个容易踩的地方：

  • 磁盘空间：重建期间旧表 + 新表同时存在，至少需要一倍额外空间。空间不足时报错 Error 1114 (HY000): The table is full，而且往往在跑到一半时才失败。
  • 锁：MySQL 5.6+ 支持 ALGORITHM=INPLACE, LOCK=NONE，但并非所有情况都能做到（例如有全文索引时可能需要锁表）。执行前必须先试 LOCK=NONE，失败再降级到 LOCK=SHARED。
  • 主从延迟：重建会产生大量 binlog（如果是 row 格式，等于把整个表重写一遍）。从库应用这些 binlog 需要同样长的时间，延迟会飙升，读业务会受影响。

一条安全的执行模板：

-- 先看能否不锁表
SELECT * FROM information_schema.INNODB_TABLES WHERE NAME LIKE 'your_db/your_table';
ALTER TABLE your_table ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
-- 若报 ERROR 1846，说明这个版本/这种表不支持，改用 pt-online-schema-change

用 ALTER TABLE ... ENGINE=InnoDB 而不是 OPTIMIZE TABLE 的原因是可以显式指定 ALGORITHM 和 LOCK，可控性更好；OPTIMIZE 在 InnoDB 上最终也是转成这个 ALTER，但参数不可控。

四、五种处置方案怎么选

按「风险从低到高」排，优先选上面的：

方案一：什么都不做，让空间复用

如果表的写入量足够大（新数据能填满空洞），碎片会自我消化。日志表这类「写完就删、删完又写」的形态，往往不需要整理。这是最便宜的选择，也是被低估的选择。

方案二：分区表 + DROP PARTITION

按时间分区的日志表，删除旧数据用 ALTER TABLE ... DROP PARTITION p202609，是「直接丢文件」级别的操作，秒级完成、产生的碎片几乎为零。如果你的表是按时间清理的，改成分区表比反复 OPTIMIZE 好十倍。

CREATE TABLE access_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at DATE NOT NULL,
  uri VARCHAR(255) NOT NULL,
  PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p202610 VALUES LESS THAN (TO_DAYS('2026-11-01')),
  PARTITION p202611 VALUES LESS THAN (TO_DAYS('2026-12-01')),
  PARTITION pmax    VALUES LESS THAN MAXVALUE
);

注意主键必须包含分区列，这是 InnoDB 分区表的硬限制，设计时就要把 created_at 放进主键。

方案三：pt-online-schema-change

Percona Toolkit 的工具，原理是建影子表 + 触发器同步增量 + 分批拷贝 + RENAME。优点是可以限速、可以中途暂停、对主库影响可控：

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  --max-load Threads_running=30 \
  --critical-load Threads_running=60 \
  --chunk-size 1000 \
  --chunk-time 0.5 \
  --print --dry-run \
  D=your_db,t=your_table

关键参数是 --max-load 和 --chunk-time：前者让工具在数据库压力大时自动暂停，后者控制每个批次的时间片。先用 --dry-run --print 看它要执行什么，再正式跑。它的缺点是要求表上有主键或唯一索引（没有就拒绝执行），以及触发器在某些高并发场景下会成为瓶颈。

方案四：gh-ost

GitHub 的方案，用 binlog 而不是触发器同步增量，对主库的额外写入更少，适合写入非常密集的表。代价是需要 binlog 为 ROW 格式、需要额外的连接权限，且迁移过程需要有人在旁边看进度。

方案五：重建到新表再改名

手工版：建新表、INSERT INTO new SELECT * FROM old 分批导入、补增量、RENAME。适合数据量中等（几十 GB 以内）且能安排维护窗口的场景。不适合 24 小时都有写入的业务表。

五、什么时候不该动手

下面这些情况，整理碎片的收益远小于风险，建议直接跳过：

  • 空闲占比低于 15%：这是正常水位，整理完一两周又回去了。
  • 表上有长时间未提交的事务：DDL 会等待元数据锁，可能把整个表卡死。执行前先查 information_schema.INNODB_TRX。
  • 磁盘余量不足两倍表大小：中途失败会留下一张残缺的临时表，清理起来更麻烦。
  • 从库延迟已经很高：重建产生的 binlog 会把延迟推得更高，先解决延迟。
  • 表本身很小（几百 MB 以下）：收益是按 MB 计的，不值得占用一次变更窗口。

查长事务：

SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS secs,
       trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY secs DESC LIMIT 10;

六、执行前后的检查清单

把下面几项做成模板，每次变更照抄一遍，能避免大部分事故：

  1. 备份：至少 mysqldump --single-transaction 一份结构 + 数据，同时记录当前表的行数，作为事后核对基准。
  2. 看余量：df -h 确认数据目录所在分区有空闲 ≥ 两倍表大小。
  3. 看负载：SHOW GLOBAL STATUS LIKE 'Threads_running'，超过 CPU 核数的两倍就先等一等。
  4. 看长事务：上面那条 SQL 结果为空或秒数很小。
  5. 限流执行：用 pt-osc 或 gh-ost，不要裸跑 ALTER。
  6. 监控主从：执行期间持续看 SHOW SLAVE STATUS 的 Seconds_Behind_Master，超过阈值就暂停。
  7. 事后核对：行数一致、索引数量一致、CHECK TABLE 通过、应用侧抽样读几条数据。
  8. 记录结果：写清整理前后的 DATA_LENGTH 和 DATA_FREE，下次判断有基线。

其中第 8 项经常被跳过，导致半年后没人知道上次整理到底有没有效果。一张表的整理记录，比整理本身更有长期价值。

七、比整理更重要的三件事

碎片是结果，不是原因。长期看，这三件事比反复 OPTIMIZE 更值得投入：

一、把「删除」换成「归档」

日志、订单历史这类数据，与其在业务表里删了再整理，不如定期搬到归档表或冷存储。业务表只留最近 N 天，数据量可控、碎片产生得少、查询也更快。归档表可以用 INSERT INTO archive SELECT ... WHERE created_at < ? + 分批 DELETE ... LIMIT 1000 的组合，避免一次性大删除。

二、主键用自增

用自增 BIGINT 做主键，插入始终在最后一页追加，页分裂少、填充率高。UUID 作为业务唯一键放到单独的 UNIQUE KEY 上。这一条改起来可能要动表结构，但长期收益最大。

三、分批删除而不是整表删

-- 错误：一次性删掉 500 万行，产生巨大碎片 + 长事务 + 主从延迟
DELETE FROM logs WHERE created_at < '2026-01-01';

-- 正确：每批 1000 行，循环执行，每批之间 sleep
DELETE FROM logs WHERE created_at < '2026-01-01' ORDER BY id LIMIT 1000;

ORDER BY id LIMIT 1000 是为了走主键索引、避免全表扫描，比不带 LIMIT 的删除快一个数量级。循环脚本里每批之间 sleep 0.1 秒，把压力摊开，主从延迟也不至于爆。

八、一个常见的误判

有人看到 DATA_FREE 很大就立刻整理，整理完发现磁盘没变——因为 InnoDB 的共享表空间（ibdata1）不会自动缩小，表空间文件 .ibd 虽然重建成小文件，但如果是 file_per_table=OFF 的旧配置，空间就不还给系统。检查一下：

SHOW VARIABLES LIKE 'innodb_file_per_table';

如果是 OFF，任何整理都不会让 ibdata1 变小，唯一办法是导出全部数据、重建实例。这个操作量级完全不同，必须先跟业务方确认必要性，不能在业务高峰期拍脑袋做。现代 MySQL 8.0 默认是 ON，但老站升级上来的实例常常还是 OFF。

九、小结

表碎片不值得「定期全库整理」，也不该完全无视。判断标准是空闲占比超过 25% 且绝对量超过 1GB；处置顺序是先结构优化（自增主键、分区表、归档）→ 再工具重建（pt-osc / gh-ost 限流）→ 最后才考虑裸 OPTIMIZE。整理前四看（空间、负载、长事务、主从延迟）、整理后四核（行数、索引、CHECK TABLE、抽样读），这套流程跑几次之后就会发现：真正让表变慢的往往不是碎片，而是缺索引和设计不当，碎片只是最容易被看见的那一个。

Last modification：November 1st, 2026 at 09:26 pm
© 允许规范转载

相关文章

Leave a Comment