在 Backend Tw 的 https://www.facebook.com/groups/616369245163622/posts/2467225396744655/ 這邊看到：

先大概回答一下假設，DELETE 後的空間是可以被同一個表格重複使用的，所以應該是還好，不過離峰時間跑一下 OPTIMIZE TABLE 也沒什麼關係就是了。

裡面提到的「13.7.2.4 OPTIMIZE TABLE Statement」(MySQL 5.7 文件) 以及「13.7.2.4 OPTIMIZE TABLE Statement」(MySQL 5.6 文件) 都有講到目前比較新的版本都已經是 Online DDL 了：(這邊抓 5.6 的文件，有支援的版本資訊)

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL. Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, and secondary indexes are not created as efficiently.

As of MySQL 5.6.17, OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

文件上有提到會有一小段 lock 的時間，不過一般來說應該不會造成太大問題。

這邊要講的是早期的經典工具 pt-online-schema-change (pt-osc)，這是使用 TRIGGER-based 的方式在跑，他的範例就直接提供了一個不需要 Online DDL 支援的版本：

Change sakila.actor to InnoDB, effectively performing OPTIMIZE TABLE in a non-blocking fashion because it is already an InnoDB table:

pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor

這在早期的時候還蠻常被拿出來用的，如果還在維護一些舊系統的話還蠻推薦的...

One thought on “MySQL InnoDB 的 OPTIMIZE TABLE 的 Lock”

1. Will Wang says:
   September 13, 2021 at 8:19 AM

   哈哈! 沒想到我的發問被大大拿到這討論 :p
   剛剛稍早我有再繼續問：

   「是說我再次細看這文件跟 [1] , 照文件說, 如果情況允許的話 (例如沒用 FULLTEXT indexes 或是 engine 有被支援(innoDB有)), rebuild 就不會用 table copy method, 是會用 in place 的方式做(The table rebuild triggered by OPTIMIZE TABLE is completed in place), 也就是只有短的時間會 lock (An exclusive table lock is only taken briefly), 不知道這樣的理解是否正確？ 🙂
   thanks!
   [1]
   https://dev.mysql.com/doc/refman/5.7/en/optimize-table.html
   」

   期待有人回答!
   然後, 我先繼續 share 一點觀察, 由於我推測我的 case (mysql 版本 / DB engine / index 使用 / ... etc ) 應該是可以使用到 online DDL / in-place, 剛剛有實際跑了 optimize table my_table 這 command:

   1. command 約跑了 50~70 sec (optimize 後的 size 不到 10 GB)
   2. 確實成功以 in-place 方式在跑, 因為過程中依然可以一直 insert, 表示這期間沒有或幾乎沒有 lock