MySQL Online DDL detailed

The function of the MySQL online DDL is to read and write to the table as well as the DDL operation on the table, that is, the DDL operation on the table does not affect transactions on the table.

Advantages of this feature:

1. Improve response efficiency and availability in busy production environments.
2. You can use the LOCK clause to reconcile performance and concurrency.
3. Use less disk space and IO overhead than the algorithm=copy algorithm.

DDL Online Status Support table:

in place Only

Operation		Rebuilds Table	permits Concurrent DML	modifies Metadata	Notes
CREATE INDEX,ADD INDEX	Yes*	no*	Yes	No	Restrictions apply for FULLTEXT indexes; see next row.
ADD FULLTEXT INDEX	Yes*	no*	No	No	Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent indexes may is added on the FULLTEXT same table without rebuilding the table.
ADD SPATIAL INDEX	Yes	No	No	No
RENAME INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
DROP INDEX	Yes	No	Yes	Yes	Only modifies table metadata.
OPTIMIZE TABLE	Yes*	Yes	Yes	No	In-place operation is not a supported for tables with FULLTEXT indexes.
Set column Default value	Yes	No	Yes	Yes	Only modifies table metadata.
Change auto-increment value	Yes	No	Yes	no*	Modifies a value stored in memory, not the data file.
ADD FOREIGN KEY constraint	Yes*	No	Yes	Yes	The INPLACE algorithm is supported was foreign_key_checks disabled. Otherwise, only the COPY algorithm is supported.
Drop FOREIGN KEY constraint	Yes	No	Yes	Yes	foreign_key_checksCan be enabled or disabled.
Rename column	Yes*	No	Yes*	Yes	To permit concurrent DML, keep the same data type and only change the column name. Is isn't ALGORITHM=INPLACE supported for renaming agenerated column.
ADD column	Yes*	Yes*	Yes*	No	Concurrent DML is a permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation. is ALGORITHM=INPLACE supported for adding a virtual generated column and not for adding a stored generated column. Adding a virtual generated column does not require a table rebuild.
Drop column	Yes	Yes*	Yes	No	Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE is supported for dropping a generated column. Dropping a virtual generated column does not require a table rebuild.
Reorder columns	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change Property ROW_FORMAT	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Change Property KEY_BLOCK_SIZE	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Make columnNULL	Yes	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation.
Make columnNOT NULL	Yes*	Yes	Yes	No	Rebuilds the table in place. STRICT_ALL_TABLES STRICT_TRANS_TABLES or SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. The server prohibits changes to foreign key columns that has the potential to cause loss of referential integrity. See section 13.1.8, "ALTER TABLE Syntax". Data is reorganized substantially, making it an expensive operation.
Change Column data type	no*	Yes	No	No	VARCHARSize may increased using online ALTER TABLE . See Modifying Column Properties for more information.
ADD PRIMARY Key	Yes*	Yes*	Yes	No	Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE is not permitted under certain conditions if columns has to being converted to NOT NULL .
Drop primary KEY and add another	Yes	Yes	Yes	No	Data is reorganized substantially, making it an expensive operation.
Drop PRIMARY Key	No	Yes	No	No	Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.
Convert Character Set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Specify character Set	No	Yes*	No	No	Rebuilds the table if the new character encoding is different.
Rebuild with FORCE option	Yes*	Yes	Yes	No	Uses. Is isn't ALGORITHM=INPLACE supported for tables with ALGORITHM=INPLACE FULLTEXT indexes.
"null" rebuild using ALTER TABLE ... ENGINE=INNODB	Yes*	Yes	Yes	No	Uses ALGORITHM=INPLACE . Is isn't ALGORITHM=INPLACE supported for tables with FULLTEXT indexes.
Set STATS_PERSISTENT , STATS_AUTO_RECALC , STATS_SAMPLE_PAGES persistent statistics options	Yes	No	Yes	Yes	Only modifies table metadata.
ALTER TABLE … ENCRYPTION	No	Yes	No	Yes
Drop a STORED column	Yes	Yes*	Yes	No	Rebuilds the table in place.
Modify STORED Column Order	Yes	Yes*	Yes	No	Rebuilds the table in place.
ADD a STORED column	Yes	Yes*	Yes	No	Rebuilds the table in place.
Drop a VIRTUAL column	Yes	No	Yes	Yes
Modify VIRTUAL Column Order	Yes	No	Yes	Yes
ADD a VIRTUAL column	Yes	No	Yes	Yes

Related syntax for online DDL operations:

To create a Level two index:

CREATE INDEX  on Table (col_list);
ALTER TABLE Table ADD INDEX name (col_list);

To delete a level two index:

Creating and deleting a Level two index on a innodb table does not produce table-copying

When the index is being created or deleted, it is possible to read and write to the table, and when the CREATE INDEX or DROP INDEX statement is executed against the table, the CREATE INDEX and DROP INDEX statements can be completed only after the transaction that accesses the table has completed. So the initial state of the index reflects the most recently used data in the table, which, in the past, causes a deadlock to be undone when the table performing the CREATE index or drop index is operating.

