萬盛學電腦網

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

MySQL OPTIMIZE TABLE 使用指南:回收空間、消除碎片、更新統計

作用

  • 回收刪除/更新留下的空洞空間(碎片整理)。
  • 重建索引,更新基數統計資訊(Cardinality),優化器受益。
  • InnoDB 映射為 ALTER TABLE ... ENGINE=InnoDB 重建表。

支援引擎

MyISAM、InnoDB、ARCHIVE。InnoDB 內部轉為 ALTER TABLE 線上重建(5.6+ 支援 INPLACE)。

語法


OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...;
  • NO_WRITE_TO_BINLOG / LOCAL:不寫入 Binlog,避免主從同步風暴,適合從庫執行。

何時執行

  • 大量 DELETE、UPDATE 導致碎片化後。
  • 定期維護窗口(每週/每月)針對高變動表。
  • SHOW TABLE STATUS 中 Data_free 佔比超過 20% 或絕對值 > 1GB 時。

實戰測試對比


-- 優化前
SHOW INDEX FROM tbl_name;
-- Cardinality 全為 1(統計失效)

OPTIMIZE TABLE tbl_name;
-- 耗時 40 秒

-- 優化後
SHOW INDEX FROM tbl_name;
-- Cardinality 恢復真實值(如 172462, 86231...),查詢計畫回正

PHP 批次優化腳本


query("SHOW TABLES");
while ($row = $tables->fetch_row()) {
    $tbl = $row[0];
    if ($mysqli->query("OPTIMIZE TABLE `$tbl`")) {
        echo "優化 $tbl 完成\n";
    } else {
        echo "優化 $tbl 失敗: " . $mysqli->error . "\n";
    }
}

注意事項

  1. 鎖表行為:MyISAM 全鎖;InnoDB 5.6+ ALGORITHM=INPLACE 僅短暫元資料鎖,允許並發讀寫。
  2. 磁碟空間:需預留 ≥ 表大小的臨時空間。
  3. 不要過度執行:低碎片表執行無增益、反增負載。
  4. 分區表:OPTIMIZE TABLE tbl PARTITION (p0, p1); 可指定分區。

替代方案

  • ANALYZE TABLE:僅更新統計資訊,不重建表,極快,適合統計過期但空間未碎片化場景。
  • ALTER TABLE ... ENGINE=InnoDB:強制重建,效果同 OPTIMIZE,但可指定 ALGORITHM=INPLACE, LOCK=NONE 更可控。
copyright © 萬盛學電腦網 all rights reserved