[MySQL] 從業務層面對MySQL高可用方案進行分解

         相對於傳統行業的相對服務時間9x9x6或者9x12x5，因為網際網路電子商務以及網際網路遊戲的實時性，所以服務要求7*24小時，業務架構不管是應用還是資料庫，都需要容災互備，在mysql的體系中，最好通過在最開始階段的資料庫架構階段來實現容災系統。所以這裡從業務巨集觀角度闡述下mysql架構的方方面面。

一，MySQL架構設計—業務分析

（1）讀多寫少

虛線表示跨機房部署，比如電子商務系統，一個Master既有讀也有些寫，對讀資料一致性需要比較重要的，讀要放在Master上面。

M(R)僅僅是一個備庫，只有M(WR)掛了之後，才會切換到M(R)上，這個時候M(R)就變成了讀寫庫。比如遊戲系統，有很多Salve會掛載後面一個M(R)上面。

 

（2）讀多寫少MMS-電商

如果是電子商務型別的，這種讀多寫少的，一般是1個master拖上4到6個slave，所有slave掛載在一個master也足夠了。

切換的時候，把M1的讀寫業務切換到M2上面，然後把所有M1上的slave掛到M2上面去，如下所示：

 

（3）讀多寫少MMSS-遊戲

如果是遊戲行業的話，讀非常多蠻明顯的，會出現一般1個Master都會掛上10個以上的Slave的情況，所以這個時候，可以把一部分Slave掛載新的M(R)上面。至少會減少一些壓力，這樣至少伺服器掛掉的時候，不會對所有的slave有影響，還有一部分在M(R)上的slave在繼續，不會對所有的slave受到影響，見圖3，

圖3

 

（4）讀少寫多

         意味著讀並不會影響寫的效率，所以讀寫都可以放在一個M1(WR)，而另外一個不提供讀也不提供寫，只提供standby冗餘異地容災。

    這個異地容災是非常重要的，否則如果是單機的，單邊的業務，萬一idc機房故障了，一般就會影響線上業務的，這種 造成業務2小時無法應用，對於線上電子商務交易來說，影響是蠻大的，所以為了最大限度的保證7*24小時，必須要做到異地容災，MM要跨idc機房。雖然對資源有一些要求，但是對HA來說是不可缺少的，一定要有這個MM機制。

         做切換的時候，把所有的讀寫從M1直接切換到M2上就可以了。

 

（5）讀寫平分秋色

讀和寫差不多，但是讀不能影響寫的能力，把讀寫放在M1(WR)上，然後把一部分讀也放在M2(R)上面，當然M1和M2也是跨機房部署的。

         切換的時候，把一部分讀和全部寫從M1切換到M2上就可以了。

 

 

 

二：MySQL架構設計—常見架構

（1）強一致性

 對讀一致性的權衡，如果是對讀寫實時性要求非常高的話，就將讀寫都放在M1上面，M2只是作為standby，就是採取和上面的一（4）的讀少寫多的一樣的架構模式。

         比如，訂單處理流程，那麼對讀需要強一致性，實時寫實時讀，類似這種涉及交易的或者動態實時報表統計的都要採用這種架構模式

 

（2）弱一致性

如果是弱一致性的話，可以通過在M2上面分擔一些讀壓力和流量，比如一些報表的讀取以及靜態配置資料的讀取模組都可以放到M2上面。比如月統計報表，比如首頁推薦商品業務實時性要求不是很高，完全可以採用這種弱一致性的設計架構模式。

 

（3）中間一致性

如果既不是很強的一致性又不是很弱的一致性，那麼我們就採取中間的策略，就是在同機房再部署一個S1(R)，作為備庫，提供讀取服務，減少M1(WR)的壓力，而另外一個idc機房的M2只做standby容災方式的用途。

當然這裡會用到3臺資料庫伺服器，也許會增加採購壓力，但是我們可以提供更好的對外資料服務的能力和途徑，實際中儘可能兩者兼顧。

 

（4）統計業務

