MySQL在其版本迭代後Online DDL功能發生了哪些變化？

DDL變更是MySQL運維當中，避免不了的日常工作，也是影響比較大的操作。為了減少風險，開源社群提供了gh-ost、pt-online-schema-change這樣便利的工具，解決了一些堵塞問題，但還是存在很多不確定的因數。

比如當資料量較大的表需要表更時：

• 建立觸發器過程中窮住了

• binlog同步發生了延遲，導致DDL無法完成

• 執行過程中，記憶體佔滿、CPU 100% 、IO等待
  最終導致阻塞讀寫請求(DML操作阻塞)，影響系統的可用性。

所以DDL操作，要考慮這些因素，避開高峰期，操作評估，預備手段需要準備好。

除了上述方式，官方的Online DDL也是一種很好的方式。Online DDL是在MySQL 5.6版本後加入的特性，用於支援DDL執行期間DML語句的並行操作，提高資料庫的吞吐量。目前迭代到的8.0，瞭解一下都有那些變化。

1. Online DDL

MySQL線上DDL特性提供了即時支援instant 、copy方式，還有原表in-place方式。 有些過程中也允許併發DML。

關鍵點：

就是ALGORITHM，LOCK 這個指標。

ALTER TABLE tbl_name ,alter_option: {...},ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

為了避免在執行ALTER TABLE操作時表可讀或不可寫，可以在ALTER TABLE語句中指定一個子句，如果請求是併發級別且不可用，則操作立即停止。

構建方式：

• COPY:對原始表的一個副本執行操作，將表資料從原始表逐行復制到新表。不允許併發DML。

  透過臨時表複製的方式實現的。新建一個帶有新結構的臨時表，將原表資料全部複製到臨時表，然後Rename，完成建立操作。可能記憶體、IO、CPU、硬碟空間使用上升。

• INPLACE:操作避免複製表資料。在操作的準備和執行階段，可以簡單地使用表上的獨佔後設資料鎖，通常支援併發DML。所謂INPLACE，也就是在原表上直接進行，不會複製臨時表。與COPY操作相比，向緩衝池中讀取的資料更少，這減少了從記憶體中清除頻繁訪問的資料。

• INSTANT:操作僅修改資料字典中的後設資料。在準備和執行期間，表上沒有獨佔後設資料鎖，並且表資料不受影響，這使得操作是瞬時的。允許併發DML。(MySQL8.0.12開始)
  上述圖中 符號就是即時操作。

備註：

可以理解COPY方式是把資料抽出來，再灌入進去，類似於Server層的操作。可以支援所有引擎。

INPLACE方式是在InnoDB引擎層完成，比如會多個frm 、ibd方式。底層 I nnoDB引擎在協助處理這個過程。

INSTANT就是後設資料的更改。

鎖級別：

• LOCK=NONE：允許併發查詢和DML(允許讀和寫) 。

• LOCK=SHARED：允許併發查詢，但阻塞DML (允許讀)

