MySQL表碎片产生于MyISAM频繁DELETE/UPDATE残留空块，InnoDB则源于页内空闲空间与二级索引分裂空洞；DATA_FREE>0不等于需OPTIMIZE，应结合free_pct>25%、data_length>100MB、avg_row_length异常及COUNT差异等指标综合判断。
MySQL 表碎片是怎么产生的
表碎片主要出现在 MyISAM 和 InnoDB 引擎中，但成因不同：MyISAM 碎片来自频繁的 DELETE 或 UPDATE 导致的空闲数据块残留；InnoDB 则更多体现为页内空闲空间（DATA_FREE）和二级索引页分裂后的逻辑空洞。InnoDB 的聚簇索引本身会自动重组织，但大范围删除后仍可能留下可观的 DATA_FREE，影响扫描效率和缓冲池利用率。
常见误判是把 DATA_FREE > 0 当作必须 OPTIMIZE TABLE 的信号——其实 InnoDB 的 DATA_FREE 是以 extent（1MB）为单位预分配的，即使只用几 KB，也可能显示几百 MB 空闲，这不等于“碎片严重”。
如何准确判断是否需要 OPTIMIZE TABLE
真正值得触发 OPTIMIZE TABLE 的场景很有限，优先看这三个指标：
-
DATA_FREE / DATA_LENGTH > 0.25且DATA_LENGTH > 100MB（排除小表噪声） -
innodb_page_size默认 16KB，若avg_row_length远小于实际行宽（比如AVG_ROW_LENGTH是 200，但多数行含TEXT实际占数 KB），说明行溢出严重，页利用率低 -
SELECT COUNT(*)和SELECT COUNT(*) FROM t USE INDEX (PRIMARY)结果差异显著（>10%），暗示主键索引页存在大量已删除但未回收的记录（尤其在READ-COMMITTED或更低隔离级别下长期未 purge）
对 InnoDB，OPTIMIZE TABLE 实质是重建表（ALTER TABLE ... FORCE），会加 SX 锁、阻塞写入，且全程占用双倍磁盘空间。生产环境应避免定期执行，而应结合慢查询日志中全表扫描变慢、Handler_read_* 计数异常升高来反推。
监控碎片的实用 SQL 查询
以下查询可嵌入巡检脚本，注意过滤掉系统表和小表：
SELECT
table_schema,
table_name,
engine,
round(data_length/1024/1024, 2) AS data_mb,
round(data_free/1024/1024, 2) AS free_mb,
round(100 * data_free / nullif(data_length, 0), 2) AS free_pct,
avg_row_length,
table_rows
FROM information_schema.tables
WHERE engine IN ('InnoDB', 'MyISAM')
AND table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
AND data_length > 100 * 1024 * 1024
ORDER BY data_free DESC
LIMIT 20;补充一点：MySQL 8.0+ 可查 INFORMATION_SCHEMA.INNODB_TABLESTATS 中的 cached_pages 和 modified_pages 辅助判断缓存污染程度，但该表非实时更新，仅作参考。
OPTIMIZE TABLE 的替代方案和陷阱
OPTIMIZE TABLE 在 InnoDB 中等价于 ALTER TABLE ... ENGINE=InnoDB，但有更轻量的选择：
- 对大表，优先用
ALTER TABLE t ALGORITHM=INPLACE, LOCK=NONE（需满足条件，如无全文索引、无虚拟列等） - 启用
innodb_file_per_table=ON（默认），否则OPTIMIZE无法释放空间回操作系统 - 避免在从库执行——会生成大量 binlog，主从延迟飙升；建议先停同步，优化完再启
-
MyISAM表执行后记得检查myisamchk -s，确认索引文件没损坏
最常被忽略的是：如果表上有 FULLTEXT 索引，OPTIMIZE TABLE 会重建全文索引，耗时可能远超预期，且期间全文搜索不可用。这类表应单独评估，或改用 ALTER TABLE ... DROP INDEX ... ADD FULLTEXT INDEX 分步操作。
