Mysql-性能優(yōu)化方案及技術

1、Mysql性能優(yōu)化方案及技術目錄 HYPERLINK l _TOC_250028 目錄1 HYPERLINK l _TOC_250027 背景及目標2 HYPERLINK l _TOC_250026 Mysql執(zhí)行優(yōu)化2 HYPERLINK l _TOC_250025 認識數(shù)據(jù)索引2 HYPERLINK l _TOC_250024 為什么使用數(shù)據(jù)索引能提高效率2 HYPERLINK l _TOC_250023 如何理解數(shù)據(jù)索引的結構2 HYPERLINK l _TOC_250022 如何理解影響結果集3 HYPERLINK l _TOC_250021 理解執(zhí)行狀態(tài)4 HYPERLINK l _

2、TOC_250020 常見分析手段4 HYPERLINK l _TOC_250019 分析流程6 HYPERLINK l _TOC_250018 總結7 HYPERLINK l _TOC_250017 Mysql運維優(yōu)化9 HYPERLINK l _TOC_250016 存儲引擎類型9 HYPERLINK l _TOC_250015 內存使用考量9 HYPERLINK l _TOC_250014 性能與安全性考量9 HYPERLINK l _TOC_250013 存儲壓力優(yōu)化10 HYPERLINK l _TOC_250012 運維監(jiān)控體系10 HYPERLINK l _TOC_250011

3、Mysql架構優(yōu)化11 HYPERLINK l _TOC_250010 架構優(yōu)化目標11 HYPERLINK l _TOC_250009 防止單點隱患11 HYPERLINK l _TOC_250008 方便系統(tǒng)擴容11 HYPERLINK l _TOC_250007 安全可控，成本可控11 HYPERLINK l _TOC_250006 分布式方案12 HYPERLINK l _TOC_250005 分庫& 拆表方案12 HYPERLINK l _TOC_250004 主從架構14 HYPERLINK l _TOC_250003 故障轉移處理15 HYPERLINK l _TOC_25000

4、2 緩存方案15 HYPERLINK l _TOC_250001 緩存結合數(shù)據(jù)庫的讀取15 HYPERLINK l _TOC_250000 緩存結合數(shù)據(jù)庫的寫入15背景及目標廈門游家公司4399用于職工培訓和分享。針對用戶群為已經使用過mysql 環(huán)境，并有一定開發(fā)經驗的工程師針對高并發(fā)，海量數(shù)據(jù)的互聯(lián)網環(huán)境。本文語言為口語，非學術標準用語。以實戰(zhàn)和解決具體問題為主要目標，非應試， 非常規(guī)教育。友情提醒，在校生學習本教程可能對成績提高有害無益。非技術挑戰(zhàn)，非高端架構師培訓，請高手自動忽略。Mysql執(zhí)行優(yōu)化認識數(shù)據(jù)索引為什么使用數(shù)據(jù)索引能提高效率數(shù)據(jù)索引的存儲是有序的在有序的情況下，通過索引查

5、詢一個數(shù)據(jù)是無需遍歷索引記錄的極端情況下，數(shù)據(jù)索引的查詢效率為二分法查詢效率，趨近于log2(N)如何理解數(shù)據(jù)索引的結構數(shù)據(jù)索引通常默認采用btree 索引，內存表也使用了hash 索引。單一有序排序序列是查找效率最高的二分查找，或者說折半查找，使用樹形索引的目的是為了到達快速的更新和增刪操作。在極端情況下 比方數(shù)據(jù)查詢需求量非常大，而數(shù)據(jù)更新需求極少，實時性要求不高，數(shù)據(jù)規(guī)模有限 ，直接使用單一排序序列，折半查找速度最快。實戰(zhàn)范例： ip 地址反查資源：Ip 地址對應表，源數(shù)據(jù)格式為startip, endip, area源數(shù)據(jù)條數(shù)為10 萬條左右，呈很大的分散性目標：需要通過任意ip 查詢

6、該 ip 所屬地區(qū)性能要求到達每秒1000 次以上的查詢效率挑戰(zhàn)：如使用betweenand 數(shù)據(jù)庫操作，無法有效使用索引。如果每次查詢請求需要遍歷10 萬條記錄，根本不行。方法：一次性排序只在數(shù)據(jù)準備中進行，數(shù)據(jù)可存儲在內存序列折半查找每次請求以折半查找方式進行在進行索引分析和SQL 優(yōu)化時，可以將數(shù)據(jù)索引字段想象為單一有序序列，并以此作為分析的基礎。實戰(zhàn)范例：復合索引查詢優(yōu)化實戰(zhàn)，同城異性列表資源：用戶表 user，字段sex 性別； area 地區(qū)； lastlogin最后登錄時間；其他略目標：查找同一地區(qū)的異性，按照最后登錄時間逆序高訪問量社區(qū)的高頻查詢，如何優(yōu)化。查詢 SQL: se

