Mysql_性能優(yōu)化教程

1、Mysql 性能優(yōu)化教程目錄目錄1背景及目標(biāo)2Mysql 執(zhí)行優(yōu)化2認識數(shù)據(jù)索引2為什么使用數(shù)據(jù)索引能提高效率2如何理解數(shù)據(jù)索引的結(jié)構(gòu)2如何理解影響結(jié)果集3理解執(zhí)行狀態(tài)4常見分析手段4分析流程6總結(jié)7Mysql 運維優(yōu)化9存儲引擎類型9內(nèi)存使用考量9性能與安全性考量9存儲壓力優(yōu)化10運維監(jiān)控體系10Mysql 架構(gòu)優(yōu)化11架構(gòu)優(yōu)化目標(biāo)11防止單點隱患11方便系統(tǒng)擴容11安全可控，成本可控11分布式方案12分庫&拆表方案12主從架構(gòu)14故障轉(zhuǎn)移處理15緩存方案15緩存結(jié)合數(shù)據(jù)庫的讀取15緩存結(jié)合數(shù)據(jù)庫的寫入15背景及目標(biāo)l 針對用戶群為已經(jīng)使用過mysql環(huán)境，并有一定開發(fā)經(jīng)驗的工程師l 針對高

2、并發(fā)，海量數(shù)據(jù)的互聯(lián)網(wǎng)環(huán)境。l 本文語言為口語，非學(xué)術(shù)標(biāo)準(zhǔn)用語。l 以實戰(zhàn)和解決具體問題為主要目標(biāo)，非應(yīng)試，非常規(guī)教育。友情提醒，在校生學(xué)習(xí)本教程可能對成績提高有害無益。l 非技術(shù)挑戰(zhàn)，非高端架構(gòu)師培訓(xùn)，請高手自動忽略。Mysql 執(zhí)行優(yōu)化認識數(shù)據(jù)索引為什么使用數(shù)據(jù)索引能提高效率n 數(shù)據(jù)索引的存儲是有序的n 在有序的情況下，通過索引查詢一個數(shù)據(jù)是無需遍歷索引記錄的n 極端情況下，數(shù)據(jù)索引的查詢效率為二分法查詢效率，趨近于 log2(N)如何理解數(shù)據(jù)索引的結(jié)構(gòu)n 數(shù)據(jù)索引通常默認采用btree索引，（內(nèi)存表也使用了hash索引）。n 單一有序排序序列是查找效率最高的（二分查找，或者說折半查找），

3、使用樹形索引的目的是為了達到快速的更新和增刪操作。n 在極端情況下（比如數(shù)據(jù)查詢需求量非常大，而數(shù)據(jù)更新需求極少，實時性要求不高，數(shù)據(jù)規(guī)模有限），直接使用單一排序序列，折半查找速度最快。u 實戰(zhàn)范例 ： ip地址反查資源： Ip地址對應(yīng)表，源數(shù)據(jù)格式為 startip, endip, area 源數(shù)據(jù)條數(shù)為 10萬條左右，呈很大的分散性目標(biāo)：需要通過任意ip查詢該ip所屬地區(qū)性能要求達到每秒1000次以上的查詢效率挑戰(zhàn)：如使用 between and 數(shù)據(jù)庫操作，無法有效使用索引。如果每次查詢請求需要遍歷10萬條記錄，根本不行。方法：一次性排序（只在數(shù)據(jù)準(zhǔn)備中進行，數(shù)據(jù)可存儲在內(nèi)存序列）折半查

4、找（每次請求以折半查找方式進行）n 在進行索引分析和SQL優(yōu)化時，可以將數(shù)據(jù)索引字段想象為單一有序序列，并以此作為分析的基礎(chǔ)。u 實戰(zhàn)范例：復(fù)合索引查詢優(yōu)化實戰(zhàn)，同城異性列表資源： 用戶表user，字段 sex性別；area 地區(qū)；lastlogin 最后登錄時間；其他略目標(biāo)： 查找同一地區(qū)的異性，按照最后登錄時間逆序 高訪問量社區(qū)的高頻查詢，如何優(yōu)化。查詢SQL: select * from user where area=$area and sex=$sex order by lastlogin desc limit 0,30;挑戰(zhàn)：建立復(fù)合索引并不難， area+sex+lastlogi

5、n 三個字段的復(fù)合索引,如何理解？首先，忘掉btree，將索引字段理解為一個排序序列。如果只使用area會怎樣？搜索會把符合area的結(jié)果全部找出來，然后在這里面遍歷，選擇命中sex的并排序。 遍歷所有 area=$area數(shù)據(jù)！如果使用了area+sex，略好，仍然要遍歷所有area=$area and sex=$sex數(shù)據(jù)，然后在這個基礎(chǔ)上排序！Area+sex+lastlogin復(fù)合索引時（切記lastlogin在最后），該索引基于area+sex+lastlogin 三個字段合并的結(jié)果排序，該列表可以想象如下。廣州女$時間1廣州女$時間2廣州女$時間3廣州男.深圳女.數(shù)據(jù)庫很容易命中到

