在 MySQL 中，查詢執行計劃是指 MySQL 在執行 SQL 查詢語句時，優化器生成的用于指導查詢引擎執行查詢操作和通路資料的一個計劃。這個計劃包含了查詢語句的執行順序、使用的索引以及關聯表等資訊，是以它對查詢性能的影響非常大。而 EXPLAIN 關鍵字可以幫助我們分析查詢執行計劃，進而優化查詢性能。
一、基本文法
在 MySQL 中，使用 EXPLAIN 關鍵字可以分析查詢語句的執行計劃。其基本文法如下：
mysql複制代碼EXPLAIN SELECT column1, column2, ... FROM table_name WHERE condition;
其中，SELECT 語句是需要分析的查詢語句，可以通過 WHERE 條件限制查詢範圍；EXPLAIN 關鍵字用來分析該查詢語句的執行計劃，輸出查詢計劃的相關資訊。下面是一個簡單的例子：
mysql複制代碼EXPLAIN SELECT * FROM users WHERE age > 18;
二、輸出結果
執行 EXPLAIN 關鍵字後，MySQL 會輸出查詢語句的執行計劃，包括以下幾個方面的資訊：
- id: 查詢編号，表示查詢語句的執行順序。
- select_type: 查詢類型，表示查詢的類型及優化器使用的政策。
- table: 表名，表示查詢時通路的表名稱。
- partitions: 分區，表示查詢操作涉及到的分區。
- type: 通路方式，表示 MySQL 在查找表時使用的讀取方式。
- possible_keys: 可能使用的索引，表示 MySQL 可以使用哪些索引來優化查詢。
- key: 實際使用的索引，表示 MySQL 最終選擇哪個索引來優化查詢。
- key_len: 索引長度，表示 MySQL 在使用索引時所需要的長度。
- ref: 傳回比對條件的列，表示 MySQL 在索引中查找值時使用的比較值。
- rows: 掃描的行數，表示 MySQL 檢索資料的行數。
- filtered: 過濾比例，表示 MySQL 對檢索的資料進行過濾的比例。
- Extra: 其他資訊，可能會包含一些有用的輔助資訊。
接下來，我們将逐個解釋這些查詢計劃中提供的資訊。
1. 查詢編号 id
id 表示了查詢語句的執行順序。在一個查詢語句中，不同的操作都會有一個唯一的編号。這個編号為整數類型，表示 MySQL 執行查詢操作的順序。在查詢計劃中，如果兩個操作的編号相同，則表示它們是同一級别的操作。
2. 查詢類型 select_type
select_type 表示了查詢操作的類型。根據查詢操作的類型，MySQL 可以使用不同的優化器政策來處理查詢語句。常見的幾種查詢類型如下：
- SIMPLE: 簡單查詢，不包含子查詢或者 UNION 查詢。
- PRIMARY: 主查詢，包含多個子查詢或者 UNION 查詢。
- SUBQUERY: 子查詢，作為其它查詢的子查詢使用。
- DERIVED: 派生表，作為其它查詢的臨時表使用。
- UNION: UNION 查詢。
- UNION RESULT: UNION 查詢的結果集。
- DEPENDENT UNION: UNION 查詢的依賴查詢。
- DEPENDENT SUBQUERY: 依賴子查詢，其結果集取決于外層查詢。
- MATERIALIZED: 物化查詢，将查詢結果先緩存再做後續操作。
可以看到，查詢類型分為簡單查詢和複雜查詢兩種。其中，複雜查詢還可以分為主查詢、子查詢、派生表以及 UNION 查詢等幾個子類型。
3. 表名 table
table 表示了目前執行的操作涉及到的表名稱。如果包含多個表，則中間使用逗号 , 隔開。
4. 分區 partitions
partitions 表示目前正在操作的分區，如果查詢操作沒有涉及到分區，則該字段值為空。否則會顯示出查詢操作所涉及到的分區名稱。
5. 通路方式 type
type 表示了 MySQL 在查找表時使用的讀取方式，也就是通路的方式。常見的幾種通路方式如下：
- ALL: 全表掃描，将整個表的資料都讀入記憶體，對于大表來說，這種方式的代價非常大，一般不建議使用。
- index: 全索引掃描，需掃描整個索引檔案，并且需要進行回表操作，進而導緻性能低下。
- range: 範圍掃描，隻掃描滿足查詢條件的記錄。
- ref: 索引查找，通過某個索引找到一個或多個值，并通路對應的行。
- eq_ref: 唯一索引查找，類似 ref，差別在于索引本身是唯一的，是以隻傳回一行資料。
- const: 常量查找，MySQL 在查詢時發現查詢條件中有常量值時，直接按常量值進行查詢。
常用的通路方式包括 ref、eq_ref、range 和 index。這些通路方式都是使用索引進行查找資料的方式，可以有效地提高查詢效率。
6. 可能使用的索引 possible_keys
possible_keys 表示 MySQL 可以使用哪些索引來優化查詢。在查詢計劃中，可能會有多個索引可以用于查詢，這個字段列舉了這些索引的名稱（多個索引名之間以逗号 , 分隔）。MySQL 在執行查詢操作時，會根據所提供的查詢條件使用其中一個索引。
7. 實際使用的索引 key
key 表示 MySQL 實際使用的索引。如果查詢語句中包含了可用的索引，則 MySQL 将使用其中一個索引以優化查詢。這個字段顯示了實際使用的索引的名稱。
8. 索引長度 key_len
key_len 表示 MySQL 在使用索引時所需要的長度。這個長度是以位元組為機關計算的，并且包含了被索引字段的所有部分。這個長度對查詢性能非常重要，如果這個值太大，将會導緻查詢速度變慢。
9. 傳回比對條件的列 ref
ref 表示 MySQL 在索引中查找值時使用的比較值。這個比較值是查詢條件中列的值，或者是常量值。如果使用的索引是唯一索引（eq_ref），則這個值隻有一個。否則，就可能有多個值。
10. 掃描的行數 rows
rows 表示 MySQL 檢索資料的行數。這個值是一個估計值，并不一定非常準确。
11. 過濾比例 filtered
filtered 表示 MySQL 對檢索的資料進行過濾的比例。如果使用了索引，則這個比例表示已經從索引中檢索出的行數占總行數的比例。
12. 其他資訊 Extra
Extra 表示其他一些資訊，可能包括：
- Using index: 表示 MySQL 使用了覆寫索引，而無需回到表中去查找資料。
- Using where: 表示 MySQL 使用了 WHERE 條件來過濾資料。
- Using temporary: 表示 MySQL 使用了臨時表。
- Using filesort: 表示 MySQL 需要使用檔案排序來完成查詢操作。
- Using join buffer: 表示 MySQL 使用了連接配接緩存來加速聯接（JOIN）操作。
- Impossible where: 表示 MySQL 發現查詢條件是不可能出現的，是以不需要執行。
- Select tables optimized away: 表示 MySQL 可以通過優化查詢的方式省略某些表。
三、參數選項
EXPLAIN 關鍵字還支援一些參數選項，可以幫助我們分析查詢計劃和優化查詢性能。以下是一些常用的參數選項：
1. EXTENDED
EXTENDED 參數選項将傳回更詳細的查詢執行計劃資訊，包括掃描的行數、記憶體使用情況等。在預設情況下，MySQL 隻傳回一些基本的資訊，該參數選項可以使輸出更加詳細。
mysql複制代碼EXPLAIN EXTENDED SELECT column1, column2, ... FROM table_name WHERE condition;
例如：
mysql複制代碼EXPLAIN EXTENDED SELECT name, age FROM users ORDER BY age DESC;
使用 EXTENDED 參數輸出的結果如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | users | index | NULL | age | 4 | NULL | 1000 | 100.00 | Using index; Using temporary; Using filesort |
查詢語句中使用了 ORDER BY 子句，是以 MySQL 使用了臨時表和檔案排序的方式進行優化。
2. FORMAT
FORMAT 參數選項可以指定輸出的格式，常用的格式有 JSON 和 XML 兩種。
mysql複制代碼EXPLAIN FORMAT=JSON SELECT column1, column2, ... FROM table_name WHERE condition;
例如：
mysql複制代碼EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 18;
使用 JSON 格式輸出的結果如下：
json複制代碼{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "0.10"
},
"table": {
"table_name": "users",
"access_type": "range",
"possible_keys": [
"age"
],
"key": "age",
"used_key_parts": [
"age"
],
"key_length": "4",
"rows_examined_per_scan": 20,
"rows_produced_per_join": 20,
"filtered": "100.00",
"index_condition": "(`users`.`age` > 18)"
}
}
}
3. PARTITIONS
PARTITIONS 參數選項可以幫助我們分析查詢操作涉及到的分區。在查詢計劃中，partitions字段可以顯示出查詢操作所涉及的分區名稱。使用 PARTITIONS 參數選項可以讓 MySQL 輸出更多分區相關的資訊。
mysql複制代碼EXPLAIN PARTITIONS SELECT column1, column2, ... FROM table_name PARTITION (p1,p2...) WHERE condition;
例如：
mysql複制代碼EXPLAIN PARTITIONS SELECT * FROM orders WHERE date >= '2022-01-01' AND date < '2022-02-01';
使用 PARTITIONS 參數輸出的結果如下：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
| 1 | SIMPLE | orders | p202201 | range | date | date | 3 | const | 10 | 100.00 |
結果顯示，查詢操作涉及到了名為 p202201 的分區表。
4. ANALYZE
ANALYZE 參數選項可以強制 MySQL 對查詢操作進行實際的執行，進而擷取更準确的查詢計劃資訊。如果沒有使用 ANALYZE 參數，則 MySQL 可能會基于統計資訊來做出一些估算。
mysql複制代碼EXPLAIN ANALYZE SELECT column1, column2, ... FROM table_name WHERE condition;
例如：
mysql複制代碼EXPLAIN ANALYZE SELECT * FROM users WHERE age > 18;
使用 ANALYZE 參數輸出的結果中包含了實際執行查詢的時間和 I/O 統計資訊，如下所示：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | cost | analyzed_time | duration | sampled_pages |
| 1 | SIMPLE | users | range | age | age | 4 | NULL | 20 | 100.00 | Using where | 0.10 | 11.796041488647 | 0.000464 | 1 |
結果顯示，執行該查詢的實際執行時間為 analyzed_time 字段所示，I/O 消耗的頁數為 sampled_pages 字段所示。
四、示例
下面舉幾個不同類型的查詢語句的查詢計劃輸出結果，以幫助讀者更好地了解 EXPLAIN 關鍵字的用法。
1. 簡單查詢
mysql複制代碼EXPLAIN SELECT * FROM users WHERE age > 18;
輸出結果：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
| 1 | SIMPLE | users | range | age | age | 4 | null | 20 | 100.00 |
該查詢操作為簡單查詢（SIMPLE），使用了範圍掃描（range）方式進行查詢。MySQL 可能使用了 age 索引來優化查詢，而實際上确實使用了該索引（age）。需要掃描的行數為 20 行，沒有涉及分區的相關資訊。
2. 複雜查詢（主查詢）
mysql複制代碼EXPLAIN SELECT * FROM users WHERE age > (SELECT AVG(age) FROM users);
輸出結果：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
| 1 | PRIMARY | users | ALL | age | null | null | null | 1000 | 10.00 | |
| 2 | SUBQUERY | const | 4 | const | 1 | 100.00 |
該查詢操作為複雜查詢，包含一個子查詢（SUBQUERY）。這個查詢語句中使用了 AVG 函數來計算平均值，并且使用該平均值作為主查詢的查詢條件。在查詢計劃中，MySQL 将主查詢和子查詢分别配置設定了不同的查詢編号。主查詢使用了全表掃描（ALL）方式，子查詢使用了常量查找（const）方式。
3. JOIN 查詢
mysql複制代碼EXPLAIN SELECT users.name, orders.order_number FROM users JOIN orders ON users.id = orders.user_id;
輸出結果：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
| 1 | SIMPLE | users | ALL | PRIMARY | null | null | null | 1000 | 100.00 | |
| 1 | SIMPLE | orders | ref | user_id | user_id | 4 | dbname.users.id | 5 | 100.00 |
該查詢為 JOIN 查詢，使用了 JOIN 關鍵字将兩個表 users 和 orders 進行聯接。在查詢計劃中，MySQL 首先使用了全表掃描（ALL）方式掃描 users 表，然後使用索引查找（ref）方式查找 orders 表中的相關記錄。需要掃描的行數比較少，分别為 1000 和 5 行。
4. ORDER BY 和 GROUP BY 查詢
mysql複制代碼EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name ORDER BY name;
輸出結果：
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
| 1 | SIMPLE | users | index | NULL | PRIMARY | 4 | null | 1000 | 100.00 |
該查詢為 GROUP BY 查詢，使用了 GROUP BY 關鍵字，按照 name 列進行分組，并對分組後的結果進行 COUNT(*) 統計。此外，還有一個 ORDER BY 子句，按 name 的字母順序排序。在查詢計劃中，MySQL 使用了索引查找（index）方式來進行查詢，并且需要掃描 1000 行記錄。
五、進階特性
除了上述可選參數以外，EXPLAIN 還支援一些進階特性，可以通過在查詢語句中使用特定的注釋來啟用這些特性。這些特性主要包括以下幾種：
1. STRAIGHT_JOIN
STRAIGHT_JOIN 可以強制 MySQL 使用連接配接表的順序。
例如：
mysql複制代碼EXPLAIN SELECT * FROM orders STRAIGHT_JOIN users ON orders.user_id = users.id;
使用 STRAIGHT_JOIN 注釋啟用該特性後，MySQL 将按照指定的順序進行連接配接操作。
2. SQL_NO_CACHE
SQL_NO_CACHE 可以讓 MySQL 不緩存查詢結果，每次都強制重新執行查詢操作。
例如：
mysql複制代碼EXPLAIN SELECT SQL_NO_CACHE * FROM users WHERE age > 18;
使用 SQL_NO_CACHE 注釋啟用該特性後，MySQL 不會緩存查詢結果。
3. SQL_CALC_FOUND_ROWS
SQL_CALC_FOUND_ROWS 可以在執行查詢操作的同時擷取總記錄數，有效地避免了多次查詢。
例如：
mysql複制代碼EXPLAIN SELECT SQL_CALC_FOUND_ROWS * FROM users WHERE age > 18 LIMIT 10;
使用 SQL_CALC_FOUND_ROWS 注釋啟用該特性後，在查詢操作的結果中，可以額外輸出一個 rows_examined 字段，表示掃描的記錄數，以及一個 rows_founds 字段，表示滿足條件的總記錄數。
六、性能優化
通過使用 EXPLAIN 關鍵字，我們可以深入了解查詢語句的執行過程，并發現其中的瓶頸和改進空間，進而優化查詢性能，提升資料庫系統的整體運作效率。
下面是一些常見的性能優化技巧：
1. 使用索引
在設計表結構時，可以通過建立索引來提高查詢效率。可以使用 EXPLAIN 檢視查詢操作是否使用了索引，以及是否使用最優的索引；如果沒有使用索引或者使用了不合适的索引，可以考慮為相應的列添加新的索引。
2. 避免全表掃描
全表掃描會消耗大量的 I/O 資源，導緻查詢效率下降。可以優化查詢條件，盡可能地使用索引或者其他方式（如分區表）來避免全表掃描。
3. 減少臨時表和檔案排序
排序操作通常需要使用臨時表和檔案排序，會消耗大量的 CPU 和 I/O 資源，降低查詢效率。可以通過優化查詢條件、增加合适的索引、調整查詢順序等方式來減少排序操作的出現。
4. 避免子查詢
子查詢通常涉及到多次查詢操作，會增加資料庫系統的負擔，導緻查詢效率下降。可以通過使用 JOIN 操作、合理使用索引等方式來避免子查詢的出現。
5. 避免隐式類型轉換
在查詢操作中，經常會涉及到不同類型之間的比較，比如字元串和數字之間的比較。如果 MySQL 需要進行隐式類型轉換，會導緻查詢效率下降。可以使用 CAST 或者 CONVERT 函數來顯式轉換資料類型，避免隐式類型轉換的出現。
總結
本文詳細講解了 MySQL 中的 EXPLAIN 關鍵字，包括其基本用法、輸出結果的各個字段含義、可選參數、更多進階特性以及性能優化等相關内容。使用 EXPLAIN 關鍵字可以深入了解查詢語句的執行過程，發現其中的瓶頸和改進空間，進而優化查詢性能，提升資料庫系統的整體運作效率。在實際應用中，我們應當密切關注查詢語句的執行情況，不斷改進優化，提高系統性能和穩定性。
作者：蜀山劍客李沐白
連結：https://juejin.cn/post/7238262857879470117
![MySQL正常執行的SQL在TiDB中變慢了[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)