MySQL InnoDB Online DDL學習

正文

MySQL Online DDL這個新特性是在MySQL5.6.7開始支持的，更早期版本的MySQL進行DDL對於DBA來說是非常痛苦的。現在主流版本都集中在5.6與5.7，為了更好的理解Online DDL的工作原理與機制，本文就對Online DDL的實現方式進行總結。

本文使用的MySQL版本為官方社區版 5.7.24。

(root@localhost) [test] > select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

主要說明

Online DDL這個新特性解決了早期版本MySQL進行DDL操作同時帶來鎖表的問題，在DDL執行的過程當中依然可以保證讀寫狀態，不影響資料庫對外提供服務，大大提高了資料庫和表維護的效率。

• 早期實現方式(MySQL5.6.7之前版本)

早期版本MySQL執行DDL語句時主要通過以下方式進行：

COPY方式：
這是InnoDB最早期支持的方式，主要實現步驟：

1. 創建與原表結構定義一致的臨時表；
2. 對原表加鎖，不允許執行DML，但允許查詢；
3. 在臨時表上執行DDL語句；
4. 逐行拷貝原表數據到臨時表；
5. 原表與臨時表進行RENAME操作，此時會升級原表上的鎖，不允許讀寫，直至完成DDL操作；

INPLACE方式：
INPLACE方式也稱為InnoDB fast index creation，是MySQL5.5及之後版本為了提高創建二級索引效率的方式，所以INPLACE方式僅限於二級索引的創建跟刪除，關於fast index creation可以參考官方文檔：InnoDB fast index creation，主要實現步驟：

1. 創建臨時的frm文件；
2. 對原表加鎖，不允許執行DML，但允許查詢；
3. 根據聚集索引的順序，構造新的索引項，按照順序插入新索引頁；
4. 升級原表上的鎖，不允許讀寫操作；
5. 進行RENAME操作，替換原表的frm文件，完成DDL操作。

相對於COPY方式，INPLACE方式在原表上進行，不會生成臨時表，也不會拷貝原表數據，減少了很多系統I/O資源占用，但還是無法進行DML操作，也只適用於索引的創建與刪除，並不適用於其他類型的DDL語句。

• 當前實現方式(MySQL5.6.7及之後版本)

在MySQL5.6.7及之後版本中推出了新的特性：

Online DDL方式：
Online DDL特性是基於MySQL5.5的InnoDB fast index creation上改進增強的。Online DDL同樣包含兩種方式：

1. COPY方式；
2. INPLACE方式。

其中，某些DDL語句不支持Online DDL的就採用COPY方式，支持Online DDL的則採用INPLACE方式，因為Online DDL是對早期INPLACE方式的增加，所以INPLACE方式根據是否涉及到記錄格式的修改又分為如下兩種情形：

1. Rebuilds Table；
2. No-Rebuilds Table。

Rebuilds Table操作是因為DDL有涉及到行記錄格格式的修改，如欄位的增、刪、類型修改等；
No-Rebuilds Table則不涉及行記錄格式的修改，如索引刪除、欄位名修改等。

Online DDL選項

• ALGORITHM={COPY|INPLACE}
  指定DDL執行時對錶的操作方式。首選是INPLACE，但並非所有的語句都支持INPLACE，需要根據DDL語句類型決定。

• LOCK={NONE|SHARED|DEFAULT|EXCLUSIVE}
  指定DDL執行時對錶鎖定方式。預設情況下MySQL在表執行DDL時會儘量使用最少的鎖定，LOCK選項可以為DDL語句指定執行更為嚴格的鎖定方式，一旦指定的鎖級別低於DDL語句執行所需的鎖級別，則DDL語句會執行失敗。
  • NONE：允許併發查詢和DML操作；
  • SHARED：允許併發查詢，但不允許DML操作；
  • DEFAULT：允許儘可能多的併發查詢或DML操作(或兩者都允許)，沒指定LOCK選項預設就為DEFAULT；
  • EXCLUSIVE：不允許併發查詢和DML操作。

Online DDL類型

根據官方文檔Online DDL Operations的描述，結合常見的表DDL執行語句，MySQL5.7支持的Online DDL操作類型主要有以下種類：

