# MySQL EXPLAIN
EXPLAIN 关键å—å¯ä»¥ç”¨äºŽèŽ·å– SQL è¯å¥æ‰§è¡Œè®¡åˆ’的相关信æ¯ï¼Œåœ¨ MySQL 8.0 ä¸ï¼ŒEXPLAIN 支æŒå¤§å¤šæ•° SQL è¯å¥ï¼Œå¦‚ SELECT ã€DELETE ã€INSERT ã€REPLACEã€å’Œ UPDATE 。示例如下：
```shell
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](https://dev.mysql.com/doc/employee/en/)，其数æ®åº“结构如下：
ä»¥ä¸‹åˆ†åˆ«ä»‹ç» EXPLAIN 输出结果ä¸å„个å—段的å«ä¹‰ï¼š
## 1. id
id ä¸ºè¡Œæ ‡è¯†ç¬¦ï¼ŒåŒæ—¶ä¹Ÿè¡¨ç¤ºè¯å¥æ‰§è¡Œçš„优先级，值越大则优先级越高。特殊情况下，如果æŸè¡Œè¯å¥å¼•ç”¨äº†å…¶ä»–多行结果集的并集，则该值å¯ä»¥ä¸º NULL。示例如下：
```shell
# 该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 | | ALL | NULL | NULL | NULL | NULL | 2757635 | NULL |
| 2 | DERIVED | salaries | index | NULL | PRIMARY | 7 | NULL | 2757635 | Using index |
+----+-------------+------------+-------+---------------+---------+---------+------+---------+-------------+
```
```shell
# 查询工资大于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 | | 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 的情况进行演示，示例如下：
```shell
# æ ¹æ®å‘˜å·¥ç¼–å·æŸ¥è¯¢å‘˜å·¥å§“ååŠå…¶å·¥èµ„总和
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
表示è¯å¥æ‰§è¡Œçš„ç›®æ ‡è¡¨ï¼Œé™¤äº†æ£å¸¸çš„表å或表别å外，还会出现以下å–值：
- ****：输出结果ä¸ç¼–å·ä¸º M 的行与编å·ä¸º N 的行的结果集的并集。
- ****：输出结果ä¸ç¼–å·ä¸º N 的行的结果集，derived 表示这是一个派生结果集，如 FROM åå¥ä¸çš„查询。
- ****：输出结果ä¸ç¼–å·ä¸º N 的行的结果集，subquery 表示这是一个物化å查询。
## 4. type
type å—段是进行性能判æ–çš„é‡è¦ä¾æ®ï¼Œå®ƒè¡¨ç¤º MySQL 使用何ç§æ–¹å¼æ¥æŸ¥æ‰¾ç›®æ ‡æ•°æ®é›†ï¼Œä¸åŒæŸ¥æ‰¾æ–¹å¼ä¼šå¯¼è‡´ä¸åŒçš„性能开销，常è§æŸ¥æ‰¾æ–¹å¼åŠå…¶æ€§èƒ½è¡¨çŽ°æŒ‰ç…§ç”±é«˜åˆ°ä½Žçš„顺åºæŽ’åºå¦‚下：
**1. system**：这是 const 类型的一个特例，åªä¼šå‡ºçŽ°åœ¨å¾…查询的表åªæœ‰ä¸€è¡Œæ•°æ®çš„情况下。
**2. const**：常出现在主键或唯一索引与常é‡å€¼è¿›è¡Œæ¯”较的场景下，æ¤æ—¶æŸ¥è¯¢æ€§èƒ½æ˜¯æœ€ä¼˜çš„。
```shell
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 时使用它。
```shell
# 这里员工部门关系表 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 时则使用它。
```shell
# 这里仅使用了å‰ç¼€ç´¢å¼•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 值列的查询。示例è¯å¥å¦‚下：
```sql
SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
```
**6. index_merge**：该è”接类型表示使用了索引进行åˆå¹¶ä¼˜åŒ–，示例如下：
```shell
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ã€> ã€< è¿™æ ·çš„æŸ¥è¯¢æ¡ä»¶ã€‚
```shell
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 相åŒï¼Œåªæ˜¯æ‰«æçš„æ˜¯ç´¢å¼•æ ‘ï¼Œé€šå¸¸å‡ºçŽ°åœ¨ç´¢å¼•æ˜¯è¯¥æŸ¥è¯¢çš„è¦†ç›–ç´¢å¼•çš„æƒ…å†µï¼š
```shell
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**：全表扫æ，效率最差的查找方å¼ã€‚
```shell
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 æœåŠ¡å™¨ä¼šåœ¨å˜å‚¨å¼•æ“Žæ£€ç´¢è¡ŒåŽå†è¿›è¡Œè¿‡æ»¤ã€‚示例如下：
```shell
# 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 è¯å¥ä¸ï¼Œä¸”排åºæˆ–分组没有基于索引，æ¤æ—¶éœ€è¦ä½¿ç”¨æ–‡ä»¶åœ¨å†…å˜ä¸è¿›è¡ŒæŽ’åºã€‚å› ä¸ºä½¿ç”¨ç´¢å¼•æŽ’åºçš„性能好于使用文件排åºï¼Œæ‰€ä»¥å‡ºçŽ°è¿™ç§æƒ…况å¯ä»¥è€ƒè™‘é€šè¿‡æ·»åŠ ç´¢å¼•è¿›è¡Œä¼˜åŒ–ã€‚ç¤ºä¾‹å¦‚ä¸‹ï¼š
```shell
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**：使用了覆盖索引进行查询，æ¤æ—¶ä¸éœ€è¦è®¿é—®è¡¨ï¼Œä»Žç´¢å¼•ä¸å°±å¯ä»¥èŽ·å–到所需的全部数æ®ã€‚示例如下：
```shell
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 è¯å¥ä¸ï¼Œç¤ºä¾‹å¦‚下：
```shell
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 官方文档：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html