[[200404]]

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

本文大綱：

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

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

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

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

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

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

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

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

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

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

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

2)合理地建立索引

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

SQL編寫規(guī)范：

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

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

2)優(yōu)化join

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

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

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

Schema Review：

1)字符集問(wèn)題

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

2)Schema設(shè)計(jì)原則

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

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

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

2、運(yùn)維規(guī)范

(1)SQL審核

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

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

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

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

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

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

(3)MySQL版本選擇

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

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

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

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

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

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

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

2、MHA

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

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

3、PXC

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

PXC的優(yōu)勢(shì)：

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

使用PXC要注意的問(wèn)題：

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

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

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

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

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

三、MySQL sharding拆分

接下來(lái)是第三個(gè)議題，MySQL拆分原則和分庫(kù)分表設(shè)計(jì)。

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

拆分能解決如下幾個(gè)問(wèn)題：

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

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

垂直拆分

優(yōu)點(diǎn)：

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

缺點(diǎn)：

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

水平拆分

優(yōu)點(diǎn)：

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

缺點(diǎn)：

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

要先分庫(kù)還是先分表?

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

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

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

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

下面說(shuō)說(shuō)我們的拆分經(jīng)驗(yàn)。

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

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

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

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

要做個(gè)“懶”DBA。

[[200407]]

四、利用NoSQL為MySQL減壓

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

為什么要使用Redis?

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

Redis主要作用還是抗讀的壓力。讀操作先到Redis，Redis中取不到再?gòu)腗ySQL數(shù)據(jù)庫(kù)訪問(wèn)，從MySQL讀取到數(shù)據(jù)后，還要回寫到Redis。

使用Redis要注意的幾點(diǎn)：

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

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

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

如何利用Redis給MySQL加速：

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

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

總結(jié)

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