【MySQL筆記】SQL最佳化利器，mysqlsql利器
有MySQL使用經驗的同學在實際項目中可能會遇到SQL慢查詢的情境，有些情境很容易定位問題所在（如單表操作有慢查詢SQL時，仔細check SQL語句通常很容易定位索引問題），而有些複雜業務情境下（如多表聯集查詢幾十個欄位並做group或sort等操作），人工check SQL語句通常很難發現SQL瓶頸根源。這個時候，MySQL提供的explain命令就派上用場了。
本筆記主要對explain的輸出結果做說明，並給出根據explain輸出對SQL做最佳化的思路。
1. EXPLAIN文法及用途
explain命令的文法說明見官網文檔，這裡略過。
該命令主要作用是輸出MySQL的optimizer對SQL的執行計畫，也即，MySQL會解釋如何處理輸入的SQL（是否使用索引，使用哪個索引，多表以什麼順序及什麼關聯欄位做join）。
explain的提示可以協助大家意識到哪些欄位應該建索引，也可以幫大家確認SQL執行時optimizer是否會以合理的順序來join多張表。比如若有類似這樣的SQL語句：
select t1.id, t2.link, t3.detail from t1, t2, t3 where t1.id < 100 and t1.id = t2.base_id and t3.link_sign = t2.sign;
該語句執行時，optimizer不一定會以from列出的表順序來join這3張表，而表的join順序很可能會影響SQL效能。
這種情境下，如果想讓optimizer以from語句列出的表順序做join，有2種方法：
1) 在select關鍵字後添加STRAIGHT_JOIN來提示optimizer按from列出的表順序來join，具體文法見SELECT文檔
2) 調整sql where條件中各表關聯欄位在等號前後的位置
本文下面的內容會說明如何通過explain輸出來確定多表join時optimizer對各表的執行次序，以及如何調整SQL來影響optimizer的執行計畫。
2. EXPLAIN輸出格式說明
explain命令會為SQL中出現的每張表返回一行資訊來說明optimizer將會如何操作這張表，其輸出中列出的表次序也是MySQL實際執行SQL時對各表的處理順序。
MySQL以
nested-loop演算法處理所有的join操作，演算法原理說明在這裡，對認識join的行為有協助，建議理解。
explain針對每張表輸出的每行記錄均包含下面幾個欄位：
下面分別進行說明。
1) id
該欄位標識select語句id，若SQL中只有1個select語句（即使是多表關聯查詢），則該值為1，否則依次遞增；若SQL是union的結果，則該值為NULL。
2) select_type
該欄位說明select語句的類型，其可能的取值如（來自官網文檔）：
其中，simple是最常見的類型，表明SQL只包含1個select語句；derived表明該行代表的資料表（derived table）其實是from子句中包含的子查詢的輸出結果；其餘類型較易理解，閱讀官方文檔即可，這裡不贅述。
3) table
該欄位表明explain輸出的每行所代表的資料集來自哪張表，其值通常是具體的表名，當資料集是union的結果時，其值可能
是<unionM,N>，當資料集來自derived table時，其值可能是<derivedN>。這裡提到的M或N均是id欄位的值。
4) type
該欄位表明各表是如何被join的，其取值比較複雜，詳細可參考官網文檔。這裡只列出最常見的幾種取值。
a. system/const
const表明上述"table"欄位代表的資料集中，最多隻有1行記錄命中本步執行計畫的查詢條件，例如這步執行計畫的sql的where
子句以某張表的primary key或unique index與常數做比較時，該執行計畫對應的type欄位取值就是const。
system只是const值的一個特例，它表示本步執行計畫要操作的資料集中只有1行記錄。
它們只可能出現在單表查詢SQL的type欄位取值中。
b. eq_ref
該值表明本步執行計畫操作的資料集中關聯欄位是索引欄位且
只有1條記錄符合上步執行計畫操作的資料集的關聯條件。
這
是對多表做關聯查詢時，可能得到的最優的join類型（因為它通常表明關聯的欄位是本步執行計畫要操作的表的primary key或unique index）。
c. ref
該值表明本步執行計畫操作的資料集中關聯欄位是索引欄位但
不只有1條記錄符合上步執行計畫操作的資料集的關聯條件。
符合關聯條件的記錄不只1條表明關聯欄位非primary key或unique index，當符合關聯條件的記錄數比較少時，這種join_type='ref'的情境還是比較合理的，但它顯然不如join_type='eq_ref'高效。
d. ref_or_null
該join type類型與ref的情境類似，但它表明MySQL會對包含NULL值的欄位做額外搜尋。例如下面SQL的join type就是ref_or_null：
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
e. index_merge
該值表明MySQL會對本步執行計畫進行index merge最佳化，觸發index merge的SQL通常包含'or'操作，常見執行個體如下：
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key=30;
SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1 AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
f. range
該值表明本步執行計畫
只操作單表且符合查詢條件的記錄不只1條，可能出現在有in或between操作的SQL中。
只限於單表操作環境也是其與前面提到的join_type取值為'ref'情境的區別，因為ref可能出現在單表/多表join操作環境下。
g. ALL
該值表明本步執行計畫會對資料集做全表掃描，這是必須做最佳化的情境。通常可以通過對某些欄位合理建索引來避免全表掃描。
h. index
該值表明MySQL執行本步計劃時掃描的是index tree，而ALL則是掃全表。
它可能在兩種情境下出現：
case1. 本步計劃查詢資料集時select語句需要返回的欄位是該資料集索引欄位的最左首碼匹配集。如table A中已建立含2個欄位的聯合索引(f1, f2)，則select f1 from A where f2 = 'yyy'可能會觸發MySQL掃描index tree，這種情況下，執行計畫的Extra欄位會包含"Using index"來表明它掃描的是index tree，因為f1是(f1, f2)最終首碼匹配集中的1個元素；而select f2 from A where f2 = 'yyy'則會掃描全表。
case2. 本步計劃按照索引順序進行全表掃描來尋找合格資料。這種情況下，執行計畫的Extra欄位不會包含"Using index"，這種全表掃描也是必須最佳化的情境。
5) possible_keys
該欄位的值是可能被MySQL用作索引的欄位，若值為NULL，則沒有欄位會被用作索引，因此查詢效率不會高，這種情況下，需要最佳化資料表的索引結構。
6) key
該欄位的值是MySQL真正用到的索引。
值得注意的是：該欄位的值有可能不是possible_keys列出的候選索引欄位，例如，當前查詢SQL要返回的欄位是資料表某索引欄位的最左首碼匹配欄位，但SQL的where條件中沒有使用資料表的索引欄位，則此時possible_keys可能為NULL，而key欄位的值可能是那個能cover住待查詢欄位的資料表索引欄位，此時，MySQL會掃描索引樹，雖然低效，但比起掃描全表還是要快。這種情境也正是本文前面解釋join_type='index'時提到的case1。
此外，在select語句中藉助"force index或"use index"可以強制MySQL使用possible_keys中列出的候選索引欄位。
7) key_len
該欄位的值表明上述key欄位的length，當MySQL將某聯合索引欄位作為SQL執行時用到的索引時，key_len欄位可以暗示MySQL真正在什麼程度上（多長的最左首碼匹配欄位）使用了該聯合索引。若key欄位的值為NULL，則key_len欄位值也為NULL。
8) ref
該欄位的值表明資料表中的哪列或哪個constants會被用於與key欄位指定的索引做比較。
9) rows
該欄位的值表明MySQL執行該步計劃對應的query時必須掃描的行數。
這個值對於SQL最佳化非常具有參考意義，通常情況下，該值越小查詢效率越高。
10) Extra
該欄位的值包含了MySQL執行query時的其它額外資訊。該欄位可能的取值情況較多，詳細情況可參考官網文檔的說明。
除explain外，MySQL還支援explain extended命令來分析optimizer的執行計畫，後者在輸出結果中多1個filtered欄位，且可以用show warnings語句來分析輸出的extra資訊。
3. 如何根據explain的輸出最佳化SQL
如果理解了explain輸出結果中每個欄位背後的含義，則據此最佳化SQL效能會變得高效且有依據。
在工程實踐中利用explain來trouble shoot低效SQL的思路，跟工程師的經驗和能力有關，這裡推薦幾篇技術資料來拋磚引玉。
1) Oreilly官網中的一篇PDF分享：Explain Demystified
2) slideshare上的一篇分享文檔：Mysql Explain Explained，該文檔循序漸進地解釋了MySQL explain的輸出及據此最佳化SQL的典型思路，值得一讀。
3) 美團官方技術部落格的一篇文章：MySQL索引原理及慢查詢最佳化，文中介紹了B+Tree的原理，並給出了幾個利用explain來最佳化SQL的工程情境，也值得精讀。
【參考資料】
1. MySQL Doc: EXPLAIN Syntax
2. MySQL Doc: EXPLAIN Output Format
3. MySQL Doc: SELECT Syntax about STRAIGHT_JOIN
4. MySQL Doc: Optimizing Queries with EXPLAIN
5. MySQL Doc: Nested-Loop Join Algorithms
6. PDF Presentation from Oreilly.com: Explain Demystified
7. slideshare: Mysql Explain Explained
8. MySQL索引原理及慢查詢最佳化
===================== EOF =====================