• LOCK=EXCLUSIVE：阻塞併發查詢和DML (獨佔後設資料鎖），儘可能短的時間內完成DDL操作。特別是伺服器空閒時候，可以使用這個子句。

2.  Online DDL的實現過程

線上DDL操作可以被看作有三個階段:

階段1: Initialization初始化

在初始化階段，伺服器將考慮儲存引擎功能、語句中指定的操作以及使用者指定的ALGORITHM和LOCK選項，確定操作期間允許多少併發性。在此階段，使用一個共享的可升級後設資料鎖來保護當前表定義。

階段2:Execution執行

在這個階段，語句是準備和執行的。後設資料鎖是否升級為排他鎖取決於初始化階段評估的因素。如果需要獨佔後設資料鎖，則只在語句準備期間短暫地使用它。

階段3:Commit Table Definition 提交表定義

在提交表定義階段，後設資料鎖升級為排他鎖，以排除舊錶定義並提交新表定義。一旦授予，獨佔後設資料鎖的持續時間就會很短。

3. Online DDL要求

與基本存放臨時日誌檔案的空間有關係：

• 當線上DDL操作建立索引或修改表時，一個臨時日誌檔案記錄併發的DML。臨時日誌檔案根據innodb_sort_buffer_size的值擴充套件到innodb_online_alter_log_max_size指定的最大值。如果臨時日誌檔案的大小超過限制，則線上DDL操作失敗（出現DB_ONLINE_LOG_TOO_BIG錯誤），未提交的併發DML操作將回滾。較大的innodb_online_alter_log_max_size設定允許在線上DDL操作期間使用更多的DML，但也會延長鎖定表以應用日誌DML時DDL操作結束的時間。

• 用於臨時排序檔案的空間

  建表的線上DDL操作在建立索引時將臨時排序檔案寫入MySQL臨時目錄(Unix上的$TMPDIR, Windows上的%TEMP%，或由——TMPDIR指定的目錄)。在包含原始表的目錄中不會建立臨時排序檔案。每個臨時排序檔案都足夠大，可以容納一列資料，當將每個排序檔案的資料合併到最終的表或索引中時，每個排序檔案都會被刪除。涉及臨時排序檔案的操作可能需要的臨時空間等於表中的資料量加上索引。如果線上DDL操作使用了資料目錄所在檔案系統上的所有可用磁碟空間，則報告一個錯誤。

  如果MySQL臨時目錄不夠大，不能儲存排序檔案，那麼將tmpdir設定為另一個目錄。或者，使用innodb_tmpdir為線上DDL操作定義一個單獨的臨時目錄。

• 中間表檔案的空間

  一些重建表的線上DDL操作在與原始表相同的目錄中建立一個臨時中間表檔案。中間表檔案可能需要與原始表大小相等的空間。中間表檔名以#sql-ib字首開始，僅線上DDL操作期間短暫出現。innodb_tmpdir選項不適用於中間表檔案。

4. 失敗條件

• ALGORITHM子句指定了與特定型別的DDL操作或儲存引擎不相容的演算法。

• LOCK子句指定與特定型別的DDL操作不相容的低階別鎖定(SHARED或NONE)。

• 在等待表上的排他鎖時發生超時，這可能在DDL操作的初始和最終階段短暫需要。

• tmpdir或innodb_tmpdir檔案系統的磁碟空間耗盡。

• 併發的DML修改了很多表，以至於臨時線上日誌的大小超過了innodb_online_alter_log_max_size配置選項的值。這種情況會導致DB_ONLINE_LOG_TOO_BIG錯誤。

• 併發DML對原始表定義允許的表進行更改，但新表定義不允許。當MySQL嘗試應用併發DML語句的所有更改時，這個操作只會在最後失敗。例如，在建立唯一索引時，可以在列中插入重複值，或者在列上建立主鍵索引時，可以在列中插入NULL值。併發DML所做的更改優先，並且ALTER TABLE操作被有效回滾。

5.  執行進度監控

獨佔後設資料鎖（exclusive）：線上DDL操作可能必須等待持有表上後設資料鎖的併發事務提交或回滾。透過show processlist通常看到窮住的情況如下

State: Waiting for table metadata lock

• 後設資料鎖資訊還透過performance_schema庫檢視

mysql> SELECT  * FROM  performance_schema.metadata_locks\G;

• 使用效能模式更改表監視：

mysql> UPDATE performance_schema.setup_instruments


      SET ENABLED = 'YES'
       WHERE NAME LIKE 'stage/innodb/alter%';

再透過performance_schema.events_stages_current表來檢查ALTER TABLE操作的進度。顯示的階段事件根據當前正在進行的ALTER TABLE階段的不同而不同。

mysql>
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED 
FROM performance_schema.events_stages_current; 

+
------------------------------------------------------+----------------+----------------+


| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | 

+
------------------------------------------------------+----------------+----------------+


| stage/innodb/
alter 
table (
read PK 
and internal 
sort) | 
280 | 
1245 | 

+
------------------------------------------------------+----------------+----------------+



1 
row 
in 
set (
0.01 sec)

備註：

1）WORK_COMPLETED列顯示已完成的工作。

2）work_estimate列提供了對剩餘工作的估計。

6.  總結

線上DDL變更可能帶來的風險，如果操作失敗，回滾線上DDL操作的代價可能很高。

• 修改大表結構執行時間往往不能預估，一般時間較長，可能帶來的風險有：修改表結構是表級鎖，影響DML寫入操作。

• 修改大表耗時較長，中途寫入失敗需要進行回滾，回滾這段時間也是不可寫入。

• 修改大表結構容易導致資料庫CPU、IO效能損耗，降低MySQL服務效能。

• 修改大表結構容易造成主從延遲加大，影響業務讀取。

需要DBA介入評估。

其實最好的方式在從庫上修改表結構，將結構變更了的從庫設定為主庫。