隨著MySQL自身的發(fā)展與不斷完善，不知不覺中整個互聯(lián)網(wǎng)行業(yè)已離不開這個完善又小巧的關(guān)系型數(shù)據(jù)庫，整個生態(tài)鏈也已經(jīng)變得非常成熟，即便是初創(chuàng)企業(yè)和傳統(tǒng)企業(yè)也可以放心大膽地把數(shù)據(jù)庫遷移到MySQL上來。在大家和MySQL數(shù)據(jù)庫愉快玩耍的同時，我來聊聊MySQL架構(gòu)設(shè)計相關(guān)的一些話題。

本文大綱：

• MySQL數(shù)據(jù)庫開發(fā)規(guī)范
• MySQL高可用架構(gòu)選型
• MySQL Sharding拆分
• 利用NoSQL為MySQL減壓

一、MySQL數(shù)據(jù)庫開發(fā)規(guī)范

數(shù)據(jù)庫規(guī)范到底有多重要?有過初創(chuàng)公司經(jīng)歷的朋友應(yīng)該都深有體會。規(guī)范是數(shù)據(jù)庫運維的一個基石，能有效地減少數(shù)據(jù)庫出問題的概率，保障數(shù)據(jù)庫schema的合理設(shè)計并方便后續(xù)自動化的管理。

曾經(jīng)我們花了大半年時間來做數(shù)據(jù)庫規(guī)范化的工作，例如制定數(shù)據(jù)庫開發(fā)指南、給程序員做培訓(xùn)等，推進的時候也會遇到一些阻力。但規(guī)范之后運維質(zhì)量會有一個質(zhì)的提升，也增進了DBA的工作效率。

在開發(fā)規(guī)范方面，我們劃分為開發(fā)規(guī)范和運維規(guī)范兩部分。

1、開發(fā)規(guī)范

表設(shè)計的規(guī)范：

• 字段數(shù)量建議不超過20-50個
• 做好數(shù)據(jù)評估，建議純INT不超過1500萬，含有CHAR的不要超過1000萬。字段類型在滿足需求條件下越小越好，盡量使用UNSIGNED存儲非負(fù)整數(shù)，因為實際使用時候存儲負(fù)數(shù)的場景不多。
• 將字符轉(zhuǎn)換成數(shù)字存儲。例如使用UNSIGNED INT存儲IPv4 地址而不是用CHAR(15) ，但這種方式只能存儲IPv4，存儲不了IPv6。另外可以考慮將日期轉(zhuǎn)化為數(shù)字，如：from_unixtime()、unix_timestamp()。
• 所有字段均定義為NOT NULL，除非你真的想存儲null。

索引設(shè)計的規(guī)范：

1)所有表必須有顯式主鍵

• InnoDB表是以主鍵排序存儲的IOT表
• 盡量使用短、自增的列做索引
• 復(fù)制結(jié)構(gòu)使用row格式，如果表有主鍵可以加速復(fù)制
• UNSIGNED INT自增列，也可以考慮BIGINT
• TINYINT做主鍵可能導(dǎo)致MySQL Crash
• 類型轉(zhuǎn)換會導(dǎo)致查詢效率很低
• 可用uuid_short()代替uuid()，轉(zhuǎn)成BIGINT存儲

2)合理地建立索引

• 選擇區(qū)分度高的列作為索引
• 單個索引字段數(shù)不超過5，單表索引數(shù)量不超過5，避免冗余索引
• 建立的索引能覆蓋80%主要的查詢，不求全，解決問題的主要矛盾
• 復(fù)合索引排序問題，多用explain去確認(rèn)

SQL編寫規(guī)范：

1)避免在數(shù)據(jù)庫中進行大量計算任務(wù)

• 大事務(wù)拆成多個事務(wù)，分批多次操作
• 慎用text、blob大型字段，如要用考慮好拆分方案
• 頻繁查詢的字典表考慮用Cache抗

2)優(yōu)化join

• 避免大表與大表之間的join，考慮讓小表去驅(qū)動大表join
• 最多允許三表join，***控制成兩表
• 控制join后面where選擇的行數(shù)

