MySQL 中的在線DDL（OnLine DDL）功能是一種強(qiáng)大的工具，可以在不中斷表或數(shù)據(jù)庫(kù)的情況下進(jìn)行數(shù)據(jù)定義語(yǔ)言（DDL）操作。通過(guò)在線DDL，使得在對(duì)表進(jìn)行結(jié)構(gòu)變更時(shí)，仍然能夠進(jìn)行讀寫操作，避免了整個(gè)表的鎖定和阻塞。

一、Online DDL的發(fā)展歷史

MySQL Online DDL 功能從 5.6 版本開(kāi)始正式引入，發(fā)展到現(xiàn)在的 8.0 版本，經(jīng)歷了多次的調(diào)整和完善。本文主要就 Online DDL 的發(fā)展過(guò)程，以及各版本的區(qū)別進(jìn)行總結(jié)。其實(shí)早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式，但是因?yàn)閷?shí)現(xiàn)的問(wèn)題，依然會(huì)阻塞 INSERT、UPDATE、DELETE 操作，這也是 MySQL 早期版本長(zhǎng)期被吐槽的原因之一。

在 MySQL 5.6 中，官方開(kāi)始支持更多的 ALTER TABLE 類型操作來(lái)避免數(shù)據(jù)拷貝，同時(shí)支持了在線上 DDL 的過(guò)程中不阻塞 DML 操作，真正意義上的實(shí)現(xiàn)了 Online DDL。然而并不是所有的 DDL 操作都支持在線操作，后面會(huì)附上 MySQL 官方文檔對(duì)于 DDL 操作的總結(jié)。

到了 MySQL 5.7，在 5.6 的基礎(chǔ)上又增加了一些新的特性，比如：增加了重命名索引支持，支持了數(shù)值類型長(zhǎng)度的增大和減小，支持了 VARCHAR 類型的在線增大等。但是基本的實(shí)現(xiàn)邏輯和限制條件相比 5.6 并沒(méi)有大的變化。

MySQL 8.0 對(duì) DDL 的實(shí)現(xiàn)重新進(jìn)行了設(shè)計(jì)，其中一個(gè)最大的改進(jìn)是 DDL 操作支持了原子特性。另外，Online DDL 的 ALGORITHM 參數(shù)增加了一個(gè)新的選項(xiàng)：INSTANT，只需修改數(shù)據(jù)字典中的元數(shù)據(jù)，無(wú)需拷貝數(shù)據(jù)也無(wú)需重建表，同樣也無(wú)需加排他 MDL 鎖，原表數(shù)據(jù)也不受影響。整個(gè) DDL 過(guò)程幾乎是瞬間完成的，也不會(huì)阻塞 DML。

二、Online DDL的算法

了解 Online DDL 先了解一下之前 DDL 的 2 種算法 copy 和 inplace。

Copy算法

1. 按照原表定義創(chuàng)建一個(gè)新的臨時(shí)表
2. 對(duì)原表加寫鎖（禁止 DML，允許 select）
3. 步驟 1）建立的臨時(shí)表執(zhí)行 DDL
4. 將原表中的數(shù)據(jù) copy 到臨時(shí)表
5. 釋放原表的寫鎖
6. 將原表刪除，并將臨時(shí)表重命名為原表
   可見(jiàn)，采用 copy 方式期間需要鎖表，禁止 DML，因此是非 Online 的。比如：刪除主鍵、修改列類型、修改字符集，這些操作會(huì)導(dǎo)致行記錄格式發(fā)生變化（無(wú)法通過(guò)全量 + 增量實(shí)現(xiàn) Online）。

Inplace算法

在原表上進(jìn)行更改，不需要生成臨時(shí)表，不需要進(jìn)行數(shù)據(jù) copy 的過(guò)程。根據(jù)是否變更行記錄格式，分為兩類：

• 1. rebuild：需要重建表（重新組織聚簇索引）。比如 optimize table、添加索引、添加/刪除列、修改列 NULL/NOT NULL 屬性等；
• 2. no-rebuild：不需要重建表，只需要修改表的元數(shù)據(jù)，比如刪除索引、修改列名、修改列默認(rèn)值、修改列自增值等。

對(duì)于 rebuild 方式實(shí)現(xiàn) Online 是通過(guò)緩存 DDL 期間的 DML，待 DDL 完成之后，將 DML 應(yīng)用到表上來(lái)實(shí)現(xiàn)的。

