一文帶你搞懂如何最佳化慢SQL

作者：京東科技 宋慧超

一、前言

最近透過SGM監控發現有兩個SQL的執行時間佔該任務總執行時間的90%，透過對該SQL進行分析和最佳化的過程中，又重新對SQL語句的執行順序和SQL語句的執行計劃進行了系統性的學習，整理的相關學習和總結如下；

二、SQL語句執行順序

要想最佳化慢SQL語句首先需要了解SQL語句的執行順序，SQL語句中的各關鍵詞執行順序如下：

◦首先執行from、join 來確定表之間的連線關係，得到初步的資料。

◦然後利用where關鍵字後面的條件對符合條件的語句進行篩選。

from&join&where：用於確定要查詢的表的範圍，涉及到哪些表。

選擇一張表，然後用join連線：

from table1 join table2 on table1.id=table2.id

選擇多張表，用where做關聯條件：

   from table1,table2 where table1.id=table2.id

最終會得到滿足關聯條件的兩張表的資料，不加關聯條件會出現笛卡爾積。

◦然後利用group by對資料進行分組。

按照SQL語句中的分組條件對資料進行分組，但是不會篩選資料。

下面用按照id的奇偶進行分組：

◦然後分組後的資料分別執行having中的普通篩選或者聚合函式篩選。

having&where

having中可以是普通條件的篩選，也能是聚合函式，而where中只能是普通函式；一般情況下，有having可以不寫where，把where的篩選放在having裡，SQL語句看上去更絲滑。

使用where再group by ： 先把不滿足where條件的資料刪除，再去分組。

使用group by 在having：先分組再刪除不滿足having條件的資料。（該兩種幾乎沒有區別）

比如舉例如下：100/2=50，此時我們把100拆分(10+10+10+10+10…)/2=5+5+5+…+5=50,只要篩選條件沒變，即便是分組了也得滿足篩選條件，所以where後group by 和group by再having是不影響結果的！

不同的是，having語法支援聚合函式,其實having的意思就是針對每組的條件進行篩選。我們之前看到了普通的篩選條件是不影響的，但是having還支援聚合函式，這是where無法實現的。

當前的資料分組情況

執行having的篩選條件，可以使用聚合函式。篩選掉工資小於各組平均工資的having salary<avg(salary)：

然後再根據我們要的資料進行select，普通欄位查詢或者聚合函式查詢，如果是聚合函式，select的查詢結果會增加一條欄位。

分組結束之後，我們再執行select語句，因為聚合函式是依賴於分組的，聚合函式會單獨新增一個查詢出來的欄位，這裡我們兩個id重複了，我們就保留一個id，重複欄位名需要指向來自哪張表，否則會出現唯一性問題。最後按照使用者名稱去重。

select employee.id,distinct name,salary, avg(salary)

將各組having之後的資料再合併資料。

◦然後將查詢到的資料結果利用distinct關鍵字去重。

◦然後合併各個分組的查詢結果，按照order by的條件進行排序。

比如這裡按照id排序。如果此時有limit那麼查詢到相應的我們需要的記錄數時，就不繼續往下查了。

◦最後使用limit做分頁。

記住limit是最後查詢的，為什麼呢？假如我們要查詢薪資最低的三個資料，如果在排序之前就擷取到3個資料。實際上查詢出來的不是最低的三個資料而是前三個資料了，記住這一點。

假如SQL語句執行順序是先做limit再執行order by，執行結果為3500,5500,7000了（正確SQL執行的最低工資的是3500,5500,5500）。

SQL查詢時需要遵循的兩個順序：

1、關鍵字的順序是不能顛倒的。

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT

2、select語句的執行順序（在MySQL和Oracle中，select執行順序基本相同）。

FROM > WHERE > GROUP BY > HAVING > SELECT的欄位 > DISTINCT > ORDER BY > LIMIT

以SQL語句舉例，那麼該語句的關鍵字順序和執行順序如下：

SELECT DISTINCT player_id, player_name, count(*) as num #順序5
FROM player JOIN team ON player.team_id = team.team_id #順序1
WHERE height > 1.80 #順序2
GROUP BY player.team_id #順序3
HAVING num > 2 #順序4
ORDER BY num DESC #順序6
LIMIT 2 #順序7

三、SQL執行計劃

• 為什麼要學習SQL的執行計劃？

因為一個sql的執行計劃可以告訴我們很多關於如何最佳化sql的資訊 。透過一個sql計劃，如何訪問表中的資料 （是使用全表掃描還是索引查詢？）一個表中可能存在多個不同的索引，表中的型別是什麼、是否子查詢、關聯查詢等…

• 如何獲取SQL的執行計劃？

在SQL語句前加上explain關鍵詞皆可以得到相應的執行計劃。其中：在MySQL8.0中是支援對select/delete/inster/replace/update語句來分析執行計劃，而MySQL5.6前只支援對select語句分析執行計劃。 replace語句是跟instert語句非常類似，只是插入的資料和表中存在的資料（存在主鍵或者唯一索引）衝突的時候**，****replace**語句會把原來的資料替換新插入的資料，表中不存在唯一的索引或主鍵，則直接插入新的資料。

•如何分析SQL語句的執行計劃？

下面對SQL語句執行計劃中的各個欄位的含義進行介紹並舉例說明。

◦id列

id標識查詢執行的順序，當id相同時，由上到下分析執行，當id不同時，由大到小分析執行。

id列中的值只有兩種情況，一組數字（說明查詢的SQL語句對資料物件的操作順序）或者NULL（代表資料由另外兩個查詢的union操作後所產生的結果集）。