6、 area+sex的邊界，并且基于下邊界向上追溯30條記錄，搞定！在索引中迅速命中所有結(jié)果，無需二次遍歷！如何理解影響結(jié)果集n 影響結(jié)果集是數(shù)據(jù)查詢優(yōu)化的一個重要中間數(shù)據(jù)u 查詢條件與索引的關(guān)系決定影響結(jié)果集如上例所示，即便查詢用到了索引，但是如果查詢和排序目標(biāo)不能直接在索引中命中，其可能帶來較多的影響結(jié)果。而這會直接影響到查詢效率u 微秒級優(yōu)化l 優(yōu)化查詢不能只看慢查詢?nèi)罩?，常?guī)來說，0.01秒以上的查詢，都是不夠優(yōu)化的。l 實戰(zhàn)范例和上案例類似，某游戲社區(qū)要顯示用戶動態(tài)，select * from userfeed where uid=$uid order by lastlogin des

7、c limit 0,30; 初期默認以uid為索引字段， 查詢?yōu)槊兴衭id=$uid的結(jié)果按照lastlogin排序。 當(dāng)用戶行為非常頻繁時，該SQL索引命中影響結(jié)果集有數(shù)百乃至數(shù)千條記錄。查詢效率超過0.01秒，并發(fā)較大時數(shù)據(jù)庫壓力較大。 解決方案：將索引改為 uid+lastlogin 復(fù)合索引，索引直接命中影響結(jié)果集30條，查詢效率提高了10倍，平均在0.001秒，數(shù)據(jù)庫壓力驟降。n 影響結(jié)果集的常見誤區(qū)u 影響結(jié)果集并不是說數(shù)據(jù)查詢出來的結(jié)果數(shù)或操作影響的結(jié)果數(shù)，而是查詢條件的索引所命中的結(jié)果數(shù)。u 實戰(zhàn)范例l 某游戲數(shù)據(jù)庫使用了innodb，innodb是行級鎖，理論上很少存在鎖

8、表情況。出現(xiàn)了一個SQL語句(delete from tabname where xid=)，這個SQL非常用SQL，僅在特定情況下出現(xiàn)，每天出現(xiàn)頻繁度不高（一天僅10次左右），數(shù)據(jù)表容量百萬級，但是這個xid未建立索引，于是悲慘的事情發(fā)生了，當(dāng)執(zhí)行這條delete 的時候，真正刪除的記錄非常少，也許一到兩條，也許一條都沒有；但是！由于這個xid未建立索引，delete操作時遍歷全表記錄，全表被delete操作鎖定，select操作全部被locked，由于百萬條記錄遍歷時間較長，期間大量select被阻塞，數(shù)據(jù)庫連接過多崩潰。這種非高發(fā)請求，操作目標(biāo)很少的SQL，因未使用索引，連帶導(dǎo)致整個數(shù)據(jù)

9、庫的查詢阻塞，需要極大提高警覺。n 總結(jié)：u 影響結(jié)果集是搜索條件索引命中的結(jié)果集，而非輸出和操作的結(jié)果集。u 影響結(jié)果集越趨近于實際輸出或操作的目標(biāo)結(jié)果集，索引效率越高。u 請注意，我這里永遠不會講關(guān)于外鍵和join的優(yōu)化，因為在我們的體系里，這是根本不允許的！ 架構(gòu)優(yōu)化部分會解釋為什么。理解執(zhí)行狀態(tài)常見分析手段l 慢查詢?nèi)罩?，關(guān)注重點如下n 是否鎖定，及鎖定時間u 如存在鎖定，則該慢查詢通常是因鎖定因素導(dǎo)致，本身無需優(yōu)化，需解決鎖定問題。n 影響結(jié)果集u 如影響結(jié)果集較大，顯然是索引項命中存在問題，需要認真對待。l Explain 操作n 索引項使用u 不建議用using index做強制

10、索引，如未如預(yù)期使用索引，建議重新斟酌表結(jié)構(gòu)和索引設(shè)置。n 影響結(jié)果集u 這里顯示的數(shù)字不一定準(zhǔn)確，結(jié)合之前提到對數(shù)據(jù)索引的理解來看，還記得嘛？就把索引當(dāng)作有序序列來理解，反思SQL。l Set profiling , show profiles for query操作n 執(zhí)行開銷u 注意，有問題的SQL如果重復(fù)執(zhí)行，可能在緩存里，這時要注意避免緩存影響。通過這里可以看到。u 執(zhí)行時間超過0.005秒的頻繁操作SQL建議都分析一下。u 深入理解數(shù)據(jù)庫執(zhí)行的過程和開銷的分布l Show processlistn 狀態(tài)清單u Sleep 狀態(tài)， 通常代表資源未釋放，如果是通過連接池，sleep狀態(tài)