說(shuō)明：

1. 在 copy 數(shù)據(jù)到新表期間，在原表上是加的 MDL 讀鎖（允許 DML，禁止 DDL）
2. 在應(yīng)用增量期間對(duì)原表加 MDL 寫鎖（禁止 DML 和 DDL）
3. 根據(jù)表A重建出來(lái)的數(shù)據(jù)是放在 tmp_file 里的，這個(gè)臨時(shí)文件是 InnoDB 在內(nèi)部創(chuàng)建出來(lái)的，整個(gè) DDL 過(guò)程都在 InnoDB 內(nèi)部完成。對(duì)于 server 層來(lái)說(shuō)，沒(méi)有把數(shù)據(jù)挪動(dòng)到臨時(shí)表，是一個(gè)原地操作，這就是“inplace”名稱的來(lái)源。

MySQL中，表級(jí)別的鎖有2種
一種是我們通常說(shuō)的表鎖，由InnoDB引擎實(shí)現(xiàn)，如lock tables …
read/write，表鎖影響較大，不常用。另一種表級(jí)別的鎖是MDL( metadata lock
)，由Server層實(shí)現(xiàn)，MDL我們不顯式使用，是在訪問(wèn)一個(gè)表時(shí)由數(shù)據(jù)庫(kù)自動(dòng)加的，對(duì)表記錄增刪改查時(shí)，加MDL讀鎖；對(duì)表結(jié)構(gòu)進(jìn)行變更時(shí)，加MDL寫鎖。MDL鎖，讀讀不互斥，讀寫、寫寫互斥。
哪些常用操作“鎖表”
創(chuàng)建二級(jí)索引（二級(jí)索引是指除主鍵索引之外的索引）、刪除索引、重命名索引、改變索引類型——不“鎖表”。
添加字段、刪除字段、重命名字段、調(diào)整字段順序、設(shè)置字段默認(rèn)值、刪除字段默認(rèn)值、修改auto-increment值、調(diào)整字段允許NULL、調(diào)整字段不允許NULL
—— 不“鎖表”。
擴(kuò)展Varchar字段大小——不“鎖表”。
更改字段數(shù)據(jù)類型，如varchar改成text——“鎖表”

三、Online DDL過(guò)程中的鎖

默認(rèn)情況下，MySQL就是支持online的DDL操作的，在online的DDL語(yǔ)句執(zhí)行的過(guò)程中，MySQL會(huì)盡量少使用鎖的限制，我們不需要特殊的操作來(lái)啟用它。

MySQL在選擇的時(shí)候，盡量少使用鎖，但是不排除它會(huì)選擇使用鎖。而如果我擔(dān)心它選擇了鎖而導(dǎo)致我們的表不能讀也不能寫，顯然這不是我們想要的結(jié)果，我們希望：如果選擇了鎖就不要執(zhí)行，直接退出執(zhí)行；如果沒(méi)有選擇鎖就執(zhí)行。想要達(dá)到我們希望的這個(gè)效果，該怎么做呢？

可以在執(zhí)行我們的online DDL語(yǔ)句的時(shí)候，使用ALGORITHM和LOCK關(guān)鍵字，這兩個(gè)關(guān)鍵字在我們的DDL語(yǔ)句的最后面，用逗號(hào)隔開(kāi)即可。示例如下：

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM的選項(xiàng)

• INPLACE：替換：直接在原表上面執(zhí)行DDL的操作。
• COPY：復(fù)制：使用一種臨時(shí)表的方式，克隆出一個(gè)臨時(shí)表，在臨時(shí)表上執(zhí)行DDL，然后再把數(shù)據(jù)導(dǎo)入到臨時(shí)表中，在重命名等。這期間需要多出一倍的磁盤空間來(lái)支撐這樣的 操作。執(zhí)行期間，表不允許DML的操作。
• DEFAULT：默認(rèn)方式，有MySQL自己選擇，優(yōu)先使用INPLACE的方式。

LOCK的選項(xiàng)

• SHARE：共享鎖，執(zhí)行DDL的表可以讀，但是不可以寫。
• NONE：沒(méi)有任何限制，執(zhí)行DDL的表可讀可寫。
• EXCLUSIVE：排它鎖，執(zhí)行DDL的表不可以讀，也不可以寫。
• DEFAULT：默認(rèn)值，也就是在DDL語(yǔ)句中不指定LOCK子句的時(shí)候使用的默認(rèn)值。如果指定LOCK的值為DEFAULT，那就是交給MySQL子句去覺(jué)得鎖還是不鎖表。不建議使用，如果你確定你的DDL語(yǔ)句不會(huì)鎖表，你可以不指定lock或者指定它的值為default，否則建議指定它的鎖類型。