1. 索引操作
2. 欄位操作
3. 組合欄位操作
4. 主鍵操作
5. 外鍵操作
6. 表操作
7. 表分區操作
8. 表空間操作

索引操作

索引操作類型如下表所示：

由以上表格可以看出涉及索引的DDL操作都可以使用INPLACE方式來完成，除了創建全文索引與空間索引之外都允許DML操作，並不會鎖表。

-- 創建添加二級索引
create index index_name on table_name (column[,column]..);
或
alter table table_name add index index_name (column[,column]..);

-- 刪除索引
drop index index_name on table_name;
或
alter table table_name drop index index_name;

-- 重命名索引
alter table table_name rename index old_index_name to new_index_name, algorithm=inplace, lock=none;

-- 創建全文索引
create fulltext index index_name on table_name(column[,column]..);

-- 創建空間索引
create table geom (g geometry not null);
alter table geom add spatial index(g), algorithm=inplace, lock=none;

-- 修改索引類型
alter table table_name drop index index_name, add index index_name(column[,column]..) using btree, algorithm=inplace;

欄位操作

欄位操作類型如下表所示：

從以上表格可以看出，除了修改欄位類型的DDL語句無法使用INPLACE方式外，其他都可以使用到INPLACE方式執行DDL語句。

-- 添加欄位，如果添加的是自增列，還是不允許DML操作
alter table table_name add column column_name(column_definition), algorithm=inplace, lock=none;

-- 刪除欄位
alter table table_name drop column column_name, algorithm=inplace, lock=none;

-- 重命名欄位
/*
為了允許併發DML操作，需保持重命名後欄位類型一致，只修改欄位名；
如果重命名的欄位在外鍵定義中，外鍵定義也會自動更新為新欄位名。
*/
alter table table_name change old_column_name new_column_name data_type, algorithm=inplace, lock=none;

-- 重排序欄位，使用first或after
after table table_name modify column column_name column_definition first, algorithm=inplace, lock=none;

-- 欄位修改類型，只支持COPY方式
alter table table_name change column column column_definition, algorithm=copy;

-- 擴展VARCHAR欄位大小
/*
當varchar位元組長度為0~255時，需要額外一個位元組進行編碼；
當varchar位元組長度大於255時，則需要額外兩個位元組進行編碼；
當varchar位元組長度在0~255之間時，並且需從小變大的情況，支持INPLACE方式；
當varchar位元組長度位元組編碼數從1變為2或者從2變為1時，則需要用COPY方式。
*/
alter table table_name change column column column_definition, algorithm=inplace, lock=none;
-- 例表：
t(c1 varchar(20))
alter table t change c1 c1 varchar(85), algorithm=inplace, lock=none;
alter table t change c1 c1 varchar(10), algorithm=copy;
alter table t change c1 c1 varchar(100), algorithm=copy;

-- 欄位指定預設值
alter table table_name alter column column_name set default literal, algorithm=inplace, lock=none;

-- 刪除欄位預設值
alter table table_name alter column column_name drop default, algorithm=inplace, lock=none;

-- 修改自增列值
alter table table_name auto_increment=next_value, algorithm=inplace, lock=none;

-- 欄位指定NULL
alter table table_name modify column column_name data_type NULL, algorithm=inplace, lock=none;

-- 欄位指定NOT NULL
-- sql_mode中需包含選項STRICT_TRANS_TABLES和STRICT_ALL_TABLES才能使用INPLACE，否則需使用COPY
alter table table_name modify column column_name data_type NOT NULL, algorithm=inplace, lock=none;

-- 修改枚舉(ENUM OR SET)定義值
-- 例表：
t (c2 enum('a','b','c'))
alter table t modify column c2 enum('a','b','c','d'), algorithm=inplace, lock=none;

組合欄位操作

組合欄位操作類型如下表所示：

-- 例表：
t (c1 int,c2 varchar(20))
-- 添加存儲組合欄位
alter table t add column (c3 int generated always as (c1 + 2) stored), algorithm=copy;

-- 刪除存儲組合欄位
alter table t drop column c3, algorithm=inplace, lock=none;

-- 修改存儲組合欄位順序
alter table t modify column c3 int generated always as (c1 + 1) stored first, algorithm=copy;