11、應(yīng)該恒定在一定數(shù)量范圍內(nèi)l 實戰(zhàn)范例： 因前端數(shù)據(jù)輸出時（特別是輸出到用戶終端）未及時關(guān)閉數(shù)據(jù)庫連接，導(dǎo)致因網(wǎng)絡(luò)連接速度產(chǎn)生大量sleep連接，在網(wǎng)速出現(xiàn)異常時，數(shù)據(jù)庫 too many connections 掛死。l 簡單解讀，數(shù)據(jù)查詢和執(zhí)行通常只需要不到0.01秒，而網(wǎng)絡(luò)輸出通常需要1秒左右甚至更長，原本數(shù)據(jù)連接在0.01秒即可釋放，但是因為前端程序未執(zhí)行close操作，直接輸出結(jié)果，那么在結(jié)果未展現(xiàn)在用戶桌面前，該數(shù)據(jù)庫連接一直維持在sleep狀態(tài)！u Waiting for net, reading from net, writing to netl 偶爾出現(xiàn)無妨l 如大量出現(xiàn)，迅速

12、檢查數(shù)據(jù)庫到前端的網(wǎng)絡(luò)連接狀態(tài)和流量l 案例: 因外掛程序，內(nèi)網(wǎng)數(shù)據(jù)庫大量讀取，內(nèi)網(wǎng)使用的百兆交換迅速爆滿，導(dǎo)致大量連接阻塞在waiting for net，數(shù)據(jù)庫連接過多崩潰u Locked狀態(tài)l 有更新操作鎖定l 通常使用innodb可以很好的減少locked狀態(tài)的產(chǎn)生，但是切記，更新操作要正確使用索引，即便是低頻次更新操作也不能疏忽。如上影響結(jié)果集范例所示。l 在myisam的時代，locked是很多高并發(fā)應(yīng)用的噩夢。所以mysql官方也開始傾向于推薦innodb。u Copy to tmp tablel 索引及現(xiàn)有結(jié)構(gòu)無法涵蓋查詢條件，才會建立一個臨時表來滿足查詢要求，產(chǎn)生巨大的恐怖的

13、i/o壓力。l 很可怕的搜索語句會導(dǎo)致這樣的情況，如果是數(shù)據(jù)分析，或者半夜的周期數(shù)據(jù)清理任務(wù)，偶爾出現(xiàn)，可以允許。頻繁出現(xiàn)務(wù)必優(yōu)化之。l Copy to tmp table 通常與連表查詢有關(guān)，建議逐漸習(xí)慣不使用連表查詢。l 實戰(zhàn)范例：n 某社區(qū)數(shù)據(jù)庫阻塞，求救，經(jīng)查，其服務(wù)器存在多個數(shù)據(jù)庫應(yīng)用和網(wǎng)站，其中一個不常用的小網(wǎng)站數(shù)據(jù)庫產(chǎn)生了一個恐怖的copy to tmp table 操作，導(dǎo)致整個硬盤i/o和cpu壓力超載。Kill掉該操作一切恢復(fù)。u Sending datal Sending data 并不是發(fā)送數(shù)據(jù)，別被這個名字所欺騙，這是從物理磁盤獲取數(shù)據(jù)的進程，如果你的影響結(jié)果集較多，

14、那么就需要從不同的磁盤碎片去抽取數(shù)據(jù)，l 偶爾出現(xiàn)該狀態(tài)連接無礙。l 回到上面影響結(jié)果集的問題，一般而言，如果sending data連接過多，通常是某查詢的影響結(jié)果集過大，也就是查詢的索引項不夠優(yōu)化。l 如果出現(xiàn)大量相似的SQL語句出現(xiàn)在show proesslist列表中，并且都處于sending data狀態(tài)，優(yōu)化查詢索引，記住用影響結(jié)果集的思路去思考。u Freeing itemsl 理論上這玩意不會出現(xiàn)很多。偶爾出現(xiàn)無礙l 如果大量出現(xiàn)，內(nèi)存，硬盤可能已經(jīng)出現(xiàn)問題。比如硬盤滿或損壞。u Sorting for l 和Sending data類似，結(jié)果集過大，排序條件沒有索引化，需要在