7、lect *from user where area=$areaand sex=$sexorder by lastlogin desc limit 0,30;挑戰(zhàn)：建立復合索引并不難，area+sex+lastlogin三個字段的復合索引,如何理解？首先，忘掉btree，將索引字段理解為一個排序序列。如果只使用area 會怎樣？搜索會把符合area 的結果全部找出來，然后在這里面遍歷，選擇命中sex 的并排序。遍歷所有area=$area數(shù)據(jù)！如果使用了area+sex，略好，仍然要遍歷所有area=$areaand sex= $sex數(shù)據(jù)，然后在這個基礎上排序！Area+sex+lastlo

8、gin復合索引時切記lastlogin在最后，該索引基于area+sex+lastlogin三個字段合并的結果排序，該列表可以想象如下。廣州女 $時間 1廣州女 $時間 2廣州女 $時間 3廣州男.深圳女.數(shù)據(jù)庫很容易命中到area+sex 的邊界， 并且基于下邊界向上追溯30 條記錄，搞定！在索引中迅速命中所有結果，無需二次遍歷！如何理解影響結果集影響結果集是數(shù)據(jù)查詢優(yōu)化的一個重要中間數(shù)據(jù)查詢條件與索引的關系決定影響結果集如上例所示， 即便查詢用到了索引，但是如果查詢和排序目標不能直接在索引中命中，其可能帶來較多的影響結果。而這會直接影響到查詢效率微秒級優(yōu)化優(yōu)化查詢不能只看慢查詢日志，常規(guī)來

9、說， 0.01 秒以上的查詢， 都是不夠優(yōu)化的。實戰(zhàn)范例和上案例類似，某游戲社區(qū)要顯示用戶動態(tài)，select * from userfeed whereuid=$uid order by lastlogin desc limit 0,30;初期默認以uid 為索引字段， 查詢?yōu)槊兴衭id=$uid 的結果按照lastlogin 排序。當用戶行為非常頻繁時，該SQL 索引命中影響結果集有數(shù)百乃至數(shù)千條記錄。查詢效率超過 0.01 秒，并發(fā)較大時數(shù)據(jù)庫壓力較大。解決方案：將索引改為uid+lastlogin復合索引，索引直接命中影響結果集 30 條，查詢效率提高了10 倍，平均在0.001 秒

10、，數(shù)據(jù)庫壓力驟降。影響結果集的常見誤區(qū)影響結果集并不是說數(shù)據(jù)查詢出來的結果數(shù)或操作影響的結果數(shù)，而是查詢條件的索引所命中的結果數(shù)。實戰(zhàn)范例某游戲數(shù)據(jù)庫使用了innodb ， innodb 是行級鎖，理論上很少存在鎖表情況。出現(xiàn)了一個SQL 語句 (delete from tabname where xid=)，這個 SQL 非常用 SQL，僅在特定情況下出現(xiàn)，每天出現(xiàn)頻繁度不高一天僅10 次左右，數(shù)據(jù)表容量百萬級，但是這個xid 未建立索引，于是悲慘的事情發(fā)生了，當執(zhí)行這條delete 的時候，真正刪除的記錄非常少，也許一到兩條，也許一條都沒有；但是！由于這個xid 未建立索引， delete

11、 操作時遍歷全表記錄，全表被delete 操作鎖定， select 操作全部被locked，由于百萬條記錄遍歷時間較長，期間大量select 被阻塞，數(shù)據(jù)庫連接過多崩潰。這種非高發(fā)請求，操作目標很少的SQL ，因未使用索引，連帶導致整個數(shù)據(jù)庫的查詢阻塞，需要極大提高警覺?？偨Y：影響結果集是搜索條件索引命中的結果集，而非輸出和操作的結果集。影響結果集越趨近于實際輸出或操作的目標結果集，索引效率越高。請注意，我這里永遠不會講關于外鍵和join的優(yōu)化，因為在我們的體系里，這是根本不允許的！架構優(yōu)化部分會解釋為什么。理解執(zhí)行狀態(tài)常見分析手段慢查詢日志，關注重點如下是否鎖定，及鎖定時間如存在鎖定， 則該