3)注重where條件，多用EXPLAIN確認(rèn)

• where條件的字段，盡量用區(qū)別度高的字段，這樣走索引的性能更好
• 出現(xiàn)子查詢的SQL，先確認(rèn)MySQL版本，利用explain確認(rèn)執(zhí)行計劃
• 進行分頁優(yōu)化;DML時候多個value合并

Schema Review：

1)字符集問題

表字符集選擇UTF8 ，如果需要存儲emoj表情，就改成UTF8mb4

2)Schema設(shè)計原則

• 核心表字段數(shù)量盡可能地少，有大字段要考慮拆分
• 適當(dāng)考慮一些反范式的表設(shè)計，增加冗余字段，減少JOIN
• 資金字段考慮統(tǒng)一*100處理成整型，避免使用decimal浮點類型存儲
• 日志類型的表可以考慮按創(chuàng)建時間水平切割，定期歸檔歷史數(shù)據(jù)

3)Schema設(shè)計目標(biāo)

• 快速實現(xiàn)功能為主，保證節(jié)省資源
• 平衡業(yè)務(wù)技術(shù)各個方面，做好取舍
• 不要在DB里進行大計算，減少復(fù)雜操作
• 整體來說，這部分規(guī)范還是很容易遵守的，實現(xiàn)起來也沒有什么難度，就能取得很好的效果。

2、運維規(guī)范

(1)SQL審核

SQL評審這部分工作相信讓很多的DBA同學(xué)都叫苦不迭，人肉審核不僅效率低下，容易出錯，對DBA的自身發(fā)展也非常不利，難道我們來上班就是為了審核SQL的嗎?在經(jīng)過了一段痛苦的人肉審核之后，我們接入了去哪兒網(wǎng)開源的Inception，并根據(jù)自身的業(yè)務(wù)特點做了一些調(diào)整。當(dāng)然現(xiàn)在開源的SQL評審軟件已經(jīng)很多了，大家可以自由選擇，也可以自行開發(fā)。

在審核與執(zhí)行上線DDL語句的時候，要注意MySQL官方原生Online DDL和Percona公司的pt-osc之間的一些差異，例如pt-osc在執(zhí)行時每次都要copy全表，相對來說比較慢，好處是不鎖表，并且有完善的條件檢測和延時負(fù)載策略控制。官方Online DDL雖然官方也一直在改進，但生產(chǎn)環(huán)境使用還不是很***，尤其要注意執(zhí)行過程中容易導(dǎo)致MDL鎖。官方Online DDL也有優(yōu)于pt-osc的地方，比如增刪索引，重命名列等，如下圖所示。

(2)權(quán)限控制

MySQL從5.6開始，逐步完善了權(quán)限系統(tǒng)，比如MySQL5.6可以安裝檢查密碼強度的插件，5.7開始增加了密碼過期機制、賬戶鎖定等功能，對SSL這一塊也做了一些優(yōu)化，8.0版本增加了角色的功能，權(quán)限系統(tǒng)已經(jīng)逐步在向Oracle數(shù)據(jù)庫靠攏了。在日常運維中，也可以使用pt-show-grants工具提高權(quán)限審查的力度。應(yīng)用程序賬號應(yīng)只賦予SELECT、INSERT、UPDATE權(quán)限，DELETE的邏輯改用UPDATE實現(xiàn)，并啟用sql_safe_updates選項。

另一個有效控制權(quán)限的方法就是SQL堡壘機，早期我們通過改造MyWebSQL實現(xiàn)，在Web版客戶端的基礎(chǔ)上加入了一些資源控制策略、審計、語法校驗等功能。后續(xù)又使用Python開發(fā)了功能更完備的SQL堡壘機，同時支持MySQL、Oracle、Greenplum等數(shù)據(jù)庫。

SQL堡壘機不僅可控制公司內(nèi)部人員的數(shù)據(jù)庫權(quán)限，追溯各類人員對數(shù)據(jù)庫的操作，也能避免大查詢或全表更新的情況發(fā)生，支持審計需求，整體運維質(zhì)量提升了一個臺階。

(3)MySQL版本選擇

