Rebuild InnoDB Index ONLINE to prevent timeouts?

When I update a particularly large table, the update times out 开发者_如何学Gobecause the table is locked while the indexes rebuild. Is there any way to rebuild the index ONLINE (i.e. Oracle) so the update does not timeout?

Simple answer: No, there is no way.

More complex answer: You can emulate online index addition by using statement-based replication and adding the index to the slave first, then making it the master. This is why people use packages like http://mysql-mmm.org/.

optimize table rebuilds indexes online

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.

Source: https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

But! I've heard much about that you should not rebuild indexes in InnoDB because they are always up to date. Google a bit about that.

pt-online-schema-change can be used to optimize a table. OPTIMIZE TABLE is effectively a noop ALTER TABLE.

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