12、慢查詢通常是因鎖定因素導致，本身無需優(yōu)化， 需解決鎖定問題。影響結果集如影響結果集較大，顯然是索引項命中存在問題，需要認真對待。Explain操作索引項使用不建議用usingindex 做強制索引，如未如預期使用索引，建議重新斟酌表結構和索引設置。影響結果集這里顯示的數(shù)字不一定準確，結合之前提到對數(shù)據(jù)索引的理解來看，還記得嘛？就把索引當作有序序列來理解，反思SQL 。Set profiling , show profiles for query操作執(zhí)行開銷注意，有問題的SQL 如果重復執(zhí)行，可能在緩存里，這時要注意防止緩存影響。通過這里可以看到。執(zhí)行時間超過0.005 秒的頻繁操作SQL 建議

13、都分析一下。深入理解數(shù)據(jù)庫執(zhí)行的過程和開銷的分布Show processlist狀態(tài)清單Sleep 狀態(tài)，通常代表資源未釋放，如果是通過連接池，sleep 狀態(tài)應該恒定在一定數(shù)量范圍內實戰(zhàn)范例：因前端數(shù)據(jù)輸出時特別是輸出到用戶終端未及時關閉數(shù)據(jù)庫連接，導致因網絡連接速度產生大量sleep 連接，在網速出現(xiàn)異常時，數(shù)據(jù)庫too many connections掛死。簡單解讀，數(shù)據(jù)查詢和執(zhí)行通常只需要不到0.01 秒，而網絡輸出通常需要1 秒左右甚至更長，原本數(shù)據(jù)連接在0.01 秒即可釋放，但是因為前端程序未執(zhí)行close 操作，直接輸出結果，那么在結果未展現(xiàn)在用戶桌面前，該數(shù)據(jù)庫連接一直維持在s

14、leep 狀態(tài)！Waiting for net, reading from net, writing to net偶爾出現(xiàn)無妨如大量出現(xiàn)，迅速檢查數(shù)據(jù)庫到前端的網絡連接狀態(tài)和流量案例 : 因外掛程序，內網數(shù)據(jù)庫大量讀取，內網使用的百兆交換迅速爆滿，導致大量連接阻塞在waiting for net ，數(shù)據(jù)庫連接過多崩潰Locked 狀態(tài)有更新操作鎖定通常使用innodb 可以很好的減少locked 狀態(tài)的產生，但是切記，更新操作要正確使用索引，即便是低頻次更新操作也不能疏忽。如上影響結果集范例所示。在 myisam 的時代， locked 是很多高并發(fā)應用的噩夢。所以mysql 官方也開始傾向于

15、推薦innodb 。Copy to tmp table索引及現(xiàn)有結構無法涵蓋查詢條件，才會建立一個臨時表來滿足查詢要求，產生巨大的恐怖的i/o 壓力。很可怕的搜索語句會導致這樣的情況，如果是數(shù)據(jù)分析， 或者半夜的周期數(shù)據(jù)清理任務，偶爾出現(xiàn)，可以允許。頻繁出現(xiàn)務必優(yōu)化之。Copy to tmp table通常與連表查詢有關，建議逐漸習慣不使用連表查詢。實戰(zhàn)范例：某社區(qū)數(shù)據(jù)庫阻塞，求救， 經查， 其服務器存在多個數(shù)據(jù)庫應用和網站，其中一個不常用的小網站數(shù)據(jù)庫產生了一個恐怖的copy to tmp table操作，導致整個硬盤i/o 和 cpu 壓力超載。 Kill掉該操作一切恢復。Sending

16、dataSending data 并不是發(fā)送數(shù)據(jù)，別被這個名字所欺騙，這是從物理磁盤獲取數(shù)據(jù)的進程，如果你的影響結果集較多，那么就需要從不同的磁盤碎片去抽取數(shù)據(jù)，偶爾出現(xiàn)該狀態(tài)連接無礙?；氐缴厦嬗绊懡Y果集的問題，一般而言， 如果 sending data 連接過多， 通常是某查詢的影響結果集過大，也就是查詢的索引項不夠優(yōu)化。如果出現(xiàn)大量相似的SQL 語句出現(xiàn)在show proesslist 列表中，并且都處于 sending data 狀態(tài)，優(yōu)化查詢索引，記住用影響結果集的思路去思考。Freeing items理論上這玩意不會出現(xiàn)很多。偶爾出現(xiàn)無礙如果大量出現(xiàn)，內存，硬盤可能已經出現(xiàn)問題。比方

