MySQL定位并優(yōu)化慢查詢sql的詳細(xì)實(shí)例

更新時(shí)間：2021年12月24日 12:50:18 作者：X凱

mysql記錄下查詢超過指定時(shí)間的語句,被稱為慢查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL定位并優(yōu)化慢查詢sql的相關(guān)資料,文中通過圖文介紹的非常詳細(xì),需要的朋友可以參考下

1.如何定位并優(yōu)化慢查詢sql　　　

a.根據(jù)慢日志定位慢查詢sql

SHOW VARIABLES LIKE '%query%' 查詢慢日志相關(guān)信息

slow_query_log 默認(rèn)是off關(guān)閉的，使用時(shí)，需要改為on 打開　　　　　　

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

long_query_time 默認(rèn)是10S，每次執(zhí)行的sql達(dá)到這個(gè)時(shí)長(zhǎng)，就會(huì)被記錄　　　　　

SHOW STATUS LIKE '%slow_queries%' 查看慢查詢狀態(tài)

Slow_queries 記錄的是慢查詢數(shù)量當(dāng)有一條sql執(zhí)行一次比較慢時(shí)，這個(gè)vlue就是1 （記錄的是本次會(huì)話的慢sql條數(shù)）

注意：

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

將默認(rèn)時(shí)間改為1S： SET GLOBAL long_query_time = 1;

（設(shè)置完需要重新連接數(shù)據(jù)庫(kù)，PS：僅在這里改的話，當(dāng)再次重啟數(shù)據(jù)庫(kù)服務(wù)時(shí)，所有設(shè)置又會(huì)自動(dòng)恢復(fù)成默認(rèn)值，永久改變需去my.ini中改）

b.使用explain等工具分析sql

在要執(zhí)行的sql前加上explain 例如：EXPLAIN SELECT menu_name FROM t_sys_menu ORDER BY menu_id DESC;

接著看explain的關(guān)鍵字段

type：

如果發(fā)現(xiàn)type的值是最后兩個(gè)中的其中一個(gè)時(shí)，證明語句需要優(yōu)化了。

extra：

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

mysql查詢優(yōu)化器會(huì)根據(jù)具體情況自己判斷走哪個(gè)索引，不一定是走主鍵（explain中的key可以看到走的哪個(gè)key）具體情況根據(jù)具體情況來定，當(dāng)你要強(qiáng)制執(zhí)行走某一個(gè)key時(shí)：

在查詢的最后加上 force index(primary); 強(qiáng)制走主鍵的

2.聯(lián)合索引的最左匹配原則的成因

簡(jiǎn)單說下什么是最左匹配原則

顧名思義：最左優(yōu)先，以最左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上。同時(shí)遇到范圍查詢(>、<、between、like)就會(huì)停止匹配。

例如：b = 2 如果建立(a,b)順序的索引，是匹配不到(a,b)索引的；但是如果查詢條件是a = 1 and b = 2或者a=1(又或者是b = 2 and b = 1)就可以，因?yàn)閮?yōu)化器會(huì)自動(dòng)調(diào)整a,b的順序。再比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，因?yàn)閏字段是一個(gè)范圍查詢，它之后的字段會(huì)停止匹配。

最左匹配原則的原理

最左匹配原則都是針對(duì)聯(lián)合索引來說的，所以我們有必要了解一下聯(lián)合索引的原理。了解了聯(lián)合索引，那么為什么會(huì)有最左匹配原則這種說法也就理解了。

我們都知道索引的底層是一顆B+樹，那么聯(lián)合索引當(dāng)然還是一顆B+樹，只不過聯(lián)合索引的健值數(shù)量不是一個(gè)，而是多個(gè)。構(gòu)建一顆B+樹只能根據(jù)一個(gè)值來構(gòu)建，因此數(shù)據(jù)庫(kù)依據(jù)聯(lián)合索引最左的字段來構(gòu)建B+樹。

例子：假如創(chuàng)建一個(gè)（a,b)的聯(lián)合索引，那么它的索引樹是這樣的

可以看到a的值是有順序的，1，1，2，2，3，3，而b的值是沒有順序的1，2，1，4，1，2。所以b = 2這種查詢條件沒有辦法利用索引，因?yàn)槁?lián)合索引首先是按a排序的，b是無序的。

同時(shí)我們還可以發(fā)現(xiàn)在a值相等的情況下，b值又是按順序排列的，但是這種順序是相對(duì)的。所以最左匹配原則遇上范圍查詢就會(huì)停止，剩下的字段都無法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因?yàn)樵赼值確定的情況下b是相對(duì)有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因?yàn)閍的值是一個(gè)范圍，在這個(gè)范圍中b是無序的。