Details of the implementation of MySQL Online DDL

MySQL Online DDL implementation details are divided into three phases:

the Prepare phase

1. Create temporary frm files

2. EXCLUSIVE-MDL lock is held. Reading and writing are prohibited.

3. Determine execution mode according to ALTER type (copy,online-rebuild, online-nobuild)

4. Update Memory Objects for Data Dictionary

5. Assign row_log object to record increment

6. Generate temporary ibd files

ddl execution phase

1. Degraded EXCLUSIVE-MDL lock, read and write allowed

2. Scan the cluster index of the original table for each record

3. Traverse the clustered index and secondary index of the new table, one by one

4. Construct corresponding index items according to records

5. Insert construction index entries into the sort_buffer block

6. Insert sort_buffer block into new index

7. Handling deltas generated during ddl execution (required only for rebuild type)

commit phase

1. Upgrade to EXCLUSIVE-MDL lock, prohibit reading and writing

2. Apply the log in the middle of the last row_log

3. Update innodb data dictionary table

4. Commit transaction (swipe redo log of transaction)

5. modification statistics

6. rename temporary idb file, frm file

7. modification completion