mysql官网上说innodb执行optimize table时会有‘An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation.’这句话该如何理解呢?会锁多久,影响范围有多大,会影响数据库正常读取写入么?
这段话要完整得看
“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 and performed under the cover by ALTER TABLE ... FORCE 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.”
在打开在线ddl(并且版本支持在线ddl)情况下,这个操作会短暂给表加个排它锁。排它锁期间不能读写,只影响这个表(但是如果是单线程主从同步,那么这个操作还是会造成主从延迟的),这个时间比较短暂,相当于内部建立了一个新的临时表。
具体在线ddl做了什么事,可以看这篇官方文档,https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html