淺談DDL技術(shù)解密
首先，用過數(shù)據(jù)庫的小伙伴們(本文以 MySQL InnoDB 為例)都知道，MySQL 不止有增刪改數(shù)據(jù)操作(DML)，還有改表結(jié)構(gòu)的操作(DDL)，當新增加字段等修改表結(jié)構(gòu)時，就需要進行 DDL 操作?？墒牵绻麑σ粋€存儲了上百萬甚至上千上萬的數(shù)據(jù)表進行 DDL 操作，數(shù)據(jù)庫是怎么做到的呢?會不會有一個很大的事務(wù)鎖?會不會影響數(shù)據(jù)的插入和更新?今天就會聊聊這個問題，以及 PT-OSC、GH-OST 等技術(shù)，是如何高效的解決這個問題的。
Before MySQL 5.5
在 MySQL 5.5 版本及之前版本，DDL 操作主要有 copy table 和 inplace 兩種方式。
1. Copy Table 方式
Copy Table 顧名思義，就是通過臨時表拷貝的方式實現(xiàn)的。在 MySQL 5.5 版本及之前版本，修改表結(jié)構(gòu)是表級鎖，所以在整個 DDL 過程中表都是鎖著不可寫入的。這使得在修改時容易導(dǎo)致數(shù)據(jù)庫 CPU、IO 等性能的消耗，以及主從同步的延遲。
上述過程，MySQL 自動完成轉(zhuǎn)存數(shù)據(jù)，交換表名和刪除舊表等操作，時間消耗最多的是在往臨時表(Server 層)插入數(shù)據(jù)的過程，整個 DDL 過程中，表是不能執(zhí)行 DML 的。
2. IN-Place 方式
在 MySQL 5.5 版本中，增加了 IN-Place 方式。所謂 IN-Place 方式，就是索引創(chuàng)建在原表上直接進行，不會 copy 整個表，只需要在原來的 idb 文件上，新建所需要的索引頁，這比 Copy Table 節(jié)約極大的 IO 資源，且減少了 DDL 執(zhí)行時長。
對比 Copy Table 和 IN-Place 兩種方式，我們看下官網(wǎng)的內(nèi)容(MySQL 5.5)：
(引自：https://dev.mysql.com/doc/refman/5.5/en/alter-table.html)
以上是 MySQL 5.5 版本中的說明，而 MySQL 5.6 版本，則正式提出了 COPY 和 INPLACE 兩種方式。
(引自：https://dev.mysql.com/doc/refman/5.6/en/alter-table.html)
3. Fast Index Creation(FIC)
Innodb 存儲引擎從 1.0.x 版本開始，對添加索引操作引入了新特性 Fast Index Creation(FIC 特性)。FIC 就是添加或刪除二級索引的時候，可以不用復(fù)制原表，而是在創(chuàng)建或刪除二級索引時會對原表加上一個 S 鎖(共享鎖)，允許其他會話進行讀操作，但禁止寫操作，根據(jù)當前表數(shù)據(jù)創(chuàng)建索引，新索引創(chuàng)建完成之后，解除 S 鎖，允許寫操作。
FIC 在創(chuàng)建索引時不需要拷貝整表數(shù)據(jù)，但只對二級索引有效，對主鍵索引無效，對于主鍵索引的創(chuàng)建和刪除同樣需要重建一個臨時表。
對比 IN-Place 和 FIC，在網(wǎng)上查了一些資料，說“INPLACE 方式也稱為 InnoDB fast index creation”，那兩個應(yīng)該不是一回事?我們看下官網(wǎng)的內(nèi)容(MySQL 5.5)：
(引自：https://dev.mysql.com/doc/refman/5.5/en/innodb-create-index-overview.html)
在 MySQL 5.5 的官方說明中，并沒有明確說明 FIC 就是 INPLACE，并且此版本中的 FIC 只支持二級索引和輔助索引的增加和刪除。而在 MySQL 5.6 官方說明中，則指出 Online DDL 特性基于 InnoDB FIC 構(gòu)建。
(引自：https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html)
在 MySQL 8.0 的官方說明中，則指出了 FIC 是 Online DDL 的延伸和擴展。
(引自：https://dev.mysql.com/doc/refman/8.0/en/glossary.html)
所以，綜上所述，“INPLACE 方式也稱為 InnoDB fast index creation”這句話是對的，Online DDL 方式延伸了 Fast Index Creation，并逐漸擴展了 FIC 的范圍。
Since MySQL 5.6
在 MySQL 5.6 版本，引入了 Online DDL，這個新特性解決了早期版本 MySQL 進行 DDL 操作時帶來的鎖表問題，Online DDL 執(zhí)行的過程中依然保證可以讀寫，不影響數(shù)據(jù)庫對外提供服務(wù)。
Online DDL
- Alter table …. , ALGORITHM [=] {DEFAULT|INPLACE|COPY}, LOCK [=] { DEFAULT| NONE| SHARED| EXCLUSIVE }
ALGORITHM 子句指定執(zhí)行 DDL 采用的方式，LOCK 子句描述持有鎖類型來控制 DML 的并發(fā)。其中，某些 DDL 語句不支持 Online DDL 的采用 COPY 方式，支持的就采用 INPLACE 方式，因為 Online DDL 是對早期 INPLACE 方式的增強，所以 INPLACE 方式根據(jù)是否涉及記錄格式的修改又分為：Rebuilds Table 和 No-Rebuilds Table，我們看下官方給出的內(nèi)容(MySQL 5.7)：
(引自：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html)
基于 Online 對比 COPY 和 INPLACE，COPY 方法從表中的數(shù)據(jù)導(dǎo)出來的存放位置叫作 tmp_table，這是一個臨時表，是在 server 層創(chuàng)建的。INPLACE 方法從表中重建出來的數(shù)據(jù)是放在 tmp_file 里的，這個臨時文件是 InnoDB 在內(nèi)部創(chuàng)建出來的，整個 DDL 過程都在 InnoDB 內(nèi)部完成。
Online DDL 實現(xiàn)過程主要包括三個階段：Initialization 階段， Execution 階段，Commit Table Definition 階段。我們看下官方給出的內(nèi)容(MySQL 8.0)：
(引自：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html)
我們詳細描述下這三個過程的過程：
(1) Initialization
- 創(chuàng)建 frm 臨時文件
- 持有EXCLUSIVE_MDL鎖，禁止讀寫
- 根據(jù) ALTER 操作，確定執(zhí)行過程(COPY，Online-Rebuilds，Online-No-Rebuilds)
- 更新數(shù)據(jù)字典的內(nèi)存對象
- 若是需要 Rebuilds，分配 row_log 對象記錄增量
- 若是需要 Rebuilds，新建 ibd 臨時文件
(2) Execution(如果僅修改 MetaData，則無此部操作)
- 降低EXCLUSIVE-MDL鎖，允許讀寫(COPY 僅允許讀)
- 記錄執(zhí)行期間產(chǎn)生的 DML 操作到 row_log(僅 Rebuilds 需要)
- 掃描老表的聚集索引中每一條記錄 record
- 遍歷新表的聚集索引和二級索引，逐一處理
- 根據(jù) record 構(gòu)造對應(yīng)的索引項
- 將構(gòu)造的索引項插入 sort_buffer 塊中
- 將 sort_buffer 塊插入新的索引
- 將 row_log 中的記錄應(yīng)用到新臨時表，應(yīng)用到最后一個 Block
(3) Commit Table Definition
- 升級到EXECLUSIVE-MDL鎖，禁止讀寫
- 重做 row_log 中最后一部分增量
- 更新 InnoDB 的數(shù)據(jù)字典
- 提交事務(wù)，寫 InnoDB redo 日志
- 修改統(tǒng)計信息
- Rename 臨時的 ibd 和 frm 文件
- DDL 執(zhí)行變更
我理解，Online DDL 中的 COPY 和 INPLACE 的區(qū)別在于有沒有原地，COPY 會將數(shù)據(jù)從 InnoDB 存儲層 copy 到 Server 層，而 INPLACE 不會;而 INPLACE 中的 Rebuilds 和 No-Rebuils 的區(qū)別在于，有沒有重建表。
PT-Online-Schema-Change(PT-OSC)
全稱 Percona Toolkit Online Schema Change，其中 Percona Toolkit 源自 Maatkit 和 Aspersa 工具，這兩個工具是管理 MySQL 最有名的工具，但 Maatkit 已經(jīng)不維護了，全部歸并到 Percona Toolkit。Percona Toolkit 是一組高級的命令行工具，用來管理 MySQL 和系統(tǒng)任務(wù)。
PT-OSC(pt-online-schema-change)工具特點與優(yōu)勢是支持并發(fā) DML 操作。
GitHub’s Online Schema Transformer(GH-OST)
GH-OST 是 GitHub 的在線表定義轉(zhuǎn)換器，與 PT-OSC 的最大區(qū)別，在于 GH-OOST 的無觸發(fā)器設(shè)計。
至此，我們對比下 Online DDL、PT-OSC 和 GH-OST 的優(yōu)缺點：
(引自：吳夏《在線DDL原理、對比分析和實踐》)
總結(jié)
傳統(tǒng)的 DDL，多數(shù)的 ALTER TABLE 操作是通過創(chuàng)建一個滿足需求的新表，之后拷貝數(shù)據(jù)到新表，在用新表替換老表，整個過程會加鎖，不支持并發(fā) DML。在 MySQL 5.5 版本中，以 InnoDB Plugin 方式，優(yōu)化了新增和刪除索引的操作，避免了這種數(shù)據(jù) copy 的開銷，出現(xiàn)了 FIC。在 MySQL 5.6 開始增強了對各種 ALTER TABLE 操作支持，避免數(shù)據(jù) copy 的開銷，同時允許在 DDL 進行中，并發(fā)執(zhí)行 DML 操作。在 MySQL 5.7 實現(xiàn)了 ALTER TABLE RENAME INDEX 操作，即支持在線的索引重命名，這種特性的綜合，即 ONLINE DDL。PT-OST 通過改造原生 DDL 的方式，實現(xiàn)不鎖表的在線修改表結(jié)構(gòu)。
【本文是51CTO專欄作者張開濤的原創(chuàng)文章，作者微信公眾號：開濤的博客，id:kaitao-1234567】
