mysql OPTIMIZE TABLE 对数据库的影响

Which Locks Does OPTIMIZE TABLE Take? (文档 ID 1910515.1)

转到底部

---

APPLIES TO: MySQL Server - Version 4.0 and later Information in this document applies to any platform. GOAL Learn what the impact of executing OPTIMIZE TABLE is for InnoDB and MyISAM tables in terms of locking.   SOLUTION OPTIMIZE TABLE always uses table level locks, but whether concurrent read and/or writes are allowed depends on the storage engine and the MySQL version. In the following the InnoDB and MyISAM storage engines will be discussed. InnoDB For InnoDB OPTIMIZE TABLE is implemented as: ALTER TABLE ... ENGINE=InnoDB; ANALYZE TABLE ...; The table will either be locked exclusively or in shared mode depending on the MySQL version: MySQL 5.5 and earlier: Concurrent reads are possible, but writes are blocked. MySQL 5.6.16 and earlier: Concurrent reads are possible, but writes are blocked. MySQL 5.6.17 and later: Concurrent reads and writes are possible. mysq 版本 并发读 并发写   MySQL 5.5 and earlier: YES NO   MySQL 5.6.16 and earlier: possible blocked   MySQL 5.6.17 and later: YES YES   Notes: Even though OPTIMIZE TABLE is online in 5.6.17 and later, a brief lock is still required during the initial and final phases. It is also still required for the OPTIMIZE TABLE to get a metadata lock before starting. While concurrent access to the table is possible, the table is still rebuild, so the additional I/O required for the rebuild operation may affect the performance.   MyISAM For MyISAM tables, the table is always locked, both for reads and writes