源于最近有幾次對于一些大表進行DDL操作，但可能因為對InnoDB引擎的Online DDL的原理所知甚少，以至于有一些同學一開口就是，"執(zhí)行DDL時會鎖表，如果數據量太大鎖表時間太長，會阻塞到線上業(yè)務"，為確保以后進行DDL操作時能夠做到心中有數，對Online DDL做一些總結。

前身

MySQL于5.6版本推出Online DDL，那么在沒有Online DDL前，它是如何做DDL？主要分為兩種形式。

copy table

• create temp table，創(chuàng)建臨時表結構，保持與原表結構一致
• lock original table，不允許對原表進行DML操作，僅允許Queries
• ddl on temp table，在臨時表上執(zhí)行DDL (lock)
• copy original table data into temp table，將原表數據復制到臨時表 (lock)
• rename temp tablename to oringal tablename，對原表加鎖并進行rename操作，不允許DML & Queries，直至整個過程完成。

從整個執(zhí)行過程來分析，為了保證執(zhí)行期間表數據的一致性，必須通過加鎖來阻止DML操作，只允許Queries。當表數據量過大時，在復制階段所需要的時間過長導致整個語句在執(zhí)行期間加鎖阻塞其他事務的DML語句，故而導致應用連接數撐爆，大量事務超時等問題。

inplace

inplace又稱為fast index creation，僅支持索引的創(chuàng)建。

• create frm, 創(chuàng)建數據字典
• lock original table，不允許對原表進行DML操作，僅允許Queries
• copy table，按照聚簇索引順序讀取數據，構造新的索引項，順序插入新的索引頁
• lock original table，鎖表，不允許DML & Queries
• rename，替換frm文件，完成DDL過程

inplace方式比起需要copy table自然是更優(yōu)的，因為只需要從聚簇索引讀取新索引的列項出來構造新索引頁，其他索引不受影響。但是，無論是copy table 還是 inplace在執(zhí)行操作的過程中都需要長時間鎖表，阻塞DML語句，毫無疑問對于服務運行而言簡直是毀滅式的行為，因此online ddl迫在眉睫。

在討論online ddl前，有必要先了解幾個點，其中包括 Innodb索引組織形式，LOCK CAUSE，METADATA LOCK。

索引組織形式

關于索引的官方文檔

開發(fā)同學在構建數據庫表時，都會根據業(yè)務特點選擇合適的索引，因為索引的目的是加速搜索。從官網文檔中可以看到Innodb存儲引擎將索引分為Clustered Index以及Secondary Index，稱為聚集索引與二級索引（也稱聚簇索引，非聚簇索引）。

二者如何區(qū)分？Innodb只會有一個聚簇索引，聚簇索引的葉子節(jié)點上存儲的是Row Data，一般情況下是表定義中的PRIMARY KEY，如果沒有則選擇表定義中第一個非空UNIQUE KEY，如果表定義中即沒有PK，也沒有UK，則會通過Innodb隱藏的一個ROW ID作為索引。而處理聚簇索引之外的其他索引，皆稱為二級索引。二級索引的葉子節(jié)點上存儲的是聚簇索引的值。

正因為這個特征，很多企業(yè)定義的SQL規(guī)范中都會有類似：

• PK盡量選擇長整型，且趨勢遞增，推薦使用AUTO INCREMENT
• PK不適合較長的字符串
• ...

LOCK CAUSE

關于LOCK CAUSE的官方文檔

默認情況下，MySQL會選擇盡可能的輕量的鎖來完成DDL操作，在某些特定情況下可以更加嚴格的鎖來完成操作。比如前文提到，DDL過程中完成不允許DML，僅允許Queries，也可以不允許DML & Queries等。

• LOCK=NONE
  Permits concurrent queries and DML.
• LOCK=SHARED
  Permits concurrent queries but blocks DML.
• LOCK=DEFAULT
  Permits as much concurrency as possible (concurrent queries, DML, or both). Omitting the LOCK clause is the same as specifying LOCK=DEFAULT.
• LOCK=EXCLUSIVE
  Blocks concurrent queries and DML

METADATA LOCK

關于METADATA LOCK的官方文檔
關于METADATA LOCK的補充文檔

元數據鎖，是MySQL的表鎖之一，屬于隱式鎖（另一種是顯式鎖，通過lock table ... with read/write指定）。在補充文檔中可以了解到元數據鎖的由來，也對其進行了解釋。補充文檔中提及元數據鎖有11種類型，常用的有MDL_SHARED_READ,MDL_SHARED_WRITE及MDL_EXCLUSIVE。元數據鎖是一個跟隨事務結束而釋放的鎖，由MySQL控制，保護處于事務中的表元數據的一致性。

