天天躁日日躁狠狠躁AV麻豆-天天躁人人躁人人躁狂躁-天天澡夜夜澡人人澡-天天影视香色欲综合网-国产成人女人在线视频观看-国产成人女人视频在线观看

mysql 性能的檢查和優(yōu)化方法

1、索引沒有建好;
2、sql寫法過于復雜;
3、配置錯誤;
4、機器實在負荷不了;
1、索引沒有建好
如果看到mysql消耗的cpu很大,可以用mysql的client工具來檢查。
在linux下執(zhí)行
/usr/local/mysql/bin/mysql -hlocalhost -uroot -p
輸入密碼,如果沒有密碼,則不用-p參數(shù)就可以進到客戶端界面中。
看看當前的運行情況
show full processlist
可以多運行幾次
這個命令可以看到當前正在執(zhí)行的sql語句,它會告知執(zhí)行的sql、數(shù)據(jù)庫名、執(zhí)行的狀態(tài)、來自的客戶端ip、所使用的帳號、運行時間等信息
在我的cache后端,這里面大部分時間是看不到顯示任何sql語句的,我認為這樣才算比較正常。如果看到有很多sql語句,那么這臺mysql就一定會有性能問題
如果出現(xiàn)了性能問題,則可以進行分析:
1、是不是有sql語句卡住了?
這是出現(xiàn)比較多的情況,如果數(shù)據(jù)庫是采用myisam,那么有可能有一個寫入的線程會把數(shù)據(jù)表給鎖定了,如果這條語句不結束,則其它語句也無法運行。
查看processlist里的time這一項,看看有沒有執(zhí)行時間很長的語句,要留意這些語句。
2、大量相同的sql語句正在執(zhí)行
如果出現(xiàn)這種情況,則有可能是該sql語句執(zhí)行的效率低下,同樣要留意這些語句。
然后把你所懷疑的語句統(tǒng)統(tǒng)集合一下,用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主鍵索引來查詢,結果集數(shù)量為1條,Extra沒有顯示,證明沒有用到排序或其他操作。由此結果可以推斷,mysql會從索引中查詢imgid=1651768337這條記錄,然后再到真實表中取出所有字段,是很簡單的操作。
key是指明當前sql會使用的索引,mysql執(zhí)行一條簡單語句時只能使用到一條索引,注意這個限制;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,所以執(zhí)行起來會非常消耗效率的。這時mysql執(zhí)行時會把整個表掃描一遍,一條一條去找到匹配userid="7mini"的記錄,然后還要對這些記錄的clicks進行一次排序,效率可想而知。真實執(zhí)行時如果發(fā)現(xiàn)還比較快的話,那是因為服務器內存還足夠將12506條比較短小的記錄全部讀入內存,所以還比較快,但是并發(fā)多起來或者表大起來的話,效率問題就嚴重了。
這時我把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一條一條排序,但是因為結果集只有區(qū)區(qū)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那次來說就差很多了。這時可以有兩種辦法可以解決,第一種辦法是再加一個索引和判斷條件,因為我只需要根據(jù)點擊量取最大的10條數(shù)據(jù),所以有很多數(shù)據(jù)我根本不需要加進來排序,比如點擊量小于10的,這些數(shù)據(jù)可能占了很大部分。
我對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中自己判斷并取用其中一個索引來執(zhí)行語句,值得注意的是,mysql取用的這個索引未必是最優(yōu)化的。這次查詢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條,排序效率應該是可以接受。
不過,采用換索引這種優(yōu)化方式需要取一個采樣點,比如這個例子中的1000這個數(shù)字,這樣,對userid的每個數(shù)值,都要去找一個采樣點,這樣對程序來說是很難辦的。如果按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"的所有記錄,并且結果是根據(jù)clicks排好序的!所以不用再把這個結果集讀入內存一條一條排序了,效率上會高很多。
但是用多字段索引這種方式有個問題,如果查詢的sql種類很多的話,就得好好規(guī)劃一下了,否則索引會建得非常多,不但會影響到數(shù)據(jù)insert和update的效率,而且數(shù)據(jù)表也容易損壞。
以上是對索引優(yōu)化的辦法,因為原因可能會比較復雜,所以寫得比較的長,一般好好優(yōu)化了索引之后,mysql的效率會提升n個檔次,從而也不需要考慮增加機器來解決問題了。
但是,mysql甚至所有數(shù)據(jù)庫,可能都不好解決limit的問題。在mysql中,limit 0,10只要索引合適,是沒有問題的,但是limit 100000,10就會很慢了,因為mysql會掃描排好序的結果,然后找到100000這個點,取出10條返回。要找到100000這個點,就要掃描100000條記錄,這個循環(huán)是比較耗時的。不知道會不會有什么好的算法可以優(yōu)化這個掃描引擎,我冥思苦想也想不出有什么好辦法。對于limit,目前直至比較久遠的將來,我想只能通過業(yè)務、程序和數(shù)據(jù)表的規(guī)劃來優(yōu)化,我想到的這些優(yōu)化辦法也都還沒有一個是萬全之策,往后再討論。
2、sql寫法過于復雜
sql寫法假如用到一些特殊的功能,比如groupby、或者多表聯(lián)合查詢的話,mysql用到什么方式來查詢也可以用desc來分析,我這邊用復雜sql的情況還不算多,所以不常分析,暫時就沒有好的建議。
3、配置錯誤
配置里主要參數(shù)是key_buffer、sort_buffer_size/myisam_sort_buffer_size,這兩個參數(shù)意思是:
key_buffer=128M:全部表的索引都會盡可能放在這塊內存區(qū)域內,索引比較大的話就開稍大點都可以,我一般設為128M,有個好的建議是把很少用到并且比較大的表想辦法移到別的地方去,這樣可以顯著減少mysql的內存占用。
sort_buffer_size=1M:單個線程使用的用于排序的內存,查詢結果集都會放進這內存里,如果比較小,mysql會多放幾次,所以稍微開大一點就可以了,重要是優(yōu)化好索引和查詢語句,讓他們不要生成太大的結果集。
另外一些配置:
thread_concurrency=8:這個配置標配=cpu數(shù)量x2
interactive_timeout=30
wait_timeout=30:這兩個配置使用10-30秒就可以了,這樣會盡快地釋放內存資源,注意:一直在使用的連接是不會斷掉的,這個配置只是斷掉了長時間不動的連接。
query_cache:這個功能不要使用,現(xiàn)在很多人看到cache這幾個字母就像看到了寶貝,這是不唯物主義的。mysql的query_cache在每次表數(shù)據(jù)有變化的時候都會重新清理連至該表的所有緩存,如果更新比較頻繁,query_cache不但幫不上忙,而且還會對效率影響很大。這個參數(shù)只適合只讀型的數(shù)據(jù)庫,如果非要用,也只能用query_cache_type=2自行用SQL_CACHE指定一些sql進行緩存。
max_connections:默認為100,一般情況下是足夠用的,但是一般要開大一點,開到400-600就可以了,能超過600的話一般就有效率問題,得另找對策,光靠增加這個數(shù)字不是辦法。
其它配置可以按默認就可以了,個人覺得問題還不是那么的大,提醒一下:1、配置雖然很重要,但是在絕大部分情況下都不是效率問題的罪魁禍首。2、mysql是一個數(shù)據(jù)庫,對于數(shù)據(jù)庫最重要考究的不應是效率,而是穩(wěn)定性和數(shù)據(jù)準確性。
4、機器實在負荷不了
如果做了以上調整,服務器還是不能承受,那就只能通過架構級調整來優(yōu)化了。
1、mysql同步。
通過mysql同步功能將數(shù)據(jù)同步到數(shù)臺從數(shù)據(jù)庫,由主數(shù)據(jù)庫寫入,從數(shù)據(jù)庫提供讀取。
我個人不是那么樂意使用mysql同步,因為這個辦法會增加程序的復雜性,并常常會引起數(shù)據(jù)方面的錯誤。在高負荷的服務中,死機了還可以快速重啟,但數(shù)據(jù)錯誤的話要恢復就比較麻煩。
2、加入緩存
加入緩存之后,就可以解決并發(fā)的問題,效果很明顯。如果是實時系統(tǒng),可以考慮用刷新緩存方式使緩存保持最新。
在前端加入squid的架構比較提倡使用,在命中率比較高的應用中,基本上可以解決問題。
如果是在程序邏輯層里面進行緩存,會增加很多復雜性,問題會比較多而且難解決,不建議在這一層面進行調整。
3、程序架構調整,支持同時連接多個數(shù)據(jù)庫
如果web加入緩存后問題還是比較嚴重,只能通過程序架構調整,把應用拆散,用多臺的機器同時提供服務。
如果拆散的話,對業(yè)務是有少許影響,如果業(yè)務當中有部分功能必須使用所有的數(shù)據(jù),可以用一個完整庫+n個分散庫這樣的架構,每次修改都在完整庫和分散庫各操作一次,或定期整理完整庫。
當然,還有一種最笨的,把數(shù)據(jù)庫整個完完整整的做拷貝,然后程序每次都把完整的sql在這些庫執(zhí)行一遍,訪問時輪詢訪問,我認為這樣要比mysql同步的方式安全。
4、使用 mysql proxy 代理
mysql proxy 可以通過代理把數(shù)據(jù)庫中的各個表分散到數(shù)臺服務器,但是它的問題是沒有能解決熱門表的問題,如果熱門內容散在多個表中,用這個辦法是比較輕松就能解決問題。
我沒有用過這個軟件也沒有認真查過,不過我對它的功能有一點點懷疑,就是它怎么實現(xiàn)多個表之間的聯(lián)合查詢?如果能實現(xiàn),那么效率如何呢?
5、使用memcachedb
數(shù)據(jù)庫換用支持mysql的memcachedb,是可以一試的想法,從memcachedb的實現(xiàn)方式和層面來看對數(shù)據(jù)沒有什么影響,不會對用戶有什么困擾。
為我現(xiàn)在因為數(shù)據(jù)庫方面問題不多,沒有試驗過這個玩意。不過,只要它支持mysql的大部分主要的語法,而且本身穩(wěn)定,可用性是無需置疑的。