-- 添加虛擬組合欄位
-- 對於非分區表才可以使用INPLACE方式，不能與其他的alter table語句合併使用
alter table t add column (c3 int generated always as (c1 + 1) virtual), algorithm=inplace, lock=none;

-- 刪除虛擬組合欄位
-- 對於非分區表才可以使用INPLACE方式，不能與其他的alter table語句合併使用
alter table t drop column c3, algorithm=inplace, lock=none;

-- 修改虛擬組合欄位順序
alter table t modify column c3 int generated always as (c1 + 1) virtual first, algorithm=copy;

主鍵操作

主鍵操作類型如下表所示：

-- 例表：
t (c1 int,c2 varchar(20))
-- 添加主鍵
alter table t add primray key (c1),algorithm=inplace, lock=none;

採用INPLACE方式進行表數據重構，如果添加主鍵涉及欄位沒有NOT NULL屬性時，則無法使用INPLACE方式，只能使用COPY方式。InnoDB表為索引組織表，當涉及到聚集索引的重新構建時需要對錶中數據進行拷貝，為了減少性能開銷，最好在建表時就指定主鍵。

因為InnoDB表的特殊性，DDL操作主鍵一定會涉及到表行數據的拷貝操作，但通過INPLACE方式添加比COPY方式添加主要有如下優勢：
1.不需要記錄undo和redo日誌，記錄日誌會提升性能開銷；
2.二級索引數據行是預先排序的，可以按順序載入；
3.無需使用到change buffer，因為沒有隨機數據插入二級索引當中。

-- 刪除主鍵
alter table t drop primary key,algorithm=copy;

-- 刪除主鍵並添加新主鍵
alter table t drop primary key,add primary key(c1,c2), algorithm=inplace, lock=none;

外鍵操作

外鍵操作類型如下表所示：

-- 添加外鍵
當系統參數foreign_key_checks = 0時，可以使用INPLACE方式，否則，只能使用COPY方式。
alter table t1 add constraint fk_name foreign key index(col1) references t2(col2) referential_actions;

-- 刪除外鍵
alter table t drop foreign key fk_name;

表操作

表操作類型如下表所示：

-- 修改行格式
alter table table_name row_format = format, algorithm=inplace, lock=none;

-- 修改索引鍵塊大小
alter table table_name key_block_size = value, algorithm=inplace, lock=none;

-- 設置永久表統計信息選項
alter table table_name stats_persistent = 0, stats_sample_pages = 20, stats_auto_recalc = 1, algorithm=inplace, lock=none;

-- 優化表
-- 如果表中有全文索引，則不能使用INPLACE方式，不能使用algorithm和lock子句。
optimize table table_name;

-- 使用FORCE選項重建表
-- 如果表中有全文索引，則不能使用INPLACE方式。
alter table table_name force, algorithm=inplace, lock=none;

-- 使用NULL重建表
-- 如果表中有全文索引，則不能使用INPLACE方式。
alter table table_name engine = InnoDB, algorithm=inplace, lock=none;

-- 重命名錶
alter table table_name rename to new_table_name, algorithm=inplace, lock=none;

表分區操作

表分區操作類型如下表所示：

表空間操作

表空間操作類型如下表所示：

主要涉及獨立表空間加密的Online DDL操作：

alter table table_name encryption='Y', algorithm=copy;

Online DDL過程

Online DDL主要有PREPARE(準備)、EXECUTE(執行)和COMMIT(提交)三個階段，如下：

• PREPARE：
  • 創建新的臨時frm文件；
  • 持有EXCLUSIVE-MDL鎖，禁止讀寫操作；
  • 根據ALTER類型，確定執行方式(copy,Online-Rebuilds,Online-No-Rebuilds)；
  • 更新數據字典的記憶體對象；
  • 分配row_log對象記錄增量(Rebuilds需要)；
  • 生成新的臨時ibd文件(Rebuilds需要)。
• EXECUTE：
  • 降級EXCLUSIVE-MDL鎖，允許讀寫；
  • 記錄執行期間產生的DML增量到row_log中(Rebuilds需要)；
  • 掃描old_table的聚集索引中每一條記錄record；
  • 遍歷新表的聚集索引和二級索引，逐一處理；
  • 根據record構造對應的索引項；
  • 將構造的索引項插入sort_buffer塊中；
  • 將sort_buffer塊插入到新的索引中；
  • 將row_log中的記錄應用到新臨時表中，應用到最後一個block；
