MySQL 5.6 及以后版本（尤其是 InnoDB 存儲(chǔ)引擎）引入的一項(xiàng)極其重要的功能，它允許數(shù)據(jù)庫(kù)管理員在執(zhí)行 ALTER TABLE 操作時(shí)，最大程度地減少對(duì)表鎖定和應(yīng)用程序可用性的影響。

核心目標(biāo)： 在 DDL 操作進(jìn)行時(shí)，允許對(duì)表進(jìn)行并發(fā)的讀?。⊿ELECT） 和寫(xiě)入（INSERT, UPDATE, DELETE） 操作。

一、Online DDL 是什么？

Online DDL 是 MySQL 5.6 版本引入，并在后續(xù)版本中不斷增強(qiáng)的一項(xiàng)功能。它允許你在執(zhí)行數(shù)據(jù)定義語(yǔ)言（DDL）操作時(shí)（如 ALTER TABLE），盡可能地減少對(duì)表的鎖定時(shí)問(wèn)，使得：

• 寫(xiě)操作（DML）：在 DDL 操作進(jìn)行的同時(shí)，應(yīng)用程序依然可以對(duì)表執(zhí)行 INSERT, UPDATE, DELETE 等操作，最大程度保證業(yè)務(wù)的連續(xù)性。
• 讀操作：SELECT 查詢(xún)通?？梢哉＿M(jìn)行，不受影響。

這與早期的 Copy Table 機(jī)制形成鮮明對(duì)比，早期方式需要全程鎖表，直到操作完成，對(duì)于大表來(lái)說(shuō)意味著長(zhǎng)時(shí)間的停機(jī)。

二、Online DDL 的三種主要算法

MySQL 在執(zhí)行 DDL 時(shí)，根據(jù)操作類(lèi)型的不同，底層主要采用三種算法。理解這些算法是理解 Online DDL 的關(guān)鍵。

2.1COPY（復(fù)制法）

過(guò)程：

• 創(chuàng)建一個(gè)與原始表結(jié)構(gòu)相同的臨時(shí)表（.frm，.ibd 等文件）。
• 在新的臨時(shí)表上執(zhí)行 DDL 操作。
• 將原始表的數(shù)據(jù)逐行復(fù)制到臨時(shí)表中。
• 在此期間，對(duì)原始表的寫(xiě)操作會(huì)被阻塞（通常只在數(shù)據(jù)拷貝的最后階段有短暫鎖表）。
• 數(shù)據(jù)復(fù)制完成后，用新的臨時(shí)表替換原始表，并刪除舊的表。

特點(diǎn)：

• 需要兩倍的存儲(chǔ)空間。
• 過(guò)程中大部分時(shí)間會(huì)阻塞寫(xiě)操作，影響業(yè)務(wù)。
• 是 MySQL 5.5 及之前版本的主要方式。

2.2 INPLACE （原地法）

過(guò)程：

無(wú)需創(chuàng)建臨時(shí)表文件，直接在原始表的存儲(chǔ)文件（如 InnoDB 的 .ibd 文件）上進(jìn)行操作。

通常分為兩個(gè)階段：

• 準(zhǔn)備階段（Prepare）：創(chuàng)建新的.frm文件，準(zhǔn)備數(shù)據(jù)字典更改。可能需要短暫的排他鎖（X鎖）。
• 執(zhí)行階段（Execute）：應(yīng)用更改到存儲(chǔ)引擎，這通常是操作中最耗時(shí)的部分。在此階段，允許并發(fā)的DML操作。

特點(diǎn)：

• 所需磁盤(pán)空間遠(yuǎn)少于 COPY 算法（通常只需要日志文件的空間）。
• 允許在執(zhí)行階段進(jìn)行并發(fā) DML，大大減少了鎖表時(shí)間。

2.3INSTANT (即刻法，MySQL 8.0+)

過(guò)程：

• 操作只修改數(shù)據(jù)字典（元數(shù)據(jù)），而不觸及表中的實(shí)際數(shù)據(jù)或索引。
• 例如，添加一個(gè)可為 NULL 且有默認(rèn)值的列，只需要在數(shù)據(jù)字典中記錄一下“這個(gè)表有這個(gè)列，默認(rèn)值是什么”，而不需要重建表或復(fù)制數(shù)據(jù)。

特點(diǎn)：

• 速度極快，通常能在毫秒級(jí)完成。
• 完全不阻塞任何 DML 操作，是真正的“Online”。
• 對(duì)存儲(chǔ)空間沒(méi)有額外要求。

