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

MySQL InnoDB 徹底釋放磁碟空間：避開「刪除不縮小」陷阱

編輯：MySQL 綜合教學

MySQL InnoDB 徹底釋放磁碟空間：避開「刪除不縮小」陷阱

InnoDB 採用「標記刪除」機制：DELETE、TRUNCATE、DROP TABLE 並不會立即將磁碟空間歸還給作業系統，導致 ibdata1 或 .ibd 檔案持續膨脹。本文說明為何會發生，並提供三種釋放空間的方案對比。

為什麼刪除資料檔案不會變小？

共享表空間：預設 innodb_file_per_table=OFF 時，所有表資料寫在 ibdata1，刪除資料僅標記頁面可重用，檔案永不縮小。
獨立表空間：innodb_file_per_table=ON（5.6+ 預設），每表一個 .ibd 檔。DROP TABLE 會刪除檔案釋放空間，但 DELETE/TRUNCATE 僅標記頁面空閒，.ibd 檔案大小不變。
碎片化： 大量刪除/更新產生頁面碎片，實際資料密度下降，查詢效能下降。

方案一：重建表 —— 標準線上釋放空間

# 方法 1：OPTIMIZE TABLE（鎖表，舊版本會重建表）
OPTIMIZE TABLE `large_table`;

# 方法 2：ALTER TABLE ... ENGINE=InnoDB（5.6+ 支援 Online DDL，僅短暫鎖表）
ALTER TABLE `large_table` ENGINE=InnoDB;

# 方法 3：pt-online-schema-change（零停機，大表首選）
pt-online-schema-change --alter "ENGINE=InnoDB" D=db,t=large_table --execute

原理： 建立新表、複製資料、切換檔案，舊 .ibd 刪除，新檔案大小 = 實際資料大小 + 少量碎片。

方案二：匯出匯入 —— 徹底整理共享表空間

針對 innodb_file_per_table=OFF 或需整理 ibdata1 時：

# 1. 匯出所有資料庫
mysqldump -u root -p --single-transaction --routines --triggers --events --all-databases > alldb.sql

# 2. 停止 MySQL
systemctl stop mysqld

# 3. 刪除舊表空間檔案
rm -f /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

# 4. 啟動 MySQL（自動建立全新 ibdata1、ib_logfile*）
systemctl start mysqld

# 5. 匯入資料
mysql -u root -p < alldb.sql

⚠️ 風險： 停機時間長、需足夠磁碟空間存放匯出檔、若有主從複寫需重新建立同步。

方案三：啟用 innodb_file_per_table 避免未來膨脹

[mysqld]
innodb_file_per_table = ON
# 重啟後新建表自動使用獨立 .ibd，舊表需 ALTER TABLE ... ENGINE=InnoDB 遷移

實戰驗證腳本

# 查看表空間碎片率
SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND(DATA_FREE/1024/1024, 2) AS free_mb,
       ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024, 2) AS total_mb,
       ROUND(DATA_FREE/(DATA_LENGTH+INDEX_LENGTH)*100, 1) AS frag_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
  AND DATA_FREE > 0
ORDER BY frag_pct DESC;

# 碎片率 > 20% 或 free_mb > 1GB 建議重建

常見誤區

操作	共享表空間	獨立表空間	說明
DELETE	❌ 不縮小	❌ 不縮小	僅標記刪除，空間標記為可重用
TRUNCATE	❌ 不縮小	✅ 縮小（5.6+）	獨立表空間會刪除 .ibd 重建
DROP TABLE	❌ 不縮小	✅ 釋放	共享表空間不回收，獨立表空間刪除檔案
OPTIMIZE TABLE	✅ 縮小	✅ 縮小	實際重建表，需鎖表

生產環境建議策略

**強制開啟** innodb_file_per_table=ON。
定期（月/季）跑碎片檢查腳本，針對高碎片表排程 pt-online-schema-change 重建。
大量刪除歷史資料時，優先用 DROP PARTITION（分區表）或 CREATE TABLE new AS SELECT ... 重建法，避免產生大量碎片。
監控 innodb_file_per_table 狀態與磁碟使用率，設定告警閾值。