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 (