比如PV、UV操作、頁數的統計、流量的統計、資料的彙總等等，都可以劃歸為統計型別的業務。

資料庫上做大查詢的統計是非常消耗資源的。統計分為實時的統計和非實時的統計，由於mysql主從是邏輯sql的模式，所以不能達到100%的實時，如果是online要嚴格的非常實時的統計比如像火車票以及金融異地結算等的統計，mysql這塊不是它的強項，就只有查詢M1主庫來實現了。

 

A，但是對於不是嚴格的實時性的統計，mysql有個很好的機制是binlog，我們可以通過binlog進行解析Parser，解析出來寫入統計表進行統計或者發訊息給應用端程式來進行統計。這種是準實時的統計操作，有一定的短暫的可接受的統計延遲現象，如果要100%實時性統計只有查詢M1主庫了。

通過binlog的方式實現統計，在網際網路行業，尤其是電商和遊戲這塊，差不多可以解決90%以上的統計業務。有時候如果使用者或者客戶提出要實時read-time了，大家可以溝通一下為什麼需要實時，瞭解具體的業務場景，有些可能真的不需要實時統計，需要有所權衡，需要跟使用者和客戶多次有效溝通，做出比較適合業務的統計架構模型。

B，還有一種offline統計業務，比如月份報表年報表統計等，這種完全可以把資料放到資料倉儲裡面或者第三方Nosql裡面進行統計。

 

 

 

（5）歷史資料遷移

歷史資料遷移，需要儘量不影響現線上上的業務，儘量不影響現線上上的查詢寫入操作，為什麼要做歷史資料遷移？因為有些業務的資料是有時效性的，比如電商中的已經完成的歷史訂單等，不會再有更新操作了，只有很簡單的查詢操作，而且查詢也不會很頻繁，甚至可能一天都不會查詢一次。

         如果這時候歷史資料還在online庫裡面或者online表裡面，那麼就會影響online的效能，所以對於這種，可以把資料遷移到新的歷史資料庫上，這個歷史資料庫可以是mysql也可以是nosql，也可以是資料倉儲甚至hbase大資料等。

         實現途徑是通過slave庫查詢出所有的資料，然後根據業務規則比如時間、某一個緯度等過濾篩選出資料，放入歷史資料庫(History Databases)裡面。遷移完了，再回到主庫M1上，刪除掉這些歷史資料。這樣在業務層面，查詢就要兼顧現在實時資料和歷史資料，可以在filter上面根據遷移規則把online查詢和history查詢對接起來。比如說一個月之內的在online庫查詢一個月之前的在history庫查詢，可以把這個規則放在DB的遷移filter層和應用查詢業務模組層。如果可以的話，還可以配置更細化，通過應用查詢業務模組層來影響DB的遷移filter層，比如以前查詢分為一個月為基準，現在查詢業務變化了，以15天為基準，那麼應用查詢業務模組層變化會自動讓DB的filter層也變化，實現半個自動化，更加智慧一些。

 

（6）MySQL Sharding

像oracle這種基於rac基於共享儲存的方式，不需要sharding只需要擴從rac儲存就能實現了。但是這種代價相對會比較高一些，共享儲存一般都比較貴，隨著業務的擴充套件資料的爆炸式增長，你會不停累計你的成本，甚至達到一個天文數字。

目前這種share disk的方式，除了oracle的業務邏輯層做的非常完善之外其他的解決方案都還不是很完美。

Mysql的sharding也有其侷限性，sharding之後的資料查詢訪問以及統計都會有很大的問題，mysql的sharding是解決share nothing的儲存的一種分散式的方法，大體上分為垂直拆分和水平拆分。

 

（6.1）垂直拆分

可以橫向拆分，可以縱向拆分，可以橫向縱向拆分，還可以按照業務拆分。

 

6.1.1橫向拆分

