判断表是否真碎片化需对比逻辑数据量与物理存储量：若.ibd文件大小超sum(data_length+index_length)50%以上且经历大量随机删改，才需优化；仅看data_free或磁盘占用不准确。
怎么判断表到底有没有碎片
别光看磁盘空间告急就急着优化，先确认是不是真有碎片。核心指标是 Data_free：执行 SHOW TABLE STATUS LIKE 'your_table_name'，如果返回的 Data_free 值明显大于 0（比如几十 MB 以上），且远超你近期删除的数据量，那基本可以断定是碎片在占地方。
更稳妥的方式是查系统表：SELECT TABLE_NAME, ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_db' AND DATA_FREE > 10485760; —— 这条语句只列出空闲空间超过 10MB 的表，避免误判小波动。
注意：InnoDB 表的 Data_free 不代表“可释放空间”，而是当前分配但未使用的页内空闲字节；它可能被后续 INSERT 复用，也可能因页分裂或行迁移固化成无效碎片。
OPTIMIZE TABLE 到底干了什么，为什么有时没用
OPTIMIZE TABLE 对 InnoDB 来说，本质就是一次隐式 ALTER TABLE ... ENGINE=InnoDB：它会新建一个临时文件，把原表数据按主键顺序重新写入 B+ 树，同时重建所有索引，最后原子替换文件。这个过程能合并页内空洞、消除行迁移残留、重排索引页物理顺序。
但它不是万能的：
- 如果表启用了
innodb_file_per_table = OFF，OPTIMIZE TABLE无法收缩系统表空间ibdata1，只会让独立 .ibd 文件变小； - 对大表（比如上百 GB），它会全程锁表（MySQL 5.6+ 的 Online DDL 能降级为读锁，但 DML 仍可能被阻塞）；
- 如果碎片主因是随机 UUID 主键导致的持续页分裂，
OPTIMIZE TABLE只是“拍平”一次，不改结构，下次插入又裂开。
什么时候该放弃 OPTIMIZE，直接重建导入
当 OPTIMIZE TABLE 执行失败、超时，或者你发现 Data_free 没降、但 DATA_LENGTH + INDEX_LENGTH 却异常膨胀（比如比实际数据量大 2–3 倍），说明碎片已深度嵌入页结构或存在损坏风险，这时该换更彻底的方式：
- 用
mysqldump --no-create-info --skip-triggers your_db your_table > data.sql导出纯数据； - 执行
DROP TABLE your_table； - 用
CREATE TABLE语句重建表（注意检查并修正主键类型、ROW_FORMAT、PACK_KEYS等易被 dump 忽略的细节）； - 再用
mysql your_db 导入。
这种方式强制从零构建物理存储，连 B+ 树层级、页填充率、甚至 innodb_fill_factor 都会重置。代价是停机时间更长、需要额外磁盘空间存 dump 文件，但效果最干净。
真正容易被忽略的两个点
一是 ANALYZE TABLE 必须跟在优化之后做。碎片清理后，统计信息还是旧的，优化器可能继续走错执行计划。不手动更新，EXPLAIN 看到的行数估计可能严重失真。
二是别迷信“自动整理”。MySQL 不会自己回收 Data_free，也不会在 DELETE 后主动压缩页。碎片是静默积累的，等你注意到磁盘告警或慢查询时，往往已经积压数月。定期（比如每月低峰期）扫描 information_schema.TABLES 并对 Data_free / (DATA_LENGTH + INDEX_LENGTH) > 0.2 的表触发 OPTIMIZE，比事后救火靠谱得多。
