概述

今天主要介紹一下MySQL 8.0.19 instant add column的新特性，基於億級資料秒速增加欄位，下面一起來看看吧~

一、MySQL DDL 的方法

MySQL 在大型表上的 DDL 會帶來耗時較久、負載較高、額外空間佔用、MDL、主從同步延時等情況。需要特別引起重視，而MySQL 的 DDL 有很多種方法。

MySQL 本身自帶三種方法，分別是：copy、inplace、instant。

copy 演算法為最古老的演算法，在 MySQL 5.5 及以下為預設演算法。從 MySQL 5.6 開始，引入了 inplace 演算法並且預設使用。inplace 演算法還包含兩種型別：rebuild-table 和 not-rebuild-table。MySQL 使用 inplace 演算法時，會自動判斷，能使用 not-rebuild-table 的情況下會盡量使用，不能的時候才會使用 rebuild-table。當 DDL 涉及到主鍵和全文索引相關的操作時，無法使用 not-rebuild-table，必須使用 rebuild-table。其他情況下都會使用 not-rebuild-table。從 MySQL 8.0.12 開始，引入了 instant 演算法並且預設使用。目前 instant 演算法只支援增加列等少量 DDL 型別的操作，其他型別仍然會預設使用 inplace。

有一些第三方工具也可以實現 DDL 操作，最常見的是 percona 的 pt-online-schema-change 工具（簡稱為 pt-osc），和 github 的 gh-ost 工具，均支援 MySQL 5.5 以上的版本。

》》各類工具的對比

一般情況下的建議：

如果使用的是 MySQL 5.5 或者 MySQL 5.6，推薦使用 gh-ost如果使用的是 MySQL 5.7，索引等不涉及修改資料的操作，建議使用預設的 inplace 演算法。如果涉及到修改資料（例如增加列），不關心主從同步延時的情況下使用預設的 inplace 演算法，關心主從同步延時的情況下使用 gh-ost如果使用的是 MySQL 8.0，推薦使用 MySQL 預設的演算法設定，在語句不支援 instant 演算法並且在意主從同步延時的情況下使用 gh-ost二、MySQL DDL 的原理簡析1、copy 演算法

較簡單的實現方法，MySQL 會建立一個新的臨時表，把源表的所有資料寫入到臨時表，在此期間無法對源表進行資料寫入。MySQL 在完成臨時表的寫入之後，用臨時表替換掉源表。這個演算法主要被早期（<=5.5）版本所使用。

2、inplace 演算法

從 5.6 開始，常用的 DDL 都預設使用這個演算法。inplace 演算法包含兩類：inplace-no-rebuild 和 inplace-rebuild，兩者的主要差異在於是否需要重建源表。

inplace 演算法的操作階段主要分為三個：

Prepare階段： - 建立新的臨時 frm 檔案(與 InnoDB 無關)。 - 持有 EXCLUSIVE-MDL 鎖，禁止讀寫。 - 根據 alter 型別，確定執行方式（copy，online-rebuild，online-not-rebuild）。 更新資料字典的記憶體物件。 - 分配 row_log 物件記錄資料變更的增量（僅 rebuild 型別需要）。 - 生成新的臨時ibd檔案 new_table（僅rebuild型別需要）。Execute 階段：降級EXCLUSIVE-MDL鎖，允許讀寫。掃描old_table聚集索引（主鍵）中的每一條記錄 rec。遍歷new_table的聚集索引和二級索引，逐一處理。根據 rec 構造對應的索引項。將構造索引項插入 sort_buffer 塊排序。將 sort_buffer 塊更新到 new_table 的索引上。記錄 online-ddl 執行過程中產生的增量（僅 rebuild 型別需要）。重放 row_log 中的操作到 new_table 的索引上（not-rebuild 資料是在原表上更新）。重放 row_log 中的DML操作到 new_table 的資料行上。Commit階段：當前 Block 為 row_log 最後一個時，禁止讀寫，升級到 EXCLUSIVE-MDL 鎖。重做 row_log 中最後一部分增量。更新 innodb 的資料字典表。提交事務（刷事務的 redo 日誌）。修改統計資訊。rename 臨時 ibd 檔案，frm檔案。變更完成，釋放 EXCLUSIVE-MDL 鎖。3、instant 演算法

MySQL 8.0.12 才提出的新演算法，目前只支援新增列等少量操作，利用 8.0 新的表結構設計，可以直接修改表的 metadata 資料，省掉了 rebuild 的過程，極大的縮短了 DDL 語句的執行時間。

4、pt-online-schema-change

借鑑了 copy 演算法的思路，由外部工具來完成臨時表的建立，資料同步，用臨時表替換源表這三個步驟。其中資料同步是利用 MySQL 的觸發器來實現的，會少量影響到線上業務的 QPS 及 SQL 響應時間。

三、mysql 8.0特性instant add column

