Reference Guide: MySQL & MariaDB Online DDL

MySQL Tutorial This column introduces and guides MySQL & MariaDB Online DDL.

Overview

In early MySQL versions, DDL operations (such as creating indexes, etc.) usually required locking the data table. DML operations will be blocked during the process, affecting normal business. MySQL 5.6 and MariaDB 10.0 begin to support Online DDL, which can perform DDL operations without affecting the normal execution of DML. Directly executing DDL operations online is basically invisible to users (some operations have an impact on performance).

Different versions of databases have certain differences in their support for various DDL statements. This article will summarize the support of MySQL and MariaDB for Online DDL. When you need to perform DDL operations, you can refer to this article. Online DDL Support section.

This article will continue to be revised and updated. For the latest content, please refer to my Programmer Growth Plan project on GITHUB. Stars are welcome. For more exciting content, please follow me.

In the ALTER TABLE statement, Online DDL is supported through the ALGORITHM and LOCK statements:

• ALGORITHM - Control how DDL operations are performed and which algorithm is used
• LOCK - Control the level of table locks allowed when executing DDL

ALTER TABLE tab ADD COLUMN c varchar(50), ALGORITHM=INPLACE, LOCK=NONE;复制代码

ALGORITHM Supported algorithms

##INPLACE Avoid table copying and reconstruction as much as possible. A more accurate name should be NOCOPYThis algorithm is a subset of the INSTANT is used to avoid

NOCOPY Algorithm support: MariaDB 10.3.2, MySQL does not support this algorithm.

INSTANT Algorithm support: MariaDB 10.3.2, MySQL 8.0.12.

Algorithm usage rules:

• If the user-specified algorithm is COPY, InnoDB uses the COPY algorithm.
• If the user specifies an algorithm other than COPY, InnoDB will select the most efficient algorithm based on algorithm efficiency. In the worst case, the user-specified algorithm will be used. For example, if the user specifies ALOGRITHM = NOCOPY, InnoDB will select the most efficient algorithm supported from (NOCOPY, INSTANT).

The MySQL service is mainly composed of Server layer and Storage engine layer. The Server layer contains the MySQL database. Some core functions, all built-in functions, cross-storage engine functions such as stored procedures, triggers, views, etc. The storage engine layer is responsible for data storage and reading, and adopts a plug-in architecture model.

COPY algorithm acts on the Server layer, and its execution process is at the Server layer, so all storage engines support the use of this algorithm. The execution process is as shown below

INPLACE algorithm acts on the storage engine layer and is a unique DDL algorithm of the InnoDB storage engine. The execution process is shown in the figure below

LOCK Policy

By default, MySQL/MariaDB will use as few locks as possible during the execution of DDL. If necessary, you can use the LOCK clause to control the level of table locks allowed when executing DDL. If the restriction level required by the specified operation does not meet (EXCLUSIVE > SHARED > NONE), the statement execution fails and an error is reported.

ALGORITHM	Description
DEFAULT	Default algorithm, automatically uses the most efficient algorithm available
COPY	The most original way, supported by all storage engines, does not use Online DDL, when operating, will create a temporary table, perform full table copy and reconstruction, and write Redo Log and a large amount of Undo Log during the process, which requires adding read locks and is very inefficient
	ENGINE algorithm. It is up to the storage engine to decide how to implement . Some operations can take effect immediately ( For example, rename columns, change column default values, etc.), but some operations still require copying and rebuilding the entire table or part of the table (such as adding and deleting columns, adding primary keys, changing columns to NULL, etc.)
	INPLACE algorithm, used to avoid the reconstruction of the clustered index (primary key index) causing the reconstruction of the entire table , and also It is said that using this algorithm will prohibit any operation that causes clustered index reconstruction
	INPLACE The algorithm is extremely inefficient when data files need to be modified. All operations involving table copying and reconstruction will be prohibited

##DEFAULTUse current The smallest granular lock policy supported by the operationNONEDoes not acquire any table locks and allows all DML operations SHAREDAdd a shared lock (read lock) to the table, allowing only read-only DML operationsEXCLUSIVEAdd an exclusive lock to the table ( Write lock), no DML operations are allowed

