如何理解MySQL執(zhí)行計(jì)劃中的各個(gè)參數(shù)及含義

這篇文章給大家介紹如何理解MySQL執(zhí)行計(jì)劃中的各個(gè)參數(shù)及含義，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

本文是對(duì)于MySQL執(zhí)行計(jì)劃的解析，主要解釋了MySQL執(zhí)行計(jì)劃中的各個(gè)參數(shù)及含義。

possible_keys

顯示可能應(yīng)用在這張表中的索引,一個(gè)或者多個(gè)；

查詢涉及到的字段上若存在索引,則該索引將被列出,但不一定被查詢實(shí)際使用(可能自己創(chuàng)建了4個(gè)索引,在執(zhí)行的時(shí)候,

可能根據(jù)內(nèi)部的自動(dòng)判斷,只使用了3個(gè))。

先創(chuàng)建索引
CREATE INDEX IDX_EMP_01 ON employee(dep_id);
CREATE INDEX IDX_EMP_02 ON employee(dep_id,NAME);
查看索引
mysql> show index from employee;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employee |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| employee |          1 | IDX_EMP_01 |            1 | dep_id      | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employee |          1 | IDX_EMP_02 |            1 | dep_id      | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| employee |          1 | IDX_EMP_02 |            2 | name        | A         |           8 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.06 sec)

可以看到表上有3個(gè)索引。

進(jìn)行查詢，并且查看執(zhí)行計(jì)劃：

EXPLAIN 
SELECT ID
FROM   EMPLOYEE
WHERE  DEP_ID = 1
       AND NAME = '魯班';
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys         | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

表上有三個(gè)索引，其中索引IDX_EMP_01是dep_id的單列索引，IDX_EMP_02是列dep_id和列name的復(fù)合索引，

在進(jìn)行查詢時(shí)，可能會(huì)用到索引，因?yàn)橛羞^濾條件dep_id=1，所以會(huì)考慮使用這兩個(gè)索引，因?yàn)樗饕牡谝涣卸际莇ep_id，

此時(shí)的possiable_keys為IDX_EMP_01和IDX_EMP_02。

key

實(shí)際使用的索引,如果為NULL,則沒有使用索引 ;

查詢中若使用了覆蓋索引 ,則該索引僅出現(xiàn)在key列表中。

possible_keys與key關(guān)系：possiable_keys表示理論應(yīng)該用到哪些索引，key表示實(shí)際用到了哪些索引。

還是和上面一樣：

EXPLAIN 
SELECT ID
FROM   EMPLOYEE
WHERE  DEP_ID = 1
       AND NAME = '魯班';
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys         | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

在possiable_keys里顯示了兩個(gè)索引，但是呢，并不是這兩個(gè)索引都會(huì)使用，用哪一個(gè)需要由優(yōu)化器自己決定。

通過key可以發(fā)現(xiàn)，使用的是IDX_EMP_02這個(gè)索引，因?yàn)閣here里同時(shí)有dep_id和name條件，

因此使用這個(gè)索引最為合理，效率最高。

通過key可以發(fā)現(xiàn)，對(duì)該表的真實(shí)的索引的使用情況。

key_len

表示索引中使用的字節(jié)數(shù),可通過該列計(jì)算查詢中使用的索引長度。

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

可以看到只使用索引的第一列時(shí)，長度為5個(gè)字節(jié)。

EXPLAIN 
SELECT ID
FROM   EMPLOYEE
WHERE  DEP_ID = 1
       AND NAME = '魯班';
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys         | key        | key_len | ref         | rows | filtered | Extra       |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | EMPLOYEE | NULL       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_02 | 68      | const,const |    1 |   100.00 | Using index |
+----+-------------+----------+------------+------+-----------------------+------------+---------+-------------+------+----------+-------------+
1 row in set, 1 warning (0.05 sec)

對(duì)于使用了整個(gè)索引時(shí)，顯示的是該索引的大小，key_len由5變成了68，說明name這個(gè)字段在索引里占用的字節(jié)數(shù)為63。

并且，key_len顯示的值是where里的索引的具體大小，不包括order by或者group by時(shí)使用的索引。

ref

索引是否被引入到, 到底引用到了哪幾個(gè)索引。

ref列用來顯示使用哪個(gè)列或常數(shù)與key一起從表中選擇相應(yīng)的行。它顯示的列的名字（或const），此列多數(shù)時(shí)候?yàn)閚ull。

EXPLAIN 
SELECT D.ID
      ,D.ADDRESS
FROM   DEPARTMENT D
      ,EMPLOYEE   E
WHERE  D.ID = E.DEP_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       | ref  | IDX_EMP_01,IDX_EMP_02 | IDX_EMP_01 | 5       | demo.D.id |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+-----------------------+------------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

Ref是用來表示索引是否被使用，到底用了哪個(gè)索引，從執(zhí)行計(jì)劃可看到，對(duì)表d進(jìn)行掃描時(shí)，沒有使用索引，type為all，

是一個(gè)全表掃描，因此ref值為null。對(duì)表e的掃描時(shí)，type為ref，表示使用了索引，并且通過key可以看到確實(shí)使用了索引，

