MySQL架構設計談：從開發規範、選型、拆分到減壓

天府雲創發表於2017-09-06
MySql架構
隨著MySQL自身的發展與不斷完善，不知不覺中整個網際網路行業已離不開這個完善又小巧的關係型資料庫，整個生態鏈也已經變得非常成熟，即便是初創企業和傳統企業也可以放心大膽地把資料庫遷移到MySQL上來。在大家和MySQL資料庫愉快玩耍的同時，我來聊聊MySQL架構設計相關的一些話題。

作者介紹
李輝，原新浪愛彩票運維負責人，常用網名：門牙沒了。曾主導新浪愛彩票的MySQL運維工作。培訓合夥人、資深講師，中國科學院大學在讀研究生（大資料方向），擅長大型專案的關係型資料庫運維和管理，現在在資料庫運維自動化方向研究。
 
本文大綱：
MySQL資料庫開發規範
MySQL高可用架構選型
MySQL Sharding拆分
利用NoSQL為MySQL減壓
 
一、MySQL資料庫開發規範
資料庫規範到底有多重要？有過初創公司經歷的朋友應該都深有體會。規範是資料庫運維的一個基石，能有效地減少資料庫出問題的概率，保障資料庫schema的合理設計並方便後續自動化的管理。
 
曾經我們花了大半年時間來做資料庫規範化的工作，例如制定資料庫開發指南、給程式設計師做培訓等，推進的時候也會遇到一些阻力。但規範之後運維質量會有一個質的提升，也增進了DBA的工作效率。
 
在開發規範方面，我們劃分為開發規範和運維規範兩部分。
 
1、開發規範
 
表設計的規範：
欄位數量建議不超過20-50個
做好資料評估，建議純INT不超過1500萬，含有CHAR的不要超過1000萬。欄位型別在滿足需求條件下越小越好，儘量使用UNSIGNED儲存非負整數，因為實際使用時候儲存負數的場景不多。
將字元轉換成數字儲存。例如使用UNSIGNED INT儲存IPv4 地址而不是用CHAR(15) ，但這種方式只能儲存IPv4，儲存不了IPv6。另外可以考慮將日期轉化為數字，如：from_unixtime()、unix_timestamp()。
所有欄位均定義為NOT NULL，除非你真的想儲存null。
 
索引設計的規範：
 
1）所有表必須有顯式主鍵
InnoDB表是以主鍵排序儲存的IOT表
儘量使用短、自增的列做索引
複製結構使用row格式，如果表有主鍵可以加速複製
UNSIGNED INT自增列，也可以考慮BIGINT
TINYINT做主鍵可能導致MySQL Crash
型別轉換會導致查詢效率很低
可用uuid_short()代替uuid()，轉成BIGINT儲存
 
2）合理地建立索引
選擇區分度高的列作為索引
單個索引欄位數不超過5，單表索引數量不超過5，避免冗餘索引
建立的索引能覆蓋80%主要的查詢，不求全，解決問題的主要矛盾
複合索引排序問題，多用explain去確認
 
SQL編寫規範：
 
1）避免在資料庫中進行大量計算任務
大事務拆成多個事務，分批多次操作
慎用text、blob大型欄位，如要用考慮好拆分方案
頻繁查詢的字典表考慮用Cache抗
 
2）優化join
避免大表與大表之間的join，考慮讓小表去驅動大表join
最多允許三表join，最好控制成兩表
控制join後面where選擇的行數
 
3）注重where條件，多用EXPLAIN確認
where條件的欄位，儘量用區別度高的欄位，這樣走索引的效能更好
出現子查詢的SQL，先確認MySQL版本，利用explain確認執行計劃
進行分頁優化;DML時候多個value合併
 
Schema Review：
 
1）字符集問題
表字符集選擇UTF8 ，如果需要儲存emoj表情，就改成UTF8mb4
 
