MySQL 8.0 Reference Manual（讀書筆記83節-- InnoDB and Online DDL (3)）

1. Foreign Key Operations

The following table provides an overview of online DDL support for foreign key operations. An asterisk【ˈæstərɪsk 星號(置於詞語旁以引起註意或另有註釋);】 indicates additional information, an exception, or a dependency.

• Adding a foreign key constraint

The INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.

ALTER TABLE tbl1 ADD CONSTRAINT fk_name FOREIGN KEY index (col1)
 REFERENCES tbl2(col2) referential_actions;

• Dropping a foreign key constraint

ALTER TABLE tbl DROP FOREIGN KEY fk_name;

Dropping a foreign key can be performed online with the foreign_key_checks option enabled or disabled.

If you do not know the names of the foreign key constraints on a particular table, issue the following statement and find the constraint name in the CONSTRAINT clause for each foreign key:

SHOW CREATE TABLE table\G

Or, query the Information Schema TABLE_CONSTRAINTS table and use the CONSTRAINT_NAME and CONSTRAINT_TYPE columns to identify the foreign key names.

You can also drop a foreign key and its associated index in a single statement:

ALTER TABLE table DROP FOREIGN KEY constraint, DROP INDEX index;

補充說明

If foreign keys are already present in the table being altered (that is, it is a child table containing a FOREIGN KEY ... REFERENCE clause), additional restrictions【riˈstrɪkʃənz 限制;約束;制約因素;限制規定;限製法規;】 apply to online DDL operations, even those not directly involving the foreign key columns:

• An ALTER TABLE on the child table could wait for another transaction to commit, if a change to the parent table causes associated changes in the child table through an ON UPDATE or ON DELETE clause using the CASCADE or SET NULL parameters.

• In the same way, if a table is the parent table in a foreign key relationship, even though it does not contain any FOREIGN KEY clauses, it could wait for the ALTER TABLE to complete if an INSERT, UPDATE, or DELETE statement causes an ON UPDATE or ON DELETE action in the child table.

2. Table Operations

The following table provides an overview of online DDL support for table operations. An asterisk indicates additional information, an exception, or a dependency【dɪˈpendənsi (尤指不正常或不必要的)依靠，依賴;附屬國;附屬地;】.

• Changing the ROW_FORMAT

ALTER TABLE tbl_name ROW_FORMAT = row_format, ALGORITHM=INPLACE, LOCK=NONE;

Data is reorganized substantially【səbˈstænʃəli 基本上;大體上;非常;大大地;總的來說;】, making it an expensive operation.

• Changing the KEY_BLOCK_SIZE

ALTER TABLE tbl_name KEY_BLOCK_SIZE = value, ALGORITHM=INPLACE, LOCK=NONE;

Data is reorganized substantially, making it an expensive operation.

• Setting persistent【pərˈsɪstənt 持續的;持久的;堅持不懈的;執著的;不屈不撓的;反覆出現的;連綿的;】 table statistics options

ALTER TABLE tbl_name STATS_PERSISTENT=0, STATS_SAMPLE_PAGES=20, STATS_AUTO_RECALC=1, ALGORITHM=INPLACE, LOCK=NONE;

Only modifies table metadata.

Persistent statistics include STATS_PERSISTENT, STATS_AUTO_RECALC, and STATS_SAMPLE_PAGES.

• Specifying a character set

ALTER TABLE tbl_name CHARACTER SET = charset_name, ALGORITHM=INPLACE, LOCK=NONE;

Rebuilds the table if the new character encoding is different.

• Converting a character set

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name, ALGORITHM=COPY;

Rebuilds the table if the new character encoding is different.

• Optimizing a table

OPTIMIZE TABLE tbl_name;

In-place operation is not supported for tables with FULLTEXT indexes. The operation uses the INPLACE algorithm, but ALGORITHM and LOCK syntax is not permitted.

• Rebuilding a table with the FORCE option

ALTER TABLE tbl_name FORCE, ALGORITHM=INPLACE, LOCK=NONE;

Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.

• Performing a "null" rebuild

ALTER TABLE tbl_name ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

Uses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.

• Renaming a table

ALTER TABLE old_tbl_name RENAME TO new_tbl_name, ALGORITHM=INSTANT;

