MySQL 表碎片產生原因、查看方法與清理最佳實踐（OPTIMIZE TABLE / ALTER TABLE）

MySQL 表碎片產生原因、查看方法與清理最佳實踐（OPTIMIZE TABLE / ALTER TABLE）

碎片怎麼產生的？

1. 刪除操作：刪除行後，頁面留下「空洞」，空間標記為可復用但不連續。
2. 更新操作：行長度變化導致頁分裂或行遷移，產生碎片。
3. 插入不連續：隨機主鍵導致頁分裂，頁面填充率下降。
4. 掃描代價：全表掃描需讀取所有已分配頁，碎片越多，實際讀取量遠大於有效數據量。

極端例子：1 萬行 × 10 Bytes = 100 KB 數據，刪除 9999 行後仍佔 100 KB 空間，掃描時仍讀 100 KB。

查看碎片大小

單表查看

SHOW TABLE STATUS LIKE 'table_name';
-- 關注 Data_free 列（字節），即碎片大小

全庫掃描碎片表

SELECT 
    TABLE_SCHEMA AS `db`,
    TABLE_NAME AS `table`,
    DATA_FREE / 1024 / 1024 AS `frag_MB`,
    ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql')
  AND DATA_FREE > 0
ORDER BY DATA_FREE DESC;

清理碎片：引擎不同，命令不同

注意：OPTIMIZE TABLE 對 InnoDB 等同於 ANALYZE TABLE（僅更新統計），不回收空間，必須用 ALTER TABLE ... ENGINE=InnoDB。

生產環境清理策略

1. 腳本化巡檢：每日/每周低峰期（如周三 03:00）運行腳本，檢測 DATA_FREE > 閾值（如 100MB 或 10% 表大小） 的表。
2. 分批執行：大表拆分多個小批次，避免長事務阻塞。
3. 工具輔助：pt-online-schema-change、gh-ost 零停機重建大表。
4. 監控指標：Innodb_page_size * Innodb_pages_free 估算實例級碎片率。

建議閾值

• DATA_FREE > 100 MB 且 DATA_FREE / DATA_LENGTH > 20% → 觸發清理。
• 核心業務表更激進（> 50 MB 或 > 10%）。

定期清理碎片，讓存儲空間與掃描性能始終在線。