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

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

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

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

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

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

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

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

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

9、說， 0.01 秒以上的查詢， 都是不夠優(yōu)化的。實(shí)戰(zhàn)范例和上案例類似，某游戲社區(qū)要顯示用戶動態(tài)，select * from userfeed whereuid=$uid order by lastlogin desc limit 0,30;初期默認(rèn)以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 秒

10、，數(shù)據(jù)庫壓力驟降。影響結(jié)果集的常見誤區(qū)影響結(jié)果集并不是說數(shù)據(jù)查詢出來的結(jié)果數(shù)或操作影響的結(jié)果數(shù)，而是查詢條件的索引所命中的結(jié)果數(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ā)生了，當(dāng)執(zhí)行這條delete 的時候，真正刪除的記錄非常少，也許一到兩條，也許一條都沒有；但是！由于這個xid 未建立索引， delete

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

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

13、都分析一下。深入理解數(shù)據(jù)庫執(zhí)行的過程和開銷的分布Show processlist狀態(tài)清單Sleep 狀態(tài)，通常代表資源未釋放，如果是通過連接池，sleep 狀態(tài)應(yīng)該恒定在一定數(shù)量范圍內(nèi)實(shí)戰(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掛死。簡單解讀，數(shù)據(jù)查詢和執(zhí)行通常只需要不到0.01 秒，而網(wǎng)絡(luò)輸出通常需要1 秒左右甚至更長，原本數(shù)據(jù)連接在0.01 秒即可釋放，但是因?yàn)榍岸顺绦蛭磮?zhí)行close 操作，直接輸出結(jié)果，那么在結(jié)果未展現(xiàn)在用戶桌面前，該數(shù)據(jù)庫連接一直維持在s

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

15、推薦innodb 。Copy to tmp table索引及現(xiàn)有結(jié)構(gòu)無法涵蓋查詢條件，才會建立一個臨時表來滿足查詢要求，產(chǎn)生巨大的恐怖的i/o 壓力。很可怕的搜索語句會導(dǎo)致這樣的情況，如果是數(shù)據(jù)分析， 或者半夜的周期數(shù)據(jù)清理任務(wù)，偶爾出現(xiàn)，可以允許。頻繁出現(xiàn)務(wù)必優(yōu)化之。Copy to tmp table通常與連表查詢有關(guān)，建議逐漸習(xí)慣不使用連表查詢。實(shí)戰(zhà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ù)。Sending

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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