在執行 MySql OPTIMIZE 命令期間避免只讀表鎖定的解決方法

我們需要優化一個大表（每週一次）以回收磁碟空間，因為記錄會定期從表中刪除。

有問題的表是一個InnoDB表，並且innodb_file_per_table在創建表期間啟用了該選項（並且仍然是）。

如手冊所述：

Note that MySQL locks the table during the time OPTIMIZE TABLE is running.

我們不能承受被鎖定的表，因為新記錄會不斷地插入到它的生產環境中。

希望對於明顯的問題存在一些解決方法；一種在處理鎖定問題時優化表的可靠方法，已在生產環境中使用。

也許創建一個臨時表並重命名它，但在執行複制時不會失去新記錄？

安裝percona-toolkit。然後你可以使用pt-online-schema-change。

我通常在必須更改表時使用它。它的作用是將數據從目前表複製到另一個表。觸發器是自動創建的，在複製時將傳入的數據複製到副本中。這只有在您還沒有觸發器的情況下才能完美執行。之後，複製的表被重命名為原始表（它被刪除，除非您指定不使用參數）。然後你就會有你的優化表。

對於 InnoDB 表，OPTIMIZE TABLE 映射到 ALTER TABLE，它會重建表以更新索引統計資訊並釋放聚集索引中未使用的空間。

所以一個

OPTIMIZE TABLE tablename; 

實際上是

ALTER TABLE tablename ENGINE = InnoDB;

然後像這樣使用 pt-online-schema-change（在命令行上，而不是在 MySQL 客戶端中）：

pt-online-schema-change --dry-run --user=root --host=localhost  --alter "ENGINE=InnoDB" --alter-foreign-keys-method=auto D=databasename,t=tablename,p=userpassword

如果執行良好，請更改--dry-run為--execute.

引用自：https://dba.stackexchange.com/questions/80830