15、內(nèi)存里排序，甚至需要創(chuàng)建臨時結(jié)構(gòu)排序。u 其他l 還有很多狀態(tài)，遇到了，去查查資料。基本上我們遇到其他狀態(tài)的阻塞較少，所以不關(guān)心。分析流程l 基本流程n 詳細了解問題狀況u Too many connections 是常見表象，有很多種原因。u 索引損壞的情況在innodb情況下很少出現(xiàn)。u 如出現(xiàn)其他情況應(yīng)追溯日志和錯誤信息。n 了解基本負載狀況和運營狀況u 基本運營狀況l 當(dāng)前每秒讀請求l 當(dāng)前每秒寫請求l 當(dāng)前在線用戶l 當(dāng)前數(shù)據(jù)容量u 基本負載情況l 學(xué)會使用這些指令n Top n Vmstatn uptime n iostat n df l Cpu負載構(gòu)成n 特別關(guān)注i/o壓力( w

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

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

18、/o壓力不高，優(yōu)先分析讀取頻繁度l 程序中在封裝的db類增加抽樣日志即可，抽樣比例酌情考慮，以不顯著影響系統(tǒng)負載壓力為底線。l 最多讀取的數(shù)據(jù)表是哪個l 最多讀取的數(shù)據(jù)SQL是什么n 該SQL進行explain 和set profiling判定n 注意判定時需要避免query cache影響u 比如，在這個SQL末尾增加一個條件子句 and 1=1 就可以避免從query cache中獲取數(shù)據(jù)，而得到真實的執(zhí)行狀態(tài)分析。 l 是否存在同一個查詢短期內(nèi)頻繁出現(xiàn)的情況n 涉及前端緩存優(yōu)化n 抓大放小，解決顯著問題u 不苛求解決所有優(yōu)化問題，但是應(yīng)以保證線上服務(wù)穩(wěn)定可靠為目標(biāo)。u 解決與評估要同時進

19、行，新的策略或解決方案務(wù)必經(jīng)過評估后上線。總結(jié)l 要學(xué)會怎樣分析問題，而不是單純拍腦袋優(yōu)化l 慢查詢只是最基礎(chǔ)的東西，要學(xué)會優(yōu)化0.01秒的查詢請求。l 當(dāng)發(fā)生連接阻塞時，不同狀態(tài)的阻塞有不同的原因，要找到原因，如果不對癥下藥，就會南轅北轍n 范例：如果本身系統(tǒng)內(nèi)存已經(jīng)超載，已經(jīng)使用到了swap，而還在考慮加大緩存來優(yōu)化查詢，那就是自尋死路了。l 監(jiān)測與跟蹤要經(jīng)常做，而不是出問題才做n 讀取頻繁度抽樣監(jiān)測u 全監(jiān)測不要搞，i/o嚇?biāo)廊?。u 按照一個抽樣比例抽樣即可。u 針對抽樣中發(fā)現(xiàn)的問題，可以按照特定SQL在特定時間內(nèi)監(jiān)測一段全查詢記錄，但仍要考慮i/o影響。n 寫入頻繁度監(jiān)測u 基于bin

20、log解開即可，可定時或不定時分析。n 微慢查詢抽樣監(jiān)測u 高并發(fā)情況下，查詢請求時間超過0.01秒甚至0.005秒的，建議酌情抽樣記錄。n 連接數(shù)預(yù)警監(jiān)測u 連接數(shù)超過特定閾值的情況下，雖然數(shù)據(jù)庫沒有崩潰，建議記錄相關(guān)連接狀態(tài)。l 學(xué)會通過數(shù)據(jù)和監(jiān)控發(fā)現(xiàn)問題，分析問題，而后解決問題順理成章。特別是要學(xué)會在日常監(jiān)控中發(fā)現(xiàn)隱患，而不是問題爆發(fā)了才去處理和解決。Mysql 運維優(yōu)化存儲引擎類型l Myisam 速度快，響應(yīng)快。表級鎖是致命問題。l Innodb 目前主流存儲引擎n 行級鎖 u 務(wù)必注意影響結(jié)果集的定義是什么u 行級鎖會帶來更新的額外開銷，但是通常情況下是值得的。n 事務(wù)提交 u 對

21、i/o效率提升的考慮u 對安全性的考慮l HEAP 內(nèi)存引擎n 頻繁更新和海量讀取情況下仍會存在鎖定狀況內(nèi)存使用考量l 理論上，內(nèi)存越大，越多數(shù)據(jù)讀取發(fā)生在內(nèi)存，效率越高l 要考慮到現(xiàn)實的硬件資源和瓶頸分布l 學(xué)會理解熱點數(shù)據(jù)，并將熱點數(shù)據(jù)盡可能內(nèi)存化n 所謂熱點數(shù)據(jù)，就是最多被訪問的數(shù)據(jù)。n 通常數(shù)據(jù)庫訪問是不平均的，少數(shù)數(shù)據(jù)被頻繁讀寫，而更多數(shù)據(jù)鮮有讀寫。n 學(xué)會制定不同的熱點數(shù)據(jù)規(guī)則，并測算指標(biāo)。u 熱點數(shù)據(jù)規(guī)模，理論上，熱點數(shù)據(jù)越少越好，這樣可以更好的滿足業(yè)務(wù)的增長趨勢。u 響應(yīng)滿足度，對響應(yīng)的滿足率越高越好。u 比如依據(jù)最后更新時間，總訪問量，回訪次數(shù)等指標(biāo)定義熱點數(shù)據(jù)，并測算不同定

22、義模式下的熱點數(shù)據(jù)規(guī)模性能與安全性考量l 數(shù)據(jù)提交方式n innodb_flush_log_at_trx_commit = 1 每次自動提交，安全性高，i/o壓力大n innodb_flush_log_at_trx_commit = 2 每秒自動提交，安全性略有影響，i/o承載強。l 日志同步n Sync-binlog=1 每條自動更新，安全性高，i/o壓力大n Sync-binlog = 0 根據(jù)緩存設(shè)置情況自動更新，存在丟失數(shù)據(jù)和同步延遲風(fēng)險，i/o承載力強。l 性能與安全本身存在相悖的情況，需要在業(yè)務(wù)訴求層面決定取舍n 學(xué)會區(qū)分什么場合側(cè)重性能，什么場合側(cè)重安全n 學(xué)會將不同安全等級的數(shù)