三、Online DDL 的鎖機(jī)制

即使是 INPLACE 算法，也并非全程無(wú)鎖。Online DDL 涉及兩種主要的鎖：

• SHARED鎖（讀鎖）：在 DDL 的準(zhǔn)備階段，可能會(huì)短暫地獲取。允許其他會(huì)話讀，但阻塞寫(xiě)。
• EXCLUSIVE鎖（寫(xiě)鎖/排他鎖）：在 DDL 的開(kāi)始（準(zhǔn)備階段）和結(jié)束（提交階段）可能會(huì)短暫地獲取。此時(shí)會(huì)阻塞所有其他的讀和寫(xiě)操作。

關(guān)鍵點(diǎn)：Online DDL 的“Online”體現(xiàn)在其耗時(shí)的數(shù)據(jù)拷貝/重建階段（Execute階段）是不鎖表的，而只在元數(shù)據(jù)變更的瞬間需要短暫的排他鎖。這個(gè)瞬間通常非常短，可以忽略不計(jì)。

四 關(guān)鍵區(qū)別

4.1 生動(dòng)的比喻：給飛行中的飛機(jī)換引擎

想象一下，你要給一架正在飛行的飛機(jī)更換引擎（這相當(dāng)于對(duì)數(shù)據(jù)庫(kù)表做 ALTER TABLE）。

COPY 算法：讓所有乘客下飛機(jī)（阻塞 DML），把飛機(jī)拖進(jìn)機(jī)庫(kù)，拆下舊引擎，換上新引擎，最后再讓乘客登機(jī)。在此期間，飛機(jī)完全停運(yùn)。

INPLACE 算法：

• 準(zhǔn)備階段 (Prepare)：工程師們做好所有準(zhǔn)備工作：新引擎運(yùn)到機(jī)場(chǎng)，所有工具就位。這需要飛機(jī)短暫地保持靜止（短暫的排他鎖）。
• 執(zhí)行階段 (Execute)：飛機(jī)保持飛行狀態(tài)（允許并發(fā) DML）。工程師們掛在機(jī)翼上，開(kāi)始拆卸舊引擎，同時(shí)安裝新引擎。乘客們（DML 操作）仍然可以在機(jī)艙內(nèi)正常走動(dòng)、點(diǎn)餐（INSERT, UPDATE, DELETE）。
• 提交階段：新引擎安裝完畢，最后進(jìn)行一個(gè)極其快速的切換和檢查，確保新引擎完全接管工作。這又需要飛機(jī)瞬間的靜止（短暫的排他鎖）。

4.2 如何指定和查看算法

指定算法： 在 ALTER TABLE 語(yǔ)句中使用 ALGORITHM 子句。

ALTER TABLE your_table ADD COLUMN new_col INT, ALGORITHM=INSTANT; -- 嘗試強(qiáng)制使用 INSTANT
ALTER TABLE your_table ADD INDEX idx_name (col_name), ALGORITHM=INPLACE, LOCK=NONE; -- 嘗試強(qiáng)制 INPLACE 且無(wú)鎖

• ALGORITHM=DEFAULT：讓 MySQL 選擇它認(rèn)為最高效的可用算法。
• ALGORITHM=COPY | INPLACE | INSTANT：強(qiáng)制使用特定算法。如果該算法不支持此操作，語(yǔ)句會(huì)報(bào)錯(cuò)。

指定鎖策略： 使用 LOCK 子句。

ALTER TABLE ... LOCK=NONE; -- 盡可能允許并發(fā)讀寫(xiě) (最高并發(fā))
ALTER TABLE ... LOCK=SHARED; -- 允許讀，阻塞寫(xiě)
ALTER TABLE ... LOCK=EXCLUSIVE; -- 阻塞讀寫(xiě) (傳統(tǒng)方式)
ALTER TABLE ... LOCK=DEFAULT; -- 讓 MySQL 選擇最小必要的鎖策略

指定的 LOCK 級(jí)別必須兼容于操作本身支持的級(jí)別。例如，一個(gè)操作在 INPLACE 執(zhí)行階段允許 LOCK=NONE，但你強(qiáng)制指定 LOCK=EXCLUSIVE 是允許的（雖然不推薦）。反之，如果操作本身在某個(gè)階段必須短暫加 EXCLUSIVE 鎖，你指定 LOCK=NONE 會(huì)導(dǎo)致語(yǔ)句失敗。

