淺析如何定位，排除和避免MySQL性能故障
首先是如何檢查SQL的效率.
1.善用explain:
設(shè)計(jì)SQL后，應(yīng)使用explain命令檢查SQL，看是否使用到索引，是否存在filesort,重點(diǎn)檢查檢索的行數(shù)（rows）是否太大。
一般來(lái)說(shuō).
rows<1000，是在可接受的范圍內(nèi)的。
rows在1000~1w之間，在密集訪問(wèn)時(shí)可能導(dǎo)致性能問(wèn)題，但如果不是太頻繁的訪問(wèn)（頻率低于1分鐘一次），又難再優(yōu)化的話，可以接受，但需要注意觀察
rows大于1萬(wàn)時(shí)，應(yīng)慎重考慮SQL的設(shè)計(jì)，優(yōu)化SQL，優(yōu)化db，一般來(lái)說(shuō)不允許頻繁運(yùn)行（頻率低于1小時(shí)一次）。
rows達(dá)到10w級(jí)別時(shí)，堅(jiān)決不能做為實(shí)時(shí)運(yùn)行的SQL。但導(dǎo)數(shù)據(jù)場(chǎng)合除外，但導(dǎo)數(shù)據(jù)必須控制好時(shí)間，頻度。
explain SQL語(yǔ)句應(yīng)該是日常開(kāi)發(fā)中的習(xí)慣動(dòng)作，有時(shí)explain出來(lái)的結(jié)果，可能會(huì)出于偏離設(shè)計(jì)的意料之外,所以
**強(qiáng)烈建議在設(shè)計(jì)SQL，尤其是稍微復(fù)雜的SQL時(shí)，一定要在測(cè)試環(huán)境甚至是實(shí)際環(huán)境上預(yù)先進(jìn)行explain**
2.MySQL慢查詢(xún)?nèi)罩?/STRONG>
一般應(yīng)打開(kāi)MySQL的慢查詢(xún)?nèi)罩?在my.cnf中加入log_slow_queries和long_query_time兩個(gè)參數(shù)),會(huì)記錄所有查詢(xún)持續(xù)時(shí)間超過(guò)long_query_time的SQL語(yǔ)句，把這些語(yǔ)句log下來(lái)之后，再一一分析(explain)優(yōu)化。
3.監(jiān)視當(dāng)前進(jìn)程
登陸MySQL,使用show processlist查看正在運(yùn)行的SQL語(yǔ)句，如果正在運(yùn)行的語(yǔ)句太多，運(yùn)行時(shí)間太長(zhǎng)，表示MySQL效率有問(wèn)題。必要的時(shí)候可以將對(duì)應(yīng)的進(jìn)程kill掉。
4.系統(tǒng)命令
使用top/vmstat等系統(tǒng)命令來(lái)檢查MySQL進(jìn)程占用的cpu,內(nèi)存,以及磁盤(pán)IO量。
對(duì)MySQL優(yōu)化的文章很多，這里只提幾點(diǎn)平時(shí)工作中比較常用到的方法。
◆建表時(shí),顯式指定使用innodb數(shù)據(jù)庫(kù)引擎，而不是myisam,myisam引擎的鎖是表鎖,讀鎖和寫(xiě)鎖是互斥的，讀寫(xiě)操作是串行的，鎖沖突會(huì)嚴(yán)重影響并發(fā).而innodb提供行級(jí)鎖，能提供較好的并發(fā)表現(xiàn)，在我們的業(yè)務(wù)場(chǎng)景里，也不會(huì)引起死鎖。
◆善用索引，對(duì)SQL語(yǔ)句where條件里使用到的字段，合理建立索引。雖然對(duì)表建立索引一定程度上會(huì)影響寫(xiě)入效率，但在表數(shù)據(jù)規(guī)模不大，寫(xiě)入壓力不是特別高的情況下，索引帶來(lái)的好處是更多的。
◆當(dāng)SQL語(yǔ)句是由代碼動(dòng)態(tài)生成的，如在運(yùn)行時(shí)根據(jù)用戶(hù)操作加入不同的where參數(shù)，應(yīng)在測(cè)試階段對(duì)SQL生成的典型情況和邊界情況進(jìn)行測(cè)試，看是否有可能造成性能問(wèn)題。并應(yīng)適當(dāng)生成一些日志，供提取最終生成的SQL進(jìn)行效率分析。
◆對(duì)數(shù)據(jù)應(yīng)合理分庫(kù)分表，由應(yīng)用層去動(dòng)態(tài)的選擇庫(kù)和表。MySQL的innodb表雖然理論上可以裝海量的數(shù)據(jù)，但在我們的業(yè)務(wù)場(chǎng)景下，數(shù)據(jù)控制在500w以下會(huì)比較合理，追求性能的話，最好控制在200w以下，合理索引。
◆需要聯(lián)合查詢(xún)時(shí)善用left join/right join而不是直接多表聯(lián)合，怎么用，查manul ^_^
◆盡量不要使用select套select的復(fù)合查詢(xún)，如果能拆開(kāi)，盡量拆開(kāi),多條精悍的SQL,組合起來(lái)可能就是一條龐大的SQL，應(yīng)該避免。
◆善用cache,將不常修改的，數(shù)據(jù)量有限的,又是被密集查詢(xún)的信息，加載到cache里，可以有效的降低數(shù)據(jù)庫(kù)壓力。在一般的業(yè)務(wù)場(chǎng)景里，推薦使用開(kāi)源memcache，簡(jiǎn)單高效。
◆如果一些邏輯可以放到應(yīng)用層去完成，可以考慮放到應(yīng)用層去完成。但如果將SQL邏輯分拆到應(yīng)用層可能導(dǎo)致對(duì)數(shù)據(jù)更頻繁的訪問(wèn)的話，那么需要考慮修改應(yīng)用邏輯，數(shù)據(jù)結(jié)構(gòu)，或回到合理的聯(lián)合查詢(xún)上來(lái)。
比如某些數(shù)據(jù)的排序可以load到php數(shù)組里，再sort.又比如需要查詢(xún)A,B兩個(gè)表，A表里的數(shù)據(jù)是B表里某個(gè)字段的對(duì)照說(shuō)明（如A:t_service表，B.t_task表），A表數(shù)據(jù)量有限，可以做聯(lián)合查詢(xún)，也可以先將A表先load到進(jìn)程或內(nèi)存里，用hash結(jié)構(gòu)cache起來(lái)，再查B表，然后在cache里依次查詢(xún)hash，獲得對(duì)照說(shuō)明。
◆關(guān)于導(dǎo)數(shù)據(jù)和統(tǒng)計(jì)性查詢(xún).導(dǎo)數(shù)據(jù)在計(jì)算和磁盤(pán)io上對(duì)數(shù)據(jù)庫(kù)壓力都會(huì)很大，應(yīng)在時(shí)間和空間上合理分?jǐn)倲?shù)據(jù)庫(kù)壓力如果需要導(dǎo)出批量的特定數(shù)據(jù)做分析，應(yīng)建立專(zhuān)供數(shù)據(jù)分析的數(shù)據(jù)庫(kù)服務(wù)器，或者建立臨時(shí)庫(kù)表，先導(dǎo)出數(shù)據(jù)，再在上面做分析運(yùn)算。
導(dǎo)數(shù)據(jù)等可能引起批量數(shù)據(jù)讀取的操作，應(yīng)建立定時(shí)任務(wù)，在數(shù)據(jù)庫(kù)不繁忙的時(shí)段（凌晨1~7時(shí))運(yùn)行一般的統(tǒng)計(jì)操作，對(duì)實(shí)時(shí)性要求都不會(huì)太高（5~10分鐘以上，甚至一天，一周等），這種數(shù)據(jù)不應(yīng)在每次訪問(wèn)時(shí)運(yùn)行庫(kù)中直接count,group,而是應(yīng)該由定時(shí)任務(wù)導(dǎo)出，建立結(jié)果表或中間結(jié)果表，供最終用戶(hù)使用。
◆生產(chǎn)數(shù)據(jù)庫(kù)上的操作權(quán)限應(yīng)嚴(yán)格控制，而開(kāi)發(fā)人員在生產(chǎn)數(shù)據(jù)庫(kù)上直接運(yùn)行SQL語(yǔ)句，要盡量慎重。
能做到以上這些，基本上可以算MySQL以及相關(guān)系統(tǒng)優(yōu)化入門(mén)，可以保證不要讓我們的數(shù)據(jù)庫(kù)整天累趴下了。
最后，即使做足了功課，也還是要例行的對(duì)數(shù)據(jù)庫(kù)運(yùn)行情況進(jìn)行觀察，監(jiān)控，盡早發(fā)現(xiàn)其性能瓶頸，在未造成危害前解決掉。
【編輯推薦】