为了避免执行 DDL 时，由于锁表导致生产服务不可用，在执行表结构变更语句时，可以添加 LOCK=NONE 子句，如果语句需要获取共享锁或者排它锁，则会直接报错，这样就可以避免意外锁表，造成线上服务不可用了。

Online DDL 执行过程

Online  DDL 操作主要分为三个阶段：

• 阶段 1：初始化

  在初始化阶段，服务器会根据存储引擎的能力，操作的语句和用户指定的 ALGORITHM 和 LOCK 选项来决定允许多大程度的并发。在这个阶段会创建一个 可升级的元数据共享锁（SU）来保护表定义。

• 阶段 2：执行

  这个阶段会 准备 并 执行 DDL 语句，根据 阶段 1 评估的结果来决定是否将元数据锁升级为 排它锁 （X），如果需要升级为排它锁，则只在 DDL 的 准备阶段 短暂的添加排它锁。

• 阶段 3：提交表定义

  在表定义的提交阶段，元数据锁会升级为排它锁来更新表的定义。独占排它锁的持续时间非常短。

元数据锁（Reference Guide: MySQL & MariaDB Online DDL，Metadata Lock）主要用于 DDL 和 DML 操作之间的并发访问控制，保护表结构（表定义）的一致，保证读写的正确性。Reference Guide: MySQL & MariaDB Online DDL 不需要显式的使用，在访问表时会自动加上。

由于上面三个阶段中对元数据锁的独占，  Online  DDL 过程必须等待已经持有元数据锁的并发事务提交或者回滚才能继续执行。

注意：当  Online  DDL 操作正在等待元数据锁时，该元数据锁会处于挂起状态，后续的所有事务都会被阻塞。在 MariaDB 10.3 之后，可以通过添加 NO WAIT 或者 WAIT n 来控制等待所得超时时间，超时立即失败。

ALTER TABLE tbl_name [WAIT n|NOWAIT] ...CREATE ... INDEX ON tbl_name (index_col_name, ...) [WAIT n|NOWAIT] ...DROP INDEX ... [WAIT n|NOWAIT]DROP TABLE tbl_name [WAIT n|NOWAIT] ...LOCK TABLE ... [WAIT n|NOWAIT]OPTIMIZE TABLE tbl_name [WAIT n|NOWAIT]RENAME TABLE tbl_name [WAIT n|NOWAIT] ...SELECT ... FOR UPDATE [WAIT n|NOWAIT]SELECT ... LOCK IN SHARE MODE [WAIT n|NOWAIT]TRUNCATE TABLE tbl_name [WAIT n|NOWAIT]复制代码

评估 Online DDL 操作的性能

Online DDL 操作的性能取决于是否发生了表的重建。在对大表执行 DDL 操作之前，为了避免影响正常业务操作，最好是先评估一下 DDL 语句的性能再选择如何操作。

1. 复制表结构，创建一个新的表
2. 在新创建的表中插入少量数据
3. 在新表上面执行 DDL 操作
4. 检查执行操作后返回的 rows affected 是否是 0。如果该值非 0，则意味着需要拷贝表数据，此时对 DDL 的上线需要慎重考虑，周密计划

比如

• 修改某一列的默认值（快速，不会影响到表数据）

  Query OK, 0 rows affected (0.07 sec)复制代码

• 添加索引（需要花费一些时间，但是 0 rows affected 说明没有发生表拷贝）

  Query OK, 0 rows affected (21.42 sec)复制代码

• 修改列的数据类型（需要花费很长时间，并且重建表）

  Query OK, 1671168 rows affected (1 min 35.54 sec)复制代码

由于在执行  Online  DDL 过程中需要记录并发执行的 DML 操作发生的变更，然后在执行完 DDL 操作之后再应用这些变更，因此使用  Online  DDL 操作花费的时间比不使用 Online 模式执行要更长一些。

Online  DDL 支持情况

INSTANT 算法支持：MariaDB 10.3.2+，MySQL 8.0.12+。NOCOPY 只支持 MariaDB 10.3.2 以上版本，不支持 MySQL，这里就暂且忽略了。

