1. 簡介
EXPLAIN語句提供有關(guān) MySQL 如何執(zhí)行語句的信息。
EXPLAIN與SELECT、DELETE、INSERT、REPLACE和UPDATE語句一起使用。
mysql> 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.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
簡單來講，通過EXPLAIN可以分析出SQL語句走沒走索引，走的是什么索引。
EXPLAIN為SELECT語句中使用的每個表返回一行信息，它按照 MySQL 在處理語句時讀取它們的順序列出了輸出中的表。
MySQL 使用嵌套循環(huán)連接(Nested-Loop Join Algorithms)解析所有連接，這意味著 MySQL 從第一個表中讀取一行，然后在第二個表，第三個表中找到匹配的行，依此類推。處理完所有表后，MySQL將通過表列表輸出選定的列后回溯直到找到一個表，其中存在更多匹配的行。從該表中讀取下一行，然后繼續(xù)下一個表。
2.EXPLAIN 輸出列
- MySQL版本 5.7.33
- Windows10 64位
從上圖看到 EXPLAIN 的結(jié)果中，包括的表頭id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra，這些字段的意思我們來學(xué)習(xí)然后通過實例進行了解一下。
2.1 id
SELECT 標(biāo)識符，查詢中 SELECT 的順序號。如果該行引用其他行的并集結(jié)果，則該值可以為NULL。在這種情況下，表列顯示類似unionM,N>的值，以指示該行引用 id 值為 M 和 N 的行的并集。
id 值分三種情況：
id 相同,執(zhí)行順序由上至下
mysql> EXPLAIN (
-> SELECT * FROM employees emp
-> LEFT JOIN dept_emp de ON emp.emp_no = de.emp_no
-> LEFT JOIN departments dept ON dept.dept_no = de.dept_no
-> WHERE emp.emp_no = 10001);
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | de | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | dept | NULL | eq_ref | PRIMARY | PRIMARY | 12 | employees.de.dept_no | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------+
3 rows in set, 1 warning (0.03 sec)
id不相同，如果是子查詢，id的序號會遞增，id的值越大被執(zhí)行的優(yōu)先級越高
mysql> EXPLAIN SELECT * FROM employees emp
-> WHERE emp.emp_no NOT IN ( SELECT de.emp_no FROM dept_emp de
-> WHERE de.dept_no NOT IN ( SELECT dept_no FROM departments WHERE dept_name = 'Development'));
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | Using where |
| 2 | SUBQUERY | de | NULL | index | PRIMARY | dept_no | 12 | NULL | 308493 | 100.00 | Using where; Using index |
| 3 | SUBQUERY | departments | NULL | const | PRIMARY,dept_name | dept_name | 122 | const | 1 | 100.00 | Using index |
+----+-------------+-------------+------------+-------+-------------------+-----------+---------+-------+--------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)
id相同和不相同都存在
如果id相同可以認(rèn)為是一組，同一組id執(zhí)行順序由上至下，不同組之間，id值越大被執(zhí)行的優(yōu)先級越高。
mysql> EXPLAIN SELECT * FROM employees emp
-> WHERE emp.emp_no IN ( SELECT de.emp_no FROM dept_emp de
-> WHERE de.dept_no IN ( SELECT dept_no FROM departments WHERE dept_name LIKE '%Develop%'));
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | subquery2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | 100.00 | NULL |
| 1 | SIMPLE | emp | NULL | ALL | PRIMARY | NULL | NULL | NULL | 299468 | 0.00 | Using where; Using join buffer (Block Nested Loop) |
| 2 | MATERIALIZED | departments | NULL | index | PRIMARY | dept_name | 122 | NULL | 9 | 11.11 | Using where; Using index |
| 2 | MATERIALIZED | de | NULL | ref | PRIMARY,dept_no | dept_no | 12 | employees.departments.dept_no | 38561 | 100.00 | Using index |
+----+--------------+-------------+------------+-------+-----------------+-----------+---------+-------------------------------+--------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.01 sec)
2.2 select_type
查詢的類型，主要用來區(qū)別普通查詢，聯(lián)合查詢，子查詢等復(fù)雜查詢。
包含SIMPLE、PRIMARY、UNION、DEPENDENT UNION、UNION RESULT、SUBQUERY、DEPENDENT SUBQUERY、DERIVED、MATERIALIZED、UNCACHEABLE SUBQUERY、UNCACHEABLE UNION
SIMPLE
簡單的SELECT，不使用UNION或子查詢。
mysql> 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.00 | NULL |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
PRIMARY
查詢中若包含任何復(fù)雜的子部分，最外層的查詢則被標(biāo)記為PRIMARY
mysql> EXPLAIN SELECT * FROM employees emp
-> WHERE emp.emp_no IN ( SELECT max(emp_no) FROM dept_emp);
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
| 1 | PRIMARY | emp | NULL | ALL | NULL | NULL | NULL | NULL | 299468 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+--------------------+-------+------------+------+---------------+------+---------+------+--------+----------+------------------------------+
2 rows in set, 1 warning (0.00 sec)
UNION
第二個或更靠后的 SELECT 語句出現(xiàn)在 UNION 之后，則被標(biāo)記為 UNION
mysql> EXPLAIN (SELECT emp_no,dept_no FROM dept_emp LIMIT 10)
-> UNION
-> SELECT emp_no,dept_no FROM dept_manager;
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
| 1 | PRIMARY | dept_emp | NULL | index | NULL | dept_no | 12 | NULL | 308493 | 100.00 | Using index |
| 2 | UNION | dept_manager | NULL | index | NULL | dept_no | 12 | NULL | 24 | 100.00 | Using index |
| NULL | UNION RESULT | union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+--------------+------------+-------+---------------+---------+---------+------+--------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)
DEPENDENT UNION
與 UNION 相同，它出現(xiàn)在 UNION 或 UNION ALL語句中，但是此查詢受外部查詢的影響
| UNION RESULT union_result Result of a UNION.
| SUBQUERY None First SELECT in subquery
| DEPENDENT SUBQUERY dependent (true) First SELECT in subquery, dependent on outer query
| DERIVED None Derived table
| MATERIALIZED materialized_from_subquery Materialized subquery
| UNCACHEABLE SUBQUERY cacheable (false) A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
| UNCACHEABLE UNION cacheable (false) The second or later select in a UNION that belongs to an uncacheable subquery (see UNCACHEABLE SUBQUERY)
總結(jié)
到此這篇關(guān)于MySQL EXPLAIN輸出列的文章就介紹到這了,更多相關(guān)MySQL EXPLAIN輸出列內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- MySQL查詢優(yōu)化之explain的深入解析
- mysql中explain用法詳解
- mysql總結(jié)之explain
- MySQL性能分析及explain的使用說明
- Mysql中explain作用詳解
- mysql之explain使用詳解(分析索引)
- 詳解MySQL中EXPLAIN解釋命令及用法講解
- MySQL中執(zhí)行計劃explain命令示例詳解
- MYSQL explain 執(zhí)行計劃
- MySQL中EXPLAIN命令詳解