1、instant add column原理

mysql資料庫針對億級別的大表加欄位是痛苦的，需要對錶進行重建，MySQL 5.7 支援 Online DDL，大部分 DDL 不影響對錶的讀取和寫入，但是依然會消耗非常多的時間，且佔用額外的磁碟空間，並會造成主從延遲。所以大表 DDL 仍是一件令 DBA 頭痛的事。而mysql8.0使用instant ADD COLUMN特性，只需很短的時間，欄位就加好了，享受MongoDB那樣的非結構化儲存的靈活方便,無形中減少了開發的工作量。

快速加列採用的是 instant 演算法，使得新增列時不再需要 rebuild 整個表，只需要在表的 metadata 中記錄新增列的基本資訊即可。在 alter 語句後增加 ALGORITHM=INSTANT 即代表使用 instant 演算法， 如果未明確指定，則支援 instant 演算法的操作會預設使用。如果 ALGORITHM=INSTANT 指定但不支援，則操作立即失敗並顯示錯誤。

關於列的 DDL 操作，是否支援 instant 等演算法，官方文件給出了一個表格，整理如下，星號表示不是全部支援，有依賴項。

instant 演算法使用最廣泛的應該是新增列了，可以看到使用該演算法還是有些限制的，一些限制如下：

如果 alter 語句包含了 add column 和其他的操作，其中有操作不支援 instant 演算法的，那麼 alter 語句會報錯，所有的操作都不會執行。如果指定了AFTER，欄位必須是在最後一列，否則需要重建表；只能順序加列, 僅支援在最後新增列，而不支援在現有列的中間新增列。不支援壓縮表，即該錶行格式不能是 COMPRESSED。不支援包含全文索引的表。不支援臨時表。不支援那些在資料字典表空間中建立的表。DROP COLUMN需要重建表；modify修改欄位屬性需要重建表。

2、實驗測試

2.1、環境準備

2.1.1、安裝sysbench

wget https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.18yum -y install gcc gcc-c++  make automake libtool pkgconfig libaio-devel tar -xvf sysbench-1.0.18.tar.gzcd sysbench-1.0.18/./autogen.sh./configure --prefix=/usr/local/sysbench/ --with-mysql  --with-mysql-includes=/usr/include/mysql \-with-mysql-libs=/usr/lib64/mysql && make && make installecho "export PATH=$PATH:/usr/local/sysbench/bin" >> /etc/profilesource /etc/profile

2.1.2、資料準備

--準備2張表，每張表1億資料>create database sbtest;nohup sysbench --mysql-host=localhost --mysql-port=53306 --mysql-user=root --mysql-password=axxxpx \--test=/usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=2 \--oltp-table-size=100000000 --num-threads=50 --rand-init=on prepare &

2.2、新增欄位

mysql 5.7 用inplace演算法去對一億的表新增欄位，耗時接近10分鐘，MySQL8.0 用instant演算法對一億的表新增欄位，耗時0.12s

--指定InPlace演算法新增列，(5.7版本新增列使用該演算法)alter table sbtest1 add column cityname1 varchar(10) , algorithm=inplace;--指定 instant 演算法新增列（8.0版本新增列使用該演算法）alter table sbtest2 add column cityname2 varchar(10) , algorithm=instant;

alter table sbtest1 alter column cityname1 set default 'wuhan' ,algorithm=inplace,lock=default;alter table sbtest2 alter column cityname2 set default 'beijing',algorithm=instant,lock=default;alter table sbtest1 alter column cityname1  drop default ,algorithm=inplace;alter table sbtest2 alter column cityname2  drop default,algorithm=instant;   

2.4、修改列操作

--inplace演算法和instant演算法均不支援alter table sbtest1 modify cityname1 datetime;

alter table sbtest1 add column (d int generated always as (k+1) virtual),algorithm=inplace;alter table sbtest2 add column (d int generated always as (k+1) virtual),algorithm=instant;alter table sbtest1 drop column d,algorithm=inplace;alter table sbtest2 drop column d,algorithm=instant;                                      

2.6、增加帶有外來鍵的列

--設定ON UPDATE CURRENT_TIMESTAMP，表示在資料庫資料有更新的時候createtime的時間會自動更新alter table sbtest1 add column createtime datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,algorithm=inplace;alter table sbtest2 add column createtime datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,algorithm=instant;

2.7、修改表名

alter table sbtest1 rename to sbtest11, algorithm=inplace;alter table sbtest2 rename to sbtest22, algorithm=instant;

基於上面的測試可以發現，在快速加列功能上使用 instant 演算法新增列基本都在 1s 內完成，而使用 5.7 版本的 inplace 演算法時，則新增列的時間上升至數分鐘。對比看來 8.0 版本的這個特性確實很實用！

後面會分享更多devops和DBA方面內容，感興趣的朋友可以關注下！