重点关注是否 重建表 和 支持并发 DML：不需要重建表，支持并发 DML 最佳。

Secondary index

Strategy	Description

##✅✅Rename index (⚠️MySQL 5.7, MariaDB 10.5.2)❌✅❌✅✅ADD ❌✅ ①❌ ①❌❌ Add ❌✅❌❌❌Modify index type✅✅Instructions:

Operation	INSTANT	INPLACE	Rebuild table	Concurrent DML	Only modify metadata
Create or add secondary index	❌	✅	❌	✅	❌
Delete index	❌	✅	❌
FULLTEXT Index
SPATIAL index (⚠️MySQL 5.7, MariaDB 10.2.2)
		##❌	✅	✅

① You need to rebuild the table when you add a full-text index field for the first time, but you don’t need it after that

• Primary Key

OperationAdd primary key##Delete primary key❌❌✅❌❌Delete a primary key and add a new one❌✅✅✅❌##Instructions:

INSTANT	INPLACE	Rebuild table	Concurrent DML	Modify metadata only
❌	✅ ②	✅ ②	✅	❌

Rebuilding a clustered index always requires copying table data (InnoDB is an "index-organized table"), so it is best to define the primary key when creating the table

If the table is created without specifying a primary key, InnoDB will Select the first

NOT NULL
UNIQUE
• index as the primary key, or use the system-generated KEY② For clustered indexes, use INPLACE mode is more efficient than
COPY
• mode: undo log and redo log will not be generated, the secondary index is ordered, so it can be loaded in order , no need to use change bufferNormal column

OperationINSTANTAdd column ✅ ③##✅❌ ③✅ ③❌Column Delete❌ ④✅✅✅❌✅##Change the order of columns❌ ⑫✅✅✅❌Set default value ✅✅❌✅✅Modify data type ❌❌✅❌❌Extension Length (⚠️MySQL 5.7, MariaDB 10.2.2) ❌ ⑬✅✅✅Remove default value of column✅✅##❌##✅✅Change the self-increment value❌✅❌✅❌ ⑦Set the column to NULL❌✅✅ ⑧ ✅❌ Set the column to NOT NULL❌✅ ⑨✅ ⑨✅❌Modify the definitions of the and columns✅ ✅✅

Description:

• ③ Concurrent DML: When inserting an auto-increment column, concurrent DML operations are not supported. When adding an auto-increment column, a large amount of data will be reorganized. Costly

• ③ Rebuild the table: When adding columns, MySQL 5.7 and earlier versions need to rebuild the table. MySQL 8.0 needs to rebuild the table when ALGORITHM=INPLACE,# No need to rebuild when ##ALGORITHM=INSTANT

• ③ INSTANT algorithm: When adding columns, use the

  INSTANT algorithm which has the following limitations

  Add column operations cannot be combined with other operations that do not support the
  • INSTANT algorithm into one ALTER TABLE statement
  New columns can only be added to the table Finally, it cannot be placed in front of other columns. After MariaDB 10.4, it is supported to add columns at any position
  • Cannot add columns to the table with
  • ROW_FORMAT=COMPRESSED
  Cannot Add columns to a table containing
  • FULLTEXT
  Cannot add columns to temporary tables, temporary tables only support
  • ALGORITHM=COPY
  Cannot Adding columns to a table residing in a data dictionary tablespace
  • The row size limit is not calculated when adding a column, the limit is checked when performing a DML operation to insert or update the table
• ④ When deleting columns, a large amount of data needs to be reorganized, which is costly. After MariaDB 10.4, deleting columns supports the INSTANT algorithm

• ⑤ When renaming a column, make sure to only change the column name and not the data type, so as to support concurrent DML operations

• ⑥ When extending the VARCHAR length, INPLACE is conditional. It must be ensured that the length bytes used to identify the length of the string remain unchanged (here are all bytes, not the character length of VARCHAR. The byte occupation is related to the character set used. Under the

  utf8 character set, One character occupies 3 bytes, utf8mb4 then 4 bytes)

  When the VARCHAR column length is 0-255 bytes, the length identifier occupies one byte
  • When the VARCHAR column length is greater than 255 bytes, the length identifier occupies two bytes
  Therefore, INPLACE only supports 0-255 bytes or 256 bytes to a larger length. VARCHAR column length reduction is not supported with INPLACE.

