時(shí)間：2015-06-28 00:00:00 來(lái)源：IT貓撲網(wǎng) 作者：網(wǎng)管聯(lián)盟 我要評(píng)論(0)
我一直是使用mysql這個(gè)數(shù)據(jù)庫(kù)軟件，它工作比較穩(wěn)定，效率也很高。在遇到嚴(yán)重性能問(wèn)題時(shí)，一般都有這么幾種可能：
1、索引沒(méi)有建好;
2、sql寫(xiě)法過(guò)于復(fù)雜;
3、配置錯(cuò)誤;
4、機(jī)器實(shí)在負(fù)荷不了;
1、索引沒(méi)有建好
如果看到mysql消耗的cpu很大，可以用mysql的client工具來(lái)檢查。
在linux下執(zhí)行
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p
輸入密碼，如果沒(méi)有密碼，則不用-p參數(shù)就可以進(jìn)到客戶(hù)端界面中。
看看當(dāng)前的運(yùn)行情況
show full processlist
可以多運(yùn)行幾次
這個(gè)命令可以看到當(dāng)前正在執(zhí)行的sql語(yǔ)句，它會(huì)告知執(zhí)行的sql、數(shù)據(jù)庫(kù)名、執(zhí)行的狀態(tài)、來(lái)自的客戶(hù)端ip、所使用的帳號(hào)、運(yùn)行時(shí)間等信息
在我的cache后端，這里面大部分時(shí)間是看不到顯示任何sql語(yǔ)句的，我認(rèn)為這樣才算比較正常。如果看到有很多sql語(yǔ)句，那么這臺(tái)mysql就一定會(huì)有性能問(wèn)題
如果出現(xiàn)了性能問(wèn)題，則可以進(jìn)行分析：
1、是不是有sql語(yǔ)句卡住了?
這是出現(xiàn)比較多的情況，如果數(shù)據(jù)庫(kù)是采用myisam，那么有可能有一個(gè)寫(xiě)入的線(xiàn)程會(huì)把數(shù)據(jù)表給鎖定了，如果這條語(yǔ)句不結(jié)束，則其它語(yǔ)句也無(wú)法運(yùn)行。
查看processlist里的time這一項(xiàng)，看看有沒(méi)有執(zhí)行時(shí)間很長(zhǎng)的語(yǔ)句，要留意這些語(yǔ)句。
2、大量相同的sql語(yǔ)句正在執(zhí)行
如果出現(xiàn)這種情況，則有可能是該sql語(yǔ)句執(zhí)行的效率低下，同樣要留意這些語(yǔ)句。
然后把你所懷疑的語(yǔ)句統(tǒng)統(tǒng)集合一下，用desc(explain)來(lái)檢查這些語(yǔ)句。
首先看看一個(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)，這條語(yǔ)句返回的結(jié)果說(shuō)明了該sql會(huì)使用PRIMARY主鍵索引來(lái)查詢(xún)，結(jié)果集數(shù)量為1條，Extra沒(méi)有顯示，證明沒(méi)有用到排序或其他操作。由此結(jié)果可以推斷，mysql會(huì)從索引中查詢(xún)imgid=1651768337這條記錄，然后再到真實(shí)表中取出所有字段，是很簡(jiǎn)單的操作。
key是指明當(dāng)前sql會(huì)使用的索引，mysql執(zhí)行一條簡(jiǎn)單語(yǔ)句時(shí)只能使用到一條索引，注意這個(gè)限制;rows是返回的結(jié)果集大小，結(jié)果集就是使用該索引進(jìn)行一次搜索的所有匹配結(jié)果;Extra一般會(huì)顯示查詢(xún)和排序的方式，。
如果沒(méi)有使用到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í)行起來(lái)會(huì)非常消耗效率的。這時(shí)mysql執(zhí)行時(shí)會(huì)把整個(gè)表掃描一遍，一條一條去找到匹配userid="7mini"的記錄，然后還要對(duì)這些記錄的clicks進(jìn)行一次排序，效率可想而知。真實(shí)執(zhí)行時(shí)如果發(fā)現(xiàn)還比較快的話(huà)，那是因?yàn)榉?wù)器內(nèi)存還足夠?qū)?2506條比較短小的記錄全部讀入內(nèi)存，所以還比較快，但是并發(fā)多起來(lái)或者表大起來(lái)的話(huà)，效率問(wèn)題就嚴(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條，效率問(wèn)題得以緩解。
但是，如果我用別的userid查詢(xún)，結(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那次來(lái)說(shuō)就差很多了。這時(shí)可以有兩種辦法可以解決，第一種辦法是再加一個(gè)索引和判斷條件，因?yàn)槲抑恍枰鶕?jù)點(diǎn)擊量取最大的10條數(shù)據(jù)，所以有很多數(shù)據(jù)我根本不需要加進(jìn)來(lái)排序，比如點(diǎn)擊量小于10的，這些數(shù)據(jù)可能占了很大部分。
我對(duì)clicks加一個(gè)索引，然后加入一個(gè)where條件再查詢(xún)：
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è)索引來(lái)執(zhí)行語(yǔ)句，值得注意的是，mysql取用的這個(gè)索引未必是最優(yōu)化的。這次查詢(xún)mysql還是使用userid這個(gè)索引來(lái)查詢(xún)的，并沒(méi)有按照我的意愿，所以結(jié)果還是沒(méi)有什么變化。改一下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)行查詢(xún)，但是結(jié)果集比userid還要大!看來(lái)還要再進(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 |
+----+-------------+-------+-------+---------------+
關(guān)鍵詞標(biāo)簽：mysql
相關(guān)閱讀
熱門(mén)文章
Xbox Game Pass
10款MySQL數(shù)據(jù)庫(kù)客戶(hù)端圖形界面管理工具推薦
MySQL常用維護(hù)管理工具
MySQL數(shù)據(jù)庫(kù)啟動(dòng)失敗1067進(jìn)程意外終止的解決辦法總結(jié)
人氣排行 10款MySQL數(shù)據(jù)庫(kù)客戶(hù)端圖形界面管理工具推薦 MySQL數(shù)據(jù)庫(kù)啟動(dòng)失敗1067進(jìn)程意外終止的解決辦法總結(jié) Mysql 1045錯(cuò)誤解決辦法 MySQL服務(wù)器進(jìn)程CPU占用100%解決辦法 MySQL導(dǎo)出導(dǎo)入命令的用例 MySQL連接字符串的實(shí)際操作步驟匯總 MySQL無(wú)法啟動(dòng)、無(wú)法停止各種解決方法總結(jié) 三種常用的MySQL建表語(yǔ)句