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:
- Improve response efficiency and availability in busy production environments.
- You can use the LOCK clause to reconcile performance and concurrency.
- Use less disk space and IO overhead than the algorithm=copy algorithm.
DDL Online Status Support table:
Operation |
| in place
Rebuilds Table |
permits Concurrent DML | Only
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_checks Can 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 |
VARCHAR Size 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