MySQL千萬級大表線上變更表結構

我接觸過很多公司的資料庫架構都缺乏有效合理的設計，如果早期的設計不合理，後期隨著表資料量的增加就不可避免的進行表結構變更。

分享一例MySQL千萬級大表線上變更表結構的案例。處理問題的思路和角度各有不同，希望這篇文章可以拋磚引玉。

 

一、問題背景

MySQL資料庫儲存的是結構化的資料型別，資料表記錄從上萬到上千萬上億，在公司業務不允許停服務的情況下，線上變更表結構是個很頭疼的事情。

當單表記錄數過大時，增刪改查效能都會急劇下降，線上變更表結構是不得不做的事，那麼如何在不停機不影響業務的情況下進行表結構變更？

 

二、難點問題

• 單例項情況下，單表量級達到千萬以上，如何確保修改欄位而不鎖表？

• 主從情況下，單表量級達到千萬以上，如何確保修改欄位而不鎖表？

• 如何對大表進行分庫分表儲存，來實現高效、穩定的資料讀寫？

 

三、問題分析思路

問題一：單表量級達到千萬以上，如何確保修改欄位而不鎖表？

單例項情況，建議使用 pt-online-schema-change 或者 gh-ost 工具，二者均不會產生鎖表，前者透過觸發器實現，後者透過解析 binlog 實現。

 

問題二：主從情況下，單表量級達到千萬以上，如何確保修改欄位而不鎖表？

主從情況，如果使用 pt-online-schema-change 工具，要在主庫做變更；如果使用 gh-ost 工具，主從均可。

 

問題 三：如何對大表進行分庫分表儲存，來實現高效、穩定的資料讀寫？

• 分割槽：表分割槽之後依靠引擎儲存去保證；

• 分表：業務邏輯涉及的更多一些，這種情況下單庫也相對好辦；

• 分庫：最大的問題就是分散式事務，目前市場有很多開源中介軟體可以選擇，但未必能夠滿足需求，需要進行選擇。

 

其實在資料庫架構初期就要判斷是否會出現大表以及資料的生命週期。對於海量資料，可以分為歷史資料和現行資料考慮儲存方式：

• 歷史資料：可以從現行資料表中進行定期剝離，後續只是進行查詢，完全可以將歷史資料轉存到歷史庫。

• 現行資料：對於頻繁訪問的資料，一般是整個系統的效能瓶頸點，可以考慮 SSD 的硬碟，這樣能保證既有業務的快速響應。

 

對於資料生命週期的管理還是需要考慮業務實際場景：

資料庫架構作為架構設計中的業務架構、應用架構、資料架構和執行架構中的架構之一，應該是與其他架構設計邏輯整合的一起的，不能把落地實現的所有功能都交給資料庫。

在實踐中比如不用儲存過程、不用外來鍵、儘量單表操作，這些應儘量交給應用層面去做，在應用層面進行邏輯處理；為什麼要放在應用層？，因為做這種處理的伺服器一般可以較好的進行擴充套件，提高響應能力，減少資料庫層面壓力。

 

四、線變更表結構方案有哪些？

• 直接 ALTER TABLE

• oak-online-alter-table

• 5.7 新增 online rename index

• pt-online-schema-change

• gh-ost

• 自定義指令碼

 

五、生產環境下，變更 Mysql 的表結構步驟是什麼？

1.確認表的後設資料資訊；

2.對需求裡的改表語句進行稽核，如果存在不規範的地方進行修正。

3.確認當前節點是主節點還是從節點。

4.確認主從狀態是否正常。

5.根據資料量、業務場景、業務容忍度，選擇變更的方案以及預估需要的時間。

6.確定操作時間點，如果資料量大，建議在低峰進行。

7.釋出變更通知，告知應用觀察對應業務。

8.變更過程中，留意 MySQL 監控和機器監控，觀察主從狀態、主從連線數、主從機器負載。

9.變更過程出現問題，及時 KILL 相關操作。

10.變更順利完成，進行資料校驗。