23、據(jù)庫用不同策略管理存儲壓力優(yōu)化l 順序讀寫性能遠高于隨機讀寫l 日志類數(shù)據(jù)可以使用順序讀寫方式進行l(wèi) 將順序?qū)憯?shù)據(jù)和隨機讀寫數(shù)據(jù)分成不同的物理磁盤，有助于i/o壓力的疏解，前提是，你確信你的i/o壓力主要來自于可順序?qū)懖僮鳎ㄒ螂S機讀寫干擾導(dǎo)致不能順序?qū)?，但是確實可以用順序?qū)懛绞竭M行的i/o操作）。運維監(jiān)控體系l 系統(tǒng)監(jiān)控n 服務(wù)器資源監(jiān)控u Cpu, 內(nèi)存，硬盤空間，i/o壓力u 設(shè)置閾值報警n 服務(wù)器流量監(jiān)控u 外網(wǎng)流量，內(nèi)網(wǎng)流量u 設(shè)置閾值報警n 連接狀態(tài)監(jiān)控u Show processlist 設(shè)置閾值，每分鐘監(jiān)測，超過閾值記錄l 應(yīng)用監(jiān)控n 慢查詢監(jiān)控u 慢查詢?nèi)罩緐 如果存在多臺數(shù)據(jù)

24、庫服務(wù)器，應(yīng)有匯總查閱機制。n 請求錯誤監(jiān)控u 高頻繁應(yīng)用中，會出現(xiàn)偶發(fā)性數(shù)據(jù)庫連接錯誤或執(zhí)行錯誤，將錯誤信息記錄到日志，查看每日的比例變化。u 偶發(fā)性錯誤，如果數(shù)量極少，可以不用處理，但是需時常監(jiān)控其趨勢。u 會存在惡意輸入內(nèi)容，輸入邊界限定缺乏導(dǎo)致執(zhí)行出錯，需基于此防止惡意入侵探測行為。n 微慢查詢監(jiān)控u 高并發(fā)環(huán)境里，超過0.01秒的查詢請求都應(yīng)該關(guān)注一下。n 頻繁度監(jiān)控u 寫操作，基于binlog，定期分析。u 讀操作，在前端db封裝代碼中增加抽樣日志，并輸出執(zhí)行時間。u 分析請求頻繁度是開發(fā)架構(gòu) 進一步優(yōu)化的基礎(chǔ)u 最好的優(yōu)化就是減少請求次數(shù)！l 總結(jié)：n 監(jiān)控與數(shù)據(jù)分析是一切優(yōu)化的

25、基礎(chǔ)。n 沒有運營數(shù)據(jù)監(jiān)測就不要妄談優(yōu)化！n 監(jiān)控要注意不要產(chǎn)生太多額外的負載，不要因監(jiān)控帶來太多額外系統(tǒng)開銷Mysql 架構(gòu)優(yōu)化架構(gòu)優(yōu)化目標(biāo)防止單點隱患l 所謂單點隱患，就是某臺設(shè)備出現(xiàn)故障，會導(dǎo)致整體系統(tǒng)的不可用，這個設(shè)備就是單點隱患。l 理解連帶效應(yīng)，所謂連帶效應(yīng)，就是一種問題會引發(fā)另一種故障，舉例而言，memcache+mysql是一種常見緩存組合，在前端壓力很大時，如果memcache崩潰，理論上數(shù)據(jù)會通過mysql讀取，不存在系統(tǒng)不可用情況，但是mysql無法對抗如此大的壓力沖擊，會因此連帶崩潰。因A系統(tǒng)問題導(dǎo)致B系統(tǒng)崩潰的連帶問題，在運維過程中會頻繁出現(xiàn)。n 實戰(zhàn)范例： 在mys

26、ql連接不及時釋放的應(yīng)用環(huán)境里，當(dāng)網(wǎng)絡(luò)環(huán)境異常（同機房友鄰服務(wù)器遭受拒絕服務(wù)攻擊，出口阻塞），網(wǎng)絡(luò)延遲加劇，空連接數(shù)急劇增加，導(dǎo)致數(shù)據(jù)庫連接過多崩潰。n 實戰(zhàn)范例2：前端代碼 通常我們封裝 mysql_connect和memcache_connect，二者的順序不同，會產(chǎn)生不同的連帶效應(yīng)。如果mysql_connect在前，那么一旦memcache連接阻塞，會連帶mysql空連接過多崩潰。n 連帶效應(yīng)是常見的系統(tǒng)崩潰，日常分析崩潰原因的時候需要認真考慮連帶效應(yīng)的影響，頭疼醫(yī)頭，腳疼醫(yī)腳是不行的。方便系統(tǒng)擴容l 數(shù)據(jù)容量增加后，要考慮能夠?qū)?shù)據(jù)分布到不同的服務(wù)器上。l 請求壓力增加時，要考慮將請

