我一直是使用mysql這個(gè)數(shù)據(jù)庫(kù)軟件，它工作比較穩(wěn)定，效率也很高。在遇到嚴(yán)重性能問題時(shí)，一般都有這么幾種可能：

1、索引沒有建好；
2、sql寫法過于復(fù)雜；
3、配置錯(cuò)誤；
4、機(jī)器實(shí)在負(fù)荷不了；

1、索引沒有建好

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

在linux下執(zhí)行

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

輸入密碼，如果沒有密碼，則不用-p參數(shù)就可以進(jìn)到客戶端界面中。

看看當(dāng)前的運(yùn)行情況

show full processlist

可以多運(yùn)行幾次

這個(gè)命令可以看到當(dāng)前正在執(zhí)行的sql語句，它會(huì)告知執(zhí)行的sql、數(shù)據(jù)庫(kù)名、執(zhí)行的狀態(tài)、來自的客戶端ip、所使用的賬號(hào)、運(yùn)行時(shí)間等信息

在我的cache后端，這里面大部分時(shí)間是看不到顯示任何sql語句的，我認(rèn)為這樣才算比較正常。如果看到有很多sql語句，那么這臺(tái)mysql就一定會(huì)有性能問題

如果出現(xiàn)了性能問題，則可以進(jìn)行分析：

1、是不是有sql語句卡住了？

這是出現(xiàn)比較多的情況，如果數(shù)據(jù)庫(kù)是采用myisam，那么有可能有一個(gè)寫入的線程會(huì)把數(shù)據(jù)表給鎖定了，如果這條語句不結(jié)束，則其它語句也無法運(yùn)行。

查看processlist里的time這一項(xiàng)，看看有沒有執(zhí)行時(shí)間很長(zhǎng)的語句，要留意這些語句。

2、大量相同的sql語句正在執(zhí)行

如果出現(xiàn)這種情況，則有可能是該sql語句執(zhí)行的效率低下，同樣要留意這些語句。

然后把你所懷疑的語句統(tǒng)統(tǒng)集合一下，用desc（explain）來檢查這些語句。

首先看看一個(gè)正常的desc輸出：

mysql> desc select * from imgs where imgid=1651768337;
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
|  1 | SIMPLE      | imgs  | const | PRIMARY       | PRIMARY | 8       | const |    1 |       |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
1 row in set (0.00 sec)

注意key、rows和Extra這三項(xiàng)，這條語句返回的結(jié)果說明了該sql會(huì)使用PRIMARY主鍵索引來查詢，結(jié)果集數(shù)量為1條，Extra沒 有顯 示，證明沒有用到排序或其他操作。由此結(jié)果可以推斷，mysql會(huì)從索引中查詢imgid=1651768337這條記錄，然后再到真實(shí)表中取出所有字 段，是很簡(jiǎn)單的操作。

key是指明當(dāng)前sql會(huì)使用的索引，mysql執(zhí)行一條簡(jiǎn)單語句時(shí)只能使用到一條索引，注意這個(gè)限制；rows是返回的結(jié)果集大小，結(jié)果集就是使用該索引進(jìn)行一次搜索的所有匹配結(jié)果；Extra一般會(huì)顯示查詢和排序的方式，。

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

mysql> desc select * from imgs where userid=”7mini” order by clicks desc limit 10;
+—-+————-+——-+——+—————+——+———+——+——-+—————————–+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+—-+————-+——-+——+—————+——+———+——+——-+—————————–+
|  1 | SIMPLE      | imgs  | ALL  | NULL          | NULL | NULL    | NULL | 12506 | Using where; Using filesort |
+—-+————-+——-+——+—————+——+———+——+——-+—————————–+
1 row in set (0.00 sec)

這條sql結(jié)果集會(huì)有12506條，用到了filesort，所以執(zhí)行起來會(huì)非常消耗效率的。這時(shí)mysql執(zhí)行時(shí)會(huì)把整個(gè)表掃描一遍，一條一條去 找到匹 配userid=”7mini”的記錄，然后還要對(duì)這些記錄的clicks進(jìn)行一次排序，效率可想而知。真實(shí)執(zhí)行時(shí)如果發(fā)現(xiàn)還比較快的話，那是因?yàn)榉?wù)器 內(nèi)存還足夠?qū)?2506條比較短小的記錄全部讀入內(nèi)存，所以還比較快，但是并發(fā)多起來或者表大起來的話，效率問題就嚴(yán)重了。

