MySQL定位並優化慢查詢sql的詳細實例

1.如何定位並優化慢查詢sql   

a.根據慢日志定位慢查詢sql

SHOW VARIABLES LIKE '%query%' 查詢慢日志相關信息

 

slow_query_log 默認是off關閉的,使用時,需要改為on 打開      

slow_query_log_file 記錄的是慢日志的記錄文件

long_query_time 默認是10S,每次執行的sql達到這個時長,就會被記錄     

SHOW STATUS LIKE '%slow_queries%' 查看慢查詢狀態

Slow_queries 記錄的是慢查詢數量 當有一條sql執行一次比較慢時,這個vlue就是1 (記錄的是本次會話的慢sql條數)

註意:

如何打開慢查詢 : SET GLOBAL slow_query_log = ON;

將默認時間改為1S: SET GLOBAL long_query_time = 1;

(設置完需要重新連接數據庫,PS:僅在這裡改的話,當再次重啟數據庫服務時,所有設置又會自動恢復成默認值,永久改變需去my.ini中改)

b.使用explain等工具分析sql

在要執行的sql前加上explain 例如:EXPLAIN SELECT menu_name FROM t_sys_menu ORDER BY menu_id DESC;

接著看explain的關鍵字段

type:

如果發現type的值是最後兩個中的其中一個時,證明語句需要優化瞭。

extra:

c.修改sql或者盡量讓sql走索引

mysql查詢優化器會根據具體情況自己判斷走哪個索引,不一定是走主鍵(explain中的key可以看到走的哪個key)具體情況根據具體情況來定,當你要強制執行走某一個key時:

在查詢的最後加上 force index(primary); 強制走主鍵的

2.聯合索引的最左匹配原則的成因

簡單說下什麼是最左匹配原則

顧名思義:最左優先,以最左邊的為起點任何連續的索引都能匹配上。同時遇到范圍查詢(>、<、between、like)就會停止匹配。

例如:b = 2 如果建立(a,b)順序的索引,是匹配不到(a,b)索引的;但是如果查詢條件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以,因為優化器會自動調整a,b的順序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引,d是用不到索引的,因為c字段是一個范圍查詢,它之後的字段會停止匹配。

最左匹配原則的原理

最左匹配原則都是針對聯合索引來說的,所以我們有必要瞭解一下聯合索引的原理。瞭解瞭聯合索引,那麼為什麼會有最左匹配原則這種說法也就理解瞭。

我們都知道索引的底層是一顆B+樹,那麼聯合索引當然還是一顆B+樹,隻不過聯合索引的健值數量不是一個,而是多個。構建一顆B+樹隻能根據一個值來構建,因此數據庫依據聯合索引最左的字段來構建B+樹。

例子:假如創建一個(a,b)的聯合索引,那麼它的索引樹是這樣的

可以看到a的值是有順序的,1,1,2,2,3,3,而b的值是沒有順序的1,2,1,4,1,2。所以b = 2這種查詢條件沒有辦法利用索引,因為聯合索引首先是按a排序的,b是無序的。

同時我們還可以發現在a值相等的情況下,b值又是按順序排列的,但是這種順序是相對的。所以最左匹配原則遇上范圍查詢就會停止,剩下的字段都無法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引,因為在a值確定的情況下b是相對有序的,而a>1and b=2,a字段可以匹配上索引,但b值不可以,因為a的值是一個范圍,在這個范圍中b是無序的。

成因:

當通過(col3,col2)這樣的聯合索引去查找時,可以看到也是一個B+樹的結構向下查找,若直接通過col2去查找,無法直接查找到34、77。也就用不到這個聯合索引瞭。

3.索引是建立得越多越好嗎

1.數據量小的表不需要建立索引,建立會增加額外的索引開銷。

2.數據變更需要維護索引,因此更多的索引意味著更多的維護成本。

3.更多的索引意味著也需要更多的空間。

總結

到此這篇關於MySQL定位並優化慢查詢sql的文章就介紹到這瞭,更多相關MySQL定位優化慢查詢sql內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: