MySQL基礎篇--線上DDL歸納總結

工具線上DDL

pt-osc原理

pt-online-schema-change
pt-osc工作流程：
1、檢查更改表是否有主鍵或唯一索引，是否有觸發器
2、檢查修改表的表結構，建立一個臨時表，在新表上執行ALTER TABLE語句
3、在源表上建立三個觸發器分别對于INSERT UPDATE DELETE操作

觸發器來應用DDL執行期間對表所做的DML操作，每種DML操作均對應一個觸發器
delete變為delete ignore，update和insert均轉換為replace into。

4、從源表拷貝資料到臨時表，在拷貝過程中，對源表的更新操作會寫入到建立表中

在拷表select時需要進行目前讀（lock in shared mode）并與insert組成一個事務，
避免快照讀導緻增量的delete操作丢失

5、将臨時表和源表rename（需要中繼資料修改鎖，需要短時間鎖表）
6、删除源表和觸發器，完成表結構的修改。      

gh-ost原理

1、 gh-ost 首先連接配接到主庫上，根據 alter 語句建立幽靈表_tablename_gho；
2、 然後gh-ost作為一個備庫連接配接到主庫上，一邊在主庫上拷貝已有的資料到幽靈表，
一邊從主庫上拉取增量資料的 binlog，然後不斷的把 binlog 應用回主庫幽靈表； 
3、 等待全部資料同步完成，進行cut-over，即進行幽靈表和原表切換。cut-over是最後一步，
鎖住主庫的源表，等待binlog應用完畢，然後替換gh-ost幽靈表為源表。gh-ost在執行中，
會在原本的binlog event裡面增加hint和心跳包，用來控制整個流程的進度，檢測狀态等。      

原生線上DDL

mysql online ddl

自 MySQL 5.6 起，MySQL 原生支援 Online DDL，即在執行 DDL 期間允許執行DML(insert、update、delete)。了解 Online DDL 先了解一下之前 DDL 的 2 種算法 copy 和 inplace。

Copy方式

1、按照原表定義建立一個新的臨時表
2、對原表加寫鎖（禁止DML，允許select）
3、步驟1）建立的臨時表執行DDL
4、将原表中的資料copy到臨時表
5、釋放原表的寫鎖
6、将原表删除，并将臨時表重命名為原表
可見，采用 copy 方式期間需要鎖表，禁止 DML，是以是非 Online 的。
比如：删除主鍵、修改列類型、修改字元集，這些操作會導緻行記錄格式發生變化（無法通過全量+增量實作 Online）。      

Inplace方式

在原表上進行更改，不需要生成臨時表，不需要進行資料copy的過程。
根據是否行記錄格式，分為兩類：
rebuild：需要重建表（重新組織聚簇索引）。
          比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 屬性等；
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的資料檔案。
說明：
在 copy 資料到新表期間，在原表上是加的 MDL 讀鎖（允許 DML，禁止 DDL）
在應用增量期間對原表加 MDL 寫鎖（禁止 DML 和 DDL）
根據表A重建出來的資料是放在 tmp_file 裡的，這個臨時檔案是 InnoDB 在内部建立出來的，
整個 DDL 過程都在 InnoDB 内部完成。
對于 server 層來說，沒有把資料挪動到臨時表，是一個原地操作，這就是“inplace”名稱的來源。      

總結

（1）pt-osc、gh-ost、原生 Online DDL copy 方式（實際上是非 Online），都是需要 copy 原表資料到一個新表，這個是非常耗時的；