Mysql – How innodb alter table inplace algorithm inner work

innodbMySQL

From the mysql doc , it says :

ALTER TABLE operations are processed using one of the following
algorithms:

COPY: Operations are performed on a copy of the original table, and
table data is copied from the original table to the new table row by
row. Concurrent DML is not permitted.

INPLACE: Operations avoid copying table data but may rebuild the table
in place. Typically, concurrent DML is supported for InnoDB tables by
a DML log that is applied after table alterations are committed.

It just says mysql can dml when modify column , but how innodb alter table inplace algorithm inner work ?

Best Answer

There are too many different cases. I suggest you read https://dev.mysql.com/doc/refman/8.0/en/innodb-create-index-overview.html , then come back with a more specific question. (Back up the version if you are using something older.)

My feeling in watching MySQL evolve over the years is this last round of Optimizations of ALTER has added ways to speed up just about everything in ALTER. That includes avoiding rebuilding the table (when practical) and avoiding locks (when practical).

"says mysql can dml when modify column" -- Well, that depends on the modify column -- add/remove NULL, change ENUM / DEFAULT / etc, ADD/DROP, etc.

The first ALTER optimization was for adding an option to ENUM at the end and not exceeding 255. That was added many versions ago -- It was rather trivial in that it changed the "meta" data only. That can even be INSTANT in the new 8.0 terminology (even better than INPLACE).

Related Question