MySQL慢查詢&分析SQL執(zhí)行效率淺談

如何在MySQL中查找效率慢的SQL語句呢?這可能是困擾很多人的一個問題,MySQL通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的SQL 語句玻靡,用--log-slow-queries[=file_name]選項啟動時冠桃,mysqld 會寫一個包含所有執(zhí)行時間超過long_query_time 秒的SQL語句的日志文件,通過查看這個日志文件定位效率較低的SQL 咧织。

MySQL慢查詢定義

分析MySQL語句查詢性能的方法除了使用 EXPLAIN 輸出執(zhí)行計劃嗓袱,還可以讓MySQL記錄下查詢超過指定時間的語句,我們將超過指定時間的SQL語句查詢稱為“慢查詢”习绢。

慢查詢的體現(xiàn)

慢查詢主要體現(xiàn)在慢上渠抹,通常意義上來講,只要返回時間大于 >1 sec上的查詢都可以稱為慢查詢。慢查詢會導(dǎo)致CPU梧却,內(nèi)存消耗過高奇颠。數(shù)據(jù)庫服務(wù)器壓力陡然過大,那么大部分情況來講放航,肯定是由某些慢查詢導(dǎo)致的大刊。

開啟慢查詢的方法:

MySQL5.0以上的版本可以支持將執(zhí)行比較慢的SQL語句記錄下來。

mysql> show variables like 'long%';

long_query_time | 10.000000

mysql> set long_query_time=1;

mysql> show variables like 'slow%';

slow_launch_time ? ?| 2

slow_query_log ? ? ?| ON

slow_query_log_file | /tmp/slow.log

mysql> set global slow_query_log='ON'

字段解釋

long_query_time 當(dāng)SQL語句執(zhí)行時間超過此數(shù)值時三椿,就會被記錄到日志中缺菌,建議設(shè)置為1或者更短。

slow_query_log 這個參數(shù)設(shè)置為ON搜锰,可以捕獲執(zhí)行時間超過一定數(shù)值的SQL語句伴郁。

slow_query_log_file 記錄日志的文件名。

一旦slow_query_log變量被設(shè)置為ON蛋叼,MySQL會立即開始記錄焊傅。

/etc/my.cnf ? 里面可以設(shè)置上面MySQL全局變量的初始值。

分析SQL執(zhí)行效率淺談

一狈涮、MySQL數(shù)據(jù)庫有幾個配置選項可以幫助我們及時捕獲低效SQL語句

slow_query_log

這個參數(shù)設(shè)置為ON狐胎,可以捕獲執(zhí)行時間超過一定數(shù)值的SQL語句。

long_query_time

當(dāng)SQL語句執(zhí)行時間超過此數(shù)值時歌馍,就會被記錄到日志中握巢,建議設(shè)置為1或者更短。

slow_query_log_file

記錄日志的文件名松却。

log_queries_not_using_indexes

這個參數(shù)設(shè)置為ON暴浦,可以捕獲到所有未使用索引的SQL語句,盡管這個SQL語句有可能執(zhí)行得挺快晓锻。

二歌焦、檢測MySQL中sql語句的效率的方法

1. 通過查詢?nèi)罩?/b>

1)Windows下開啟MySQL慢查詢

MySQL在Windows系統(tǒng)中的配置文件一般是是my.ini找到[mysqld]下面加上

代碼如下

log slow queries = F:/MySQL/log/mysqlslowquery.log

long_query_time = 2

2)Linux下啟用MySQL慢查詢

MySQL在Linux系統(tǒng)中的配置文件一般是是my.cnf找到[mysqld]下面加上

代碼如下

log slow queries = /data/mysqldata/slowquery.log

long_query_time = 2

說明:

log slow queries = /data/mysqldata/slowquery.log

為慢查詢?nèi)罩敬娣诺奈恢茫话氵@個目錄要有MySQL的運行帳號的可寫權(quán)限砚哆,一般都將這個目錄設(shè)置為MySQL的數(shù)據(jù)存放目錄独撇;