• MySQL社區(qū)版，用戶群體***
• MySQL企業(yè)版，收費
• Percona Server版，新特性多，和MySQL社區(qū)版最接近
• MariaDB版，國內(nèi)用戶暫時不多
• 選擇優(yōu)先級：MySQL社區(qū)版> Percona Server > MariaDB > MySQL 企業(yè)版

對于版本選擇這件事，建議大家還是跟進官方社區(qū)版比較好，目前比較穩(wěn)定的版本是MySQL5.6，推薦大家使用。有特殊需求的話再選擇MySQL5.7、PXC、TiDB、TokuDB等數(shù)據(jù)庫。

二、MySQL高可用架構(gòu)選型

MySQL高可用方面，目前業(yè)界主流依然是基于異步復(fù)制的技術(shù)，例如Keepalived、MHA、ZooKeeper等，要求數(shù)據(jù)強一致的場景逐步開始使用分布式協(xié)議，這方面的典型代表有PXC、Group Replication、TiDB。下面我們就重點來說說keepalived、MHA和PXC這幾種大家用得比較多的架構(gòu)。

1、keepalived高可用架構(gòu)

業(yè)內(nèi)使用非常普遍，它部署容易、方便維護，還節(jié)省服務(wù)器資源。這種架構(gòu)的一個好處就是在發(fā)生切換后，原Master只需重新拉起來即可恢復(fù)高可用，不需要過多干預(yù)。擴展起來也方便，可以任意掛載只讀庫和災(zāi)備庫。但它存在的問題也很明顯，比如Keepalived的檢測機制不完善、有腦裂隱患、數(shù)據(jù)一致性較弱等等。

還需要注意主從拓?fù)涞脑O(shè)計。如下圖，只讀庫掛到哪個Master比較合適?顯然是M2，其它兩種拓?fù)湓诎l(fā)生切換后都會影響到只讀庫的訪問。

2、MHA

MHA自誕生以來，就得到了業(yè)內(nèi)的廣泛關(guān)注，并迅速流行開來。與keepalived相比，MHA***的優(yōu)點就是在發(fā)生故障切換之后，能自動補齊binlog，***程度保證數(shù)據(jù)一致性。從服務(wù)器能自動切換，無需人工干預(yù)，能非常好的工作在讀寫分離的環(huán)境下?；赑erl語言的腳本也非常方便進行二次開發(fā)。MHA非常適合讀寫壓力比較大的應(yīng)用。

但由于MHA在工作時需要配置SSH互信，因此選擇這種架構(gòu)時內(nèi)網(wǎng)安全一定要做到位。另外也可以搭配Binlog Server使用。

3、PXC

PXC全稱是Percona XtraDB Cluster，是Percona公司基于Galera協(xié)議開發(fā)的一個產(chǎn)品。PXC犧牲了CAP里面的P(Partition Tolerance)，保留了C(Consistency )和A(Availability )。這種結(jié)構(gòu)非常適合電商、金融類業(yè)務(wù)，自PXC和Group Replication出現(xiàn)以后，MySQL徹底掃清了進入金融行業(yè)的障礙。

PXC的優(yōu)勢：

• 同步復(fù)制，解決了傳統(tǒng)架構(gòu)復(fù)制延遲和腦裂的問題
• 數(shù)據(jù)強一致
• 多主復(fù)制，每個節(jié)點都可以讀寫數(shù)據(jù)
• 并行復(fù)制，多個事務(wù)可以并行推送到其他節(jié)點
• 高可用，單點故障不影響集群可用性
• 新節(jié)點自動部署
• 與傳統(tǒng)MySQL幾乎完全兼容

使用PXC要注意的問題：

• 不要有大事務(wù)
• 木桶效應(yīng)，集群性能取決于性能最差的那個節(jié)點
• 并發(fā)效率有損失
• 網(wǎng)絡(luò)要求較高，建議萬兆網(wǎng)絡(luò)
• 多點并發(fā)寫時鎖沖突、死鎖問題多
• 寫無法擴展，無法解決熱點更新問題

