MySQL 如何 ALTER 含有大量資料的 TABLE ，而不讓整個 DB 被卡住

前陣子公司另一個team的同事準備要離職，因為公司人力配置的關係所以最後是我要去交接QQ，在進行交接的時候有提到之前他們team遇到的一個事件，就是 Alter table 讓整個 DB 卡住，影響到整個線上服務，覺得這是個滿容易被忽略的點，又會造成很大的影響，因此記錄在這邊。

之所以會造成這樣的事情，主要的原因在於 Alter table 的底層操作步驟如下：
1. 建立一張新的空表，裡面的欄位是修改過後欄位
2. 將舊表的資料一筆筆寫入新的表中
3. 將兩張表交換，並刪除舊的表
而在進行這項操作時，整張表是被 lock 住的，可以讀但不能寫。

如上所述，由於 alter table 會將每筆資料重新寫進修改欄位後的表，而我們要變更的那張表資料量很大（約17G），因此會花費很多時間。

除此之外，工程師在操作時看到畫面一直在轉，以為是壞掉了所以就按下了取消，又導致 alter table 要 rollback ，再加上操作時正好是流量尖峰的時段，不斷有request進來，最後就讓整個 DB hang在那邊了。

那如果要 alter table 有大量資料的 table 應該要怎麼辦呢？

首先第一點也是最重要的一點：不要在流量尖峰時間做這種事！

且不僅限於 alter table 這種動到 DB 的操作，就連一般的推版，可以的話也最好避開掉流量尖峰時間，萬一真的發生問題的話才可以讓衝擊降到最低。

再來，如果去搜尋要如何 alter 大量資料的 table 時，可能會看到有人建議下面這種操作：

CREATE TABLE tablename_tmp LIKE tablename;
ALTER TABLE tablename_tmp ADD fieldname fieldtype;
INSERT INTO tablename_tmp SELECT * FROM tablename;
ALTER TABLE tablename RENAME tablename_old;
ALTER TABLE tablename_tmp RENAME tablename;

但首先，這個步驟本來就是 alter table 內建在做的事情了，除此之外，INSERT INTO tablename_tmp SELECT * FROM tablename;這個步驟，在 InnoDB 底下會是一個 transaction 操作，會導致這種操作甚至可能比原本的 Alter table還要效能更差，再加上在操作的同時不能夠讓其他人新增或更新原本 table 內資料，因此並不是個好的做法。

另一種比較可行的方式是採用 Active-Passive Master-Master replication，先讓主要的 DB 繼續服務，在隨從的 DB 上進行 alter table 的操作，等到操作完成後將主從的角色交換，原本的隨從切換成主要服務的 DB，再於另一台進行 alter table。但這樣的做法會需要開另一台機器跟 DB 做主從式架構，需要對 DB HA 有比較進階的了解跟操作知識才行。

那怎麼辦呢？聽起來都很麻煩，table 資料量大錯了嗎？QQ

所幸這種麻煩的事情，大部分都已經有人想到更好的做法了，遇到這種要 alter 大筆資料的情境，最好還是使用一些工具去處理，例如pt-online-schema-change 或是 gh-ost 。

其實這類工具在實作上和原本的 alter table 步驟差異不大，都是會先建立個 shadow table 後再把資料倒過去，差別在於會針對資料處理的細節進行優化，可以讓我們在實作中少掉很多要擔心的事情。

以 gh-ost為例，會先建立新的空表，再慢慢批次把資料新增到新的表中，同時也會紀錄過程中的變更，最後再將新舊 table 交換，完成整個 alter 操作。但 gh-ost 跟其他工具不同的地方在於，一般工具都是用 MySQL 的 trigger 去紀錄過程中的資料變更，但根據 gh-ost 表示 trigger 有其缺點在，因此 gh-ost 是採用 triggerless 的架構，而是用 binary log stream 去紀錄資料變更的。

來總結一下，在 alter 有大量資料的 table 時，首先最重要的就是不要在流量尖峰時間做這種事，再來是 alter 有兩種方式可以選擇，一種是用 DB HA 架構去處理，另一種是用 gh-ost 等工具去處理。

不過導入任何技術都會有其成本在，我會建議先把資料表複製一份下來，找個空的機器直接去 alter 它看看要花多少時間，如果是在離峰時間甚至是系統 down time 許可就能完成的情況下，那就直接 alter 就好了，會是成本最低的做法。

最後一個小小的補充，如果發現 DB hang住，可以用下面的指令：

SHOW PROCESSLIST;

去看看是哪個 process 卡住，找到後再用KILL把它終止掉（當然要先搞清楚自己在殺什麼東西就是了XD）

參考資料：
https://stackoverflow.com/questions/11450089/changing-large-mysql-innodb-tables
https://raima.com/rdme-high-availability-database/
https://stackoverflow.com/questions/7599519/alter-table-add-column-takes-a-long-time
https://github.com/github/gh-ost
https://stackoverflow.com/questions/35293625/mysql-alter-table-hangs
https://stackoverflow.com/questions/41051284/mysql-hangs-on-alter-table