MySQL5.7 InnoDB線上DDL操作

wuworker發表於2024-10-20
MySql

MYSQL官方文件：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

目錄
  • 線上DDL原理
  • 線上DDL支援情況
    • Index Operations（索引操作）
    • Primary Key Operations（主鍵操作）
    • Column Operations（列操作）
    • Table Operations（表操作）
  • pt-osc方式
  • 線上DDL和pt-osc對比
  • 參考

線上DDL原理

MySQL5.6開始支援線上DDL操作。在這之前，對錶的修改會阻塞整個表的讀寫操作。

在5.6之後，可以使用ALTER TABLE語句的ALGORITHM和LOCK子句來控制DDL操作的各個方面。這些子句位於語句的末尾，透過逗號與表和列規範分開。例如：

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

LOCK子句有助於微調對錶的併發訪問程度。ALGORITHM子句主要用於效能比較，以及作為在遇到任何問題時回落到舊的表複製行為的備用方案。

  • ALGORITHM=INPLACE 表示執行DDL的過程中不發生表複製，另一種是COPY，就是之前的複製方式。一般都是預設，讓 MySQL 以儘量保證 DML 併發操作的原則選擇執行方式。
  • LOCK=NONE 表示對 DML 操作不加鎖，DDL 過程中允許所有的 DML 操作。此外還有 EXCLUSIVE（持有排它鎖，阻塞所有的請求）、SHARED（允許SELECT，但是阻塞INSERT UPDATE DELETE）和 DEFAULT（根據DDL的型別，在保證最大併發的原則下來選擇LOCK的取值）。

2種演算法原理

  • COPY方式
    1. 按照原表定義建立一個新的臨時表；
    2. 對原表加寫鎖（禁止DML，允許select）；
    3. 在步驟1 建立的臨時表執行 DDL；
    4. 將原表中的資料 copy 到臨時表；
    5. 釋放原表的寫鎖；
    6. 將原表刪除，並將臨時表重新命名為原表。

採用 copy 方式期間需要鎖表，禁止DML，因此是非Online的

  • INPLACE方式
    在原表上進行更改，不需要生成臨時表，不需要進行資料copy的過程。根據是否行記錄格式，又可分為兩類：

    • rebuild：需要重建表（重新組織聚簇索引）
    • no-rebuild：不需要重建表，只需要修改表的後設資料

    對於 rebuild 方式實現 Online 是透過快取 DDL 期間的 DML，待 DDL 完成之後，將 DML 應用到表上來實現的。例如，執行一個 alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下：

    1. 建立一個臨時檔案，掃描表 A 主鍵的所有資料頁；
    2. 用資料頁中表 A 的記錄生成 B+ 樹，儲存到臨時檔案中；
    3. 生成臨時檔案的過程中，將所有對 A 的操作記錄在一個日誌檔案（row log）中；
    4. 臨時檔案生成後，將日誌檔案中的操作應用到臨時檔案，得到一個邏輯資料上與表 A 相同的資料檔案；
    5. 用臨時檔案替換表 A 的資料檔案。

說明：

  1. 在 copy 資料到新表期間，在原表上是加的 MDL 讀鎖（允許 DML，禁止 DDL）；
  2. 在應用增量期間對原表加 MDL 寫鎖（禁止 DML 和 DDL）；
  3. 根據表 A 重建出來的資料是放在 tmp_file 裡的，這個臨時檔案是 InnoDB 在內部建立出來的，整個 DDL 過程都在 InnoDB 內部完成。對於 server 層來說，沒有把資料挪動到臨時表，是一個原地操作，這就是”inplace”名稱的來源。

回滾

  • 使用Inplace方式執行的DDL，發生錯誤或被kill時，需要一定時間的回滾期，執行時間越長，回滾時間越長。
  • 使用Copy方式執行的DDL，需要記錄過程中的undo和redo日誌，同時會消耗buffer pool的資源，效率較低，優點是可以快速停止

執行流程
Online DDL主要包括3個階段，prepare階段，ddl執行階段，commit階段
image

  • prepare階段：持有EXCLUSIVE-MDL鎖，禁止讀寫（禁止DML，DDL）
  • ddl執行階段：降級EXCLUSIVE-MDL鎖，允許讀寫（copy不可寫）（允許DML，禁止DDL）
  • commit階段：升級EXCLUSIVE-MDL鎖，禁止讀寫（禁止DML，DDL）