27、求壓力分布到不同服務(wù)器上。l 擴容設(shè)計時需要考慮防止單點隱患。安全可控，成本可控l 數(shù)據(jù)安全，業(yè)務(wù)安全l 人力資源成本帶寬流量成本硬件成本n 成本與流量的關(guān)系曲線應(yīng)低于線性增長（流量為橫軸，成本為縱軸）。n 規(guī)模優(yōu)勢l 本教程僅就與數(shù)據(jù)庫有關(guān)部分討論，與數(shù)據(jù)庫無關(guān)部門請自行參閱其他學(xué)習(xí)資料。分布式方案分庫&拆表方案l 基本認識n 用分庫&拆表是解決數(shù)據(jù)庫容量問題的唯一途徑。n 分庫&拆表也是解決性能壓力的最優(yōu)選擇。n 分庫 不同的數(shù)據(jù)表放到不同的數(shù)據(jù)庫服務(wù)器中（也可能是虛擬服務(wù)器）n 拆表 一張數(shù)據(jù)表拆成多張數(shù)據(jù)表，可能位于同一臺服務(wù)器，也可能位于多臺服務(wù)器（含虛擬服務(wù)器）。l 去關(guān)聯(lián)化原則n

28、 摘除數(shù)據(jù)表之間的關(guān)聯(lián)，是分庫的基礎(chǔ)工作。n 摘除關(guān)聯(lián)的目的是，當(dāng)數(shù)據(jù)表分布到不同服務(wù)器時，查詢請求容易分發(fā)和處理。n 學(xué)會理解反范式數(shù)據(jù)結(jié)構(gòu)設(shè)計，所謂反范式，第一要點是不用外鍵，不允許Join操作，不允許任何需要跨越兩個表的查詢請求。第二要點是適度冗余減少查詢請求，比如說，信息表，fromuid, touid, message字段外，還需要一個fromuname字段記錄用戶名，這樣查詢者通過touid查詢后，能夠立即得到發(fā)信人的用戶名，而無需進行另一個數(shù)據(jù)表的查詢。n 去關(guān)聯(lián)化處理會帶來額外的考慮，比如說，某一個數(shù)據(jù)表內(nèi)容的修改，對另一個數(shù)據(jù)表的影響。這一點需要在程序或其他途徑去考慮。l 分

29、庫方案n 安全性拆分u 將高安全性數(shù)據(jù)與低安全性數(shù)據(jù)分庫，這樣的好處第一是便于維護，第二是高安全性數(shù)據(jù)的數(shù)據(jù)庫參數(shù)配置可以以安全優(yōu)先，而低安全性數(shù)據(jù)的參數(shù)配置以性能優(yōu)先。參見運維優(yōu)化相關(guān)部分。n 順序?qū)憯?shù)據(jù)與隨機讀寫數(shù)據(jù)分庫u 順序數(shù)據(jù)與隨機數(shù)據(jù)區(qū)分存儲地址，保證物理i/o優(yōu)化。這個實話說，我只聽說了概念，還沒學(xué)會怎么實踐。n 基于業(yè)務(wù)邏輯拆分u 根據(jù)數(shù)據(jù)表的內(nèi)容構(gòu)成，業(yè)務(wù)邏輯拆分，便于日常維護和前端調(diào)用。u 基于業(yè)務(wù)邏輯拆分，可以減少前端應(yīng)用請求發(fā)送到不同數(shù)據(jù)庫服務(wù)器的頻次，從而減少鏈接開銷。u 基于業(yè)務(wù)邏輯拆分，可保留部分數(shù)據(jù)關(guān)聯(lián)，前端web工程師可在限度范圍內(nèi)執(zhí)行關(guān)聯(lián)查詢。n 基于負載壓

30、力拆分u 基于負載壓力對數(shù)據(jù)結(jié)構(gòu)拆分，便于直接將負載分擔(dān)給不同的服務(wù)器。u 基于負載壓力拆分，可能拆分后的數(shù)據(jù)庫包含不同業(yè)務(wù)類型的數(shù)據(jù)表，日常維護會有一定的煩惱。l 分表方案n 數(shù)據(jù)量過大或者訪問壓力過大的數(shù)據(jù)表需要切分n 忙閑分表u 單數(shù)據(jù)表字段過多，可將頻繁更新的整數(shù)數(shù)據(jù)與非頻繁更新的字符串?dāng)?shù)據(jù)切分u 范例 user表 ，個人簡介，地址，QQ號，聯(lián)系方式，頭像 這些字段為字符串類型，更新請求少； 最后登錄時間，在線時常，訪問次數(shù)，信件數(shù)這些字段為整數(shù)型字段，更新頻繁，可以將后面這些更新頻繁的字段獨立拆出一張數(shù)據(jù)表，表內(nèi)容變少，索引結(jié)構(gòu)變少，讀寫請求變快。n 橫向切表u 等分切表，如哈希切表

31、或其他基于對某數(shù)字取余的切表。等分切表的優(yōu)點是負載很方便的分布到不同服務(wù)器；缺點是當(dāng)容量繼續(xù)增加時無法方便的擴容，需要重新進行數(shù)據(jù)的切分或轉(zhuǎn)表。而且一些關(guān)鍵主鍵不易處理。u 遞增切表，比如每1kw用戶開一個新表，優(yōu)點是可以適應(yīng)數(shù)據(jù)的自增趨勢；缺點是往往新數(shù)據(jù)負載高，壓力分配不平均。u 日期切表，適用于日志記錄式數(shù)據(jù)，優(yōu)缺點等同于遞增切表。u 個人傾向于遞增切表，具體根據(jù)應(yīng)用場景決定。n 熱點數(shù)據(jù)分表u 將數(shù)據(jù)量較大的數(shù)據(jù)表中將讀寫頻繁的數(shù)據(jù)抽取出來，形成熱點數(shù)據(jù)表。通常一個龐大數(shù)據(jù)表經(jīng)常被讀寫的內(nèi)容往往具有一定的集中性，如果這些集中數(shù)據(jù)單獨處理，就會極大減少整體系統(tǒng)的負載。u 熱點數(shù)據(jù)表與舊有