因此在ref列需要被標(biāo)記，索引被引用了，并且把該索引引用了到了d表的id字段上。

Rows

rows列顯示的是mysql解析器認(rèn)為執(zhí)行此SQL時(shí)必須掃描的行數(shù)。此數(shù)值為一個(gè)預(yù)估值，不是具體值，通常比實(shí)際值小。

沒有建索引前：

EXPLAIN 
SELECT D.ID
      ,E.DEP_ID
FROM   DEPARTMENT D
      ,EMPLOYEE   E
WHERE  D.ID = E.DEP_ID;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
| 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       | ALL   | NULL          | NULL    | NULL    | NULL |    8 |    12.50 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

通過執(zhí)行計(jì)劃可以看到對(duì)表d采用索引全掃的掃描方式，需要對(duì)整個(gè)索引進(jìn)行掃描；對(duì)表e采用全表掃描的方式，

因此需要對(duì)整個(gè)數(shù)據(jù)集進(jìn)行掃描，d表有5行，e表有8行，因此在rows里顯示為5和8.

對(duì)e表的dep_id列創(chuàng)建索引：

create index idx_employee_01 on employee(dep_id);
再次查詢：
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+-----------+------+----------+-------------+
| 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_employee_01 | idx_employee_01 | 5       | demo.D.id |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+-----------------+-----------------+---------+-----------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

通過執(zhí)行計(jì)劃可以看到，對(duì)d表采用索引全掃的方式，需要對(duì)整個(gè)索引進(jìn)行掃描；對(duì)表e采用ref的非唯一性索引掃描，

只需要掃描索引的部分?jǐn)?shù)據(jù)，d表有5行數(shù)據(jù)，所以rows為5，對(duì)于e表，掃描滿足條件的數(shù)據(jù)，rows為2。

表d的數(shù)據(jù)：
+----+----------------+---------+
| id | deptName       | address |
+----+----------------+---------+
|  1 | 研發(fā)部(RD)     | 2層     |
|  2 | 人事部(HR)     | 3層     |
|  3 | 市場部(MK)     | 4層     |
|  4 | 后勤部(MIS)    | 5層     |
|  5 | 財(cái)務(wù)部(FD)     | 6層     |
+----+----------------+---------+
5 rows in set (0.07 sec)
表e的數(shù)據(jù)：
+----+-----------+--------+------+---------+--------+
| id | name      | dep_id | age  | salary  | cus_id |
+----+-----------+--------+------+---------+--------+
|  1 | 魯班      |      1 |   10 | 1000.00 |      1 |
|  2 | 后裔      |      1 |   20 | 2000.00 |      1 |
|  3 | 孫尚香    |      1 |   20 | 2500.00 |      1 |
|  4 | 凱        |      4 |   20 | 3000.00 |      1 |
|  5 | 典韋      |      4 |   40 | 3500.00 |      2 |
|  6 | 貂蟬      |      6 |   20 | 5000.00 |      1 |
|  7 | 孫臏      |      6 |   50 | 5000.00 |      1 |
|  8 | 蔡文姬    |     30 |   35 | 4000.00 |      1 |
+----+-----------+--------+------+---------+--------+
8 rows in set (0.00 sec)
對(duì)d表的id和e表的dep_id查詢：
+----+--------+
| ID | DEP_ID |
+----+--------+
|  1 |      1 |
|  1 |      1 |
|  1 |      1 |
|  4 |      4 |
|  4 |      4 |
+----+--------+
5 rows in set (0.00 sec)

Ref是對(duì)傳過來的數(shù)據(jù)進(jìn)行索引的掃描，d表的id有5行，e的dep_id值有多個(gè)，在進(jìn)行等值匹配時(shí)，只有1和4滿足條件。

對(duì)表d先進(jìn)性操作，先掃描這5行數(shù)據(jù)，然后把1和4傳給e表，所以對(duì)于e表只需要對(duì)1和4進(jìn)行索引的掃描，只需要掃描兩行。

Filtered

滿足查詢的記錄數(shù)量的比例，注意是百分比，不是具體記錄數(shù)；值越大越好，filtered列的值依賴統(tǒng)計(jì)信息，并不十分準(zhǔn)確。

對(duì)全表進(jìn)行掃描時(shí)：

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)

進(jìn)行全表掃描時(shí)，會(huì)對(duì)所有的數(shù)據(jù)進(jìn)行掃描，此時(shí)filtered為100.

當(dāng)進(jìn)行范圍查詢時(shí)：

EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_ID > 4;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

對(duì)dep_id大于4的數(shù)據(jù)進(jìn)行查詢，也是全表掃描，返回的數(shù)據(jù)為3行，優(yōu)化器估計(jì)返回了三分之一的數(shù)據(jù)，標(biāo)記為33.33。

再一次進(jìn)行查詢：

EXPLAIN
SELECT * FROM EMPLOYEE WHERE DEP_ID > 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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 |    33.33 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

對(duì)dep_id大于1的數(shù)據(jù)進(jìn)行查詢，也是全表掃描，返回的數(shù)據(jù)為5行，但是還是被認(rèn)定filterd為33.33。

關(guān)于如何理解MySQL執(zhí)行計(jì)劃中的各個(gè)參數(shù)及含義就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。