除此之外，還有一類采用DNS/ZooKeeper的高可用架構(gòu)，這種架構(gòu)通常都需要自行開發(fā)，無通用的方案，比較適合大規(guī)模集群的高可用，這里我們不過多贅述。

下面簡單回顧一下上述幾種高可用架構(gòu)：

1. 雙Master架構(gòu)：非常成熟，使用很普遍，要注意延遲和數(shù)據(jù)的一致性。
2. PXC: 分布式協(xié)議，數(shù)據(jù)強一致性，并發(fā)效率略低，可用性好
3. MHA：各項指標(biāo)介于M-M和PXC之間，性能無損失，適合讀寫分離架構(gòu)。

總而言之，沒有最***的架構(gòu)，只有最適合的架構(gòu)。選擇適合自己業(yè)務(wù)的即可。

三、MySQL sharding拆分

接下來是第三個議題，MySQL拆分原則和分庫分表設(shè)計。

首先先提一個問題，為什么要拆，不拆不行嗎?按照我們的經(jīng)驗來看，當(dāng)數(shù)據(jù)和業(yè)務(wù)到了一定的規(guī)模，都不可避免的要面臨分庫分表的問題。這就好像汽車的發(fā)動機一樣，要達到更高的性能，4缸6缸明顯是不夠用的，V8、V12才是王道。

拆分能解決如下幾個問題：

• 單庫并發(fā)較大
• 單庫物理文件太大
• 單表過大，DDL無法接受
• 防止出現(xiàn)性能瓶頸，提升性能
• 防止出現(xiàn)抖動不穩(wěn)定現(xiàn)象

確定要進行數(shù)據(jù)庫的拆分了，應(yīng)該怎么拆呢?

垂直拆分

優(yōu)點：

• 拆分簡單明了，拆分規(guī)則明確
• 應(yīng)用程序模塊清晰，整合容易
• 數(shù)據(jù)維護方便易行，容易定位

缺點：

• 表關(guān)聯(lián)需要改到程序中完成
• 事務(wù)處理變的復(fù)雜
• 熱點表還有可能存在性能瓶頸
• 過度拆分會造成管理復(fù)雜

水平拆分

優(yōu)點：

• 不會影響表關(guān)聯(lián)、事務(wù)操作
• 超大規(guī)模的表和高負(fù)載的表可以打散
• 應(yīng)用程序端改動比較小
• 拆分能提升性能，也比較易擴展

缺點：

• 數(shù)據(jù)分散，影響聚集函數(shù)的使用
• 切分規(guī)則復(fù)雜，維護難度增加
• 后期遷移較復(fù)雜

要先分庫還是先分表?

• 分庫的優(yōu)點：實現(xiàn)簡單，庫與庫之間界限分明，便于維護，缺點是不利于頻繁跨庫操作，單表數(shù)據(jù)量大的問題解決不了。
• 分表的優(yōu)點：能解決分庫的不足點，但是缺點恰恰是分庫的優(yōu)點，分表實現(xiàn)起來比較復(fù)雜，特別是分表規(guī)則的劃分，程序的編寫，以及后期的數(shù)據(jù)庫拆分移植維護。

一巴掌拍板直接選分庫或分表都是不可取的，主要是看需要達到什么樣的擴展方式，才能決定先分庫還是先分表，根據(jù)具體的場景決定。分庫分表的最終目的還是為了擴展，而且要看拆分的規(guī)劃設(shè)計是針對哪一層。

上述問題都解決了，該考慮如何實現(xiàn)了，到底是在應(yīng)用程序中實現(xiàn)，還是使用中間件?個人建議如果是小規(guī)模的拆分，直接在程序邏輯中實現(xiàn)即可，大規(guī)模的拆分再考慮使用各種中間件。

目前業(yè)內(nèi)已經(jīng)開源了很多的MySQL中間件產(chǎn)品，例如Atlas、DBProxy、MyCAT、OneProxy、DRDS、Vitess等等，每個中間件都有自己的特點，個別不太成熟的可能會存在一些Bug，選用之前要做好相關(guān)的調(diào)研與測試工作，上線使用一定要保證自己能hold住。如果要完全貼合自身業(yè)務(wù)，并且掌控得較好的還是要自行開發(fā)。

