您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL DELETE 刪除數據後磁碟空間不釋放的原因與解決方案

編輯：MySQL 綜合教學

MySQL DELETE 後空間不回收？一文看懂碎片整理與 OPTIMIZE TABLE

很多開發者發現執行 DELETE FROM table WHERE ... 後，資料庫檔案大小（.ibd 或 .MYD）並未縮小。這是因為 DELETE 僅標記行為「已刪除」，實際頁面仍保留在表空間中，供後續 INSERT 重用。若需真正釋放磁碟空間，必須進行碎片整理。

現象對比

操作	數據行	表空間大小	說明
`DELETE FROM t WHERE id > 100`	標記刪除	不變	留下碎片，空間可被新 INSERT 重用
`DELETE FROM t` (無 WHERE)	全部刪除	歸零	MyISAM 直接截斷檔案；InnoDB 仍保留 10MB 最小表空間
`TRUNCATE TABLE t`	全部刪除	歸零	DDL 操作，直接釋放空間，速度極快

核心原因：碎片化

InnoDB 採用 B+ 樹索引，DELETE 導致頁面填充率下降，產生「空洞」。
變長列（VARCHAR、BLOB、TEXT）更新會產生行遷移，加劇碎片。
大量刪除後，雖然邏輯上空間可復用，但磁碟檔案不會自動縮小。

解決方案：OPTIMIZE TABLE

OPTIMIZE TABLE table_name;

適用引擎：MyISAM、InnoDB、BDB。

實際執行動作（以 MyISAM 為例）：

若有刪除/分裂行 → REPAIR TABLE 修復。
索引頁未排序 → 排序索引頁。
統計信息過舊 → 更新統計信息（ANALYZE TABLE）。

InnoDB 內部實際執行 ALTER TABLE ... ENGINE=InnoDB 重建表，達到整理碎片、回收空間、更新統計的效果。

操作建議與風險

鎖表：MySQL 5.6 前會鎖表阻塞寫入；5.6+ 支援 ONLINE DDL，允許並發 DML，但仍佔用臨時空間（約原表 1~1.5 倍）。
時間長：大表（>100GB）可能耗時數小時，建議維護窗口執行。
備份優先：操作前務必備份，防極端情況下數據損壞。
替代方案：Percona pt-online-schema-change 或 gh-ost 可無鎖重建，適合核心業務大表。

日常維護策略

批量刪除後立即執行 OPTIMIZE TABLE（非高峰期）。
定期（如每周）對高頻寫入表執行 ANALYZE TABLE 更新統計，延緩碎片惡化。
大量刪除考慮 pt-archiver 分批刪除 + 歸檔，避免產生巨大事務與碎片。
InnoDB 啟用 innodb_file_per_table=ON（預設開啟），每表獨立 .ibd，便於單表整理。

快速驗證碎片率

SELECT 
    TABLE_NAME,
    DATA_LENGTH/1024/1024 AS data_mb,
    DATA_FREE/1024/1024 AS free_mb,
    ROUND(DATA_FREE/DATA_LENGTH*100,2) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND DATA_FREE > 0
ORDER BY frag_pct DESC;

frag_pct > 20% 建議安排 OPTIMIZE TABLE。