Renaming a table can be performed instantly or in place. MySQL renames files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables.) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

3.Tablespace Operations

The following table provides an overview of online DDL support for tablespace operations.

• Renaming a general tablespace

ALTER TABLESPACE tablespace_name RENAME TO new_tablespace_name;

ALTER TABLESPACE ... RENAME TO uses the INPLACE algorithm but does not support the ALGORITHM clause.

• Enabling or disabling general tablespace encryption

ALTER TABLESPACE tablespace_name ENCRYPTION='Y';

ALTER TABLESPACE ... ENCRYPTION uses the INPLACE algorithm but does not support the ALGORITHM clause.

• Enabling or disabling file-per-table tablespace encryption

ALTER TABLE tbl_name ENCRYPTION='Y', ALGORITHM=COPY;

4.Online DDL Performance and Concurrency

Online DDL improves several aspects of MySQL operation:

• Applications that access the table are more responsive【rɪˈspɑːnsɪv 反應敏捷的;反應熱烈的;熱情的;反應敏捷;反應積極;】 because queries and DML operations on the table can proceed while the DDL operation is in progress. Reduced locking and waiting for MySQL server resources leads to greater scalability, even for operations that are not involved in the DDL operation. 

• Instant operations only modify metadata in the data dictionary. An exclusive metadata lock on the table may be taken briefly during the execution phase of the operation. Table data is unaffected, making operations instantaneous. Concurrent DML is permitted.

• Online operations avoid the disk I/O and CPU cycles associated with the table-copy method, which minimizes overall load on the database. Minimizing load helps maintain good performance and high throughput during the DDL operation.

• Online operations read less data into the buffer pool than table-copy operations, which reduces purging of frequently accessed data from memory. Purging of frequently accessed data can cause a temporary performance dip after a DDL operation.

4.1 The LOCK clause

 By default, MySQL uses as little locking as possible during a DDL operation. The LOCK clause can be specified for in-place operations and some copy operations to enforce more restrictive locking, if required. If the LOCK clause specifies a less restrictive level of locking than is permitted for a particular DDL operation, the statement fails with an error. LOCK clauses are described below, in order of least to most restrictive:

 • LOCK=NONE:

Permits concurrent【kənˈkɜːrənt 同時發生的;同意的，一致的;】 queries and DML. For example, use this clause for tables involving【ɪnˈvɑːlvɪŋ 需要;影響;(使)參加，加入;包含;牽涉;牽連;使成為必然部分(或結果);】 customer signups or purchases【ˈpɜːrtʃəsɪz 購買;採購;買;】, to avoid making the tables unavailable during lengthy DDL operations.

• LOCK=SHARED:

Permits concurrent queries but blocks DML.

For example, use this clause on data warehouse tables, where you can delay data load operations until the DDL operation is finished, but queries cannot be delayed for long periods【ˈpɪriədz 時期;(人生或國家歷史的)階段，時代;一段時間;紀(地質年代，代下分紀);】.

• LOCK=DEFAULT:

Permits as much concurrency【併發】 as possible (concurrent queries, DML, or both). Omitting【əˈmɪtɪŋ 忽略;遺漏;刪除;漏掉;不做;未能做;】 the LOCK clause is the same as specifying LOCK=DEFAULT.

Use this clause when you do not expect the default locking level of the DDL statement to cause any availability problems for the table.

• LOCK=EXCLUSIVE:

Blocks concurrent queries and DML.

Use this clause if the primary concern is finishing the DDL operation in the shortest amount of time possible, and concurrent query and DML access is not necessary. You might also use this clause if the server is supposed to be idle【ˈaɪdl 空閑的;閑置的;懶惰的;閑散的;懈怠的;沒有工作的;漫無目的的;】, to avoid unexpected table accesses.

 4.2 Online DDL and Metadata Locks

Online DDL operations can be viewed as having three phases【ˈfeɪzɪz 階段;時期;】:

• Phase 1: Initialization

In the initialization phase, the server determines how much concurrency【併發;】 is permitted during the operation, taking into account【考慮到;慮及;】 storage engine capabilities, operations specified in the statement, and user-specified ALGORITHM and LOCK options. During this phase, a shared upgradeable metadata lock is taken to protect the current table definition.