Online DDL 過程中佔用 exclusive MDL 的步驟執行很快，所以幾乎不會阻塞 DML 語句。
不過，在 DDL 執行前或執行時，其他事務可以獲取 MDL（metadata lock）。由於需要用到 exclusive MDL，所以必須要等到其他佔有 metadata lock 的事務提交或回滾後才能執行上面兩個涉及到 MDL 的地方。

（MDL說明：在訪問一個表的時候會被自動加上，以保證讀寫的正確性。當對一個表做 DML 操作的時候，加 MDL 讀鎖；當做 DDL 操作時候，加 MDL 寫鎖）


雖然線上DDL不阻塞DML，但是對大表做操作，還是涉及到資料庫IO，CPU的消耗，儘量在業務低峰進行。

線上DDL支援情況

Index Operations（索引操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允許併發DML） Only Modifies Metadata（僅修改後設資料）
Creating or adding a secondary index Yes No Yes No
Dropping an index Yes No Yes Yes
Renaming an index Yes No Yes Yes
Adding a FULLTEXT index Yes* No* No No
Adding a SPATIAL index Yes No No No
Changing the index type Yes No Yes Yes

建立二級索引，In Place的方式執行，整個過程只會涉及到複製二級索引列相關的資料用於建立索引


建立/刪除二級索引，表仍然可以進行讀寫操作。這些操作只有在訪問表的所有事務完成後才結束


如果伺服器在建立二級索引時退出，在恢復時，MySQL會丟棄任何部分建立的索引。必須重新執行ALTER TABLE或CREATE INDEX語句


刪除索引，只是後設資料修改，實際索引佔用的儲存空間並不會立刻釋放。需要執行optimize table才能釋放出空間

Primary Key Operations（主鍵操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允許併發DML） Only Modifies Metadata（僅修改後設資料）
Adding a primary key Yes* Yes* Yes No
Dropping a primary key No Yes No No
Dropping a primary key and adding another Yes Yes Yes No

主鍵的修改，都會重建表，一般也不會進行此類修改，就不討論這種情況了。

Column Operations（列操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允許併發DML） Only Modifies Metadata（僅修改後設資料）
Adding a column Yes Yes Yes* No
Dropping a column Yes Yes Yes No
Renaming a column Yes No Yes* Yes
Reordering columns Yes Yes Yes No
Setting a column default value Yes No Yes Yes
Changing the column data type No Yes No No
Extending VARCHAR column size Yes No Yes Yes
Dropping the column default value Yes No Yes Yes
Changing the auto-increment value Yes No Yes No*
Making a column NULL Yes Yes* Yes No
Making a column NOT NULL Yes* Yes* Yes No
Modifying the definition of an ENUM or SET column Yes No Yes Yes

對於列的修改（新增，刪除，重新排序，變更列型別，修改列為空或非空）都會重建表（COPY的方式）


會鎖表的情況（不支援併發DML）：

  • 新增自增列：資料被大量重新組織，使其成為一個昂貴的操作。至少需要ALGORITHM=INPLACE, LOCK=SHARED。
  • 修改列型別

VARCHAR型別的修改：

  • VARCHAR列所需的長度位元組數必須保持不變。對於大小為0到255位元組的VARCHAR列，需要一個位元組來編碼值。對於大小為256位元組或更大的VARCHAR列，需要兩個位元組。因此，In Place ALTER TABLE只支援將VARCHAR列大小從0到255位元組增加，或從256位元組增加到更大的大小。In Place ALTER TABLE不支援將VARCHAR列大小從小於256位元組增加到等於或大於256位元組。在這種情況下，所需的長度位元組數從1變為2，這僅由表複製（ALGORITHM=COPY）支援。
  • 減少VARCHAR大小也需要表複製（ALGORITHM=COPY）支援

僅修改列名，設定/刪除預設值，僅修改後設資料資訊

Table Operations（表操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允許併發DML） Only Modifies Metadata（僅修改後設資料）
Changing the ROW_FORMAT Yes Yes Yes No
Changing the KEY_BLOCK_SIZE Yes Yes Yes No
Setting persistent table statistics Yes No Yes Yes
Specifying a character set Yes Yes* Yes No
Converting a character set No Yes* No No
Optimizing a table Yes* Yes Yes No
Rebuilding with the FORCE option Yes* Yes Yes No
Performing a null rebuild Yes* Yes Yes No
Renaming a table Yes No Yes Yes

一般常用的就Optimizing a table，對於具有FULLTEXT索引的表，不支援原地操作。操作使用INPLACE演算法，但不允許使用ALGORITHM和LOCK語法。


