在 MySQL 5.6 之前，InnoDB 在索引構(gòu)建期間會(huì)對(duì)表進(jìn)行排它鎖定，這意味著其他會(huì)話無(wú)法讀取或修改表中的數(shù)據(jù)，從而導(dǎo)致長(zhǎng)時(shí)間阻塞和性能問題。

自 MySQL 5.6 起，InnoDB 開始采用一種名為“Online DDL”的技術(shù)，允許在不阻塞其他會(huì)話的情況下創(chuàng)建或刪除索引。Online DDL 針對(duì)不同的操作提供了多種實(shí)現(xiàn)方式，包括 COPY、INSTANT 和 INPLACE。

由于 DDL 涉及多種操作，如索引創(chuàng)建、字段增加和外鍵添加等，因此不同操作的支持方式也各不相同。具體支持方式可參考 MySQL 官方文檔（https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html）。

以索引創(chuàng)建為例：

圖片

從上文可見，當(dāng)我們創(chuàng)建、刪除或重命名索引時(shí)，會(huì)采用“in place”的模式。

需要注意的是，盡管 Online DDL 能夠減少鎖定時(shí)間和對(duì)性能的影響，但在索引構(gòu)建期間仍可能出現(xiàn)鎖定和阻塞情況。例如，在添加索引時(shí)，如果表中存在大量未提交的事務(wù)，則需要等待這些事務(wù)提交后才能開始索引構(gòu)建。因此，建議在非高峰時(shí)段進(jìn)行此類操作，以避免影響用戶的正常使用。在執(zhí)行任何 DDL 操作之前，最好進(jìn)行充分的測(cè)試和規(guī)劃，并確保有備份和回滾計(jì)劃，以應(yīng)對(duì)意外情況。

擴(kuò)展知識(shí)

什么是 Online DDL

DDL，即數(shù)據(jù)定義語(yǔ)言（Data Definition Language），用于定義數(shù)據(jù)庫(kù)結(jié)構(gòu)的操作。DDL 操作包括創(chuàng)建、修改和刪除數(shù)據(jù)庫(kù)中的表、索引、視圖、約束等數(shù)據(jù)庫(kù)對(duì)象，而不涉及實(shí)際數(shù)據(jù)的操作。以下是一些常見的 DDL 操作：

• CREATE（創(chuàng)建）
• ALTER（修改）
• DROP（刪除）
• TRUNCATE（截?cái)啵?/li>

相對(duì)應(yīng)的是 DML，即數(shù)據(jù)操作語(yǔ)言（Data Manipulation Language），用于操作數(shù)據(jù)。包括我們常用的 INSERT、DELETE 和 UPDATE 等操作。

在 MySQL 5.6 之前，所有的 ALTER 操作實(shí)際上都會(huì)阻塞 DML 操作，例如添加或刪除字段、添加或刪除索引等，都會(huì)導(dǎo)致表被鎖定。

然而，在 MySQL 5.6 中引入了 Online DDL，它是 MySQL 5.6 提出的一種加速 DDL 的方案，旨在盡可能保證 DDL 期間不會(huì)阻塞 DML 操作。但需要注意的是，并非所有的 DDL 語(yǔ)句都會(huì)利用 Online DDL 進(jìn)行加速。

Online DDL 的優(yōu)點(diǎn)在于可以減少阻塞，它是 MySQL 內(nèi)置的一種優(yōu)化手段。但需注意的是，在 DDL 開始和結(jié)束階段，都需要獲取 MDL 鎖，如果在獲取鎖時(shí)存在未提交的事務(wù)，則 DDL 可能因?yàn)殒i定失敗而被阻塞，從而影響性能。

此外，如果 Online DDL 操作失敗，其回滾操作可能會(huì)造成較高的成本。長(zhǎng)時(shí)間運(yùn)行的 Online DDL 操作也可能導(dǎo)致主從同步的延遲。

DDL 算法

在 MySQL 5.6 支持 Online DDL 之前，存在兩種 DDL 算法，分別是 COPY 和 INPLACE。

我們可以使用以下 SQL 來(lái)指定 DDL 算法：

ALTER TABLE paidaxing_ddl_test ADD PRIMARY KEY (id) ,ALGORITHM=INPLACE,LOCK=NONE

copy 算法原理

1. 創(chuàng)建一張臨時(shí)表。
2. 對(duì)原表加共享 MDL 鎖，阻止對(duì)原表的寫操作，僅允許查詢操作。
3. 逐行將原表數(shù)據(jù)拷貝到臨時(shí)表中，且無(wú)需進(jìn)行排序。
4. 數(shù)據(jù)拷貝完成后，將原表鎖升級(jí)為排他 MDL 鎖，阻止對(duì)原表的讀寫操作。
5. 對(duì)臨時(shí)表進(jìn)行重命名操作，并創(chuàng)建索引，完成 DDL 操作。