explain
select course_id,class_name,level_name,title,study_cnt
from imc_course a
join imc_class b on b.class_id=a.class_id
join imc_level c on c.level_id =a.level_id
where study_cnt > 3000

返回3行結果，並且ID值是一樣的。由上往下讀取sql的執行計劃，第一行是table c表作為驅動表 ，等於是以C表為基礎來進行迴圈巢狀的一個關聯查詢。 （4 *100*1 =400 總共掃描400行等到資料）

◦select_type列

例如：查詢學習人數大於3000, 合併 課程是MySQL的記錄。

EXPLAIN
SELECT 
course_id,class_name,level_name,title,study_cnt
FROM imc_course a
join imc_class b on b.class_id =a.class_id
join imc_level c on c.level_id = a.level_id
WHERE study_cnt > 3000

union

SELECT course_id,class_name,level_name,title,study_cnt
FROM imc_course a
join imc_class b on b.class_id = a.class_id
join imc_level c on c.level_id = a.level_id
WHERE class_name ='MySQL'

分析資料表：先看id等於2

id=2 則是查詢mysql課程的sql資訊，分別是b,a,c 3個表，是union操作，selecttype為是UNION。

id=1 為是查詢學習人數3000人的sql資訊，是primary操作的結果集，分別是c,a,b3個表，select_type為PRIMARY。

最後一行是NULL, select_type是UNION RESULT 代表是2個sql 組合的結果集。

◦table列

指明是該SQL語句從哪個表中獲取資料

EXPLAIN
SELECT
course id,class name,level name,title,study cnt
FROM imc course a
join imc class b on b.class id =a.class id
join imc level c on c.level id = a.level id
WHERE study cnt > 3000

union

SELECT course id,class name,level name,title,study _cnt
FROM imc course a
join imc class b on b.class id = a.class id
join imc level c on c.level id = a.level id
WHERE class name ='MySOL'

◦type列

注意: 在MySQL中不一定是使用JOIN才算是關聯查詢，實際上MySQL會認為每一個查詢都是連線查詢，就算是查詢一個表，對MySQL來說也是關聯查詢。

type的取值是體現了MySQL訪問資料的一種方式。type列的值按照效能高到低排列 system \> const \> eq_ref \> ref \> ref_or_null \> index_merge \> range \> index \> ALL

•如果where like “MySQL%”，type型別為？

雖然class_name 加了索引 ，但是使用where的like% 右統配， 所以會走索引範圍掃描。

EXPLAIN
SELECT
course id,class name,level name,title,study_cnt
FROM imc course a
join imc class b on b.class id= a.class id
join imc level c on c.level id = a.level id
WHERE class namelike'MySQL%'

•如果where like “%MySQL%”，type型別為？

雖然class_name 加了索引 ，但是使用where的%like% 左右統配， 所以會走全索引掃描，如果不加索引的話，左右統配會走全表掃描。

EXPLAIN
SELECT
course id,class name,level name,title,study_cnt
FROM imc course a
join imc class b on b.class id= a.class id
join imc level c on c.level id = a.level id
WHERE class namelike'%MySQL%'

◦possible_key、key列

possible_keys說明表可能用到了哪些索引，而key是指實際上使用到的索引。基於查詢列和過濾條件進行判斷。查詢出來都會被列出來，但是不一定會是使用到。

如果在表中沒有可用的索引，那麼key列 展示NULL，possible_keys是NULL，這說明查詢到覆蓋索引。

◦key_len列

實際用的的索引使用的位元組數。

注意，在聯合索引中，如果有3列，那麼總位元組是長度是100個位元組的話，那麼key_len值資料可能少於100位元組，比如30個位元組，這就說明了查詢中並沒有使用聯合索引的所有列。而只是利用到某一些列或者2列。

key_len的長度是由表中的定義的欄位長度來計算的，並不是儲存的實際長度，所以滿足資料最短的實際欄位儲存，因為會直接影響到生成執行計劃的生成 。

◦ref列

指出那些列或常量被用於索引查詢

◦rows列

（ 有2個含義）1、根據統計資訊預估的掃描行數。

2、另一方面是關聯查詢內嵌的次數，每獲取匹配一個值都要對目標表查詢，所以迴圈次數越多效能越差。

因為掃描行數的值是預估的，所以並不準確。

◦filtered列

表示返回結果的行數佔需讀取行數的百分比。

filtered列跟rows列是有關聯的，是返回預估符合條件的資料集，再去取的行的百分比。也是預估的值。數值越高查詢效能越好。

◦Extra列

包括了不適合在其他列中所顯示的額外資訊。

四、SQL索引失效

◦最左字首原則：要求建立索引的一個列都不能缺失，否則會出現索引失效。

◦索引列上的計算，函式、型別轉換（列型別是字串在條件中需要使用引號，否則不走索引）、均會導致索引失效。

◦索引列中使用is not null會導致索引列失效。

◦索引列中使用like查詢的前以%開頭會導致索引列失效。

◦索引列用or連線時會導致索引失效。

五、實際最佳化慢SQL中遇到問題

下面是在慢SQL最佳化過程中所遇到的一些問題。

•MySQL查詢到的資料排序是穩定的麼？

•force_index的使用方式？

•為什麼有時候order by id會導致索引失效？

•........未完整理中......

六、總結

透過本次對慢SQL的最佳化的需求進而發現有關SQL語句執行順序、執行計劃、索引失效場景、底層SQL語句執行原理相關知識還存在盲區，得益於此次需求的開發，有深入的對相關知識進行學習和總結。接下來會對SQL底層是如何執行SQL語句