32、數(shù)據(jù)關(guān)系l 可以是一張冗余表，即該表數(shù)據(jù)丟失不會妨礙使用，因源數(shù)據(jù)仍存在于舊有結(jié)構(gòu)中。優(yōu)點是安全性高，維護方便，缺點是寫壓力不能分擔(dān)，仍需要同步寫回原系統(tǒng)。l 可以是非冗余表，即熱點數(shù)據(jù)的內(nèi)容原有結(jié)構(gòu)不再保存，優(yōu)點是讀寫效率全部優(yōu)化；缺點是當(dāng)熱點數(shù)據(jù)發(fā)生變化時，維護量較大。l 具體方案選擇需要根據(jù)讀寫比例決定，在讀頻率遠高于寫頻率情況下，優(yōu)先考慮冗余表方案。u 熱點數(shù)據(jù)表可以用單獨的優(yōu)化的硬件存儲，比如昂貴的閃存卡或大內(nèi)存系統(tǒng)。u 熱點數(shù)據(jù)表的重要指標(biāo)l 熱點數(shù)據(jù)的定義需要根據(jù)業(yè)務(wù)模式自行制定策略，常見策略為，按照最新的操作時間；按照內(nèi)容豐富度等等。l 數(shù)據(jù)規(guī)模，比如從1000萬條數(shù)據(jù)，抽取出

33、100萬條熱點數(shù)據(jù)。l 熱點命中率，比如查詢10次，多少次命中在熱點數(shù)據(jù)內(nèi)。l 理論上，數(shù)據(jù)規(guī)模越小，熱點命中率越高，說明效果越好。需要根據(jù)業(yè)務(wù)自行評估。u 熱點數(shù)據(jù)表的動態(tài)維護l 加載熱點數(shù)據(jù)方案選擇n 定時從舊有數(shù)據(jù)結(jié)構(gòu)中按照新的策略獲取n 在從舊有數(shù)據(jù)結(jié)構(gòu)讀取時動態(tài)加載到熱點數(shù)據(jù)l 剔除熱點數(shù)據(jù)方案選擇n 基于特定策略，定時將熱點數(shù)據(jù)中訪問頻次較少的數(shù)據(jù)剔除n 如熱點數(shù)據(jù)是冗余表，則直接刪除即可，如不是冗余表，需要回寫給舊有數(shù)據(jù)結(jié)構(gòu)。u 通常，熱點數(shù)據(jù)往往是基于緩存或者key-value方案冗余存儲，所以這里提到的熱點數(shù)據(jù)表，其實更多是理解思路，用到的場合可能并不多.l 表結(jié)構(gòu)設(shè)計n 查

34、詢?nèi)哂啾碓O(shè)計u 涉及分表操作后，一些常見的索引查詢可能需要跨表，帶來不必要的麻煩。確認查詢請求遠大于寫入請求時，應(yīng)設(shè)置便于查詢項的冗余表。u 實戰(zhàn)范例，l 用戶分表，將用戶庫分成若干數(shù)據(jù)表l 基于用戶名的查詢和基于uid的查詢都是高并發(fā)請求。l 用戶分表基于uid分成數(shù)據(jù)表，同時基于用戶名做對應(yīng)冗余表。u 冗余表要點l 數(shù)據(jù)一致性，簡單說，同增，同刪，同更新。l 可以做全冗余，或者只做主鍵關(guān)聯(lián)的冗余，比如通過用戶名查詢uid，再基于uid查詢源表。n 中間數(shù)據(jù)表u 為了減少會涉及大規(guī)模影響結(jié)果集的表數(shù)據(jù)操作，比如count，sum操作。應(yīng)將一些統(tǒng)計類數(shù)據(jù)通過中間數(shù)據(jù)表保存。u 中間數(shù)據(jù)表應(yīng)能通

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

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

37、出現(xiàn)故障，系統(tǒng)是否會出現(xiàn)異常？用戶訪問是否會出現(xiàn)異常。n 目標(biāo)：任意一臺服務(wù)器崩潰，負載和數(shù)據(jù)操作均會很短時間內(nèi)自動轉(zhuǎn)移到其他服務(wù)器，不會影響業(yè)務(wù)的正常進行。不會造成惡性的數(shù)據(jù)丟失。（哪些是可以丟失的，哪些是不能丟失的）緩存方案緩存結(jié)合數(shù)據(jù)庫的讀取l Memcached是最常用的緩存系統(tǒng)l Mysql 最新版本已經(jīng)開始支持memcache插件，但據(jù)牛人分析，尚不成熟，暫不推薦。l 數(shù)據(jù)讀取n 并不是所有數(shù)據(jù)都適合被緩存，也并不是進入了緩存就意味著效率提升。n 命中率是第一要評估的數(shù)據(jù)。n 如何評估進入緩存的數(shù)據(jù)規(guī)模，以及命中率優(yōu)化，是非常需要細心分析的。l 實景分析： 前端請求先連接緩存，緩存