Mysql庫裡面的橫向拆分是指，每一個資料庫例項裡面都有很多個db庫，每一個db庫裡面都有A表B表，比如db1庫有A表B表，db2庫裡也有A表和B表，那麼我們把db1、db2庫的A表B表拆分出來，把一個庫分成2個，就拆分成db1、db2、db3、db4，其中db1庫和db2庫放A表資料，db3庫和db4庫放B表的資料，db1、db2庫裡面只有A表資料，db3、db4庫裡面只有B表的資料。

打個比方，作為電商來說，每個庫裡面都有日誌表和訂單表，假如A表是日誌表log表，B表是訂單表Order表，一般說來寫日誌和寫訂單沒有強關聯性，我們可以講A表日誌表和B表訂單表拆分出來。那麼這個時候就做了一次橫向的拆分工作，將A表日誌表和B表訂單表拆分開來放在不同的庫，當然A表和B表所在的資料庫名也可以保持一致(PS：在不同的例項裡面)，如下圖所示：

 

PS：這種拆分主要針對於不同的業務對錶的影響不大，表之間的業務關聯很弱或者基本上沒有業務關聯。拆分的好處是不相關的資料表拆分到不同的例項裡面，對資料庫的容量擴充套件和效能提高的均衡來說，都是蠻有好處的。

 

6.1.2縱向拆分

         把同一個例項上的不同的db庫拆分出來，放入單獨的不同例項中。這種拆分的適應場景和要求是db1和db2是沒有多少業務聯絡的，類似6.1.2裡面的A表和B表那樣。如果你用到了跨庫業務同時使用db1和db2的話，個人建議要重新考慮下業務，重新梳理下儘量把一個模組的表放在一個庫裡面，不要垮庫操作。

         這種庫縱向拆分裡面，單獨的庫db1，表A和表B是強關聯的。如下圖所示：

 

PS：看到很多使用mysql的人，總是把很多沒有業務關聯性的表放在一個庫裡面，或者總是把很多個的db庫放在同一個例項裡面，就像使用oracle那樣就一個instance的概念而已。Mysql的使用一大原則就是簡單，儘量單一，簡單的去使用mysql，庫要嚴格的分開；表沒有關係的，要嚴格拆分成庫。這樣的話擴充套件我們的業務就非常方便簡單了，只需要把業務模組所在的db拆分出來，放入新的資料庫伺服器上即可。

 

6.1.3 橫向縱向拆分

有些剛起步的，開始為了快速出產品，就把所以的庫所有的表都放在一個例項上，等業務發展後，就面臨著資料拆分，這裡就會把橫向縱向拆分結合起來，一起實現，如下圖所示：

 

6.1.4 業務拆分

         跟水平拆分有點類似，但是有不同的地方。比如一個供應商，可能整個網站上有10個供應商，一個網站上面每一個供應商都有一定的量，而且供應商之間的資料量規模都差不多的規模，那麼這個時候就可以使用供應商的緯度來做拆分。

         比如usern庫中，a、b、c表都是強關聯的，都有完整的業務邏輯存在，這裡只有使用者（供應商）緯度是沒有關聯的，那這個時候就可以把資料以使用者的緯度來進行拆分。

         就是使用者1和使用者2各自都有一套完整的業務邏輯，而且彼此之間不關聯，所以就可以把使用者1和使用者2資料拆分到不同的資料庫例項上面。目前很多網際網路公司或者遊戲公司有很多業務都是以使用者緯度進行拆分的，比如qunaer、sohu game、sina等。

（6.2） 水平拆分

         水平拆分相對要簡單一些，但是難度偏大，會導致分散式的情況、跨資料的情況、跨事務的情況可以分為大概三類，1是歷史資料和實時資料拆分，2是單庫多表拆分，3是多庫多表拆分。

6.2.1 實時資料歷史資料的拆分

和歷史資料遷移是一樣的邏輯，就是要將online庫的資料遷移到listory的資料庫裡面，對於實時的讀寫來說，資料是放在online db庫裡面，對於時間較遠的資料來說，是放在歷史History DB記錄庫裡面的，這裡的歷史庫可以是mysql也可以是別的nosql庫等。

        

 