這時(shí)我把userid加入索引：

create index userid on imgs (userid);

然后再檢查：

mysql> desc select * from imgs where userid=”7mini” order by clicks desc limit 10;
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
|  1 | SIMPLE      | imgs  | ref  | userid        | userid | 51      | const |    8 | Using where; Using filesort |
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
1 row in set (0.00 sec)

嗯，這時(shí)可以看到mysql使用了userid這個(gè)索引搜索了，用userid索引一次搜索后，結(jié)果集有8條。然后雖然使用了filesort一條一條排序，但是因?yàn)榻Y(jié)果集只有區(qū)區(qū)8條，效率問題得以緩解。

但是，如果我用別的userid查詢，結(jié)果又會(huì)有所不同：

mysql> desc select * from imgs where userid=”admin” order by clicks desc limit 10;
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
|  1 | SIMPLE      | imgs  | ref  | userid        | userid | 51      | const | 2944 | Using where; Using filesort |
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
1 row in set (0.00 sec)

這個(gè)結(jié)果和userid=”7mini”的結(jié)果基本相同，但是mysql用userid索引一次搜索后結(jié)果集的大小達(dá)到2944條，這2944條記 錄都會(huì) 加入內(nèi)存進(jìn)行filesort，效率比起7mini那次來說就差很多了。這時(shí)可以有兩種辦法可以解決，第一種辦法是再加一個(gè)索引和判斷條件，因?yàn)槲抑恍枰? 根據(jù)點(diǎn)擊量取最大的10條數(shù)據(jù)，所以有很多數(shù)據(jù)我根本不需要加進(jìn)來排序，比如點(diǎn)擊量小于10的，這些數(shù)據(jù)可能占了很大部分。

我對(duì)clicks加一個(gè)索引，然后加入一個(gè)where條件再查詢：

create index clicks on imgs(clicks);

mysql> desc select * from imgs where userid=”admin” order by clicks desc limit 10;
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra                       |
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
|  1 | SIMPLE      | imgs  | ref  | userid,clicks | userid | 51      | const | 2944 | Using where; Using filesort |
+—-+————-+——-+——+—————+——–+———+——-+——+—————————–+
1 row in set (0.00 sec)

這時(shí)可以看到possible_keys變成了userid,clicks，possible_keys是可以匹配的所有索引，mysql會(huì)從 possible_keys中自己判斷并取用其中一個(gè)索引來執(zhí)行語句，值得注意的是，mysql取用的這個(gè)索引未必是最優(yōu)化的。這次查詢mysql還是使 用userid這個(gè)索引來查詢的，并沒有按照我的意愿，所以結(jié)果還是沒有什么變化。改一下sql加上use index強(qiáng)制mysql使用clicks索引：

mysql> desc select * from imgs use index (clicks) where userid=’admin’ and clicks>10 order by clicks desc limit 10
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
|  1 | SIMPLE      | imgs  | range | clicks        | clicks | 4       | NULL | 5455 | Using where |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
1 row in set (0.00 sec)

這時(shí)mysql用到了clicks索引進(jìn)行查詢，但是結(jié)果集比userid還要大！看來還要再進(jìn)行限制：

mysql> desc select * from imgs use index (clicks) where userid=’admin’ and clicks>1000 order by clicks desc limit 10
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows | Extra       |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
|  1 | SIMPLE      | imgs  | range | clicks        | clicks | 4       | NULL |  312 | Using where |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
1 row in set (0.00 sec)

加到1000的時(shí)候結(jié)果集變成了312條，排序效率應(yīng)該是可以接受。

不過，采用換索引這種優(yōu)化方式需要取一個(gè)采樣點(diǎn)，比如這個(gè)例子中的1000這個(gè)數(shù)字，這樣，對(duì)userid的每個(gè)數(shù)值，都要去找一個(gè)采樣點(diǎn)，這樣對(duì) 程序來 說是很難辦的。如果按1000取樣的話，那么userid=’7mini’這個(gè)例子中，取到的結(jié)果將不會(huì)是8條，而是2條，給用戶造成了困惑。

當(dāng)然還有另一種辦法，加入雙索引：

create index userid_clicks on imgs (userid, clicks)