Another use of the online DDL is that when you are migrating data, you can create a table structure, import data, and finally create related indexes, which often improves the efficiency of data migration.

 

To modify the property values of a field:

ALTER TABLE ALTER COLUMN SET DEFAULT literal; ALTER TABLE ALTER COLUMN DROP DEFAULT;

The default value of the field is stored in the table's. frm file, not in the InnoDB data dictionary.

To modify the self-increment of a field:

ALTER TABLE Table Auto_increment=next_value;

In a distributed system that uses replication, or in a shard, you need to reset the value from increment to a specified number, in the Data warehouse, you sometimes need to clear the data in the table, then reset self-increment, and then import the new data.

Rename Field name:

ALTER TABLE tbl Change old_col_name new_col_name datatype;

When you use this statement to modify only the name of a field, and you do not modify the field type, the execution of the statement is always online.

When you rename a field in a foreign key constraint, the name of the field is automatically updated in the foreign key's definition to the renamed name, and the operation is only run in In-place mode when the field in the external key is renamed.

When specified in the Modify statementALGORITHM=COPY或者其他因素导致修改语句使用了 ALGORITHM=COPY，那么重命名字段的语句将会执行失败。

To modify the length of a varchar field using In-place mode:

ALTER TABLE T1 algorithm=COLUMNVARCHAR(255);

Each time you modify the length of a varchar field, the value of the byte length required by the varchar internal encoding must remain the same, since the varchar value from 0-255 requires 1 bytes of length to encode the value, and when varchar requires 2 bytes of length to encode the value from 256 bytes, So when modifying the length of a varchar field, increasing from 0 bytes to 255 bytes, or increasing the length from greater than or equal to 256 bytes is supported in In-place mode, when the length from a less than 256 bytes is increased to a length greater than 256 bytes, Since the byte length required for the varchar intrinsic encoded value is changed from 1 bytes to 2 bytes in length, the In-place mode cannot be used at this time, only the algorithm=copy mode can be used, for example, the following statement that modifies the varchar length will fail:

ALTER TABLE T1 algorithm=COLUMNVARCHAR(N);
ERROR 0a000:algorithm= is not supported. Reason:cannotChangecolumn type INPLACE. Try algorithm=COPY.

In-place mode is not supported when the length of the varchar field is reduced, and table copy () is required at this time ALGORITHM=COPY .

To add a delete foreign key:

ALTER TABLE ADD CONSTRAINT FOREIGN KEY Index REFERENCES tbl2 (col2) referential_actions; ALTER TABLE DROP FOREIGN KEY Fk_name;

Whenforeign_key_checks参数为disable时，创建外键是online的。删除外键时，与该参数的取值无关，都是online状态的。

To delete a foreign key and index:

ALTER TABLE Table DROP FOREIGN KEY constraint DROP INDEX index;

When the foreign key is already being used for the table being modified, there are some additional restrictions on the online DDL:

If an ALTER TABLE operation is performed on a child table, when there isON UPDATE或者使用了CASCADE和SET NULL参数的ON DELETE子句时，导致子表的数据随着父表进行变化，

The ALTER TABLE statement on the child table waits for a transaction commit on the parent table, and the ALTER TABLE operation on the parent table also waits for the related transaction commit on the child table.

Because the read and write operations on the table are not affected during DDL operations, the response efficiency of the application is increased.

Because the in-place operation does not require rebuild table, it saves disk IO and CPU overhead, minimizes the load on the database, and provides better performance during DDL execution.

Compared to the copy table,in-place operation, only a small amount of data is read into the buffer pool, avoiding the flushing of frequently used data from the buffer pool, which, in previous versions, resulted in degraded system performance.

Locking options for Online DDL

Enforcing a tighter lock mode with the lock clause, if the LOCK clause specifies that the locks are less stringent than the minimum lock severity of some DDL, then many will be error-

LOCK=NONE:

Allow concurrent queries and DML

LOCK=SHARED:

Concurrent queries are allowed, but DML is blocked

LOCK=DEFAULT:

Try to satisfy the maximum concurrency if no lock clause is specified, this mode is defaulted by default

LOCK=EXCLUSIVE:

Blocking concurrent queries and DML

Use this pattern when the primary concern is to complete the DDL in the shortest possible time, while concurrent queries and DML execution are not important

In most cases, the online DDL operation on the table waits for a transaction commit or rollback that is currently accessing the table, because a temporary exclusive access to the table is required when the DLL statement is being prepared.

Similarly, the online DDL requires a short, exclusive access to the table before it is complete. Therefore, if the transaction running on the table takes too long to execute, the online DDL waits for an exclusive access timeout.

Not to be continued

This blog has just opened, the purpose of my work and learning experience in this sharing, because the previous information is disorganized, too fragmented, so the time to organize a few, many places also did not describe clearly, in the follow-up process will be further organized.

MySQL Online DDL detailed