如何在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è)余時間喜歡跑步,旅游嫉嘀,看書稼跳。