MySQLOnlineDDL方案剖析

蘭春發表於2018-11-23
MySql

一、常見的幾種方案

  • 1.1 MySQL源生的IN-PLACE ONLINE DDL

5.5，5.6 開始支援
5.7 支援的更好，有更多ddl操作支援online
8.0 支援快速加列功能
  • 1.2 第三方工具
1. pt-online-schema-change
2. gh-ost
  • 1.3 slave 先ddl，後切換主從

二、方案剖析

2.1 MySQL源生的IN-PLACE ONLINE DDL

  • 原理
原理比較複雜，不一一解讀。但是中間有幾個重要的過程：
1. 加一會排它鎖，開啟戰場，並釋放排它鎖
2. 記錄ddl期間產生的增量dml（大小由innodb_online_alter_log_max_size控制）
3. 應用這些增量dml
4. 再加一會排它鎖，清理戰場，釋放排它鎖

這裡關心的問題：
1. 如果再ddl期間，innodb_online_alter_log_max_size的大小被佔滿，會有怎樣的後果？
2. 如果DDL期間，被強行終止了，會有怎麼樣的後果？
  • 優點
1. 官方出品，原生態，品質有保障
  • 缺點
1. 有所等待風險
2. innodb_online_alter_log_max_size 是有限制的
3. 有可能造成主從延遲
4. 不是所有的ddl都是online的，對ddl型別有要求
  • 哪些DDL可以online (基於5.7的官方文件)

8.0 可以支援快速加列

型別 操作 是否需要copy資料，重新rebuild表 是否允許併發DML 是否只修改後設資料 備註
索引相關 建立、新增二級索引 NO YES NO –
索引相關 刪除索引 NO YES YES –
索引相關 重新命名索引 NO YES YES –
索引相關 新增FULLTEXT索引 NO* NO NO –
索引相關 新增SPATIAL索引 NO NO NO –
索引相關 改變索引型別（USING {BTREE or HASH}） NO YES YES –
主鍵相關 新增主鍵 YES* YES NO –
主鍵相關 刪除主鍵 YES NO NO –
主鍵相關 刪除主鍵並且又新增主鍵 YES YES NO –
列操作相關 新增列 YES YES* NO –
列操作相關 刪除列 YES YES NO –
列操作相關 重新命名列 NO YES* YES –
列操作相關 重新排列列（use FIRST or AFTER） YES YES NO –
列操作相關 設定列的預設值 NO YES YES –
列操作相關 修改列的資料型別 YES NO NO –
列操作相關 擴充套件varchar列的長度 NO YES YES 0~255 , 256 ~ 256+ 這兩個區間可以in-place
列操作相關 刪除列的預設值 NO YES YES –
列操作相關 修改auto-increcement的值 NO YES NO* –
列操作相關 使某列修改成NULL YES* YES NO –
列操作相關 使某列修改成NOT NULL YES* YES NO –
列操作相關 修改列定義為ENUM、SET NO YES YES –
表相關操作 optimizing table YES YES NO –
表相關操作 Rebuilding with the FORCE option YES YES NO –
表相關操作 Renaming a table NO YES YES –

三、第三方工具

3.0 第三方工具大致原理

  1. 先建立一個臨時表 old_table_tmp
  2. 給臨時表變更結構 alter old_table_tmp …
  3. 然後呢就是關鍵了： 將增量資料 和 原表的資料 都拷貝到 臨時表
  4. 當原表資料拷貝完畢後，對原表加鎖，進行切換
  5. 打掃戰場，結束

好了，這裡pt-online-shema-change 是通過觸發器的方式，來同步增量資料的 ， gh-ost 是通過模擬slave，監聽binlog並應用binlog來完成增量資料同步的，這裡是主要區別。

所以，不管哪種方式，這裡需要解決一個時序的問題(因為rowcopy和row_apply是並行的，不知道哪個先哪個後)，我們暫且認為 拷貝原表資料叫： rowcopy ， 拷貝增量資料並應用為 row_apply

由於rowcopy從時序上來說，都是老資料，所以它的優先順序是最低的，所以將rowcopy的動作轉換為inset ignore，意味著，row apply是可以覆蓋rowcopy資料的，這樣理解沒問題吧

好了，上面的問題解決了，其他的基本就不是問題了

3.1 pt-online-shema-change

  • 優點
1. percona 出品，必屬金品

2. 經過多年的生產環境驗證，質量可靠

3. 支援併發DML操作
  • 缺點
1. 原表不能有觸發器
3. 由於觸發器的原因，對master的效能消耗比較大
4. 處理外來鍵有一定的風險，需要特殊處理
5. 原表中至少要有主鍵或者唯一鍵
    檢查是否具有主鍵或者唯一索引，如果都沒有，這一步會報錯
    提示The new table `xx`.`_xx_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.

6. ddl不能有新增唯一索引的操作
    如果對錶增加唯一索引的話，會存在丟資料的風險。
    具體原因是因為pt-osc在copy已有的資料時會使用insert ignore將老表中的資料插入到新表中，因為新表已經增加了unique index，所以重複的資料會被ignore掉
    --check-unique-key-change 可以避免  , 預設yes
  • 原理
1. 建立一張新表
2. alter新表
3. 原表建立insert，update，delete三種觸發器
4. 原表開始拷貝資料到新表，且觸發器也開始對映到新表
5. 處理外來鍵（如果沒有忽略）
6. 重新命名新表和原表
7. 清理戰場


重要：

insert觸發器 =SQL轉換=> replace into

update觸發器
    =SQL轉換=> delete ignore + replace into (大於3.0.2版本)
    =SQL轉換=> replace into（低於3.0.2版本，所以這個版本會有問題，如果這時候對老的主鍵修改，那麼修改之前的值不會去掉，從而多了一些異常資料）
delete觸發器 =SQL轉換=> delete ignore

copy rows    =SQL轉換=> insert ignore into
  • 最佳實踐
1. innodb_autoinc_lock_mode 設定成 2 ， 否則會經常死鎖，autoinc鎖
2. 如果中途ddl失敗，需要先刪除觸發器，再刪除新的臨時表

3.2 gh-ost

  • 優點
1. 無觸發器設計
2. out-over方案設計
3. 對主機效能級別無影響
4. 可以暫停
  • 缺點
1. 原表不能有外來鍵
2. 原表不能有觸發器
3. 強制要求binlog為row格式
4. 原表不能有字母大小不同的同名表
5. 當併發寫入多的時候，在應用binlog階段由於是單執行緒，所以會非常慢,影響ddl效能和進度
  • 原理
原理基本都一樣，這裡主要的區別就是row apply這裡，pt-osc是觸發器，這裡是監聽master binlog並應用日誌，其餘的差別不大，這裡不再贅述

四、 slave 先ddl，後切換主從

如果其餘方式都不行，只能祭出大招slave先ddl，然後主從切換了

  • 優點
1. slave操作，不影響master
  • 缺點
1. 需要主從切換，主從切換越平滑，此方案就越好
2. 有幾點需要考慮和處理下：
    2.1 add column after|before ， 這樣的操作slave先做是否有影響
    2.2 slave先新增欄位，可能會導致主從同步停掉，需要設定某些引數

五、 ONLINE DDL 最佳方案選型

    1. 如果是建立索引、修改預設值這樣的，online ddl 快速且無影響的操作，儘量優先選擇online ddl
    1. 如果當前伺服器寫入量不高，負載不高，且原表沒有觸發器，沒有外來鍵，且此表有主鍵，儘量優先選擇pt-online-schema-change
    1. 其餘情況，選擇主從切換


相關文章