1

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 ?

0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.