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

Mysql 性能優(yōu)化方案及技術(shù)目錄目錄1背景及目標(biāo)2Mysql 執(zhí)行優(yōu)化2認(rèn)識(shí)數(shù)據(jù)索引2為什么使用數(shù)據(jù)索引能提高效率2如何理解數(shù)據(jù)索引的結(jié)構(gòu)2如何理解影響結(jié)果集3理解執(zhí)行狀態(tài)4常見(jiàn)分析手段4分析流程6總結(jié)7Mysql 運(yùn)維優(yōu)化9存儲(chǔ)引擎類(lèi)型9內(nèi)存使用考量9性能與安全性考量9存儲(chǔ)壓力優(yōu)化10運(yùn)維監(jiān)控體系10Mysql 架構(gòu)優(yōu)化11架構(gòu)優(yōu)化目標(biāo)11防止單點(diǎn)隱患11方便系統(tǒng)擴(kuò)容11安全可控，成本可控11分布式方案12分庫(kù)&拆表方案12主從架構(gòu)14故障轉(zhuǎn)移處理15緩存方案15緩存結(jié)合數(shù)據(jù)庫(kù)的讀取15緩存結(jié)合數(shù)據(jù)庫(kù)的寫(xiě)入15背景及目標(biāo)l 廈門(mén)游家公司（4399.com）用于員工培訓(xùn)和分享。l 針對(duì)用戶(hù)群為已經(jīng)使用過(guò)mysql環(huán)境，并有一定開(kāi)發(fā)經(jīng)驗(yàn)的工程師l 針對(duì)高并發(fā)，海量數(shù)據(jù)的互聯(lián)網(wǎng)環(huán)境。l 本文語(yǔ)言為口語(yǔ)，非學(xué)術(shù)標(biāo)準(zhǔn)用語(yǔ)。l 以實(shí)戰(zhàn)和解決具體問(wèn)題為主要目標(biāo)，非應(yīng)試，非常規(guī)教育。友情提醒，在校生學(xué)習(xí)本教程可能對(duì)成績(jī)提高有害無(wú)益。l 非技術(shù)挑戰(zhàn)，非高端架構(gòu)師培訓(xùn)，請(qǐng)高手自動(dòng)忽略。Mysql 執(zhí)行優(yōu)化認(rèn)識(shí)數(shù)據(jù)索引為什么使用數(shù)據(jù)索引能提高效率n 數(shù)據(jù)索引的存儲(chǔ)是有序的n 在有序的情況下，通過(guò)索引查詢(xún)一個(gè)數(shù)據(jù)是無(wú)需遍歷索引記錄的n 極端情況下，數(shù)據(jù)索引的查詢(xún)效率為二分法查詢(xún)效率，趨近于 log2(N)如何理解數(shù)據(jù)索引的結(jié)構(gòu)n 數(shù)據(jù)索引通常默認(rèn)采用btree索引，（內(nèi)存表也使用了hash索引）。n 單一有序排序序列是查找效率最高的（二分查找，或者說(shuō)折半查找），使用樹(shù)形索引的目的是為了達(dá)到快速的更新和增刪操作。n 在極端情況下（比如數(shù)據(jù)查詢(xún)需求量非常大，而數(shù)據(jù)更新需求極少，實(shí)時(shí)性要求不高，數(shù)據(jù)規(guī)模有限），直接使用單一排序序列，折半查找速度最快。u 實(shí)戰(zhàn)范例 ： ip地址反查資源： Ip地址對(duì)應(yīng)表，源數(shù)據(jù)格式為 startip, endip, area 源數(shù)據(jù)條數(shù)為 10萬(wàn)條左右，呈很大的分散性目標(biāo)：需要通過(guò)任意ip查詢(xún)?cè)搃p所屬地區(qū)性能要求達(dá)到每秒1000次以上的查詢(xún)效率挑戰(zhàn)：如使用 between and 數(shù)據(jù)庫(kù)操作，無(wú)法有效使用索引。如果每次查詢(xún)請(qǐng)求需要遍歷10萬(wàn)條記錄，根本不行。方法：一次性排序（只在數(shù)據(jù)準(zhǔn)備中進(jìn)行，數(shù)據(jù)可存儲(chǔ)在內(nèi)存序列）折半查找（每次請(qǐng)求以折半查找方式進(jìn)行）n 在進(jìn)行索引分析和SQL優(yōu)化時(shí)，可以將數(shù)據(jù)索引字段想象為單一有序序列，并以此作為分析的基礎(chǔ)。u 實(shí)戰(zhàn)范例：復(fù)合索引查詢(xún)優(yōu)化實(shí)戰(zhàn)，同城異性列表資源： 用戶(hù)表user，字段 sex性別；area 地區(qū)；lastlogin 最后登錄時(shí)間；其他略目標(biāo)： 查找同一地區(qū)的異性，按照最后登錄時(shí)間逆序 高訪(fǎng)問(wèn)量社區(qū)的高頻查詢(xún)，如何優(yōu)化。查詢(xún)SQL: select * from user where area=$area and sex=$sex order by lastlogin desc limit 0,30;挑戰(zhàn)：建立復(fù)合索引并不難， area+sex+lastlogin 三個(gè)字段的復(fù)合索引,如何理解？首先，忘掉btree，將索引字段理解為一個(gè)排序序列。如果只使用area會(huì)怎樣？搜索會(huì)把符合area的結(jié)果全部找出來(lái)，然后在這里面遍歷，選擇命中sex的并排序。 遍歷所有 area=$area數(shù)據(jù)！如果使用了area+sex，略好，仍然要遍歷所有area=$area and sex=$sex數(shù)據(jù)，然后在這個(gè)基礎(chǔ)上排序！Area+sex+lastlogin復(fù)合索引時(shí)（切記lastlogin在最后），該索引基于area+sex+lastlogin 三個(gè)字段合并的結(jié)果排序，該列表可以想象如下。廣州女$時(shí)間1廣州女$時(shí)間2廣州女$時(shí)間3廣州男.深圳女.數(shù)據(jù)庫(kù)很容易命中到 area+sex的邊界，并且基于下邊界向上追溯30條記錄，搞定！在索引中迅速命中所有結(jié)果，無(wú)需二次遍歷！如何理解影響結(jié)果集n 影響結(jié)果集是數(shù)據(jù)查詢(xún)優(yōu)化的一個(gè)重要中間數(shù)據(jù)u 查詢(xún)條件與索引的關(guān)系決定影響結(jié)果集如上例所示，即便查詢(xún)用到了索引，但是如果查詢(xún)和排序目標(biāo)不能直接在索引中命中，其可能帶來(lái)較多的影響結(jié)果。而這會(huì)直接影響到查詢(xún)效率u 微秒級(jí)優(yōu)化l 優(yōu)化查詢(xún)不能只看慢查詢(xún)?nèi)罩?，常?guī)來(lái)說(shuō)，0.01秒以上的查詢(xún)，都是不夠優(yōu)化的。l 實(shí)戰(zhàn)范例和上案例類(lèi)似，某游戲社區(qū)要顯示用戶(hù)動(dòng)態(tài)，select * from userfeed where uid=$uid order by lastlogin desc limit 0,30; 初期默認(rèn)以u(píng)id為索引字段， 查詢(xún)?yōu)槊兴衭id=$uid的結(jié)果按照l(shuí)astlogin排序。 當(dāng)用戶(hù)行為非常頻繁時(shí)，該SQL索引命中影響結(jié)果集有數(shù)百乃至數(shù)千條記錄。查詢(xún)效率超過(guò)0.01秒，并發(fā)較大時(shí)數(shù)據(jù)庫(kù)壓力較大。 解決方案：將索引改為 uid+lastlogin 復(fù)合索引，索引直接命中影響結(jié)果集30條，查詢(xún)效率提高了10倍，平均在0.001秒，數(shù)據(jù)庫(kù)壓力驟降。n 影響結(jié)果集的常見(jiàn)誤區(qū)u 影響結(jié)果集并不是說(shuō)數(shù)據(jù)查詢(xún)出來(lái)的結(jié)果數(shù)或操作影響的結(jié)果數(shù)，而是查詢(xún)條件的索引所命中的結(jié)果數(shù)。u 實(shí)戰(zhàn)范例l 某游戲數(shù)據(jù)庫(kù)使用了innodb，innodb是行級(jí)鎖，理論上很少存在鎖表情況。出現(xiàn)了一個(gè)SQL語(yǔ)句(delete from tabname where xid=)，這個(gè)SQL非常用SQL，僅在特定情況下出現(xiàn)，每天出現(xiàn)頻繁度不高（一天僅10次左右），數(shù)據(jù)表容量百萬(wàn)級(jí)，但是這個(gè)xid未建立索引，于是悲慘的事情發(fā)生了，當(dāng)執(zhí)行這條delete 的時(shí)候，真正刪除的記錄非常少，也許一到兩條，也許一條都沒(méi)有；但是！由于這個(gè)xid未建立索引，delete操作時(shí)遍歷全表記錄，全表被delete操作鎖定，select操作全部被locked，由于百萬(wàn)條記錄遍歷時(shí)間較長(zhǎng)，期間大量select被阻塞，數(shù)據(jù)庫(kù)連接過(guò)多崩潰。這種非高發(fā)請(qǐng)求，操作目標(biāo)很少的SQL，因未使用索引，連帶導(dǎo)致整個(gè)數(shù)據(jù)庫(kù)的查詢(xún)阻塞，需要極大提高警覺(jué)。n 總結(jié)：u 影響結(jié)果集是搜索條件索引命中的結(jié)果集，而非輸出和操作的結(jié)果集。u 影響結(jié)果集越趨近于實(shí)際輸出或操作的目標(biāo)結(jié)果集，索引效率越高。u 請(qǐng)注意，我這里永遠(yuǎn)不會(huì)講關(guān)于外鍵和join的優(yōu)化，因?yàn)樵谖覀兊捏w系里，這是根本不允許的！ 架構(gòu)優(yōu)化部分會(huì)解釋為什么。理解執(zhí)行狀態(tài)常見(jiàn)分析手段l 慢查詢(xún)?nèi)罩荆P(guān)注重點(diǎn)如下n 是否鎖定，及鎖定時(shí)間u 如存在鎖定，則該慢查詢(xún)通常是因鎖定因素導(dǎo)致，本身無(wú)需優(yōu)化，需解決鎖定問(wèn)題。n 影響結(jié)果集u 如影響結(jié)果集較大，顯然是索引項(xiàng)命中存在問(wèn)題，需要認(rèn)真對(duì)待。l Explain 操作n 索引項(xiàng)使用u 不建議用using index做強(qiáng)制索引，如未如預(yù)期使用索引，建議重新斟酌表結(jié)構(gòu)和索引設(shè)置。n 影響結(jié)果集u 這里顯示的數(shù)字不一定準(zhǔn)確，結(jié)合之前提到對(duì)數(shù)據(jù)索引的理解來(lái)看，還記得嘛？就把索引當(dāng)作有序序列來(lái)理解，反思SQL。l Set profiling , show profiles for query操作n 執(zhí)行開(kāi)銷(xiāo)u 注意，有問(wèn)題的SQL如果重復(fù)執(zhí)行，可能在緩存里，這時(shí)要注意避免緩存影響。通過(guò)這里可以看到。u 執(zhí)行時(shí)間超過(guò)0.005秒的頻繁操作SQL建議都分析一下。u 深入理解數(shù)據(jù)庫(kù)執(zhí)行的過(guò)程和開(kāi)銷(xiāo)的分布l Show processlistn 狀態(tài)清單u Sleep 狀態(tài)， 通常代表資源未釋放，如果是通過(guò)連接池，sleep狀態(tài)應(yīng)該恒定在一定數(shù)量范圍內(nèi)l 實(shí)戰(zhàn)范例： 因前端數(shù)據(jù)輸出時(shí)（特別是輸出到用戶(hù)終端）未及時(shí)關(guān)閉數(shù)據(jù)庫(kù)連接，導(dǎo)致因網(wǎng)絡(luò)連接速度產(chǎn)生大量sleep連接，在網(wǎng)速出現(xiàn)異常時(shí)，數(shù)據(jù)庫(kù) too many connections 掛死。l 簡(jiǎn)單解讀，數(shù)據(jù)查詢(xún)和執(zhí)行通常只需要不到0.01秒，而網(wǎng)絡(luò)輸出通常需要1秒左右甚至更長(zhǎng)，原本數(shù)據(jù)連接在0.01秒即可釋放，但是因?yàn)榍岸顺绦蛭磮?zhí)行close操作，直接輸出結(jié)果，那么在結(jié)果未展現(xiàn)在用戶(hù)桌面前，該數(shù)據(jù)庫(kù)連接一直維持在sleep狀態(tài)！u Waiting for net, reading from net, writing to netl 偶爾出現(xiàn)無(wú)妨l 如大量出現(xiàn)，迅速檢查數(shù)據(jù)庫(kù)到前端的網(wǎng)絡(luò)連接狀態(tài)和流量l 案例: 因外掛程序，內(nèi)網(wǎng)數(shù)據(jù)庫(kù)大量讀取，內(nèi)網(wǎng)使用的百兆交換迅速爆滿(mǎn)，導(dǎo)致大量連接阻塞在waiting for net，數(shù)據(jù)庫(kù)連接過(guò)多崩潰u Locked狀態(tài)l 有更新操作鎖定l 通常使用innodb可以很好的減少locked狀態(tài)的產(chǎn)生，但是切記，更新操作要正確使用索引，即便是低頻次更新操作也不能疏忽。如上影響結(jié)果集范例所示。l 在myisam的時(shí)代，locked是很多高并發(fā)應(yīng)用的噩夢(mèng)。所以mysql官方也開(kāi)始傾向于推薦innodb。u Copy to tmp tablel 索引及現(xiàn)有結(jié)構(gòu)無(wú)法涵蓋查詢(xún)條件，才會(huì)建立一個(gè)臨時(shí)表來(lái)滿(mǎn)足查詢(xún)要求，產(chǎn)生巨大的恐怖的i/o壓力。l 很可怕的搜索語(yǔ)句會(huì)導(dǎo)致這樣的情況，如果是數(shù)據(jù)分析，或者半夜的周期數(shù)據(jù)清理任務(wù)，偶爾出現(xiàn)，可以允許。頻繁出現(xiàn)務(wù)必優(yōu)化之。l Copy to tmp table 通常與連表查詢(xún)有關(guān)，建議逐漸習(xí)慣不使用連表查詢(xún)。l 實(shí)戰(zhàn)范例：n 某社區(qū)數(shù)據(jù)庫(kù)阻塞，求救，經(jīng)查，其服務(wù)器存在多個(gè)數(shù)據(jù)庫(kù)應(yīng)用和網(wǎng)站，其中一個(gè)不常用的小網(wǎng)站數(shù)據(jù)庫(kù)產(chǎn)生了一個(gè)恐怖的copy to tmp table 操作，導(dǎo)致整個(gè)硬盤(pán)i/o和cpu壓力超載。Kill掉該操作一切恢復(fù)。u Sending datal Sending data 并不是發(fā)送數(shù)據(jù)，別被這個(gè)名字所欺騙，這是從物理磁盤(pán)獲取數(shù)據(jù)的進(jìn)程，如果你的影響結(jié)果集較多，那么就需要從不同的磁盤(pán)碎片去抽取數(shù)據(jù)，l 偶爾出現(xiàn)該狀態(tài)連接無(wú)礙。l 回到上面影響結(jié)果集的問(wèn)題，一般而言，如果sending data連接過(guò)多，通常是某查詢(xún)的影響結(jié)果集過(guò)大，也就是查詢(xún)的索引項(xiàng)不夠優(yōu)化。l 如果出現(xiàn)大量相似的SQL語(yǔ)句出現(xiàn)在show proesslist列表中，并且都處于sending data狀態(tài)，優(yōu)化查詢(xún)索引，記住用影響結(jié)果集的思路去思考。u Freeing itemsl 理論上這玩意不會(huì)出現(xiàn)很多。偶爾出現(xiàn)無(wú)礙l 如果大量出現(xiàn)，內(nèi)存，硬盤(pán)可能已經(jīng)出現(xiàn)問(wèn)題。比如硬盤(pán)滿(mǎn)或損壞。u Sorting for l 和Sending data類(lèi)似，結(jié)果集過(guò)大，排序條件沒(méi)有索引化，需要在內(nèi)存里排序，甚至需要?jiǎng)?chuàng)建臨時(shí)結(jié)構(gòu)排序。u 其他l 還有很多狀態(tài)，遇到了，去查查資料。基本上我們遇到其他狀態(tài)的阻塞較少，所以不關(guān)心。分析流程l 基本流程n 詳細(xì)了解問(wèn)題狀況u Too many connections 是常見(jiàn)表象，有很多種原因。u 索引損壞的情況在innodb情況下很少出現(xiàn)。u 如出現(xiàn)其他情況應(yīng)追溯日志和錯(cuò)誤信息。n 了解基本負(fù)載狀況和運(yùn)營(yíng)狀況u 基本運(yùn)營(yíng)狀況l 當(dāng)前每秒讀請(qǐng)求l 當(dāng)前每秒寫(xiě)請(qǐng)求l 當(dāng)前在線(xiàn)用戶(hù)l 當(dāng)前數(shù)據(jù)容量u 基本負(fù)載情況l 學(xué)會(huì)使用這些指令n Top n Vmstatn uptime n iostat n df l Cpu負(fù)載構(gòu)成n 特別關(guān)注i/o壓力( wa%)n 多核負(fù)載分配l 內(nèi)存占用n Swap分區(qū)是否被侵占n 如Swap分區(qū)被侵占，物理內(nèi)存是否較多空閑l 磁盤(pán)狀態(tài)n 硬盤(pán)滿(mǎn)和inode節(jié)點(diǎn)滿(mǎn)的情況要迅速定位和迅速處理n 了解具體連接狀況u 當(dāng)前連接數(shù) l Netstat an|grep 3306|wc ll Show processlistu 當(dāng)前連接分布 show processlistl 前端應(yīng)用請(qǐng)求數(shù)據(jù)庫(kù)不要使用root帳號(hào)！n Root帳號(hào)比其他普通帳號(hào)多一個(gè)連接數(shù)許可。n 前端使用普通帳號(hào)，在too many connections的時(shí)候root帳號(hào)仍可以登錄數(shù)據(jù)庫(kù)查詢(xún) show processlist!n 記住，前端應(yīng)用程序不要設(shè)置一個(gè)不叫root的root帳號(hào)來(lái)糊弄！非root賬戶(hù)是骨子里的，而不是名義上的。l 狀態(tài)分布n 不同狀態(tài)代表不同的問(wèn)題，有不同的優(yōu)化目標(biāo)。n 參見(jiàn)如上范例。l 雷同SQL的分布n 是否較多雷同SQL出現(xiàn)在同一狀態(tài)u 當(dāng)前是否有較多慢查詢(xún)?nèi)罩緇 是否鎖定l 影響結(jié)果集n 頻繁度分析u 寫(xiě)頻繁度l 如果i/o壓力高，優(yōu)先分析寫(xiě)入頻繁度l Mysqlbinlog 輸出最新binlog文件，編寫(xiě)腳本拆分l 最多寫(xiě)入的數(shù)據(jù)表是哪個(gè)l 最多寫(xiě)入的數(shù)據(jù)SQL是什么l 是否存在基于同一主鍵的數(shù)據(jù)內(nèi)容高頻重復(fù)寫(xiě)入？n 涉及架構(gòu)優(yōu)化部分，參見(jiàn)架構(gòu)優(yōu)化-緩存異步更新u 讀取頻繁度l 如果cpu資源較高，而i/o壓力不高，優(yōu)先分析讀取頻繁度l 程序中在封裝的db類(lèi)增加抽樣日志即可，抽樣比例酌情考慮，以不顯著影響系統(tǒng)負(fù)載壓力為底線(xiàn)。l 最多讀取的數(shù)據(jù)表是哪個(gè)l 最多讀取的數(shù)據(jù)SQL是什么n 該SQL進(jìn)行explain 和set profiling判定n 注意判定時(shí)需要避免query cache影響u 比如，在這個(gè)SQL末尾增加一個(gè)條件子句 and 1=1 就可以避免從query cache中獲取數(shù)據(jù)，而得到真實(shí)的執(zhí)行狀態(tài)分析。 l 是否存在同一個(gè)查詢(xún)短期內(nèi)頻繁出現(xiàn)的情況n 涉及前端緩存優(yōu)化n 抓大放小，解決顯著問(wèn)題u 不苛求解決所有優(yōu)化問(wèn)題，但是應(yīng)以保證線(xiàn)上服務(wù)穩(wěn)定可靠為目標(biāo)。u 解決與評(píng)估要同時(shí)進(jìn)行，新的策略或解決方案務(wù)必經(jīng)過(guò)評(píng)估后上線(xiàn)?？偨Y(jié)l 要學(xué)會(huì)怎樣分析問(wèn)題，而不是單純拍腦袋優(yōu)化l 慢查詢(xún)只是最基礎(chǔ)的東西，要學(xué)會(huì)優(yōu)化0.01秒的查詢(xún)請(qǐng)求。l 當(dāng)發(fā)生連接阻塞時(shí)，不同狀態(tài)的阻塞有不同的原因，要找到原因，如果不對(duì)癥下藥，就會(huì)南轅北轍n 范例：如果本身系統(tǒng)內(nèi)存已經(jīng)超載，已經(jīng)使用到了swap，而還在考慮加大緩存來(lái)優(yōu)化查詢(xún)，那就是自尋死路了。l 監(jiān)測(cè)與跟蹤要經(jīng)常做，而不是出問(wèn)題才做n 讀取頻繁度抽樣監(jiān)測(cè)u 全監(jiān)測(cè)不要搞，i/o嚇?biāo)廊?。u 按照一個(gè)抽樣比例抽樣即可。u 針對(duì)抽樣中發(fā)現(xiàn)的問(wèn)題，可以按照特定SQL在特定時(shí)間內(nèi)監(jiān)測(cè)一段全查詢(xún)記錄，但仍要考慮i/o影響。n 寫(xiě)入頻繁度監(jiān)測(cè)u 基于binlog解開(kāi)即可，可定時(shí)或不定時(shí)分析。n 微慢查詢(xún)抽樣監(jiān)測(cè)u 高并發(fā)情況下，查詢(xún)請(qǐng)求時(shí)間超過(guò)0.01秒甚至0.005秒的，建議酌情抽樣記錄。n 連接數(shù)預(yù)警監(jiān)測(cè)u 連接數(shù)超過(guò)特定閾值的情況下，雖然數(shù)據(jù)庫(kù)沒(méi)有崩潰，建議記錄相關(guān)連接狀態(tài)。l 學(xué)會(huì)通過(guò)數(shù)據(jù)和監(jiān)控發(fā)現(xiàn)問(wèn)題，分析問(wèn)題，而后解決問(wèn)題順理成章。特別是要學(xué)會(huì)在日常監(jiān)控中發(fā)現(xiàn)隱患，而不是問(wèn)題爆發(fā)了才去處理和解決。Mysql 運(yùn)維優(yōu)化存儲(chǔ)引擎類(lèi)型l Myisam 速度快，響應(yīng)快。表級(jí)鎖是致命問(wèn)題。l Innodb 目前主流存儲(chǔ)引擎n 行級(jí)鎖 u 務(wù)必注意影響結(jié)果集的定義是什么u 行級(jí)鎖會(huì)帶來(lái)更新的額外開(kāi)銷(xiāo)，但是通常情況下是值得的。n 事務(wù)提交 u 對(duì)i/o效率提升的考慮u 對(duì)安全性的考慮l HEAP 內(nèi)存引擎n 頻繁更新和海量讀取情況下仍會(huì)存在鎖定狀況內(nèi)存使用考量l 理論上，內(nèi)存越大，越多數(shù)據(jù)讀取發(fā)生在內(nèi)存，效率越高l 要考慮到現(xiàn)實(shí)的硬件資源和瓶頸分布l 學(xué)會(huì)理解熱點(diǎn)數(shù)據(jù)，并將熱點(diǎn)數(shù)據(jù)盡可能內(nèi)存化n 所謂熱點(diǎn)數(shù)據(jù)，就是最多被訪(fǎng)問(wèn)的數(shù)據(jù)。n 通常數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)是不平均的，少數(shù)數(shù)據(jù)被頻繁讀寫(xiě)，而更多數(shù)據(jù)鮮有讀寫(xiě)。n 學(xué)會(huì)制定不同的熱點(diǎn)數(shù)據(jù)規(guī)則，并測(cè)算指標(biāo)。u 熱點(diǎn)數(shù)據(jù)規(guī)模，理論上，熱點(diǎn)數(shù)據(jù)越少越好，這樣可以更好的滿(mǎn)足業(yè)務(wù)的增長(zhǎng)趨勢(shì)。u 響應(yīng)滿(mǎn)足度，對(duì)響應(yīng)的滿(mǎn)足率越高越好。u 比如依據(jù)最后更新時(shí)間，總訪(fǎng)問(wèn)量，回訪(fǎng)次數(shù)等指標(biāo)定義熱點(diǎn)數(shù)據(jù)，并測(cè)算不同定義模式下的熱點(diǎn)數(shù)據(jù)規(guī)模性能與安全性考量l 數(shù)據(jù)提交方式n innodb_flush_log_at_trx_commit = 1 每次自動(dòng)提交，安全性高，i/o壓力大n innodb_flush_log_at_trx_commit = 2 每秒自動(dòng)提交，安全性略有影響，i/o承載強(qiáng)。l 日志同步n Sync-binlog=1 每條自動(dòng)更新，安全性高，i/o壓力大n Sync-binlog = 0 根據(jù)緩存設(shè)置情況自動(dòng)更新，存在丟失數(shù)據(jù)和同步延遲風(fēng)險(xiǎn)，i/o承載力強(qiáng)。l 性能與安全本身存在相悖的情況，需要在業(yè)務(wù)訴求層面決定取舍n 學(xué)會(huì)區(qū)分什么場(chǎng)合側(cè)重性能，什么場(chǎng)合側(cè)重安全n 學(xué)會(huì)將不同安全等級(jí)的數(shù)據(jù)庫(kù)用不同策略管理存儲(chǔ)壓力優(yōu)化l 順序讀寫(xiě)性能遠(yuǎn)高于隨機(jī)讀寫(xiě)l 日志類(lèi)數(shù)據(jù)可以使用順序讀寫(xiě)方式進(jìn)行l(wèi) 將順序?qū)憯?shù)據(jù)和隨機(jī)讀寫(xiě)數(shù)據(jù)分成不同的物理磁盤(pán)，有助于i/o壓力的疏解，前提是，你確信你的i/o壓力主要來(lái)自于可順序?qū)懖僮鳎ㄒ螂S機(jī)讀寫(xiě)干擾導(dǎo)致不能順序?qū)懀谴_實(shí)可以用順序?qū)懛绞竭M(jìn)行的i/o操作）。運(yùn)維監(jiān)控體系l 系統(tǒng)監(jiān)控n 服務(wù)器資源監(jiān)控u Cpu, 內(nèi)存，硬盤(pán)空間，i/o壓力u 設(shè)置閾值報(bào)警n 服務(wù)器流量監(jiān)控u 外網(wǎng)流量，內(nèi)網(wǎng)流量u 設(shè)置閾值報(bào)警n 連接狀態(tài)監(jiān)控u Show processlist 設(shè)置閾值，每分鐘監(jiān)測(cè)，超過(guò)閾值記錄l 應(yīng)用監(jiān)控n 慢查詢(xún)監(jiān)控u 慢查詢(xún)?nèi)罩緐 如果存在多臺(tái)數(shù)據(jù)庫(kù)服務(wù)器，應(yīng)有匯總查閱機(jī)制。n 請(qǐng)求錯(cuò)誤監(jiān)控u 高頻繁應(yīng)用中，會(huì)出現(xiàn)偶發(fā)性數(shù)據(jù)庫(kù)連接錯(cuò)誤或執(zhí)行錯(cuò)誤，將錯(cuò)誤信息記錄到日志，查看每日的比例變化。u 偶發(fā)性錯(cuò)誤，如果數(shù)量極少，可以不用處理，但是需時(shí)常監(jiān)控其趨勢(shì)。u 會(huì)存在惡意輸入內(nèi)容，輸入邊界限定缺乏導(dǎo)致執(zhí)行出錯(cuò)，需基于此防止惡意入侵探測(cè)行為。n 微慢查詢(xún)監(jiān)控u 高并發(fā)環(huán)境里，超過(guò)0.01秒的查詢(xún)請(qǐng)求都應(yīng)該關(guān)注一下。n 頻繁度監(jiān)控u 寫(xiě)操作，基于binlog，定期分析。u 讀操作，在前端db封裝代碼中增加抽樣日志，并輸出執(zhí)行時(shí)間。u 分析請(qǐng)求頻繁度是開(kāi)發(fā)架構(gòu) 進(jìn)一步優(yōu)化的基礎(chǔ)u 最好的優(yōu)化就是減少請(qǐng)求次數(shù)！l 總結(jié)：n 監(jiān)控與數(shù)據(jù)分析是一切優(yōu)化的基礎(chǔ)。n 沒(méi)有運(yùn)營(yíng)數(shù)據(jù)監(jiān)測(cè)就不要妄談優(yōu)化！n 監(jiān)控要注意不要產(chǎn)生太多額外的負(fù)載，不要因監(jiān)控帶來(lái)太多額外系統(tǒng)開(kāi)銷(xiāo)Mysql 架構(gòu)優(yōu)化架構(gòu)優(yōu)化目標(biāo)防止單點(diǎn)隱患l 所謂單點(diǎn)隱患，就是某臺(tái)設(shè)備出現(xiàn)故障，會(huì)導(dǎo)致整體系統(tǒng)的不可用，這個(gè)設(shè)備就是單點(diǎn)隱患。l 理解連帶效應(yīng)，所謂連帶效應(yīng)，就是一種問(wèn)題會(huì)引發(fā)另一種故障，舉例而言，memcache+mysql是一種常見(jiàn)緩存組合，在前端壓力很大時(shí)，如果memcache崩潰，理論上數(shù)據(jù)會(huì)通過(guò)mysql讀取，不存在系統(tǒng)不可用情況，但是mysql無(wú)法對(duì)抗如此大的壓力沖擊，會(huì)因此連帶崩潰。因A系統(tǒng)問(wèn)題導(dǎo)致B系統(tǒng)崩潰的連帶問(wèn)題，在運(yùn)維過(guò)程中會(huì)頻繁出現(xiàn)。n 實(shí)戰(zhàn)范例： 在mysql連接不及時(shí)釋放的應(yīng)用環(huán)境里，當(dāng)網(wǎng)絡(luò)環(huán)境異常（同機(jī)房友鄰服務(wù)器遭受拒絕服務(wù)攻擊，出口阻塞），網(wǎng)絡(luò)延遲加劇，空連接數(shù)急劇增加，導(dǎo)致數(shù)據(jù)庫(kù)連接過(guò)多崩潰。n 實(shí)戰(zhàn)范例2：前端代碼 通常我們封裝 mysql_connect和memcache_connect，二者的順序不同，會(huì)產(chǎn)生不同的連帶效應(yīng)。如果mysql_connect在前，那么一旦memcache連接阻塞，會(huì)連帶mysql空連接過(guò)多崩潰。n 連帶效應(yīng)是常見(jiàn)的系統(tǒng)崩潰，日常分析崩潰原因的時(shí)候需要認(rèn)真考慮連帶效應(yīng)的影響，頭疼醫(yī)頭，腳疼醫(yī)腳是不行的。方便系統(tǒng)擴(kuò)容l 數(shù)據(jù)容量增加后，要考慮能夠?qū)?shù)據(jù)分布到不同的服務(wù)器上。l 請(qǐng)求壓力增加時(shí)，要考慮將請(qǐng)求壓力分布到不同服務(wù)器上。l 擴(kuò)容設(shè)計(jì)時(shí)需要考慮防止單點(diǎn)隱患。安全可控，成本可控l 數(shù)據(jù)安全，業(yè)務(wù)安全l 人力資源成本帶寬流量成本硬件成本n 成本與流量的關(guān)系曲線(xiàn)應(yīng)低于線(xiàn)性增長(zhǎng)（流量為橫軸，成本為縱軸）。n 規(guī)模優(yōu)勢(shì)l 本教程僅就與數(shù)據(jù)庫(kù)有關(guān)部分討論，與數(shù)據(jù)庫(kù)無(wú)關(guān)部門(mén)請(qǐng)自行參閱其他學(xué)習(xí)資料。分布式方案分庫(kù)&拆表方案l 基本認(rèn)識(shí)n 用分庫(kù)&拆表是解決數(shù)據(jù)庫(kù)容量問(wèn)題的唯一途徑。n 分庫(kù)&拆表也是解決性能壓力的最優(yōu)選擇。n 分庫(kù) 不同的數(shù)據(jù)表放到不同的數(shù)據(jù)庫(kù)服務(wù)器中（也可能是虛擬服務(wù)器）n 拆表 一張數(shù)據(jù)表拆成多張數(shù)據(jù)表，可能位于同一臺(tái)服務(wù)器，也可能位于多臺(tái)服務(wù)器（含虛擬服務(wù)器）。l 去關(guān)聯(lián)化原則n 摘除數(shù)據(jù)表之間的關(guān)聯(lián)，是分庫(kù)的基礎(chǔ)工作。n 摘除關(guān)聯(lián)的目的是，當(dāng)數(shù)據(jù)表分布到不同服務(wù)器時(shí)，查詢(xún)請(qǐng)求容易分發(fā)和處理。n 學(xué)會(huì)理解反范式數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)，所謂反范式，第一要點(diǎn)是不用外鍵，不允許Join操作，不允許任何需要跨越兩個(gè)表的查詢(xún)請(qǐng)求。第二要點(diǎn)是適度冗余減少查詢(xún)請(qǐng)求，比如說(shuō)，信息表，fromuid, touid, message字段外，還需要一個(gè)fromuname字段記錄用戶(hù)名，這樣查詢(xún)者通過(guò)touid查詢(xún)后，能夠立即得到發(fā)信人的用戶(hù)名，而無(wú)需進(jìn)行另一個(gè)數(shù)據(jù)表的查詢(xún)。n 去關(guān)聯(lián)化處理會(huì)帶來(lái)額外的考慮，比如說(shuō)，某一個(gè)數(shù)據(jù)表內(nèi)容的修改，對(duì)另一個(gè)數(shù)據(jù)表的影響。這一點(diǎn)需要在程序或其他途徑去考慮。l 分庫(kù)方案n 安全性拆分u 將高安全性數(shù)據(jù)與低安全性數(shù)據(jù)分庫(kù)，這樣的好處第一是便于維護(hù)，第二是高安全性數(shù)據(jù)的數(shù)據(jù)庫(kù)參數(shù)配置可以以安全優(yōu)先，而低安全性數(shù)據(jù)的參數(shù)配置以性能優(yōu)先。參見(jiàn)運(yùn)維優(yōu)化相關(guān)部分。n 順序?qū)憯?shù)據(jù)與隨機(jī)讀寫(xiě)數(shù)據(jù)分庫(kù)u 順序數(shù)據(jù)與隨機(jī)數(shù)據(jù)區(qū)分存儲(chǔ)地址，保證物理i/o優(yōu)化。這個(gè)實(shí)話(huà)說(shuō)，我只聽(tīng)說(shuō)了概念，還沒(méi)學(xué)會(huì)怎么實(shí)踐。n 基于業(yè)務(wù)邏輯拆分u 根據(jù)數(shù)據(jù)表的內(nèi)容構(gòu)成，業(yè)務(wù)邏輯拆分，便于日常維護(hù)和前端調(diào)用。u 基于業(yè)務(wù)邏輯拆分，可以減少前端應(yīng)用請(qǐng)求發(fā)送到不同數(shù)據(jù)庫(kù)服務(wù)器的頻次，從而減少鏈接開(kāi)銷(xiāo)。u 基于業(yè)務(wù)邏輯拆分，可保留部分?jǐn)?shù)據(jù)關(guān)聯(lián)，前端web工程師可在限度范圍內(nèi)執(zhí)行關(guān)聯(lián)查詢(xún)。n 基于負(fù)載壓力拆分u 基于負(fù)載壓力對(duì)數(shù)據(jù)結(jié)構(gòu)拆分，便于直接將負(fù)載分擔(dān)給不同的服務(wù)器。u 基于負(fù)載壓力拆分，可能拆分后的數(shù)據(jù)庫(kù)包含不同業(yè)務(wù)類(lèi)型的數(shù)據(jù)表，日常維護(hù)會(huì)有一定的煩惱。l 分表方案n 數(shù)據(jù)量過(guò)大或者訪(fǎng)問(wèn)壓力過(guò)大的數(shù)據(jù)表需要切分n 忙閑分表u 單數(shù)據(jù)表字段過(guò)多，可將頻繁更新的整數(shù)數(shù)據(jù)與非頻繁更新的字符串?dāng)?shù)據(jù)切分u 范例 user表 ，個(gè)人簡(jiǎn)介，地址，QQ號(hào)，聯(lián)系方式，頭像 這些字段為字符串類(lèi)型，更新請(qǐng)求少； 最后登錄時(shí)間，在線(xiàn)時(shí)常，訪(fǎng)問(wèn)次數(shù)，信件數(shù)這些字段為整數(shù)型字段，更新頻繁，可以將后面這些更新頻繁的字段獨(dú)立拆出一張數(shù)據(jù)表，表內(nèi)容變少，索引結(jié)構(gòu)變少，讀寫(xiě)請(qǐng)求變快。n 橫向切表u 等分切表，如哈希切表或其他基于對(duì)某數(shù)字取余的切表。等分切表的優(yōu)點(diǎn)是負(fù)載很方便的分布到不同服務(wù)器；缺點(diǎn)是當(dāng)容量繼續(xù)增加時(shí)無(wú)法方便的擴(kuò)容，需要重新進(jìn)行數(shù)據(jù)的切分或轉(zhuǎn)表。而且一些關(guān)鍵主鍵不易處理。u 遞增切表，比如每1kw用戶(hù)開(kāi)一個(gè)新表，優(yōu)點(diǎn)是可以適應(yīng)數(shù)據(jù)的自增趨勢(shì)；缺點(diǎn)是往往新數(shù)據(jù)負(fù)載高，壓力分配不平均。u 日期切表，適用于日志記錄式數(shù)據(jù)，優(yōu)缺點(diǎn)等同于遞增切表。u 個(gè)人傾向于遞增切表，具體根據(jù)應(yīng)用場(chǎng)景決定。n 熱點(diǎn)數(shù)據(jù)分表u 將數(shù)據(jù)量較大的數(shù)據(jù)表中將讀寫(xiě)頻繁的數(shù)據(jù)抽取出來(lái)，形成熱點(diǎn)數(shù)據(jù)表。通常一個(gè)龐大數(shù)據(jù)表經(jīng)常被讀寫(xiě)的內(nèi)容往往具有一定的集中性，如果這些集中數(shù)據(jù)單獨(dú)處理，就會(huì)極大減少整體系統(tǒng)的負(fù)載。u 熱點(diǎn)數(shù)據(jù)表與舊有數(shù)據(jù)關(guān)系l 可以是一張冗余表，即該表數(shù)據(jù)丟失不會(huì)妨礙使用，因源數(shù)據(jù)仍存在于舊有結(jié)構(gòu)中。優(yōu)點(diǎn)是安全性高，維護(hù)方便，缺點(diǎn)是寫(xiě)壓力不能分擔(dān)，仍需要同步寫(xiě)回原系統(tǒng)。l 可以是非冗余表，即熱點(diǎn)數(shù)據(jù)的內(nèi)容原有結(jié)構(gòu)不再保存，優(yōu)點(diǎn)是讀寫(xiě)效率全部?jī)?yōu)化；缺點(diǎn)是當(dāng)熱點(diǎn)數(shù)據(jù)發(fā)生變化時(shí)，維護(hù)量較大。l 具體方案選擇需要根據(jù)讀寫(xiě)比例決定，在讀頻率遠(yuǎn)高于寫(xiě)頻率情況下，優(yōu)先考慮冗余表方案。u 熱點(diǎn)數(shù)據(jù)表可以用單獨(dú)的優(yōu)化的硬件存儲(chǔ)，比如昂貴的閃存卡或大內(nèi)存系統(tǒng)。u 熱點(diǎn)數(shù)據(jù)表的重要指標(biāo)l 熱點(diǎn)數(shù)據(jù)的定義需要根據(jù)業(yè)務(wù)模式自行制定策略，常見(jiàn)策略為，按照最新的操作時(shí)間；按照內(nèi)容豐富度等等。l 數(shù)據(jù)規(guī)模，比如從1000萬(wàn)條數(shù)據(jù)，抽取出100萬(wàn)條熱點(diǎn)數(shù)據(jù)。l 熱點(diǎn)命中率，比如查詢(xún)10次，多少次命中在熱點(diǎn)數(shù)據(jù)內(nèi)。l 理論上，數(shù)據(jù)規(guī)模越小，熱點(diǎn)命中率越高，說(shuō)明效果越好。需要根據(jù)業(yè)務(wù)自行評(píng)估。u 熱點(diǎn)數(shù)據(jù)表的動(dòng)態(tài)維護(hù)l 加載熱點(diǎn)數(shù)據(jù)方案選擇n 定時(shí)從舊有數(shù)據(jù)結(jié)構(gòu)中按照新的策略獲取n 在從舊有數(shù)據(jù)結(jié)構(gòu)讀取時(shí)動(dòng)態(tài)加載到熱點(diǎn)數(shù)據(jù)l 剔除熱點(diǎn)數(shù)據(jù)方案選擇n 基于特定策略，定時(shí)將熱點(diǎn)數(shù)據(jù)中訪(fǎng)問(wèn)頻次較少的數(shù)據(jù)剔除n 如熱點(diǎn)數(shù)據(jù)是冗余表，則直接刪除即可，如不是冗余表，需要回寫(xiě)給舊有數(shù)據(jù)結(jié)構(gòu)。u 通常，熱點(diǎn)數(shù)據(jù)往往是基于緩存或者key-value方案冗余存儲(chǔ)，所以這里提到的熱點(diǎn)數(shù)據(jù)表，其實(shí)更多是理解思路，用到的場(chǎng)合可能并不多.l 表結(jié)構(gòu)設(shè)計(jì)n 查詢(xún)?nèi)哂啾碓O(shè)計(jì)u 涉及分表操作后，一些常見(jiàn)的索引查詢(xún)可能需要跨表，帶來(lái)不必要的麻煩。確認(rèn)查詢(xún)請(qǐng)求遠(yuǎn)大于寫(xiě)入請(qǐng)求時(shí)，應(yīng)設(shè)置便于查詢(xún)項(xiàng)的冗余表。u 實(shí)戰(zhàn)范例，l 用戶(hù)分表，將用戶(hù)庫(kù)分成若干數(shù)據(jù)表l 基于用戶(hù)名的查詢(xún)和基于uid的查詢(xún)都是高并發(fā)請(qǐng)求。l 用戶(hù)分表基于uid分成數(shù)據(jù)表，同時(shí)基于用戶(hù)名做對(duì)應(yīng)冗余表。u 冗余表要點(diǎn)l 數(shù)據(jù)一致性，簡(jiǎn)單說(shuō)，同增，同刪，同更新。l 可以做全冗余，或者只做主鍵關(guān)聯(lián)的冗余，比如通過(guò)用戶(hù)名查詢(xún)uid，再基于uid查詢(xún)?cè)幢?。n 中間數(shù)據(jù)表u 為了減少會(huì)涉及大規(guī)模影響結(jié)果集的表數(shù)據(jù)操作，比如count，sum操作。應(yīng)將一些統(tǒng)計(jì)類(lèi)數(shù)據(jù)通過(guò)中間數(shù)據(jù)表保存。u 中間數(shù)據(jù)表應(yīng)能通過(guò)源數(shù)據(jù)表恢復(fù)。u 實(shí)戰(zhàn)范例：l 論壇板塊的發(fā)帖量，回帖量，每日新增數(shù)據(jù)等l 網(wǎng)站每日新增用戶(hù)數(shù)等。l 后臺(tái)可以通過(guò)源數(shù)據(jù)表更新該數(shù)字。n 歷史數(shù)據(jù)表u 歷史數(shù)據(jù)表對(duì)應(yīng)于熱點(diǎn)數(shù)據(jù)表，將需求較少又不能丟棄的數(shù)據(jù)存入，僅在少數(shù)情況下被訪(fǎng)問(wèn)。主從架構(gòu)l 基本認(rèn)識(shí)n 讀寫(xiě)分離對(duì)負(fù)載的減輕遠(yuǎn)遠(yuǎn)不如分庫(kù)分表來(lái)的直接。n 寫(xiě)壓力會(huì)傳遞給從表，只讀從庫(kù)一樣有寫(xiě)壓力，一樣會(huì)產(chǎn)生讀寫(xiě)鎖！n 一主多從結(jié)構(gòu)下，主庫(kù)是單點(diǎn)隱患，很難解決（如主庫(kù)當(dāng)機(jī)，從庫(kù)可以響應(yīng)讀寫(xiě)，但是無(wú)法自動(dòng)擔(dān)當(dāng)主庫(kù)的分發(fā)功能）n 主從延遲也是重大問(wèn)題。一旦有較大寫(xiě)入問(wèn)題，如表結(jié)構(gòu)更新，主從會(huì)產(chǎn)生巨大延遲。l 應(yīng)用場(chǎng)景n 在線(xiàn)熱備n 異地分布u 寫(xiě)分布，讀統(tǒng)一。u 仍然困難重重，受限于網(wǎng)絡(luò)環(huán)境問(wèn)題巨多！n 自動(dòng)障礙轉(zhuǎn)移u 主崩潰，從自動(dòng)接管n 個(gè)人建議，負(fù)載均衡主要使用分庫(kù)方案，主從主要用于熱備和障礙轉(zhuǎn)移。l 潛在優(yōu)化點(diǎn)n 為了減少寫(xiě)壓力，有些人建議主不建索引提升i/o性能，從建立索引滿(mǎn)足查詢(xún)要求。個(gè)人認(rèn)為這樣維護(hù)較為麻煩。而且從本身會(huì)繼承主的i/o壓力，因此優(yōu)化價(jià)值有限。該思路特此分享，不做推薦。故障轉(zhuǎn)移處理l 要點(diǎn)n 程序與數(shù)據(jù)庫(kù)的連接，基于虛地址而非真實(shí)ip，由負(fù)載均衡系統(tǒng)監(jiān)控。n 保持主從結(jié)構(gòu)的簡(jiǎn)單化，否則很難做到故障點(diǎn)摘除。l 思考方式n 遍歷對(duì)服務(wù)器集群的任何一臺(tái)服務(wù)器，前端web，中間件，監(jiān)控，緩存，db等等，假設(shè)該服務(wù)器出現(xiàn)故障，系統(tǒng)是否會(huì)出現(xiàn)異常？用戶(hù)訪(fǎng)問(wèn)是否會(huì)出現(xiàn)異常。n 目標(biāo)：任意一臺(tái)服務(wù)器崩潰，負(fù)載和數(shù)據(jù)操作均會(huì)很短時(shí)間內(nèi)自動(dòng)轉(zhuǎn)移到其他服務(wù)器，不會(huì)影響業(yè)務(wù)的正常進(jìn)行。不會(huì)造成惡性的數(shù)據(jù)丟失。（哪些是可以丟失的，哪些是不能丟失的）緩存方案緩存結(jié)合數(shù)據(jù)庫(kù)的讀取l Memcached是最常用的緩存系統(tǒng)l Mysql 最新版本已經(jīng)開(kāi)始支持memcache插件，但據(jù)牛人分析，尚不成熟，暫不推薦。l 數(shù)據(jù)讀取n 并不是所有數(shù)據(jù)都適合被緩存，也并不是進(jìn)入了緩存就意味著效率提升。n 命中率是第一要評(píng)估的數(shù)據(jù)。n 如何評(píng)估進(jìn)入緩存的數(shù)據(jù)規(guī)模，以及命中率優(yōu)化，是非常需要細(xì)心分析的。l 實(shí)景分析： 前端請(qǐng)求先連接緩存，緩存未命中連接數(shù)據(jù)庫(kù)，進(jìn)行查詢(xún)，未命中狀態(tài)比單純連接數(shù)據(jù)庫(kù)查詢(xún)多了一次連接和查詢(xún)的操作；如果緩存命中率很低，則這個(gè)額外的操作非但不能提高查詢(xún)效率，反而為系統(tǒng)帶來(lái)了額外的負(fù)載和復(fù)雜性，得不償失。n 相關(guān)評(píng)估類(lèi)似于熱點(diǎn)數(shù)據(jù)表的介紹。n 善于利用內(nèi)存，請(qǐng)注意數(shù)據(jù)存儲(chǔ)的格式及壓縮算法。l Key-value 方案繁多，本培訓(xùn)文檔暫不展開(kāi)。緩存結(jié)合數(shù)據(jù)庫(kù)的寫(xiě)入l 利用緩存不但可以減少數(shù)據(jù)讀取請(qǐng)求，還可以減少數(shù)據(jù)庫(kù)寫(xiě)入i/o壓力l 緩存實(shí)時(shí)更新，數(shù)據(jù)庫(kù)異步更新n