php技術mysql 性能的檢查和優(yōu)化方法,轉載需保留來源!

鄭重聲明:本文版權歸原作者所有,轉載文章僅為傳播更多信息之目的,如作者信息標記有誤,請第一時間聯(lián)系我們修改或刪除,多謝。

主站蜘蛛池模板: 麻豆免费高清完整版 | 日韩欧美精品有码在线播放免费 | 国内九一激情白浆发布 | 国产又爽又黄又不遮挡视频 | 国产 精品 亚洲 欧美 高清 | 中文日韩亚洲欧美字幕 | 迈开腿让我看下你的小草莓声音 | 国产免费午夜高清 | 国产成A人片在线观看 | 国产互换后人妻的疯狂VIDEO | 国产免费久久爱久久啪 | 久久精品国产免费播高清无卡 | 无套日出白浆在线播放 | 野草观看免费高清视频 | 欧美色妞AV重囗味视频 | 中文字幕在线不卡精品视频99 | 成人在线小视频 | 宝贝好紧好爽再搔一点试視頻 | 花蝴蝶在线观看免费中文版高清 | 在线观看成人免费视频 | 大胸美女裸身色诱网站 | 国产人A片在线乱码视频 | 福利视频一二三在线观看 | 国产成人啪精视频精东传媒网站 | 毛片免费在线播放 | 日韩国产精品欧美一区二区 | 国产伦精品一区二区三区免费观看 | 精品网站一区二区三区网站 | 女朋友的妈妈在线观看 | 免费一级毛片在线观看 | 神马电影dy888午夜我不卡 | 玩50岁四川熟女大白屁股直播 | 与邻居换娶妻子2在线观看 瑜伽牲交AV | 成人毛片手机版免费看 | 国语自产偷成人精品视频 | 玩弄放荡人妻一区二区三区 | 色哦色哦哦色天天综合 | 亚洲精品沙发午睡系列 | 亚洲国产精品一区二区动图 | 中文字幕亚洲乱码熟女在线萌芽 | 国产色综合色产在线视频 |