不會看 Explain 執行計劃,勸你簡歷別寫熟悉 SQL 優化

chengxy-nds發表於2020-05-22

個人部落格地址:www.chengxy-nds.top,技術資料自取

昨天中午在食堂,和部門的技術大牛們坐在一桌吃飯,作為一個卑微技術渣仔默默的吃著飯,聽大佬們高談闊論,研究各種高階技術,我TM也想說話可實在插不上嘴。

聊著聊著突然說到他上午面試了一個工作6年的程式設計師,表情挺複雜,他說:我看他簡歷寫著熟悉SQL語句調優,就問了下 Explain 執行計劃怎麼看?結果這老哥一問三不知,工作6年這麼基礎的東西都不瞭解!

感受到了大佬的王之鄙視,回到工位我就開始默默寫這個,哎~ 我TM也不太懂 Explain ,老哥你這是針對我啊!哭唧唧~
在這裡插入圖片描述

Explain有什麼用

ExplainSQL語句一起使用時,MySQL 會顯示來自優化器關於SQL執行的資訊。也就是說,MySQL解釋了它將如何處理該語句,包括如何連線表以及什麼順序連線表等。

  • 表的載入順序
  • sql 的查詢型別
  • 可能用到哪些索引,哪些索引又被實際使用
  • 表與表之間的引用關係
  • 一個表中有多少行被優化器查詢
    …..

    Explain有哪些資訊

Explain 執行計劃包含欄位資訊如下:分別是 idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra 12個欄位。

下邊我們會結合具體的SQL示例,詳細的解讀每個欄位以及每個欄位中不同引數的含義,以下所有示例資料庫版本為 MySQL.5.7.17

mysql> select version() from dual;
+------------+
| version()  |
+------------+
| 5.7.17-log |
+------------+

我們建立三張表 onetwothree,表之間的關係 one.two_id = two.two_id AND two.three_id = three.three_id

Explain執行計劃詳解

一、id

id: :表示查詢中執行select子句或者操作表的順序,id的值越大,代表優先順序越高,越先執行id大致會出現 3種情況:

1、id相同

看到三條記錄的id都相同,可以理解成這三個表為一組,具有同樣的優先順序,執行順序由上而下,具體順序由優化器決定。

mysql> EXPLAIN SELECT * FROM one o,two t, three r WHERE o.two_id = t.two_id AND t.three_id = r.three_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                 |    2 |      100 | NULL                                               |
|  1 | SIMPLE      | t     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                 |    2 |       50 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xin-slave.t.three_id |    1 |      100 | NULL                                               |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+
2、id不同

如果我們的 SQL 中存在子查詢,那麼 id的序號會遞增,id值越大優先順序越高,越先被執行 。當三個表依次巢狀,發現最裡層的子查詢 id最大,最先執行。

mysql> EXPLAIN select * from one o where o.two_id = (select t.two_id from two t where t.three_id = (select r.three_id  from three r where r.three_name='我是第三表2'));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |       50 | Using where |
|  2 | SUBQUERY    | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |       50 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |       50 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
3、以上兩種同時存在

將上邊的 SQL 稍微修改一下,增加一個子查詢,發現 id的以上兩種同時存在。相同id劃分為一組,這樣就有三個組,同組的從上往下順序執行,不同組 id值越大,優先順序越高,越先執行。

mysql>  EXPLAIN select * from one o where o.two_id = (select t.two_id from two t where t.three_id = (select r.three_id  from three r where r.three_name='我是第三表2')) AND o.one_id in(select one_id from one where o.one_name="我是第一表2");
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+
|  1 | PRIMARY     | o     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL               |    2 |       50 | Using where |
|  1 | PRIMARY     | one   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xin-slave.o.one_id |    1 |      100 | Using index |
|  2 | SUBQUERY    | t     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               |    2 |       50 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL               |    2 |       50 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------------+------+----------+-------------+

二、select_type

select_type:表示 select 查詢的型別,主要是用於區分各種複雜的查詢,例如:普通查詢聯合查詢子查詢等。

1、SIMPLE

