Mysql5.6 prior

Update steps

    1. Write lock on original table
    1. Redefine an empty temporary table as defined by the original table and the execution statement.
    1. Add indexes (if any) to temporary tables.
    1. Then Copy the data in the original table to the temporary table.
    1. After all the records in the original table are copied to a temporary table, the original table is deleted. Then name the temporary table with the original table name.

In this case, the entire DDL process is the entire table lock.

Mysql5.6 later

Update steps

    1. Write lock on original table
    1. Redefine an empty temporary table as defined by the original table and the execution statement. And request rowlog space.
    1. Copy the original table data to the temporary table, and all table data changes (additions, deletions, and changes) are stored in the RowLog. In this case, the table client can perform operations.
    1. After all the original table data is copied, changes in the Rowlog are synchronized to the temporary table, a process that cannot be performed by the client.
    1. All records in the original table are copied to a temporary table, and all client additions, deletions, and changes are synchronized to the temporary table. Then name the temporary table with the original table name.

conclusion

ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE However, since Mysql5.6, ONLINE DDL has been added to make this table unavailable for a much shorter period of time.

Pay attention to

ALTER TABLE; ALTER TABLE; If the amount of data in this table is very large. Do not set the default value.

For example, there are currently more than 20 million tables. If the value of default is added to the field. Mysql will update the default value of the entire table after executing Online DDL, i.e

UPDATE `table_name` SET new_col =[Default value]WHERE TRUE
Copy the code

This is equivalent to updating 2000W + of data, all in the same transaction. That is, the transaction locks the entire table until all data records have been updated to their default values.

This is a very long time, and the table is unavailable for a very long time because it locks all records for the table.

I have tested 16 core, 32 GB, Mysql default configuration. 500W data plus one field.

  • Without default, the entire DDL update process is 66 seconds. In addition, the query, modify, and add operations of the table are available throughout the update process. There is almost no effect on the availability of the table.
  • With the default value, the entire DDL update process is 213 seconds. After about 100 seconds, the query, modify, and add operations of the table are in a waiting state.