萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL DELETE 刪除資料後空間不釋放?OPTIMIZE TABLE 與 TRUNCATE 實戰對比

執行 DELETE 清除大量資料後,資料庫檔案大小不變——這是 MySQL 設計使然。本文深度解析兩種刪除模式差異、碎片產生機制、OPTIMIZE TABLE 回收流程與 TRUNCATE 適用場景。

兩種 DELETE 行為差異

語句空間回收速度適用場景
DELETE FROM tbl WHERE ...否(留下碎片)慢(逐行標記)條件刪除,需保留部分資料
DELETE FROM tbl / TRUNCATE TABLE tbl是(立即釋放)極快(重建表)全表清空,資料可全捨棄

為何帶 WHERE 的 DELETE 不回收空間?

  • InnoDB/MyISAM 僅將記錄標記為「已刪除」,放入空閒鏈表供下次 INSERT 復用
  • 資料檔不縮小,索引樹不重建,碎片累積導致掃描效能下降

OPTIMIZE TABLE 回收碎片

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
  • MyISAM:修復表、排序索引、更新統計資訊
  • InnoDB:執行 ALTER TABLE ... ENGINE=InnoDB 重建表(需 innodb_file_per_table=ON)
  • 執行期間鎖表,必須低峰期操作
  • 建議頻率:高變動表每週/月一次

TRUNCATE TABLE:徹底重建

TRUNCATE TABLE friends;
  • 保留結構,等同 DROP + CREATE
  • 自增計數器歸零
  • 不觸發 ON DELETE 觸發器
  • 無法回滾(非事務安全)

最佳實踐建議

  1. 條件刪除:DELETE WHERE ... → 定期 OPTIMIZE TABLE
  2. 全表清空且無外鍵依賴:TRUNCATE TABLE 最快
  3. 大量刪除考慮分批 DELETE ... LIMIT 10000 避免長鎖表
copyright © 萬盛學電腦網 all rights reserved