執(zhí)行DDL操作時(shí)，ALGORITHM選項(xiàng)可以不指定，這時(shí)候MySQL按照INSTANT、INPLACE、COPY的順序自動(dòng)選擇合適的模式。也可以指定ALGORITHM=DEFAULT，也是同樣的效果。如果指定了ALGORITHM選項(xiàng)，但不支持的話，會(huì)直接報(bào)錯(cuò)。

注意:
在執(zhí)行OnlineDDL之前，要在非業(yè)務(wù)高峰期去執(zhí)行，并要確認(rèn)待執(zhí)行的表上面沒(méi)有未提交的事務(wù)、鎖等信息?？梢酝ㄟ^(guò)如下的SQL語(yǔ)句查看是否有事務(wù)和鎖等信息。

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
select * from information_schema.processlist;

四、理解DDL操作的需求和挑戰(zhàn)

DDL操作涉及對(duì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)的修改，例如添加/刪除列、修改列定義、添加/刪除索引等。在以往的版本中，執(zhí)行這些DDL操作時(shí)需要鎖定整個(gè)表，對(duì)數(shù)據(jù)庫(kù)的可用性產(chǎn)生了負(fù)面影響。因此，實(shí)現(xiàn)在線DDL成為了提高系統(tǒng)靈活性和性能的重要需求。

五、MySQL 5.7的在線DDL功能特點(diǎn)

MySQL 5.7通過(guò)InnoDB存儲(chǔ)引擎實(shí)現(xiàn)了在線DDL功能的改進(jìn)。以下是該功能的主要特點(diǎn)：

• 支持添加輔助索引：可以在運(yùn)行中的表上添加輔助索引，而不會(huì)對(duì)整個(gè)表進(jìn)行鎖定。
• 支持修改列定義：可以在線修改列的數(shù)據(jù)類型、長(zhǎng)度等定義。
• 支持修改字符集和排序規(guī)則：可以在線修改表的字符集和排序規(guī)則設(shè)置。
• 支持重命名列：可以在不影響正在進(jìn)行的讀寫操作的情況下，對(duì)表中的列進(jìn)行重命名。

六、實(shí)現(xiàn)原理和優(yōu)化

在線DDL功能的實(shí)現(xiàn)涉及以下關(guān)鍵步驟和優(yōu)化：

• 1 創(chuàng)建臨時(shí)表：通過(guò)創(chuàng)建臨時(shí)表來(lái)存儲(chǔ)將要進(jìn)行的DDL操作所需的新結(jié)構(gòu)。這樣，舊表仍然可用于讀寫操作。
• 2 數(shù)據(jù)復(fù)制和同步：將舊表中的數(shù)據(jù)逐步復(fù)制到臨時(shí)表中，并保持舊表數(shù)據(jù)與臨時(shí)表數(shù)據(jù)的同步。這一過(guò)程確保了數(shù)據(jù)在DDL操作期間的完整性和一致性。
• 3 變更捕獲和重放：通過(guò)使用日志和重做日志等機(jī)制，捕獲在執(zhí)行DDL操作期間發(fā)生的數(shù)據(jù)變更，并將其重放到臨時(shí)表中。這確保了DDL操作完成后數(shù)據(jù)的一致性。
• 4 最終切換：當(dāng)DDL操作完成時(shí)，數(shù)據(jù)庫(kù)引擎將在適當(dāng)?shù)臅r(shí)機(jī)切換到臨時(shí)表，使其成為新的表結(jié)構(gòu)，并且對(duì)新表進(jìn)行后續(xù)的讀寫操作。

七、使用限制和注意事項(xiàng)

盡管MySQL 5.7的在線DDL功能提供了一種近似在線的體驗(yàn)，但仍然有一些限制和注意事項(xiàng)：

• 并非所有DDL操作都支持在線執(zhí)行，某些操作仍然需要鎖定整個(gè)表。
• 在進(jìn)行DDL操作期間，可能會(huì)占用較多的系統(tǒng)資源，因此在高負(fù)載時(shí)應(yīng)謹(jǐn)慎使用。
• 進(jìn)行在線DDL操作時(shí)，需要對(duì)操作進(jìn)行充分的評(píng)估和測(cè)試，以確保數(shù)據(jù)的完整性和一致性。

