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

《mysql性能優(yōu)化教程》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在行業(yè)資料-天天文庫。

1、Mysql性能優(yōu)化教程目錄目錄1背景及目標(biāo)2Mysql執(zhí)行優(yōu)化2認(rèn)識(shí)數(shù)據(jù)索引2為什么使用數(shù)據(jù)索引能提高效率2如何理解數(shù)據(jù)索引的結(jié)構(gòu)2優(yōu)化實(shí)戰(zhàn)范例3認(rèn)識(shí)影響結(jié)果集4影響結(jié)果集的獲取4影響結(jié)果集的解讀4常見案例及優(yōu)化思路5理解執(zhí)行狀態(tài)7常見關(guān)注重點(diǎn)7執(zhí)行狀態(tài)分析8分析流程9常見案例解析11總結(jié)12Mysql運(yùn)維優(yōu)化14存儲(chǔ)引擎類型14內(nèi)存使用考量14性能與安全性考量14存儲(chǔ)/寫入壓力優(yōu)化15運(yùn)維監(jiān)控體系15Mysql架構(gòu)優(yōu)化17架構(gòu)優(yōu)化目標(biāo)17防止單點(diǎn)隱患17方便系統(tǒng)擴(kuò)容17安全可控，成本可控17分布式方案18分庫&拆表方案18反范式設(shè)計(jì)（冗余結(jié)構(gòu)設(shè)計(jì)）

2、20主從架構(gòu)21故障轉(zhuǎn)移處理22緩存方案22緩存結(jié)合數(shù)據(jù)庫的讀取22緩存結(jié)合數(shù)據(jù)庫的寫入23總結(jié)24背景及目標(biāo)l廈門游家公司（4399.com）用于員工培訓(xùn)和分享。l針對(duì)用戶群為已經(jīng)使用過mysql環(huán)境，并有一定開發(fā)經(jīng)驗(yàn)的工程師l針對(duì)高并發(fā)，海量數(shù)據(jù)的互聯(lián)網(wǎng)環(huán)境。l本文語言為口語，非學(xué)術(shù)標(biāo)準(zhǔn)用語。l以實(shí)戰(zhàn)和解決具體問題為主要目標(biāo)，非應(yīng)試，非常規(guī)教育。友情提醒，在校生學(xué)習(xí)本教程可能對(duì)成績提高有害無益。l非技術(shù)挑戰(zhàn)，非高端架構(gòu)師培訓(xùn)，請(qǐng)高手自動(dòng)忽略。l本文檔在2011年7月-12月持續(xù)更新，加強(qiáng)了影響結(jié)果集分析的內(nèi)容并增補(bǔ)優(yōu)化實(shí)戰(zhàn)案例若干。Mysql執(zhí)行

3、優(yōu)化認(rèn)識(shí)數(shù)據(jù)索引為什么使用數(shù)據(jù)索引能提高效率n關(guān)系型數(shù)據(jù)庫的數(shù)據(jù)索引（Btree及常見索引結(jié)構(gòu)）的存儲(chǔ)是有序的。n在有序的情況下，通過索引查詢一個(gè)數(shù)據(jù)是無需遍歷索引記錄的n關(guān)系型數(shù)據(jù)庫數(shù)據(jù)索引的查詢效率趨近于二分法查詢效率，趨近于log2(N)。n極端情況下（更新請(qǐng)求少，更新實(shí)時(shí)要求低，查詢請(qǐng)求頻繁），建立單向有序序列可替代數(shù)據(jù)索引。nHASH索引的查詢效率是尋址操作，趨近于1次查詢，比有序索引查詢效率更高，但是不支持比對(duì)查詢，區(qū)間查詢，排序等操作，僅支持key-value類型查詢。不是本文重點(diǎn)。如何理解數(shù)據(jù)索引的結(jié)構(gòu)n數(shù)據(jù)索引通常默認(rèn)采用btree