17、硬盤滿或損壞。Sorting for和 Sending data 類似，結果集過大，排序條件沒有索引化，需要在內存里排序，甚至需要創(chuàng)建臨時結構排序。其他還有很多狀態(tài)， 遇到了， 去查查資料。 基本上我們遇到其他狀態(tài)的阻塞較少，所以不關心。分析流程基本流程詳細了解問題狀況Too many connections 是常見表象，有很多種原因。索引損壞的情況在innodb 情況下很少出現(xiàn)。如出現(xiàn)其他情況應追溯日志和錯誤信息。了解基本負載狀況和運營狀況基本運營狀況當前每秒讀請求當前每秒寫請求當前在線用戶 當前數(shù)據(jù)容量基本負載情況學會使用這些指令Top Vmstat uptime iostat dfCpu

18、 負載構成特別關注i/o 壓力 ( wa%)多核負載分配內存占用Swap 分區(qū)是否被侵占如 Swap 分區(qū)被侵占，物理內存是否較多空閑磁盤狀態(tài)硬盤滿和inode 節(jié)點滿的情況要迅速定位和迅速處理了解具體連接狀況當前連接數(shù)Netstat an|grep 3306|wc l Show processlist當前連接分布show processlist前端應用請求數(shù)據(jù)庫不要使用root 帳號！Root 帳號比其他普通帳號多一個連接數(shù)許可。前端使用普通帳號，在too many connections 的時候 root 帳號仍可以登錄數(shù)據(jù)庫查詢show processlist!記住， 前端應用程序不要設

19、置一個不叫root 的 root 帳號來糊弄！ 非 root 賬戶是骨子里的，而不是名義上的。狀態(tài)分布不同狀態(tài)代表不同的問題，有不同的優(yōu)化目標。參見如上范例。雷同 SQL 的分布是否較多雷同SQL 出現(xiàn)在同一狀態(tài)當前是否有較多慢查詢日志是否鎖定影響結果集頻繁度分析寫頻繁度如果 i/o 壓力高，優(yōu)先分析寫入頻繁度Mysqlbinlog輸出最新binlog 文件，編寫腳本拆分最多寫入的數(shù)據(jù)表是哪個最多寫入的數(shù)據(jù)SQL 是什么是否存在基于同一主鍵的數(shù)據(jù)內容高頻重復寫入？涉及架構優(yōu)化部分，參見架構優(yōu)化-緩存異步更新讀取頻繁度如果 cpu 資源較高，而i/o 壓力不高，優(yōu)先分析讀取頻繁度程序中在封裝的d

20、b 類增加抽樣日志即可，抽樣比例酌情考慮，以不顯著影響系統(tǒng)負載壓力為底線。最多讀取的數(shù)據(jù)表是哪個最多讀取的數(shù)據(jù)SQL 是什么該 SQL 進行 explain和 set profiling 判定注意判定時需要防止query cache 影響比方，在這個SQL 末尾增加一個條件子句and 1=1 就可以防止從 query cache 中獲取數(shù)據(jù)，而得到真實的執(zhí)行狀態(tài)分析。是否存在同一個查詢短期內頻繁出現(xiàn)的情況涉及前端緩存優(yōu)化抓大放小，解決顯著問題不苛求解決所有優(yōu)化問題，但是應以保證線上服務穩(wěn)定可靠為目標。解決與評估要同時進行，新的策略或解決方案務必經過評估后上線?？偨Y要學會怎樣分析問題，而不是單純

21、拍腦袋優(yōu)化慢查詢只是最基礎的東西，要學會優(yōu)化0.01 秒的查詢請求。當發(fā)生連接阻塞時， 不同狀態(tài)的阻塞有不同的原因，要找到原因， 如果不對癥下藥， 就會南轅北轍范例：如果本身系統(tǒng)內存已經超載，已經使用到了swap，而還在考慮加大緩存來優(yōu)化查詢，那就是自尋死路了。監(jiān)測與跟蹤要經常做，而不是出問題才做讀取頻繁度抽樣監(jiān)測全監(jiān)測不要搞，i/o 嚇死人。按照一個抽樣比例抽樣即可。針對抽樣中發(fā)現(xiàn)的問題，可以按照特定SQL 在特定時間內監(jiān)測一段全查詢記錄，但仍要考慮i/o 影響。寫入頻繁度監(jiān)測基于 binlog 解開即可，可定時或不定時分析。微慢查詢抽樣監(jiān)測高并發(fā)情況下， 查詢請求時間超過0.01 秒甚至