查看算法和鎖： 執(zhí)行 ALTER TABLE 前，使用 ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE 并加上 NO_WRITE_TO_BINLOG 和 COMMIT 子句通常不會(huì)真正執(zhí)行，MySQL 會(huì)檢查并報(bào)告它將使用的算法和鎖。更好的方法是查詢(xún) INFORMATION_SCHEMA.INNODB_TABLES 或使用 SHOW CREATE TABLE 觀察進(jìn)度（對(duì)于長(zhǎng)時(shí)間操作），或者直接執(zhí)行后觀察輸出信息（很多客戶端會(huì)顯示使用的算法）。最準(zhǔn)確的是查看官方文檔對(duì)具體操作的支持矩陣。

4.3 重要注意事項(xiàng)

• 并非所有 DDL 都是 Online 的： 即使使用 INPLACE 算法，部分操作在準(zhǔn)備或提交階段也需要短暫的排他鎖 (EXCLUSIVE)。一些操作（如修改主鍵、修改某些列的數(shù)據(jù)類(lèi)型、更改表字符集等）可能仍然需要 COPY 算法或更長(zhǎng)時(shí)間的鎖。務(wù)必查閱官方文檔對(duì)應(yīng)版本的 Online DDL 支持矩陣。
• 空間與性能： INPLACE 操作雖然避免了重建整個(gè)表，但可能涉及大量的數(shù)據(jù)重組、日志記錄、排序操作，仍然會(huì)消耗大量 I/O 和 CPU 資源，可能影響系統(tǒng)性能。INSTANT 操作在這方面開(kāi)銷(xiāo)最小。
• 復(fù)制： Online DDL 在 MySQL 復(fù)制環(huán)境（主從）中的行為也需要考慮。通常在主庫(kù)上執(zhí)行的 Online DDL，其效果也會(huì)在從庫(kù)上以類(lèi)似的方式應(yīng)用（可能也是 Online 的，取決于從庫(kù)版本和設(shè)置）。
• 元數(shù)據(jù)鎖 (MDL)： 即使算法本身允許并發(fā) DML，長(zhǎng)時(shí)間的 DDL 操作也可能因?yàn)槌钟?MDL 而阻塞后續(xù)需要獲取沖突 MDL 的其他 DDL 或某些事務(wù)。LOCK=NONE 的目標(biāo)就是最小化 MDL 沖突。
• INSTANT 的限制： INSTANT 算法雖然強(qiáng)大，但有諸多限制（列的位置、數(shù)據(jù)類(lèi)型、索引類(lèi)型、表格式等），且限制隨版本更新而變化。使用前務(wù)必確認(rèn)操作是否支持 ALGORITHM=INSTANT。
• 版本差異： Online DDL 的支持程度和具體行為在不同 MySQL 版本（5.6, 5.7, 8.0）和 InnoDB 版本中有顯著差異。強(qiáng)烈建議參考對(duì)應(yīng)版本的官方文檔。

三、總結(jié)

MySQL 的 Online DDL 通過(guò) COPY, INPLACE, INSTANT 三種算法，極大地提升了 DDL 操作的并發(fā)性和可用性。尤其是 INSTANT 算法（MySQL 8.0+）對(duì)于支持的列操作實(shí)現(xiàn)了近乎瞬時(shí)的變更，對(duì)在線業(yè)務(wù)影響最小。INPLACE 算法則是大多數(shù)索引和列操作的主力，在執(zhí)行階段允許并發(fā)讀寫(xiě)。COPY 算法作為最后的選擇，應(yīng)盡量避免。

最佳實(shí)踐：

• 優(yōu)先使用 MySQL 8.0+ 以獲得最完善的 INSTANT 支持。
• 在執(zhí)行 DDL 前，務(wù)必查閱官方文檔，明確該操作在你的 MySQL 版本上支持的算法和鎖定行為。
• 在 ALTER TABLE 語(yǔ)句中顯式指定 ALGORITHM 和 LOCK 子句（如 ALGORITHM=INSTANT, LOCK=NONE），讓 MySQL 在無(wú)法滿足要求時(shí)報(bào)錯(cuò)，而不是默默使用低效的方式。
• 對(duì)于大表操作，即使使用 INPLACE，也應(yīng)在業(yè)務(wù)低峰期進(jìn)行，并監(jiān)控服務(wù)器資源（I/O, CPU, Memory）。
• 充分利用 INSTANT 算法進(jìn)行高頻次的表結(jié)構(gòu)變更（如快速加列）。

到此這篇關(guān)于深入理解Mysql OnlineDDL的算法的文章就介紹到這了,更多相關(guān)Mysql OnlineDDL 內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！ 

最新評(píng)論