210_mysql_innodb_5_innodb_online_ddl
官網
https://dev.Mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-generated-column-operations
Mysql online DDL 原始方式
1. 按照表 A 的定義新建一個表 B 2. 對表 A 加寫鎖 3. 在表 B 上執行 DDL 指定的操作 4. 將 A 中的數據拷貝到 B 5. 釋放 A 的寫鎖 6. 刪除表 A 7. 將表 B 重命名為 A 在 2-4 的過程中，如果表 A 數據量比較大，拷貝到表 B 的過程會消耗大量時間，并占用額外的存儲空間。此外，由于 DDL 操作占用了表 A 的寫鎖，所以表 A 上的 DDL 和 DML 都將阻塞無法提供服務
一 online DDL
1.1 Online DDL劃分 在mysql 8.0上，對于Online DDL的討論主要從兩個角度進行了分類討論，
1通過加鎖范圍來區分不同ddl與dml的并發程度；
2根據是否拷貝數據來劃分不同的執行邏輯
SQL 方式
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
如果沒有指定ALGORITHM子句，系統決定，選擇最優的算法執行DDL。 用戶可以選用上述算法來執行，但本身收到DDL類型限制，如果指定的算法無法執行DDL，則ALTER操作會報錯
1 鎖與并發度劃分 Lock
通過LOCK關鍵字來指定DDL期間加鎖程度。其可選擇的值如下：
值
含義
NONE
對DML不加鎖, 允許并發查詢和DML
SHARED
允許并發查詢，但阻塞DML
DEFAULT
由數據庫決定選擇最大并發的模式，指定該類型與不指定LOCK關鍵字含義相同
EXCLUSIVE
阻塞查詢和DML
默認的情況下，MySQL在執行DDL操作期間盡可能少的使用鎖，以提高并發。當然也可以通過LOCK子句，來指定更加嚴格的鎖。但是，如果LOCK子句指定的鎖定級別低于特定DDL操作所允許的限制級別，則語句將失敗，并出現錯誤
2 拷貝數據，通過ALGORITHM關鍵字進行指定，值有如下幾種：
值
含義
COPY
是指DDL時，會生成（臨時）新表，將原表數據逐行拷貝到新表中，在此期間會阻塞DML
INPLACE
無需拷貝全表數據到新表，但可能還是需要IN-PLACE方式（原地，無需生成新的臨時表）重建整表，過程中允許并發執行DML? 這種情況下，在DDL的初始準備和最后結束兩個階段時通常需要加排他MDL鎖（metadata lock，元數據鎖），除此外，DDL期間不會阻塞DML
l? rebuild涉及表的重建，會在原表路徑下生成新的.frm和.ibd文件，同時申請row log空間記錄DDL執行期間的DML操作記錄，最后再DDL提交階段重做row log中的內容
l? no-rebuild不涉及表的重建，除了創建添加索引會產生二級索引的寫入操作外，其余操作只修改元數據信息，不會生成.ibd文件，并且不會申請row log空間，這種場景消耗IO較少，速度較快
INSTANT
instant：該特性是MySQL8.0.12引入，只修改數據字典的元數據信息，無需拷貝數據也無需重建表，原表數據不受影響。整個DDL過程執行非常快，不會阻塞DML操作
DEFAULT
系統決定，選擇最優的算法執行DDL
1.2 執行流程 ?Online DDL執行過程可以分為三個階段：
alter操作MDL鎖流程(copy方式 改主鍵/數據類型等) 1) Opening tables階段，加共享鎖 a) 加MDL_INTENTION_EXCLUSIVE鎖 b) 加MDL_SHARED_UPGRADABLE鎖，升級到MDL_SHARED_NO_WRITE SNRW鎖 2) 操作數據，copy data，流程如下： a) 創建臨時表tmp，重定義tmp為修改后的表結構 b) 從原表讀取數據插入到tmp表 3) 將MDL_SHARED_NO_WRITE讀鎖升級到MDL_EXCLUSIVE鎖 a) 刪除原表，將tmp重命名為原表名 4) 提交階段，釋放MDL鎖 a) 釋放MDL_INTENTION_EXCLUSIVE鎖 b) 釋放MDL_EXCLUSIVE鎖 Online DDL-- inplace 第一階段 ： Prepare階段 創建新的臨時frm文件(與InnoDB無關) 持有EXCLUSIVE-MDL X鎖，禁止讀寫 根據alter類型，確定執行方式(copy,online-rebuild,online-norebuild) 假如是Add Index，則選擇online-norebuild即INPLACE方式 更新數據字典的內存對象 分配row_log對象存儲空間記錄增量(僅rebuild類型需要,記錄在row_log里記錄執行階段的記錄增量) 生成新的臨時ibd文件(僅rebuild類型需要) 第二階段： ddl執行階段 降級EXCLUSIVE-MDL鎖為 MDL_SHARED_UPGRADABLE(SU) 允許讀寫 掃描old_table的聚集索引每一條記錄rec 遍歷新表的聚集索引和二級索引，逐一處理 根據rec構造對應的索引項 將構造索引項插入sort_buffer塊排序 將sort_buffer塊更新到新的索引上 記錄ddl執行過程中產生的增量(僅rebuild類型需要) 重放row_log中的操作到新索引上(no-rebuild數據是在原表上更新的) 重放row_log間產生dml操作append到row_log最后一個Block 第三階段： commit階段 當前Block為row_log最后一個時，禁止讀寫，升級到EXCLUSIVE-MDL X鎖 重做row_log中最后一部分增量 更新innodb的數據字典表 提交事務(刷事務的redo日志) 修改統計信息 rename臨時idb文件，frm文件 變更完成
1.3 online ddl 支持范圍
INSTANT DDL 是 MySQL 8.0 引入的新功能，當前支持的范圍較小,
修改二級索引類型
新增列
修改列默認值
修改列 ENUM 值
重命名表
非online DDL
新增全文索引
新增空間索引
刪除主鍵
修改列數據類型
指定表字符集
修改表字符集
操作
Instant
In Place
重建表
可并行DML
只修改元數據
新增輔助索引
否
是
否
是
否
刪除輔助索引
否
是
否
是
是
修改索引名
否
是
否
是
是
新增主鍵
否
是
是
是
否
刪除主鍵
否
否
是
否
否
刪除同時新增主鍵
否
是
是
是
否
新增字段
是(追加)
是
否
是
否
刪除字段
否
是
是
是
否
修改字段類型
否
否
是
否
否
擴展varchar長度
否
是
是
Yes
否
新增STORED虛擬列
否
否
YES
否
是
新增VIRTUAL虛擬列
是
是
否
是
是
轉換字符集
否
否
是
否
否
Optimize table
否
是
是
是
否
修改表名
是
是
否
是
是
注意 一般DDL操作最好都采用pt-osc或gh-ost這樣的工具來實施，并且實施之前務必要先檢查當前目標表上是否有事務或大查詢未結束，避免嚴重的MDL鎖等待 除了8.0以上版本，除了追加式新增列、表改名、新增虛擬列這三種支持INSTANT的操作可以直接跑DDL，其余的都統統采用pt-osc/gh-osc工具，相對更不容易出狀 執行ALTER TABLE DDL時，不要節外生枝指定ALGORITHM=?, LOCK=? 選項，因為MySQL會自行判斷該采用哪種方式。本來可以INPLACE的，可能不小心給指定成COPY就悲劇了
總表匯總
操作
版本
INSTANT
INPLACE
Rebuild
并發 DML
Only Modifies Metadata
二級索引
創建二級索引
MySQL 8.0
No
Yes
No
Yes
No
MySQL 5.7
Yes
No
Yes
No
MySQL 5.6
Yes
No
Yes
No
刪除索引
MySQL 8.0
No
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
Yes
No
Yes
Yes
重命名索引
MySQL 8.0
No
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
增加全文索引
MySQL 8.0
No
Yes*
No*
No
No
MySQL 5.7
Yes*
No*
No
No
MySQL 5.6
Yes*
No*
No
No
增加空間索引
MySQL 8.0
No
Yes
No
No
No
MySQL 5.7
Yes
No
No
No
MySQL 5.6
修改索引類型
MySQL 8.0
Yes
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
Yes
No
Yes
Yes
主鍵
操作
版本
INSTANT
INPLACE
Rebuild
并發 DML
Only Modifies Metadata
增加主鍵
MySQL 8.0
No
Yes*
Yes*
Yes
No
MySQL 5.7
Yes*
Yes*
Yes
No
MySQL 5.6
Yes*
Yes*
Yes
No
刪除主鍵
MySQL 8.0
No
No
Yes
No
No
MySQL 5.7
No
Yes
No
No
MySQL 5.6
No
Yes
No
No
重建主鍵
MySQL 8.0
No
Yes
Yes
Yes
No
MySQL 5.7
Yes
Yes
Yes
No
MySQL 5.6
Yes
Yes
Yes
No
列操作
操作
版本
INSTANT
INPLACE
Rebuild
并發 DML
Only Modifies Metadata
新增列
MySQL 8.0
Yes*
Yes
No*
Yes*
No
MySQL 5.7
Yes
Yes
Yes*
No
MySQL 5.6
Yes
Yes
Yes*
No
刪除列
MySQL 8.0
No
Yes
Yes
Yes
No
MySQL 5.7
Yes
Yes
Yes
No
MySQL 5.6
Yes
Yes
Yes
No
重命名列
MySQL 8.0
No
Yes
No
Yes*
Yes
MySQL 5.7
Yes
No
Yes*
Yes
MySQL 5.6
Yes
No
Yes*
Yes
調整列順序
MySQL 8.0
No
Yes
Yes
Yes
No
MySQL 5.7
Yes
Yes
Yes
No
MySQL 5.6
Yes
Yes
Yes
No
修改列默認值
MySQL 8.0
Yes
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
Yes
No
Yes
Yes
修改列數據類型
MySQL 8.0
No
No
Yes
No
No
MySQL 5.7
No
Yes
No
No
MySQL 5.6
No
Yes
No
No
擴展 VARCHAR 長度
MySQL 8.0
No
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
刪除列默認值
MySQL 8.0
Yes
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
Yes
No
Yes
Yes
修改自增值
MySQL 8.0
No
Yes
No
Yes
No*
MySQL 5.7
Yes
No
Yes
No*
MySQL 5.6
Yes
No
Yes
No*
修改列為空
MySQL 8.0
No
Yes
Yes*
Yes
No
MySQL 5.7
Yes
Yes*
Yes
No
MySQL 5.6
Yes
Yes*
Yes
No
修改列為非空
MySQL 8.0
No
Yes*
Yes*
Yes
No
MySQL 5.7
Yes*
Yes*
Yes
No
MySQL 5.6
Yes*
Yes*
Yes
No
修改列 ENUM 值
MySQL 8.0
Yes
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
Yes
No
Yes
Yes
表操作
操作
版本
INSTANT
INPLACE
Rebuild
并發 DML
Only Modifies Metadata
修改 ROW_FORMAT
MySQL 8.0
No
Yes
Yes
Yes
No
MySQL 5.7
Yes
Yes
Yes
No
MySQL 5.6
Yes
Yes
Yes
No
修改 KEY_BLOCK_SIZE
MySQL 8.0
No
Yes
Yes
Yes
No
MySQL 5.7
Yes
Yes
Yes
No
MySQL 5.6
Yes
Yes
Yes
No
指定字符集
MySQL 8.0
No
Yes
Yes*
No
No
MySQL 5.7
Yes
Yes*
No
No
MySQL 5.6
Yes
Yes*
No
No
修改字符集
MySQL 8.0
No
No
Yes*
No
No
MySQL 5.7
No
Yes*
No
No
MySQL 5.6
No
Yes
No
No
OPTIMIZE 表
MySQL 8.0
No
Yes*
Yes
Yes
No
MySQL 5.7
Yes*
Yes
Yes
No
MySQL 5.6
Yes*
Yes
Yes
No
重命名表
MySQL 8.0
Yes
Yes
No
Yes
Yes
MySQL 5.7
Yes
No
Yes
Yes
MySQL 5.6
Yes
No
Yes
Yes
MySQL
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。
相關文章