作者：京東科技 宋慧超
一、前言
最近通過 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 列
值含義SIMPLE不包含子查詢或者 UNION 操作的查詢（簡單查詢）PRIMARY查詢中如果包含任何子查詢，那么最外層的查詢則被標記為 PRIMARYSUBQUERYselect 列表中的子查詢DEPENDENT SUBQUERY依賴外部結果的子查詢UNIONunion 操作的第二個或者之后的查詢值為 unionDEPENDENT UNION當 union 作為子查詢時，第二或是第二個后的查詢的值為 select_typeUNION RESULTunion 產生的結果集DERIVED出現在 from 子句中的子查詢（派生表）
例如：查詢學習人數大于 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 語句從哪個表中獲取數據
值含義<table name>展示數據庫表名（如果表取了別名顯示別名）<unionM, N>由 ID 為 M、N 查詢 union 產生的結果集<dirived N> / <subquery N>由 ID 為 N 的查詢產生的結果（通常也是一個子查詢的臨時表）
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
值含義systemconst 連接類型的特例，當查詢的表只有一行時使用const表中有且只有一個匹配的行時使用，如隊逐漸或唯一索引的查詢，這是效率最高的連接方式eq_ref唯一索引或主鍵查詢，對應每個索引建，表中只有一條記錄與之匹配【A 表掃描每一行 B 表只有一行匹配滿足】ref_or_null類似于 ref 類型的查詢，但是附加了對 NULL 值列的查詢index_merge表示使用了索引合并優化方法range索引范圍掃描，常見于 between、>、< 這樣的查詢條件indexFULL index Scan 全索引掃描，同 ALL 的區別是，遍歷的是索引樹ALLFULL TABLE Scan 全表掃描，效率最差的連接方式
• 如果 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 列
包括了不適合在其他列中所顯示的額外信息。
值含義Distinct優化 distinct 操作，在找到第一匹配的元組后即停止找同樣值得動作Not exists使用 not exisits 來優化查詢Using filesort使用文件來進行排序，通常會出現在 order by 或 group by 查詢中Using index使用了覆蓋索引進行查詢【查詢所需要的信息用所用來獲取，不需要對表進行訪問】Using temporaryMySQL 需要使用臨時表來處理，常見于排序、子查詢和分組查詢Using where需要在 MySQL 服務器層使用 where 條件來過濾數據select tables optimized away直接通過索引來獲取數據，不用訪問表
四、SQL 索引失效
?最左前綴原則：要求建立索引的一個列都不能缺失，否則會出現索引失效。
?索引列上的計算，函數、類型轉換（列類型是字符串在條件中需要使用引號，否則不走索引）、均會導致索引失效。
?索引列中使用 is not null 會導致索引列失效。
?索引列中使用 like 查詢的前以 % 開頭會導致索引列失效。
?索引列用 or 連接時會導致索引失效。
五、實際優化慢 SQL 中遇到問題
下面是在慢 SQL 優化過程中所遇到的一些問題。
•MySQL 查詢到的數據排序是穩定的么？
•force_index 的使用方式？
• 為什么有時候 order by id 會導致索引失效？
•........ 未完整理中......
六、總結
通過本次對慢 SQL 的優化的需求進而發現有關 SQL 語句執行順序、執行計劃、索引失效場景、底層 SQL 語句執行原理相關知識還存在盲區，得益于此次需求的開發，有深入的對相關知識進行學習和總結。接下來會對 SQL 底層是如何執行 SQL 語句
