MySQL 5.6 online DDL

InnoDB The storage Engine implements the online DDL The principle is that when you perform a create or delete operation, the DML The operation log is written to a cache, and the index is created and then re-applied to the table to achieve data consistency, the cache size is determined by the parameter innodb_online_alter_log_max_size control, default is 128M , if the user updates a larger table, and in the creation process With a large number of write transactions, you may encounter this parameter space is not enough to use the situation.

the following types of DDL operations can be done online:

A : Creation and deletion of secondary indexes

B : Change the self-growth value

C : Add or remove foreign keys

D : Renaming of Columns

E : Add and Remove columns, but copy table

F : Change Row_format , change the key_block_size, but it will copy table

G : Add NOT null default XX for deleted columns , default null Properties, but it will copy table

H : Add a primary key, although it can be modified online, but will cause the table to be re-organized, copy table, The price is very high .

The following types of DDL operations cannot be manipulated online (DML operations are blocked by the online DDL):

A: Modifying the data column type definition will copy the table

B: Modify table or column character set conversion, copy the table

C: Delete primary key, copy table

D: Add a full-text index without copy table

It is important to note that due to the online DDL data consistency is achieved by re-doing the DML operations log after the index is created, which means that during index creation or deletion, the SQL the optimizer does not select an index that is being created or deleted.

MySQL 5.6 online DDL