mysql> desc select * from imgs where userid=”admin” order by clicks desc limit 10;
+—-+————-+——-+——+———————-+—————+———+——-+——+————-+
| id | select_type | table | type | possible_keys        | key           | key_len | ref   | rows | Extra       |
+—-+————-+——-+——+———————-+—————+———+——-+——+————-+
|  1 | SIMPLE      | imgs  | ref  | userid,userid_clicks | userid_clicks | 51      | const | 2944 | Using where |
+—-+————-+——-+——+———————-+—————+———+——-+——+————-+
1 row in set (0.00 sec)

這時(shí)可以看到，結(jié)果集還是2944條，但是Extra中的filesort不見了。這時(shí)mysql使用userid_clicks這個(gè)索引去查詢， 這不但 能快速查詢到userid=”admin”的所有記錄，并且結(jié)果是根據(jù)clicks排好序的！所以不用再把這個(gè)結(jié)果集讀入內(nèi)存一條一條排序了，效率上會(huì)高 很多。

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

以上是對(duì)索引優(yōu)化的辦法，因?yàn)樵蚩赡軙?huì)比較復(fù)雜，所以寫得比較的長(zhǎng)，一般好好優(yōu)化了索引之后，mysql的效率會(huì)提升n個(gè)檔次，從而也不需要考慮增加機(jī)器來解決問題了。

但是，mysql甚至所有數(shù)據(jù)庫(kù)，可能都不好解決limit的問題。在mysql中，limit 0,10只要索引合適，是沒有問題的，但是limit 100000,10就會(huì)很慢了，因?yàn)閙ysql會(huì)掃描排好序的結(jié)果，然后找到100000這個(gè)點(diǎn)，取出10條返回。要找到100000這個(gè)點(diǎn)，就要掃描 100000條記錄，這個(gè)循環(huán)是比較耗時(shí)的。不知道會(huì)不會(huì)有什么好的算法可以優(yōu)化這個(gè)掃描引擎，我冥思苦想也想不出有什么好辦法。對(duì)于limit，目前直 至比較久遠(yuǎn)的將來，我想只能通過業(yè)務(wù)、程序和數(shù)據(jù)表的規(guī)劃來優(yōu)化，我想到的這些優(yōu)化辦法也都還沒有一個(gè)是萬全之策，往后再討論。

2、sql寫法過于復(fù)雜

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

3、配置錯(cuò)誤

配置里主要參數(shù)是key_buffer、sort_buffer_size/myisam_sort_buffer_size，這兩個(gè)參數(shù)意思是：

key_buffer=128M：全部表的索引都會(huì)盡可能放在這塊內(nèi)存區(qū)域內(nèi)，索引比較大的話就開稍大點(diǎn)都可以，我一般設(shè)為128M，有個(gè)好的建議是把很少用到并且比較大的表想辦法移到別的地方去，這樣可以顯著減少mysql的內(nèi)存占用。
sort_buffer_size=1M：?jiǎn)蝹€(gè)線程使用的用于排序的內(nèi)存，查詢結(jié)果集都會(huì)放進(jìn)這內(nèi)存里，如果比較小，mysql會(huì)多放幾次，所以稍微開大一點(diǎn)就可以了，重要是優(yōu)化好索引和查詢語句，讓他們不要生成太大的結(jié)果集。

另外一些配置：
thread_concurrency=8：這個(gè)配置標(biāo)配=cpu數(shù)量x2
interactive_timeout=30
wait_timeout=30：這兩個(gè)配置使用10-30秒就可以了，這樣會(huì)盡快地釋放內(nèi)存資源，注意：一直在使用的連接是不會(huì)斷掉的，這個(gè)配置只是斷掉了長(zhǎng)時(shí)間不動(dòng)的連接。
query_cache：這個(gè)功能不要使用，現(xiàn)在很多人看到cache這幾個(gè)字母就像看到了寶貝，這是不唯物主義的。mysql的query_cache 在每次表數(shù)據(jù)有變化的時(shí)候都會(huì)重新清理連至該表的所有緩存，如果更新比較頻繁，query_cache不但幫不上忙，而且還會(huì)對(duì)效率影響很大。這個(gè)參數(shù)只 適合只讀型的數(shù)據(jù)庫(kù)，如果非要用，也只能用query_cache_type=2自行用SQL_CACHE指定一些sql進(jìn)行緩存。
max_connections：默認(rèn)為100，一般情況下是足夠用的，但是一般要開大一點(diǎn)，開到400-600就可以了，能超過600的話一般就有效率問題，得另找對(duì)策，光靠增加這個(gè)數(shù)字不是辦法。