INPLACE 算法原理

INPLACE 算法是在 MySQL 5.5 中引入的，旨在優(yōu)化索引的創(chuàng)建和刪除過程的效率。其原理是盡可能地使用原地算法進(jìn)行 DDL 操作，而不是重新創(chuàng)建或復(fù)制表。

1. 創(chuàng)建索引數(shù)據(jù)字典。
2. 對(duì)原表加共享 MDL 鎖，阻止對(duì)原表的寫操作，只允許查詢操作。
3. 根據(jù)聚集索引的順序，查詢表中的數(shù)據(jù)，并提取所需的索引列數(shù)據(jù)。將提取的索引數(shù)據(jù)進(jìn)行排序，并插入到新的索引頁(yè)中。
4. 等待當(dāng)前表的所有只讀事務(wù)提交。
5. 索引創(chuàng)建完成。

MySQL 中的 INPLACE 算法實(shí)際上分為兩種：

• inplace-no-rebuild：對(duì)二級(jí)索引的增刪改查、修改變長(zhǎng)字段長(zhǎng)度（例如：varchar）、重命名列名等操作都不需要重建原表。
• inplace-rebuild：修改主鍵索引、增加或刪除列、修改字符集、創(chuàng)建全文索引等操作需要重建原表。

OnlineDDL 算法

前面提到，ALGORITHM 可以指定 DDL 操作的算法，目前主要支持以下幾種：

1. COPY 算法
2. INPLACE 算法
3. INSTANT 算法：MySQL 8.0.12 引入的新算法，目前只支持添加列等少量操作。它利用了 8.0 新的表結(jié)構(gòu)設(shè)計(jì)，可以直接修改表的元數(shù)據(jù)，省去了重建原表的過程，從而極大地縮短了 DDL 語(yǔ)句的執(zhí)行時(shí)間。對(duì)于其他類型的改表語(yǔ)句，默認(rèn)使用 inplace 算法。關(guān)于 INSTANT 支持的場(chǎng)景可參考官方文檔 [Online DDL Operations]：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html。
4. DEFAULT：如果不指定 ALGORITHM，MySQL 會(huì)自行選擇默認(rèn)算法。它優(yōu)先考慮 INSTANT，其次是 INPLACE，然后是 COPY。

以下是 MySQL 官網(wǎng)上給出的 Online DDL 對(duì)索引操作的支持情況：

圖片

OnlineDDL 的原理

以下是 Online DDL 的整體步驟，主要分為 Prepare 階段、DDL 執(zhí)行階段以及 Commit 階段。

Prepare 階段：

1. 創(chuàng)建臨時(shí) frm 文件。
2. 加 EXCLUSIVE-MDL 鎖，阻止讀寫操作。
3. 根據(jù) ALTER 類型，確定執(zhí)行方式（copy/online-rebuild/online-norebuild）。需要注意，如果使用 copy 算法，則不是 Online DDL。
4. 更新數(shù)據(jù)字典的內(nèi)存對(duì)象。
5. 分配 row_log 對(duì)象，記錄 Online DDL 過程中增量的 DML。
6. 生成新的臨時(shí) idb 文件。

Execute 階段：

1. 降級(jí) EXCLUSIVE-MDL 鎖為 SHARED-MDL 鎖，允許讀寫操作。
2. 掃描原表聚集索引的每一條記錄。
3. 遍歷新表的聚集索引和二級(jí)索引，逐一處理。
4. 根據(jù)原表中的記錄構(gòu)造對(duì)應(yīng)的索引項(xiàng)。
5. 將構(gòu)造的索引項(xiàng)插入 sort_buffer 塊排序。
6. 將 sort_buffer 塊更新到新表的索引上。
7. 記錄 Online DDL 執(zhí)行過程中產(chǎn)生的增量（online-rebuild）。
8. 重放 row_log 中的操作到新表的索引上（online-not-rebuild 數(shù)據(jù)是在原表上更新）。
9. 重放 row_log 中的 DML 操作到新表的數(shù)據(jù)行上。

Commit 階段：

1. 升級(jí)到 EXCLUSIVE-MDL 鎖，阻止讀寫操作。
2. 重做 row_log 中最后一部分增量。
3. 更新 InnoDB 的數(shù)據(jù)字典表。
4. 提交事務(wù)，寫 redo log。
5. 修改統(tǒng)計(jì)信息。
6. 重命名臨時(shí) ibd 文件，frm 文件。
7. 變更完成，釋放 EXCLUSIVE-MDL 鎖。

盡管 Prepare 階段和 Commit 階段也加了 EXCLUSIVE-MDL 鎖，但操作非常輕量，因此耗時(shí)較低。Execute 階段允許讀寫操作，并通過 row_log 記錄期間的變更數(shù)據(jù)記錄，最終應(yīng)用這些變更到新表中，從而實(shí)現(xiàn) Online DDL 的效果。