38、未命中連接數(shù)據(jù)庫，進行查詢，未命中狀態(tài)比單純連接數(shù)據(jù)庫查詢多了一次連接和查詢的操作；如果緩存命中率很低，則這個額外的操作非但不能提高查詢效率，反而為系統(tǒng)帶來了額外的負載和復(fù)雜性，得不償失。n 相關(guān)評估類似于熱點數(shù)據(jù)表的介紹。n 善于利用內(nèi)存，請注意數(shù)據(jù)存儲的格式及壓縮算法。l Key-value 方案繁多，本培訓(xùn)文檔暫不展開。緩存結(jié)合數(shù)據(jù)庫的寫入l 利用緩存不但可以減少數(shù)據(jù)讀取請求，還可以減少數(shù)據(jù)庫寫入i/o壓力l 緩存實時更新，數(shù)據(jù)庫異步更新n 緩存實時更新數(shù)據(jù)，并將更新記錄寫入隊列n 可以使用類似mq的隊列產(chǎn)品，自行建立隊列請注意使用increment來維持隊列序號。n 不建議使用 get

39、 后處理數(shù)據(jù)再set的方式維護隊列l(wèi) 測試范例：l 范例1 $var=Memcache_get($memcon,”var”); $var+;memcache_set($memcon,”var”,$var);這樣一個腳本，使用apache ab去跑，100個并發(fā)，跑10000次，然后輸出緩存存取的數(shù)據(jù)，很遺憾，并不是1000，而是5000多，6000多這樣的數(shù)字，中間的數(shù)字全在 get & set的過程中丟掉了。原因，讀寫間隔中其他并發(fā)寫入，導(dǎo)致數(shù)據(jù)丟失。l 范例2用memcache_increment來做這個操作，同樣跑測試會得到完整的10000，一條數(shù)據(jù)不會丟。l 結(jié)論： 用incremen

40、t存儲隊列編號，用標(biāo)記+編號作為key存儲隊列內(nèi)容。n 后臺基于緩存隊列讀取更新數(shù)據(jù)并更新數(shù)據(jù)庫l 基于隊列讀取后可以合并更新l 更新合并率是重要指標(biāo)l 實戰(zhàn)范例：某論壇熱門貼，前端不斷有views=views+1數(shù)據(jù)更新請求。緩存實時更新該狀態(tài)后臺任務(wù)對數(shù)據(jù)庫做異步更新時，假設(shè)執(zhí)行周期是5分鐘，那么五分鐘可能會接收到這樣的請求多達數(shù)十次乃至數(shù)百次，合并更新后只執(zhí)行一次update即可。類似操作還包括游戲打怪，生命和經(jīng)驗的變化；個人主頁訪問次數(shù)的變化等。n 異步更新風(fēng)險l 前后端同時寫，可能導(dǎo)致覆蓋風(fēng)險。l 使用后端異步更新，則前端應(yīng)用程序就不要寫數(shù)據(jù)庫，否則可能造成寫入沖突。一種兼容的解決方

41、案是，前端和后端不要寫相同的字段。l 實戰(zhàn)范例：用戶在線上時，后臺異步更新用戶狀態(tài)。管理員后臺屏蔽用戶是直接更新數(shù)據(jù)庫。結(jié)果管理員屏蔽某用戶操作完成后，因該用戶在線有操作，后臺異步更新程序再次基于緩存更新用戶狀態(tài)，用戶狀態(tài)被復(fù)活，屏蔽失效。l 緩存數(shù)據(jù)丟失或服務(wù)崩潰可能導(dǎo)致數(shù)據(jù)丟失風(fēng)險。l 如緩存中間出現(xiàn)故障，則緩存隊列數(shù)據(jù)不會回寫到數(shù)據(jù)庫，而用戶會認為已經(jīng)完成，此時會帶來比較明顯的用戶體驗問題。l 一個不徹底的解決方案是，確保高安全性，高重要性數(shù)據(jù)實時數(shù)據(jù)更新，而低安全性數(shù)據(jù)通過緩存異步回寫方式完成。此外，使用相對數(shù)值操作而不是絕對數(shù)值操作更安全。n 范例：支付信息，道具的購買與獲得，一旦丟失會對用戶造成極大的傷害。而經(jīng)驗值，訪問數(shù)字，如果只丟失了很少時間的內(nèi)容，用戶還是可以容忍的。n 范例：如果使用 Views=Views+的操作，一旦出現(xiàn)數(shù)據(jù)格式錯誤，從binlog中反推是可以進行數(shù)據(jù)還原，但是如果使用Views=特定值的操作，一旦緩存中數(shù)據(jù)有錯誤，則直接被賦予了一個錯誤數(shù)據(jù)，無法回溯！l 異步更新如出現(xiàn)隊列阻塞可能導(dǎo)致數(shù)據(jù)丟失風(fēng)險。l 異步更新通常是使用緩存隊列后，在后臺由cron或其他守護進程寫入