SIMPLE:表示最簡單的 select 查詢語句,也就是在查詢中不包含子查詢或者 union交併差集等操作。

2、PRIMARY

PRIMARY:當查詢語句中包含任何複雜的子部分,最外層查詢則被標記為PRIMARY

3、SUBQUERY

SUBQUERY:當 selectwhere 列表中包含了子查詢,該子查詢被標記為:SUBQUERY

4、DERIVED

DERIVED:表示包含在from子句中的子查詢的select,在我們的 from 列表中包含的子查詢會被標記為derived

5、UNION

UNION:如果union後邊又出現的select 語句,則會被標記為union;若 union 包含在 from 子句的子查詢中,外層 select 將被標記為 derived

6、UNION RESULT

UNION RESULT:代表從union的臨時表中讀取資料,而table列的<union1,4>表示用第一個和第四個select的結果進行union操作。

mysql> EXPLAIN select t.two_name, ( select one.one_id from one) o from (select two_id,two_name from two where two_name ='') t  union (select r.three_name,r.three_id from three r);

+------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | two        | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |       50 | Using where     |
|    2 | SUBQUERY     | one        | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |      100 | Using index     |
|    4 | UNION        | r          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL |    2 |      100 | NULL            |
| NULL | UNION RESULT | <union1,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

三、table

查詢的表名,並不一定是真實存在的表,有別名顯示別名,也可能為臨時表,例如上邊的DERIVED<union1,4>等。

四、partitions

查詢時匹配到的分割槽資訊,對於非分割槽表值為NULL,當查詢的是分割槽表時,partitions顯示分割槽表命中的分割槽情況。

+----+-------------+----------------+---------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table          | partitions                      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+---------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | one            | p201801,p201802,p201803,p300012 | index | NULL          | PRIMARY | 9       | NULL |    3 |      100 | Using index |
+----+-------------+----------------+---------------------------------+-------+---------------+---------+---------+------+------+----------+-------------+

五、type

type:查詢使用了何種型別,它在 SQL優化中是一個非常重要的指標,以下效能從好到壞依次是:system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

1、system

system: 當表僅有一行記錄時(系統表),資料量很少,往往不需要進行磁碟IO,速度非常快。

2、const

const:表示查詢時命中 primary key 主鍵或者 unique 唯一索引,或者被連線的部分是一個常量(const)值。這類掃描效率極高,返回資料量少,速度非常快。

mysql> EXPLAIN SELECT * from three where three_id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | three | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |      100 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3、eq_ref

eq_ref:查詢時命中主鍵primary key 或者 unique key索引, type 就是 eq_ref

mysql> EXPLAIN select o.one_name from one o ,two t where o.one_id = t.two_id ; 
+----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key      | key_len | ref                | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | index  | PRIMARY       | idx_name | 768     | NULL               |    2 |      100 | Using index |
|  1 | SIMPLE      | t     | NULL       | eq_ref | PRIMARY       | PRIMARY  | 4       | xin-slave.o.one_id |    1 |      100 | Using index |
+----+-------------+-------+------------+--------+---------------+----------+---------+--------------------+------+----------+-------------+
4、ref

ref:區別於eq_refref表示使用非唯一性索引,會找到很多個符合條件的行。

mysql> select o.one_id from one o where o.one_name = "xin" ; 
+--------+
| one_id |
+--------+
|      1 |
|      3 |
+--------+
mysql> EXPLAIN select o.one_id from one o where o.one_name = "xin" ; 
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | o     | NULL       | ref  | idx_name      | idx_name | 768     | const |    1 |      100 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
5、ref_or_null

ref_or_null:這種連線型別類似於 ref,區別在於 MySQL會額外搜尋包含NULL值的行。

mysql> EXPLAIN select o.one_id from one o where o.one_name = "xin" OR o.one_name IS NULL; 
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | o     | NULL       | ref_or_null | idx_name      | idx_name | 768     | const |    3 |      100 | Using where; Using index |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
6、index_merge

index_merge:使用了索引合併優化方法,查詢使用了兩個以上的索引。

下邊示例中同時使用到主鍵one_id 和 欄位one_nameidx_name 索引 。