八、各版本支持的詳細(xì)情況

本文數(shù)據(jù)全部來(lái)自 MySQL 官方文檔，此處進(jìn)行一個(gè)集中的整理和總結(jié)：

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

九、 DDL 的執(zhí)行模式

結(jié)合上面的表格，對(duì) MySQL 當(dāng)前 DDL 的執(zhí)行模式總結(jié)如下：

1. INSTANT DDL 是 MySQL 8.0 引入的新功能，當(dāng)前支持的范圍較小，包括：

• 修改二級(jí)索引類型
• 新增列
• 修改列默認(rèn)值
• 修改列 ENUM 值
• 重命名表

2. 在執(zhí)行 DDL 操作時(shí)，MySQL 內(nèi)部對(duì)于 ALGORITHM 的選擇策略：
如果用戶顯式指定了 ALGORITHM，那么使用用戶指定的選項(xiàng)；
如果用戶未指定，那么如果該操作支持 INPLACE 則優(yōu)先選擇 INPLACE，否則選擇 COPY；
當(dāng)前不支持 INPLACE 的操作主要有：

• 刪除主鍵
• 修改列數(shù)據(jù)類型
• 修改表字符集

3. 我們常說(shuō)的 Online DDL，其實(shí)是從 DML 操作的角度描述的，如果 DDL 操作不阻塞 DML 操作，那么這個(gè) DDL 就是 Online 的。當(dāng)前非 Online 的 DDL 其實(shí)已經(jīng)比較少了，主要有：

• 新增全文索引
• 新增空間索引
• 刪除主鍵
• 修改列數(shù)據(jù)類型
• 指定表字符集
• 修改表字符集

更多詳細(xì)的示例請(qǐng)參考上面的官方文檔的地址。

十、探討幾個(gè)問(wèn)題

最后討論幾個(gè)非常容易混淆的問(wèn)題：

• Online DDL 不會(huì)鎖表，可以隨意的執(zhí)行。
• 支持 INPLACE 算法的 DDL 一定是 Online 的。
• 對(duì)于支持 INPLACE 算法的 DDL，DDL 操作是原地修改數(shù)據(jù)，不需要額外的數(shù)據(jù)空間。

Q1: Online DDL 會(huì)不會(huì)鎖表

Online DDL 會(huì)不會(huì)鎖表？要回答這個(gè)問(wèn)題，首先要明確“鎖表”的含義。很多 MySQL 用戶經(jīng)常在表無(wú)法正常的進(jìn)行 DML 時(shí)就覺(jué)得是鎖表了，這種說(shuō)法其實(shí)過(guò)于寬泛，實(shí)際上能夠影響 DML 操作的鎖至少包括以下幾種（默認(rèn)為 InnoDB 表）：

MDL 鎖
表鎖
行鎖
GAP 鎖

其中除了 MDL 鎖是在 Server 層加的之外，其它三種都是在 InnoDB 層加的。具體的加鎖邏輯不在此進(jìn)行展開(kāi)，但是需要明確一點(diǎn)：所有的操作（不管是 DDL 還是 DML 還是查詢語(yǔ)句）都需要先拿 Server 層的 MDL 鎖，然后再去拿 InnoDB 層的某個(gè)需要的鎖。

一個(gè) DDL 的基本過(guò)程是這樣的：

• 首選，在開(kāi)始進(jìn)行 DDL 時(shí)，需要拿到對(duì)應(yīng)表的 MDL X 鎖，然后進(jìn)行一系列的準(zhǔn)備工作；
• 然后將 MDL X 鎖降級(jí)為 MDL S 鎖，進(jìn)行真正的 DDL 操作；
• 最后再次將 MDL S 鎖升級(jí)為 MDL X 鎖，完成 DDL 操作，釋放 MDL 鎖；

所以在真正執(zhí)行 DDL 操作期間，確實(shí)是不會(huì)“鎖表”的，但是如果在第一階段拿 MDL X 鎖時(shí)無(wú)法正常獲取，那就可能真的會(huì)“鎖表了”。一個(gè)簡(jiǎn)單的例子如下：

# session 1
select sleep(500) from mytest.t1;
# session 2
optimize table mytest.t1;
# session 3
select * from mytest.t1;