• MDL_SHARED_READ，Queries時加鎖，屬于共享鎖
• MDL_SHARED_WRITE，DML時加鎖，屬于共享鎖
• MDL_EXCLUSIVE，DDL時加鎖，屬于獨占鎖

Online DDL

簡述

MySQL在5.6.7版本推了Online DDL能力，主要是在基于原有的fast index creation上增強實現。其次，假定前提，衡量DDL是否足夠Online在于是否長時間允許DML。

目前Online DDL支持兩種形式

• COPY
• INPLACE

MySQL如何選擇該兩種形式呢？

• 支持指定，即在執(zhí)行語句上指定ALGORITHM=INPLACE / COPY。
• 默認對于不支持Online DDL的sql語句則采用COPY，相反則采用INPLACE。

INPLACE會根據是否涉及到修改行記錄格式分為三種情形

• Rebuilds Table，修改了行記錄格式，比如修改列類類型、增減列等
• Not Rebuilds Table & Not Only Modifies Metadata，不需要重建表但是也不僅僅只是修改元數據，比如增加索引。
• Only Modifies Metadata，僅修改元數據，比如刪除索引、設置列默認值，重命名列名等

Online DDL支持選項

• ALGORITHM={COPY|INPLACE}
• LOCK={NONE|SHARED|DEFAULT|EXCLUSIVE}，參照前文LOCK CAUSE

可以從官方文檔查看Online DDL支持情況。

有幾個點需要特意說明

• INPLACE并不表示絕對支持并行DML，但是COPY絕對不支持并行DML。
• 簡單區(qū)分 INPLACE與COPY，在于是否需要創(chuàng)建臨時表
• 簡單區(qū)分 INPLACE與COPY，COPY主要由Server支持，INPLACE主要由Innodb支持
• INPLACE不是不需要額外的數據空間，取決是否為Only Modifies Metadata
• Only Modifies Metadata不需要Rebuilds Table，不需要Rebuilds Table的不一定是Only Modifies Metadata

image.png

實現原理

Online DDL主要分為3個階段，PREPARE，EXECUTE，COMMIT

• PREPARE
  • 創(chuàng)建新的臨時frm文件
  • 持有MDL_EXCLUSIVE鎖，禁止讀寫
  • 根據alter類型，確定執(zhí)行方式（copy，rebuild，not-rebuild）
  • 更新數據字典的內存對象
  • 若是需要rebuild，分配row_log對象用于記錄增量
  • 若是需要rebuild，生成新的臨時ibd文件
• EXECUTE
  • 如果是僅修改元數據：
    • 這部分無操作
  • 其他，則是：
    • 降低MDL_EXCLUSIVE鎖，允許DML & Queries（copy 不允許寫）
    • 記錄DDL執(zhí)行過程中產生的增量row-log（非only modify metadata類型需要）
    • 掃描old_table的聚集索引每一條記錄record
    • 遍歷新表的聚集索引和二級索引，逐一處理
    • 根據record構造對應的索引項
    • 將構造索引項插入sort_buffer塊
    • 將sort_buffer塊插入新的索引
    • 把row_log中的操作應用到新臨時表中，應用到最后一個Block
• COMMIT
  • 升級到MDL_EXECLUSIVE鎖，禁止讀寫
  • 重做最后一部分的row_log增量
  • 更新innodb的數據字典表
  • 提交事務，寫redo日志
  • 修改統計信息
  • rename臨時的ibd文件、frm文件
  • DDL完成

在整個Online DDL的過程中，并非是完全的Permits Concurrent DML，但是由于整個過程中持有MDL_EXCLUSIVE鎖的時間較短，所以近似的認為整個過程是Permits Concurrent DML

Online DDL帶來的優(yōu)勢

• Online DDL期間，Queries和DML操作在多數情況下可以正常執(zhí)行，鎖表時間大大減少。
• 允許INPLACE操作的DDL，避免COPY方式的磁盤IO及CPU資源，減少對數據庫的整體負荷，使得在DDL期間，能夠維持數據庫的高性能及高吞吐量；
• 允許INPLACE操作的 DDL，比需要COPY到臨時表的操作要更少占用buffer pool，避免以往DDL過程中性能的臨時下降，因為需要拷貝數據到臨時表，這個過程會占用到buffer pool，導致內存中的部分頻繁訪問的數據會被清理出去。

資料

https://www.cnblogs.com/cchust/p/4639397.html
https://www.cnblogs.com/xinysu/p/6732646.html
https://www.cnblogs.com/dbabd/p/10381942.html
http://mysql.taobao.org/monthly/2021/03/06/