MySQL Online DDL operations

mysql5.6 started to support Online DDL, and online DDL provides the following benefits;

1 Improve production environment availability 2 during DDL execution, you get the balance of performance and concurrency, you can specify the LOCK clause and the algorithm clause , lock=exclusize blocks access to the entire table, lock=shared allows queries but does not allow DML, Lock=none allows querying and DML operations, lock=default or unspecified, MySQL uses the lowest level of lock, algorithm specifies whether to copy the table or not copy the table directly inside the operation, 3 only to change where needed, not to create a new temporary table.

Before the cost of DDL operations was expensive, many of the ALTER TABLE statements were created with a new, empty table created as needed, and then copied the existing rows into the new table, updating the index of the inserted row, after all the rows were copied, the old table was deleted, and the copied table was renamed to the name of the original table.

The CREATE INDEX and drop index, optimized in 5.5 and 5.1, avoids the behavior of table copies, a feature called Fast index creation, 56. Enhanced, not DML can be processed when changing, called online DDL.

Some alter statements allow concurrent DML, but you still need to copy tables, which have a faster copy of the table than the previous version

• Adding, dropping, or reordering columns.

• Adding or dropping a primary key.

• changing the row_format  or key_block_size  properties for a table.

• changing The Nullable status for a column.

• optimize TABLE

• rebuilding a table with The force  option

• rebuilding a table using A " null "  alter TABLE ... Engine=innodb  statement

When the DDL is changing the table, whether the table is locked depends on how the operation works internally and the lock clause of ALTER TABLE, the online DDL statement always waits for the transaction commit or rollback of the Access table, because the DDL statement requires a brief exclusive request during preparation. The online DDL takes longer to log the changes resulting from concurrent DML operations, and finally applies these changes.

To see if the DDL is using temporary tables or internal operations, you can see how many rows in the statement execution result have been affected, and if it is 0 rows, then the table is not replicated, and if it is 0, the table is replicated.

As you can see in the following tests, renaming a column does not copy the record, the type of the modified column copies the table, and algorithm specifies that replace will prompt not to support this way, and that adding the column also does not have a copy table.

>alter table T8 Change name R_name char (80);
Query OK, 0 rows affected (0.04 sec)
records:0 duplicates:0 warnings:0



>alter table T8 Change R_name Rr_name char (90);
Query OK, 148 rows affected (0.10 sec)
records:148 duplicates:0 warnings:0



Test>alter table T8 Change Rr_name Rrr_name char (n), Algorithm=inplace;
ERROR 1846 (0a000): Algorithm=inplace is not supported. Reason:cannot Change Column type INPLACE. Try algorithm=copy.

>alter table T8 add column sex int;
Query OK, 0 rows affected (0.09 sec)
records:0 duplicates:0 warnings:0

MySQL Online DDL operations