mysql性能的檢查和調優方法

　　我一直是使用mysql這個數據庫軟件它工作比較穩定效率也很高在遇到嚴重性能問題時一般都有這麼幾種可能

　　索引沒有建好

　　sql寫法過於復雜

　　配置錯誤

　　機器實在負荷不了

　　索引沒有建好

　　如果看到mysql消耗的cpu很大可以用mysql的client工具來檢查

　　在linux下執行

　　/usr/local/mysql/bin/mysql hlocalhost uroot p

　　輸入密碼如果沒有密碼則不用p參數就可以進到客戶端界面中

　　看看當前的運行情況

　　show full processlist

　　可以多運行幾次

　　這個命令可以看到當前正在執行的sql語句它會告知執行的sql數據庫名執行的狀態來自的客戶端ip所使用的帳號運行時間等信息

　　在我的cache後端這裡面大部分時間是看不到顯示任何sql語句的我認為這樣才算比較正常如果看到有很多sql語句那麼這台mysql就一定會有性能問題

　　如果出現了性能問題則可以進行分析

　　是不是有sql語句卡住了？

　　這是出現比較多的情況如果數據庫是采用myisam那麼有可能有一個寫入的線程會把數據表給鎖定了如果這條語句不結束則其它語句也無法運行

　　查看processlist裡的time這一項看看有沒有執行時間很長的語句要留意這些語句

　　大量相同的sql語句正在執行

　　如果出現這種情況則有可能是該sql語句執行的效率低下同樣要留意這些語句

　　然後把你所懷疑的語句統統集合一下用desc（explain）來檢查這些語句

　　首先看看一個正常的desc輸出

　　mysql> desc select * from imgs where imgid=;

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　注意keyrows和Extra這三項這條語句返回的結果說明了該sql會使用PRIMARY主鍵索引來查詢結果集數量為條Extra沒有顯示證明沒有用到排序或其他操作由此結果可以推斷mysql會從索引中查詢imgid=這條記錄然後再到真實表中取出所有字段是很簡單的操作

　　key是指明當前sql會使用的索引mysql執行一條簡單語句時只能使用到一條索引注意這個限制rows是返回的結果集大小結果集就是使用該索引進行一次搜索的所有匹配結果Extra一般會顯示查詢和排序的方式

　　如果沒有使用到key或者rows很大而用到了filesort排序一般都會影響到效率例如

　　mysql> desc select * from imgs where userid=mini order by clicks desc limit ;

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　這條sql結果集會有條用到了filesort所以執行起來會非常消耗效率的這時mysql執行時會把整個表掃描一遍一條一條去找到匹配userid=mini的記錄然後還要對這些記錄的clicks進行一次排序效率可想而知真實執行時如果發現還比較快的話那是因為服務器內存還足夠將條比較短小的記錄全部讀入內存所以還比較快但是並發多起來或者表大起來的話效率問題就嚴重了

　　這時我把userid加入索引

　　create index userid on imgs (userid);

　　然後再檢查

　　mysql> desc select * from imgs where userid=mini order by clicks desc limit ;

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　嗯這時可以看到mysql使用了userid這個索引搜索了用userid索引一次搜索後結果集有條然後雖然使用了filesort一條一條排序但是因為結果集只有區區條效率問題得以緩解

　　但是如果我用別的userid查詢結果又會有所不同

　　mysql> desc select * from imgs where userid=admin order by clicks desc limit ;

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　這個結果和userid=mini的結果基本相同但是mysql用userid索引一次搜索後結果集的大小達到條這條記錄都會加入內存進行filesort效率比起mini那次來說就差很多了這時可以有兩種辦法可以解決第一種辦法是再加一個索引和判斷條件因為我只需要根據點擊量取最大的條數據所以有很多數據我根本不需要加進來排序比如點擊量小於的這些數據可能占了很大部分

　　我對clicks加一個索引然後加入一個where條件再查詢

　　create index clicks on imgs(clicks);

　　mysql> desc select * from imgs where userid=admin order by clicks desc limit ;

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　這時可以看到possible_keys變成了useridclickspossible_keys是可以匹配的所有索引mysql會從possible_keys中自己判斷並取用其中一個索引來執行語句值得注意的是mysql取用的這個索引未必是最優化的這次查詢mysql還是使用userid這個索引來查詢的並沒有按照我的意願所以結果還是沒有什麼變化改一下sql加上use index強制mysql使用clicks索引

　　mysql> desc select * from imgs use index (clicks) where userid=admin and clicks> order by clicks desc limit

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　這時mysql用到了clicks索引進行查詢但是結果集比userid還要大！看來還要再進行限制

　　mysql> desc select * from imgs use index (clicks) where userid=admin and clicks> order by clicks desc limit

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　加到的時候結果集變成了條排序效率應該是可以接受

　　不過采用換索引這種優化方式需要取一個采樣點比如這個例子中的這個數字這樣對userid的每個數值都要去找一個采樣點這樣對程序來說是很難辦的如果按取樣的話那麼userid=mini這個例子中取到的結果將不會是條而是條給用戶造成了困惑

　　當然還有另一種辦法加入雙索引

　　create index userid_clicks on imgs (userid clicks)

　　mysql> desc select * from imgs where userid=admin order by clicks desc limit ;

　　+++++++++++

　　+++++++++++

　　+++++++++++

　　 row in set ( sec)

　　這時可以看到結果集還是條但是Extra中的filesort不見了這時mysql使用userid_clicks這個索引去查詢這不但能快速查詢到userid=admin的所有記錄並且結果是根據clicks排好序的！所以不用再把這個結果集讀入內存一條一條排序了效率上會高很多