22、0.005 秒的， 建議酌情抽樣記 錄 。 連接數(shù)預警監(jiān)測連接數(shù)超過特定閾值的情況下，雖然數(shù)據(jù)庫沒有崩潰，建議記錄相關連接狀態(tài)。學會通過數(shù)據(jù)和監(jiān)控發(fā)現(xiàn)問題，分析問題， 而后解決問題順理成章。特別是要學會在日常監(jiān)控中發(fā)現(xiàn)隱患，而不是問題爆發(fā)了才去處理和解決。Mysql運維優(yōu)化存儲引擎類型Myisam速度快，響應快。表級鎖是致命問題。Innodb目前主流存儲引擎行級鎖務必注意影響結果集的定義是什么行級鎖會帶來更新的額外開銷，但是通常情況下是值得的。事務提交對 i/o 效率提升的考慮對安全性的考慮HEAP內存引擎頻繁更新和海量讀取情況下仍會存在鎖定狀況內存使用考量理論上，內存越大，越多數(shù)據(jù)讀取發(fā)生在

23、內存，效率越高要考慮到現(xiàn)實的硬件資源和瓶頸分布學會理解熱點數(shù)據(jù)，并將熱點數(shù)據(jù)盡可能內存化所謂熱點數(shù)據(jù)，就是最多被訪問的數(shù)據(jù)。通常數(shù)據(jù)庫訪問是不平均的，少數(shù)數(shù)據(jù)被頻繁讀寫，而更多數(shù)據(jù)鮮有讀寫。學會制定不同的熱點數(shù)據(jù)規(guī)則，并測算指標。熱點數(shù)據(jù)規(guī)模， 理論上， 熱點數(shù)據(jù)越少越好，這樣可以更好的滿足業(yè)務的增長趨勢。響應滿足度，對響應的滿足率越高越好。比方依據(jù)最后更新時間，總訪問量， 回訪次數(shù)等指標定義熱點數(shù)據(jù)，并測算不同定義模式下的熱點數(shù)據(jù)規(guī)模性能與安全性考量數(shù)據(jù)提交方式innodb_flush_log_at_trx_commit = 1每次自動提交，安全性高，i/o 壓力大innodb_flush_

24、log_at_trx_commit = 2每秒自動提交， 安全性略有影響，i/o 承載強。日志同步Sync-binlog=1 每條自動更新，安全性高，i/o 壓力大Sync-binlog = 0根據(jù)緩存設置情況自動更新，存在喪失數(shù)據(jù)和同步延遲風險，i/o 承載力強。性能與安全本身存在相悖的情況，需要在業(yè)務訴求層面決定取舍學會區(qū)分什么場合側重性能，什么場合側重安全學會將不同安全等級的數(shù)據(jù)庫用不同策略管理存儲壓力優(yōu)化順序讀寫性能遠高于隨機讀寫日志類數(shù)據(jù)可以使用順序讀寫方式進行將順序寫數(shù)據(jù)和隨機讀寫數(shù)據(jù)分成不同的物理磁盤，有助于i/o 壓力的疏解，前提是，你確信你的i/o 壓力主要來自于可順序寫操作

25、因隨機讀寫干擾導致不能順序寫，但是確實可以用順序寫方式進行的i/o 操作。運維監(jiān)控體系系統(tǒng)監(jiān)控服務器資源監(jiān)控Cpu, 內存，硬盤空間，i/o 壓力設置閾值報警服務器流量監(jiān)控外網流量，內網流量設置閾值報警連接狀態(tài)監(jiān)控Show processlist 設置閾值，每分鐘監(jiān)測，超過閾值記錄應用監(jiān)控慢查詢監(jiān)控慢查詢日志如果存在多臺數(shù)據(jù)庫服務器，應有匯總查閱機制。請求錯誤監(jiān)控高頻繁應用中， 會出現(xiàn)偶發(fā)性數(shù)據(jù)庫連接錯誤或執(zhí)行錯誤，將錯誤信息記錄到日志，查看每日的比例變化。偶發(fā)性錯誤，如果數(shù)量極少，可以不用處理，但是需時常監(jiān)控其趨勢。會存在惡意輸入內容，輸入邊界限定缺乏導致執(zhí)行出錯，需基于此防止惡意入侵探測行

26、為。微慢查詢監(jiān)控高并發(fā)環(huán)境里，超過0.01 秒的查詢請求都應該關注一下。頻繁度監(jiān)控寫操作，基于binlog ，定期分析。讀操作，在前端db 封裝代碼中增加抽樣日志，并輸出執(zhí)行時間。分析請求頻繁度是開發(fā)架構進一步優(yōu)化的基礎最好的優(yōu)化就是減少請求次數(shù)！總結：監(jiān)控與數(shù)據(jù)分析是一切優(yōu)化的基礎。沒有運營數(shù)據(jù)監(jiān)測就不要妄談優(yōu)化！監(jiān)控要注意不要產生太多額外的負載，不要因監(jiān)控帶來太多額外系統(tǒng)開銷Mysql架構優(yōu)化架構優(yōu)化目標防止單點隱患所謂單點隱患， 就是某臺設備出現(xiàn)故障，會導致整體系統(tǒng)的不可用，這個設備就是單點隱患。理解連帶效應，所謂連帶效應，就是一種問題會引發(fā)另一種故障，舉例而言， memcache+my

