溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這期內容當中小編將會給大家帶來有關MySQL執行計劃中的各個參數及含義指的是什么，文章內容豐富且以專業的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。
可以使用explain + SQL或者desc + SQL
mysql> explain select * from employee; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | employee | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec)
mysql> desc select * from employee; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | employee | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
select查詢的序列號；包含一組數字,表示查詢中執行select子句或操作表的順序。
id的值分為三種情況：
id完全相同；
id完全不同；
id部分相同部分不相同。
對三個表進行關聯
EXPLAIN SELECT * FROM EMPLOYEE E ,DEPARTMENT D ,CUSTOMER C WHERE E.DEP_ID = D.ID AND E.CUS_ID = C.ID; +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+ | **1** | SIMPLE | C | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | E | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 12.50 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | D | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.E.dep_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+----------------------------------------------------+ 3 rows in set, 1 warning (0.01 sec)
可以看到id這一列的數據都是相同的，都是1，執行順序由上到下（先掃描c，在掃描e，最后掃描d）。
使用嵌套子查詢
EXPLAIN SELECT * FROM DEPARTMENT WHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 1)); +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | DEPARTMENT | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | EMPLOYEE | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3 rows in set, 1 warning (0.01 sec)
id為1、2、3，如果是子查詢,id的序號會遞增,id值越大優先級越高,優先被執行。
使用子查詢作為表
EXPLAIN SELECT * FROM DEPARTMENT D ,(SELECT DEP_ID FROM EMPLOYEE GROUP BY DEP_ID) E WHERE D.ID = E.DEP_ID; +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ | 1 | PRIMARY | D | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | demo.D.id | 2 | 100.00 | Using index | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
id部分相同，部分不相同。可以認為是一組,（id=1為一組，id=2為另一組）從上往下順序執行；在所有組中,id值越大,優先級越高,越先執行；先執行完2組，然后執行1組，1組內，相同的依然是從上到下的執行順序。
最終的執行順序：
①id = 2先執行
②id = 1的組內的第一行先執行(d)
③id = 1的組內的第二行最后執行(<derived2>)
對于ID字段來說，相同的值則遵循從上往下的執行順序；不同值時遵循大的先執行。
查詢類型,主要用于區別普通查詢,聯合查詢,子查詢等復雜查詢
SIMPLE、 PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT（DEPENDENT SUBQUERY、DEPENDENT UNION）
簡單select查詢,查詢中不包含子查詢或者UNION。
對employee進行查詢
EXPLAIN SELECT * FROM EMPLOYEE; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
類型為simple，只對一個單表進行查詢。
查詢中若包含任何復雜的子查詢,最外層查詢則被標記為primary。
EXPLAIN SELECT * FROM DEPARTMENT WHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 1)); +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | DEPARTMENT | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | EMPLOYEE | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
Id=1的select_type為primary，對表department進行掃描，并且該查詢是最外層查詢，所以被標記為primary。
在select或where中包含了子查詢，子查詢中的第一個select查詢，不依賴于外部查詢結果。
EXPLAIN SELECT * FROM DEPARTMENT WHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 1)); +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | DEPARTMENT | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 2 | SUBQUERY | EMPLOYEE | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
Id = 2 和id = 3的類型為subquery，是一個子查詢，被標記為subquery，并且由于該子查詢不依賴與外部的查詢結果，所以只顯示subquery。
子查詢的內容依賴與外部查詢的內容，最明顯的標量子查詢。
EXPLAIN SELECT ID ,(SELECT DEP_ID FROM EMPLOYEE E WHERE E.ID = D.ID) DEP_ID FROM DEPARTMENT D; +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ | 1 | PRIMARY | D | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | E | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.D.id | 1 | 100.00 | NULL | +----+--------------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+ 2 rows in set, 2 warnings (0.00 sec)
select中的子查詢依賴與外部查詢d表的結果，所以id = 2是子查詢，并且是依賴于外部查詢的子查詢，被標記為DEPENDENT SUBQUERY。
在from列表中包含的子查詢被標記為derived(衍生)，把結果放在臨時表當中。
EXPLAIN SELECT * FROM DEPARTMENT D ,(SELECT DEP_ID FROM EMPLOYEE GROUP BY DEP_ID) E WHERE D.ID = E.DEP_ID; +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ | 1 | PRIMARY | D | NULL | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 5 | demo.D.id | 2 | 100.00 | Using index | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using temporary | +----+-------------+------------+------------+------+---------------+-------------+---------+-----------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)
從id=2可以看到，先執行2，從table可以發現，被掃描的表是employee，是子查詢的表，該子查詢的結果被放在一個臨時表里，被標記為DERIVED。
若第二個select出現在union之后,則被標記為union，并且是不依賴于外部查詢。
EXPLAIN SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT; +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | EMPLOYEE | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index | | 2 | UNION | DEPARTMENT | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 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)
該查詢是由兩個單獨的select進行的union，不存在子查詢，所以直接把id=2的類型標記為UNION，并且第一個的select會被默認的標記為primary，之后用來union的select都會在該select之前執行。
若第二個select出現在union之后,則被標記為union，并且是依賴于外部查詢。
EXPLAIN SELECT ID ,NAME FROM CUSTOMER WHERE ID IN (SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT); +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | CUSTOMER | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | EMPLOYEE | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | 3 | DEPENDENT UNION | DEPARTMENT | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+ 4 rows in set, 1 warning (0.01 sec)
還是和上面一樣，emp表和dep表進行了union操作，不過現在放在了子查詢里，現在的結果集和外面的查詢的結果集有了聯系。先執行的id=3為department表，并且該查詢在union后面， 由于與外部查詢有聯系，所以被標記為DEPENDENT UNION；第二步執行的是id= 2的表employee，該部分是一個子查詢部分，并且和外部查詢有聯系，所以是一個依賴子查詢，標記為DEPENDENT SUBQUERY；最后執行的是id=1部分，最外層的查詢，被標記為primary。
若union包含在from子句的子查詢中,外層select將被標記為deriver（deriver表必須有別名），如果沒有別名將會報錯。
不寫別名，會報錯：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT); ERROR 1248 (42000): Every derived table must have its own alias
正確寫法，寫上別名：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT) T; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 13 | 100.00 | NULL | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL | | 3 | UNION | DEPARTMENT | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 4 rows in set, 1 warning (0.00 sec)
和上面略有不同，現在是把union的子查詢直接放在了from里面當作一個表，而不是根據一個表的字段去in或者exists連接，此時執行計劃將會有所不同。
第一點：select * from (select * from b)的寫法，必須給括號里的內容起一個別名，如果沒有別名，將會報錯“1248 - Every derived table must have its own alias”。
第二點：在使用這種寫法時，會把括號里的查詢生成一個衍生表（臨時表），標記為DERIVED，進一步分析子查詢，首先執行的是id=3的department表，因為在union 后面，所以對應該表的操作的select_type被標記為union；其次執行的是id=2的employee表，此處被標記為DERIVED；最后執行id=3，并且最外層是select * from ()，被標記為primary，外部查詢，而且由于是查詢括號里的衍生表的記錄，所以掃描的表是該衍生表deriverd2。
從union表獲取結果select；兩個UNION合并的結果集在最后。
若第二個select出現在union之后,則被標記為union，并且是不依賴于外部查詢。
EXPLAIN SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT; +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | EMPLOYEE | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index | | 2 | UNION | DEPARTMENT | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 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)
該查詢是由兩個單獨的select進行的union，不存在子查詢，先執行的id=2，對department進行掃描，在對employee表進行掃描，然后把這兩個單獨查詢結果集進行合并，使用UNION RESULT表示union操作的合并的步驟，并且union是需要對兩部分的數據進行排序然后在進行合并，所以只有當存在排序之后的合并才會有UNION RESULT。
若第二個select出現在union之后,則被標記為union，并且是不依賴于外部查詢。
EXPLAIN SELECT ID FROM EMPLOYEE UNION ALL SELECT ID FROM DEPARTMENT; +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | EMPLOYEE | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index | | 2 | UNION | DEPARTMENT | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | +----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
該查詢是由兩個單獨的select進行的union，不存在子查詢，所以直接把id=2的類型標記為UNION，并且第一個的select會被默認的標記為primary，之后用來union的select都會在該select之前執行。Id部分和select_type部分與union時候一致，唯獨少了最后的UNION RESULT，該部分是因為要對兩部分的數據排序在合并才會產生，而union all不需要排序，只需要把兩部分內容合并就可以，所以不會有UNION RESULT。
若第二個select出現在union之后,則被標記為union，并且是依賴于外部查詢。
EXPLAIN SELECT ID ,NAME FROM CUSTOMER WHERE ID IN (SELECT ID FROM EMPLOYEE UNION ALL SELECT ID FROM DEPARTMENT); +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ | 1 | PRIMARY | CUSTOMER | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | EMPLOYEE | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | | 3 | DEPENDENT UNION | DEPARTMENT | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | +----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)
還是和UNION一樣， 只不過少了UNION RESULT部分。
若union all包含在from子句的子查詢中,外層select將被標記為deriver（deriver表必須有別名），如果沒有別名將會報錯。
不寫別名，會報錯：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT); ERROR 1248 (42000): Every derived table must have its own alias
正確寫法：
EXPLAIN SELECT * FROM (SELECT ID ,NAME FROM EMPLOYEE UNION SELECT ID ,DEPTNAME FROM DEPARTMENT) T; +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 13 | 100.00 | NULL | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | NULL | | 3 | UNION | DEPARTMENT | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+ 4 rows in set, 1 warning (0.00 sec)
和union一樣，不過少了UNION RESULT部分。
上述就是小編為大家分享的MySQL執行計劃中的各個參數及含義指的是什么了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關知識，歡迎關注億速云行業資訊頻道。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
