MySQL 8.0 Reference Manual（读书笔记81节-- InnoDB and Online DDL (1)）
1. 概述
The online DDL feature provides support for instant and in-place table alterations and concurrent DML. Benefits of this feature include:
• Improved responsiveness【rɪ'spɒnsɪvnəs 响应性;灵敏度;敏感性;响应度;易起反应;】 and availability【əˌveɪlə'bɪləti 可利用性;可利用；可用性；有用（效）性；使用价值；（有效）利用率；工作效率;】 in busy production environments, where making a table unavailable for minutes or hours is not practical【ˈpræktɪkl 实际的;适用的;切实可行的;有用的;真实的;明智的;几乎完全的;客观存在的;心灵手巧的；】----在实际生产中,让一个表在分钟级别\小时级别 不可用,都是不现时的,不符合要求的.
• For in-place operations, the ability to adjust the balance between performance and concurrency during DDL operations using the LOCK clause.
• Less disk space usage and I/O overhead【ˌoʊvərˈhed 开销;经常费用;经常开支;】 than the table-copy method. --更少的磁盘空间使用和更低的IO消耗
注意:ALGORITHM=INSTANT support is available for ADD COLUMN and other operations in MySQL 8.0.12.
Typically, you do not need to do anything special to enable online DDL. By default, MySQL performs the operation instantly or in place, as permitted, with as little locking as possible. ---一般会自动选择好的方法
You can control aspects of a DDL operation using the ALGORITHM and LOCK clauses of the ALTER TABLE statement. These clauses are placed at the end of the statement, separated from the table and column specifications by commas. For example:---也可以显示指定
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
The LOCK clause may be used for operations that are performed in place and is useful for fine-tuning the degree of concurrent access to the table during operations. Only LOCK=DEFAULT is supported for operations that are performed instantly. The ALGORITHM clause is primarily intended for performance comparisons【kəmˈpɛrəsənz 比较;对比;相比;】 and as a fallback to the older table-copying behavior in case you encounter any issues. For example:
• To avoid accidentally【æksəˈdɛntəli 意外地;出其不意地；不知不觉地;】making the table unavailable for reads, writes, or both, during an in-place ALTER TABLE operation, specify a clause on the ALTER TABLE statement such as LOCK=NONE (permit reads and writes) or LOCK=SHARED (permit reads). The operation halts【hɔːlts (使)停止，停下;】immediately if the requested level of concurrency is not available.
• To compare performance between algorithms, run a statement with ALGORITHM=INSTANT, ALGORITHM=INPLACE and ALGORITHM=COPY. You can also run a statement with the old_alter_table configuration option enabled to force the use of ALGORITHM=COPY.
• To avoid tying up【tying up 捆绑;欲望捆绑;】 the server with an ALTER TABLE operation that copies the table, include ALGORITHM=INSTANT or ALGORITHM=INPLACE. The statement halts immediately if it cannot use the specified algorithm.----不行的话,就会立即报错退出的
2.Index Operations
The following table provides an overview of online DDL support for index operations. An asterisk indicates additional information, an exception, or a dependency.
| Operation | Instant | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
| Creating or adding a secondary index | NO | YES | NO | YES | NO |
| Dropping an index | NO | YES | NO | YES | YES |
| Renaming an index | NO | YES | NO | YES | YES |
| Adding a FULLTEXT index | NO | YES* | NO* | NO | NO |
| Adding a SPATIAL index | NO | YES | NO | NO | NO |
| Changing the index type | YES | YES | NO | YES | YES |
说明
• Creating or adding a secondary index
The table remains available for read and write operations while the index is being created. The CREATE INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table.
Online DDL support for adding secondary indexes means that you can generally speed the overall process of creating and loading a table and associated indexes by creating the table without secondary indexes, then adding secondary indexes after the data is loaded.
A newly created secondary index contains only the committed data in the table at the time the CREATE INDEX or ALTER TABLE statement finishes executing. It does not contain any uncommitted values, old versions of values, or values marked for deletion but not yet removed from the old index.
Some factors affect the performance, space usage, and semantics of this operation.
• Dropping an index
The table remains available for read and write operations while the index is being dropped. The DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects【rɪˈflekts 反映；反射(声、光、热等)；显示，表明，表达(事物的自然属性或人们的态度、情感等)；映出(影像)】 the most recent【ˈriːsnt 最近的;近来的;新近的;】 contents of the table.
• Adding a FULLTEXT index
Adding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Additional FULLTEXT indexes may be added without rebuilding the table.
3.Primary Key Operations
The following table provides an overview of online DDL support for primary key operations. An asterisk【ˈæstərɪsk 星号(置于词语旁以引起注意或另有注释)】 indicates additional information, an exception, or a dependency.
| Operation | Instant | In Place | Rebuilds Table | Permits Concurrent DML | Only Modifies Metadata |
| Adding a primary key | NO | Yes* | Yes* | Yes | NO |
| Dropping a primary key | NO | No | Yes | NO | NO |
| Dropping a primary key and adding another | NO | Yes | Yes | Yes | NO |
• Adding a primary key
Rebuilds the table in place. Data is reorganized substantially【səbˈstænʃəli 基本上;大体上;非常;大大地;总的来说;】, making it an expensive operation. ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.
Restructuring the clustered index always requires copying of table data. Thus, it is best to define the primary key when you create a table, rather than issuing ALTER TABLE ... ADD PRIMARY KEY later.
When you create a UNIQUE or PRIMARY KEY index, MySQL must do some extra work. For UNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, MySQL also checks that none of the PRIMARY KEY columns contains a NULL.
When you add a primary key using the ALGORITHM=COPY clause, MySQL converts NULL values in the associated【əˈsoʊsieɪtɪd 相关的;(用于联合企业的名称)联合的;有关联的;有联系的;】 columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for DATETIME. This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using ALGORITHM=INPLACE is only permitted when the SQL_MODE setting includes the strict_trans_tables or strict_all_tables flags; when the SQL_MODE setting is strict, ALGORITHM=INPLACE is permitted, but the statement can still fail if the requested primary key columns contain NULL values. The ALGORITHM=INPLACE behavior is more standard-compliant.
If you create a table without a primary key, InnoDB chooses one for you, which can be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify the PRIMARY KEY clause as part of the CREATE TABLE statement.
MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is renamed with the name of the original table, and the original table is dropped from the database.
The online performance enhancements that apply to operations on secondary indexes do not apply to the primary key index. The rows of an InnoDB table are stored in a clustered index organized based on the primary key, forming what some database systems call an “index-organized table”. Because the table structure is closely tied to the primary key, redefining the primary key still requires copying the data.
When an operation on the primary key uses ALGORITHM=INPLACE, even though the data is still copied, it is more efficient than using ALGORITHM=COPY because:
• No undo logging or associated redo logging is required for ALGORITHM=INPLACE. These operations add overhead to DDL statements that use ALGORITHM=COPY.
• The secondary index entries are pre-sorted, and so can be loaded in order.
• The change buffer is not used, because there are no random-access inserts into the secondary indexes.
• Dropping a primary key
ALTER TABLE tbl_name DROP PRIMARY KEY, ALGORITHM=COPY;
Only ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.
• Dropping a primary key and adding another
ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
Data is reorganized substantially, making it an expensive operation.