27、sql 是一種常見緩存組合，在前端壓力很大時，如果 memcache 崩潰， 理論上數(shù)據(jù)會通過mysql 讀取， 不存在系統(tǒng)不可用情況，但是 mysql 無法對抗如此大的壓力沖擊，會因此連帶崩潰。因A 系統(tǒng)問題導致B 系統(tǒng)崩潰的連帶問題，在運維過程中會頻繁出現(xiàn)。實戰(zhàn)范例：在 mysql 連接不及時釋放的應用環(huán)境里，當網絡環(huán)境異常同機房友鄰服務器遭受拒絕服務攻擊，出口阻塞，網絡延遲加劇，空連接數(shù)急劇增加，導致數(shù)據(jù)庫連接過多崩潰。實戰(zhàn)范例2：前端代碼通常我們封裝mysql_connect 和 memcache_connect， 二者的順序不同，會產生不同的連帶效應。如果mysql_connect

28、在前，那么一旦 memcache 連接阻塞，會連帶mysql 空連接過多崩潰。連帶效應是常見的系統(tǒng)崩潰，日常分析崩潰原因的時候需要認真考慮連帶效應的影響，頭疼醫(yī)頭，腳疼醫(yī)腳是不行的。方便系統(tǒng)擴容數(shù)據(jù)容量增加后，要考慮能夠將數(shù)據(jù)分布到不同的服務器上。請求壓力增加時，要考慮將請求壓力分布到不同服務器上。 擴容設計時需要考慮防止單點隱患。安全可控，成本可控數(shù)據(jù)安全，業(yè)務安全人力資源成本 帶寬流量成本硬件成本成本與流量的關系曲線應低于線性增長流量為橫軸，成本為縱軸。規(guī)模優(yōu)勢本教程僅就與數(shù)據(jù)庫有關部分討論，與數(shù)據(jù)庫無關部門請自行參閱其他學習資料。分布式方案分庫& 拆表方案基本認識用分庫 & 拆表是解決數(shù)

29、據(jù)庫容量問題的唯一途徑。分庫& 拆表也是解決性能壓力的最優(yōu)選擇。分庫 不同的數(shù)據(jù)表放到不同的數(shù)據(jù)庫服務器中也可能是虛擬服務器拆表 一張數(shù)據(jù)表拆成多張數(shù)據(jù)表，可能位于同一臺服務器，也可能位于多臺服務器含虛擬服務器 。去關聯(lián)化原則摘除數(shù)據(jù)表之間的關聯(lián)，是分庫的基礎工作。摘除關聯(lián)的目的是，當數(shù)據(jù)表分布到不同服務器時，查詢請求容易分發(fā)和處理。學會理解反范式數(shù)據(jù)結構設計，所謂反范式，第一要點是不用外鍵，不允許Join 操作，不允許任何需要跨越兩個表的查詢請求。第二要點是適度冗余減少查詢請求， 比方說， 信息表， fromuid, touid, message 字段外， 還需要一個fromuname 字段

30、記錄用戶名，這樣查詢者通過touid 查詢后，能夠立即得到發(fā)信人的用戶名，而無需進行另一個數(shù)據(jù)表的查詢。去關聯(lián)化處理會帶來額外的考慮，比方說， 某一個數(shù)據(jù)表內容的修改，對另一個數(shù)據(jù)表的影響。這一點需要在程序或其他途徑去考慮。分庫方案安全性拆分將高安全性數(shù)據(jù)與低安全性數(shù)據(jù)分庫，這樣的好處第一是便于維護，第二是高安全性數(shù)據(jù)的數(shù)據(jù)庫參數(shù)配置可以以安全優(yōu)先，而低安全性數(shù)據(jù)的參數(shù)配置以 性能優(yōu)先。參見運維優(yōu)化相關部分。順序寫數(shù)據(jù)與隨機讀寫數(shù)據(jù)分庫順序數(shù)據(jù)與隨機數(shù)據(jù)區(qū)分存儲地址，保證物理i/o 優(yōu)化。這個實話說，我只聽說了概念，還沒學會怎么實踐。基于業(yè)務邏輯拆分根據(jù)數(shù)據(jù)表的內容構成，業(yè)務邏輯拆分，便于日常

