-------+--------------------+-------+----------+-------------+
| 1 | PRIMARY | d | <null> | ref | PRIMARY,dept_no | dept_no | 12 | const | 33212 | 100.0 | Using index |
| 1 | PRIMARY | e | <null> | eq_ref | PRIMARY | PRIMARY | 4 | employees.d.emp_no | 1 | 100.0 | <null> |
| 3 | SUBQUERY | de | <null> | const | dept_name | dept_name | 122 | const | 1 | 100.0 | Using index |
+----+-------------+-------+------------+--------+-----------------+-----------+---------+--------------------+-------+----------+-------------+
3 rows in set
Time: 0.020s
select_type
select_type为表查询的类型，根据官方文档总结几种常见类型如下表：
| SIMPLE |
None |
简单查询，不包含unino查询或子查询 |
| PRIMARY |
None |
位于最外层的查询 |
| UNION |
None |
当出现union查询时第二个或之后的查询 |
| DEPENDENT UNION |
dependent(true) |
当出现union查询时第二个或之后的查询，取决于外部查询 |
| UNION RESULT |
union_result |
union查询的结果 |
| SUBQUERY |
None |
子查询当中第一个select查询 |
| DEPENDENT SUBQUERY |
dependent(true) |
子查询当中第一个select查询，取决于外部的查询 |
| DERIVED |
None |
派生表，from子句中出现的子查询 |
- SIMPLE：最常见的查询类型，通常情况下没有子查询、union查询就是SIMPLE类型。
mysql root@localhost:employees> explain select * from employees where emp_no = 10001;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
| 1 | SIMPLE | employees | <null> | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.0 | <null> |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+--------+
1 row in set
Time: 0.019s
- PRIMARY和SUBQUERY：在含有子查询的语句中会出现。
mysql root@localhost:employees> explain select * from dept_emp d where d.dept_no = (select de.dept_no from departments de where de.dept_name = 'De
velopment');
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
| 1 | PRIMARY | d | <null> | ref | dept_no | dept_no | 12 | const | 148054 | 100.0 | Using where |
| 2 | SUBQUERY | de | <null> | const | dept_name | dept_name | 122 | const | 1 | 100.0 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+-------+--------+----------+-------------+
2 rows in set
Time: 0.021s
- UNION和UNION RESULT：在有union查询的语句中出现。
mysql root@localhost:employees> explain select * from departments where dept_no = 'd005' union select * from departments where dept_no = 'd004';
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+--------+--------------+-------------+------------+-------+---------------+---------+---------+--------+--------+----------+------------