完美世界有声小说,女强穿越玄幻完结小说,小说改编的网页游戏

新聞中心

這里有您想知道的互聯網營銷解決方案

淺談DDL技術解密

首先，用過數據庫的小伙伴們(本文以 MySQL InnoDB 為例)都知道，MySQL 不止有增刪改數據操作(DML)，還有改表結構的操作(DDL)，當新增加字段等修改表結構時，就需要進行 DDL 操作?？墒?，如果對一個存儲了上百萬甚至上千上萬的數據表進行 DDL 操作，數據庫是怎么做到的呢?會不會有一個很大的事務鎖?會不會影響數據的插入和更新?今天就會聊聊這個問題，以及 PT-OSC、GH-OST 等技術，是如何高效的解決這個問題的。

目前成都創(chuàng)新互聯已為成百上千的企業(yè)提供了網站建設、域名、虛擬空間、網站改版維護、企業(yè)網站設計、萬年網站維護等服務，公司將堅持客戶導向、應用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協力一起成長，共同發(fā)展。

Before MySQL 5.5

在 MySQL 5.5 版本及之前版本，DDL 操作主要有 copy table 和 inplace 兩種方式。

1. Copy Table 方式

Copy Table 顧名思義，就是通過臨時表拷貝的方式實現的。在 MySQL 5.5 版本及之前版本，修改表結構是表級鎖，所以在整個 DDL 過程中表都是鎖著不可寫入的。這使得在修改時容易導致數據庫 CPU、IO 等性能的消耗，以及主從同步的延遲。

上述過程，MySQL 自動完成轉存數據，交換表名和刪除舊表等操作，時間消耗最多的是在往臨時表(Server 層)插入數據的過程，整個 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 兩種方式，我們看下官網的內容(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 就是添加或刪除二級索引的時候，可以不用復制原表，而是在創(chuàng)建或刪除二級索引時會對原表加上一個 S 鎖(共享鎖)，允許其他會話進行讀操作，但禁止寫操作，根據當前表數據創(chuàng)建索引，新索引創(chuàng)建完成之后，解除 S 鎖，允許寫操作。

FIC 在創(chuàng)建索引時不需要拷貝整表數據，但只對二級索引有效，對主鍵索引無效，對于主鍵索引的創(chuàng)建和刪除同樣需要重建一個臨時表。

對比 IN-Place 和 FIC，在網上查了一些資料，說“INPLACE 方式也稱為 InnoDB fast index creation”，那兩個應該不是一回事?我們看下官網的內容(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 構建。

(引自：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í)行的過程中依然保證可以讀寫，不影響數據庫對外提供服務。

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 方式根據是否涉及記錄格式的修改又分為：Rebuilds Table 和 No-Rebuilds Table，我們看下官方給出的內容(MySQL 5.7)：

(引自：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html)

基于 Online 對比 COPY 和 INPLACE，COPY 方法從表中的數據導出來的存放位置叫作 tmp_table，這是一個臨時表，是在 server 層創(chuàng)建的。INPLACE 方法從表中重建出來的數據是放在 tmp_file 里的，這個臨時文件是 InnoDB 在內部創(chuàng)建出來的，整個 DDL 過程都在 InnoDB 內部完成。

Online DDL 實現過程主要包括三個階段：Initialization 階段， Execution 階段，Commit Table Definition 階段。我們看下官方給出的內容(MySQL 8.0)：

(引自：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html)

我們詳細描述下這三個過程的過程：

(1) Initialization

創(chuàng)建 frm 臨時文件
持有EXCLUSIVE_MDL鎖，禁止讀寫
根據 ALTER 操作，確定執(zhí)行過程(COPY，Online-Rebuilds，Online-No-Rebuilds)
更新數據字典的內存對象
若是需要 Rebuilds，分配 row_log 對象記錄增量
若是需要 Rebuilds，新建 ibd 臨時文件

(2) Execution(如果僅修改 MetaData，則無此部操作)

降低EXCLUSIVE-MDL鎖，允許讀寫(COPY 僅允許讀)
記錄執(zhí)行期間產生的 DML 操作到 row_log(僅 Rebuilds 需要)
掃描老表的聚集索引中每一條記錄 record
遍歷新表的聚集索引和二級索引，逐一處理
根據 record 構造對應的索引項
將構造的索引項插入 sort_buffer 塊中
將 sort_buffer 塊插入新的索引
將 row_log 中的記錄應用到新臨時表，應用到最后一個 Block

(3) Commit Table Definition

升級到EXECLUSIVE-MDL鎖，禁止讀寫
重做 row_log 中最后一部分增量
更新 InnoDB 的數據字典
提交事務，寫 InnoDB redo 日志
修改統(tǒng)計信息
Rename 臨時的 ibd 和 frm 文件
DDL 執(zhí)行變更

我理解，Online DDL 中的 COPY 和 INPLACE 的區(qū)別在于有沒有原地，COPY 會將數據從 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 已經不維護了，全部歸并到 Percona Toolkit。Percona Toolkit 是一組高級的命令行工具，用來管理 MySQL 和系統(tǒng)任務。

PT-OSC(pt-online-schema-change)工具特點與優(yōu)勢是支持并發(fā) DML 操作。

GitHub’s Online Schema Transformer(GH-OST)

GH-OST 是 GitHub 的在線表定義轉換器，與 PT-OSC 的最大區(qū)別，在于 GH-OOST 的無觸發(fā)器設計。

至此，我們對比下 Online DDL、PT-OSC 和 GH-OST 的優(yōu)缺點：

(引自：吳夏《在線DDL原理、對比分析和實踐》)

總結

傳統(tǒng)的 DDL，多數的 ALTER TABLE 操作是通過創(chuàng)建一個滿足需求的新表，之后拷貝數據到新表，在用新表替換老表，整個過程會加鎖，不支持并發(fā) DML。在 MySQL 5.5 版本中，以 InnoDB Plugin 方式，優(yōu)化了新增和刪除索引的操作，避免了這種數據 copy 的開銷，出現了 FIC。在 MySQL 5.6 開始增強了對各種 ALTER TABLE 操作支持，避免數據 copy 的開銷，同時允許在 DDL 進行中，并發(fā)執(zhí)行 DML 操作。在 MySQL 5.7 實現了 ALTER TABLE RENAME INDEX 操作，即支持在線的索引重命名，這種特性的綜合，即 ONLINE DDL。PT-OST 通過改造原生 DDL 的方式，實現不鎖表的在線修改表結構。

【本文是專欄作者張開濤的原創(chuàng)文章，作者微信公眾號：開濤的博客，id:kaitao-1234567】

戳這里，看該作者更多好文

當前標題：淺談DDL技術解密
URL鏈接：http://fisionsoft.com.cn/article/cdpdgoj.html

新聞中心

其他資訊