31、維護和前端調用?；跇I(yè)務邏輯拆分，可以減少前端應用請求發(fā)送到不同數(shù)據(jù)庫服務器的頻次， 從而減少鏈接開銷。基于業(yè)務邏輯拆分，可保留部分數(shù)據(jù)關聯(lián)，前端web 工程師可在限度范圍內執(zhí)行關聯(lián)查詢?；谪撦d壓力拆分基于負載壓力對數(shù)據(jù)結構拆分，便于直接將負載分擔給不同的服務器?；谪撦d壓力拆分，可能拆分后的數(shù)據(jù)庫包含不同業(yè)務類型的數(shù)據(jù)表，日常維護會有一定的煩惱。分表方案數(shù)據(jù)量過大或者訪問壓力過大的數(shù)據(jù)表需要切分忙閑分表單數(shù)據(jù)表字段過多， 可將頻繁更新的整數(shù)數(shù)據(jù)與非頻繁更新的字符串數(shù)據(jù)切分范例user 表 ，個人簡介，地址，QQ 號，聯(lián)系方式，頭像這些字段為字符串類型，更新請求少；最后登錄時間，在線時常，訪

32、問次數(shù)，信件數(shù)這些字段為整數(shù)型字段，更新頻繁， 可以將后面這些更新頻繁的字段獨立拆出一張數(shù)據(jù)表，表內容變少，索引結構變少，讀寫請求變快。橫向切表等分切表， 如哈希切表或其他基于對某數(shù)字取余的切表。等分切表的優(yōu)點是負 載很方便的分布到不同服務器；缺點是當容量繼續(xù)增加時無法方便的擴容，需要重新進行數(shù)據(jù)的切分或轉表。而且一些關鍵主鍵不易處理。遞增切表，比方每1kw 用戶開一個新表，優(yōu)點是可以適應數(shù)據(jù)的自增趨勢； 缺點是往往新數(shù)據(jù)負載高，壓力分配不平均。日期切表，適用于日志記錄式數(shù)據(jù)，優(yōu)缺點等同于遞增切表。個人傾向于遞增切表，具體根據(jù)應用場景決定。熱點數(shù)據(jù)分表將數(shù)據(jù)量較大的數(shù)據(jù)表中將讀寫頻繁的數(shù)據(jù)抽取

33、出來，形成熱點數(shù)據(jù)表。 通常一個龐大數(shù)據(jù)表經常被讀寫的內容往往具有一定的集中性，如果這些集中數(shù)據(jù)單獨處理，就會極大減少整體系統(tǒng)的負載。熱點數(shù)據(jù)表與舊有數(shù)據(jù)關系可以是一張冗余表，即該表數(shù)據(jù)喪失不會阻礙使用，因源數(shù)據(jù)仍存在于舊有結構中。 優(yōu)點是安全性高，維護方便，缺點是寫壓力不能分擔，仍需要同步寫回原系統(tǒng)?？梢允欠侨哂啾恚?即熱點數(shù)據(jù)的內容原有結構不再保存，優(yōu)點是讀寫效率全部優(yōu)化；缺點是當熱點數(shù)據(jù)發(fā)生變化時，維護量較大。具體方案選擇需要根據(jù)讀寫比例決定，在讀頻率遠高于寫頻率情況下，優(yōu)先考慮冗余表方案。熱點數(shù)據(jù)表可以用單獨的優(yōu)化的硬件存儲，比方昂貴的閃存卡或大內存系統(tǒng)。熱點數(shù)據(jù)表的重要指標熱點數(shù)據(jù)的

34、定義需要根據(jù)業(yè)務模式自行制定策略，常見策略為， 按照最新的操作時間；按照內容豐富度等等。數(shù)據(jù)規(guī)模，比方從1000 萬條數(shù)據(jù)，抽取出100 萬條熱點數(shù)據(jù)。熱點命中率，比方查詢10 次，多少次命中在熱點數(shù)據(jù)內。理論上，數(shù)據(jù)規(guī)模越小，熱點命中率越高，說明效果越好。需要根據(jù)業(yè)務自行評估。熱點數(shù)據(jù)表的動態(tài)維護加載熱點數(shù)據(jù)方案選擇定時從舊有數(shù)據(jù)結構中按照新的策略獲取在從舊有數(shù)據(jù)結構讀取時動態(tài)加載到熱點數(shù)據(jù)剔除熱點數(shù)據(jù)方案選擇基于特定策略，定時將熱點數(shù)據(jù)中訪問頻次較少的數(shù)據(jù)剔除如熱點數(shù)據(jù)是冗余表，則直接刪除即可，如不是冗余表， 需要回寫給舊有數(shù)據(jù)結構。通常，熱點數(shù)據(jù)往往是基于緩存或者key-value 方案

