本文基於MySQL 5.7編寫，對於其它版本也適用
(一)執行計劃概述
什麼是執行計劃呢？SQL是一種傻瓜式語言，每一個條件就是一個需求，訪問的順序不同就形成了不同的執行計劃。MySQL必須做出選擇，一次只能有一種訪問路徑，一個訪問路徑就是一個執行計劃。
通常一條SQL有多個執行計劃，那我們如何選擇？MySQL資料庫與Oracle一樣，使用的是基於開銷(cost)的優化器策略，那種執行開銷更低，就意味著效能更好，速度更快，MySQL就選擇哪一種。
(二)執行計劃的檢視
MySQL資料庫的執行計劃可以通過explain關鍵字檢視，使用explain可以檢視SELECT,DELETE,INSERT,REPLACE,UPDATE語句的執行計劃。對於SELECT語句，還可以使用SHOW WARNINGS檢視額外的執行計劃資訊。可以在資料庫中檢視explain的幫助：
mysql> help explain Name: 'EXPLAIN' Description: Syntax: {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: { EXTENDED | PARTITIONS | FORMAT = format_name } format_name: { TRADITIONAL | JSON } explainable_stmt: { SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement }
需要注意的是：
1.在早期的MySQL版本中，使用EXTENDED檢視擴充套件資訊，目前預設已經啟用了擴充套件資訊的輸出，因此該引數顯得多餘了，在MySQL 8.0中已經移除該引數。
2.在早期版本中，分割槽資訊是使用EXPLAIN PARTITIONS輸出的，目前已經預設開啟了分割槽資訊的輸出，該引數也已經不再需要，在MySQL 8.0中已經移除該引數。
3.不能在同一個EXPLAIN中同時使用EXTENDED和PARTITIONS關鍵字，這2個關鍵字都不能與FORMAT關鍵字一起使用。
FORMAT引數用於選擇輸出格式，一共有2種輸出格式：
-- TRADITIONAL ：以表格顯示輸出，預設模式
-- JSON ：以json格式輸出
此外，在MySQL 8.0中還提供了TREE方式輸出，這裡暫時不作了解，後面單獨說明。
總結一下，EXPLAIN的語法看著較為複雜，實則非常簡單。在去除過時引數後，真正可選的引數只有一個FORMAT=json，其它引數都不用選。所以最終執行計劃有2種輸出形式：
-- 以表格格式輸出執行計劃，預設方式 EXPLAIN sql_stmt -- 以json格式輸出執行計劃 EXPLAIN FORMAT=JSON sql_stmt
(三)執行計劃解釋
（3.1）執行計劃基礎資訊
這裡我們以下面查詢的執行計劃為例，來解釋執行計劃中各個列的含義。
首先以TRADITIONAL格式檢視執行計劃：
mysql> explain select empno,ename,job from dept a join emp b where a.deptno = b.deptno; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | a | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 4 | 100.00 | Using index | | 1 | SIMPLE | b | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 10.00 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set, 1 warning (0.00 sec)
再以JSON格式檢視執行計劃：
mysql> explain format = json select empno,ename,job from dept a join emp b where a.deptno = b.deptno;
… 結果略 …
可以看到，兩種格式輸出的資訊基本相同，但是也存在不一樣的地方，個人覺得最大的區別在於：josn格式的執行計劃把cost給展示出來了，MySQL優化器是基於cost選擇執行計劃的，檢視cost對於調優很重要。但是，在實際的使用過程中，我們往往會以表格的形式檢視執行計劃，因為表格形式的執行計劃較為簡練，便於我們檢視。本文在講解執行計劃時，也只使用表格格式。
這裡解釋各個列的含義：
- id：查詢識別符號。在所有組中，id值越大，優先順序越高，越先執行，id如果相同，可以認為是一組，從上往下順序執行；
例子1：id相同，執行順序從上到下，下面例子的執行順序為：t1 --> t2 --> t3
例子2：id不同，執行順序為id從大到小，下面例子的順序為：t3 --> t2 –> t1
例子3：id相同又不同。id相同，可以認為一組，從上往下執行，在所有組中，id值越大，優先順序越高，越先執行。下面例子的執行順序為：t1 --> <derived2> --> t2
- select_type：select_type可選的引數較多，如下：
select值 含義
---------------------- -------------------------------------
SIMPLE 簡單的select查詢，未使用UNION和子查詢
PRIMARY 查詢中包含任何複雜的子部分，則被標記為PRIMARY，PRIMARY為最外層查詢，最後執行
UNION 第2個SELECT在UNION之後，則被標記為UNION
DEPENDENT UNION 含有UNION查詢的第二個或最後一個表，依賴外部的查詢
UNION RESULT UNION結果
SUBQUERY 在SELECT或WHERE中包含的子查詢
DEPENDENT SELECT 子查詢中的第一個SELECT，依賴外部的查詢
DERIVED 衍生表，衍生表是FROM子句中子查詢的內部名稱
MATERIALIZED 物化子查詢
UNCACHEABLE SUBQUERY 子查詢，其結果無法快取，必須針對外部查詢的每一行進行評估
UNCACHEABLE UNION 在UNION裡的第二個或最後一個表屬於不可快取的子查詢
- table：輸出所使用的表名稱，也可以是：
-- <unionM,N>：該行是id為M和N的行的並集
-- <derivedN>：該行是id為N的行的派生表
-- <subqueryN>：該行是物化子查詢的結果
以這個執行計劃為例，訪問順序為：t1 --> derivered2 --> t2。其中”derived2”中的2是t1的id值。
- partitions：該引數用於記錄使用的分割槽資訊，NULL表示該表不是分割槽表
create table t_part_table ( id int primary key, col2 varchar(20) ) PARTITION by range(id) ( partition p100 values less than(100), partition p200 values less than(200), partition p300 values less than(300), partition p400 values less than(400), partition p500 values less than(500), partition p600 values less than(600), partition p700 values less than(700), partition p800 values less than(800), partition p900 values less than(900), partition p_max values less than MAXVALUE ); CREATE DEFINER=`root`@`%` PROCEDURE `p_insert_part_table`() BEGIN #Routine body goes here... DECLARE str1 varchar(30); DECLARE i int; set i = 1; while i <= 2000 do set str1 = substring(md5(rand()),1,15); insert into t_part_table(id,col2) values(i,str1); set i = i + 1; end while; END
/使用分割槽的樣例如下：
- type：連線型別，見後面"執行計劃連線型別type"
- possible_keys：在該查詢中，MySQL可能使用的索引，如果此列是NULL，則沒有相關的索引，在這種情況下，需要檢查WHERE字句，以確定是否適合建立索引
- key：MySQL實際使用的索引。在大多數情況下，key中的值都在possible_key裡面，但也會出現possible_key不存在該值，但key裡面存在的情
- key_len：顯示索引使用的位元組數，key_len顯示的值為索引欄位的最大長度，並非實際使用長度，越短越好
- ref：ref列顯示哪些列或者常量與key中的索引進行比較，以從表中選擇行
例子1：這裡顯示常量與主鍵進行比較，選擇相應的行
例子2：這裡在查詢t2、t3表的時候，使用主鍵進行查詢，並且使用t1.id列與主鍵進行比較過濾，選擇合適的列
- rows：MySQL查詢需要遍歷的行數，對於innodb表，可能並不總是準確的。這裡需要特別注意，Oracle資料庫的執行計劃裡面也有rows列，不過代表結果的行數，含義不一樣
- filtered:被條件過濾的行數百分比。最大值為100，表示沒有行過濾，值從100減小表示過濾增加。rows表示檢查的行數，rows * filtered/100表示過濾後的行數，也就是與下表進行連線的行
- Extra：執行計劃的額外資訊，見後面"執行計劃額外資訊Extra"
（3.2）執行計劃連線型別type
explain的type列表示表的連線型別，從最佳到最差型別如下(其中黑體部分是常見的重點型別)：
System --> const --> eq_ref --> ref --> fulltext --> ref_or_unll --> index_merge --> unique_subquery --> index_subquery --> range --> index --> ALL
這裡解釋各個型別的含義：
- system : 該表只有一行，這是const連線的特殊情況，平時不會出現，可以不用重點注意
- const：該表最多隻有一個匹配行，該行在查詢開始時讀取。因為只有一行，所以優化器的其餘部分可以將這一行中的值做為常量，因為它值讀取一次。const在基於主鍵或者唯一性索引比較時使用。
- eq_ref：與驅動表的連線查詢，後表(被驅動表)僅讀取一行資料，當被驅動表存在主鍵索引或者unique+not null時使用，eq_ref用於使用"="運算的索引列（參考：https://oomake.com/question/1081106）
- ref：與驅動表的連線查詢，後表(被驅動表)讀取一行或多行資料。ref用於僅使用key的最左字首，或者說key不是PAIMARY KAY或UNIQUE索引。換句話說，如果連線無法根據KEY選擇單個行，則使用ref，否則使用eq_ref。ref可以用於使用"="或者"<=>"運算子進行的比較
- fulltext：使用FULLTEXT進行連線
- ref_or_null：這種方式類似於ref，但是MySQL會額外搜尋包含NULL值的行
- index_merge：索引合併優化，把多個索引合併為一個使用
- unique_subquery：該型別替換eq_ref形式下的IN子查詢，子查詢中最多返回一個值，提高查詢效率：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
- index_subquery：該型別類似於unique_subquery，它用來替代子查詢，子查詢中返回值的個數不確
value IN (SELECT key_column FROM single_table WHERE some_expr)
- range：使用索引去檢索一個範圍的行資料，key列是使用到的索引，ref列為NULL。range可以在=，<>，>,>=,<,<=,IS NULL,<=>,BETWEEN,LIKE或IN()
- index：index與ALL相似，只是索引樹被掃描，對應2種情況：
a.索引本省儲存相關的列資料，如果索引中的資料可滿足查詢需求，則僅掃描索引樹，在這種情況下，Extra列顯示為Using index。
b.使用對索引的讀取執行全表掃描，以按順序查詢資料行，在這種情況下，Extra沒有出現Using index。
- ALL：全表掃描，如果驅動表不是以const方式獲取資料的，則可以會導致非常糟糕的查詢效能。通常可以新增索引來避免權標掃描
（3.3）執行計劃額外資訊Extra
Extra列用於輸出EXPLAIN的額外資訊，這裡說明了可以在此列中顯示的值，因為這隻值較多，這裡先列出重要的資訊和我能理解的資訊，有的引數翻譯過來實在不理解什麼意思，就忽略了，見諒。
- const row not found：諸如這樣的查詢SELECT ... FROM table_name，表為NUL
- Deleting all rows：對於DELETE操作，某些儲存引擎(如MYISAM)支援一種方法，能夠快速刪除所有資料，如果刪除資料時使用到這種方法，Extra列就顯示該值
- Dintinct：MySQL正在尋找不同的值，因此在找到第一個匹配的行後，將停止當前行搜尋更多
- FirstMatch(table_name)：半連線快捷方式用於該表
- Full scan on NULL key:當優化器無法使用索引訪問時，子查詢優化作為一個備用策略（不明覺厲）
- Impossible HAVING:該HAVING子句始終為false，無法選擇任何行
- Impossible WHERE：該WHERE子句始終為false，無法選擇任何行
- No matching min/max row:沒有行滿足查詢的條件，例如SELECT MIN(...) FROM ... WHERE condition
- No matching row in const table：對於具有連線的查詢，存在一個空表或沒有滿足唯一索引條件的行的表
- No matching rows after partition pruning：在分割槽修剪後，優化器未發現任何要刪除或更新的內容
- No table used：查詢沒有FROM子句，或者有FROM DUAL子句
- Not exists：MySQL能夠對LEFT JOIN進行優化，並且在找到符合LEFT JOIN條件的一行後，不檢查該表中的值相等的行，例如:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL
假設t2.id定義為NOT NULL，在這種情況下，MySQL掃描t1表，並且使用t1.id查詢t2中id相等的資料，如果在t2中找到與t1中id相等的行，MySQL知道t2.id從來不會為NULL， 因此不會再繼續掃描t2表中id相等的值。換句話說，對於t1的每一行資料， MySQL僅僅需要找到t2中與之id相同的一條資料即可，不論t2中有多少條與t1中id相同的資料
- Scanned N databases：這表明在處理information_schema表查詢時伺服器執行了多少目錄掃描。N值可能是0、1、all
- unique row not found：使用唯一性索引、主鍵索引查詢資料，為匹配到行資料
- Using filesort：MySQL必須額外找出如何按順序檢索行。通過根據連線型別遍歷所有行並儲存與該where子句匹配的所有行的排序鍵和指向該行的指標來完成排序。然後對鍵進行排序，並按排序順序檢索行
- Using index：僅使用索引樹中的資訊從表中檢索列資訊，不需要回表訪問資料
- Using index for group-by：表示MySQL找到了一個索引，該索引可用於檢索GROUP BY或DISTINCT查詢的所有列，而無需對實際表進行額外的磁碟訪問
- Using join buffer(Block Nested Loop)，Using join buffer(Batched key Access)：表示使用塊巢狀迴圈演算法，並使用批處理祕鑰訪問演算法
- Using Temporary：為了查詢，MySQL需要建立一個臨時表來儲存結果。這種情況通常發生在GROUP BY、ORDER BY查詢中
- Using where：使用where去過濾返回客戶端的資料行數
- Zero limit：查詢使用了LIMIT 0，沒有返回任何行
【完】