2）Schema設計原則
核心表欄位數量儘可能地少，有大欄位要考慮拆分
適當考慮一些反正規化的表設計，增加冗餘欄位，減少JOIN
資金欄位考慮統一*100處理成整型，避免使用decimal浮點型別儲存
日誌型別的表可以考慮按建立時間水平切割，定期歸檔歷史資料
 
3）Schema設計目標
快速實現功能為主，保證節省資源
平衡業務技術各個方面，做好取捨
不要在DB裡進行大計算，減少複雜操作
 
整體來說，這部分規範還是很容易遵守的，實現起來也沒有什麼難度，就能取得很好的效果。
 
2、運維規範
 
（1）SQL稽核
SQL評審這部分工作相信讓很多的DBA同學都叫苦不迭，人肉稽核不僅效率低下，容易出錯，對DBA的自身發展也非常不利，難道我們來上班就是為了稽核SQL的嗎？在經過了一段痛苦的人肉稽核之後，我們接入了去哪兒網開源的Inception，並根據自身的業務特點做了一些調整。當然現在開源的SQL評審軟體已經很多了，大家可以自由選擇，也可以自行開發。
 
在稽核與執行上線DDL語句的時候，要注意MySQL官方原生Online DDL和Percona公司的pt-osc之間的一些差異，例如pt-osc在執行時每次都要copy全表，相對來說比較慢，好處是不鎖表，並且有完善的條件檢測和延時負載策略控制。官方Online DDL雖然官方也一直在改進，但生產環境使用還不是很完美，尤其要注意執行過程中容易導致MDL鎖。官方Online DDL也有優於pt-osc的地方，比如增刪索引，重新命名列等，如下圖所示。
 
 
（2）許可權控制
MySQL從5.6開始，逐步完善了許可權系統，比如MySQL5.6可以安裝檢查密碼強度的外掛，5.7開始增加了密碼過期機制、賬戶鎖定等功能，對SSL這一塊也做了一些優化，8.0版本增加了角色的功能，許可權系統已經逐步在向Oracle資料庫靠攏了。在日常運維中，也可以使用pt-show-grants工具提高許可權審查的力度。應用程式賬號應只賦予SELECT、INSERT、UPDATE許可權，DELETE的邏輯改用UPDATE實現，並啟用sql_safe_updates選項。
 
另一個有效控制許可權的方法就是SQL堡壘機，早期我們通過改造MyWebSQL實現，在Web版客戶端的基礎上加入了一些資源控制策略、審計、語法校驗等功能。後續又使用Python開發了功能更完備的SQL堡壘機，同時支援MySQL、Oracle、Greenplum等資料庫。
 
 
 
SQL堡壘機不僅可控制公司內部人員的資料庫許可權，追溯各類人員對資料庫的操作，也能避免大查詢或全表更新的情況發生，支援審計需求，整體運維質量提升了一個臺階。
 
（3）MySQL版本選擇
MySQL社群版，使用者群體最大
MySQL企業版，收費
Percona Server版，新特性多，和MySQL社群版最接近
MariaDB版，國內使用者暫時不多
選擇優先順序：MySQL社群版> Percona Server > MariaDB > MySQL 企業版
 
對於版本選擇這件事，建議大家還是跟進官方社群版比較好，目前比較穩定的版本是MySQL5.6，推薦大家使用。有特殊需求的話再選擇MySQL5.7、PXC、TiDB、TokuDB等資料庫。
 
二、MySQL高可用架構選型
MySQL高可用方面，目前業界主流依然是基於非同步複製的技術，例如Keepalived、MHA、ZooKeeper等，要求資料強一致的場景逐步開始使用分散式協議，這方面的典型代表有PXC、Group Replication、TiDB。下面我們就重點來說說keepalived、MHA和PXC這幾種大家用得比較多的架構。
 
1、keepalived高可用架構
 
業內使用非常普遍，它部署容易、方便維護，還節省伺服器資源。這種架構的一個好處就是在發生切換後，原Master只需重新拉起來即可恢復高可用，不需要過多幹預。擴充套件起來也方便，可以任意掛載只讀庫和災備庫。但它存在的問題也很明顯，比如Keepalived的檢測機制不完善、有腦裂隱患、資料一致性較弱等等。
 