35、冗余存儲，所以這里提到的熱點數(shù)據(jù)表，其實更多是理解思路，用到的場合可能并不多.表結構設計查詢冗余表設計涉及分表操作后，一些常見的索引查詢可能需要跨表，帶來不必要的麻煩。確認查詢請求遠大于寫入請求時，應設置便于查詢項的冗余表。實戰(zhàn)范例，用戶分表，將用戶庫分成假設干數(shù)據(jù)表基于用戶名的查詢和基于uid 的查詢都是高并發(fā)請求。用戶分表基于uid 分成數(shù)據(jù)表，同時基于用戶名做對應冗余表。冗余表要點數(shù)據(jù)一致性，簡單說，同增，同刪，同更新?？梢宰鋈哂?，或者只做主鍵關聯(lián)的冗余，比方通過用戶名查詢uid，再基于 uid 查詢源表。中間數(shù)據(jù)表為了減少會涉及大規(guī)模影響結果集的表數(shù)據(jù)操作，比方count，sum 操

36、作。應將一些統(tǒng)計類數(shù)據(jù)通過中間數(shù)據(jù)表保存。中間數(shù)據(jù)表應能通過源數(shù)據(jù)表恢復。實戰(zhàn)范例：論壇板塊的發(fā)帖量，回帖量，每日新增數(shù)據(jù)等網站每日新增用戶數(shù)等。后臺可以通過源數(shù)據(jù)表更新該數(shù)字。歷史數(shù)據(jù)表歷史數(shù)據(jù)表對應于熱點數(shù)據(jù)表，將需求較少又不能丟棄的數(shù)據(jù)存入，僅在少數(shù)情況下被訪問。主從架構基本認識讀寫別離對負載的減輕遠遠不如分庫分表來的直接。寫壓力會傳遞給從表，只讀從庫一樣有寫壓力，一樣會產生讀寫鎖！一主多從結構下，主庫是單點隱患，很難解決如主庫當機，從庫可以響應讀寫， 但是無法自動擔當主庫的分發(fā)功能主從延遲也是重大問題。一旦有較大寫入問題，如表結構更新， 主從會產生巨大延遲。應用場景在線熱備異地分布寫分

37、布，讀統(tǒng)一。仍然困難重重，受限于網絡環(huán)境問題巨多！ 自動障礙轉移主崩潰，從自動接管個人建議，負載均衡主要使用分庫方案，主從主要用于熱備和障礙轉移。潛在優(yōu)化點為了減少寫壓力， 有些人建議主不建索引提升i/o 性能， 從建立索引滿足查詢要求。個人認為這樣維護較為麻煩。而且從本身會繼承主的i/o 壓力，因此優(yōu)化價值有限。該思路特此分享，不做推薦。故障轉移處理要點程序與數(shù)據(jù)庫的連接，基于虛地址而非真實ip，由負載均衡系統(tǒng)監(jiān)控。保持主從結構的簡單化，否則很難做到故障點摘除。思考方式遍歷對服務器集群的任何一臺服務器，前端web，中間件，監(jiān)控，緩存，db 等等， 假設該服務器出現(xiàn)故障，系統(tǒng)是否會出現(xiàn)異常？用

38、戶訪問是否會出現(xiàn)異常。目標：任意一臺服務器崩潰，負載和數(shù)據(jù)操作均會很短時間內自動轉移到其他服務 器，不會影響業(yè)務的正常進行。不會造成惡性的數(shù)據(jù)喪失。哪些是可以喪失的， 哪些是不能喪失的緩存方案緩存結合數(shù)據(jù)庫的讀取Memcached 是最常用的緩存系統(tǒng)Mysql最新版本已經開始支持memcache 插件，但據(jù)牛人分析，尚不成熟，暫不推薦。數(shù)據(jù)讀取并不是所有數(shù)據(jù)都適合被緩存，也并不是進入了緩存就意味著效率提升。命中率是第一要評估的數(shù)據(jù)。如何評估進入緩存的數(shù)據(jù)規(guī)模，以及命中率優(yōu)化，是非常需要細心分析的。實景分析：前端請求先連接緩存，緩存未命中連接數(shù)據(jù)庫，進行查詢，未命中狀態(tài)比單純連接數(shù)據(jù)庫查詢多了一次連接和查詢的操作；如果緩存命中率很低，則這個額外的操作非但不能提高查詢效率，反而為系統(tǒng)帶來了額外的負載和復雜性，得不償失。相關評估類似于熱點數(shù)據(jù)表的介紹。善于利用內存，請注意數(shù)據(jù)存儲的格式及壓縮算法。Key-value方案繁多，本培訓文檔暫不展開。緩存結合數(shù)據(jù)庫的寫入利用緩存不但可以減少數(shù)據(jù)讀取