MySQL 釋放磁碟空間實戰：OPTIMIZE TABLE vs 重建表兩大方案深度對比與避坑指南

MySQL 釋放磁碟空間實戰

執行 DELETE 刪除大量行後，表文件 (`.ibd`) 大小**不會縮小**，空間被標記為「可復用」但未歸還作業系統。這是 InnoDB **B+ 樹頁面分裂/合併機制** (類比 Oracle 高水位線) 導致的碎片化。本文對比兩大釋放方案並給出生產環境操作建議。

方案一：OPTIMIZE TABLE (官方標準、在線重建)

OPTIMIZE TABLE `table_name`;

原理

• MyISAM：直接整理碎片、更新統計信息。
• InnoDB：映射為 ALTER TABLE ... ENGINE=InnoDB，**重建表** (建臨時表 → 拷貝數據 → 切換 → 刪舊表)，同時更新索引統計信息。

優缺點與避坑

方案二：手動重建表 (可控、可分批、適合超大表)

-- 1. 建立新表 (同結構)
CREATE TABLE `new_table` LIKE `old_table`;

-- 2. 分批拷貝數據 (避免長事務、減輕主從延遲)
INSERT INTO `new_table` SELECT * FROM `old_table` WHERE id BETWEEN 1 AND 10000;
-- ... 循環分批，或用 pt-archiver

-- 3. 切換表名 (原子操作，極短鎖)
RENAME TABLE `old_table` TO `old_table_bak`, `new_table` TO `old_table`;

-- 4. 驗證無誤後刪除備份
DROP TABLE `old_table_bak`;

優勢

• 可控制拷貝批次、併發度、暫停恢複。
• 配合 pt-online-schema-change (PT-OSC) 實現**真·在線不鎖表**重建 (內部用觸發器同步增量)。

碎片率評估 (決定是否需整理)

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

經驗值：碎片率 > 20% 或 DATA_FREE > 1GB** 時建議整理。

IF 表 < 10GB AND 低峰期可接受鎖 10-30min:
    → OPTIMIZE TABLE (最簡單)
ELSE IF 表 > 10GB OR 極高併發不可鎖:
    → PT-OSC 在線重建 (pt-online-schema-change --alter="ENGINE=InnoDB")
ELSE IF 需要精細控制/異構遷移:
    → 手動新建表 + 分批 INSERT + RENAME