long_query_time = 2中的2表示查詢超過兩秒才記錄;

2. show processlist 命令

SHOW PROCESSLIST顯示哪些線程正在運行躁锁。您也可以使用mysqladmin processlist語句得到此信息纷铣。

例如如圖:

各列的含義和用途:

Id列:一個標識,你要kill一個語句的時候很有用灿里,用命令殺掉此查詢 /*/mysqladmin kill 進程號关炼。

User列:顯示單前用戶程腹,如果不是root匣吊,這個命令就只顯示你權(quán)限范圍內(nèi)的sql語句。

Host列:顯示這個語句是從哪個ip的哪個端口上發(fā)出的。用于追蹤出問題語句的用戶色鸳。

db列:顯示這個進程目前連接的是哪個數(shù)據(jù)庫社痛。

Command列:顯示當(dāng)前連接的執(zhí)行的命令,一般就是休眠(sleep)命雀,查詢(query)蒜哀,連接(connect)。

Time列:此這個狀態(tài)持續(xù)的時間吏砂,單位是秒撵儿。

State列:顯示使用當(dāng)前連接的sql語句的狀態(tài),很重要的列狐血,后續(xù)會有所有的狀態(tài)的描述淀歇,請注意,state只是語句執(zhí)行中的某一個狀態(tài)匈织,一個 sql語句浪默,以查詢?yōu)槔赡苄枰?jīng)過copying to tmp table缀匕,Sorting result纳决,Sending data等狀態(tài)才可以完成

Info列;顯示這個sql語句乡小,因為長度有限阔加,所以長的sql語句就顯示不全,但是一個判斷問題語句的重要依據(jù)满钟。

這個命令中最關(guān)鍵的就是state列掸哑,MySQL列出的狀態(tài)主要有以下幾種:

Checking table:正在檢查數(shù)據(jù)表(這是自動的)。

Closing tables:正在將表中修改的數(shù)據(jù)刷新到磁盤中零远,同時正在關(guān)閉已經(jīng)用完的表苗分。這是一個很快的操作,如果不是這樣的話牵辣,就應(yīng)該確認磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負中摔癣。

Connect Out:復(fù)制從服務(wù)器正在連接主服務(wù)器。

Copying to tmp table on disk:由于臨時結(jié)果集大于tmp_table_size纬向,正在將臨時表從內(nèi)存存儲轉(zhuǎn)為磁盤存儲以此節(jié)省內(nèi)存择浊。

Creating tmp table:正在創(chuàng)建臨時表以存放部分查詢結(jié)果。

deleting from main table:服務(wù)器正在執(zhí)行多表刪除中的第一部分逾条,剛刪除第一個表琢岩。

deleting from reference tables:服務(wù)器正在執(zhí)行多表刪除中的第二部分,正在刪除其他表的記錄师脂。

Flushing tables:正在執(zhí)行FLUSH TABLES担孔,等待其他線程關(guān)閉數(shù)據(jù)表江锨。

Killed:發(fā)送了一個kill請求給某線程,那么這個線程將會檢查kill標志位糕篇,同時會放棄下一個kill請求啄育。MySQL會在每次的主循環(huán)中檢查kill標志位,不過有些情況下該線程可能會過一小段才能死掉拌消。如果該線程程被其他線程鎖住了挑豌,那么kill請求會在鎖釋放時馬上生效。

Locked:被其他查詢鎖住了墩崩。

Sending data:正在處理SELECT查詢的記錄氓英,同時正在把結(jié)果發(fā)送給客戶端。

Sorting for group:正在為GROUP BY做排序鹦筹。

Sorting for order:正在為ORDER BY做排序债蓝。

Opening tables:這個過程應(yīng)該會很快,除非受到其他因素的干擾盛龄。例如饰迹,在執(zhí)ALTER TABLE或LOCK TABLE語句行完以前,數(shù)據(jù)表無法被其他線程打開余舶。正嘗試打開一個表啊鸭。

Removing duplicates:正在執(zhí)行一個SELECT DISTINCT方式的查詢,但是MySQL無法在前一個階段優(yōu)化掉那些重復(fù)的記錄匿值。因此饭耳,MySQL需要再次去掉重復(fù)的記錄棘催,然后再把結(jié)果發(fā)送給客戶端。

Reopen table:獲得了對一個表的鎖,但是必須在表結(jié)構(gòu)修改之后才能獲得這個鎖克婶。已經(jīng)釋放鎖立美,關(guān)閉數(shù)據(jù)表盾似,正嘗試重新打開數(shù)據(jù)表氓涣。

Repair by sorting:修復(fù)指令正在排序以創(chuàng)建索引。

Repair with keycache:修復(fù)指令正在利用索引緩存一個一個地創(chuàng)建新索引达传。它會比Repair by sorting慢些篙耗。

Searching rows for update:正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關(guān)的記錄之前就完成了宪赶。

Sleeping:正在等待客戶端發(fā)送新請求.

System lock:正在等待取得一個外部的系統(tǒng)鎖宗弯。如果當(dāng)前沒有運行多個mysqld服務(wù)器同時請求同一個表,那么可以通過增加--skip-external-locking參數(shù)來禁止外部系統(tǒng)鎖搂妻。

Upgrading lock:INSERT DELAYED正在嘗試取得一個鎖表以插入新記錄蒙保。

Updating:正在搜索匹配的記錄,并且修改它們欲主。

User Lock:正在等待GET_LOCK()邓厕。

Waiting for tables:該線程得到通知逝嚎,數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了,需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)邑狸。然后,為了能的重新打開數(shù)據(jù)表涤妒,必須等到所有其他線程關(guān)閉這個表单雾。以下幾種情況下會產(chǎn)生這個通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。

waiting for handler insert:INSERT DELAYED已經(jīng)處理完了所有待處理的插入操作她紫,正在等待新的請求硅堆。

大部分狀態(tài)對應(yīng)很快的操作,只要有一個線程保持同一個狀態(tài)好幾秒鐘贿讹,那么可能是有問題發(fā)生了渐逃,需要檢查一下。

還有其他的狀態(tài)沒在上面中列出來民褂,不過它們大部分只是在查看服務(wù)器是否有存在錯誤是才用得著茄菊。

三、explain來了解SQL執(zhí)行的狀態(tài)

explain顯示了mysql如何使用索引來處理select語句以及連接表赊堪∶嬷常可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句。

使用方法哭廉,在select語句前加上explain就可以了脊僚,例如下:

explain select c.title, cc.content form comment_content cc, comment c where cc.id=c.id

結(jié)果如圖:

EXPLAIN列的解釋

table列:顯示這一行的數(shù)據(jù)是關(guān)于哪張表的

type列:這是重要的列,顯示連接使用了何種類型遵绰。從最好到最差的連接類型為const辽幌、eq_reg、ref椿访、range乌企、index和ALL

possible_keys 列:顯示可能應(yīng)用在這張表中的索引。如果為空成玫,沒有可能的索引逛犹。可以為相關(guān)的域從WHERE語句中選擇一個合適的語句

key列:實際使用的索引梁剔。如果為NULL虽画,則沒有使用索引。很少的情況下荣病,MYSQL會選擇優(yōu)化不足的索引码撰。這種情況下,可以在SELECT語句 中使用USE INDEX(indexname)來強制使用一個索引或者用IGNORE INDEX(indexname)來強制MYSQL忽略索引

key_len列:使用的索引的長度个盆。在不損失精確性的情況下脖岛,長度越短越好

ref列:顯示索引的哪一列被使用了朵栖,如果可能的話,是一個常數(shù)

rows列:MYSQL認為必須檢查的用來返回請求數(shù)據(jù)的行數(shù)

Extra列:關(guān)于MYSQL如何解析查詢的額外信息柴梆。

最后MySQL優(yōu)化建議

索引優(yōu)化陨溅,最簡單粗暴的辦法,給查詢語句添加復(fù)合索引绍在,但不是最好的方式

將大表拆成小的匯總表

重在實踐门扇,MySQL優(yōu)化器在很多情況下不能給出,最快的實現(xiàn)方式

避免在大表上的group by偿渡,order by臼寄,offset 操作,除非你知道如何優(yōu)化的前提下

SQL WHERE查詢條件溜宽,盡量按照添加的索引順序來寫

本文作者:李洋(點融黑幫)吉拳,點融網(wǎng)FinTech團隊后端開發(fā)攻城獅,5年開發(fā)經(jīng)驗适揉,平時喜歡關(guān)注一些互聯(lián)網(wǎng)相關(guān)資訊以及技術(shù)留攒。業(yè)余時間喜歡跑步,旅游嫉嘀,看書稼跳。

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個濱河市吃沪,隨后出現(xiàn)的幾起案子汤善,更是在濱河造成了極大的恐慌,老刑警劉巖票彪,帶你破解...
    沈念sama閱讀 207,113評論 6 481
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件红淡,死亡現(xiàn)場離奇詭異,居然都是意外死亡降铸,警方通過查閱死者的電腦和手機在旱,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,644評論 2 381
  • 文/潘曉璐 我一進店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來推掸,“玉大人桶蝎,你說我怎么就攤上這事×鲁” “怎么了登渣?”我有些...
    開封第一講書人閱讀 153,340評論 0 344
  • 文/不壞的土叔 我叫張陵,是天一觀的道長毡泻。 經(jīng)常有香客問我胜茧,道長,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 55,449評論 1 279
  • 正文 為了忘掉前任呻顽,我火速辦了婚禮雹顺,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘廊遍。我一直安慰自己嬉愧,他們只是感情好,可當(dāng)我...
    茶點故事閱讀 64,445評論 5 374
  • 文/花漫 我一把揭開白布喉前。 她就那樣靜靜地躺著没酣,像睡著了一般。 火紅的嫁衣襯著肌膚如雪被饿。 梳的紋絲不亂的頭發(fā)上四康,一...
    開封第一講書人閱讀 49,166評論 1 284
  • 那天搪搏,我揣著相機與錄音狭握,去河邊找鬼。 笑死疯溺,一個胖子當(dāng)著我的面吹牛论颅,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播囱嫩,決...
    沈念sama閱讀 38,442評論 3 401
  • 文/蒼蘭香墨 我猛地睜開眼恃疯,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了墨闲?” 一聲冷哼從身側(cè)響起今妄,我...
    開封第一講書人閱讀 37,105評論 0 261
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎鸳碧,沒想到半個月后盾鳞,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 43,601評論 1 300
  • 正文 獨居荒郊野嶺守林人離奇死亡瞻离,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點故事閱讀 36,066評論 2 325
  • 正文 我和宋清朗相戀三年腾仅,在試婚紗的時候發(fā)現(xiàn)自己被綠了。 大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片套利。...
    茶點故事閱讀 38,161評論 1 334
  • 序言:一個原本活蹦亂跳的男人離奇死亡推励,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出肉迫,到底是詐尸還是另有隱情验辞,我是刑警寧澤,帶...
    沈念sama閱讀 33,792評論 4 323
  • 正文 年R本政府宣布喊衫,位于F島的核電站受神,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏格侯。R本人自食惡果不足惜鼻听,卻給世界環(huán)境...
    茶點故事閱讀 39,351評論 3 307
  • 文/蒙蒙 一财著、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧撑碴,春花似錦撑教、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,352評論 0 19
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至亿卤,卻和暖如春愤兵,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背排吴。 一陣腳步聲響...
    開封第一講書人閱讀 31,584評論 1 261
  • 我被黑心中介騙來泰國打工秆乳, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留,地道東北人钻哩。 一個月前我還...
    沈念sama閱讀 45,618評論 2 355
  • 正文 我出身青樓屹堰,卻偏偏與公主長得像,于是被迫代替她去往敵國和親街氢。 傳聞我的和親對象是個殘疾皇子扯键,可洞房花燭夜當(dāng)晚...
    茶點故事閱讀 42,916評論 2 344

推薦閱讀更多精彩內(nèi)容