帶你看懂MySQL執行計劃
本文轉載自微信公眾號「MySQL技術」，作者MySQL技術。轉載本文請聯系MySQL技術公眾號。
前言：
前面文章，我們學習了 MySQL 慢日志相關內容，當我們篩選得到具體的慢 SQL 后，就要想辦法去優化啦。優化 SQL 的第一步應該是讀懂 SQL 的執行計劃。本篇文章，我們一起來學習下 MySQL explain 執行計劃相關知識。
1.執行計劃簡介
執行計劃是指一條 SQL 語句在經過 MySQL 查詢優化器的優化會后，具體的執行方式。MySQL 為我們提供了 EXPLAIN 語句，來獲取執行計劃的相關信息。需要注意的是，EXPLAIN 語句并不會真的去執行相關的語句，而是通過查詢優化器對語句進行分析，找出最優的查詢方案，并顯示對應的信息。
執行計劃通常用于 SQL 性能分析、優化等場景。通過 explain 的結果，可以了解到如數據表的查詢順序、數據查詢操作的操作類型、哪些索引可以被命中、哪些索引實際會命中、每個數據表有多少行記錄被查詢等信息。
explain 執行計劃支持 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 語句。我們一般多用于分析 select 查詢語句。
2.執行計劃實戰
我們簡單來看下一條查詢語句的執行計劃：
- mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
- +----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
- +----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
- | 1 | PRIMARY | dept_emp | NULL | ALL | NULL | NULL | NULL | NULL | 331143 | 100.00 | Using where |
- | 2 | SUBQUERY | dept_emp | NULL | index | PRIMARY,dept_no | PRIMARY | 16 | NULL | 331143 | 100.00 | Using index |
- +----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
可以看到，執行計劃結果中共有 12 列，各列代表的含義總結如下表：
|
列名 |
含義 |
|
id |
SELECT查詢的序列標識符 |
|
select_type |
SELECT關鍵字對應的查詢類型 |
|
table |
用到的表名 |
|
partitions |
匹配的分區，對于未分區的表，值為 NULL |
|
type |
表的訪問方法 |
|
possible_keys |
可能用到的索引 |
|
key |
實際用到的索引 |
|
key_len |
所選索引的長度 |
|
ref |
當使用索引等值查詢時，與索引作比較的列或常量 |
|
rows |
預計要讀取的行數 |
|
filtered |
按表條件過濾后，留存的記錄數的百分比 |
|
Extra |
附加信息 |
下面我們來看下執行計劃中部分重要列詳解：
id：
SELECT 標識符。這是查詢中 SELECT 的序號。如果該行引用其他行的并集結果，則值可以為 NULL 。當 id 相同時，執行順序 由上向下;當 id 不同時，id 值越大，優先級越高，越先執行。
select_type：
查詢的類型，常見的值有：
- SIMPLE：簡單查詢，不包含 UNION 或者子查詢。
- PRIMARY：查詢中如果包含子查詢或其他部分，外層的 SELECT 將被標記為 PRIMARY。
- SUBQUERY：子查詢中的第一個 SELECT。
- UNION：在 UNION 語句中，UNION 之后出現的 SELECT。
- DERIVED：在 FROM 中出現的子查詢將被標記為 DERIVED。
- UNION RESULT：UNION 查詢的結果。
table：
表示查詢用到的表名，每行都有對應的表名，表名除了正常的表之外，也可能是以下列出的值：
type：
查詢執行的類型，描述了查詢是如何執行的。所有值的順序從最優到最差排序為：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
常見的幾種類型具體含義如下：
- system：如果表使用的引擎對于表行數統計是精確的(如：MyISAM)，且表中只有一行記錄的情況下，訪問方法是 system ，是 const 的一種特例。
- const：表中最多只有一行匹配的記錄，一次查詢就可以找到，常用于使用主鍵或唯一索引的所有字段作為查詢條件。
- eq_ref：當連表查詢時，前一張表的行在當前這張表中只有一行與之對應。是除了 system 與 const 之外最好的 join 方式，常用于使用主鍵或唯一索引的所有字段作為連表條件。
- ref：使用普通索引作為查詢條件，查詢結果可能找到多個符合條件的行。
- index_merge：當查詢條件使用了多個索引時，表示開啟了 Index Merge 優化，此時執行計劃中的 key 列列出了使用到的索引。
- range：對索引列進行范圍查詢，執行計劃中的 key 列表示哪個索引被使用了。
- index：查詢遍歷了整棵索引樹，與 ALL 類似，只不過掃描的是索引，而索引一般在內存中，速度更快。
- ALL：全表掃描。
possible_keys：
possible_keys 列表示 MySQL 執行查詢時可能用到的索引。如果這一列為 NULL ，則表示沒有可能用到的索引;這種情況下，需要檢查 WHERE 語句中所使用的的列，看是否可以通過給這些列中某個或多個添加索引的方法來提高查詢性能。
key：
key 列表示 MySQL 實際使用到的索引。如果為 NULL，則表示未用到索引。
key_len：
key_len 列表示 MySQL 實際使用的索引的最大長度;當使用到聯合索引時，有可能是多個列的長度和。在滿足需求的前提下越短越好。如果 key 列顯示 NULL ，則 key_len 列也顯示 NULL 。
rows：
rows 列表示根據表統計信息及選用情況，大致估算出找到所需的記錄或所需讀取的行數，數值越小越好。
Extra：
這列包含了 MySQL 解析查詢的額外信息，通過這些信息，可以更準確的理解 MySQL 到底是如何執行查詢的。常見的值如下：
- Using filesort：在排序時使用了外部的索引排序，沒有用到表內索引進行排序。
- Using temporary：MySQL 需要創建臨時表來存儲查詢的結果，常見于 ORDER BY 和 GROUP BY。
- Using index：表明查詢使用了覆蓋索引，不用回表，查詢效率非常高。
- Using index condition：表示查詢優化器選擇使用了索引條件下推這個特性。
- Using where：表明查詢使用了 WHERE 子句進行條件過濾。一般在沒有使用到索引的時候會出現。
- Using join buffer (Block Nested Loop)：連表查詢的方式，表示當被驅動表的沒有使用索引的時候，MySQL 會先將驅動表讀出來放到 join buffer 中，再遍歷被驅動表與驅動表進行查詢。
這里提醒下，當 Extra 列包含 Using filesort 或 Using temporary 時，MySQL 的性能可能會存在問題，需要盡可能避免。
參考：
- https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
- https://juejin.cn/post/6953444668973514789