session 1 模擬了一個(gè)慢查詢，然后 session 2 開(kāi)始進(jìn)行 DDL 操作，無(wú)法拿到 MDL X 鎖，處于等到中。此時(shí) session 3 需要執(zhí)行一個(gè)查詢，發(fā)現(xiàn)無(wú)法執(zhí)行。實(shí)際上，在 session 1 結(jié)束前，表 t1 的所有操作都無(wú)法進(jìn)行了，也可以說(shuō)表 t1 “鎖表”了。MySQL 5.7/8.0 可以在開(kāi)啟 performance_schema 的情況下直接查詢 metadata_locks 表。阿里云 RDS 5.6 版本新增了 I_S.MDL_INFO 表，提供 MDL 的查詢。

MySQL [performance_schema]> select * from metadata_locks where OBJECT_NAME = 't1';
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | mytest        | t1          | NULL        |       140730442220576 | SHARED_READ          | TRANSACTION   | GRANTED     | sql_parse.cc:5916 |            1083 |             24 |
| TABLE       | mytest        | t1          | NULL        |       140730576178368 | SHARED_NO_READ_WRITE | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1091 |              3 |
| TABLE       | mytest        | t1          | NULL        |       140730374843168 | SHARED_READ          | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1092 |              3 |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set (0.00 sec)

明確了上面的概念之后，再回到我們的問(wèn)題，Online DDL 是不是不鎖表？如果非要回答，那么只能說(shuō)，Online DDL 并不是絕對(duì)安全，更不是可以隨意的執(zhí)行。線上操作還是需要在業(yè)務(wù)低峰期謹(jǐn)慎操作。

Q2: 支持 INPLACE 算法的 DDL 一定是 Online 的

從概念上來(lái)說(shuō)，INPLACE 和 Online 是兩個(gè)不同維度的事情。COPY 和 INPLACE 指的是 DDL 內(nèi)部的執(zhí)行邏輯，可以簡(jiǎn)單的理解成：COPY 是在 Server 層的操作，INPLACE 是在 InnoDB 層的操作。而用戶更加關(guān)心 Online 與否，通常只與一個(gè)問(wèn)題有關(guān)：是否允許并發(fā) DML。

兩個(gè)基本結(jié)論：

• COPY 算法執(zhí)行的 DDL 肯定不是 Online 的；
• INPLACE 算法執(zhí)行的 DDL 不一定是 Online 的；

Q3: INPLACE DDL 需不需要額外的數(shù)據(jù)空間

前面我們提到過(guò)，MySQL 內(nèi)部對(duì)于 DDL 的 ALGORITHM 有兩種選擇：INPLACE 和 COPY（8.0 新增了 INSTANT，但是使用范圍較?。?。COPY 算法理解起來(lái)相對(duì)簡(jiǎn)單一點(diǎn)：創(chuàng)建一張臨時(shí)表，然后將原表的數(shù)據(jù)拷貝到臨時(shí)表中，最后再用臨時(shí)表替換原表。對(duì)于上面的步驟，由于需要將原表的數(shù)據(jù)拷貝到臨時(shí)表中，所以肯定需要消耗額外的數(shù)據(jù)空間。

那么對(duì)于支持 INPLACE 算法的 DDL，是不是不需要額外的數(shù)據(jù)空間？

答案是：需要。其實(shí)之所以會(huì)問(wèn)這個(gè)問(wèn)題，還是因?yàn)閷?duì) INPLACE 本身的理解出現(xiàn)了偏差。簡(jiǎn)單來(lái)說(shuō)：INPLACE 描述的是表，而不是數(shù)據(jù)文件。只要不創(chuàng)建臨時(shí)表，那么都是 INPLACE 的。

實(shí)際上，很多 INPLACE DDL 都會(huì)重建表（會(huì)創(chuàng)建臨時(shí)數(shù)據(jù)文件），所以都會(huì)需要額外的數(shù)據(jù)空間，例如：

• 增加主鍵
• 重建主鍵
• 新增列（8.0 支持 INSTANT DDL，不需要）
• 刪除列
• 調(diào)整列順序
• 刪除列默認(rèn)值
• 增加列默認(rèn)值
• 修改表的 ROW_FORMAT
• OPTIMIZE 表

到此這篇關(guān)于MySQL Online DDL詳解:從歷史演進(jìn)到原理及使用的文章就介紹到這了,更多相關(guān)mysql online ddl使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論