還需要注意主從拓撲的設計。如下圖，只讀庫掛到哪個Master比較合適？顯然是M2，其它兩種拓撲在發生切換後都會影響到只讀庫的訪問。
 
2、MHA
 
MHA自誕生以來，就得到了業內的廣泛關注，並迅速流行開來。與keepalived相比，MHA最大的優點就是在發生故障切換之後，能自動補齊binlog，最大程度保證資料一致性。從伺服器能自動切換，無需人工干預，能非常好的工作在讀寫分離的環境下。基於Perl語言的指令碼也非常方便進行二次開發。MHA非常適合讀寫壓力比較大的應用。
 
但由於MHA在工作時需要配置SSH互信，因此選擇這種架構時內網安全一定要做到位。另外也可以搭配Binlog Server使用。
 
3、PXC
 
 
PXC全稱是Percona XtraDB Cluster，是Percona公司基於Galera協議開發的一個產品。PXC犧牲了CAP裡面的P（Partition Tolerance），保留了C(Consistency )和A(Availability )。這種結構非常適合電商、金融類業務，自PXC和Group Replication出現以後，MySQL徹底掃清了進入金融行業的障礙。
 
PXC的優勢：
同步複製，解決了傳統架構複製延遲和腦裂的問題
資料強一致
多主複製，每個節點都可以讀寫資料
並行複製，多個事務可以並行推送到其他節點
高可用，單點故障不影響叢集可用性
新節點自動部署
與傳統MySQL幾乎完全相容
 
使用PXC要注意的問題：
不要有大事務
木桶效應，叢集效能取決於效能最差的那個節點
併發效率有損失
網路要求較高，建議萬兆網路
多點併發寫時鎖衝突、死鎖問題多
寫無法擴充套件，無法解決熱點更新問題
 
除此之外，還有一類採用DNS/ZooKeeper的高可用架構，這種架構通常都需要自行開發，無通用的方案，比較適合大規模叢集的高可用，這裡我們不過多贅述。
 
下面簡單回顧一下上述幾種高可用架構：
雙Master架構：非常成熟，使用很普遍，要注意延遲和資料的一致性。
PXC: 分散式協議，資料強一致性，併發效率略低，可用性好
MHA：各項指標介於M-M和PXC之間，效能無損失，適合讀寫分離架構。
 
總而言之，沒有最完美的架構，只有最適合的架構。選擇適合自己業務的即可。
 
三、MySQL sharding拆分
接下來是第三個議題，MySQL拆分原則和分庫分表設計。
 
首先先提一個問題，為什麼要拆，不拆不行嗎？按照我們的經驗來看，當資料和業務到了一定的規模，都不可避免的要面臨分庫分表的問題。這就好像汽車的發動機一樣，要達到更高的效能，4缸6缸明顯是不夠用的，V8、V12才是王道。
 
拆分能解決如下幾個問題：
單庫併發較大
單庫物理檔案太大
單表過大，DDL無法接受
防止出現效能瓶頸，提升效能
防止出現抖動不穩定現象
 
確定要進行資料庫的拆分了，應該怎麼拆呢？
 
垂直拆分
優點：
拆分簡單明瞭，拆分規則明確
應用程式模組清晰，整合容易
資料維護方便易行，容易定位
 
缺點：
表關聯需要改到程式中完成
事務處理變的複雜
熱點表還有可能存在效能瓶頸
過度拆分會造成管理複雜
 
水平拆分
優點：
不會影響表關聯、事務操作
超大規模的表和高負載的表可以打散
應用程式端改動比較小
拆分能提升效能，也比較易擴充套件
 
缺點：
資料分散，影響聚集函式的使用
切分規則複雜，維護難度增加
後期遷移較複雜
 
要先分庫還是先分表？
分庫的優點：實現簡單，庫與庫之間界限分明，便於維護，缺點是不利於頻繁跨庫操作，單表資料量大的問題解決不了。
分表的優點：能解決分庫的不足點，但是缺點恰恰是分庫的優點，分表實現起來比較複雜，特別是分表規則的劃分，程式的編寫，以及後期的資料庫拆分移植維護。
 
