日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
MySQL架構(gòu)設(shè)計(jì)談：從開發(fā)規(guī)范、選型、拆分到減壓

隨著MySQL自身的發(fā)展與不斷完善，不知不覺中整個(gè)互聯(lián)網(wǎng)行業(yè)已離不開這個(gè)完善又小巧的關(guān)系型數(shù)據(jù)庫，整個(gè)生態(tài)鏈也已經(jīng)變得非常成熟，即便是初創(chuàng)企業(yè)和傳統(tǒng)企業(yè)也可以放心大膽地把數(shù)據(jù)庫遷移到MySQL上來。在大家和MySQL數(shù)據(jù)庫愉快玩耍的同時(shí)，我來聊聊MySQL架構(gòu)設(shè)計(jì)相關(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ù)庫運(yùn)維的一個(gè)基石，能有效地減少數(shù)據(jù)庫出問題的概率，保障數(shù)據(jù)庫schema的合理設(shè)計(jì)并方便后續(xù)自動化的管理。

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

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

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

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

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

索引設(shè)計(jì)的規(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ū)分度高的列作為索引
  • 單個(gè)索引字段數(shù)不超過5，單表索引數(shù)量不超過5，避免冗余索引
  • 建立的索引能覆蓋80%主要的查詢，不求全，解決問題的主要矛盾
  • 復(fù)合索引排序問題，多用explain去確認(rèn)

SQL編寫規(guī)范：

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

  • 大事務(wù)拆成多個(gè)事務(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í)行計(jì)劃
  • 進(jìn)行分頁優(yōu)化;DML時(shí)候多個(gè)value合并

Schema Review：

1)字符集問題

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

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

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

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

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

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

(1)SQL審核

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

在審核與執(zhí)行上線DDL語句的時(shí)候，要注意MySQL官方原生Online DDL和Percona公司的pt-osc之間的一些差異，例如pt-osc在執(zhí)行時(shí)每次都要copy全表，相對來說比較慢，好處是不鎖表，并且有完善的條件檢測和延時(shí)負(fù)載策略控制。官方Online DDL雖然官方也一直在改進(jìn)，但生產(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可以安裝檢查密碼強(qiáng)度的插件，5.7開始增加了密碼過期機(jī)制、賬戶鎖定等功能，對SSL這一塊也做了一些優(yōu)化，8.0版本增加了角色的功能，權(quán)限系統(tǒng)已經(jīng)逐步在向Oracle數(shù)據(jù)庫靠攏了。在日常運(yùn)維中，也可以使用pt-show-grants工具提高權(quán)限審查的力度。應(yīng)用程序賬號應(yīng)只賦予SELECT、INSERT、UPDATE權(quán)限，DELETE的邏輯改用UPDATE實(shí)現(xiàn)，并啟用sql_safe_updates選項(xiàng)。

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

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

(3)MySQL版本選擇

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

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

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

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

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

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

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

2、MHA

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

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

使用PXC要注意的問題：

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

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

下面簡單回顧一下上述幾種高可用架構(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之間，性能無損失，適合讀寫分離架構(gòu)。

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

三、MySQL sharding拆分

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

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

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

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

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

垂直拆分

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

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

缺點(diǎn)：

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

水平拆分

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

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

缺點(diǎn)：

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

要先分庫還是先分表?

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

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

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

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

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

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

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

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

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

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

四、利用NoSQL為MySQL減壓

***一個(gè)議題，我們聊一聊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要注意的幾點(diǎn)：

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

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

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

如何利用Redis給MySQL加速：

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

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

總結(jié)

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


分享標(biāo)題：MySQL架構(gòu)設(shè)計(jì)談：從開發(fā)規(guī)范、選型、拆分到減壓
URL分享：http://www.5511xx.com/article/coocded.html