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