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

核心目標： 在 DDL 操作進行時，允許對表進行并發(fā)的讀?。⊿ELECT） 和寫入（INSERT, UPDATE, DELETE） 操作。

一、Online DDL 是什么？

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

• 寫操作（DML）：在 DDL 操作進行的同時，應用程序依然可以對表執(zhí)行 INSERT, UPDATE, DELETE 等操作，最大程度保證業(yè)務的連續(xù)性。
• 讀操作：SELECT 查詢通常可以正常進行，不受影響。

這與早期的 Copy Table 機制形成鮮明對比，早期方式需要全程鎖表，直到操作完成，對于大表來說意味著長時間的停機。

二、Online DDL 的三種主要算法

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

2.1COPY（復制法）

過程：

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

特點：

• 需要兩倍的存儲空間。
• 過程中大部分時間會阻塞寫操作，影響業(yè)務。
• 是 MySQL 5.5 及之前版本的主要方式。

2.2 INPLACE （原地法）

過程：

無需創(chuàng)建臨時表文件，直接在原始表的存儲文件（如 InnoDB 的 .ibd 文件）上進行操作。

通常分為兩個階段：

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

特點：

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

2.3INSTANT (即刻法，MySQL 8.0+)

過程：

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

特點：

• 速度極快，通常能在毫秒級完成。
• 完全不阻塞任何 DML 操作，是真正的“Online”。
• 對存儲空間沒有額外要求。

三、Online DDL 的鎖機制

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

• SHARED鎖（讀鎖）：在 DDL 的準備階段，可能會短暫地獲取。允許其他會話讀，但阻塞寫。
• EXCLUSIVE鎖（寫鎖/排他鎖）：在 DDL 的開始（準備階段）和結束（提交階段）可能會短暫地獲取。此時會阻塞所有其他的讀和寫操作。

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

四 關鍵區(qū)別

4.1 生動的比喻：給飛行中的飛機換引擎

想象一下，你要給一架正在飛行的飛機更換引擎（這相當于對數據庫表做 ALTER TABLE）。

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

INPLACE 算法：

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

4.2 如何指定和查看算法

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

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

• ALGORITHM=DEFAULT：讓 MySQL 選擇它認為最高效的可用算法。
• ALGORITHM=COPY | INPLACE | INSTANT：強制使用特定算法。如果該算法不支持此操作，語句會報錯。

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

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

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

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

4.3 重要注意事項

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

三、總結

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

最佳實踐：

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

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

最新評論