下面說說我們的拆分經(jīng)驗。

首先我們先在壓力比較大的數(shù)據(jù)庫上做垂直拆分，剝離出活動、后臺統(tǒng)計等業(yè)務(wù)。這一步也是最容易實現(xiàn)的。

接下來，如果是消息類的數(shù)據(jù)，就基于時間維度進行拆分，單表控制在5-10G，行數(shù)控制到500-1000w這個樣子。這個時候我們發(fā)現(xiàn)數(shù)據(jù)庫的性能是比較好的，而且比較好維護。如果是用戶類的數(shù)據(jù)，就按照Hash或Range進行拆分。這種情況下用這種方法拆分會拆的比較均勻一些。

并發(fā)仍然比較高怎么辦?可以在時間維度拆分的基礎(chǔ)上再按Range或Hash進行拆分。

***要注意的就是不要過度的拆分，會造成復(fù)雜度的上升。Schema設(shè)計合理的情況下，10億的數(shù)據(jù)量也能跑的好好的。個別不關(guān)鍵的應(yīng)用，例如日志、監(jiān)控數(shù)據(jù)等，使用分區(qū)表、TokuDB也能抗。拆分對應(yīng)用層總是有損的。

要做個“懶”DBA。

四、利用NoSQL為MySQL減壓

***一個議題，我們聊一聊NoSQL。NoSQL現(xiàn)在遍地開花，應(yīng)用也很廣泛了，業(yè)內(nèi)用的比較多的主要集中在Redis、MongoDB、Cassandra等NoSQL數(shù)據(jù)庫上。今天我們主要來說說和MySQL關(guān)聯(lián)最為密切的Redis。

為什么要使用Redis?

• 數(shù)據(jù)存儲在內(nèi)存中，訪問速度快
• 能支持大批量操作及爆發(fā)性負(fù)載
• 數(shù)據(jù)結(jié)構(gòu)豐富，有效緩解MySQL壓力
• 協(xié)議簡單，支持各種語言的API
• 存儲大量數(shù)據(jù)無需擔(dān)心性能

Redis主要作用還是抗讀的壓力。讀操作先到Redis，Redis中取不到再從MySQL數(shù)據(jù)庫訪問，從MySQL讀取到數(shù)據(jù)后，還要回寫到Redis。

使用Redis要注意的幾點：

性能方面，由于Redis完全是基于內(nèi)存的訪問，性能無需擔(dān)心。

在使用Redis時，要注意Cache 和Storage不要混合使用。不要依賴Redis的持久化，持久化這一塊Redis要努力的還很多。另外如果你把Redis拿來做Storage的話，一旦Redis的內(nèi)存跑滿，那就慘了，所有的Redis連接都會卡著不響應(yīng)。如果只是把Redis來做cache的話，那問題就不大。

還有諸如緩存穿透、緩存雪崩、熱點key重建時緩存失效這些問題也是重點關(guān)注的對象。

如何利用Redis給MySQL加速：

1)利用K/V結(jié)構(gòu)，緩存結(jié)果，例如存儲用戶信息、全局排行、統(tǒng)計信息等。

2)利用其豐富的數(shù)據(jù)結(jié)構(gòu)為MySQL減壓，例如計數(shù)器、排序、Hash(把表映射到Redis中)、消息隊列等。

總結(jié)

系統(tǒng)架構(gòu)設(shè)計是一個長期總結(jié)與進化的過程，講究均衡與取舍。在進行大規(guī)模MySQL架構(gòu)設(shè)計的過程中，除了要汲取別人的經(jīng)驗之外，還要關(guān)注各種架構(gòu)背后的業(yè)務(wù)場景與架構(gòu)思想，與自己的實際業(yè)務(wù)場景相結(jié)合，才能設(shè)計出一個好的系統(tǒng)架構(gòu)來。

文章題目：MySQL架構(gòu)設(shè)計談：從開發(fā)規(guī)范、選型、拆分到減壓
網(wǎng)頁地址：http://uogjgqi.cn/article/coocded.html