EXPLAIN 關鍵字可以用於獲取 SQL 語句執行計劃的相關資訊，在 MySQL 8.0 中，EXPLAIN 支援大多數 SQL 語句，如 SELECT 、DELETE 、INSERT 、REPLACE、和 UPDATE 。示例如下：
mysql> EXPLAIN SELECT * FROM employees;
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299379 | NULL |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------+
複製程式碼注：本篇文章的測試資料來源於 MySQL 官方提供的 Employees Sample Database，其資料庫結構如下：
以下分別介紹 EXPLAIN 輸出結果中各個欄位的含義：
1. id
id 為行識別符號，同時也表示語句執行的優先順序，值越大則優先順序越高。特殊情況下，如果某行語句引用了其他多行結果集的並集，則該值可以為 NULL。示例如下：
# 該FROM字句只是用於演示，並沒有任何實際意義
mysql> EXPLAIN SELECT COUNT(1) FROM (SELECT emp_no FROM salaries) AS t;
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2757635 | NULL |
| 2 | DERIVED | salaries | index | NULL | PRIMARY | 7 | NULL | 2757635 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
複製程式碼# 查詢工資大於500000或部門編號等於d007的所有僱員的編號
mysql> EXPLAIN SELECT emp_no FROM salaries WHERE salary>500000 UNION ALL SELECT emp_no FROM dept_emp WHERE dept_no = "d007";
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+
| 1 | PRIMARY | salaries | ALL | NULL | NULL | NULL | NULL | 2837161 | Using where |
| 2 | UNION | dept_emp | ref | dept_no | dept_no | 4 | const | 91566 | Using where; Using index |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------+---------------+---------+---------+-------+---------+--------------------------+
複製程式碼2. select_type
select_type 用於表示查詢的型別，常見型別及其含義如下：
- SIMPLE：不包含子查詢或者 UNION 操作的查詢；
- PRIMARY：查詢中如果包含任何子查詢，那麼最外層的查詢則被標記為 PRIMARY ；
- SUBQUERY：子查詢中第一個 SELECT ；
- DEPENDENT SUBQUERY：子查詢中的第一個 SELECT，取決於外部查詢；
- UNION：UNION 操作的第二個或者之後的查詢；
- DEPENDENT UNION：UNION 操作的第二個或者之後的查詢,取決於外部查詢；
- UNION RESULT：UNION 產生的結果集；
- DERIVED：出現在 FROM 字句中的子查詢。
這裡以查詢型別為 SUBQUERY 的情況進行演示，示例如下：
# 根據員工編號查詢員工姓名及其工資總和
mysql> EXPLAIN SELECT first_name,(SELECT sum(salary) FROM salaries WHERE emp_no = 10001) FROM employees WHERE emp_no = 10001;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | employees | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
| 2 | SUBQUERY | salaries | ref | PRIMARY | PRIMARY | 4 | const | 17 | NULL |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
複製程式碼3. table
表示語句執行的目標表，除了正常的表名或表別名外，還會出現以下取值：
- <unionM,N>：輸出結果中編號為 M 的行與編號為 N 的行的結果集的並集。
- <derivedN>：輸出結果中編號為 N 的行的結果集，derived 表示這是一個派生結果集，如 FROM 子句中的查詢。
- <subqueryN>：輸出結果中編號為 N 的行的結果集，subquery 表示這是一個物化子查詢。
4. type
type 欄位是進行效能判斷的重要依據，它表示 MySQL 使用何種方式來查詢目標資料集，不同查詢方式會導致不同的效能開銷，常見查詢方式及其效能表現按照由高到低的順序排序如下：
1. system：這是 const 型別的一個特例，只會出現在待查詢的表只有一行資料的情況下。
2. const：常出現在主鍵或唯一索引與常量值進行比較的場景下，此時查詢效能是最優的。
mysql> EXPLAIN SELECT * FROM employees WHERE emp_no = 10008;
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | employees | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
複製程式碼3. eq_ref：當連線使用的是完整的索引並且是 PRIMARY KEY 或 UNIQUE NOT NULL INDEX 時使用它。
# 這裡員工部門關係表 dept_no 的聯合主鍵為 emp_no + dept_no ，即員工編號+部門標號
mysql> EXPLAIN SELECT * FROM employees e,dept_emp d WHERE e.emp_no = d.emp_no AND dept_no = "d005";
+----+-------------+-------+--------+-----------------+---------+---------+--------------------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------+---------+---------+--------------------+------+-----------------------+
| 1 | SIMPLE | d | ref | PRIMARY,dept_no | dept_no | 4 | const | 1 | Using index condition |
| 1 | SIMPLE | e | eq_ref | PRIMARY | PRIMARY | 4 | employees.d.emp_no | 1 | NULL |
+----+-------------+-------+--------+-----------------+---------+---------+--------------------+------+-----------------------+
複製程式碼4. ref：當連線使用的是字首索引或連線條件不是 PRIMARY KEY 或 UNIQUE INDEX 時則使用它。
# 這裡僅使用了字首索引emp_no，所以其型別為 ref , 而不是 eq_ref
mysql> EXPLAIN SELECT * FROM employees e,dept_emp d WHERE e.emp_no = d.emp_no;
+----+-------------+-------+------+---------------+---------+---------+--------------------+--------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+--------------------+--------+-------+
| 1 | SIMPLE | e | ALL | PRIMARY | NULL | NULL | NULL | 299379 | NULL |
| 1 | SIMPLE | d | ref | PRIMARY | PRIMARY | 4 | employees.e.emp_no | 1 | NULL |
+----+-------------+-------+------+---------------+---------+---------+--------------------+--------+-------+
複製程式碼5. ref_or_null：類似於 ref 型別的查詢，但是附加了對 NULL 值列的查詢。示例語句如下：
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
複製程式碼6. index_merge：該聯接型別表示使用了索引進行合併優化，示例如下：
mysql> EXPLAIN SELECT * FROM dept_emp WHERE dept_no = "d004" AND emp_no < 10020;
+----+-------------+----------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
| 1 | SIMPLE | dept_emp | index_merge | PRIMARY,dept_no | dept_no,PRIMARY | 8,4 | NULL | 1 | Using intersect(dept_no,PRIMARY); Using where |
+----+-------------+----------+-------------+-----------------+-----------------+---------+------+------+-----------------------------------------------+
複製程式碼7. range：使用索引進行範圍掃描，常見於 between、> 、< 這樣的查詢條件。
mysql> EXPLAIN SELECT * FROM employees WHERE emp_no > 10000;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | range | PRIMARY | PRIMARY | 4 | NULL | 149689 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
複製程式碼8. index：索引連線型別與 ALL 相同，只是掃描的是索引樹，通常出現在索引是該查詢的覆蓋索引的情況：
mysql> EXPLAIN SELECT emp_no FROM employees;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | index | NULL | PRIMARY | 4 | NULL | 299379 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
複製程式碼9. ALL：全表掃描，效率最差的查詢方式。
mysql> EXPLAIN SELECT * FROM employees WHERE first_name ="Bezalel";
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299379 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
複製程式碼5. possible_keys
表示在執行過程中可能會用到哪些索引來進行優化。
6. key
表示在執行過程中實際用到的用於優化的索引。
7. key_len
表示使用到的索引的位元組數。
8. ref
顯示哪些列或常量與 key 列中指定的索引進行比較。
9. rows
MySQL 為了找到目標行而讀取的所有行的數量，這是一個估算的值。
10. Extra
Extra 列主要用於顯示額外的資訊，常見資訊及其含義如下：
1. Using where ：MySQL 伺服器會在儲存引擎檢索行後再進行過濾。示例如下：
# first_name 欄位是一個普通的列，非索引列
mysql> EXPLAIN SELECT * FROM employees WHERE first_name = "Sumant";
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299379 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
複製程式碼2. Using filesort：通常出現在 GROUP BY 或 ORDER BY 語句中，且排序或分組沒有基於索引，此時需要使用檔案在記憶體中進行排序。因為使用索引排序的效能好於使用檔案排序，所以出現這種情況可以考慮通過新增索引進行優化。示例如下：
mysql> EXPLAIN SELECT * FROM employees ORDER BY first_name ;
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299379 | Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+----------------+
複製程式碼3. Using index：使用了覆蓋索引進行查詢，此時不需要訪問表，從索引中就可以獲取到所需的全部資料。示例如下：
mysql> EXPLAIN SELECT emp_no FROM employees;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | employees | index | NULL | PRIMARY | 4 | NULL | 299379 | Using index |
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
複製程式碼4. Using temporary：表示需要使用臨時表來處理查詢，常出現在 GROUP BY 或 ORDER BY 語句中，示例如下：
mysql> EXPLAIN SELECT first_name,count(first_name) FROM employees GROUP BY first_name ;
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299379 | Using temporary; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+--------+---------------------------------+
複製程式碼參考資料
更多引數的說明可以參考 MySQL 官方文件：dev.mysql.com/doc/refman/…
更多文章，歡迎訪問 [全棧工程師手冊] ，GitHub 地址：github.com/heibaiying/…