4、索引，（內(nèi)存表也使用了hash索引）。n僅就有序前提而言，單向有序排序序列是查找效率最高的（二分查找，或者說折半查找），使用樹形索引的目的是為了達(dá)到快速的更新和增刪操作。n在極端情況下（比如數(shù)據(jù)查詢需求量非常大，而數(shù)據(jù)更新需求極少，實(shí)時(shí)性要求不高，數(shù)據(jù)規(guī)模有限），直接使用單一排序序列，折半查找速度最快。n在進(jìn)行索引分析和SQL優(yōu)化時(shí)，可以將數(shù)據(jù)索引字段想象為單一有序序列，并以此作為分析的基礎(chǔ)。涉及到復(fù)合索引情況，復(fù)合索引按照索引順序拼湊成一個(gè)字段，想象為單一有序序列，并以此作為分析的基礎(chǔ)。n一條數(shù)據(jù)查詢只能使用一個(gè)索引，索引可以是多個(gè)字段合并的復(fù)合索

5、引。但是一條數(shù)據(jù)查詢不能使用多個(gè)索引。優(yōu)化實(shí)戰(zhàn)范例l實(shí)戰(zhàn)范例1：ip地址反查n資源：Ip地址對(duì)應(yīng)表，源數(shù)據(jù)格式為startip,endip,area源數(shù)據(jù)條數(shù)為10萬條左右，呈很大的分散性n目標(biāo)：需要通過任意ip查詢?cè)搃p所屬地區(qū)性能要求達(dá)到每秒1000次以上的查詢效率n挑戰(zhàn)：如使用betweenstartipandendip這樣的條件數(shù)據(jù)庫操作，因?yàn)樯婕皟蓚€(gè)字段的betweenand,無法有效使用索引。如果每次查詢請(qǐng)求需要遍歷10萬條記錄，根本不行。n方法：一次性排序（只在數(shù)據(jù)準(zhǔn)備中進(jìn)行，數(shù)據(jù)可存儲(chǔ)在內(nèi)存序列）折半查找（每次請(qǐng)求以折半查找方式進(jìn)行）

6、l實(shí)戰(zhàn)范例2：目標(biāo)：查找與訪問者同一地區(qū)的異性，按照最后登錄時(shí)間逆序n挑戰(zhàn)：高訪問量社區(qū)的高頻查詢，如何優(yōu)化。查詢SQL:select*fromuserwherearea=’$area’andsex=’$sex’orderbylastlogindesclimit0,30;建立復(fù)合索引并不難，area+sex+lastlogin三個(gè)字段的復(fù)合索引,如何理解？n解讀：首先，忘掉btree，將索引字段理解為一個(gè)排序序列。另外，牢記數(shù)據(jù)查詢只能使用一個(gè)索引，每個(gè)字段建立獨(dú)立索引的情況下，也只能有一條索引被使用！如果只使用area會(huì)怎樣？搜索會(huì)把符合area的

7、結(jié)果全部找出來，然后在這里面遍歷，選擇命中sex的并排序。遍歷所有area=’$area’數(shù)據(jù)！如果使用了area+sex，略好，仍然要遍歷所有area=’$area’andsex=’$sex’數(shù)據(jù)，然后在這個(gè)基礎(chǔ)上排序??！Area+sex+lastlogin復(fù)合索引時(shí)（切記lastlogin在最后），該索引基于area+sex+lastlogin三個(gè)字段合并的結(jié)果排序，該列表可以想象如下。廣州女$時(shí)間1廣州女$時(shí)間2廣州女$時(shí)間3…廣州男….深圳女….數(shù)據(jù)庫很容易命中到area+sex的邊界，并且基于下邊界向上追溯30條記錄，搞定！在索引中迅速命中

8、所有結(jié)果，無需二次遍歷！認(rèn)識(shí)影響結(jié)果集影響結(jié)果集的獲取n通過Explain分析SQL，查看rows列內(nèi)容n通