本文首發於公眾號：Hunter後端 原文鏈接：MySQL面試必備一之索引 在面試過程中，會有一些關於 MySQL 索引相關的問題，以下總結了一些： MySQL 的數據存儲使用的是什麼索引結構 B+ 樹的結構是什麼樣子 什麼是複合索引、聚簇索引、覆蓋索引 什麼是最左匹配原則 數據 B+ 樹中是如何查詢 ...
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.
