MySQL的DDL, 討論的背景是MySQL 8.0+InnoDB。
- DDL(Data Definition Language)
- 眾所周知, DDL定義了數據在數據庫中的結構、關係以及權限等。比如CREATE，ALTER，DROP等等。本期我們討論MySQL 8.0(使用InnoDB存儲引擎)在修改表結構時, 究竟會發生什麼？
- DDL與表結構
- 既然DDL的作用是改變表結構，那表結構在InnoDB引擎中是什麼樣的呢？如上圖，邏輯上, InnoDB表中的數據 可以理解成 按照主鍵(聚簇索引)順序存放的，每一行的數據依次排列 (物理上, InnoDB表中的數據按照InnoDB的數據結構B+樹進行排列)。當需要對錶增加一列時，會涉及到每一行數據排列的變動，需要重建整張表的數據，可想而知這種變動的成本是高昂的。
- 然而並不是每一種DDL都要付出這麼大的成本，要看具體的分類。MySQL 8.0 將DDL用以下五個維度分類討論:
- Instant: 此變更可以"立刻"完成
- In Place: 此變更由InnoDB引擎獨立完成, 不需要使用Redo log等, 可以節省開銷
- Rebuild Table: 此變更會重建聚簇索引, 一般情況下, 涉及到數據變更時才需要重建聚簇索引
- Permits Concurrent DML: 此變更進行時, 是否允許其他DML變更同一張表. 此特性關係到變更是否會長時間阻塞業務
- Only Modifies Metadata: 此變更是否只變更元信息, 不涉及數據變更
- 為了容易理解DDL的分類, 下圖中, 我們窮舉了MySQL DDL文檔中的分類, 列出了這五個維度的每種組合情況, 每種情況中分別挑選一例典型進行討論。以下分類是按照DDL的成本從低到高排序。
<code>e.g. ALTER TABLE `t1` ALTER COLUMN `c1` SET DEFAULT '1';/<code>
- 修改列的默認值不需要變動已有的數據頁，僅需要修改表的元信息即可，所以這是成本最低的一種情況，可以"立刻"完成。
<code>e.g. ALTER TABLE `t1` DROP INDEX `idx1`;/<code>
- 刪除二級索引除了修改表的元信息之外，需要將對應的二級索引標記為刪除狀態，因為不需要真的刪除，僅僅設置標記量，所以這仍然是一種成本較低的情況。
但由於需要等待所有訪問表的事務全部結束後才能成功，所以不算是"立刻"能完成的DDL。
<code>e.g. ALTER TABLE `t1` ADD INDEX `idx1` (`name`(10) ASC) ;/<code>
- 創建二級索引除了修改表元信息之外，還需要在存儲引擎層建立相應的二級索引結構。
為了支持併發的DML操作，MySQL還需要額外維護一份DDL期間的數據變更日誌，在DDL操作最後將併發的DML操作回放至新建的二級索引。不過由於二級索引是通過聚簇索引構造，不需要包含所有的行數據，所以這還不能算是一種較高成本的操作。
<code>e.g. ALTER TABLE `t1` DROP COLUMN `c1`;/<code>
- 刪除列和我們之前提到的增加列情況類似，由於需要改動數據行，MySQL在InnoDB引擎內部需要重建聚簇索引 (按照聚簇索引生成臨時表, 再取而代之)。同時，為了支持併發的DML操作，還需要維護DDL期間的數據變更日誌。可見當數據量較大時，這是一種非常高成本的操作。
<code>e.g. ALTER TABLE `t1` MODIFY COLUMN `c1` INTEGER;/<code>
- 變更數據列類型，按照文檔描述這是一種無法Inplace的操作，即需要MySQL在server層完成一次表的複製，相比由InnoDB內部完成重建，這種操作需要記錄Redo log，佔用更多的buffer pool。不過由於在執行過程中，無法併發DML操作，不需要記錄DDL期間的變更日誌。即便如此，這仍然是一種高成本的操作。
- 運維建議
- DDL應顯式指定ALGORITHM，從低成本(INSTANT)到高成本(COPY)逐一嘗試，當不匹配時MySQL會報錯。以防我們認為的一個低成本的DDL，因為認為失誤而需要重建表，造成運維事故。
- 在以前版本中，MySQL的DDL都需要重建表，所以會建議將一個表的多個變更寫在同一句DDL中，用一次重建實施多個變更。
而現在，如果一句DDL中的多個變更的算法不同，那麼會使用其中最高成本的算法。運維中，需要仔細甄別情況，使得一部分變更可以更快完成上線。 - DDL語句允許我們選擇鎖類型和DDL類型，給予我們更好的自由度。
比如當執行刪除列時，MySQL默認使用的是Inplace Rebuild操作，鎖級別是None (允許併發讀寫)。如果業務可以妥協，那麼可以將鎖級別設置為SHARED (允許併發讀但阻塞寫)，這樣DDL可以更快完成。 - 思考題
- 本期我們說添加列需要重建表，而MySQL引入的騰訊團隊的Instant方案 目標就是 讓添加列"立刻"完成。那麼Instant版本的添加列是如何完成的?
- Instant 是否完全不影響業務，是否是真正的"立刻"完成?
- 對於Inplace Rebuild，重建數據的過程與併發的DML如何不互相干擾？
分享到:
閱讀更多 愛可生雲數據庫 的文章