其它配置可以按默認(rèn)就可以了，個(gè)人覺得問題還不是那么的大，提醒一下：1、配置雖然很重要，但是在絕大部分情況下都不是效率問題的罪魁禍?zhǔn)住?、mysql是一個(gè)數(shù)據(jù)庫(kù)，對(duì)于數(shù)據(jù)庫(kù)最重要考究的不應(yīng)是效率，而是穩(wěn)定性和數(shù)據(jù)準(zhǔn)確性。

4、機(jī)器實(shí)在負(fù)荷不了

如果做了以上調(diào)整，服務(wù)器還是不能承受，那就只能通過架構(gòu)級(jí)調(diào)整來優(yōu)化了。

1、mysql同步。

通過mysql同步功能將數(shù)據(jù)同步到數(shù)臺(tái)從數(shù)據(jù)庫(kù)，由主數(shù)據(jù)庫(kù)寫入，從數(shù)據(jù)庫(kù)提供讀取。

我個(gè)人不是那么樂意使用mysql同步，因?yàn)檫@個(gè)辦法會(huì)增加程序的復(fù)雜性，并常常會(huì)引起數(shù)據(jù)方面的錯(cuò)誤。在高負(fù)荷的服務(wù)中，死機(jī)了還可以快速重啟，但數(shù)據(jù)錯(cuò)誤的話要恢復(fù)就比較麻煩。

2、加入緩存

加入緩存之后，就可以解決并發(fā)的問題，效果很明顯。如果是實(shí)時(shí)系統(tǒng)，可以考慮用刷新緩存方式使緩存保持最新。

在前端加入squid的架構(gòu)比較提倡使用，在命中率比較高的應(yīng)用中，基本上可以解決問題。

如果是在程序邏輯層里面進(jìn)行緩存，會(huì)增加很多復(fù)雜性，問題會(huì)比較多而且難解決，不建議在這一層面進(jìn)行調(diào)整。

3、程序架構(gòu)調(diào)整，支持同時(shí)連接多個(gè)數(shù)據(jù)庫(kù)

如果web加入緩存后問題還是比較嚴(yán)重，只能通過程序架構(gòu)調(diào)整，把應(yīng)用拆散，用多臺(tái)的機(jī)器同時(shí)提供服務(wù)。

如果拆散的話，對(duì)業(yè)務(wù)是有少許影響，如果業(yè)務(wù)當(dāng)中有部分功能必須使用所有的數(shù)據(jù)，可以用一個(gè)完整庫(kù)+n個(gè)分散庫(kù)這樣的架構(gòu)，每次修改都在完整庫(kù)和分散庫(kù)各操作一次，或定期整理完整庫(kù)。

當(dāng)然，還有一種最笨的，把數(shù)據(jù)庫(kù)整個(gè)完完整整的做拷貝，然后程序每次都把完整的sql在這些庫(kù)執(zhí)行一遍，訪問時(shí)輪詢?cè)L問，我認(rèn)為這樣要比mysql同步的方式安全。

4、使用 mysql proxy 代理

mysql proxy 可以通過代理把數(shù)據(jù)庫(kù)中的各個(gè)表分散到數(shù)臺(tái)服務(wù)器，但是它的問題是沒有能解決熱門表的問題，如果熱門內(nèi)容散在多個(gè)表中，用這個(gè)辦法是比較輕松就能解決問題。

我沒有用過這個(gè)軟件也沒有認(rèn)真查過，不過我對(duì)它的功能有一點(diǎn)點(diǎn)懷疑，就是它怎么實(shí)現(xiàn)多個(gè)表之間的聯(lián)合查詢？如果能實(shí)現(xiàn)，那么效率如何呢？

5、使用memcachedb

數(shù)據(jù)庫(kù)換用支持mysql的memcachedb，是可以一試的想法，從memcachedb的實(shí)現(xiàn)方式和層面來看對(duì)數(shù)據(jù)沒有什么影響，不會(huì)對(duì)用戶有什么困擾。

為我現(xiàn)在因?yàn)閿?shù)據(jù)庫(kù)方面問題不多，沒有試驗(yàn)過這個(gè)玩意。不過，只要它支持mysql的大部分主要的語法，而且本身穩(wěn)定，可用性是無需置疑的。

FROM:http://www./linux/mysql_debug.html