• ⑦ The change of the auto-increment column value is the modified value in the memory, not the data file

• ⑧ ⑨ Set the column value

  [ NOT] NULL, a large amount of data is reorganized, which is costly

• ⑩ Modify the column definitions of

  ENUM and SET types When, whether a table copy is needed depends on the number of existing elements and the position of inserted members

• ⑫ After MariaDB 10.4, column sorting supports the INSTANT algorithm

• ⑬ After MariaDB 10.4.3, InnoDB supports using the INSTANT algorithm to increase the length of the column, but there are some restrictions. For details, refer to Changing the Data Type of a Column

Generating columns

INPLACE	Rebuild table	Concurrent DML	Modify metadata only
				##Column Rename	❌
❌	✅ ⑤	✅
				VARCHAR
❌ ⑥
			ENUM	SET
❌ ⑩✅

OperationINSTANTINPLACERebuild tableConcurrent DMLModify metadata onlyAdd ❌❌✅❌❌Modify the sorting of ❌❌✅❌❌Delete❌✅✅✅❌Add ✅✅❌✅✅## Modify the sorting of VIRTUALDELETE VIRTUAL##Foreign Key

STORED column
STORED columns
STORED Column
VIRTUAL COLUMN
columns✅	❌	✅	❌	❌
COLUMN ✅	✅	❌	✅	✅

OperationINSTANTINPLACERebuild tableConcurrent DMLModify metadata onlyAdd foreign key constraints❌✅ ⑭❌✅✅❌

##Delete foreign key constraint

✅

❌

✅

✅

Note:

• ⑭ When adding a foreign key, the INPLACE algorithm is only supported when the
foreign_key_checks

option is disabled.

Table

##Optimize table ❌✅ ⑰✅✅❌USE ❌✅ ⑱✅✅❌Perform an empty rebuild❌✅ ⑲✅✅❌Rename table✅✅❌✅✅

Operation	INSTANT	INPLACE	Rebuild table	Concurrent DML	Only modify metadata
ModifyROW_FORMAT	❌	✅	✅	✅	❌
ModifyKEY_BLOCK_SIZE	❌	✅	✅	✅	❌
Set persistent table statistics	❌	✅	❌	✅	✅
Specified character set	❌	✅	✅ ⑮	❌	❌
Convert character set	❌	❌	✅ ⑯	❌	❌
FORCE Option Rebuild Table

Note:

⑮⑯ When the character sets are different, the table needs to be rebuilt
• ⑰⑱⑲ If the table contains
• FULLTEXT fields, it will not Support INPLACE

Table space

OperationINSTANTINPLACERebuild TableConcurrent DMLModify metadata only##Rename regular tablespaceEnable or disable regular tablespace encryptionEnable or disable file -per-tableLimitations

❌	✅	❌	✅	✅
❌	✅	❌	✅	❌
Table space encryption❌	❌	✅	❌	❌

A table copy occurs when creating an index on a temporary table

TEMPORARY TABLE
• If there is a # on the table ##ON...CASCADE
or
• ON...SET NULL constraints, then ALERT TABLE does not support the words LOCK=NONEBefore the Onlne DDL operation is completed, it must wait for the transaction that already holds the metadata lock in the related table to be committed or rolled back. During this process, new transactions in the related table will be blocked and cannot be executed.
When When executing DDL involving table reconstruction on a large table, the following limitations exist
• There is no mechanism to pause Online DDL operations or limit the I/O or CPU usage of Online DDL operations
• Rolling back an Online DDL operation is very costly if the operation fails. Long-running Online DDL may cause replication delays. Online DDL operations must be executed on the Master before they can be executed on the Slave. In this process, concurrently processed DML must wait for the DDL operation to be completed on the Slave before being executed.
  • Written at the end
  This article will continue to be revised and updated, please follow me for more exciting content.

More related free learning recommendations:

mysql tutorial

(Video)

The above is the detailed content of Reference Guide: MySQL & MariaDB Online DDL. For more information, please follow other related articles on the PHP Chinese website!