SQL 如何监控表碎片（MySQL OPTIMIZE TABLE 的触发条件）

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 分步操作。