6.2.2 單庫多表拆分

         主要不是解決容量問題，而是解決效能問題而擴充套件的，加入當前例項只有一個DB，有一個大表，一個大表就把整個例項佔滿了，這個時候就不能拆分db了，因為只有一個單表，這個時候我們就只能拆表了，拆表的方式主要是解決效能問題，因為單個表越大，對於mysql來說遍歷表的樹形結構遍歷資料會消耗更多的資源，有時候一個簡單的查詢就可能會引起整個db的很多葉子節點都要變動。表的insert、update、delete操作都會引起幾乎所有節點的變更，此時操作量會非常大，操作的時候讀寫效能都會很低，這個時候我們就可以考慮把大表拆分成多個小表，工作經歷中是按照hash取模打散成16個小表，也有按照id主鍵/50取模打散到50個小表當中，下圖例項是打散成2個小表。

 

6.2.3 多庫多表拆分

         在單庫多表的基礎上，如果單庫空間資源已經不足以提供業務支撐的話，可以考慮多庫多表的方式來做，解決了空間問題和效能問題，不過會有一個問題就是跨庫查詢操作，查詢就會有另外的策略，比如說加一個logic db層來實現跨庫跨例項自動查詢，簡單如下圖所示：

 

6.2.4水平拆分小結

水平拆分原則：

-- a. 儘量均勻的拆分維度。

-- b. 儘量避免跨庫事務。

-- c. 儘量避免跨庫查詢。

設計：

--a根據拆分維度，做mod進行資料表拆分，大部分都是取模的拆分機制，比如hash的16模原則等。

--b根據資料容量，劃分資料庫拆分

資料操作

--a跨事務操作：分散式事務，通過預寫日誌的方式來間接地實現。

--b跨庫查詢：資料彙總or訊息服務

6.2.5 案例說明

u  案例：

–      按照使用者維度進行拆分成64個分庫，1024個分表

?       user_id%1024 拆分到1024張分表中

?       每個分庫中存放1024/64張分表

?       取模的時候，可以用id的最後4位資料或者3位數字來取模就可以了。

 

u  操作1：採用Configure DB

–      拆分之後的查詢操作，做一個Configure DB，這個DB存放的是所有例項的庫表的對映關係，當我APP發來有一個請求查詢user1的資料，那麼這個user1的資料是存放在上千個例項中的哪一個庫表呢？這個關聯資訊就在Configure DB裡面，APP先去Configure DB裡面取得user1的關聯絡資訊(比如是存放在d_01庫上的t_0016表裡面)，然後APP根據關聯資訊直接去查詢對應的d_01例項的t_0016表裡面取得資料。

 

u  操作2：採用Proxy

–      拆分之後的查詢操作，做一個Proxy，APP訪問Proxy，Proxy根據訪問規則就可以直接路由到具體的db例項，生成新的sql去操作對應的db例項，然後通過Proxy協議進行操作把操作結果返回給APP。

–      優勢是Proxy和db例項是在一個網段，這樣Proxy與db例項的操作的時間是非常短的。

u  操作3：採用Data Engine

–      拆分之後的查詢操作，有一個Data Engine Service，這個DES裡面配置了所有資料庫例項的對映關係，需要在APP應用端安裝一個Agent，是同步邏輯，在JDBC層實現，DES可以實現讀寫分離，原理可以參考TDDL的實現。

6.3 叢集管理

縱向擴容：一個例項拆分成多個例項，縱向拆分比較簡單，修改的東西比較少，拆分的時候要通知到Configure DB或者DES，以免拆分之後查詢不到資料或者資料錄入不到新的db上面，如下圖所示：

 

橫向擴容：比較複雜，在縱向擴容成2個庫的基礎之上，再一次對庫的表進行擴容，所以需要及時通知Configure DB和DES更細庫和表的路由連線資訊。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26230597/viewspace-1289714/，如需轉載，請註明出處，否則將追究法律責任。