MySQL執(zhí)行計劃中的各個參數(shù)及含義是什么

發(fā)布時間：2021-10-08 16:05:30 來源：億速云閱讀：123 作者：柒染欄目：MySQL數(shù)據(jù)庫

本篇文章給大家分享的是有關MySQL執(zhí)行計劃中的各個參數(shù)及含義是什么，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

TABLE

顯示這一行的數(shù)據(jù)是關于哪張表的。

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.04 sec)

Id完全相同，都是1，并且select_type的類型為simple，所以是對單表進行掃描，第一步執(zhí)行時，操作的數(shù)據(jù)是c的，

第二步在執(zhí)行時，操作的數(shù)據(jù)是e的，最后執(zhí)行時，操作的數(shù)據(jù)是d的。

Partitions

如果查詢是基于分區(qū)表的話, 會顯示查詢訪問的分區(qū)。

Type

Type表示訪問類型，以下為最好的到最差的訪問類型的排列

結果值最好到最差：

Type：null、system、const、eq_ref、ref、range、index、ALL

type要求：

一般來說,保證查詢至少達到range級別；

最好能達到ref。

NULL

Null是最好的訪問方式，MySQL對語句進行優(yōu)化分解后，在執(zhí)行階段用不著訪問表或者索引（在索引列中查詢最小值）。

EXPLAIN
SELECT MIN(ID) FROM EMPLOYEE WHERE ID < 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.03 sec

可以看到此時的type的類型為null，不經過掃描表或者索引，訪問的效率是最高的

SYSTEM

SYSTEM表示表里只有一行記錄時，const類型的特例。

EXPLAIN
SELECT * FROM (SELECT * FROM CUSTOMER LIMIT 1) A;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  2 | DERIVED     | CUSTOMER   | NULL       | ALL    | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.03 sec)

先執(zhí)行的id=2的，把子查詢里的結果放在臨時表里，該臨時表只有1行記錄，然后在外部查詢該臨時表，

此時id=1對應的type的值為system。

Const

確定只有一行匹配的時候，mysql優(yōu)化器會在查詢前讀取它并且只讀取一次，速度非?？?；

const用于比較primary 或者 unique索引；直接查詢主鍵或者唯一索引。

EXPLAIN 
SELECT * FROM EMPLOYEE WHERE ID = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在查詢時，使用了id列的主鍵索引(或者唯一索引)，是唯一的，所以type的結果為const。

System和const對應的記錄都只有一條，system對應的是表的記錄只有一條；const對應的是通過主鍵索引或者唯一索引，

只查詢到表的記錄的一條，兩個值都對應一條記錄，但是const對應的表的記錄不一定只有一行，大部分情況下，

const的情況很多，system很少。

Eq_ref

對于每個來自于前面的表的行組合，從該表中讀取一行，常用在一個索引是unique key或者primary key；

對于每個索引鍵,表中只有一條記錄與之匹配。

EXPLAIN 
SELECT *
FROM   EMPLOYEE   E
      ,DEPARTMENT D
WHERE  E.ID = D.ID;
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | D     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    5 |   100.00 | NULL  |
|  1 | SIMPLE      | E     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.D.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

Id相同，從上往下執(zhí)行，先對表d進行掃描，然后掃描e；d掃描的結果id的字段與e掃描的結果id進行連接查詢，

由于e的id字段有主鍵索引，所以對與該表的掃描的type為eq_ref。

Ref

對于來自前面的表的行組合，所有有匹配索引值的行都從這張表中讀取,如果聯(lián)接只使用鍵的最左邊的前綴，

或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯(lián)接不能基于關鍵字選擇單個行的話），則使用ref。

ref可以用于使用=或<=>操作符的帶索引的列；

非唯一性索引掃描,返回匹配某個單獨值的所有行；

本質上也是一種索引訪問；

它返回所有匹配某個單獨值的行；

可能會找到多個符合條件的行,；

所以它應該屬于查找和掃描的混合體。

查詢employee表dep_id和department表的id字段。
SELECT E.DEP_ID AS E_DEP_ID
      ,D.ID     AS D_ID
FROM   EMPLOYEE   E
      ,DEPARTMENT D
WHERE  E.DEP_ID = D.ID;
+----------+------+
| E_DEP_ID | D_ID |
+----------+------+
|        1 |    1 |
|        1 |    1 |
|        1 |    1 |
|        4 |    4 |
|        4 |    4 |
+----------+------+
5 rows in set (0.01 sec)
查看該執(zhí)行計劃：
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | E     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    8 |   100.00 | Using where |
|  1 | SIMPLE      | D     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.E.dep_id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

對于表e的掃描的type為all，全表掃描。

在e表的dep_id字段建立索引：

create index idx_emp_01 on employee(dep_id);
再次查看執(zhí)行計劃：
+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | D     | NULL       | index | PRIMARY       | PRIMARY    | 4       | NULL      |    5 |   100.00 | Using index |
|  1 | SIMPLE      | E     | NULL       | ref   | idx_emp_01    | idx_emp_01 | 5       | demo.D.id |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

對于e表的type由all變成了ref，表示使用了索引，但是由于dep_id的值1多個重復值，因此會根據(jù)該值查詢出多個記錄，

所以該type為ref，表示使用了索引，但是由于存在重復值，因此只能是ref。

Range

索引范圍掃描：只檢索給定范圍的行，使用一個索引來選擇行。key列顯示使用了哪個索引。

key_len包含所使用索引的最長關鍵元素。在該類型中ref列為NULL，當使用=、<>、>、>=、<、<=、IS NULL、<=>、

BETWEEN或者IN操作符，用常量比較關鍵字列時，可以使用range。

這種范圍掃描索引比全表掃描要好，因為它只需要開始于索引的某一點而結束語另一點，不用掃描全部索引。

EXPLAIN 
SELECT * FROM EMPLOYEE E WHERE ID BETWEEN 1 AND 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | E     | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    3 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

查詢id在1到3之間的數(shù)據(jù)，由于id有索引，因此是對該字段的索引的范圍掃描，type為range。

Index

Full Index Scan 索引全掃描，index與All區(qū)別為index類型只遍歷索引樹,通常比All要快,因為索引文件通常比數(shù)據(jù)文件要小，

all和index都是讀全表,但index是從索引中讀取,all是從硬盤當中讀取。

EXPLAIN 
SELECT ID FROM EMPLOYEE;
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | index | NULL          | idx_emp_01 | 5       | NULL |    8 |   100.00 | Using index |
+----+-------------+----------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查詢表里id字段，需要對整個表進行掃描，但是由于id有字段，所以不用從磁盤去掃描全數(shù)據(jù)，

只需要對整個索引進行全掃描，此時type值為index，表示對索引的全掃描。

All

將全表進行掃描,從硬盤當中讀取數(shù)據(jù)。

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)

對全表數(shù)據(jù)的查詢，此時只能對全表進行掃描，采用全表掃描的方式，此時type的值為all，表示全表掃描。

在對表進行掃描的方式有8種，性能依次為null、system、const、eq_ref、ref、range、index、ALL。

其中all是全表掃描，性能最差；index是索引全掃描，性能也不好，因此，盡量想辦法時掃描的type值至少是

變?yōu)閞ange以上，走索引，并且是高效的索引。

以上就是MySQL執(zhí)行計劃中的各個參數(shù)及含義是什么，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注億速云行業(yè)資訊頻道。

向AI問一下細節(jié)