• Phase 2: Execution

In this phase, the statement is prepared and executed. Whether the metadata lock is upgraded to exclusive depends on the factors assessed in the initialization phase. If an exclusive metadata lock is required, it is only taken briefly during statement preparation.

• Phase 3: Commit Table Definitio

In the commit table definition phase, the metadata lock is upgraded to exclusive to evict the old table definition and commit the new one. Once granted, the duration of the exclusive metadata lock is brief.

Due to the exclusive metadata lock requirements outlined above, an online DDL operation may have to wait for concurrent transactions that hold metadata locks on the table to commit or rollback. Transactions started before or during the DDL operation can hold metadata locks on the table being altered. In the case of a long running or inactive transaction, an online DDL operation can time out waiting for an exclusive metadata lock. Additionally, a pending exclusive metadata lock requested by an online DDL operation blocks subsequent transactions on the table.

The following example demonstrates an online DDL operation waiting for an exclusive metadata lock, and how a pending metadata lock blocks subsequent transactions on the table.

Session 1:

mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;

The session 1 SELECT statement takes a shared metadata lock on table t1.

Session 2:

mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

The online DDL operation in session 2, which requires an exclusive metadata lock on table t1 to commit table definition changes, must wait for the session 1 transaction to commit or roll back.

Session 3:

mysql> SELECT * FROM t1;

The SELECT statement issued in session 3 is blocked waiting for the exclusive metadata lock requested by the ALTER TABLE operation in session 2 to be granted.

You can use SHOW FULL PROCESSLIST to determine if transactions are waiting for a metadata lock.

mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************
 Id: 5
 User: root
 Host: localhost
 db: test
Command: Query
 Time: 44
 State: Waiting for table metadata lock
 Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
 Id: 7
 User: root
 Host: localhost
 db: test
Command: Query
 Time: 5
 State: Waiting for table metadata lock
 Info: SELECT * FROM t1
4 rows in set (0.00 sec)

Metadata lock information is also exposed【ɪkˈspoʊzd 暴露;露出;揭露;使面臨，使遭受(危險或不快);顯露;揭穿;】 through the Performance Schema metadata_locks table【通過系統表也可以查看】, which provides information about metadata lock dependencies between sessions, the metadata lock a session is waiting for, and the session that currently holds the metadata lock.

4.3 Online DDL Performance

The performance of a DDL operation is largely determined by【很大程度上被...所決定】 whether the operation is performed instantly, in place, and whether it rebuilds the table.

To assess the relative performance of a DDL operation, you can compare results using ALGORITHM=INSTANT, ALGORITHM=INPLACE, and ALGORITHM=COPY. A statement can also be run with old_alter_table enabled to force the use of ALGORITHM=COPY.

For DDL operations that modify table data, you can determine whether a DDL operation performs changes in place or performs a table copy by looking at the “rows affected” value displayed after the command finishes. For example:

• Changing the default value of a column (fast, does not affect the table data):

Query OK, 0 rows affected (0.07 sec)

• Adding an index (takes time, but 0 rows affected shows that the table is not copied):

Query OK, 0 rows affected (21.42 sec)

• Changing the data type of a column (takes substantial time and requires rebuilding all the rows of the table):

Query OK, 1671168 rows affected (1 min 35.54 sec)

Before running a DDL operation on a large table, check whether the operation is fast or slow as follows:

1. Clone the table structure.

2. Populate the cloned table with a small amount of data.

3. Run the DDL operation on the cloned table.

4. Check whether the “rows affected” value is zero or not. A nonzero value means the operation copies table data, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replica server one at a time.

說明

For a greater understanding of the MySQL processing associated with a DDL operation, examine Performance Schema and INFORMATION_SCHEMA tables related to InnoDB before and after DDL operations to see the number of physical reads, writes, memory allocations, and so on.

Because there is some processing work involved with recording the changes made by concurrent DML operations, then applying those changes at the end, an online DDL operation could take longer overall than the table-copy mechanism that blocks table access from other sessions. The reduction in raw performance is balanced against better responsiveness for applications that use the table. When evaluating the techniques for changing table structure, consider end-user perception of performance, based on factors such as load times for web pages.