表的重新命名，也不需要複製。（您也可以使用RENAME TABLE語句重新命名錶。詳情請參閱RENAME TABLE語句。）但重新命名的表授予的許可權不會遷移到新名稱。必須手動更改

pt-osc方式

pt-osc 用於修改表時不鎖表，簡單地說，這個工具建立一個與原始表一樣的新的空表，並根據需要更改表結構，然後將原始表中的資料以小塊形式複製到新表中，然後刪除原始表，然後將新表重新命名為原始名稱。在複製過程中，對原始表的所有新的更改（insert，delete，update）都將應用於新表，因為在原始表上建立了一個觸發器，以確保所有新的更改都將應用於新表。

流程如下：

  1. 建立一個和要執行 alter 操作的表一樣的新的空表結構 (是 alter 之前的結構)；

  2. 在新表執行 alter table 語句（速度應該很快）；

  3. 在原表中建立觸發器 3 個觸發器分別對應 insert,update,delete 操作，如果表中已經定義了觸發器這個工具就不能工作了；

  4. 以一定塊大小從原表複製資料到臨時表，複製過程中透過原表上的觸發器在原表進行的寫操作都會更新到新建的臨時表，保證資料不會丟失（會限制每次複製資料的行數以保證複製不會過多消耗伺服器資源，採用 LOCK IN SHARE MODE 來獲取要複製資料段的最新資料並對資料加共享鎖阻止其他會話修改資料，不過每次加 S 鎖的行數不多，很快就會被釋放）；

  5. 將原表 Rename 為 old 表，再把新表 Rename 為原表（整個過程只在 rename 表的時間會鎖一下表，其他時候不鎖表）；

  6. 如果有參考該表的外來鍵，根據 alter-foreign-keys-method 引數的值，檢測外來鍵相關的表，做相應設定的處理（根據修改後的資料，修改外來鍵關聯的子表），如果被修改表存在外來鍵定義但沒有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執行；

  7. 預設最後將舊原表刪除、觸發器刪除。

限制

  1. 在使用此工具之前，應為表定義 PRIMARY KEY 或唯一索引，因為它是 DELETE 觸發器所必需的；

  2. 如果表已經定義了觸發器，則不支援 pt-osc ；（注：不是不能有任何觸發器，只是不能有針對 insert、update、delete 的觸發器存在，因為一個表上不能有兩個相同型別的觸發器）；

  3. 如果表具有外來鍵約束，需要使用選項 --alter-foreign-keys-method，如果被修改表存在外來鍵定義但沒有使用 --alter-foreign-keys-method 指定特定的值，該工具不予執行；

  4. 還是因為外來鍵，物件名稱可能會改變（indexes names 等）；

  5. 在 Galera 叢集環境中，不支援更改 MyISAM 表，系統變數 wsrep_OSU_method 必須設定為總序隔離（Total Order Isolation，TOI）；

  6. 此工具僅適用於 MySQL 5.0.2 及更新版本（因為早期版本不支援觸發器）；

  7. 需要給執行的賬戶在 MySQL 上授權，才能正確執行。（應在伺服器上授予 PROCESS、SUPER、REPLICATION SLAVE 全域性許可權以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表許可權。Slave 只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 許可權。）

線上DDL和pt-osc對比

指標 線上DDL pt-osc
鎖表風險 較低 極低
執行時間 較長 長
主從延遲 較大 小
需要額外空間 中 大
IO負載 中 大

對於常見的DDL選擇

  • 建立二級索引：線上DDL使用InPlace方式執行，整個過程，只涉及到複製二級索引列相關的資料用於建立索引，相對於pt-osc，複製資料量會少很多，磁碟IO相對pt-osc少。但是選擇線上DDL，會有主從延遲的問題

  • 刪除索引：線上DDL僅修改後設資料，選擇線上DDL就好

  • 新增列，刪除列，變更列型別，修改列為空或非空：線上DDL都會執行Rebuilds Table，而且還有主從延遲的問題。建議選擇pt-osc

  • 修改列名，設定/刪除預設值：線上DDL僅修改後設資料，選擇線上DDL就好

  • 增加/減少varchar欄位長度：參見上述Column Operations（列操作）。如果所需的長度位元組數從1變為2，建議選擇pt-osc

  • 執行Optimize table：線上DDL會執行Rebuilds Table，建議選擇pt-osc

  • 修改表名：線上DDL僅修改後設資料，選擇線上DDL就好

參考

https://zhuanlan.zhihu.com/p/567083306
https://zhuanlan.zhihu.com/p/633326199
https://blog.csdn.net/jdcdev_/article/details/140521811

相關文章