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