• COMMIT：
  • 升級到EXECLUSIVE-MDL鎖，禁止讀寫；
  • 重做row_log中最後一部分的增量；
  • 更新InnoDB的數據字典表；
  • 提交事務，寫InnoDB redo日誌；
  • 修改統計信息；
  • RENAME臨時的ibd和frm文件；
  • 執行變更完成。

row_log記錄了DDL執行期間產生的DML操作，這保證了變更期間表的併發性，通過以上過程可以看出在EXECUTE(執行)階段表允許讀寫操作，操作記錄在row_log中，在最後階段應用到新表當中，保證了數據的完整性。

Online DDL涉及參數

• old_alter_table

指定是否使用早期版本的DDL方式，預設為OFF，為動態參數，可以全局和會話級別修改。指定表DDL的執行過程當中採用COPY方式生成臨時表複製數據。

• innodb_online_alter_log_max_size

指定Online DDL執行期間產生臨時日誌文件的最大大小，單位位元組，預設大小為128MB。日誌文件記錄的是表在DDL期間的數據插入、更新和刪除信息(DML操作)，一旦日誌文件超過該參數指定值時，DDL執行就會失敗並回滾所有未提交的當前DML操作，所以，當執行DDL期間有大量DML操作時可以提高該參數值，但同時也會增加DDL執行完成時應用日誌時鎖定表的時間。

Online DDL註意事項

對於線上環境的MySQL來說，任何類型的DDL都要十分謹慎，最好在語句執行之前可以分析下語句所使用的方式以及預估判斷下影響的時長，儘量選擇在業務訪問的低峰期進行操作，主要有以下幾點需要註意：

• 空間需求
  • 臨時日誌文件大小(innodb_online_alter_log_max_size)
    當DDL執行過程當中允許併發執行DML操作時的日誌大小需求。
  • 臨時排序文件大小(tmpdir)
    當DDL執行過程中表需要rebuild時臨時排序文件是放在tmpdir指定的路徑下的，需要保證該路徑下的磁碟空間充足。臨時排序文件都足夠容納所有二級索引以及聚簇索引的主鍵列，最終合併到新表或索引後，臨時排序文件會被刪除。在MySQL5.7.11及之後版本當中新增系統參數innodb_tmpdir專門用來指定Online DDL產生排序文件的路徑。
  • 臨時中間表文件大小
    當有些DDL執行過程中表需要rebuild時會在當前表所在路徑下產生臨時中間表文件，臨時中間表文件大小可能需要與原表大小一致，在DDL執行過程當中產生。

• 合併拆分同表的DDL操作
  早期不支持Online DDL時通常將同一張表中的多個DDL合併一起執行，以便減少多次rebuild錶帶來的性能消耗；
  現在Online DDL特性出現之後，可以通過COPY方式和INPLACE方式來進行分類併合並分組。其中INPLACE方式又可以根據是否rebuild表來進行分組合併，儘量減少DDL對系統的CPU、I/O資源的影響。

• 對於一些大表進行Online DDL並需要重建表的操作
  • 現在還沒有機制可以做到暫停Online DDL的操作或者限制Online DDL對伺服器CPU、I/O資源的使用；
  • 如果Online DDL執行失敗，則回滾有可能會是一項昂貴的操作；
  • 執行時間過長的Online DDL可能會導致主從複製的延遲。因為主庫在執行DDL時可能允許DML併發操作，而在從庫只能在執行完DDL語句之後再進行應用DML語句操作。

總結

總之，對於線上環境的DDL語句執行同樣也需要保持敬畏之心，無論執行的DDL語句是複雜還是簡單，最好可以評估下執行成本，還有需要選擇在業務低峰期進行操作。

參考

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html
https://dev.mysql.com/doc/refman/5.5/en/innodb-create-index.html
http://www.cnblogs.com/cchust/p/4639397.html
http://www.cnblogs.com/xinysu/p/6732646.html
http://hedengcheng.com/?p=421

☆〖本人水平有限，文中如有錯誤還請留言批評指正！〗☆