一巴掌拍板直接選分庫或分表都是不可取的，主要是看需要達到什麼樣的擴充套件方式，才能決定先分庫還是先分表，根據具體的場景決定。分庫分表的最終目的還是為了擴充套件，而且要看拆分的規劃設計是針對哪一層。
 
上述問題都解決了，該考慮如何實現了，到底是在應用程式中實現，還是使用中介軟體？個人建議如果是小規模的拆分，直接在程式邏輯中實現即可，大規模的拆分再考慮使用各種中介軟體。
 
目前業內已經開源了很多的MySQL中介軟體產品，例如Atlas、DBProxy、Mycat、OneProxy、DRDS、Vitess等等，每個中介軟體都有自己的特點，個別不太成熟的可能會存在一些Bug，選用之前要做好相關的調研與測試工作，上線使用一定要保證自己能hold住。如果要完全貼合自身業務，並且掌控得較好的還是要自行開發。
 
下面說說我們的拆分經驗。
首先我們先在壓力比較大的資料庫上做垂直拆分，剝離出活動、後臺統計等業務。這一步也是最容易實現的。
 
接下來，如果是訊息類的資料，就基於時間維度進行拆分，單表控制在5-10G，行數控制到500-1000w這個樣子。這個時候我們發現資料庫的效能是比較好的，而且比較好維護。如果是使用者類的資料，就按照Hash或Range進行拆分。這種情況下用這種方法拆分會拆的比較均勻一些。
 
併發仍然比較高怎麼辦？可以在時間維度拆分的基礎上再按Range或Hash進行拆分。
 
最後要注意的就是不要過度的拆分，會造成複雜度的上升。Schema設計合理的情況下，10億的資料量也能跑的好好的。個別不關鍵的應用，例如日誌、監控資料等，使用分割槽表、TokuDB也能抗。拆分對應用層總是有損的。
 
要做個“懶”DBA。
 
四、利用NoSQL為MySQL減壓
最後一個議題，我們聊一聊NoSQL。NoSQL現在遍地開花，應用也很廣泛了，業內用的比較多的主要集中在Redis、MongoDB、Cassandra等NoSQL資料庫上。今天我們主要來說說和MySQL關聯最為密切的Redis。
 
為什麼要使用Redis？
資料儲存在記憶體中，訪問速度快
能支援大批量操作及爆發性負載
資料結構豐富，有效緩解MySQL壓力
協議簡單，支援各種語言的API
儲存大量資料無需擔心效能
 
Redis主要作用還是抗讀的壓力。讀操作先到Redis，Redis中取不到再從MySQL資料庫訪問，從MySQL讀取到資料後，還要回寫到Redis。
 
使用Redis要注意的幾點：
效能方面，由於Redis完全是基於記憶體的訪問，效能無需擔心。
 
在使用Redis時，要注意Cache 和Storage不要混合使用。不要依賴Redis的持久化，持久化這一塊Redis要努力的還很多。另外如果你把Redis拿來做Storage的話，一旦Redis的記憶體跑滿，那就慘了，所有的Redis連線都會卡著不響應。如果只是把Redis來做cache的話，那問題就不大。
 
還有諸如快取穿透、快取雪崩、熱點key重建時快取失效這些問題也是重點關注的物件。
 
如何利用Redis給MySQL加速：
1）利用K/V結構，快取結果，例如儲存使用者資訊、全域性排行、統計資訊等。
2）利用其豐富的資料結構為MySQL減壓，例如計數器、排序、Hash（把表對映到Redis中）、訊息佇列等。
 
總結

    系統架構設計是一個長期總結與進化的過程，講究均衡與取捨。在進行大規模MySQL架構設計的過程中，除了要汲取別人的經驗之外，還要關注各種架構背後的業務場景與架構思想，與自己的實際業務場景相結合，才能設計出一個好的系統架構來

相關文章