　　但是用多字段索引這種方式有個問題如果查詢的sql種類很多的話就得好好規劃一下了否則索引會建得非常多不但會影響到數據insert和update的效率而且數據表也容易損壞

　　以上是對索引優化的辦法因為原因可能會比較復雜所以寫得比較的長一般好好優化了索引之後mysql的效率會提升n個檔次從而也不需要考慮增加機器來解決問題了

　　但是mysql甚至所有數據庫可能都不好解決limit的問題在mysql中limit 只要索引合適是沒有問題的但是limit 就會很慢了因為mysql會掃描排好序的結果然後找到這個點取出條返回要找到這個點就要掃描條記錄這個循環是比較耗時的不知道會不會有什麼好的算法可以優化這個掃描引擎我冥思苦想也想不出有什麼好辦法對於limit目前直至比較久遠的將來我想只能通過業務程序和數據表的規劃來優化我想到的這些優化辦法也都還沒有一個是萬全之策往後再討論

　　sql寫法過於復雜

　　sql寫法假如用到一些特殊的功能比如groupby或者多表聯合查詢的話mysql用到什麼方式來查詢也可以用desc來分析我這邊用復雜sql的情況還不算多所以不常分析暫時就沒有好的建議

　　配置錯誤

　　配置裡主要參數是key_buffersort_buffer_size/myisam_sort_buffer_size這兩個參數意思是

　　key_buffer=M全部表的索引都會盡可能放在這塊內存區域內索引比較大的話就開稍大點都可以我一般設為M有個好的建議是把很少用到並且比較大的表想辦法移到別的地方去這樣可以顯著減少mysql的內存占用

　　sort_buffer_size=M單個線程使用的用於排序的內存查詢結果集都會放進這內存裡如果比較小mysql會多放幾次所以稍微開大一點就可以了重要是優化好索引和查詢語句讓他們不要生成太大的結果集

　　另外一些配置

　　thread_concurrency=這個配置標配=cpu數量x

　　interactive_timeout=

　　wait_timeout=這兩個配置使用秒就可以了這樣會盡快地釋放內存資源注意一直在使用的連接是不會斷掉的這個配置只是斷掉了長時間不動的連接

　　query_cache這個功能不要使用現在很多人看到cache這幾個字母就像看到了寶貝這是不唯物主義的mysql的query_cache在每次表數據有變化的時候都會重新清理連至該表的所有緩存如果更新比較頻繁query_cache不但幫不上忙而且還會對效率影響很大這個參數只適合只讀型的數據庫如果非要用也只能用query_cache_type=自行用SQL_CACHE指定一些sql進行緩存

　　max_connections默認為一般情況下是足夠用的但是一般要開大一點開到就可以了能超過的話一般就有效率問題得另找對策光靠增加這個數字不是辦法

　　其它配置可以按默認就可以了個人覺得問題還不是那麼的大提醒一下配置雖然很重要但是在絕大部分情況下都不是效率問題的罪魁禍首mysql是一個數據庫對於數據庫最重要考究的不應是效率而是穩定性和數據准確性

　　機器實在負荷不了

　　如果做了以上調整服務器還是不能承受那就只能通過架構級調整來優化了

　　mysql同步

　　通過mysql同步功能將數據同步到數台從數據庫由主數據庫寫入從數據庫提供讀取

　　我個人不是那麼樂意使用mysql同步因為這個辦法會增加程序的復雜性並常常會引起數據方面的錯誤在高負荷的服務中死機了還可以快速重啟但數據錯誤的話要恢復就比較麻煩

　　加入緩存

　　加入緩存之後就可以解決並發的問題效果很明顯如果是實時系統可以考慮用刷新緩存方式使緩存保持最新

　　在前端加入squid的架構比較提倡使用在命中率比較高的應用中基本上可以解決問題

　　如果是在程序邏輯層裡面進行緩存會增加很多復雜性問題會比較多而且難解決不建議在這一層面進行調整

　　程序架構調整支持同時連接多個數據庫

　　如果web加入緩存後問題還是比較嚴重只能通過程序架構調整把應用拆散用多台的機器同時提供服務

　　如果拆散的話對業務是有少許影響如果業務當中有部分功能必須使用所有的數據可以用一個完整庫+n個分散庫這樣的架構每次修改都在完整庫和分散庫各操作一次或定期整理完整庫

　　當然還有一種最笨的把數據庫整個完完整整的做拷貝然後程序每次都把完整的sql在這些庫執行一遍訪問時輪詢訪問我認為這樣要比mysql同步的方式安全

　　使用 mysql proxy 代理

　　mysql proxy 可以通過代理把數據庫中的各個表分散到數台服務器但是它的問題是沒有能解決熱門表的問題如果熱門內容散在多個表中用這個辦法是比較輕松就能解決問題

　　我沒有用過這個軟件也沒有認真查過不過我對它的功能有一點點懷疑就是它怎麼實現多個表之間的聯合查詢？如果能實現那麼效率如何呢？

　　使用memcachedb

　　數據庫換用支持mysql的memcachedb是可以一試的想法從memcachedb的實現方式和層面來看對數據沒有什麼影響不會對用戶有什麼困擾

　　為我現在因為數據庫方面問題不多沒有試驗過這個玩意不過只要它支持mysql的大部分主要的語法而且本身穩定可用性是無需置疑的

From:http://tw.wingwit.com/Article/program/MySQL/201311/29476.html