mysql效能的檢查和調優方法

我一直是使用mysql這個資料庫軟體，它工作比較穩定，效率也很高。在遇到嚴重效能問題時，一般都有這麼幾種可能：

1、索引沒有建好；
2、sql寫法過於複雜；
3、配置錯誤；
4、機器實在負荷不了；

1、索引沒有建好

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

在linux下執行

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

輸入密碼，如果沒有密碼，則不用-p引數就可以進到客戶端介面中。

看看當前的執行情況

show full processlist

可以多執行幾次

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

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

如果出現了效能問題，則可以進行分析：

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

這是出現比較多的情況，如果資料庫是採用myisam，那麼有可能有一個寫入的執行緒會把資料表給鎖定了，如果這條語句不結束，則其它語句也無法執行。

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

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

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

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

首先看看一個正常的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這三項，這條語句返回的結果說明了該sql會使用PRIMARY主鍵索引來查詢，結果集數量為1條，Extra沒 有顯 示，證明沒有用到排序或其他操作。由此結果可以推斷，mysql會從索引中查詢imgid=1651768337這條記錄，然後再到真實表中取出所有字 段，是很簡單的操作。

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

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

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

這時我把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)

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

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

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)

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

我對clicks加一個索引，然後加入一個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)

這時可以看到possible_keys變成了userid,clicks，possible_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>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)

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

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的時候結果集變成了312條，排序效率應該是可以接受。

不過，採用換索引這種最佳化方式需要取一個取樣點，比如這個例子中的1000這個數字，這樣，對userid的每個數值，都要去找一個取樣點，這樣對 程式來 說是很難辦的。如果按1000取樣的話，那麼userid=’7mini’這個例子中，取到的結果將不會是8條，而是2條，給使用者造成了困惑。

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

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)

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

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

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

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

2、sql寫法過於複雜

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

3、配置錯誤

配置裡主要引數是key_buffer、sort_buffer_size/myisam_sort_buffer_size，這兩個引數意思是：

key_buffer=128M：全部表的索引都會盡可能放在這塊記憶體區域內，索引比較大的話就開稍大點都可以，我一般設為128M，有個好的建議是把很少用到並且比較大的表想辦法移到別的地方去，這樣可以顯著減少mysql的記憶體佔用。
sort_buffer_size=1M：單個執行緒使用的用於排序的記憶體，查詢結果集都會放進這記憶體裡，如果比較小，mysql會多放幾次，所以稍微開大一點就可以了，重要是最佳化好索引和查詢語句，讓他們不要生成太大的結果集。

另外一些配置：
thread_concurrency=8：這個配置標配=cpu數量x2
interactive_timeout=30
wait_timeout=30：這兩個配置使用10-30秒就可以了，這樣會盡快地釋放記憶體資源，注意：一直在使用的連線是不會斷掉的，這個配置只是斷掉了長時間不動的連線。
query_cache：這個功能不要使用，現在很多人看到cache這幾個字母就像看到了寶貝，這是不唯物主義的。mysql的query_cache 在每次表資料有變化的時候都會重新清理連至該表的所有快取，如果更新比較頻繁，query_cache不但幫不上忙，而且還會對效率影響很大。這個引數只 適合只讀型的資料庫，如果非要用，也只能用query_cache_type=2自行用SQL_CACHE指定一些sql進行快取。
max_connections：預設為100，一般情況下是足夠用的，但是一般要開大一點，開到400-600就可以了，能超過600的話一般就有效率問題，得另找對策，光靠增加這個數字不是辦法。

其它配置可以按預設就可以了，個人覺得問題還不是那麼的大，提醒一下：1、配置雖然很重要，但是在絕大部分情況下都不是效率問題的罪魁禍首。2、mysql是一個資料庫，對於資料庫最重要考究的不應是效率，而是穩定性和資料準確性。

4、機器實在負荷不了

如果做了以上調整，伺服器還是不能承受，那就只能透過架構級調整來最佳化了。

1、mysql同步。

透過mysql同步功能將資料同步到數臺從資料庫，由主資料庫寫入，從資料庫提供讀取。

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

2、加入快取

加入快取之後，就可以解決併發的問題，效果很明顯。如果是實時系統，可以考慮用重新整理快取方式使快取保持最新。

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

如果是在程式邏輯層裡面進行快取，會增加很多複雜性，問題會比較多而且難解決，不建議在這一層面進行調整。

3、程式架構調整，支援同時連線多個資料庫

如果web加入快取後問題還是比較嚴重，只能透過程式架構調整，把應用拆散，用多臺的機器同時提供服務。

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

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

4、使用 mysql proxy 代理

mysql proxy 可以透過代理把資料庫中的各個表分散到數臺伺服器，但是它的問題是沒有能解決熱門表的問題，如果熱門內容散在多個表中，用這個辦法是比較輕鬆就能解決問題。

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

5、使用memcachedb

資料庫換用支援mysql的memcachedb，是可以一試的想法，從memcachedb的實現方式和層面來看對資料沒有什麼影響，不會對使用者有什麼困擾。

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