mysql> EXPLAIN select * from one o where o.one_id >1 and o.one_name ='xin'; 
+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                          |
+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | index_merge | PRIMARY,idx_name | idx_name,PRIMARY | 772,4   | NULL |    1 |      100 | Using intersect(idx_name,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+------------------------------------------------+
7、unique_subquery

unique_subquery:替換下面的 IN子查詢,子查詢返回不重複的集合。

value IN (SELECT primary_key FROM single_table WHERE some_expr)
8、index_subquery

index_subquery:區別於unique_subquery,用於非唯一索引,可以返回重複值。

value IN (SELECT key_column FROM single_table WHERE some_expr)
9、range

range:使用索引選擇行,僅檢索給定範圍內的行。簡單點說就是針對一個有索引的欄位,給定範圍檢索資料。在where語句中使用 bettween...and<><=in 等條件查詢 type 都是 range

舉個例子:three表中three_id為唯一主鍵,user_id普通欄位未建索引。

mysql> EXPLAIN SELECT * from three where three_id BETWEEN 2 AND 3;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | three | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |      100 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

從結果中看到只有對設定了索引的欄位,做範圍檢索 type 才是 range

mysql> EXPLAIN SELECT * from three where user_id BETWEEN 2 AND 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | three | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
10、index

indexIndexALL 其實都是讀全表,區別在於index是遍歷索引樹讀取,而ALL是從硬碟中讀取。

下邊示例:three_id 為主鍵,不帶 where 條件全表查詢 ,type結果為index

mysql> EXPLAIN SELECT three_id from three ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | three | NULL       | index | NULL          | PRIMARY | 4       | NULL |    1 |      100 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
11、ALL

ALL:將遍歷全表以找到匹配的行,效能最差。

mysql> EXPLAIN SELECT * from two ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | two   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

六、possible_keys

possible_keys:表示在MySQL中通過哪些索引,能讓我們在表中找到想要的記錄,一旦查詢涉及到的某個欄位上存在索引,則索引將被列出,但這個索引並不定一會是最終查詢資料時所被用到的索引。具體請參考上邊的例子。

七、key

key:區別於possible_keys,key是查詢中實際使用到的索引,若沒有使用索引,顯示為NULL。具體請參考上邊的例子。

typeindex_merge 時,可能會顯示多個索引。

八、key_len

key_len:表示查詢用到的索引長度(位元組數),原則上長度越短越好 。

  • 單列索引,那麼需要將整個索引長度算進去;
  • 多列索引,不是所有列都能用到,需要計算查詢中實際用到的列。

注意:key_len只計算where條件中用到的索引長度,而排序和分組即便是用到了索引,也不會計算到key_len中。

九、ref

ref:常見的有:constfuncnull,欄位名。

  • 當使用常量等值查詢,顯示const
  • 當關聯查詢時,會顯示相應關聯表的關聯欄位
  • 如果查詢條件使用了表示式函式,或者條件列發生內部隱式轉換,可能顯示為func
  • 其他情況null

    十、rows

    rows:以表的統計資訊和索引使用情況,估算要找到我們所需的記錄,需要讀取的行數。

這是評估SQL 效能的一個比較重要的資料,mysql需要掃描的行數,很直觀的顯示 SQL 效能的好壞,一般情況下 rows 值越小越好。

mysql> EXPLAIN SELECT * from three;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | three | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

十一、filtered

filtered 這個是一個百分比的值,表裡符合條件的記錄數的百分比。簡單點說,這個欄位表示儲存引擎返回的資料在經過過濾後,剩下滿足條件的記錄數量的比例。

MySQL.5.7版本以前想要顯示filtered需要使用explain extended命令。MySQL.5.7後,預設explain直接顯示partitionsfiltered的資訊。

十二、Extra

Extra :不適合在其他列中顯示的資訊,Explain 中的很多額外的資訊會在 Extra 欄位顯示。

1、Using index

Using index:我們在相應的 select 操作中使用了覆蓋索引,通俗一點講就是查詢的列被索引覆蓋,使用到覆蓋索引查詢速度會非常快,SQl優化中理想的狀態。

什麼又是覆蓋索引?

一條 SQL只需要通過索引就可以返回,我們所需要查詢的資料(一個或幾個欄位),而不必通過二級索引,查到主鍵之後再通過主鍵查詢整行資料(select * )。

one_id表為主鍵

mysql> EXPLAIN SELECT one_id from one ;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | one   | NULL       | index | NULL          | idx_two_id | 5       | NULL |    3 |      100 | Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

注意:想要使用到覆蓋索引,我們在 select 時只取出需要的欄位,不可select *,而且該欄位建了索引。

mysql> EXPLAIN SELECT * from one ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | one   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2、Using where

Using where:查詢時未找到可用的索引,進而通過where條件過濾獲取所需資料,但要注意的是並不是所有帶where語句的查詢都會顯示Using where

下邊示例create_time 並未用到索引,typeALL,即MySQL通過全表掃描後再按where條件篩選資料。

mysql> EXPLAIN SELECT one_name from one where create_time ='2020-05-18';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | one   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
3、Using temporary

Using temporary:表示查詢後結果需要使用臨時表來儲存,一般在排序或者分組查詢時用到。

mysql> EXPLAIN SELECT one_name from one where one_id in (1,2) group by one_name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | one   | NULL       | range| NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
4、Using filesort

Using filesort:表示無法利用索引完成的排序操作,也就是ORDER BY的欄位沒有索引,通常這樣的SQL都是需要優化的。

mysql> EXPLAIN SELECT one_id from one  ORDER BY create_time;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | one   | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |      100 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

如果ORDER BY欄位有索引就會用到覆蓋索引,相比執行速度快很多。

mysql> EXPLAIN SELECT one_id from one  ORDER BY one_id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | one   | NULL       | index | NULL          | PRIMARY | 4       | NULL |    3 |      100 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
5、Using join buffer

Using join buffer:在我們聯表查詢的時候,如果表的連線條件沒有用到索引,需要有一個連線緩衝區來儲存中間結果。

先看一下有索引的情況:連線條件 one_nametwo_name 都用到索引。

mysql> EXPLAIN SELECT one_name from one o,two t where o.one_name = t.two_name;
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref                  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+--------------------------+
|  1 | SIMPLE      | o     | NULL       | index | idx_name      | idx_name | 768     | NULL                 |    3 |      100 | Using where; Using index |
|  1 | SIMPLE      | t     | NULL       | ref   | idx_name      | idx_name | 768     | xin-slave.o.one_name |    1 |      100 | Using index              |
+----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+--------------------------+

接下來刪掉 連線條件 one_nametwo_name 的欄位索引。發現Extra 列變成 Using join buffertype均為全表掃描,這也是SQL優化中需要注意的地方。

mysql> EXPLAIN SELECT one_name from one o,two t where o.one_name = t.two_name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |      100 | NULL                                               |
|  1 | SIMPLE      | o     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
6、Impossible where

Impossible where:表示在我們用不太正確的where語句,導致沒有符合條件的行。

mysql> EXPLAIN SELECT one_name from one WHERE 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 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     | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
7、No tables used

No tables used:我們的查詢語句中沒有FROM子句,或者有 FROM DUAL子句。

mysql> EXPLAIN select now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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     | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

Extra列的資訊非常非常多,這裡就不再一一列舉了,詳見 MySQL官方文件 :dev.mysql.com/doc/refman/5.7/en/ex...
在這裡插入圖片描述

總結

上邊只是簡單介紹了下 Explain 執行計劃各個列的含義,瞭解它不僅僅是要應付面試,在實際開發中也經常會用到。比如對慢SQL進行分析,如果連執行計劃結果都不會看,那還談什麼SQL優化呢?


整理了幾百本各類技術電子書和視訊課程 ,送給小夥伴們。同名公號內回【666】自行領取。和一些小夥伴們建了一個技術交流群,一起探討技術、分享技術資料,旨在共同學習進步,如果感興趣就掃碼加入我們吧!

本作品採用《CC 協議》,轉載必須註明作者和本文連結

相關文章